Intermediate SQL

Subquery, Common Table Expression (CTE)

Subquery
1. A query nested inside another query.
2. Used to perform operations in multiple steps.
3. Example SELECT * FROM transaction WHERE customer_id IN ( SELECT id FROM customer WHERE address = 'Kathmandu');
CTE (Common Table Expression)
1. Temporary named result set that we can reference later.
2. Improves readability and modularity of complex queries.
3. Example WITH ktm_customer AS ( SELECT id AS customer_id FROM customer WHERE address = 'Kathmandu') SELECT * FROM transaction WHERE customer_id IN (SELECT customer_id FROM ktm_customer);

Aggregation, Grouping and HAVING

• Similar to what we learn in NumPy, aggregate functions
• Compute summary statistics across groups of records.
• Common Functions: COUNT, SUM, AVG, MIN, MAX.
• GROUP BY is used to compute aggregates for each group.
• HAVING is used to filter groups after aggregation.
Example:
1. SELECT COUNT(*) AS total_txn FROM transaction;
2. SELECT SUM(salary) AS total_salary FROM employee;
3. SELECT AVG(price) AS avg_price FROM product WHERE category = 'Electronics';
4. SELECT category, AVG(price) AS avg_price FROM product GROUP BY category;
5. SELECT department, AVG(salary) AS avg_salary FROM employee GROUP BY 1 HAVING AVG(salary) > 50000;
6. SELECT brand, name, COUNT(*) FROM product GROUP BY 1, 2 HAVING COUNT(*) < 5;

Joining Multiple Tables

• For lookup and find information across tables.
• Referential columns with foreign key constraints are used for joining tables.
• Sample Doc with JOIN explanation.

SQL JOIN Types

Example of Joining Multiple Tables

• SELECT c.name AS customer_name , e.name AS employee_name , p.name AS product_name , t.product_qty, t.txn_date FROM transaction t [LEFT|RIGHT|CROSS|FULL|INNER] JOIN customer c ON t.customer_id = c.id JOIN employee e ON t.employee_id = e.id JOIN product p ON t.product_id = p.id;

Overall Syntax

• SELECT... FROM JOIN ... ON ... ... ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;
• NOTE: We can skip any of the above clauses if not needed. But if used order of syntax has to be same as above.

SET Operations

• Used to combine results of two or more SELECT statements.

Set Operation Examples

• SELECT name FROM customer WHERE address = 'Kathmandu' UNION SELECT name FROM employee WHERE department = 'HR';
• SELECT name FROM customer WHERE address = 'Kathmandu' UNION ALL SELECT name FROM employee WHERE department = 'HR';
• SELECT name FROM customer WHERE address = 'Kathmandu' INTERSECT SELECT name FROM employee WHERE department = 'HR';
• SELECT name FROM customer WHERE address = 'Kathmandu' EXCEPT SELECT name FROM employee WHERE department = 'HR';

What's Next?

• Window Functions
• Recursive CTE
• Performance Tuning
• Common Functions of SQL