SQL Interview Questions: 50 Sample Queries on Student, Course, Teacher, and Enrollment Tables

The article provides a set of 50 practical SQL interview questions using a simple academic schema consisting of four tables: Student(Sid, Sname, Sage, Ssex) , Course(Cid, Cname, Tid) , SC(Sid, Cid, Score) , and Teacher(Tid, Tname) .

1. Query student IDs where course "001" scores are higher than course "002"

select a.sid from (select sid,score from sc where cid='001') a, (select sid,score from sc where cid='002') b where a.sid = b.sid and a.score > b.score;

2. Query student IDs and average scores where the average is greater than 60

select sid, avg(score) from sc group by sid having avg(score) > 60;

3. Query each student's ID, name, number of courses taken, and total score

select s.sid, s.sname, count_cid as 选课数, sum_score as 总成绩 from student s left join (select sid, count(cid) as count_cid, sum(score) as sum_score from sc group by sid) sc on s.sid = sc.sid;

4. Count teachers whose name starts with "李"

select count(tname) from teacher where tname like '李%';

5. Query IDs and names of students who have not taken any class taught by teacher "叶平"

select s.sid, s.sname from student s where s.sid not in (select distinct sid from sc as sc join course c on sc.cid = c.cid join teacher t on c.tid = t.tid where t.tname = '叶平');

6. Query IDs and names of students who have taken all courses taught by teacher "叶平"

select s.sid, s.sname from student s where s.sid in (select distinct sc.sid from sc join course c on sc.cid = c.cid join teacher t on c.tid = t.tid where t.tname = '叶平' group by sc.sid having count(sc.cid) = (select count(cid) from course c join teacher t on c.tid = t.tid where t.tname = '叶平'));

7. Query IDs and names of students who have taken both course "001" and course "002"

SELECT s.sid, s.sname FROM student s LEFT JOIN sc ON s.sid = sc.sid WHERE sc.cid = '001' AND EXISTS (SELECT * FROM sc sc_2 WHERE sc.sid = sc_2.sid AND sc_2.cid='002');

8. Query IDs and names of students whose score in course "002" is lower than in "001"

select sid, sname from (select student.sid, student.sname, score, (select score from sc as sc_2 where sc_2.sid = student.sid and sc_2.cid = '002') as score2 from student, sc where student.sid = sc.sid and cid = '001') s_2 where score2 < score;

9. Query IDs and names of students whose all course scores are below 60

select sid, sname from student where sid not in (select s.sid from student s, sc where s.sid = sc.sid and score > 60);

10. Query IDs and names of students who have not taken all courses

select s.sid, s.sname from student s, sc where s.sid = sc.sid group by s.sid, s.sname having count(sc.cid) < (select count(cid) from course);

11. Query IDs and names of students who share at least one course with student "1001"

select student.sid, sname from student, sc where student.sid = sc.sid and cid in (select cid from sc where sid='1001');

12. Query IDs and names of students who have taken at least one course that student "001" has taken

-- (code omitted in source; typical query would use a subquery similar to question 11)

13. Update scores of courses taught by teacher "叶平" to the average score of each course

update sc set score = (select avg(sc_2.score) from sc sc_2 where sc_2.cid = sc.cid) where cid in (select c.cid from course c left join teacher t on t.tid = c.tid where t.tname = '叶平');

14. Query IDs and names of students whose set of courses exactly matches that of student "1002"

select a.sid, s.sname from (select cid from sc where sid='1002') a left join sc sc_1 on a.cid = sc_1.cid where sc_1.sid <> '1002' group by sc_1.sid having count(sc_1.cid) = (select count(cid) from sc where sid='1002');

15. Delete SC records of courses taught by teacher "叶平"

delete from sc WHERE cid in (select c.cid from course c LEFT JOIN teacher t on c.tid = t.tid where t.tname = '叶平');

16. Insert records into SC for students who have not taken course "003" with the average score of course "002"

insert into sc select sid, '002', (select avg(score) from sc where cid='0022') from student where sid not in (select sid from sc where cid='002');

17. Show each student's scores for Database, Business Management, English, plus count of valid courses and average score

select sid as 学生id, (SELECT score FROM sc WHERE sc.sid = t.sid AND cid='004') as 数据库, (select score FROM sc WHERE sc.sid = t.sid AND cid='001') as 企业管理, (select score FROM sc WHERE sc.sid = t.sid AND cid='015') as 英语, count(cid) as 有效课程数, avg(t.score) as 平均成绩 from sc t group by sid order by avg(t.score) desc;

18. Query each course's highest and lowest scores

select cid as 课程id, max(score) as 最高分, min(score) as 最低分 from sc group by cid;

19. Query each course's average score and pass‑rate percentage

SELECT t.cid as 课程号, c.cname as 课程名, COALESCE(avg(score),0) as 平均成绩, 100*sum(CASE WHEN COALESCE(score,0) >= 60 THEN 1 ELSE 0 END)/count(*) as 及格百分数 FROM sc t LEFT JOIN course c on t.cid = c.cid GROUP BY t.cid ORDER BY 100*sum(CASE WHEN COALESCE(score,0) >= 60 THEN 1 ELSE 0 END)/count(*) DESC;

20. Query average scores and pass rates for specific courses in a single line

-- (code not provided in source)

21. Query average scores of different teachers' courses in descending order

