经典sql⾯试题（学⽣表_课程表_成绩表_教师表）表架构
Student(S#,Sname,Sage,Ssex) 学⽣表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
建表语句
CREATE TABLE student
(
s#    INT,
sname nvarchar(32),
sage  INT,
ssex  nvarchar(8)
)
CREATE TABLE course
(
c#    INT,
cname nvarchar(32),
t#    INT
)
CREATE TABLE sc
(
s#    INT,
c#    INT,
score INT
)
CREATE TABLE teacher
(
t#    INT,
tname nvarchar(16)
)
插⼊测试数据语句
insert into Student select1,N'刘⼀',18,N'男'union all
select2,N'钱⼆',19,N'⼥'union all
select3,N'张三',17,N'男'union all
select4,N'李四',18,N'⼥'union all
select5,N'王五',17,N'男'union all
select6,N'赵六',19,N'⼥'
insert into Teacher select1,N'叶平'union all
select2,N'贺⾼'union all
select3,N'杨艳'union all
select4,N'周磊'
insert into Course select1,N'语⽂',1union all
select2,N'数学',2union all
select3,N'英语',3union all
select4,N'物理',4
insert into SC
select1,1,56union all
select1,2,78union all
select1,3,67union all
select1,4,58union all
select2,1,79union all
select2,2,81union all
select2,3,92union all
select2,4,68union all
select3,1,91union all
select3,2,47union all
select3,3,88union all
select3,4,56union all
select4,2,88union all
select4,3,90union all
select4,4,93union all
select5,1,46union all
select5,3,78union all
select5,4,53union all
select6,1,35union all
select6,2,68union all
select6,4,71
问题
问题：
1、查询“001”课程⽐“002”课程成绩⾼的所有学⽣的学号；
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;
2、查询平均成绩⼤于60分的同学的学号和平均成绩；
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查询姓“李”的⽼师的个数；
select count(distinct(Tname))
from Teacher
where Tname like'李%';
5、查询没学过“叶平”⽼师课的同学的学号、姓名；
select Student.S#,Student.Sname
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select*from SC as SC_2 where
SC_2.S#=SC.S# and SC_2.C#='002');
7、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平'));
8、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
9、查询所有课程成绩⼩于60分的同学的学号、姓名；
select S#,Sname
from Student
where S# not in (select S.S# from Student AS S,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询⾄少有⼀门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct S#,Sname from Student,SC where Student.S#=SC.S# and SC.C# in (select C# from SC where S#='1001');
12、查询⾄少学过学号为“001”同学所有⼀门课的其他同学学号和姓名；
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩；
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select S# from SC where C# in (select C# from SC where S#='1002')
group by S# having count(*)=(select count(*) from SC where S#='1002');
15、删除学习“叶平”⽼师课的SC表记录；
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩；
Insert SC select S#,'002',(Select avg(score)
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID,，数据库，企业管理，英语，有效课程数，有效平均分
SELECT S# as学⽣ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS英语
,COUNT(*) AS有效课程数, AVG(t.score) AS平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分
SELECT L.C# As课程ID,L.score AS最⾼分,R.score AS最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
⾃⼰写的:select c# ,max(score)as最⾼分 ,min(score) as最低分from dbo.sc  group by c#
19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序
SELECT t.C# AS课程号,max(course.Cname)AS课程名,isnull(AVG(score),0) AS平均成绩
,100*SUM(CASE WHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*) AS及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY100*SUM(CASE WHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(⽤"1⾏"显⽰): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
SELECT SUM(CASE WHEN C# ='001'THEN score ELSE0END)/SUM(CASE C# WHEN'001'THEN1ELSE0END) AS企业管理平均分
,100*SUM(CASE WHEN C# ='001'AND score >=60THEN1ELSE0END)/SUM(CASE WHEN C# ='001'THEN1ELSE0END) AS企业管理及格百分数
,SUM(CASE WHEN C# ='002'THEN score ELSE0END)/SUM(CASE C# WHEN'002'THEN1ELSE0END) AS马克思平均分
,100*SUM(CASE WHEN C# ='002'AND score >=60THEN1ELSE0END)/SUM(CASE WHEN C# ='002'THEN1ELSE0END) AS马克思及格百分数
,SUM(CASE WHEN C# ='003'THEN score ELSE0END)/SUM(CASE C# WHEN'003'THEN1ELSE0END) AS UML平均分
,100*SUM(CASE WHEN C# ='003'AND score >=60THEN1ELSE0END)/SUM(CASE WHEN C# ='003'THEN1ELSE0END) AS UML及格百分数
,SUM(CASE WHEN C# ='004'THEN score ELSE0END)/SUM(CASE C# WHEN'004'THEN1ELSE0END) AS数据库平均分
,100*SUM(CASE WHEN C# ='004'AND score >=60THEN1ELSE0END)/SUM(CASE WHEN C# ='004'THEN1ELSE0END) AS数据库及格百分数
FROM SC
21、查询不同⽼师所教不同课程平均分从⾼到低显⽰
SELECT max(Z.T#) AS教师ID,MAX(Z.Tname) AS教师姓名,C.C# AS课程ＩＤ,MAX(C.Cname) AS课程名称,AVG(Score) AS平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
22、查询如下课程成绩第3名到第6名的学⽣成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
[学⽣ID],[学⽣姓名]，企业管理，马克思,UML，数据库，平均成绩
SELECT DISTINCT top3
SC.S# As学⽣学号,
Student.Sname AS学⽣姓名 ,
T1.score AS企业管理,
T2.score AS马克思,
T3.score AS UML,
T4.score AS数据库,
ISNULL(T1.score,0) +ISNULL(T2.score,0) +ISNULL(T3.score,0) +ISNULL(T4.score,0) as总分
FROM Student,SC  LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# ='001'
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# ='002'
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# ='003'
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# ='004'
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) +ISNULL(T2.score,0) +ISNULL(T3.score,0) +ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP15WITH TIES
ISNULL(T1.score,0) +ISNULL(T2.score,0) +ISNULL(T3.score,0) +ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# ='k1'
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# ='k2'
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# ='k3'
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# ='k4'
ORDER BY ISNULL(T1.score,0) +ISNULL(T2.score,0) +ISNULL(T3.score,0) +ISNULL(T4.score,0) DESC);
23、统计列印各科成绩，各分数段⼈数：课程ID，课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.C# as课程ID, Cname as课程名称
,SUM(CASE WHEN score BETWEEN85AND100THEN1ELSE0END) AS[100 - 85]
,SUM(CASE WHEN score BETWEEN70AND85THEN1ELSE0END) AS[85 - 70]
,SUM(CASE WHEN score BETWEEN60AND70THEN1ELSE0END) AS[70 - 60]
,SUM(CASE WHEN score <60THEN1ELSE0END) AS[60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
24、查询学⽣平均成绩及其名次
SELECT1+(SELECT COUNT( distinct平均成绩)
FROM (SELECT S#,AVG(score) AS平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE平均成绩> T2.平均成绩) as名次,
S# as学⽣学号，平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
每天学点sql经典句子FROM SC
GROUP BY S#
) AS T2
ORDER BY平均成绩desc;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as学⽣ID,t1.C# as课程ID,Score as分数
FROM SC t1
WHERE score IN (SELECT TOP3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
26、查询每门课程被选修的学⽣数
select c#,count(S#) from sc group by C#;
27、查询出只选修了⼀门课程的全部学⽣的学号和姓名
select SC.S#,Student.Sname,count(C#) AS选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
28、查询男⽣、⼥⽣⼈数
Select count(Ssex) as男⽣⼈数from Student group by Ssex having Ssex='男';
Select count(Ssex) as⼥⽣⼈数from Student group by Ssex having Ssex='⼥'；
29、查询姓“张”的学⽣名单
SELECT Sname FROM Student WHERE Sname like'张%';
30、查询同名同性学⽣名单，并统计同名⼈数
select Sname,count(*) from Student group by Sname having count(*)>1;;
31、1981年出⽣的学⽣名单(注：Student表中Sage列的类型是datetime)
select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))='1981';
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
33、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩
select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
34、查询课程名称为“数据库”，且分数低于60的学⽣姓名和分数
Select Sname,isnull(score,0)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。