Self Join

Employee Table

create table if Not Exists employee (employee_id int primary key,employee_name varchar(100),salary int,manager_id int);
insert into employee(employee_id,employee_name,salary,manager_id) values
(1,'AVA',85000,null),
(2,'BOB',72000,1),
(3,'Cat',59000,1),
(4,'DAN',85000,2);

Employee With Same Salary

select e1.employee_name from employee e1 inner join employee e2 on e1.salary = e2.salary where 
e1.employee_name <> e2.employee_name;

Employee With High Salary with Employees

select * from employee e1 inner join employee e2 on e1.salary > e2.salary;

Employee With their Manager

select * from employee e1 left join employee e2 on e1.manager_id=e2.employee_id; 

SubQueries -:

Return the difference between each country happiness score and average.

select year, country,happiness_score -(select AVG(happiness_score) from happiness_scores) as diff_Hp_Score from happiness_scores;

Return list of factories , products and count of products factory make

select fp.factory, fp.product_name,fn.pr_count 
from
(select factory,product_name 
from products) fp
left join
(select factory ,count(product_name) pr_count from products group by factory) fn
on fp.factory=fn.factory order by fn.factory;

Returns products where unit_price is less than the unit price of all products from wicked choocy

select * 
from products where unit_price
< ALL
(select unit_price from products where factory="Wicked Choccy's");

CTE (Comman Table Expression)

Why we use CTE instead of Subqueries

1. Readability: Complex queries with CTE is much easier to read.

2. Reusability: CTEs can be Refereced multiple times with in query.

3. Recursiveness: CTEs can be handle recursive queries.

Syntax -:

with table_name as (query)

Return the number of orders which are sales are above than $200

with Sales as
(select o.order_id ,sum(o.units*p.unit_price) as Amount from orders o left join products p on o.product_id = p.product_id group by o.order_id having Amount>200)

select count(*) from Sales;

Multiple CTEs

syntax -:

with tb as(query),
tb1 as(query)

Return list of factories , products and count of products factory make

with tb1 as(
select factory,count(product_name) as product_count from products group by factory),
tb2 as (select factory ,product_name from products)

SELECT tb2.factory, tb2.product_name, tb1.product_count
FROM tb2
JOIN tb1 ON tb2.factory = tb1.factory;

Recursive CTEs

-: Useful for generating sequence and working with hierarchial data

Syntax -:

with recursive cte_name as
(
 select ....
 union ALL
 select ... from cte_name
)
select * from cte_name

Example -:

with recursive date(dt) as (
select '2025-05-20'
union all
select dt+ interval 1 day from date where dt<'2025-05-26')
select * from date;

Temporary Table and View

Syntax -:

create temporary table table_name as()
create view view_name as()

Window Functions -:

Syntax-:

select customer_id,order_id,order_date,transaction_id,row_number() over(partition by customer_id order by transaction_id) transaction_number from orders;

Rank() -:  Dense_Rank() -:
1          1
1          1
3          2
3          3
4          3

Top Name of the Gender

select gender,name,babies,First_Value(name) over(partition by gender order by babies desc) as top_name from baby_names where top_name=name;

-: First_Value , Last_Value and nth_value(...,n)

Second Product of each orders

select distinct * from
(select order_id,product_id,units, nth_value(product_id,2) over(partition by order_id order by units desc) as product_range from orders) as Sp
where Product_id=product_range;

Lead -: allow to return the value from the next row.

Lag -: allow to return the value from the previous row.

Ntile

with cte as(
select o.customer_id,sum(o.units*p.unit_price) as total_spend
from orderso left join products p
on p.product_id=o.product_id
group by o.customer_id
order by total_spend desc
),
sp as (select customer_id,total_spend,ntile(100) over (order by total_spend desc) as spend_pct
from ts)

select * from sp where spend_pct=1;

Function Data Types-:

Cast -: Type Casting

cast(123.8 as int)

Date

select order_id,order_date , date_add(order_date ,interval 2 day) as Ship_Date from orders;

Return the First Word of the Each Event-:

SUBSTR(event_name,1,instr(event_name,' ')-1)
-- Handle Single Word
select event_name,
case when instr(event_name,' ')=then event_name
else SUBSTR(event_name,1,instr(event_name,' ')-1)
END as first_word
from events;

Students with three letter as first name

select name from students where like '___ %';

IFFNULL

IFNULL(email,"email")

Fetch the Student with highest marks from Subjects..

with cte as(
select student_id,class_id,class_name,final_grade,row_number() over(partition by  student_id order by final_grade desc) as Top from student_grades)

select  c.student_id ,s.student_name,c.final_grade,c.class_name from cte c left join students  s on s.id=c.student_id where Top=1;

Pivot -: transforms row into column to summarize your column

Done by -: case when

Rolling Calculations -: including sub-total,cummulative sums, and moving averages allow to perform calculations across rows data..

Use with the Window Function -: Like -: sum,avg...