Advanced Topics in SQL

Visit https://www.jovian.ai

Transcript

1. Advanced Topics in SQL
2. What We’ll Cover
3. Working with Dates • Data Types & Parts • Creation & Arithmetic • Formatting & Parsing
4. Advanced SQL Clauses • GROUP BY & HAVING • Common Table Expressions • Union, Intersection & Difference • CASE-WHEN-THEN-ELSE
5. Advanced SQL Statements • Stored Procedures • Stored Functions • Recursion
6. Database & System Setup
7. Classic Models Database
8. • Offices & employees worldwide • Customers are toy/gift stores • Dedicated sales representative • Pay for multiple orders at once
9. Entity Relationship Diagram (ERD)
10. Local Database Setup • Download the SQL script • Install and start MySQL Server • Execute on MySQL Workbench
11. Problem Solving Approach • Understand inputs & outputs • Articulate solution verbally • Construct query step-by-step
12. Working with Dates
13. Date & Time Data Types • DATE: "2004-07-28" • TIME: "15:20:52.312468" • DATETIME: "2004-07-28 15:20:52" • TIMESTAMP: includes timezone
14. Date & Time Parts Q: Show the year, month number, month name, week number, day of month, weekday number, weekday name, hour, minute, and second for all payments made by customers.
15. Date & Time Parts SELECT *, DATE(paymentDate) AS date, TIME(paymentDate) AS time, YEAR(paymentDate) AS year, MONTH(paymentDate) AS month, MONTHNAME(paymentDate) AS monthName, WEEK(paymentDate) AS week, DAYOFMONTH(paymentDate) AS dayOfMonth, WEEKDAY(paymentDate) AS dayOfWeek, DAYNAME(paymentDate) AS dayName, HOUR(paymentDate) AS hour, MINUTE(paymentDate) AS minute, SECOND(paymentDate) AS second FROM payments;
16. Current Date & Time • NOW & TIMESTAMP • CURRENT_DATE • CURRENT_TIME • INTERVAL expr unit
17. Current Date & Time SELECT NOW() AS curentDatetime, CURRENT_TIMESTAMP() AS currentTimestamp, CURRENT_DATE() AS currentDate, CURRENT_TIME() AS currentTime, CURRENT_DATE() + INTERVAL 1 DAY AS tomorrow, NOW() + INTERVAL 3 MONTH AS threeMonthsLater, CURRENT_TIME() + INTERVAL 2 HOUR AS twoHoursLater;
18. Date & Time Arithmetic • DATE_ADD & DATE_SUB • DATEDIFF • TIMEDIFF • ADDTIME
19. Date & Time Arithmetic Q: Determine the number of days and the exact duration between the required date and shipped date for each order. Then, list the orders that were shipped less than 72 hours before the required date.
20. Date & Time Arithmetic SELECT *, DATEDIFF(requiredDate, shippedDate) AS diffDays, TIMEDIFF(requiredDate, shippedDate) AS diffTime FROM orders WHERE requiredDate < shippedDate + INTERVAL 72 HOUR;
21. Date & Time Formatting Q: Show the date and time for payments made by customers in the formats “Friday, January 23rd, 2004” and “3:15:23 PM” respectively.
22. Date & Time Formatting SELECT *, DATE_FORMAT(paymentDate, "%W, %M %D, %Y") AS formattedDate, TIME_FORMAT(paymentDate, "%r") AS formattedTime FROM payments;
23. Date Parsing Q: Parse the date string 
 "Friday, January 23rd, 2004, 3:15:23 PM"
 into a SQL DATETIME object.
