Master SQL Basics and Interview Questions: From Simple Queries to Advanced Techniques

SQL Basics Overview

SQL statements are composed of clauses such as SELECT (result set), FROM (source tables), WHERE (filter conditions), GROUP BY (grouping), HAVING (group filter), ORDER BY (sorting), and LIMIT (row limits). Aggregate functions include DISTINCT, SUM, COUNT, AVG, MAX, MIN.

Basic Clauses

SELECT: specify columns or expressions, e.g., [StudentID, AVG(Score)] FROM: indicate source tables, e.g., [Score table] WHERE: filter rows, e.g., [b.CourseID='0003' AND b.Score>80] GROUP BY: group rows, e.g., [by StudentID] (required for non‑grouped columns in Oracle/SQL Server)

HAVING: filter groups, e.g., [AVG(Score)>60] ORDER BY: sort results, e.g., Score ASC or Score DESC LIMIT: return top N rows, e.g.,

LIMIT 2

SELECT * FROM table LIMIT 2,1; -- skip 2 rows, read 1 row (third row)

Aggregate functions: DISTINCT, SUM, COUNT, AVG, MAX, MIN.

Multi‑Table Joins

INNER JOIN (default) – JOIN ... ON ... LEFT JOIN – LEFT JOIN tableB ON a.key = b.key RIGHT JOIN – RIGHT JOIN tableB ON a.key = b.key UNION – combine result sets without duplicates

UNION ALL – combine result sets with duplicates

INTERSECT – intersection

MINUS (EXCEPT) – difference

Oracle Specific Objects

Views

Simple view example:

CREATE VIEW emp_vw AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 90;

Simple views allow DML operations; complex views (with aggregates) do not.

Sequences

CREATE SEQUENCE emp_seq1 START WITH 1 INCREMENT BY 1 MAXVALUE 10000 MINVALUE 1 CYCLE NOCACHE;

Use emp_seq1.NEXTVAL and emp_seq1.CURRVAL to generate unique numbers.

Indexes

Oracle automatically creates indexes for UNIQUE or PRIMARY KEY columns. Manual index creation:

CREATE INDEX emp_idx ON emp2(name);<br/>CREATE INDEX emp_idx2 ON emp2(id, name);

Synonyms

CREATE SYNONYM d1 FOR departments;<br/>SELECT * FROM d1;

Data Definition Language (DDL)

CREATE TABLE ...;<br/>DROP TABLE ...;<br/>RENAME ... TO ...;<br/>TRUNCATE TABLE ...;<br/>ALTER TABLE ...;

Data Manipulation Language (DML)

INSERT INTO ... VALUES ...;<br/>UPDATE ... SET ... WHERE ...;<br/>DELETE FROM ... WHERE ...;

Data Control Language (DCL)

COMMIT;<br/>ROLLBACK;<br/>GRANT ... TO ...;<br/>REVOKE ... FROM ...;

Important Tips

Use SELECT ... with aggregate functions (MIN, MAX, SUM, AVG, COUNT).

Left join syntax: LEFT JOIN ... ON ...; right join similarly.

WHERE clause for filtering.

GROUP BY for grouping.

HAVING for group filtering.

ORDER BY for sorting (ASC/DESC).

LIMIT (0,N) for top‑N rows.

Common SQL Interview Questions (50 Classic Questions)

Database schema includes four tables: student(学号, 姓名, 出生日期, 性别), score(学号, 课程号, 成绩), course(课程号, 课程名称, 教师号), teacher(教师号, 教师姓名).

Sample Queries

Find students with surname "猴": SELECT * FROM student WHERE name LIKE '猴%'; Count teachers with surname "孟":

SELECT COUNT(教师号) FROM teacher WHERE 教师姓名 LIKE '孟%';

Total score for course 0002:

SELECT SUM(成绩) FROM score WHERE 课程号='0002';

Number of students who selected a course: SELECT COUNT(DISTINCT 学号) FROM score; Highest and lowest scores per course:

SELECT 课程号, MAX(成绩) AS 最高分, MIN(成绩) AS 最低分 FROM score GROUP BY 课程号;

Students count per gender:

SELECT 性别, COUNT(*) FROM student GROUP BY 性别;

Students with average score >60:

