Mastering SQL: Group By, Having, CASE, Order By, Join, Top, and Limit
Unlock the full potential of SQL with our deep dive into essential query commands like Group By, Having, CASE, Order By, Join, Top, and Limit. In this video, we'll guide you through real-world examples and use cases that showcase how to effectively group, filter, and order your data in SQL.
📜 What You'll Learn:
Group By & Having: Understand how to aggregate data and filter groups based on conditions.
CASE Statements: Learn how to use conditional logic within your queries to customize output.
Order By: Master sorting your results for better data presentation.
Joins: Explore how to combine data from multiple tables with inner and outer joins.
Top & Limit: Discover how to control the number of records returned by your queries.
💻 Practical Examples:
Total Sales per Quarter: Use GROUP BY and CASE to display sales figures for each quarter, represented by their respective periods.
Inventory Analysis: Identify how many cars, motorcycles, trains, and ships are available, treating all types of cars as just "Cars."
Motorcycle Orders: Find countries with more than 10 motorcycle orders using HAVING and JOIN.
Script available :https://shorturl.at/HcNrp
select * from Sales_order;
select * from Customers;
select * from Products;
3) Display the total sales figure for each quarter. Represent each quarter with their respective period.
select sum(sales) as total_sales
, case when qtr_id = 1 then 'JAN-MAR'
when qtr_id = 2 then 'APR-JUN'
when qtr_id = 3 then 'JUL-SEP'
when qtr_id = 4 then 'OCT-DEC'
end period
from sales_order
group by qtr_id;
select sum(sales) as total_sales
, case when qtr_id = 1 then 'JAN-MAR'
when qtr_id = 2 then 'APR-JUN'
when qtr_id = 3 then 'JUL-SEP'
else 'OCT-DEC'
end period
from sales_order
group by qtr_id;
SELECT
SUM(sales) AS total_sales,
CASE
WHEN MONTH(s.order_date) BETWEEN 1 AND 3 THEN 'JAN-MAR'
WHEN MONTH(s.order_date) BETWEEN 4 AND 6 THEN 'APR-JUN'
WHEN MONTH(s.order_date) BETWEEN 7 AND 9 THEN 'JUL-SEP'
WHEN MONTH(s.order_date) BETWEEN 10 AND 12 THEN 'OCT-DEC'
END AS qtr
FROM
sales_order s
GROUP BY
CASE
WHEN MONTH(s.order_date) BETWEEN 1 AND 3 THEN 'JAN-MAR'
WHEN MONTH(s.order_date) BETWEEN 4 AND 6 THEN 'APR-JUN'
WHEN MONTH(s.order_date) BETWEEN 7 AND 9 THEN 'JUL-SEP'
WHEN MONTH(s.order_date) BETWEEN 10 AND 12 THEN 'OCT-DEC'
END;
SELECT
qtr_id,
SUM(sales) AS total_sales,
CASE
WHEN MONTH(s.order_date) BETWEEN 1 AND 3 THEN 'JAN-MAR'
WHEN MONTH(s.order_date) BETWEEN 4 AND 6 THEN 'APR-JUN'
WHEN MONTH(s.order_date) BETWEEN 7 AND 9 THEN 'JUL-SEP'
WHEN MONTH(s.order_date) BETWEEN 10 AND 12 THEN 'OCT-DEC'
END AS qtr
FROM
sales_order s
GROUP BY
qtr_id,
CASE
WHEN MONTH(s.order_date) BETWEEN 1 AND 3 THEN 'JAN-MAR'
WHEN MONTH(s.order_date) BETWEEN 4 AND 6 THEN 'APR-JUN'
WHEN MONTH(s.order_date) BETWEEN 7 AND 9 THEN 'JUL-SEP'
WHEN MONTH(s.order_date) BETWEEN 10 AND 12 THEN 'OCT-DEC'
END
ORDER BY
qtr_id;
select * from Sales_order;
select * from Customers;
select distinct product_line from Products;
4) Identify how many cars, Motorcycles, trains and ships are available in the inventory.
Treat all type of cars as just "Cars".
select case when product_line like '%Cars%' then 'Cars'
else product_line
end as products
, count(1) as no_of_vehicles
from Products
where product_line in ('Motorcycles','Trains','Ships','Classic Cars','Vintage Cars')
group by case when product_line like '%Cars%' then 'Cars'
else product_line
end;
select product_line
, count(1) as no_of_vehicles
from Products
where product_line in ('Motorcycles','Trains','Ships')
group by product_line
union
select 'Cars' as prd, count(1) as no_of_vehicles
from products
where product_line like '%Cars%'
select * from Sales_order; a,b,c a=b,a=c, b=c
select * from Customers;
select * from Products;
6) Find the countries which have done more than 10 motorcycles orders.
select top 3 c.country, count(1) as no_of_orders
from Sales_order s
join Customers c on c.customer_id = s.customer
join products p on p.product_code = s.product
where p.product_line = 'Motorcycles'
group by c.country
having count(1) (greater than) 10
order by no_of_orders desc;
-- Order of Execution:
SELECT -- 6
FROM -- 1
JOIN -- 2
WHERE -- 3
GROUP BY -- 4
HAVING -- 5
ORDER BY -- 7
LIMIT -- 8
This video is ideal for data analysts, developers, and SQL enthusiasts looking to strengthen their query skills. Don't forget to like, subscribe, and hit the bell to stay updated on more SQL tutorials!
Introduction to SQL Normalization: 1NF (First Normal Form) - Part 1
• Introduction to SQL Normalization: 1NF (Fi...
Understanding SQL Normalization: 2NF (Second Normal Form) - Part 2
• Understanding SQL Normalization: 2NF (Seco...
#SQL #GroupBy #Having #CASE #OrderBy #Joins #Top #Limit #DataAnalysis #SQLTutorial"