SQL Queries – All in 1

[Download PDF – SQL Queries]

–Section–SQL Queries–
Part 1: Basic Selection QueriesSelect, Order By, Distinct, Limit, Count
Part 2: Aggregate Functions SQLSum, Avg, Min, Max, Group By, Having
Part 3: String Functions SQLUpper, Lower, Left, Right, Length, Position, Substring, Concat
Part 4: Conditional QueriesCase, Coalesce, Cast, Replace
Part 5: Combining Data QueriesUnion, Union All, Except, Intersect, Date
Part 6: Window FunctionsOver, Partition BY, Order By
Part 7: Ranking FunctionsRank, Dense Rank, Row Number, NTile
Part 8: Insert QueriesInsert data using fix data, select query, Stored Proc, CTE
Part 9: Update QueriesUpdate data using from, joins, subquery, CTE, conditions
Part 10: Common Table ExpressionSingle CTE, Multiple CTE, Recursive CTE, Merge
Part 11: User Defined FunctionsScalar Functions, Drop, List, Create, Call UDFs
Part 12: Table FunctionsInline and Multi Statement Table Functions, Cross Apply, Outer Apply
Part 13: SQL SubQuerySubQuery in where, from, select and Correlated Subquery
Part 14: SQL JoinsInner Join, Left Join, Right Join, Cross Join, Self Join, Multiple Joins
Part 15: SQL WildcardsWild Card Filters with Where Clause
Part 16: SQL Range OperatorsNot, In, Like, Between, And, Or
Part 17: SQL Windows and RangeRows, Range, Window Definition, Modification, Referencing, Select Into
[SQL Stored Procedures]SQL Stored Procedures Cheat Sheet
[SQL with Java JDBC]Java JDBC for SQL and Stored Procedures
Download this Cheat Sheet with :

Part 1: Basic Selection Queries – Select, Order By, Distinct, Limit, Count

ClauseTypeQueryMeaningOutput
Selectall columnsselect * from actor;display all columns from table actor
Selectspecific columnsselect first_name,last_name
from actor;
display only first_name and last_name columns from table actor
Order Bydescending orderingselect first_name,last_name
from actor
order by first_name desc;
sort the selected columns by first_name in descending order Z-Afirst_name | last_name
Zach . . . | Schnider
Amy . . . .| Farah
Order Byascending ordering
default
select first_name,last_name
from actor
order by first_name asc;
sort the selected columns by last_name in ascending order A-Zfirst_name | last_name
Amy . . . .| Farah
Zach . . . | Schnider
Order Bymultiple column orderingselect *
from payment
order by customer_id asc,
amount
desc;
first sorts result by customer_id in ascending – if multiple customer_id rows are present then sorts them by amount in descendingcustomer_id | amount
1 . . . . . | 10
1 . . . . . | 5
2 . . . . . | 12
2 . . . . . | 8
Distinctsingle columnselect
distinct
rating
from film;
distinct will give unique values in a column – will show all distinct rating values in the rating columnrating
PG
G
X
Distinctmultiple columnselect
distinct
rating,awards
from film
order by rating;
distinct will apply on both columns – will return unique combinations of both columns – rating and durationrating | awards
G . . .| Emmy
G . . .| Oscar
PG . . | Filmfare
PG . . | Oscar
X . . .| Emmy
Limitselect *
from rental
order by rental_date desc
limit 3;
returns only top x rows – used with order by on date to return latest x results
Countwith *select
count(*)
from customer;
count(single_column or *)
Counts the number of rows in the table when *
Counts number of non-null value in column
returns number of customer
Countwith columnselect
count(first_name)
from customer;
Number of customers in customer table – count will not count null values present in the column
Countwith distinctselect
count(distinct first_name)
from customer;
Counts the number of distinct values in the column

Part 2: Aggregate Functions SQL – Sum, Avg, Min, Max, Group By, Having

ClauseTypeQueryMeaningOutput
Sumsum of column valuesselect sum(amount) as total
from payment
adds all the values in amount column from payment table and returns only one added up valuetotal
1526
Avgaverage of column valuesselect avg(amount) as avg_amnt
from payment
averages values in amount column and return one average valueavg_amnt
34.56789
Roundinput1 = floating number
input2 = number of digits to return after decimal
select round(
  avg(amount),
  2
) as avg_amnt
from payment;
round(float,n)

rounds the floating numer (average in this case) to n decimal places

example 4.923423489 to 4.92
avg_amnt
34.56
Minminimum value in a columnselect min(amount) as minimum_sale
from payment;
returns a single minimum value from the column specifiedminimum_sale
1.99
Maxmaximum value in a columnselect max(amount) as maximum_sale
from payment;
returns a single maximum value from the column specifiedmaximum_sale
25.99
Mixmultiple aggregation functionsselect
sum(amount) as sum_amnt,
avg(amount) as avg_amnt,
count(amount) as cnt_amnt
from payment
returns 3 columns – sum of all values in amount column, average of all values in amount column, and count of  records in amount column from paymnet tablesum_amnt | avg_amnt | cnt_amnt
1526 . . | 34.5678 .| 5
Group bybasic – group rows on a specific column – then apply aggregation functions over the groupselect customer_id, sum(amount)
from payment
group by customer_id;
select col1, col2, agg_func(col3) from table
group by col1, col2
can select columns in group by clause and any other columns on which aggregate functions are applied

