TSQL on the JOB (1)

This is a walkthrough based on the SQL On The JOB, please buy it Pointing up

Introduction

the sql is an ANSI standard so you can run it on

● sql server

● mysql

● postresql

● db2

● oracle

● sybase

● etc

DBMS = is an environment that manage data in relational format

Database and Querying Concepts and Overview

Identity Seed = first id of a PK

Identity Increment = increment for new rows

Relational data = ensuring the relationship between tables and code coherency

Cascading referential integrity = update on row of the table with PK has to be reflected on the FK tables that reference the PK table to maintain referential integrity

Ex

table Customers with PK with ID

table Orders with FK ID on CustomerID

defining the cascading ref integrity we can define action taken on the Orders row affected by any change on the Customers rows, if I delete a Customer I want to delete its orders to avoid to have orders rows pointing through the CustomerID to Customer that doesn’t exist at all

what ID to choose as PK

when the entities represented in the table has natural ID use then to avoid duplication on real entity. If a person has a SSN used in the system so use it instead to use a general ID auto incremented

SELECT: the basics

N> capitalize the keywords

the * get all the columns from the table

not use because

● no idea what is the data coming

● force to go through all the column enumerating them

escaping column names

use the [] in sql server to say the word are column name

use ‘ in mysql

Filtering with WHERE

clip_image002

filtering on where

ex

select * from orders

where orderdate < ‘2009-07-01’

use a single quotation mark for string and date

basics operator

<> = < > <= >=

and/or/not

to define composite logical condition

ex

select * from ordreitems

where

(itemsku = ‘BEZ-M’ and qty >1)

OR

(itemsku = ‘BEZ-R’ and qty >10)

select * from customers

where

not clubmemeber = 1

between/in

to use in a range of ordered values

ex

select * from orders

where orderdate between ‘2009’ and ‘2010’

select * from orders

where level between ‘A’ and ‘E’

select * from orders

where level IN (‘A’ ,’C’, ‘E’)

NULL

N> it’s a special value, everything compared to null is false

NULL = NULL is false !

so use the IS operator

ex

select * from orders

where completedate IS NULL

like

using this operator allows to use wildcards

% = any number character

_ = any 1 character

N> use the difference() function that will give you back how much 2 words are similar in the case you are not sure about some value and the wild cards are not enough

SELECT: using Functions

clip_image004

function works on input to transform it in output

ex

select upper(name), [address]

from customers

not deterministic functions

getdate() now() they will give you different output for the same input

N. the not deterministic cannot be used in certain context

ex

select *

from customers

where lower(name) = ‘mario amatucci’

scalar functions

it takes one input and gives one output

N> you can use a it everywhere a column is expected

SELECT: Aggregates, Group BY, HAVING

clip_image006

aggregate functions

they have multiple input values and 1 or more output value

ex

select count(*) from customers

select avg(itemprice) from orderitmes

group by

using the group by and aggregation function you can calculate aggregation on the row sets

N> you can select only on col in group by or in aggregate func, otherwise the query will fail

Ex

select orderid, sum(itemprice*orderqty)

from orderitems

group by orderid

// the result is aggregation of the rows on the orderid, all the rows with the same orderid are group together (applying the sum()) and will be shown as unique row in the results

N> if I will put the

select orderid, itemsku, sum(itemprice*orderqty)

from orderitems

group by orderid

this will not work as in the aggregated result 2 different itemsku values could be part of the same aggregation, but as they are aggregated, there is no way to show this details, so you have to put in the group by clause too to sub group on itemsku too.

order by

you can define the order by clause to order the result from the select

N> in case of aggregation we have limits to the column we can use in the order by clause

ex

select orderid, sum(itemprice*orederqty)

from orderitems

group by orderid

order by itemprice

fires an error as itemprice cannot be used, use the whole aggregated sum(itemprice*orederqty)

having

it’s a special where clause to use in aggregate in condition we need a where

you can’t use aggregate on where

ex

select orderid, sum(itemprice*orederqty)

from orderitems

group by orderid

where sum(itemprice*orederqty) >30

use

having instead

you can’t use column in group by (they are aggregated) and then define where on it

ex

select orderid, sum(itemprice*orederqty)

from orderitems

group by orderid

where orderid >5

use

having

SELECT: Advanced Tricks

clip_image008

top/limit

use TOP to get required number of rows

N> it builds the query and stop when the top criteria is reached

ex

select top (2) * from customers

N> adding order by clause could be hard when no index are defined on the order by column

use the PERCENT to get a percent of results

OFFSET

the OFFSET can be useful in paging queries

N> this kind of syntax is very platform dependent

