Notes › DBMS
SQL Essentials and Joins
Sample tables
employees
| id | name | dept_id | salary | manager_id |
|---|---|---|---|---|
| 1 | Aman | 10 | 90000 | NULL |
| 2 | Riya | 10 | 60000 | 1 |
| 3 | Kabir | 20 | 75000 | 1 |
| 4 | Neha | NULL | 50000 | 3 |
departments
| dept_id | dept_name |
|---|---|
| 10 | Engineering |
| 20 | Sales |
| 30 | HR |
Command types
| Type | Commands |
|---|---|
| DDL (structure) | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML (data) | SELECT, INSERT, UPDATE, DELETE |
| DCL (permissions) | GRANT, REVOKE |
| TCL (transactions) | COMMIT, ROLLBACK, SAVEPOINT |
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
dept_id INT,
salary DECIMAL(10,2) CHECK (salary > 0),
manager_id INT
);
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
INSERT INTO employees (name, dept_id, salary) VALUES ('Aman', 10, 90000);
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 10;
DELETE FROM employees WHERE id = 4;
DELETE vs TRUNCATE vs DROP: DELETE removes chosen rows (can use WHERE, can roll back, fires triggers); TRUNCATE removes all rows fast (resets auto-increment, no WHERE); DROP removes the table itself.
SELECT basics
SELECT name, salary
FROM employees
WHERE salary > 55000 AND dept_id IN (10, 20)
ORDER BY salary DESC
LIMIT 2; -- SQL Server: TOP 2; Oracle: FETCH FIRST 2 ROWS ONLY
Useful filters: BETWEEN 50000 AND 80000, LIKE 'A%' (starts with A), LIKE '_i%' (second letter i), IS NULL (never = NULL), DISTINCT.
Aggregates, GROUP BY and HAVING
SELECT dept_id, COUNT(*) AS people, AVG(salary) AS avg_sal, MAX(salary)
FROM employees
WHERE salary > 40000 -- filters ROWS before grouping
GROUP BY dept_id
HAVING COUNT(*) >= 2; -- filters GROUPS after grouping
Result: dept 10 → 2 people, avg 75000.
- Every non-aggregated column in SELECT must be in GROUP BY.
COUNT(*)counts rows;COUNT(col)skips NULLs.
Logical order of execution
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
That's why you can't use a SELECT alias in WHERE, but you can in ORDER BY.
Joins
INNER JOIN — only matching rows
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
| name | dept_name |
|---|---|
| Aman | Engineering |
| Riya | Engineering |
| Kabir | Sales |
(Neha has no dept; HR has no employees → both missing.)
LEFT JOIN — all rows from the left
SELECT e.name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;
Same as above plus Neha | NULL.
RIGHT JOIN — all rows from the right
Inner result plus NULL | HR.
FULL OUTER JOIN — everything
Inner result + Neha | NULL + NULL | HR. (MySQL has no FULL JOIN: use LEFT JOIN UNION RIGHT JOIN.)
CROSS JOIN — every combination
4 employees × 3 departments = 12 rows.
SELF JOIN — a table joined with itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
| employee | manager |
|---|---|
| Aman | NULL |
| Riya | Aman |
| Kabir | Aman |
| Neha | Kabir |
Finding rows with no match (anti-join)
-- departments with no employees
SELECT d.dept_name
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.id IS NULL; -- → HR
Subqueries
-- earning above the company average
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
-- correlated: above their own department's average (runs per row)
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
-- EXISTS
SELECT dept_name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
Watch out: NOT IN (subquery) returns nothing if the subquery has a NULL — prefer NOT EXISTS.
UNION vs UNION ALL
UNION removes duplicates (slower); UNION ALL keeps them. Both need the same number and types of columns.
Window functions
Calculate over related rows without collapsing them like GROUP BY.
SELECT name, dept_id, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn_in_dept,
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees;
For salaries 100, 90, 90, 80: ROW_NUMBER 1 2 3 4, RANK 1 2 2 4, DENSE_RANK 1 2 2 3. Also LAG/LEAD (previous/next row) and running totals.
Classic interview queries
-- 2nd highest salary
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
-- Nth highest salary (N = 3)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
-- or with a window
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) t WHERE r = 3;
-- duplicate emails
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
-- highest-paid person in each department
SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees e
) t WHERE rn = 1;
Views, CASE and COALESCE
SELECT name,
CASE WHEN salary >= 80000 THEN 'High'
WHEN salary >= 60000 THEN 'Mid'
ELSE 'Low' END AS band,
COALESCE(dept_id, 0) AS dept -- first non-NULL value
FROM employees;
Interview one-liners
- WHERE filters rows before grouping; HAVING filters groups after.
- INNER = matches only; LEFT = all left rows + matches; FULL = everything.
= NULLis never true — useIS NULL.- RANK leaves gaps after ties; DENSE_RANK doesn't.
- Practise on SQL practice. Related: Indexing, B+ Trees and SQL vs NoSQL, Keys and Normalization.