returns all customer ids and the sum of amount paid by each customer
customer_id | sum(amount)
3 . . . . . |  100
4 . . . . . |  200
5 . . . . . |  260
Group bygroup by with where clause – filter data before groupingselect customer_id, sum(amount)
from payment
where customer_id>3
group by customer_id;
first filter data having customer_id  greater than 3 – then perform grouping on remaining customer ids and the sum of amount paid by each customercustomer_id | sum(amount)
4 . . . . . | 200
5 . . . . . | 260
Group bygroup by with order clauseselect customer_id, sum(amount)
from payment
group by customer_id
order by sum(amount) desc;
returns all customer ids and the sum of amount paid by each customer – orders the result by the maximum amount paidcustomer_id | sum(amount)
5 . . . . . | 260
4 . . . . . | 200
Group bygroup by multiple columnsselect staff_id, customer_id,
sum(amount), count(*)
from payment
group by staff_id, customer_id
order by count(*) desc
returns unique combinations of staff_id and customer_id with the sum of amount showing how much a particular staff has sold to a particular member. Count(*) shows the number of payments made by a customer to a particular staff.staff_id | customer_id | sum | count
1 . . . .| 5 . . . . . | 280 | 12
2 . . . .| 5 . . . . . | 260 | 10
2 . . . .| 6 . . . . . | 150 | 8
Havingfilter data after groupings – applied over the aggregation functionselect customer_id, sum(amount)
from payment
group by customer_id
having sum(amount)>200
select col1, col2, agg_func(col3) from table where colx = ‘xyz’ group by col1, col2 having agg_func(col3) = 100
having can only be used on columns with aggregation functions. where clause can be used on any column in the table

returns customers who have paid a total amount greater than 200 in all payment records
staff_id | customer_id  | sum | count
1 . . . .| 5 . . . . . .| 280 | 12
2 . . . .| 5 . . . . . .| 260 | 10

Part 3: String Functions SQL – Upper, Lower, Left, Right, Length, Position, Substring, Concat

ClauseTypeQueryMeaningOutput
upperin columnselect upper(email) as email_upper from peopleupper(column/value)
t
ranforms column data inot upper case
email_upper
K@YH.COM  
LIN@P.COM  
lowerin columnselect lower(email) as email_lower from peoplelower(column/value)
tranforms column data inot lower case
email_lower
k@yh.com  
lin@p.com
lengthin columnselect length(email) as len from peoplelength(column/value)
calculates length of each value in the specified column
len  
8
9
upper
lower
length
in where clauseselect lower(email) as email_lower from people where length(email) > 20 can combine functions in same or different columns of select clauseemail_lower
kronos-the-hero@yahoomail.com
leftin columnselect left(email,3) as left_email, email from peopleleft(column,n) function will return the first n characters from the left side of every value in the columnleft_email | email
k@y . . . .| k@yh.com
lin . . . .| lin@p.com
rightin columnselect right(email,3) as right_email, email from peopleright(column,n) function will return the last n characters from the right side of every value in the columnright_email | email
com . . . . | k@yh.com
com . . . . | lin@p.com
left
right
nestedselect left(right(email,4),1)
as fourth_last_char, email from people
right(column,4) on email will give .com on all email values. left on that value with index of 1 will give first character which is dot (.) fourth_last_char | email
. . . . . . . . .| k@yh.com
. . . . . . . . .| lin@p.com
concatbasicselect first_name+‘.’+last_name
as display_name,
first_name, last_name from people
|| or + operator will combine the values in columns specifieddisplay_name | first_name | last_name
lin.do . . . | lin . . . .| do
nancy.chen . | nancy . . .| chen
position /
charindex
of a fixed characterselect position(‘@’ in email),
left(
  email,
  position(‘@’ in email)-1
) as email_left,
email from people
position(string/column-to-find  in  string/column)
position(string1 in string2) returns the position of string1 inside string2 starting from 1
position | email_left | email
2 . . . .| k . . . . .| k@yh.com
4 . . . .| lin . . . .| lin@p.com
position /
charindex
of a value in columnselect position(lname in email),
left(
  email,
  position(lname in email)-2
) as fname_left,
lname, email from people
position(column1 in column2) returns the position of string in column1 inside string present in column2. Index starts from 1 for each row.pos | fname_left | lname | email
7 . | nancy . . .| chen .| nancy.chen@yh.in
5 . | lin . . . .| do . .| lin.do@p.com
substringbasicselect substring(
  email
  from position(‘.’ in email)+1
  for length(lname)
) as last_name, email from people;

select
substring(
  email
  from position(‘.’ in email)+1
  for position(‘@’ in email)-
      position(‘.’ in email)-1
) as last_name, email from people
substring(col/string from position [for length])

finds substing in the column or string – starting from the position specified – and having the length specified
last_name | email
chen . . .| nancy.chen@gmail.com

Part 4 Conditional Queries – Case, Coalesce, Cast, Replace

ClauseTypeQueryMeaningOutput
Casebasicselect amount,
case
  when
amount<2 then ‘low’
  when amount<5 then ‘medium’
  else ‘high’
end from payment
case
  when condition1 then result1
  when condition2 then result2
  else result
end
amount | case
1 . . .|  low
2 . . .|  medium
3 . . .|  medium
7 . . .|  high
5 . . .|  high
Casewith group by and countselect count(*) as pay_count,
case
  when
amount<2 then ‘low’
  when amount<5 then ‘medium’
  else ‘high’
end as pay_size
from payment group by pay_size
groups the rows on the case column (pay_size) and then run aggregate function count(*) over the groups to return the count column (pay_count) of each group (pay_size)pay_count | pay_size
420 . . . |  low
750 . . . |  medium
260 . . . |  high
Casewith sumselect sum(
  case
    when
rating in (‘PG’,’G’) then 1
    else 0
  end
) as count_pg_g from film
returns the count of number of film records having a specific case condition (PG or G rating) count_pg_g
257
Casewith sum – multipleselect sum(
  case
    when
rating=’G’ then 1 else 0
  end
) as g_count,
sum(
  case
    when
rating=’PG’ then 1 else 0
  end
) as pg_count from film
returns count of films having a specific rating in a different columng_count  |  pg_count
20 . . . |  34
Case with countselect jobtitle,
count(
  case
    when
gender=’M’ then 1 else null
  end
) as male_cnt,
count(
  case
    when
gender=’F’ then 1 else null
  end
) as female_cnt
from employee group by jobtitle
count() will not count nulls
job_title .| male_cnt | female_cnt
account . .| 2 . . . .| 1
clerk . . .| 5 . . . .| 2
tester . . | 1 . . . .| 7
Caseanywhere a value is requiredcase can be used anywhere a value is required-
1. in where clause condition
2. in set clause of update query
3. in on clause in join conditions
Coalescebasicselect fname, lname,
coalesce(fname+’.’+lname, fname)
  as full_name,
