2 → WHERE (Filters applied).
3 → GROUP BY (Rows grouped).
4 → HAVING (Filter on grouped data).
5 → SELECT (Columns selected).
6 → ORDER BY (Sort the data).
7 → LIMIT (Restrict number of rows).
𝗖𝗼𝗺𝗺𝗼𝗻 𝗤𝘂𝗲𝗿𝗶𝗲𝘀 𝗧𝗼 𝗣𝗿𝗮𝗰𝘁𝗶𝗰𝗲 ↓
↬ Find the second-highest salary:
SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);
↬ Find duplicate records:
SELECT Name, COUNT(*)
FROM Emp
GROUP BY Name
HAVING COUNT(*) > 1;
💡 What is Self JOIN in #SQL?
Self JOIN is a technique in SQL where a table is joined with itself. It’s used to find relationships within the same table, such as hierarchical data or comparisons between rows.
🔑 When to Use Self JOIN?
- To explore hierarchies (e.g., employees and managers).
- To compare rows (e.g., finding duplicates).
- For parent-child relationships (e.g., categories and subcategories).
🛠 Example 1: Employee and Manager
Suppose we have an employees table:
| employee_id | name | manager_id |
|-------------|----------|------------|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
Query:
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;
Result:
| Employee | Manager |
|----------|----------|
| Alice | NULL |
| Bob | Alice |
| Charlie | Alice |
| David | Bob |
🛠 Example 2: Finding Duplicates
For a products table:
| product_id | name | price |
|------------|----------|-------|
| 1 | Laptop | 1000 |
| 2 | Laptop | 1000 |
| 3 | Monitor | 200 |
| 4 | Laptop | 1200 |
Query:
SELECT p1.product_id, p2.product_id, p1.name, p1.price
FROM products p1
JOIN products p2
ON p1.name = p2.name AND p1.price = p2.price AND p1.product_id < p2.product_id;
Result:
| Product1 | Product2 | Name | Price |
|----------|----------|---------|-------|
| 1 | 2 | Laptop | 1000 |
No comments:
Post a Comment