Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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.

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

More DBMS notes