1 Clause Type Query Meaning Output

Select all columns select * from actor; display all columns from table actor
select first_name,last_name display only first_name and last_name columns from
Select specific columns from actor; table actor
first_name | last_name
select first_name,last_name Zach . . . | Schnider
from actor sort the selected columns by first_name in Amy . . . .| Farah
Order By descending ordering order by first_name desc; descending order Z-A
first_name | last_name
select first_name,last_name Amy . . . .| Farah
ascending ordering from actor sort the selected columns by last_name in ascending Zach . . . | Schnider
Order By default order by first_name asc; order A-Z
customer_id | amount
1 . . . . . | 10
select * 1 . . . . . | 5
from payment first sorts result by customer_id in ascending - if 2 . . . . . | 12
multiple column order by customer_id asc, multiple customer_id rows are present then sorts 2 . . . . . | 8
Order By ordering amount desc; them by amount in descending
department |
select finance
distinct department distinct will give unique values in a column - will security
Distinct single column from employee; show all distinct rating values in the rating column telecom
department | region
finance . | USA
finance . | AUS
select unique values of columns in every row security . | EU
distinct department, region - distinct will apply on all columns specified telecom . | USA
from employee - will return unique combinations of specified telecom . | EU
Distinct multiple column order by department; columns
select department |
distinct department finance
from employee returns only top x rows - used with order by on date security
Limit limit 2; to return latest x results
count(single_column or *) count
select Counts the number of rows in the table when * 6
count(*) Counts number of non-null value in column
Count with * from customer; returns number of customer total 6 records in customer table
count
select 5
count(first_name) Number of customers in customer table - count will
Count with column from customer; not count null values present in the column one first_name entry has null value
count
4
select
count(distinct first_name) one first_name is null and two
Count with distinct from customer; Counts the number of distinct values in the column customers have same fisrt_name

[Link]/sql 1
 2 Clause Type Query Meaning Output
select sum(amount) as total adds all the values in amount column from payment total
Sum sum of column values from payment table and returns only one added up value 1526
average of column select avg(amount) as avg_amnt averages values in amount column and return one avg_amnt
Avg values from payment average value 34.56789
round(float,n)
input1 = floating select round(
number avg(amount), rounds the floating numer (average in this case) to
input2 = number of 2 n decimal places
digits to return after ) as avg_amnt avg_amnt
Round decimal from payment; example 4.923423489 to 4.92 34.56
minimum value in a select min(amount) as minimum_sale returns a single minimum value from the column minimum_sale
Min column from payment; specified 1.99
maximum value in a select max(amount) as maximum_sale returns a single maximum value from the column maximum_sale
Max column from payment; specified 25.99
select
sum(amount) as sum_amnt, returns 3 columns - sum of all values in amount
avg(amount) as avg_amnt, column, average of all values in amount column, and
multiple aggregation count(amount) as cnt_amnt count of records in amount column from paymnet sum_amnt | avg_amnt | cnt_amnt
Mix functions from payment table 1526 . . | 34.5678 .| 5
select col1, col2, agg_func(col3) from table
group by col1, col2
basic - group rows on a can select columns in group by clause and any other customer_id | sum(amount)
specific column - then columns on which aggregate functions are applied 3 . . . . . | 100
apply aggregation select customer_id, sum(amount) 4 . . . . . | 200
functions over the from payment returns all customer ids and the sum of amount paid 5 . . . . . | 260
Group by group group by customer_id; by each customer
select customer_id, sum(amount) customer_id | sum(amount)
group by with where from payment first filter data having customer_id greater than 3 4 . . . . . | 200
clause - filter data where customer_id>3 - then perform grouping on remaining customer ids 5 . . . . . | 260
Group by before grouping group by customer_id; and the sum of amount paid by each customer
select customer_id, sum(amount) customer_id | sum(amount)
from payment returns all customer ids and the sum of amount paid 5 . . . . . | 260
group by with order group by customer_id by each customer - orders the result by the maximum 4 . . . . . | 200
Group by clause order by sum(amount) desc; amount paid
select staff_id, customer_id, returns unique combinations of staff_id and staff_id | customer_id | sum | count
sum(amount), count(*) customer_id with the sum of amount showing how much 1 . . . .| 5 . . . . . | 280 | 12
from payment a particular staff has sold to a particular member. 2 . . . .| 5 . . . . . | 260 | 10
group by multiple group by staff_id, customer_id Count(*) shows the number of payments made by a 2 . . . .| 6 . . . . . | 150 | 8
Group by columns order by count(*) desc customer to a particular staff.
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
filter data after select customer_id, sum(amount) the table staff_id | customer_id | sum | count
groupings - applied from payment 1 . . . .| 5 . . . . . .| 280 | 12
over the aggregation group by customer_id returns customers who have paid a total amount 2 . . . .| 5 . . . . . .| 260 | 10
Having function having sum(amount)>200 greater than 200 in all payment records

