SQL Queries: Commands, Joins, Subqueries and Classic Exam Queries · এসকিউএল কোয়েরি: কমান্ড, জয়েন, সাবকোয়েরি ও পরীক্ষার পরিচিত কোয়েরি

1. What SQL is and its command groups

SQL (Structured Query Language) is the standard language for relational databases (MySQL, Oracle, PostgreSQL, SQL Server, SQLite). It was developed at IBM in the 1970s (first called SEQUEL), became an ANSI standard in 1986 and an ISO standard (ISO/IEC 9075) in 1987. It is declarative: you say what rows you want, and the DBMS decides how to fetch them. Keywords are not case-sensitive; every statement ends with ;.

Group	Full form	Job	Commands
DDL	Data Definition Language	Define or change structure	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Data Manipulation Language	Change the data	INSERT, UPDATE, DELETE (MERGE)
DQL	Data Query Language	Read the data	SELECT
DCL	Data Control Language	Permissions	GRANT, REVOKE
TCL	Transaction Control Language	Transactions	COMMIT, ROLLBACK, SAVEPOINT

Many books and BD exam keys put SELECT under DML and name only three groups (DDL, DML, DCL); if DQL is not among the options, SELECT is DML. TRUNCATE removes rows but is DDL.

GRANT SELECT, INSERT ON Employee TO clerk;   -- DCL: give rights
REVOKE INSERT ON Employee FROM clerk;        -- DCL: take a right back

2. The sample tables used on this page