fname+lname as dname
from people
operations like + – or concat will retunr null if any of the input is null

coalesce(value1, value2, …)
will return the first value passed to it which is not null
fname | lname . | full_name | dname
Xin . | Po . . .| Xin.Po . .| XinPo
Neil .| null . .| Neil . . .| null
Castbasicselect coalesce(
  cast(end_date – start_date as varchar),
  ‘running’
) as duration,
start_date, end_date from transactions
cast (value/column as datatype)
cast changes the datatype of value
duration | start_date | end_date
10 …….. | 15:00 …….| 25:00
running. | 30:00 …….| null
Replacebasicselect amount, cast(
  replace(amount,’,’,”) as int
) as amnt_no
from sales
replace(column, old_string, new_string)
replaces old_string present in column values with new_string
amount | amnt_no
5,200 .. | 5200
2,400 .. | 2400

Part 5: Combining Data Queries – Union, Union All, Except, Intersect, Date

ClauseTypeQueryMeaningOutput
unioncombine with distinctselect fname,lname from old_people
union
select fname,lname from new_people
1. combines 2 tables or select results
2. both results should have same number of columns
3. need compatible data types in corresponding columns (string and numbers are not compatible)
4. final columns will use the biggest compatible data type (tinyint and bigint column will create bigint column)
4. gives distinct records (removes duplicate records) – takes more time
5. column names are taken from the first set of results

Union = Distinct (A + B)
old_people
Jake  | Jacob
Peter | Parker

new_people
Jake  | Jacob
Kurt  | Konner

union
fname | lname
Jake  | Jacob
Kurt  | Konner
Peter | Parker
union allcombine no distinctselect fname,lname from old_people
union all
select fname,lname from new_people
same as union but will not remove duplicates

Union All = (A + B)
fname | lname
Jake  | Jacob
Jake  | Jacob
Kurt  | Konner
Peter | Parker

both tables have entry for Jake
exceptcombine with minusselect fname,lname from old_people
except
select fname,lname from new_people
1. all the rows in the first table except rows in the second table
2. all the rows in the first table that do not match rows in the second table

Except = (A – B)
fname | lname
Peter | Parker

both tables have entry for Jake
intersectcombine only commonselect fname,lname from old_people
intersect
select fname,lname from new_people
1. all the rows in the first table that are also present in the second table

Intersect = Common Rows in A and B
fname | lname
Jake  | Jacob

both tables have entry for Jake
union
union all
except
intersect
with order byselect fname,lname from old_people
union
select fname,lname from new_people
order by fname asc
1. order by clause cannot come before union / except / intersect clauses
2. order by clause can only come after the result of union / except / intersect
fname | lname
Jake  | Jacob
Kurt  | Konner
Peter | Parker
Dateselect date(payment_time) as payment_date, payment_time from payment;date(timestamp_value)
extract date in a format from the timestamp column

example if payment_date is a big timestamp in milliseconds, then date(payment_date) will give simple timestamp like 2023-02-24
payment_date | payment_time
2023-02-24 . | 2023-02-24:05:25:36:66

Part 6: Window Functions – Over, Partition BY, Order By

ClauseTypeQueryMeaningOutput
Any All Window FunctionsWindow Functions are evaluated after Where clause. So window functions are not allowed in where clause.

However, this can be done by using the main window function query in a cte with clause and then using where on the window function value
Over with Partition Byselect
emp_id, sal_amount, sal_date,
sum(sal_amount)
over (
  partition by employee_id
) as sum
from salary
Aggregation(agg_column) over (partition by part_column1, part_column2)

Partition by = Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

partition_column = Specifies the column by which the rowset is partitioned.

Similar to group by clause but – group by will give a single row for every group. Partition will return all rows and give the aggreagted value for every row – depending on the partition
emp_id | sal_amount | sal_date | sum
121 . .| 2500 . . . | Jun-2023 | 5500
121 . .| 3000 . . . | Aug-2023 | 5500
122 . .| 3500 . . . | Aug-2023 | 4500
122 . .| 1000 . . . | Sep-2023 | 4500
Over with Order Byselect emp_id, sal_amount, sal_date,
sum(sal_amount)
over (
  order by employee_id
) as sum
from salary
Aggregation(agg_column) over (order by order_column)

when partition by is not used with over then the window function is applied over all the rows in the result as a single partition

Order by = defines the logical order of the rows within each partition of the result set

the window function is computed for each partition separately but result is cumulative based on the order_column.

Gives a running total/aggregation on each row depending on the order column
emp_id | sal_amount | sal_date | sum
121 . .| 2500 . . . | Jun-2023 | 2500
121 . .| 3000 . . . | Aug-2023 | 5500
122 . .| 3500 . . . | Aug-2023 | 9000
122 . .| 1000 . . . | Sep-2023 | 10000
Over with Partition By and Order Byselect emp_id, sal_amount, sal_date,
sum(sal_amount)
over (
  partition by employee_id
  order by employee_id
) as sum
from salary
Aggregation(agg_column) over (partition by part_column order by part_column)

Gives a running total/aggregation on each row depending on the order by column

The running aggregation is calculated for every partition separately
emp_id | sal_amount | sal_date | sum
121 . .| 2500 . . . | Jun-2023 | 2500
121 . .| 3000 . . . | Aug-2023 | 5500
122 . .| 3500 . . . | Aug-2023 | 3500
122 . .| 1000 . . . | Sep-2023 | 4500

Part 7: Ranking Functions – Rank, Dense Rank, Row Number, NTile

ClauseTypeQueryMeaningOutput
Dense Rankwith Order Byselect emp_id, sal_amount, sal_date,
dense_rank() over (
  order by month_salary
) as rank
from salary
dense_rank() over (order by order_column)

dense_rank() will give a rank number to the row in every partition separately. rank will depend on the order by column