3 Clause Type Query Meaning Output

[Link]/sql 2
 email_upper
K@[Link]
select upper(email) as email_upper from upper(column/value) LIN@[Link]
upper in column people tranforms column data inot upper case
email_lower
k@[Link]
select lower(email) as email_lower from lower(column/value) lin@[Link]
lower in column people tranforms column data inot lower case
len
length(column/value) 8
calculates length of each value in the specified 9
length in column select length(email) as len from people column
upper email_lower
lower select lower(email) as email_lower from can combine functions in same or different columns kronos-the-hero@[Link]
length in where clause people where length(email) > 20 of select clause
left(column,n) function will return the first n left_email | email
select left(email,3) as left_email, email characters from the left side of every value in the k@y . . . .| k@[Link]
left in column from people column lin . . . .| lin@[Link]
right(column,n) function will return the last n right_email | email
select right(email,3) as right_email, email characters from the right side of every value in the com . . . . | k@[Link]
right in column from people column com . . . . | lin@[Link]
right(column,4) on email will give .com on all email fourth_last_char | email
left select left(right(email,4),1) values. left on that value with index of 1 will give . . . . . . . . .| k@[Link]
right nested as fourth_last_char, email from people first character which is dot (.) . . . . . . . . .| lin@[Link]
select first_name+'.'+last_name display_name | first_name | last_name
as display_name, || or + operator will combine the values in columns [Link] . . . | lin . . . .| do
concat basic first_name, last_name from people specified [Link] . | nancy . . .| chen
select position('@' in email),
left(
email,
position('@' in email)-1 position(string/column-to-find in string/column) position | email_left | email
position / ) as email_left, position(string1 in string2) returns the position of 2 . . . .| k . . . . .| k@[Link]
charindex of a fixed character email from people string1 inside string2 starting from 1 4 . . . .| lin . . . .| lin@[Link]
select position(lname in email),
left(
email,
position(lname in email)-2 position(column1 in column2) returns the position of pos | fname_left | lname | email
position / ) as fname_left, string in column1 inside string present in column2. 7 . | nancy . . .| chen .| [Link]@[Link]
charindex of a value in column lname, email from people Index starts from 1 for each row. 5 . | lin . . . .| do . .| [Link]@[Link]
select substring(
email
from position('.' in email)+1
for length(lname)
) as last_name, email from people;

select substring(
email substring(col/string from position [for length])
from position('.' in email)+1
for position('@' in email)- finds substing in the column or string - starting last_name | email
position('.' in email)-1 from the position specified - and having the length chen . . .| [Link]@[Link]
substring basic ) as last_name, email from people specified

4 Clause Type Query Meaning Output

[Link]/sql 3
 amount | case