SUB-QUERY

you can define sub query in SELECT statement to retrieve a subset matching the output of the inner query

ex

select * from orders

WHERE customerid IN

(SELECT id from customers name like ‘%M%’)

N> this is not ideal, use multi-table joins

Alias

use alias to change the column names in the output

ex

select name as CustomerName from customers

N> you can’t reuse the alias in clauses in the query itself

ex

select name AS CustomerName from customers

where CustomerName like ‘%m%’

fails

use name

you can define alias for table name

N> useful in joins between different tables

N> here it can be referred to columns of tables

ex

select * from customers c

where c.name like ‘%m%’

distinct

to get distinct values of a result set on the required column

casting data

the cast convert the representation from a type to another type

N> implicit cast are implemented differently on diff platforms

ex

select cast(itemsku as varchar(20) as converted

from orderitems

INSERT, UPDATE, DELETE

clip_image010

insert

ex

INSERT INTO customers

VALUE (‘mario Amatucci’, ‘Dublin’ , IR ,1)

N> you have to provide all the values if you don’t provide columns names

If you have default value for columns they will used if not specified

inserting using subqueries

ex

INSERT INTO (priKey, description)

SELECT forKey, Description

FROM SomeView

they order of columns is important of course

UPDATE

ex

UPDATE customers

SET address = ‘Columbia’

where name = ‘Mario A’

N> the where is crucial

N> you can use DEFAULT in defined

DELETE

ex

delete from customers

N> to delete all in the tables, so use where everytime

SELECT: Multi-Table

join aims to join table rows together in different way

clip_image012

cross join

Cartesian product in which all the rows of the 1st table are put together with each row oft he 2nd tables in ordered order

ex

select ord_num from sales

select stor_name from stores

select ord_num, stor_name from sales sa

cross join stores st

clip_image014

as expected the number of the cross join is 126 =21 x 6

inner join

match the rows between tables based on the criteria

When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

ex

select ord_num, stor_name from sales sa

cross join stores st

where sa.stor_id = st.stor_id

clip_image016

N> this is an inner join, it’s different from the cross join, so use the following syntax

ex

select ord_num, stor_name from sales sa

inner join stores st

on sa.stor_id = st.stor_id

N. use the aliasing when the column name are the same (ex ID)

left join

it includes all the rows on the left table and all the rows on the right column that will match the condition, it means the left data will be decorated with extra info, if any coming from right table

clip_image018

ex

N> delete the sales for store Eric the Read Books

select stor_name, ord_num from stores st

left join sales sa

on st.stor_id = sa.stor_id

order by stor_name, ord_num

select stor_name, ord_num from stores st

right join sales sa

on st.stor_id = sa.stor_id

order by stor_name, ord_num

clip_image020

note the NULL in the first case and the missing in the second as expected

full join

it matches all the rows regardless an existing association, if there is no match it adds NULL

ex

let’s remove the constraint to create an orphan sales order

clip_image022

let’s add 9999, no store has this Id

clip_image024

select st.stor_id as StId , sa.stor_id as SaStoreId, sa.ord_num as SaOrdNum from stores st

full join sales sa

on st.stor_id = sa.stor_id

order by stor_name, ord_num

clip_image026

as expected we have the orphan row for the sales and the one for the one for the store without any sale (coming from the previous example)

N> usually you use the IS NULL to retrieve all the orphan row for maintenance purpose

Advance Multi-Tables

clip_image028

Union

create a single result set from 2 or more select statements

Ex

SELECT au_fname, au_lname, city, ‘X’ AS my INTO authors_x FROM authors

SELECT au_fname, au_lname, city, ‘Y’ AS my INTO authors_Y FROM authors

SELECT * FROM authors_X

UNION

SELECT * FROM authors_Y

ORDER BY au_fname

SELECT au_fname, au_lname, city FROM authors_x

UNION

SELECT au_fname, au_lname, city FROM authors_y

ORDER BY au_fname

N> to see duplication in the second case to use UNION ALL

N> all the clause has to be in the end of the UNION, think it as a big SELECT

N> the order is important as the data type have to match and of course the number too

INTERSECT

The INTERSECT keyword in TSQL is used to find what is common between two datasets

EXCEPT

the complement o INTERSECT, The EXCEPT keyword in TSQL is used to find what is different between two datasets.

N> it takes what is on the left that is not in the right of the statement

NATURAL JOIN

it’s a special join where the same tuple are kept

clip_image030

N> check whether common columns exist in both tables before doing a natural join.

One thought on “TSQL on the JOB (1)

  1. Pingback: TSQL on the JOB (2) « Obar1 aka Mario Amatucci

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s