Intermediate SQL

2. Common Table Expression

2.1 🧩 What is a CTE?
1. CTE means Common Table Expression. A CTE is a temporary named result set that we can use later in the same query. It helps make complex SQL queries easier to read.
2.2 CTE Syntax
1. WITH cte_name AS (SELECT ... ) SELECT ... FROM cte_name;
2.3 CTE Example
1. WITH ktm_customer AS (SELECT id AS customer_idFROM customerWHERE address = 'Kathmandu' )SELECT * FROM transaction_details WHERE customer_id IN (SELECT customer_idFROM ktm_customer ); 📌 Idea: A CTE is like giving a name to a temporary result so we can use it later.

3. Aggregation, Grouping, and HAVING

3.1 📊 What is Aggregation?
1. Aggregation means summarizing many rows into one result. This is similar to NumPy aggregate functions like: 👉 sum 👉 mean 👉 min 👉 max

3.2 Aggregate Examples
1. Count total transactions: SELECT COUNT(*) AS total_txn FROM transaction_details; Find total salary: SELECT SUM(salary) AS total_salary FROM employee; Find average product price: SELECT AVG(price) AS avg_price FROM product; Find minimum and maximum salary: SELECTMIN(salary) AS min_salary,MAX(salary) AS max_salary FROM employee; 📌 Simple idea: Aggregate functions summarize data.

4. GROUP BY

4.1 🧺 What is GROUP BY?
1. GROUP BY is used when we want summary results for each group. Example: Find average salary for each department.
4.2 GROUP BY Example
1. SELECTdepartment,AVG(salary) AS avg_salary FROM employee GROUP BY department; This groups employees by department and calculates average salary for each department. 📌 Simple idea: GROUP BY creates groups, then aggregate functions summarize each group.
4.3 GROUP BY Multiple Columns
1. We can also group by more than one column. SELECTbrand,name,COUNT(*) AS product_count FROM product GROUP BY brand, name; 📌 This groups data by both brand and name.

5. HAVING

5.1 🎯 What is HAVING?
1. HAVING is used to filter groups after aggregation. Example: Show only departments where average salary is greater than 50000.
5.2 HAVING Example
1. SELECTdepartment,AVG(salary) AS avg_salary FROM employee GROUP BY department HAVING AVG(salary) > 50000; 📌 Simple idea: WHERE filters rows before grouping. HAVING filters groups after grouping.

5.4 Example with both
1. SELECTdepartment,AVG(salary) AS avg_salary FROM employee WHERE is_active = TRUE GROUP BY department HAVING AVG(salary) > 50000; Here: WHERE is_active = TRUE filters employees first GROUP BY department groups remaining employees HAVING AVG(salary) > 50000 filters the grouped result

6. Joining Multiple Tables

6.1 🔗 What is a JOIN?
1. A JOIN is used to combine data from multiple tables. Example: transaction_details stores transaction data customer stores customer details employee stores employee or cashier details product stores product details Using joins, we can create one result that combines information from all these tables. 📌 Simple idea: JOIN helps us look up related information from different tables.
6.2 Why Do We Join Tables?
1. We join tables when one table has IDs and another table has details. Example: transaction_details columns: customer_id, product_id, cashier_id customer columns: id, name, address So we connect: transaction_details.customer_id = customer.id

7. Types of SQL Joins

7.2 INNER JOIN
1. INNER JOIN returns only matching rows from both tables. SELECTt.id,c.name AS customer_name,t.txn_date FROM transaction_details t INNER JOIN customer c ON t.customer_id = c.id; 📌 If a customer does not match, that transaction will not appear.

7.3 LEFT JOIN
1. LEFT JOIN returns all rows from the left table. If there is no match in the right table, it shows NULL. SELECTt.id,c.name AS customer_name,t.txn_date FROM transaction_details t LEFT JOIN customer c ON t.customer_id = c.id; 📌 Simple idea: Keep everything from the left table.
7.4 RIGHT JOIN
1. RIGHT JOIN returns all rows from the right table. SELECTt.id,c.name AS customer_name,t.txn_date FROM transaction_details t RIGHT JOIN customer c ON t.customer_id = c.id; 📌 Simple idea: Keep everything from the right table.
7.5 FULL OUTER JOIN
1. FULL OUTER JOIN returns all rows from both tables. SELECTt.id,c.name AS customer_name,t.txn_date FROM transaction_details t FULL OUTER JOIN customer c ON t.customer_id = c.id; 📌 If a row does not match, missing values appear as NULL.
7.6 CROSS JOIN
1. CROSS JOIN gives all possible combinations of rows. SELECTc.name AS customer_name,p.name AS product_name FROM customer c CROSS JOIN product p; 📌 Be careful: CROSS JOIN can create many rows quickly.
7.7 SELF JOIN
1. A SELF JOIN means joining a table with itself. Example: Employees table has manager_id, and we want to find each employee's manager. SELECTe.name AS employee_name,m.name AS manager_name FROM employee e LEFT JOIN employee m ON e.manager_id = m.id; 📌 Simple idea: SELF JOIN compares rows inside the same table.

8. Example of Joining Multiple Tables

8.1 Joining Transaction, Customer, Employee, and Product
1. SELECTc.name AS customer_name,e.name AS employee_name,p.name AS product_name,t.product_qty,t.txn_date FROM transaction_details t LEFT JOIN customer c ON t.customer_id = c.id LEFT JOIN employee e ON t.cashier_id = e.id LEFT JOIN product p ON t.product_id = p.id; This query combines: - Customer name from customer - Employee name from employee - Product name from product - Quantity and date from transaction_details 📌 Simple idea: One table stores IDs. Other tables explain what those IDs mean.

9. Overall SQL Query Order

9.1 Full Query Structure
1. SELECT ... FROM table_name JOIN table_name ON condition WHERE condition GROUP BY columns HAVING condition ORDER BY columns LIMIT number;
9.2 Important Rule
1. We can skip clauses we do not need, but if we use them, they should follow this order: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT 📌 SQL clauses must be written in the proper order.

10. SET Operations

10.1 🧮 What are SET Operations?
1. SET operations combine the results of two or more SELECT statements. They are useful when we want to compare or combine similar results.

10.3 Important Rule
1. For SET operations, both SELECT queries should return: - Same number of columns - Compatible data types - Same column order 📌 Simple idea: SET operations stack query results on top of each other.

11. SET Operation Examples

11.1 UNION
1. SELECT name FROM customer WHERE address = 'Kathmandu'UNIONSELECT name FROM employee WHERE department = 'HR'; 📌 UNION removes duplicate names.

11.2 UNION ALL
1. SELECT name FROM customer WHERE address = 'Kathmandu'UNION ALLSELECT name FROM employee WHERE department = 'HR'; 📌 UNION ALL keeps duplicate names.
11.3 INTERSECT
1. SELECT name FROM customer WHERE address = 'Kathmandu'INTERSECTSELECT name FROM employee WHERE department = 'HR'; 📌 INTERSECT returns names found in both results.
11.4 EXCEPT
1. SELECT name FROM customer WHERE address = 'Kathmandu'EXCEPTSELECT name FROM employee WHERE department = 'HR'; 📌 EXCEPT returns names from the first result that are not in the second result.