SELECT 学号, AVG(成绩) FROM score GROUP BY 学号 HAVING AVG(成绩)>60;

Students who selected at least two courses:

SELECT 学号, COUNT(课程号) AS 选修课程数 FROM score GROUP BY 学号 HAVING COUNT(课程号)>=2;

Students with same name and gender, count duplicates:

SELECT 姓名, COUNT(*) AS 人数 FROM student GROUP BY 姓名 HAVING COUNT(*)>=2;

Courses with failing scores sorted descending:

SELECT 课程号 FROM score WHERE 成绩<60 ORDER BY 课程号 DESC;

Average score per course sorted ascending, ties by course ID descending:

SELECT 课程号, AVG(成绩) AS 平均成绩 FROM score GROUP BY 课程号 ORDER BY 平均成绩 ASC, 课程号 DESC;

Students with score <60 in course 0004 sorted by score descending:

SELECT 学号 FROM score WHERE 课程号='0004' AND 成绩<60 ORDER BY 成绩 DESC;

Courses with enrollment >2, sorted by enrollment descending then course ID ascending:

SELECT 课程号, COUNT(学号) AS 选修人数 FROM score GROUP BY 课程号 HAVING COUNT(学号)>2 ORDER BY 选修人数 DESC, 课程号 ASC;

Complex Queries

Students who failed any course:

SELECT 学号, 姓名 FROM student WHERE 学号 IN (SELECT 学号 FROM score WHERE 成绩<60);

Students not enrolled in all courses:

SELECT 学号, 姓名 FROM student WHERE 学号 IN (SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号) < (SELECT COUNT(课程号) FROM course));

Students who selected exactly two courses:

SELECT 学号, 姓名 FROM student WHERE 学号 IN (SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号)=2);

Students born in 1990:

SELECT 学号, 姓名 FROM student WHERE YEAR(出生日期)=1990;

Top‑N per Group

To get the top N rows per group, first identify groups, then use ORDER BY and LIMIT, finally combine with UNION ALL. Example for top 2 scores per course:

(SELECT * FROM score WHERE 课程号='0001' ORDER BY 成绩 DESC LIMIT 2)<br/>UNION ALL<br/>(SELECT * FROM score WHERE 课程号='0002' ORDER BY 成绩 DESC LIMIT 2)<br/>UNION ALL<br/>(SELECT * FROM score WHERE 课程号='0003' ORDER BY 成绩 DESC LIMIT 2);

Row‑to‑Column Pivot

Transform the score table from rows to columns using CASE expressions and aggregation:

SELECT 学号,<br/>MAX(CASE WHEN 课程号='0001' THEN 成绩 ELSE 0 END) AS 课程号0001,<br/>MAX(CASE WHEN 课程号='0002' THEN 成绩 ELSE 0 END) AS 课程号0002,<br/>MAX(CASE WHEN 课程号='0003' THEN 成绩 ELSE 0 END) AS 课程号0003<br/>FROM score GROUP BY 学号;

Multi‑Table Queries

Student ID, name, number of courses, total score:

SELECT a.学号, a.姓名, COUNT(b.课程号) AS 选课数, SUM(b.成绩) AS 总成绩 FROM student a LEFT JOIN score b ON a.学号=b.学号 GROUP BY a.学号;

Students with average score >85:

SELECT a.学号, a.姓名, AVG(b.成绩) AS 平均成绩 FROM student a LEFT JOIN score b ON a.学号=b.学号 GROUP BY a.学号 HAVING AVG(b.成绩)>85;

Student course selection details:

SELECT a.学号, a.姓名, c.课程号, c.课程名称 FROM student a INNER JOIN score b ON a.学号=b.学号 INNER JOIN course c ON b.课程号=c.课程号;

Pass/fail counts per course using CASE:

SELECT 课程号,<br/>SUM(CASE WHEN 成绩>=60 THEN 1 ELSE 0 END) AS 及格人数,<br/>SUM(CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS 不及格人数<br/>FROM score GROUP BY 课程号;

Summary

The article provides a thorough review of SQL fundamentals, common functions, Oracle‑specific objects, DDL/DML/DCL commands, and a large set of interview questions covering basic queries, aggregations, subqueries, grouping, top‑N retrieval, pivoting, and multi‑table joins, equipping readers with practical skills for database development and interview preparation.