dense_rank returns the rank of each row within a partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank value that come before that specific row based on order by column.
emp_id | sal_amount | sal_date | rank
122 . .| 1000 . . . | Sep-2023 | 1
121 . .| 2500 . . . | Jun-2023 | 2
121 . .| 3000 . . . | Aug-2023 | 3
122 . .| 3500 . . . | Aug-2023 | 4
Dense Rankwith Partition By and Order Byselect emp_id, sal_amount, sal_date,
dense_rank() over (
  partition by employee_id
  order by month_salary
) as rank
from salary
dense_rank() over (partition by part_column order by order_column)

dense_rank() will give a rank number to each row in the partition depending on the order by column
emp_id | sal_amount | sal_date | rank
121 . .| 2500 . . . | Jun-2023 | 1
121 . .| 3000 . . . | Aug-2023 | 2
122 . .| 1000 . . . | Sep-2023 | 1
122 . .| 3500 . . . | Aug-2023 | 2
Rankwith Partition By and Order Byrank() over  
(partition by … order by …)
rank and dense_rank provides the same numeric value for ties.

If two or more rows tie for a rank (based on the order by column), then each tied row receives the same rank (for example 1, 2, 2, 4, 5)

in case of ties, dense_rank will give sequential ranks (for example 1, 2, 2, 3, 4, 5)

in case of ties, rank function does not return consecutive integers (for example 1, 2, 2, 4, 5)
Row Numberwith Partition By and Order Byrow_number() over
(partition by … order by …)


query usage similar to dense rank
row_number returns the sequential number of a row within every partition separately, starting at 1 for the first row in each partition.

row_number numbers all rows sequentially (for example 1, 2, 3, 4, 5).
Row Numberwith cte – to return a subset of rowswith my_cte as (
  select emp_id, sal_amount, sal_date,
  row_number()
over
  (order by sal_amount desc)
  as rnum from salary
)
select * from my_cte
where rnum between 1 and 2
emp_id | sal_amount | sal_date | rnum
123 . .| 7500 . . . | Oct-2023 | 1
122 . .| 6500 . . . | Aug-2023 | 1
121 . .| 4000 . . . | Aug-2023 | 2
121 . .| 3500 . . . | Aug-2023 | 2
Ntilewith Order Byselect emp_id, sal_amount, sal_date,
ntile(4)
over (
  order by sal_amount desc
) as ntile
from salary
Ntile(n) distributes the rows in an ordered partition into a specified n number of groups.

The groups are numbered, starting at one. For each row, ntile returns the number of the group to which the row belongs.

for example ntile(4) will divide every partition into 4 groups. Each row in the partition can have a group number (1, 2, 3, 4) based on the order by column
emp_id | sal_amount | sal_date | ntile
123 . .| 7500 . . . | Oct-2023 | 1
122 . .| 6500 . . . | Aug-2023 | 1
121 . .| 4000 . . . | Aug-2023 | 2
121 . .| 3500 . . . | Aug-2023 | 2
121 . .| 3000 . . . | Aug-2023 | 3
121 . .| 2500 . . . | Jun-2023 | 3
122 . .| 1000 . . . | Sep-2023 | 4
Ntilewith Partition By and Order Byselect emp_id, sal_amount, sal_date,
ntile(4)
over (
  partition by emp_id
  order by sal_amount desc
) as ntile
from salary
emp_id | sal_amount | sal_date | ntile
121 . .| 4000 . . . | Aug-2023 | 1
121 . .| 3500 . . . | Aug-2023 | 2
121 . .| 3000 . . . | Aug-2023 | 3
121 . .| 2500 . . . | Jun-2023 | 4
122 . .| 9000 . . . | Sep-2023 | 1
122 . .| 6500 . . . | Aug-2023 | 2
122 . .| 3500 . . . | Aug-2023 | 3
122 . .| 2500 . . . | Aug-2023 | 4
Ntilewith cte – to return a subset of rowswith my_cte as (
  select emp_id, sal_amount, sal_date,
  ntile(4)
over (
    order by sal_amount desc
  ) as qtile
  from salary
)
select * from my_cte where qtile = 1
emp_id | sal_amount | sal_date | ntile
123 . .| 7500 . . . | Oct-2023 | 1
122 . .| 6500 . . . | Aug-2023 | 1

Part 8: Insert Query – Insert data using fix data, select query, Stored Proc, CTE

ClauseTypeQueryMeaningOutput
Insertwith valueinsert into accounts (id, account_name) values (10,’tony-stark’)insert a record
– with fixed hardcoded values
Insertwith multiple valuesinsert into accounts (id, account_name) values (10,’tony-stark’),(11,’peter-parker’)insert multiple records
– with different fixed values
Insertwith selectinsert into accounts (id, account_name)
select account_id, name from third_party_accounts join….
insert multiple/bulk records
– by using data from another table
– by forming a result with joins on different tables
Insertwith procedureinsert into accounts (id, account_name)
execute dbo.getEmployeeAccounts
insert multiple/bulk records
– by using data returned from a stored procedure
Insertwith ctewith cte as (
select account_id,name from …join…)
insert into accounts
select account_id,name from cte
insert multiple/bulk records
– by using data from a cte using with clause
– cte can be created with multiple tables, joins

Part 9: Update Query – Update data using from, joins, subquery, CTE, conditions

ClauseTypeQueryMeaningOutput
Updatebasicupdate accounts set band = ‘low’
where id=12
update a single record
– using fixed value on a where condition
Updatewith fromupdate a set a.band = ‘low’
from accounts a
where a.id=12
updates single / multiple records
– using fixed value on a where condition
– from clause to alias the table being updated
Updatewith from and joinsupdate a set a.band = s.salary_level
from accounts a
join salary s on s.account_id=a.id
where s.amount > 10000
updates single / multiple records
– using value from another table
– from clause to alias the table being updated
– join clause to match the records and get corresponging update value for each row
– can only update data from existing columns in the joined table
Updatewith subqueryupdate a set band = (
  select derived_col from salary s
  where s.account_id=a.id
)
from accounts a
updates single / multiple records
– using value from another table
– from clause to alias the table being updated
– subquery to match the records and get corresponging update value for each row
– can update a column based on dervied value from subquery
Updatewith from and caseupdate a set a.band = (
  case
    when a.salary > 10000 then ‘high’
    else ‘low’
  
end
)
from accounts a
updates single / multiple records
– using value calculated using case clause
– from clause to alias the table being updated
– case clause to get the calculated value
– can update a column based on derived value from case clause
– case clause can internally have subqueries depending on complex requirements
Updatewith ctewith my_cte as (select band, derived_band from accounts join salary on …)

