–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 all SQL Queries Free!
Part 1: Basic Selection Queries – Select, Order By, Distinct, Limit, Count
Visual View.. coming soon!
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( from position(‘.’ in email)+1 for length(lname) ) as last_name, email from people; select substring( 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 |
3 comments on SQL Queries – All in One