select amount, 1 . . .| low
case case 2 . . .| medium
when amount<2 then 'low' when condition1 then result1 3 . . .| medium
when amount<5 then 'medium' when condition2 then result2 7 . . .| high
else 'high' else result 5 . . .| high
Case basic end from payment end
select count(*) as pay_count,
case
when amount<2 then 'low' pay_count | pay_size
when amount<5 then 'medium' groups the rows on the case column (pay_size) and 420 . . . | low
else 'high' then run aggregate function count(*) over the groups 750 . . . | medium
end as pay_size to return the count column (pay_count) of each group 260 . . . | high
Case with group by and count from payment group by pay_size (pay_size)
select sum(
case
when rating in ('PG','G') then 1
else 0
end returns the count of number of film records having a count_pg_g
Case with sum ) as count_pg_g from film specific case condition (PG or G rating) 257
select sum(
case
when rating='G' then 1 else 0
end
) as g_count,
sum(
case
when rating='PG' then 1 else 0 g_count | pg_count
end returns count of films having a specific rating in a 20 . . . | 34
Case with sum - multiple ) as pg_count from film different column
select jobtitle,
count(
case
when gender='M' then 1 else null
end
) as male_cnt,
count(
case job_title .| male_cnt | female_cnt
when gender='F' then 1 else null account . .| 2 . . . .| 1
end clerk . . .| 5 . . . .| 2
) as female_cnt count() will not count nulls tester . . | 1 . . . .| 7
Case with count from employee group by jobtitle
case can be used anywhere a value is required-
1. in where clause condition
anywhere a value is 2. in set clause of update query
Case required 3. in on clause in join conditions
operations like + - or concat will retunr null if
select fname, lname, any of the input is null
coalesce(fname+'.'+lname, fname)
as full_name, coalesce(value1, value2, ...) fname | lname . | full_name | dname
fname+lname as dname will return the first value passed to it which is Xin . | Po . . .| [Link] . .| XinPo
Coalesce basic from people not null Neil .| null . .| Neil . . .| null

[Link]/sql 4
 select coalesce(
cast(end_date - start_date as varchar),
'running' cast (value/column as datatype) duration | start_date | end_date
) as duration, cast changes the datatype of value 10 ........ | 15:00 .......| 25:00
Cast basic start_date, end_date from transactions running. | 30:00 .......| null
select amount, cast( replace(column, old_string, new_string) amount | amnt_no
replace(amount,',','') as int replaces old_string present in column values with 5,200 .. | 5200
) as amnt_no new_string 2,400 .. | 2400
Replace basic from sales

6 Clause Type Query Meaning Output

Window 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
Any All Window Functions where on the window function value
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

select rowset is partitioned. emp_id | sal_amount | sal_date | sum
emp_id, sal_amount, sal_date, 121 . .| 2500 . . . | Jun-2023 | 5500
sum(sal_amount) over ( Similar to group by clause but - group by will give 121 . .| 3000 . . . | Aug-2023 | 5500
partition by employee_id a single row for every group. Partition will return 122 . .| 3500 . . . | Aug-2023 | 4500
) as sum all rows and give the aggreagted value for every row 122 . .| 1000 . . . | Sep-2023 | 4500
Over with Partition By from salary - depending on the partition
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 emp_id | sal_amount | sal_date | sum
select emp_id, sal_amount, sal_date, separately but result is cumulative based on the 121 . .| 2500 . . . | Jun-2023 | 2500
sum(sal_amount) over ( order_column. 121 . .| 3000 . . . | Aug-2023 | 5500
order by employee_id 122 . .| 3500 . . . | Aug-2023 | 9000
) as sum Gives a running total/aggregation on each row 122 . .| 1000 . . . | Sep-2023 | 10000
Over with Order By from salary depending on the order column

[Link]/sql 5
 Aggregation(agg_column) over (partition by
part_column order by part_column)
select emp_id, sal_amount, sal_date, emp_id | sal_amount | sal_date | sum
sum(sal_amount) over ( Gives a running total/aggregation on each row 121 . .| 2500 . . . | Jun-2023 | 2500
partition by employee_id depending on the order by column 121 . .| 3000 . . . | Aug-2023 | 5500
order by employee_id 122 . .| 3500 . . . | Aug-2023 | 3500
with Partition By and ) as sum The running aggregation is calculated for every 122 . .| 1000 . . . | Sep-2023 | 4500
Over Order By from salary partition separately