update my_cte set band = derived_band
updates single / multiple records
– using value from a cte
– can update data from existing/derived columns in the cte
Updatewith cte joinwith my_cte as (select account_id, derived_band from accounts join salary on …)

update a set a.band = c.derived_band
from accounts a
join my_cte c on c.account_id=a.id
updates single / multiple records
– using value from a cte
– from clause to alias the table being updated
– join clause to match the records from cte and get corresponging update value for each row
– can update a column based on a derived column in cte

Part 10: Common Table Expression – Single CTE, Multiple CTE, Recursive CTE, Merge

ClauseTypeQueryMeaningOutput
withsingle ctewith sales_cte (person_id, order_count)
as (
  select person_id, COUNT(*)
  from sales group by person_id
)
select * from sales_cte;
With clause defines a temporary named result set, known as CTE.
– CTE result is derived from a simple/complex query
– CTE can be used only for the next single sql statement.
person_id | order_count
121       | 74
122       | 33
124       | 14
withmultiple ctewith sales_cte (person_id, order_count)
as (
  select person_id, COUNT(*)
  from sales group by person_id
),
person_cte (person_id, person_name) as (
  select person_id, person_name from person
)
select person_id, person_name, avg(order_count) as avg_orders from sales_cte join person_cte
on sales_cte.person_id = person_cte.person_id;
Multiple CTEs separated by comma.
– can join different cte declared for the next statement
person_id | person_name | avg_orders
121       | Jake        | 74
122       | Kurt        | 33
124       | Aamy        | 14
withrecursive ctewith emp_cte as (
  select empid, emp_name,
  mgrid, 0 as level
  from employee
  where mgrid is null

  
union all

  select e.empid, e.emp_name, m.level+1
  from employee e
  join emp_cte c
  on e.mgrid = c.empid
)

select * from emp_cte;
The recursive CTE syntax –
1. must contain at least two CTE query definitions
2. an anchor query and a recursive query.
3. multiple anchor and recursive queries can be defined
4. all anchor queries must be put before the first recursive queries.
5. anchor query does not join the CTE itself.
6. anchor and recursive queries must be combined by union all

Internally how it works –
1. init – the cte is empty and an internal copy of table (employee) is created with all rows
2. first anchor query is evaluated
3. this fills up cte with a row
4. same row is removed from the table internal copy
5. then recursive query is evaluated by joining cte filled in previous step with the updated internal copy of table
6. result of recursive query is added to fill up cte further
7. again rows returned from recursive query are removed from internal copy of table
8. then recursive query is evaluated again and again till no rows are present in the internal copy of table
anchor query evaluation =
empid | emp_name | mgrid | level
123   | John     | null  | 0

anchor + recursive query =
empid | emp_name | mgrid | level
123   | John     | null  | 0
124   | Kurt     | 123   | 1

anchor + rec query + rec query=
empid | emp_name | mgrid | level
123   | John     | null  | 0
124   | Kurt     | 123   | 1
125   | Hugo     | 124   | 2
126   | Lobo     | 124   | 2

anchor + rec + rec + rec =
empid | emp_name | mgrid | level
123   | John     | null  | 0
124   | Kurt     | 123   | 1
125   | Hugo     | 124   | 2
126   | Lobo     | 124   | 2
127   | Aamy     | 125   | 3
128   | Pike     | 126   | 3
129   | Masy     | 126   | 3
mergemergemerge prod_people
  as tgt
using (SELECT person_id, name from staging …. join …)
  as src (person_id, name)
on (tgt.person_id= src.person_id)
when matched and tgt.to_delete = 1 then
  delete
when matched then

  update set name = src.name
when not matched by target then
  insert
(person_id, name)
  values (src.person_id, src.name)
when not matched by source then
  delete
when matched clause typically used to fire update query since target and source table record is matching

– a maximum of 2
when matched clauses can be present. If 2 are present then one should have an and condition

when not matched by source clause typically used to fire delete query on target table since record is not present in source table but present in target

– a maximum of 2
when not matched by source clauses can be present. If 2 are present then one should have an and condition

when not matched by target clause typically used to fire insert query on target table since record is present in target table but not present in source

–  only 1
when not matched by target clauses can be present
Merge is basically used to sync a target table with a source table or derived result

can use insert, update and delete queries together

Part 11: User Defined Functions – UDF, Scalar Functions, Drop, List, Create, Call UDFs

ClauseTypeQueryMeaningOutput
functionscalarcreate function addfunc
(@param1 int, @param2 int)
returns int
as begin
  
return @param1 + @param2
end
function benefits
– modular code .. can be used in select
– can be used in where clause to allow complex conditions
– can be faster than join and group by

scalar function can return only one value back
functionscalar function callselect jan_sale, feb_sale, dbo.addfunc(jan_sale, feb_sale) as jf_sale from sales;

declare @jf_sale int
@jf_sale = addfunc 120 200
select @jf_sale
functions can be used in select statements and execute command

stored procedure can only be used in execute command
jan_sale | feb_sale | jf_sale
120 . . .| 200 . . .| 320
functionscalarcreate function tranCount
(@customer_id int)
returns int
as begin
  declare @tran_count int
  select @tran_count = count(*)
  from transactions
  where customer_id = @customer_id
  
return @tran_count
end
functionscalar function callselect customer_id,
tranCount(customer_id) as tran_count
from transactions;
customer_id | tran_count
201 . . . . | 12
202 . . . . | 18
functiondropdrop function tranList;
functionlistselect * from sys.objects where name = ‘tranCount’lists all database objects including functionsname . . . | type | type_desc
tranCount .| FN . | SQL_SCALAR_FUNCTION
functiondrop + createif exists (select * from sys.objects where name = ‘tranCount’)
  drop function