CREATE TABLE Department (
  dept_id   INTEGER PRIMARY KEY,
  dept_name VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE Employee (
  emp_id     INTEGER PRIMARY KEY,
  name       VARCHAR(30) NOT NULL,
  dept_id    INTEGER REFERENCES Department(dept_id),
  salary     INTEGER CHECK (salary > 0),
  manager_id INTEGER REFERENCES Employee(emp_id)
);

Department

dept_id	dept_name
10	CSE
20	EEE
30	HR
40	Audit

Employee

emp_id	name	dept_id	salary	manager_id
1	Rafiq	10	50000	NULL
2	Karim	10	40000	1
3	Salma	20	60000	1
4	Nadia	20	40000	3
5	Tanvir	NULL	30000	3
6	Mitu	10	NULL	1

Tanvir has no department, Mitu's salary is unknown, and HR and Audit have no staff: these rows are there on purpose, because exam traps live in NULLs and unmatched rows. The constraints work: inserting an employee with dept_id 99 fails with "FOREIGN KEY constraint failed", and a salary of −5 fails the CHECK.

More DDL that exams ask about:

• CHAR(n) vs VARCHAR(n): CHAR is fixed length (a shorter value is padded with spaces to n); VARCHAR is variable length (it stores only the characters given, up to n).
• DEFAULT gives a column its value when an INSERT leaves it out: status VARCHAR(10) DEFAULT 'active'.
• ON DELETE CASCADE on a foreign key deletes the child rows when their parent row is deleted; without it, deleting a parent that still has children fails (checked in SQLite: "FOREIGN KEY constraint failed").
• ALTER TABLE changes a table: ALTER TABLE Employee ADD COLUMN email VARCHAR(50); and ALTER TABLE Employee DROP COLUMN email; (Oracle and SQL Server leave out the word COLUMN after ADD). To change a column's type, MySQL and Oracle write MODIFY salary DECIMAL(10,2), while SQL Server and PostgreSQL write ALTER COLUMN; SQLite has neither.

3. SELECT and the logical order of its clauses

SELECT   dept_id, COUNT(*) AS staff, AVG(salary) AS avg_sal   -- 5
FROM     Employee                                             -- 1
WHERE    salary IS NOT NULL                                   -- 2
GROUP BY dept_id                                              -- 3
HAVING   COUNT(*) >= 2                                        -- 4
ORDER BY avg_sal DESC;                                        -- 6

Worked example 1: run the query above step by step.

1. FROM takes all 6 Employee rows.
2. WHERE salary IS NOT NULL drops Mitu: 5 rows left.
3. GROUP BY dept_id makes three groups: NULL {Tanvir}, 10 {Rafiq, Karim}, 20 {Salma, Nadia}. (All NULLs fall into one group.)
4. HAVING COUNT(*) >= 2 drops the NULL group (1 row).
5. SELECT computes the averages: dept 10 → (50000 + 40000) / 2 = 45000; dept 20 → (60000 + 40000) / 2 = 50000.
6. ORDER BY avg_sal DESC puts 20 first.

dept_id	staff	avg_sal
20	2	50000.0
10	2	45000.0

The order explains three rules: FROM is processed first; WHERE cannot use an aggregate (groups do not exist yet), so aggregates go in SELECT or HAVING; and a SELECT alias cannot be used in WHERE (standard SQL, MySQL, Oracle), though ORDER BY can use it. Other operators in WHERE: BETWEEN 40000 AND 50000 (inclusive: Rafiq, Karim, Nadia), IN (…), LIKE 'r%' (% = zero or more characters, _ = exactly one character), IS NULL. To remove duplicate rows from a result use DISTINCT; to sort use ORDER BY (ASC by default).

4. Aggregate functions and NULL

SELECT COUNT(*), COUNT(salary), SUM(salary), AVG(salary), MIN(salary), MAX(salary)
FROM Employee;

COUNT(*)	COUNT(salary)	SUM	AVG	MIN	MAX
6	5	220000	44000.0	30000	60000

• COUNT(*) counts rows (6); COUNT(salary) counts non-NULL values (5).
• Every aggregate except COUNT(*) ignores NULL, so AVG = 220000 / 5 = 44000, not 220000 / 6 = 36666.67.
• COUNT(DISTINCT salary) = 4 (60000, 50000, 40000, 30000).

NULL rules. NULL means unknown, so any comparison with it is unknown, never true:

Expression	Result
NULL = NULL	NULL (unknown), not true
NULL IS NULL	true (1)
5 + NULL	NULL
COALESCE(NULL, 0)	0
WHERE salary = NULL	0 rows
WHERE salary IS NULL	Mitu

5. Joins: a worked example

Worked example 2: Employee joined with Department on dept_id.

Join	Rows	What happens to Tanvir (no dept) and HR, Audit (no staff)
INNER JOIN	5	All three dropped
LEFT JOIN (Employee left)	6	Tanvir kept with dept_name NULL
RIGHT JOIN	7	HR and Audit kept with name NULL; Tanvir dropped
FULL OUTER JOIN	8	All kept: 5 matches + Tanvir + HR + Audit
CROSS JOIN	24	No condition: 6 × 4 pairs

SELECT e.name, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

name	dept_name
Rafiq	CSE
Karim	CSE
Salma	EEE
Nadia	EEE
Tanvir	NULL
Mitu	CSE

How to count without running: INNER = matching pairs (5); LEFT = INNER + unmatched left rows (5 + 1); RIGHT = INNER + unmatched right rows (5 + 2); FULL = INNER + both (5 + 1 + 2). "Which join keeps rows that have no match?" → an outer join.

Three more names exams use: an equi-join is a join whose condition uses only = (the joins above); a NATURAL JOIN is an equi-join on all same-named columns that shows each such column once (Employee NATURAL JOIN Department joins on dept_id: 5 rows); a theta join uses any comparison (<, >, ≠), so ON e.dept_id < d.dept_id gives 13 rows. CROSS JOIN is the Cartesian product.

A self join joins a table to itself with two aliases:

SELECT e.name AS employee, m.name AS manager
FROM Employee e JOIN Employee m ON e.manager_id = m.emp_id;

This returns 5 rows (Karim–Rafiq, Salma–Rafiq, Nadia–Salma, Tanvir–Salma, Mitu–Rafiq); Rafiq has no manager, so the inner join drops him. Adding WHERE e.salary > m.salary finds employees who earn more than their manager: Salma (60000 > 50000).

6. Subqueries: IN, ANY, ALL, EXISTS and correlated

Form	True when	Example on our data	Result
x IN (subquery)	x equals some value returned	dept_id IN (ids of CSE, HR)	Rafiq, Karim, Mitu
x > ANY (subquery)	x is greater than at least one value (same as > MIN when the subquery returns at least one row and no NULL)	salary > ANY (dept 20 salaries: 60000, 40000)	Rafiq, Salma
x >= ALL (subquery)	x is at least every value (same as >= MAX when the subquery returns at least one row and no NULL; on an empty subquery ALL is true)	salary >= ALL (dept 20 salaries)	Salma
EXISTS (subquery)	the subquery returns at least one row	departments with an employee	CSE, EEE
NOT EXISTS (subquery)	the subquery returns no row	departments with no employee	HR, Audit

-- EXISTS: departments that have at least one employee
SELECT d.dept_name FROM Department d
WHERE EXISTS (SELECT 1 FROM Employee e WHERE e.dept_id = d.dept_id);

-- Correlated subquery: employees paid above their own department's average
SELECT name, salary FROM Employee e
WHERE salary > (SELECT AVG(salary) FROM Employee WHERE dept_id = e.dept_id);

The second query is correlated: the inner query uses e.dept_id from the outer row, so it runs once per row. Dept 10's average is 45000 (Mitu's NULL ignored) and dept 20's is 50000, so the result is Rafiq 50000 and Salma 60000. Tanvir is excluded because dept_id = NULL is never true.

