Intermediate SQL

Subquery, Common Table Expression (CTE)

Subquery

A query nested inside another query.
Used to perform operations in multiple steps.
Example SELECT * FROM transaction WHERE customer_id IN ( SELECT id FROM customer WHERE address = 'Kathmandu');

CTE (Common Table Expression)

Temporary named result set that we can reference later.
Improves readability and modularity of complex queries.
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);

Example:

SELECT COUNT(*) AS total_txn FROM transaction;
SELECT SUM(salary) AS total_salary FROM employee;
SELECT AVG(price) AS avg_price FROM product WHERE category = 'Electronics';
SELECT category, AVG(price) AS avg_price FROM product GROUP BY category;
SELECT department, AVG(salary) AS avg_salary FROM employee GROUP BY 1 HAVING AVG(salary) > 50000;
SELECT brand, name, COUNT(*) FROM product GROUP BY 1, 2 HAVING COUNT(*) < 5;

Types of SQL Joins:

Join Type	Description
INNER JOIN	Returns records that have matching values in both tables.
LEFT JOIN	Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.
RIGHT JOIN	Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the left side.
FULL OUTER JOIN	Returns all records when there is a match in either left or right table. If there is no match, the result is NULL on the side that does not have a match.
CROSS JOIN	Returns the Cartesian product of the two tables, i.e., all possible combinations of records from both tables.
SELF JOIN	A regular join but the table is joined with itself. Used to compare rows within the same table.

Examples of different types of SQL joins

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;

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.

Common SQL Set Operations:

Operation	Description
UNION	Combines results of two SELECT statements and removes duplicates.
UNION ALL	Combines results of two SELECT statements and includes duplicates.
INTERSECT	Returns only the records that are common to both SELECT statements.
EXCEPT	Returns records from the first SELECT statement that are not in the second SELECT statement.

Examples of common SQL set operations

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';