tranCount
go

create function tranCount (….) returns int
as begin
  ….
  
return ….
end

Part 12: Table Functions – Inline Table Functions, Multi Statement Table Functions, Cross Apply, Outer Apply

ClauseTypeQueryMeaningOutput
functioninline table functioncreate function tranList
  (@customer_id int)
returns table
as return
(
  select * from transaction
  where customer_id = @customer_id
)
Inline table functions can return a table result back

can have only a single statement to return table
functioninline table function callselect * from dbo.tranList(210);

select * from customers c where exists (select * from dbo.tranList(c.customer_id))
Since return is a table, the function can be called in from clause of the select querycustomer_id | amount | tran_date
210 . . . . | 2300 . | ——
210 . . . . | 1500 . | ——
210 . . . . | 700 . .| ——
functionmulti statement table function create function tranList
  (@customer_id int)
returns @returnTrans table as
  (customer_id int, amount bigint,
  tran_date date)
as begin
  insert into @returnTrans
  (customer_id, amount, tran_date)
  select customer_id, amount, tran_date
  from transaction
  where customer_id = @customer_id
  
return
end
Multi-statement table functions can have multiple statements

– can have if conditions to determine logic

functionmulti statement table function callselect * from dbo.tranList(210);

select * from customers c
where exists (
  select * from dbo.tranList(c.customer_id)
)
multi-statement table functions can be called in the from clause of the select querycustomer_id | amount | tran_date
210 . . . . | 2300 . | ——
210 . . . . | 1500 . | ——
210 . . . . | 700 . .| ——
functiontable function call
– correlated subquery
– in select
select c.customer_id, c.name, (
  select count(*)
  from dbo.tranList(c.customer_id)
) as tran_count
from customer c
customer_id | name | tran_count
210 . . . . | jake | 3
211 . . . . | kurt | 7
functiontable function call
– correlated subquery
– in where
select c.customer_id, c.name
from customer c
where (
  select count(*)
  from dbo.tranList(c.customer_id) > 5
)
customer_id | name | tran_count
211 . . . . | kurt | 7
applycross applyselect c.customer_id, c.name, tl.amount
from customer c
cross apply
dbo.tranList(c.customer_id) tl
used to inner join the result of table function with other tables

the on condition is not required as it is implictly dependent on the input parameter of the function
customer_id | name | amount
210 . . . . | jake | 2300
210 . . . . | jake | 1500
211 . . . . | kurt | 7500
applyouter applyselect c.customer_id, c.name, tl.amount
from customer c
outer apply
dbo.tranList(c.customer_id) tl
used to left outer join the result of table function with other tablescustomer_id | name | amount
209 . . . . | tate | null
210 . . . . | jake | 2300
210 . . . . | jake | 1500
211 . . . . | kurt | 7500
applycross apply – built in table function splitselect customer_id, amount
from customer_sales;

select customer_id, ss.value
from customer_sales cs
cross apply string_split(cs.amount,’;’) ss;

every string_split function call will return a table having a single column with multiple rows having splitted single values

cross apply will join the results of customer table and the split return table on the input column – amount
customer_id | amount
210 . . . . | 2300; 1500; 700
211 . . . . | 7500; 300

customer_id | amount
210 . . . . | 2300
210 . . . . | 1500
210 . . . . | 700
211 . . . . | 7500
211 . . . . | 300

Part 13: SQL SubQuery – SubQuery in where, from, select and Correlated Subquery

ClauseTypeQueryMeaningOutput
subqueryinside where inselect customer_id, amount
from sales
where customer_id in (
  select customer_id from customers
  where region = ‘USA’
);
subquery inside where clause
– subquery returns multiple values
– where can use in clause
customer_id | amount
121 . . . . | 200
121 . . . . | 450
122 . . . . | 600
subqueryinside where = < >select customer_id, amount
from sales
where customer_id = (
  select customer_id from customers
  where name = ‘Jake’
);
subquery inside where clause
– subquery returns single values
– where can use comparison operators = > <
customer_id | amount
121 . . . . | 200
121 . . . . | 450
subqueryinside from select scj.customer_id,
scj.name,
scj.amount
from (
  select s.customer_id, c.name,
  c.region, s.amount
  from sales s
  join customers c
  on s.customer_id = c.customer_id
)
as scj
where scj.region = ‘USA’;
subquery from clause
– subquery returns a table result
– alias is required for the select subquery
customer_id | name | amount
121 . . . . | Jake | 200
121 . . . . | Jake | 450
122 . . . . | Kurt | 600
subqueryinside selectselect customer_id, amount, (
  select avg(amount) from sales
) as avg_amt
from sales
subquery inside select clause
– subquery should returns a single value for all rows
customer_id | amount | avg_amt
121 . . . . | 200 .  | 420
121 . . . . | 450 .  | 420
122 . . . . | 600 .  | 420
correlated subqueryinside where select customer_id, amount, month
from sales s1
where s1.amount > (
  select avg(s2.amount)
  from sales s2
  where s1.month = s2.month
);
Correlated Subqueries in general:
– the subquery depends on the outer query for its values
– the subquery is executed repeatedly, once for each row selected by the outer query

correlated subquery inside where clause
customer_id | amount | month
121 . . . . | 450 .  | Jan
122 . . . . | 600 .  | Feb

shows sales where transaction amount is greater than the average of the monthly sales
correlated subqueryinside selectselect customer_id, amount, (
  select c.name
  from customers c
  where c.customer_id = s.customer_id
) as name
from sales s;
correlated subquery inside select clause
– can add a new column from another table like a join
customer_id | name | amount
121 . . . . | Jake | 200
121 . . . . | Jake | 450
122 . . . . | Kurt | 600

Part 14: SQL Joins – Inner Join, Left Join, Right Join, Cross Join, Self Join, Multiple Joins

ClauseTypeQueryMeaningOutput
joininnerselect e.empid, e.emp_name, t.amount
from employee e
join transaction t
on e.empid=t.empid
match 2 tables using join clause on a matching condition between columns of the 2 tables

inner join – if a row is not matched between 2 tables then it is not presented in output

