[Download PDF – SQL Queries]

–Section–	—SQL Queries–
Part 1: Basic Selection Queries	Select, Order By, Distinct, Limit, Count
Part 2: Aggregate Functions SQL	Sum, Avg, Min, Max, Group By, Having
Part 3: String Functions SQL	Upper, Lower, Left, Right, Length, Position, Substring, Concat
Part 4: Conditional Queries	Case, Coalesce, Cast, Replace
Part 5: Combining Data Queries	Union, Union All, Except, Intersect, Date
Part 6: Window Functions	Over, Partition BY, Order By
Part 7: Ranking Functions	Rank, Dense Rank, Row Number, NTile
Part 8: Insert Queries	Insert data using fix data, select query, Stored Proc, CTE
Part 9: Update Queries	Update data using from, joins, subquery, CTE, conditions
Part 10: Common Table Expression	Single CTE, Multiple CTE, Recursive CTE, Merge
Part 11: User Defined Functions	Scalar Functions, Drop, List, Create, Call UDFs
Part 12: Table Functions	Inline and Multi Statement Table Functions, Cross Apply, Outer Apply
Part 13: SQL SubQuery	SubQuery in where, from, select and Correlated Subquery
Part 14: SQL Joins	Inner Join, Left Join, Right Join, Cross Join, Self Join, Multiple Joins
Part 15: SQL Wildcards	Wild Card Filters with Where Clause
Part 16: SQL Range Operators	Not, In, Like, Between, And, Or
Part 17: SQL Windows and Range	Rows, 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

Visual View

Clause	Type	Query	Meaning	Output
Select	all columns	select * from actor;	display all columns from table actor
Select	specific columns	select first_name,last_name from actor;	display only first_name and last_name columns from table actor
Order By	descending ordering	select first_name,last_name from actor order by first_name desc;	sort the selected columns by first_name in descending order Z-A	first_name | last_name Zach . . . | Schnider Amy . . . .| Farah
Order By	ascending 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-Z	first_name | last_name Amy . . . .| Farah Zach . . . | Schnider
Order By	multiple column ordering	select * 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 descending	customer_id | amount 1 . . . . . | 10 1 . . . . . | 5 2 . . . . . | 12 2 . . . . . | 8
Distinct	single column	select distinct rating from film;	distinct will give unique values in a column – will show all distinct rating values in the rating column	rating PG G X
Distinct	multiple column	select distinct rating,awards from film order by rating;	distinct will apply on both columns – will return unique combinations of both columns – rating and duration	rating | awards G . . .| Emmy G . . .| Oscar PG . . | Filmfare PG . . | Oscar X . . .| Emmy
Limit		select * 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
Count	with *	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
Count	with column	select count(first_name) from customer;	Number of customers in customer table – count will not count null values present in the column
Count	with distinct	select 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

Clause	Type	Query	Meaning	Output
Sum	sum of column values	select sum(amount) as total from payment	adds all the values in amount column from payment table and returns only one added up value	total 1526
Avg	average of column values	select avg(amount) as avg_amnt from payment	averages values in amount column and return one average value	avg_amnt 34.56789
Round	input1 = 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
Min	minimum value in a column	select min(amount) as minimum_sale from payment;	returns a single minimum value from the column specified	minimum_sale 1.99
Max	maximum value in a column	select max(amount) as maximum_sale from payment;	returns a single maximum value from the column specified	maximum_sale 25.99
Mix	multiple aggregation functions	select 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 table	sum_amnt | avg_amnt | cnt_amnt 1526 . . | 34.5678 .| 5
Group by	basic – group rows on a specific column – then apply aggregation functions over the group	select 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 by	group by with where clause – filter data before grouping	select 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 customer	customer_id | sum(amount) 4 . . . . . | 200 5 . . . . . | 260
Group by	group by with order clause	select 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 paid	customer_id | sum(amount) 5 . . . . . | 260 4 . . . . . | 200
Group by	group by multiple columns	select 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
Having	filter data after groupings – applied over the aggregation function	select 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

Clause	Type	Query	Meaning	Output
upper	in column	select upper(email) as email_upper from people	upper(column/value) tranforms column data inot upper case	email_upper K@YH.COM   LIN@P.COM
lower	in column	select lower(email) as email_lower from people	lower(column/value) tranforms column data inot lower case	email_lower k@yh.com   lin@p.com
length	in column	select length(email) as len from people	length(column/value) calculates length of each value in the specified column	len   8 9
upper lower length	in where clause	select lower(email) as email_lower from people where length(email) > 20	can combine functions in same or different columns of select clause	email_lower kronos-the-hero@yahoomail.com
left	in column	select left(email,3) as left_email, email from people	left(column,n) function will return the first n characters from the left side of every value in the column	left_email | email k@y . . . .| k@yh.com lin . . . .| lin@p.com
right	in column	select right(email,3) as right_email, email from people	right(column,n) function will return the last n characters from the right side of every value in the column	right_email | email com . . . . | k@yh.com com . . . . | lin@p.com
left right	nested	select 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
concat	basic	select first_name+‘.’+last_name as display_name, first_name, last_name from people	|| or + operator will combine the values in columns specified	display_name | first_name | last_name lin.do . . . | lin . . . .| do nancy.chen . | nancy . . .| chen
position / charindex	of a fixed character	select 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 column	select 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
substring	basic	select 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

Clause	Type	Query	Meaning	Output
Case	basic	select 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
Case	with group by and count	select 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
Case	with sum	select 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
Case	with sum – multiple	select 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 column	g_count  |  pg_count 20 . . . |  34
Case	with count	select 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
Case	anywhere a value is required		case 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
Coalesce	basic	select 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
Cast	basic	select 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
Replace	basic	select 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