select t.tid as 教师id, t.tname as 教师姓名, sc.cid as 课程id, avg(score) as 平均成绩 from sc left join course c on sc.cid = c.cid left join teacher t on c.tid = t.tid group by sc.cid order by avg(sc.score) desc;

22. Query ranks 3‑6 for specific courses

-- (code not provided in source)

23. Statistics of score ranges per course

select sc.cid as 课程id, cname as 课程名称, sum(case when score between 85 and 100 then 1 else 0 end) as '[100-85]', sum(case when score between 70 and 85 then 1 else 0 end) as '[85-70]', sum(case when score between 60 and 70 then 1 else 0 end) as '[70-60]', sum(case when score < 60 then 1 else 0 end) as '[60-0]' from sc left join course c on sc.cid = c.cid group by sc.cid;

24. Query student average scores and ranking

select 1+(select count(distinct avg_score) from (select sid, avg(score) as avg_score from sc group by sid) t1 where avg_score > t2.avg_score) as 名次, sid as 学生学号, avg_score from (select sid, avg(score) as avg_score from sc group by sid) t2 order by avg_score desc;

25. Query top three records per course (no ties)

select sid, cid, score from sc sc_1 where (select count(3) from sc sc_2 where sc_1.cid = sc_2.cid and sc_2.score >= sc_1.score) <= 2 order by sc_1.cid;

26. Query number of students enrolled in each course

select cid, count(sid) from sc group by cid;

27. Query students who have selected only one course

select sc.sid, s.sname, count(sc.cid) as 课程数 from sc left join student s on sc.sid = s.sid group by sc.sid having count(sc.cid) = 1;

28. Query male and female student counts

select count(ssex) as 男生人数 from student where ssex = '男'; select count(*) as 女生人数 from student where ssex = '女';

29. Query students whose name starts with "张"

select sid, sname from student where sname like '张%';

30. Query duplicate names and their frequencies

select sname, count(*) from student group by sname having count(*) > 1;

31. Query students born in 1981 (Sage stored as datetime)

-- (code not provided in source)

32. Query students with average score greater than 85

select s.sname, sc.sid, avg(sc.score) as 平均成绩 from sc left join student s on sc.sid = s.sid group by sc.sid having avg(sc.score) > 85;

33. Query each course's average score, ordered ascending, and by course number descending when equal

select cid, avg(score) from sc group by cid order by avg(score) asc, cid desc;

34. Query students who took the "数据库" course and scored below 60

select c.cname, s.sid, s.sname, sc.score from course c left join sc on sc.cid = c.cid left join student s on s.sid = sc.sid where c.cname = '数据库' and sc.score < 60;

35. Query all students' course selections

select sc.sid, sc.cid, s.sname, c.cname from sc left join course c on sc.cid = c.cid left join student s on sc.sid = s.sid;

36. Query names, course names, and scores for any course where the score is above 70

select distinct s.sid, s.sname, c.cname, sc.score from sc left join student s on sc.sid = s.sid left join course c on sc.cid = c.cid where sc.score > 70;

37. Query courses with failing scores, ordered by course ID descending

select cid from sc where score < 60 order by cid desc;

38. Query IDs and names of students who scored above 80 in course "003"

select sc.sid, s.sname from sc left join student s on sc.sid = s.sid where sc.cid = '003' and sc.score > 80;

39. Query the number of students who have selected at least one course

select count(distinct sid) from sc;

40. Query the student with the highest score among those who took courses taught by teacher "叶平"

select s.sname, sc.score from sc left join student s on sc.sid = s.sid left join course c on sc.cid = c.cid left join teacher t on c.tid = t.tid where t.tname = '叶平' and sc.score = (select max(score) from sc sc_1 where sc.cid = sc_1.cid);

41. Query each course and the number of students enrolled

select cid, count(*) from sc group by cid;

42. Query students who have the same score in different courses

select distinct a.sid, a.cid, a.score from sc a, sc b where a.score = b.score and a.cid <> b.cid;

43. Query the top two scores for each course

-- (code not provided in source)

44. Query courses with more than 10 students, showing course ID and enrollment count, ordered by count descending then ID ascending

select cid as 课程号, count(*) as 选修人数 from sc group by cid having count(sid) > 10 order by count(*) desc, cid;

45. Query IDs of students who have selected at least two courses

select sid from sc group by sid having count(*) >= 2;

46. Query all courses that have been selected by any student

select cid, cname from course where cid in (select cid from sc group by cid);

47. Query names of students who have never taken any course taught by teacher "叶平"

select sname from student where sid not in (select sid from sc, course, teacher where course.tid = teacher.tid and sc.cid = course.cid and teacher.tname = '叶平');

48. Query IDs and average scores of students who have failed more than two courses

select sid, avg(COALESCE(score,0)) from sc where sid in (select sid from sc where score < 60 group by sid having count(*) > 2) group by sid;

49. Query IDs of students in course "004" with scores below 60, ordered by score descending

select sid, score from sc where cid='004' and score < 60 order by score desc;

50. Delete the record of student "002" for course "001"

delete from sc where sid = '002' and cid = '001';

These queries illustrate a wide range of SQL techniques useful for interview preparation, including basic SELECT statements, various JOIN types, subqueries, aggregation functions, windowing logic, data modification (UPDATE, INSERT, DELETE), and conditional filtering.