7 Clause Type Query Meaning Output

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

with Partition By and rank() over in case of ties, rank function does not return
Rank Order By (partition by ... order by ...) consecutive integers (for example 1, 2, 2, 4, 5)
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() over
(partition by ... order by ...) row_number numbers all rows sequentially (for
with Partition By and example 1, 2, 3, 4, 5).
Row Number Order By query usage similar to dense rank
with my_cte as (
select emp_id, sal_amount, sal_date,
row_number() over emp_id | sal_amount | sal_date | rnum
(order by sal_amount desc) 123 . .| 7500 . . . | Oct-2023 | 1
as rnum from salary 122 . .| 6500 . . . | Aug-2023 | 1
) 121 . .| 4000 . . . | Aug-2023 | 2
with cte - to return a select * from my_cte 121 . .| 3500 . . . | Aug-2023 | 2
Row Number subset of rows where rnum between 1 and 2

[Link]/sql 6
 Ntile(n) distributes the rows in an ordered
partition into a specified n number of groups.
emp_id | sal_amount | sal_date | ntile
The groups are numbered, starting at one. For each 123 . .| 7500 . . . | Oct-2023 | 1
row, ntile returns the number of the group to which 122 . .| 6500 . . . | Aug-2023 | 1
the row belongs. 121 . .| 4000 . . . | Aug-2023 | 2
select emp_id, sal_amount, sal_date, 121 . .| 3500 . . . | Aug-2023 | 2
ntile(4) over ( for example ntile(4) will divide every partition 121 . .| 3000 . . . | Aug-2023 | 3
order by sal_amount desc into 4 groups. Each row in the partition can have a 121 . .| 2500 . . . | Jun-2023 | 3
) as ntile group number (1, 2, 3, 4) based on the order by 122 . .| 1000 . . . | Sep-2023 | 4
Ntile with Order By from salary column
emp_id | sal_amount | sal_date | ntile
121 . .| 4000 . . . | Aug-2023 | 1
121 . .| 3500 . . . | Aug-2023 | 2
121 . .| 3000 . . . | Aug-2023 | 3
select emp_id, sal_amount, sal_date, 121 . .| 2500 . . . | Jun-2023 | 4
ntile(4) over ( 122 . .| 9000 . . . | Sep-2023 | 1
partition by emp_id 122 . .| 6500 . . . | Aug-2023 | 2
order by sal_amount desc 122 . .| 3500 . . . | Aug-2023 | 3
with Partition By and ) as ntile 122 . .| 2500 . . . | Aug-2023 | 4
Ntile Order By from salary
with my_cte as (
select emp_id, sal_amount, sal_date,
ntile(4) over (
order by sal_amount desc emp_id | sal_amount | sal_date | ntile
) as qtile 123 . .| 7500 . . . | Oct-2023 | 1
from salary 122 . .| 6500 . . . | Aug-2023 | 1
with cte - to return a )
Ntile subset of rows select * from my_cte where qtile = 1

8 Clause Type Query Meaning Output

