100 Common SQL Interview Questions with Answers and Explanations

This article introduces and analyzes 100 common SQL interview questions, divided into three modules: basic queries, advanced queries, and database design & development. It uses three example tables— employee , department , and job —to demonstrate each concept.

Department table ( department ) with fields dept_id (PK) and dept_name .

Job table ( job ) with fields job_id (PK) and job_title .

Employee table ( employee ) with fields emp_id (PK), emp_name , sex , dept_id (FK), manager (self‑FK), hire_date , job_id (FK), salary , bonus , email .

1. What is SQL and what can it do?

SQL (Structured Query Language) is the universal language for relational databases. It supports data manipulation (DML), definition (DDL), query (DQL), transaction control (TCL), and data control (DCL). It is declarative: you specify *what* you want, not *how* to obtain it.

2. How to view employee names and genders?

SELECT emp_name, sex FROM employee;

3. How to view all columns of the employee table?

SELECT * FROM employee; or list each column explicitly to avoid using * in production code.

4. How to calculate each employee's annual total income?

SELECT emp_name, salary * 12 + COALESCE(bonus, 0) FROM employee; – COALESCE converts NULL bonuses to 0.

5. How to give a query result a readable column alias?

SELECT emp_name AS "姓名", salary * 12 + COALESCE(bonus, 0) "年薪" FROM employee;

6. How to retrieve female employees?

SELECT * FROM employee WHERE sex = '女';

7. How to find employees with salaries between 8000 and 12000?

SELECT * FROM employee WHERE salary BETWEEN 8000 AND 12000;

8. How to query employees named 张三, 李四, or 张飞?

SELECT * FROM employee WHERE emp_name IN ('张三', '李四', '张飞');

9. How to find employees whose name contains the character “云”?

SELECT * FROM employee WHERE emp_name LIKE '%云%';

10. How to list employees who have a bonus?

SELECT emp_name, bonus FROM employee WHERE bonus IS NOT NULL;

11. How to find female employees hired after 2010‑01‑01?

SELECT emp_name, sex, hire_date FROM employee WHERE sex = '女' AND hire_date > DATE '2010-01-01';

12. Does the following query cause an error? Why?

SELECT * FROM employee WHERE 1 = 0 AND 1/0 = 1; – No error because SQL uses short‑circuit evaluation; the second condition is never evaluated.

13. How to remove duplicate rows (e.g., distinct genders)?

SELECT DISTINCT sex FROM employee;

14. How to sort employees by salary descending?

SELECT * FROM employee ORDER BY salary DESC;

15. How to sort by salary then by bonus?

SELECT * FROM employee ORDER BY salary DESC, bonus DESC;

16. How to sort Chinese names by pinyin?

MySQL: SELECT emp_name FROM employee ORDER BY CONVERT(emp_name USING GBK); Oracle: SELECT emp_name FROM employee ORDER BY NLSSORT(emp_name,'NLS_SORT = SCHINESE_PINYIN_M');

17. How does NULL ordering differ between MySQL and Oracle?

MySQL: NULLs appear first in ASC, last in DESC. Oracle: NULLs appear last in ASC, first in DESC (unless overridden with NULLS FIRST/LAST ).

18. How to get the top‑3 highest‑paid employees?

Oracle 12c: SELECT emp_name, salary FROM employee ORDER BY salary DESC FETCH NEXT 3 ROWS ONLY; MySQL: SELECT emp_name, salary FROM employee ORDER BY salary DESC LIMIT 3;

19. How to return ties when selecting the top‑3?

Oracle 12c: SELECT emp_name, salary FROM employee ORDER BY salary DESC FETCH NEXT 3 ROWS WITH TIES;

20. How to implement pagination (rows 11‑15)?

Oracle 12c: SELECT emp_name, salary FROM employee ORDER BY salary DESC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY; MySQL: SELECT emp_name, salary FROM employee ORDER BY salary DESC LIMIT 5 OFFSET 10;

21. What are SQL functions and their categories?

Functions are modules that take inputs and return a value. They are classified as scalar functions (operate per row) and aggregate functions (operate on a set of rows).

22‑100. (Further questions cover string functions, date arithmetic, CASE expressions, aggregate queries, GROUP BY, HAVING, subqueries, joins, set operations, window functions, database concepts, DDL/DML statements, indexes, execution plans, stored procedures, triggers, and auditing.) Each question follows the pattern: a brief problem statement, the exact SQL statement(s) for MySQL and/or Oracle, and a concise explanation of the syntax and underlying principle. FunTester原创专题推荐~ (list of related articles and resources)