SQLite does not support ANY and ALL (a syntax error), so on SQLite write the MIN/MAX form; MySQL, Oracle, PostgreSQL and SQL Server support both.

7. Set operators: UNION vs UNION ALL

Both queries must return the same number of columns with compatible types.

Operator	Keeps	dept_id from Employee (6 rows) with dept_id from Department (4 rows)
UNION	Distinct rows only	5 rows: NULL, 10, 20, 30, 40
UNION ALL	Every row, duplicates too (faster: no duplicate check)	10 rows
INTERSECT	Rows in both	10, 20
EXCEPT (Oracle: MINUS)	Rows in the first, not the second	NULL (Employee EXCEPT Department); Department EXCEPT Employee gives 30, 40

SQLite, PostgreSQL and SQL Server use EXCEPT; Oracle uses MINUS (SQLite rejects MINUS). MySQL has INTERSECT and EXCEPT only from 8.0.31.

8. DELETE vs TRUNCATE vs DROP

Point	DELETE	TRUNCATE	DROP
Group	DML	DDL	DDL
Removes	Chosen rows (WHERE) or all rows	All rows	The whole table: rows, structure, indexes
WHERE allowed	Yes	No	No
Table exists afterwards	Yes	Yes, empty	No
Rollback	Yes, inside a transaction	Oracle and MySQL: no (implicit commit); PostgreSQL and SQL Server: yes inside a transaction	Oracle and MySQL: no (implicit commit); PostgreSQL and SQL Server: yes inside a transaction
Row triggers fire	Yes	No	No
Speed	Slower, logs each row	Fast, deallocates pages	Fast
Auto-increment counter	Kept	Reset (MySQL, SQL Server)	Gone with the table

Checked in SQLite: inside BEGIN … ROLLBACK, DELETE FROM Employee WHERE salary < 45000 removed 3 rows (Karim, Nadia, Tanvir), and ROLLBACK brought the count back to 6. SQLite has no TRUNCATE (syntax error); a DELETE FROM t without WHERE does the same job. After DROP TABLE T, SELECT * FROM T fails with "no such table". The usual exam answer is "TRUNCATE cannot be rolled back", which holds for Oracle and MySQL.

9. Views

A view is a stored SELECT that behaves like a virtual table. It stores no data of its own, so it always shows the current rows.

CREATE VIEW dept_summary AS
SELECT dept_id, COUNT(*) AS staff, MAX(salary) AS top_salary
FROM Employee GROUP BY dept_id;

Dept 10 shows staff 3, top_salary 50000; after UPDATE Employee SET salary = 55000 WHERE emp_id = 2 the same view shows 55000 without being changed. Views hide columns (security) and save repeated joins. Table vs view: a table stores data; a view stores only its query. A view built with GROUP BY or aggregates cannot be updated; SQLite makes every view read-only unless an INSTEAD OF trigger is defined. To change a view's query use CREATE OR REPLACE VIEW (MySQL, Oracle, PostgreSQL) or ALTER VIEW … AS SELECT … (MySQL, SQL Server); in Oracle, ALTER VIEW only recompiles the view or changes its constraints.

10. Classic exam queries

Worked example 3: the second highest salary. Salaries: 60000, 50000, 40000, 40000, 30000, NULL.

-- Method 1: works in every SQL dialect
SELECT MAX(salary) AS second_highest
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

1. The inner query finds the highest salary, 60000.
2. WHERE keeps salaries below it: 50000, 40000, 40000, 30000.
3. MAX of those = 50000.

The equivalent WHERE salary NOT IN (SELECT MAX(salary) FROM Employee) also gives 50000.

-- Method 2: LIMIT/OFFSET (MySQL, PostgreSQL, SQLite); skip 1 distinct value, take 1
SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- Method 3: N-th highest with a window function (SQL:2003; MySQL 8+, Oracle, SQLite 3.25+)
SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee WHERE salary IS NOT NULL
) ranked
WHERE rnk = 3;