insert into accounts (id, account_name) insert a record
Insert with value values (10,'tony-stark') - with fixed hardcoded values
insert into accounts (id, account_name)
values (10,'tony-stark'),(11,'peter- insert multiple records
Insert with multiple values parker') - with different fixed values
insert multiple/bulk records
insert into accounts (id, account_name) - by using data from another table
select account_id, name from - by forming a result with joins on different
Insert with select third_party_accounts join.... tables
insert into accounts (id, account_name) insert multiple/bulk records
Insert with procedure execute [Link] - by using data returned from a stored procedure
with cte as (
select account_id,name from ...join...) insert multiple/bulk records
insert into accounts - by using data from a cte using with clause
Insert with cte select account_id,name from cte - cte can be created with multiple tables, joins

9 Clause Type Query Meaning Output

update accounts set band = 'low' update a single record
Update basic where id=12 - using fixed value on a where condition

[Link]/sql 7
 update a set [Link] = 'low' updates single / multiple records
from accounts a - using fixed value on a where condition
Update with from where [Link]=12 - from clause to alias the table being updated
updates single / multiple records
- using value from another table
- from clause to alias the table being updated
update a set [Link] = s.salary_level - join clause to match the records and get
from accounts a corresponging update value for each row
join salary s on s.account_id=[Link] - can only update data from existing columns in the
Update with from and joins where [Link] > 10000 joined table
updates single / multiple records
- using value from another table
- from clause to alias the table being updated
update a set band = ( - subquery to match the records and get
select derived_col from salary s corresponging update value for each row
where s.account_id=[Link] - can update a column based on dervied value from
Update with subquery ) from accounts a subquery
updates single / multiple records
- using value calculated using case clause
update a set [Link] = ( - from clause to alias the table being updated
case - case clause to get the calculated value
when [Link] > 10000 then 'high' - can update a column based on derived value from
else 'low' case clause
end - case clause can internally have subqueries
Update with from and case ) from accounts a depending on complex requirements
with my_cte as (select band, derived_band updates single / multiple records
from accounts join salary on ...) - using value from a cte
- can update data from existing/derived columns in
Update with cte update my_cte set band = derived_band the cte
with my_cte as (select account_id, updates single / multiple records
derived_band from accounts join salary on - using value from a cte
...) - from clause to alias the table being updated
- join clause to match the records from cte and get
update a set [Link] = c.derived_band corresponging update value for each row
from accounts a - can update a column based on a derived column in
Update with cte join join my_cte c on c.account_id=[Link] cte

10 Clause Type Query Meaning Output

with sales_cte (person_id, order_count)
as ( With clause defines a temporary named result set,
select person_id, COUNT(*) known as CTE. person_id | order_count
from sales group by person_id - CTE result is derived from a simple/complex query 121 | 74
) - CTE can be used only for the next single sql 122 | 33
with single cte select * from sales_cte; statement. 124 | 14

[Link]/sql 8
 with 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 Multiple CTEs separated by comma. person_id | person_name | avg_orders
join person_cte - can join different cte declared for the next 121 | Jake | 74
on sales_cte.person_id = person_cte. statement 122 | Kurt | 33
with multiple cte person_id; 124 | Aamy | 14
The recursive CTE syntax -
1. must contain at least two CTE query definitions
2. an anchor query and a recursive query. anchor query evaluation =
3. multiple anchor and recursive queries can be empid | emp_name | mgrid | level
defined 123 | John | null | 0
4. all anchor queries must be put before the first
recursive queries. anchor + recursive query =
5. anchor query does not join the CTE itself. empid | emp_name | mgrid | level
6. anchor and recursive queries must be combined by 123 | John | null | 0
union all 124 | Kurt | 123 | 1

Internally how it works - anchor + rec query + rec query=

with emp_cte as ( 1. init - the cte is empty and an internal copy of empid | emp_name | mgrid | level
select empid, emp_name, table (employee) is created with all rows 123 | John | null | 0
mgrid, 0 as level 2. first anchor query is evaluated 124 | Kurt | 123 | 1
from employee 3. this fills up cte with a row 125 | Hugo | 124 | 2
where mgrid is null 4. same row is removed from the table internal copy 126 | Lobo | 124 | 2
5. then recursive query is evaluated by joining cte
union all filled in previous step with the updated internal anchor + rec + rec + rec =
copy of table empid | emp_name | mgrid | level
select [Link], e.emp_name, [Link]+1 6. result of recursive query is added to fill up cte 123 | John | null | 0
from employee e further 124 | Kurt | 123 | 1
join emp_cte c 7. again rows returned from recursive query are 125 | Hugo | 124 | 2
on [Link] = [Link] removed from internal copy of table 126 | Lobo | 124 | 2
) 8. then recursive query is evaluated again and again 127 | Aamy | 125 | 3
till no rows are present in the internal copy of 128 | Pike | 126 | 3
with recursive cte select * from emp_cte; table 129 | Masy | 126 | 3

[Link]/sql 9
 - 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
merge prod_people
as tgt - when not matched by source clause typically used
using (SELECT person_id, name from staging to fire delete query on target table since record is
.... join ...) not present in source table but present in target
as src (person_id, name)
on (tgt.person_id= src.person_id) - a maximum of 2 when not matched by source clauses
when matched and tgt.to_delete = 1 then can be present. If 2 are present then one should
delete have an and condition
when matched then Merge is basically used to sync a
update set name = [Link] - when not matched by target clause typically used target table with a source table or
when not matched by target then to fire insert query on target table since record is derived result
insert (person_id, name) present in target table but not present in source
values (src.person_id, [Link]) can use insert, update and delete
when not matched by source then - only 1 when not matched by target clauses can be queries together
merge merge delete present

11 Clause Type Query Meaning Output

create function addfunc function benefits
(@param1 int, @param2 int) - modular code .. can be used in select
returns int - can be used in where clause to allow complex
as begin conditions
return @param1 + @param2 - can be faster than join and group by
end
function scalar scalar function can return only one value back
select jan_sale, feb_sale, [Link]
(jan_sale, feb_sale) as jf_sale from sales;
functions can be used in select statements and
declare @jf_sale int execute command
@jf_sale = addfunc 120 200 jan_sale | feb_sale | jf_sale
function scalar function call select @jf_sale stored procedure can only be used in execute command 120 . . .| 200 . . .| 320
create 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
function scalar end
select customer_id,
tranCount(customer_id) as tran_count customer_id | tran_count
from transactions; 201 . . . . | 12
function scalar function call 202 . . . . | 18
function drop drop function tranList;
select * from [Link] where name = name . . . | type | type_desc
function list 'tranCount' lists all database objects including functions tranCount .| FN . | SQL_SCALAR_FUNCTION

[Link]/sql 10
 if exists (select * from [Link] where
name = 'tranCount')
drop function tranCount
go

create function tranCount (....) returns

int
as begin
....
return ....
function drop + create end

12 Clause Type Query Meaning Output

create function tranList
(@customer_id int)
returns table Inline table functions can return a table result
as return ( back
select * from transaction
where customer_id = @customer_id can have only a single statement to return table
function inline table function )
select * from [Link](210); customer_id | amount | tran_date
210 . . . . | 2300 . | ------
select * from customers c where exists 210 . . . . | 1500 . | ------
inline table function (select * from [Link](c.customer_id)) Since return is a table, the function can be called 210 . . . . | 700 . .| ------
function call in from clause of the select query
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) Multi-statement table functions can have multiple
select customer_id, amount, tran_date statements
from transaction
where customer_id = @customer_id - can have if conditions to determine logic
multi statement table return
function function end -
select * from [Link](210);
customer_id | amount | tran_date
select * from customers c 210 . . . . | 2300 . | ------
where exists ( 210 . . . . | 1500 . | ------
multi statement table select * from [Link](c.customer_id) multi-statement table functions can be called in the 210 . . . . | 700 . .| ------
function function call ) from clause of the select query
select c.customer_id, [Link], (
select count(*)
table function call from [Link](c.customer_id) customer_id | name | tran_count
- correlated subquery ) as tran_count 210 . . . . | jake | 3
function - in select from customer c 211 . . . . | kurt | 7