24. Date Parsing SELECT STR_TO_DATE( "Friday, January 23rd, 2004, 3:15:23 PM", "%W, %M %D, %Y, %r" ) AS parsedDate;
25. Advanced SQL Clauses
26. GROUP BY & HAVING Q: List customers who have placed more than 2 orders on or after April 1st, 2004. Show the customer number, customer name, number of orders placed, and last order date.
27. GROUP BY & HAVING SELECT c.customerNumber, c.customerName, COUNT(*) AS numOrders, MAX(orderDate) AS lastOrderDate FROM customers c JOIN orders o ON c.customerNumber = o.customerNumber WHERE o.orderDate >= '2004-04-01' GROUP BY c.customerNumber HAVING numOrders > 3;
28. Common Table Expression WITH cte_name (column_list) AS ( query ) SELECT * FROM cte_name WHERE ...;
29. Common Table Expression Q: Identify the top 5 sales representatives based on total amounts of orders shipped in 2003. Output employee number, first name, last name, and total sales in 2003.
30. Common Table Expression WITH orderValues AS ( SELECT o.customerNumber, o.orderNumber, SUM(od.quantityOrdered * od.priceEach) AS orderValue FROM orders o JOIN orderDetails od ON od.orderNumber = o.orderNumber WHERE YEAR(o.shippedDate) = 2003 AND status = 'Shipped' GROUP BY o.orderNumber)
31. Common Table Expression SELECT e.employeeNumber, e.lastName, e.firstName, ROUND(SUM(orderValue), 2) AS sales FROM orderValues ov JOIN customers c ON ov.customerNumber = c.customerNumber JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber GROUP BY c.salesRepEmployeeNumber ORDER BY sales DESC LIMIT 5;
32. Union, Intersection & Difference
33. Union Q: List the cities where Classic Models either has an office or a customer.
34. Union (SELECT city, state, country FROM offices UNION DISTINCT SELECT city, state, country FROM customers) ORDER BY city;
35. Exercise - Intersection Q: List the cities where Classic Models has an office and has at least one customer.
36. Exercise - Difference Q: List the cities where Classic Models has an office but does not have any customers.
37. CASE-WHEN-THEN-ELSE Q: Classify customers based on number of orders as “New” (0 orders), “One-time” (1 order), “Repeat” (2 orders), or “Loyal” (> 2 orders).
38. CASE-WHEN-THEN-ELSE SELECT c.customerNumber, c.customerName, COUNT(o.orderNumber) AS orders, CASE COUNT(o.orderNumber) WHEN 0 THEN "New" WHEN 1 THEN "One-time" WHEN 2 THEN "Repeat" ELSE "Loyal" END AS customerType FROM customers c LEFT OUTER JOIN orders o ON c.customerNumber = o.customerNumber GROUP BY c.customerNumber ORDER BY orders DESC;
39. Pivoting using CASE Q: Show the number of “Shipped”, “On Hold”, “In Process”, “Resolved”, “Cancelled”, “Disputed” and total orders for each customer.
40. Pivoting using CASE SELECT customerNumber, customerName, SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS 'Shipped', SUM(CASE WHEN status = 'On Hold' THEN 1 ELSE 0 END) AS 'On Hold', SUM(CASE WHEN status = 'In Process' THEN 1 ELSE 0 END) AS 'In Process', SUM(CASE WHEN status = 'Resolved' THEN 1 ELSE 0 END) AS 'Resolved', SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS 'Cancelled', SUM(CASE WHEN status = 'Disputed' THEN 1 ELSE 0 END) AS 'Disputed', COUNT(orderNumber) AS 'Total' FROM orders NATURAL JOIN customers GROUP BY customerNumber ORDER BY customerName;
41. Exercise Q: Classify products based on total units shipped as “Bestseller” (> 1500), “Popular” (1001-1500), “Infrequent” (<= 1000). Then, list the number of unique products in each category.
42. Advanced SQL Statements
43. Stored Procedure • Reuse query without retyping • Takes zero or more inputs • Returns rows, list, or value
44. Stored Procedure Q: Create a stored procedure to list all the orders for a given customer. Also show the total order value for each order.
45. Stored Procedure DELIMITER $$ CREATE PROCEDURE ordersWithValue(IN custNo INT) BEGIN SELECT o.orderNumber, o.customerNumber, o.orderDate, ROUND(SUM(od.priceEach * od.quantityOrdered), 2) AS orderValue FROM orders o JOIN orderDetails od ON o.orderNumber = od.orderNumber WHERE o.customerNumber = custNo GROUP BY orderNumber; END$$ DELIMITER ;
46. Stored Procedure -- Usage CALL ordersWithValue(103); -- Deletion DROP PROCEDURE IF EXISTS ordersWithValue; -- Listing SHOW PROCEDURE STATUS WHERE db = 'classicmodels';
47. Stored Function • Reuse logic within queries • Accepts zero or more inputs • Returns a single value
48. Stored Function Q: Create a stored function to classify payments as small (up to $10k), medium ($10k-$50k), large (over $50k). Using the function, show the total number of payments of each type.
49. Stored Function DELIMITER $$ CREATE FUNCTION PAYMENT_SIZE(amount NUMERIC) RETURNS VARCHAR(10) DETERMINISTIC BEGIN DECLARE size VARCHAR(10); IF amount <= 10000 THEN SET size = 'Small'; ELSEIF amount <= 50000 THEN SET size = 'Medium'; ELSE SET size = 'Large'; END IF; RETURN size; END$$ DELIMITER ;
50. Stored Function -- Usage SELECT PAYMENT_SIZE(amount) AS paymentSize, COUNT(*) AS paymentCount FROM payments GROUP BY paymentSize; -- Deletion DROP FUNCTION IF EXISTS PAYMENT_SIZE; — Listing SHOW FUNCTION STATUS WHERE db = 'classicmodels';
51. Exercise Q: Enhance the ordersWithValue stored procedure to list orders for all customers if the provided customer number is NULL.
52. Recursion • Query that references itself • Starts with an initial value/row • Compute next value using last • Ends if computed value is NULL
53. Recursion WITH RECURSIVE cte_name AS ( initial_query -- anchor member UNION ALL recursive_query -- recursive member ) SELECT * FROM cte_name;
54. Recursion Q: Write a recursive SQL statement to list numbers from 1 to 10.
55. Recursion WITH RECURSIVE nums (n) AS ( SELECT 1 -- start UNION ALL SELECT n + 1 -- increment FROM nums WHERE n < 10 -- stop ) SELECT * FROM nums;
56. Recursion Q: List the employee number, name, and designation of the chain of managers for employee number 1401.
57. Recursion WITH RECURSIVE employeeChain AS ( SELECT e.employeeNumber, e.lastName, e.firstName, e.jobTitle, e.reportsTo FROM employees e WHERE e.employeeNumber = 1401 UNION ALL SELECT e.employeeNumber, e.lastName, e.firstName, e.jobTitle, e.reportsTo FROM employees e JOIN employeeChain ec ON e.employeeNumber = ec.reportsTo ) SELECT * FROM employeeChain;
58. Exercise Q: Create a stored procedure to list the employee number, name, and designation of the chain of managers for a given employee.
59. Summary
60. Working with Dates • Data Types & Parts • Creation & Arithmetic • Formatting & Parsing
61. Advanced SQL Clauses • GROUP BY & HAVING • Common Table Expressions • Union, Intersection & Difference • CASE-WHEN-THEN-ELSE
62. Advanced SQL Statements • Stored Procedures • Stored Functions • Recursion
63. References • MySQL Reference Manual • MYSQL Tutorial • TechTFQ YouTube Channel
64. Practice SQL Online • LeetCode • Interview Query • StrataScratch
65. Advanced Topics in SQL