Method 2 gives 50000; method 3 with rnk = 3 gives Karim 40000 and Nadia 40000 (ties share a rank). Two details: use DISTINCT with OFFSET, or ties shift the answer; and if every row has the same salary, method 1 returns one row containing NULL while method 2 returns no row. Oracle 12c+ and SQL Server 2012+ write OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY (older Oracle: ROWNUM; SQL Server also has TOP) instead of LIMIT … OFFSET.

Count per group, including empty groups.

SELECT d.dept_name, COUNT(e.emp_id) AS staff
FROM Department d
LEFT JOIN Employee e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

dept_name	staff
CSE	3
EEE	2
HR	0
Audit	0

With COUNT() instead, HR and Audit show 1, because the LEFT JOIN gives each of them one row (full of NULLs) and COUNT() counts rows. COUNT(e.emp_id) skips the NULL.

Department name with maximum and average salary (an inner join, so only departments with staff appear): CSE 50000 / 45000.0, EEE 60000 / 50000.0.

Duplicates: SELECT salary, COUNT(*) FROM Employee GROUP BY salary HAVING COUNT(*) > 1; → 40000, 2. The same pattern with name finds duplicate names.

11. Where SQLite differs from standard SQL and MySQL

Feature	Standard SQL / MySQL	SQLite (tested, 3.50.4)
ANY / ALL subqueries	Supported	Syntax error: use MIN / MAX
TRUNCATE TABLE	Supported (DDL)	Syntax error: use DELETE FROM t
GRANT / REVOKE	Supported	Not implemented (file permissions instead)
RIGHT and FULL OUTER JOIN	Standard and most DBMSs; MySQL has no FULL JOIN (use LEFT JOIN UNION RIGHT JOIN)	Supported since 3.39
Alias in WHERE	Error	Allowed: WHERE yearly > 500000 worked
Column not in GROUP BY	Error unless the column is functionally dependent on the GROUP BY columns (MySQL with ONLY_FULL_GROUP_BY, the default since 5.7.5)	Allowed: takes the value from one row of the group
7 / 2	MySQL 3.5000	3 (integer division; 7 / 2.0 gives 3.5)
ROUND(232.42, −2)	200 (MySQL, Oracle)	232.0 (a negative digit count is treated as 0)
Foreign keys	Always enforced (MySQL InnoDB)	Only after PRAGMA foreign_keys = ON
LIKE 'r%'	Case depends on collation (MySQL default: case-insensitive)	Case-insensitive for ASCII: matched Rafiq

Joining strings also differs: SQLite, Oracle and PostgreSQL write 'Riaz' || ' (CSE)' (SQLite printed "Riaz (CSE)"), while MySQL uses CONCAT('Riaz', ' (CSE)'), because in MySQL the double bar means OR by default.

12. Tricky exam cases

• 10 records, unique non-NULL salaries. SELECT COUNT(*) FROM Employee WHERE SALARY > ANY (SELECT SALARY FROM Employee) prints 9: every salary except the lowest beats at least one value. With > ALL it prints 0: no salary is greater than itself. (Checked with the MIN and MAX forms in SQLite: 9 and 0.)
• NOT IN with a NULL. SELECT dept_name FROM Department WHERE dept_id NOT IN (SELECT dept_id FROM Employee) returns 0 rows, not HR and Audit, because Tanvir's NULL dept_id makes every NOT IN test unknown. Add WHERE dept_id IS NOT NULL inside, or use NOT EXISTS.
• ROUND with negative digits. ROUND(45.926, −1) = 50 and ROUND(232.420, −2) = 200 in Oracle and MySQL (the digits count leftwards from the decimal point).
• WHERE vs HAVING. WHERE filters rows before grouping and cannot hold COUNT, SUM, AVG; HAVING filters groups after. Aggregates may appear in SELECT, HAVING and ORDER BY, and inside a subquery in WHERE, but never directly in WHERE.
• UPDATE or DELETE without WHERE changes every row.
• Index. CREATE INDEX idx_emp_dept ON Employee(dept_id) lets the DBMS search instead of scanning (SQLite's plan: "SEARCH Employee USING INDEX idx_emp_dept"), but it costs space and slows INSERT and UPDATE, so it does not always make an application faster. In most DBMSs a PRIMARY KEY or UNIQUE constraint creates an index automatically.
• Transactions. A transaction ends with COMMIT (keep the changes) or ROLLBACK (undo them); SAVEPOINT marks a point to roll back to part-way.