[Link]/sql 11
 select c.customer_id, [Link]
from customer c
where (
select count(*)
table function call from [Link](c.customer_id) > 5
- correlated subquery ) customer_id | name | tran_count
function - in where 211 . . . . | kurt | 7
used to inner join the result of table function with
select c.customer_id, [Link], [Link] other tables customer_id | name | amount
from customer c 210 . . . . | jake | 2300
cross apply the on condition is not required as it is implictly 210 . . . . | jake | 1500
apply cross apply [Link](c.customer_id) tl dependent on the input parameter of the function 211 . . . . | kurt | 7500
customer_id | name | amount
select c.customer_id, [Link], [Link] 209 . . . . | tate | null
from customer c 210 . . . . | jake | 2300
outer apply used to left outer join the result of table function 210 . . . . | jake | 1500
apply outer apply [Link](c.customer_id) tl with other tables 211 . . . . | kurt | 7500
customer_id | amount
210 . . . . | 2300; 1500; 700
211 . . . . | 7500; 300
every string_split function call will return a table
select customer_id, amount having a single column with multiple rows having customer_id | amount
from customer_sales; splitted single values 210 . . . . | 2300
210 . . . . | 1500
select customer_id, [Link] cross apply will join the results of customer table 210 . . . . | 700
cross apply - built in from customer_sales cs and the split return table on the input column - 211 . . . . | 7500
apply table function split cross apply string_split([Link],';') ss; amount 211 . . . . | 300

