TSQL on the JOB (1)

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


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


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


filtering on where


select * from orders

where orderdate < ‘2009-07-01’

use a single quotation mark for string and date

basics operator

<> = < > <= >=


to define composite logical condition


select * from ordreitems


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


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

select * from customers


not clubmemeber = 1


to use in a range of ordered values


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’)


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

NULL = NULL is false !

so use the IS operator


select * from orders

where completedate IS NULL


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


function works on input to transform it in output


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


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


aggregate functions

they have multiple input values and 1 or more output value


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


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


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)


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

you can’t use aggregate on where


select orderid, sum(itemprice*orederqty)

from orderitems

group by orderid

where sum(itemprice*orederqty) >30


having instead

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


select orderid, sum(itemprice*orederqty)

from orderitems

group by orderid

where orderid >5



SELECT: Advanced Tricks



use TOP to get required number of rows

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


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


the OFFSET can be useful in paging queries

N> this kind of syntax is very platform dependent


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


select * from orders

WHERE customerid IN

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

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


use alias to change the column names in the output


select name as CustomerName from customers

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


select name AS CustomerName from customers

where CustomerName like ‘%m%’


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


select * from customers c

where c.name like ‘%m%’


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


select cast(itemsku as varchar(20) as converted

from orderitems





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


INSERT INTO (priKey, description)

SELECT forKey, Description

FROM SomeView

they order of columns is important of course



UPDATE customers

SET address = ‘Columbia’

where name = ‘Mario A’

N> the where is crucial

N> you can use DEFAULT in defined



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


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


select ord_num from sales

select stor_name from stores

select ord_num, stor_name from sales sa

cross join stores st


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.


select ord_num, stor_name from sales sa

cross join stores st

where sa.stor_id = st.stor_id


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


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



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


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


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


let’s add 9999, no store has this Id


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


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



create a single result set from 2 or more select statements


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


SELECT * FROM authors_Y

ORDER BY au_fname

SELECT au_fname, au_lname, city FROM authors_x


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


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


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


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


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