inner join = matching rows in A and B
empid | emp_name | amount | date
123   | John     | 200    | jun
123   | John     | 350    | jul
125   | Kurt     | 700    | jul
joinleftselect e.empid, e.emp_name, t.amount
from employee e
left join transaction t
on e.empid=t.empid
left join – if a row from the left table is not matched to the right table then – left table’s row is still presented in output

if columns from right table are present in the output, they are filled with null

left join = matching rows in A and B + extra rows in A
empid | emp_name | amount | date
123   | John     | 200    | jun
123   | John     | 350    | jul
124   | Hugo     | null   | null
125   | Kurt     | 700    | jul
joinrightselect e.empid, e.emp_name, t.amount
from employee e
right join transaction t
on e.empid=t.empid
right join – if a row from the right table is not matched to the left table then right table’s row is still presented in output

if columns from left table are present in the output, they are filled with null

right join = matching rows in A and B + extra rows in B
empid | emp_name | amount | date
123   | John     | 200    | jun
123   | John     | 350    | jul
125   | Kurt     | 700    | jul
null  | null     | 100    | feb
joincrossselect m.meal, d.drink
from main_course m
cross join drinks d
cross join – every row in left table is matched with every row in right table

all possible combinations of records between 2 tables is recieved

cross join = multiplication of rows in A and B
meal          | drink
spinach saute | sprite
spinach saute | melon juice
potato rice   | sprite
potato rice   | melon juice
joinmultipleselect e.empid, e.emp_name, t.amount, d.name
from employee e
join transaction t
on e.empid=t.empid
join department d
on e.depid=d.depid
can join multiple tables in a single query

each join can be of any type (inner/left/right)
empid | emp_name | amount | dept
123   | John     | 200    | sales
123   | John     | 350    | sales
125   | Kurt     | 700    | finance
joinselfselect e.empid, e.name as emp_name,
e.mgrid, m.name as mgr_name
from employee e
left join employee m
on e.mgrid = m.empid
can join same table with itself based on 2 columns in the same tableempid | emp_name | mgrid | mgr_name
123   | John     | null  | null
124   | Hugo     | 123   | John
125   | Kurt     | 124   | Hugo
126   | Lolo     | 124   | Hugo

Part 15 SQL Wildcards – Wild Card Filters with Where Clause

ClauseTypeQueryMeaningOutput
nulloperationsselect name, length(name) from employee;any operation (+ – / *) or built-in function on null value will return nullname | length
John | 4
null | null
whereequalsselect empid, name from employee
where name =’John’
equals operator =
for string values quotes are required on values
for numbers no quotes are required on values
wherenot equalsselect empid, name from employee
where name <> ‘John’
not equals operator <>
wherelessselect empid, name from employee
where empid < 2
greater than operator >
wheregreaterselect empid, name from employee
where empid > 2
less than operator >
whereless or equal
greater or equal
select empid, name from employee
where empid <= 2
less than or equal to <=
greater than or equal to >=
wherelike string
select empid, name from employee
where name like ‘J%’
% is the wildcard character which will match a string of any length

% = match any string of any length
empid | name
100   | John
256   | Jonny
wherelike string multiple
select empid, name from employee
where name like ‘%ar%’
% wildcard can be used multiple time in same string to form a complex matchempid | name
234   | Larry
256   | Mary
wherelike characterselect empid, name from employee
where name like ‘J___’
_ wildcard character will match any one character

_ = match any one character
empid | name
100   | John
256   | Jimy
wherelike character in rangeselect empid, name from employee
where name like ‘[A-J]%’
[a-z] will match any one character in the specified range

[x-x] = match any one character in given range
empid | name
24    | Amy
27    | Bob
70    | Fanny
100   | John
wherelike character in listselect empid, name from employee
where name like ‘[BFJ]%’
[bfj] will match any one character from the specified list of characters

[xxx] = match any one character from the given list
empid | name
27    | Bob
70    | Fanny
100   | John
wherelike character not from listselect empid, name from employee
where name like ‘[^BFJ]%’
[^bfj] will match any one character which is not in the specified list of characters

[^xxx] = match any one character not in the given list
empid | name
24    | Amy

Part 16: SQL Range Operators – Not, In, Like, Between, And, Or

ClauseTypeQueryMeaningOutput
whereandselect * from employee
where empid>100 and empid<200
and = if both conditions are true, then the row is returned

if any one of the conditions is false, then row is not returned
empid | name
120   | Tony
180   | Elvis
whereorselect * from employee
where (empid<=100 or empid>=200)
or = if (any one / both) conditions are true, then the row is returned

if both conditions are false, then row is not returned
empid | name
70    | Fanny
100   | John
200   | Mony
240   | Lily
wherebetweenselect * from employee
where empid between 100 and 200
between = between 2 numbers. both numbers are inclusive.empid | name
100   | John
120   | Tony
180   | Elvis
200   | Mony
whereinselect * from employee
where empid in (100, 180, 200)
in = if condition value is in the list of values, then row is returnedempid | name
100   | John
180   | Elvis
200   | Mony
whereinselect * from employee
where name in (‘John’, ‘Mony’)
in can be used with numbers and stringempid | name
100   | John
200   | Mony
wherenot conditionselect * from employee
where not name like ‘A%’
not (condition) = all rows that do not satisfy the condition

opposite / reverse of the condition
empid | name
24    | Amy
wherenot equal toselect * from employee
where empid != 100
!= means not equal to condition. This is same as <> operatorempid | name
70    | Fanny
180   | Elvis
wherenot multiple conditionsselect * from employee
where not (empid>100 and empid<200)
not (multiple conditions) = all rows that do not satisfy the complete conditionempid | name
70    | Fanny
100   | John
200   | Mony
240   | Lily
wherenot likeselect * from employee
where name not like ‘A%’
not like = all rows for which the like condition is not satidfiedempid | name
70    | Fanny
100   | John
wherenot betweenselect * from employee
where empid not between 100 and 200
not between = all rows that are not between 2 inclusive numbers