13 Clause Type Query Meaning Output

select customer_id, amount
from sales
where customer_id in ( customer_id | amount
select customer_id from customers subquery inside where clause 121 . . . . | 200
where region = 'USA' - subquery returns multiple values 121 . . . . | 450
subquery inside where in ); - where can use in clause 122 . . . . | 600
select customer_id, amount
from sales
where customer_id = ( customer_id | amount
select customer_id from customers subquery inside where clause 121 . . . . | 200
where name = 'Jake' - subquery returns single values 121 . . . . | 450
subquery inside where = < > ); - where can use comparison operators = > <
select scj.customer_id,
[Link],
[Link]
from (
select s.customer_id, [Link],
[Link], [Link]
from sales s customer_id | name | amount
join customers c 121 . . . . | Jake | 200
on s.customer_id = c.customer_id subquery from clause 121 . . . . | Jake | 450
) as scj - subquery returns a table result 122 . . . . | Kurt | 600
subquery inside from where [Link] = 'USA'; - alias is required for the select subquery

[Link]/sql 12
 select customer_id, amount, ( customer_id | amount | avg_amt
select avg(amount) from sales subquery inside select clause 121 . . . . | 200 . | 420
) as avg_amt - subquery should returns a single value for all 121 . . . . | 450 . | 420
subquery inside select from sales rows 122 . . . . | 600 . | 420
Correlated Subqueries in general:
- the subquery depends on the outer query for its
select customer_id, amount, dept values customer_id | amount | dept
from sales s1 - the subquery is executed repeatedly, once for 121 . . . . | 450 . | Accounts
where [Link] > ( each row selected by the outer query 122 . . . . | 600 . | Marketing
select avg([Link])
from sales s2 correlated subquery inside where clause to filter shows sales where transaction amount is
correlated where [Link]= [Link] results on each group (like department) greater than the average of every
subquery inside where ); department
select customer_id, amount, dept
from sales s1 customer_id | amount | dept
where [Link] = ( 121 . . . . | 450 . | Accounts
select max([Link]) 122 . . . . | 600 . | Marketing
from sales s2
correlated where [Link]= [Link] correlated subquery inside where clause to find shows sales where transaction amount is
subquery inside where ); maximum on each group (like department) highest transaction in every department
select customer_id, amount, (
select [Link] customer_id | name | amount
from customers c 121 . . . . | Jake | 200
where c.customer_id = s.customer_id correlated subquery inside select clause 121 . . . . | Jake | 450
correlated ) as name - can add a new column from another table like a 122 . . . . | Kurt | 600
subquery inside select from sales s; join

select * from table1 go statement is the separator for batches

select * from table2
go by default all statements written together are
batch select * from table3 executed in a batch

where --
ALL
ANY
IN
EXISTS

5 Clause Type Query Meaning Output

[Link]/sql 13
 old_people