Clause	Type	Query	Meaning	Output
union	combine with distinct	select 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 all	combine no distinct	select 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
except	combine with minus	select 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
intersect	combine only common	select 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 by	select 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
Date		select 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

Clause	Type	Query	Meaning	Output
Any	All Window Functions		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 where on the window function value
Over	with Partition By	select 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 By	select 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 By	select 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

Clause	Type	Query	Meaning	Output
Dense Rank	with Order By	select 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 Rank	with Partition By and Order By	select 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
Rank	with Partition By and Order By	rank() 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 Number	with Partition By and Order By	row_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 Number	with cte – to return a subset of rows	with 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
Ntile	with Order By	select 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
Ntile	with Partition By and Order By	select 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
Ntile	with cte – to return a subset of rows	with 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

Clause	Type	Query	Meaning	Output
Insert	with value	insert into accounts (id, account_name) values (10,’tony-stark’)	insert a record – with fixed hardcoded values
Insert	with multiple values	insert into accounts (id, account_name) values (10,’tony-stark’),(11,’peter-parker’)	insert multiple records – with different fixed values
Insert	with select	insert 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
Insert	with procedure	insert into accounts (id, account_name) execute dbo.getEmployeeAccounts	insert multiple/bulk records – by using data returned from a stored procedure
Insert	with cte	with 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

Clause	Type	Query	Meaning	Output
Update	basic	update accounts set band = ‘low’ where id=12	update a single record – using fixed value on a where condition
Update	with from	update 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
Update	with from and joins	update 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
Update	with subquery	update 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
Update	with from and case	update 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
Update	with cte	with 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
Update	with cte join	with 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

Clause	Type	Query	Meaning	Output
with	single cte	with 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
with	multiple cte	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 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
with	recursive cte	with 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
merge	merge	merge 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

Clause	Type	Query	Meaning	Output
function	scalar	create 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
function	scalar function call	select 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
function	scalar	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 end
function	scalar function call	select customer_id, tranCount(customer_id) as tran_count from transactions;		customer_id | tran_count 201 . . . . | 12 202 . . . . | 18
function	drop	drop function tranList;
function	list	select * from sys.objects where name = ‘tranCount’	lists all database objects including functions	name . . . | type | type_desc tranCount .| FN . | SQL_SCALAR_FUNCTION
function	drop + create	if 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

Clause	Type	Query	Meaning	Output
function	inline table function	create 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
function	inline table function call	select * 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 query	customer_id | amount | tran_date 210 . . . . | 2300 . | —— 210 . . . . | 1500 . | —— 210 . . . . | 700 . .| ——
function	multi 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 –
function	multi statement table function call	select * 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 query	customer_id | amount | tran_date 210 . . . . | 2300 . | —— 210 . . . . | 1500 . | —— 210 . . . . | 700 . .| ——
function	table 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
function	table 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
apply	cross apply	select 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
apply	outer apply	select 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 tables	customer_id | name | amount 209 . . . . | tate | null 210 . . . . | jake | 2300 210 . . . . | jake | 1500 211 . . . . | kurt | 7500
apply	cross apply – built in table function split	select 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

Clause	Type	Query	Meaning	Output
subquery	inside where in	select 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
subquery	inside 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
subquery	inside 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
subquery	inside select	select 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 subquery	inside 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 subquery	inside select	select 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

Clause	Type	Query	Meaning	Output
join	inner	select 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
join	left	select 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
join	right	select 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
join	cross	select 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
join	multiple	select 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
join	self	select 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 table	empid | 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

Clause	Type	Query	Meaning	Output
null	operations	select name, length(name) from employee;	any operation (+ – / *) or built-in function on null value will return null	name | length John | 4 null | null
where	equals	select 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
where	not equals	select empid, name from employee where name <> ‘John’	not equals operator <>
where	less	select empid, name from employee where empid < 2	greater than operator >
where	greater	select empid, name from employee where empid > 2	less than operator >
where	less or equal greater or equal	select empid, name from employee where empid <= 2	less than or equal to <= greater than or equal to >=
where	like 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
where	like string multiple	select empid, name from employee where name like ‘%ar%’	% wildcard can be used multiple time in same string to form a complex match	empid | name 234   | Larry 256   | Mary
where	like character	select 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
where	like character in range	select 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
where	like character in list	select 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
where	like character not from list	select 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

Clause	Type	Query	Meaning	Output
where	and	select * 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
where	or	select * 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
where	between	select * 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
where	in	select * from employee where empid in (100, 180, 200)	in = if condition value is in the list of values, then row is returned	empid | name 100   | John 180   | Elvis 200   | Mony
where	in	select * from employee where name in (‘John’, ‘Mony’)	in can be used with numbers and string	empid | name 100   | John 200   | Mony
where	not condition	select * 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
where	not equal to	select * from employee where empid != 100	!= means not equal to condition. This is same as <> operator	empid | name 70    | Fanny 180   | Elvis …
where	not multiple conditions	select * from employee where not (empid>100 and empid<200)	not (multiple conditions) = all rows that do not satisfy the complete condition	empid | name 70    | Fanny 100   | John 200   | Mony 240   | Lily
where	not like	select * from employee where name not like ‘A%’	not like = all rows for which the like condition is not satidfied	empid | name 70    | Fanny 100   | John …
where	not between	select * 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
where	not in	select * 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

Clause	Type	Query	Meaning	Output
select	column headings	select 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
select	into	if 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
select	into	if 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
general	partition 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.
window	window definition	select 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
window	window modification	select 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
window	window referencing	select 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
rows	between	select 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
range	between	select 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	preceding	rows 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]