opposite / reverse of between
empid | name
70    | Fanny
240   | Lily
wherenot inselect * from employee
where empid not in (100, 180, 200)
not in = all rows for which the in condition is not satisfied

opposite / reverse of in
empid | name
70    | Fanny
240   | Lily

Part 17: SQL Window and Range – Rows, Range, Window Definition, Modification, Referencing, Select Into

ClauseTypeQueryMeaningOutput
selectcolumn headingsselect product_name,
no_discount_sale
= (qty * unit_price),
discount
= (qty * unit_price * unit_discount)
from orders
Another way to display column without the ‘as’ keyword
selectintoif object_id(‘#bicycles’,’U’) is not null
  drop table #bicycles;
go

select *
into #bicycles
from hc.dbo.product
where product_type=’BK’;
creates a temporary table #bicycles in tempdb
selectintoif object_id(‘hc.dbo.bicycles’,’U’) is not null
  drop table hc.dbo.bicycles;
go

select *
into hc.dbo.bicycles
from hc.dbo.products;
creates a permanent table dbo.bicycles in hc database
generalpartition by
order by
1. partition by can have multiple columns to create a smaller partition

2. order by can have multiple columns with different ordering like asc and desc to create a complex sorting

3. more than one window function can be used in a single query with a single FROM clause. The OVER clause for each function can differ in partitioning and ordering.

4. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
windowwindow definitionselect order_id as oid,
product_id as pid, qty,
sum(qty)
over my_win as total,
avg(qty)
over my_win as avg,
count(qty)
over my_win as count
from orders
where order_year = ‘2023’
window my_win as (partition by order_id);
1. Named window determines the partitioning and ordering of a rowset before the window function which uses the window in OVER clause is applied.

2. More than one named window can be defined in the WINDOW clause.
oid | pid | qty | total | avg | count
1   | 5   | 3   | 15    | 5   | 3
1   | 7   | 10  | 15    | 5   | 3
1   | 12  | 2   | 15    | 5   | 3
2   | 5   | 7   | 22    | 11  | 2
2   | 6   | 15  | 22    | 11  | 2

windowwindow modificationselect order_id as oid,
product_id as pid, qty,
sum(qty)
over my_win (
  partition by order_id
) as avg,
count(qty)
over my_win (
  
partition by order_id
  rows between
  unbounded preceding and 1 following
) as count
from orders
where order_year = ‘2023’
window
my_win as (order by order_id, product_id);
1. Can define a common specification in a window and use it to define additional specifications in the OVER clause.

2. However, the properties specified in WINDOW clause cannot be redefined in the OVER clause.
oid | pid | qty | total | count
1   | 5   | 3   | 3     | 2
1   | 7   | 10  | 13    | 3
1   | 12  | 2   | 15    | 2
2   | 5   | 7   | 7     | 2
2   | 6   | 15  | 22    | 2

windowwindow referencingselect order_id as oid,
product_id as pid, qty,
sum(qty)
over win1 as avg,
count(qty)
over win2 as count
from orders
where order_year = ‘2023’
window
win1 as (order by order_id, product_id),
win2 as (win1
partition by order_id);
1. When a query uses multiple windows, one named window can reference another named window using the window_name.

2. Cyclic references of windows are not allowed.
oid | pid | qty | total | count
1   | 5   | 3   | 3     | 1
1   | 7   | 10  | 13    | 2
1   | 12  | 2   | 15    | 3
2   | 5   | 7   | 22    | 1
2   | 6   | 15  | 37    | 2

rowsbetweenselect person_id as pid,
pre = count(*) over(order by person_id
  rows between
  unbounded preceding
and current row),
center = count(*) over(order by person_id
  rows between
  2
preceding and 2 following),
post = count(*) over(order by person_id
  rows between
  current row
and unbounded following)
from person
order by person_id asc
1. Further limits the rows within the partition
2. Aggregation function is applied only at the
3. specified start and end points within the partition.

agg_func over (order by order_column rows between x and y)

x can be
n preceding
  – from 2 preceding rows
unbounded preceding
  – from all preceding rows
current row
  – from current row

y can be
n following
  
– till 3 following rows
unbounded following
  
– till all following rows
current row
  – till current row
pid | pre | center | post
3   | 1   | 3      | 156
3   | 2   | 4      | 155
8   | 3   | 5      | 154
9   | 4   | 5      | 153
10  | 5   | 5      | 152

270 | 153 | 5      | 4
273 | 154 | 5      | 3
277 | 155 | 4      | 2
280 | 156 | 3      | 1
rangebetweenselect book_id as bid, amount as amt,
sale_year as [year],
sum(amount)
over win as [sum],
sum(amount)
over win_rows as srow,
sum(amount)
over win_range as srnge
from hc.dbo.orders
window
win as (
  
partition by sale_year
  
order by book_id),
win_rows as (
  win
rows between
  
unbounded preceding and current row),
win_range as (
  win
range between
  
unbounded preceding and current row);
1. RANGE cannot be used with n PRECEDING or n FOLLOWING.

2. The ROWS clause limits the rows within a partition by considering a fixed number of rows before or after the current row.

3. The RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row.

ie.

1. if the ordering is unique (there are no ties), then there is no difference in output of Rows and Range

2. if there are ties in the ordering, then Range will treat the tied rows as a single row and give the same value for both tied rows
bid| amt| year | sum | srow | srnge
1    10   2022   20    10     20
1    10   2022   20    20     20
2    12   2022   44    32     44
2    12   2022   44    44     44

1    10   2023   30    10     30
1    10   2023   30    20     30
1    10   2023   30    30     30
2    12   2023   54    42     54
2    12   2023   54    54     54

rows
range
precedingrows unbounded preceding =
rows between
unbounded preceding and current row

rows n preceding =
rows between
n preceding and current row

range unbounded preceding =
range between
unbounded preceding and current row

range n preceding =
range between
n preceding and current row
1. The between keyword can be skipped and only the start of the rows/range can be provided.

2. The range/rows will end at the current row

[Download PDF – SQL Queries]

3 comments on SQL Queries – All in 1

Leave a Reply

Your email address will not be published. Required fields are marked *