1. combines 2 tables or select results Jake | Jacob
2. both results should have same number of columns Peter | Parker
3. need compatible data types in corresponding
columns (string and numbers are not compatible) new_people
4. final columns will use the biggest compatible Jake | Jacob
data type (tinyint and bigint column will create Kurt | Konner
bigint column)
4. gives distinct records (removes duplicate union
records) - takes more time fname | lname
5. column names are taken from the first set of Jake | Jacob
select fname,lname from old_people results Kurt | Konner
union Peter | Parker
union combine with distinct select fname,lname from new_people Union = Distinct (A + B)
fname | lname
Jake | Jacob
Jake | Jacob
Kurt | Konner
Peter | Parker
select fname,lname from old_people same as union but will not remove duplicates
union all both tables have entry for Jake
union all combine no distinct select fname,lname from new_people Union All = (A + B)
1. all the rows in the first table except rows in
the second table fname | lname
2. all the rows in the first table that do not match Peter | Parker
select fname,lname from old_people rows in the second table
except both tables have entry for Jake
except combine with minus select fname,lname from new_people Except = (A - B)
fname | lname
1. all the rows in the first table that are also Jake | Jacob
select fname,lname from old_people present in the second table
intersect both tables have entry for Jake
intersect combine only common select fname,lname from new_people Intersect = Common Rows in A and B
fname | lname
union select fname,lname from old_people 1. order by clause cannot come before union / except Jake | Jacob
union all union / intersect clauses Kurt | Konner
except select fname,lname from new_people 2. order by clause can only come after the result of Peter | Parker
intersect with order by order by fname asc union / except / intersect
date(timestamp_value)
extract date in a format from the timestamp column

example if payment_date is a big timestamp in payment_date | payment_time

select date(payment_time) as payment_date, milliseconds, then date(payment_date) will give 2023-02-24 . | 2023-02-24:05:25:36:66
Date payment_time from payment; simple timestamp like 2023-02-24

14 Clause Type Query Meaning Output

match 2 tables using join clause on a matching
condition between columns of the 2 tables
empid | emp_name | amount | date
select [Link], e.emp_name, [Link] inner join - if a row is not matched between 2 123 | John | 200 | jun
from employee e tables then it is not presented in output 123 | John | 350 | jul
join transaction t 125 | Kurt | 700 | jul
join inner on [Link]=[Link] inner join = matching rows in A and B

[Link]/sql 14
 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
empid | emp_name | amount | date
if columns from right table are present in the 123 | John | 200 | jun
select [Link], e.emp_name, [Link] output, they are filled with null 123 | John | 350 | jul
from employee e 124 | Hugo | null | null
left join transaction t left join = matching rows in A and B + extra rows in 125 | Kurt | 700 | jul
join left on [Link]=[Link] A
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 empid | emp_name | amount | date
select [Link], e.emp_name, [Link] output, they are filled with null 123 | John | 200 | jun
from employee e 123 | John | 350 | jul
right join transaction t right join = matching rows in A and B + extra rows 125 | Kurt | 700 | jul
join right on [Link]=[Link] in B null | null | 100 | feb
cross join - every row in left table is matched with
every row in right table
meal | drink
all possible combinations of records between 2 spinach saute | sprite
select [Link], [Link] tables is recieved spinach saute | melon juice
from main_course m potato rice | sprite
join cross cross join drinks d cross join = multiplication of rows in A and B potato rice | melon juice
select [Link], e.emp_name, [Link], d.
name
from employee e empid | emp_name | amount | dept
join transaction t 123 | John | 200 | sales
on [Link]=[Link] can join multiple tables in a single query 123 | John | 350 | sales
join department d 125 | Kurt | 700 | finance
join multiple on [Link]=[Link] each join can be of any type (inner/left/right)
empid | emp_name | mgrid | mgr_name
select [Link], [Link] as emp_name, 123 | John | null | null
[Link], [Link] as mgr_name 124 | Hugo | 123 | John
from employee e 125 | Kurt | 124 | Hugo
left join employee m can join same table with itself based on 2 columns 126 | Lolo | 124 | Hugo
join self on [Link] = [Link] in the same table

[Link]/sql 15