SQL练习题库
表结构
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
试题：
1、查询“0001”课程比“C002”课程成绩高的所有学生的学号；
Select s# from sc
Where c#='0001' and score>any(select score from sc c#='0002')
2、查询平均成绩大于60分的同学的学号和平均成绩；
select s#,avg(score) from SC
group by s#
having avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩；
select student.s#,student.sname,count(sc.c#)as 选课数,sum(score)总成绩from student,sc
where student.s#=sc.s#
group by student.s#,student.sname
4、查询姓“张”的老师的个数；
select count(*)人数from teacher
where tname like'张%'
5、查询没学过“叶平”老师课的同学的学号、姓名；
select student.s#,student.sname from student,course,teacher,sc
where student.s#=sc.s# and course.t#=teacher.t# and teacher.t# not in(select t# from teacher where tname='张丽芬') group by student.s#,student.sname
6、查询学过“0001”并且也学过编号“0002”课程的同学的学号、姓名；
select sc.s#,sname from sc,student
where sc.c# = '0001' and student.s# = sc.s# and sc.s# in (select s# from sc where sc.c# = '0002')
--并（两表值）
select student.s#,student.sname from student,sc
where student.s#=sc.s# and sc.c#='0001'
union
select student.s#,student.sname from student,sc
where student.s#=sc.s# and sc.c#='0002'
--交（有相同值）
select student.s#,student.sname from student,sc
where student.s#=sc.s# and sc.c#='0001'
intersect
select student.s#,student.sname from student,sc
where student.s#=sc.s# and sc.c#='0002'
--差（不同值）
select student.s#,student.sname from student,sc
where student.s#=sc.s# and sc.c#='0001'
except
select student.s#,student.sname from student,sc
where student.s#=sc.s# and sc.c#='0002'
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select student.s#,student.sname from student,course,teacher,sc
where student.s#=sc.s# and course.t#=teacher.t# and teacher.t# in(select t# from teacher where tname='张丽芬')
group by student.s#,student.sname
8、查询课程编号“001”的成绩比课程编号“002”课程低的所有同学的学号、姓名；
select student.s#,sname,score from student,sc
where student.s#=sc.s# and score in(( select score from sc where c#='0001' )<all( select score from sc where c#='0002' )) 9、查询所有课程成绩小于60分的同学的学号、姓名；
select student.s#,sname from student
where s# in(
select s# from sc where 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 student.s#,student.sname from student,sc
where student.s#=sc.s# and c#=any(
select c# from sc
where s#='1001'
)
group by student.s#,student.sname
12、查询至少学过学号为“0001”同学所有一门课的其他同学学号和姓名；
Select sc.s#,sname from sc inner join student on sc.s#=student.s#
Where c# in (select c# from sc where s#=’0001’)
13、把“SC”表中“赵雁南”老师教的课的成绩都更改为此课程的平均成绩；
update sc
set score=(select avg(sc.score) from sc,teacher,course
where sc.c#=course.c# and course.t#=teacher.t# ame='赵雁南')
where sc.c#=(select c# from course,teacher where course.t#=teacher.t# and tname='赵雁南' )
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select student.s#,sname from student,sc
where student.s#=sc.s# and sc.c#=all(select c# from sc where s#='1005') and student.s#<>'1005'
15、删除学习“朱玉文”老师课的SC表记录；
delete from sc
where c# in(select c# from sc where c# in (select c# from course,teacher where teacher.t#=course.t# ame='朱玉文' ) )
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“0002”课程的同学学号、号课的平均成绩；
insert into sc
values
select s# from sc where s# not in (select s# from sc where c#='0002'
select avg(score) as 平均成绩from sc where c#='0002'
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式
显示：学生ID,，数据库，企业管理，英语，有效课程数，有效平均分
select sc.s#,courseame,sc.score,avg(score)as 平均成绩from sc inner join course on sc.c#=course.c#
where sc.c# in(select c# from course where cname=any(select cname from course where cname in('计算机基础','Oracle','软件工程')))
group by sc.s#,courseame,sc.score
order by avg(score) desc
SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='0001') AS 计算机基础
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='0002') AS Oracle
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='0011') AS 软件工程
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM sc AS t
GROUP BY S#
ORDER BY avg(t.score) desc
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select c# 课程ID,max(score) 最高分,min(score) 最低分from sc
group by c#
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select c#, avg(score) from sc
where score>60
group by c#
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（），马克思（），OO&UML （），数据库（）
21、查询不同老师所教不同课程平均分从高到低显示
select course.t#,avg(sc.score) as 平均分from sc,course
where sc.c#=course.c#
group by course.t#
order by avg(sc.score) desc
22、查询如下课程成绩第3 名到第6 名的学生成绩单：企业管理，马克思，UML，数据库
[学生ID],[学生姓名]，企业管理，马克思,UML，数据库，平均成绩
select student.s#,student.sname,sc.score,courseame from student inner join sc on student.s#=sc.s#
inner join course on sc.c#=course.c#
where courseame in('oracle','电路分析','计算机基础')
order by courseame,sc.score desc
23、统计列印各科成绩，各分数段人数：课程ID，课程名称,[100-85],[85-70],[70-60],[ <60]
select sc.c#,courseame,count(*)人数from sc,course
where course.c#=sc.c#
group by sc.c#,courseame
24、查询学生平均成绩及其名次
select sc.s#,avg(score) from sc
group by sc.s#
order by avg(score) desc
25、查询各科成绩前三名的记录考虑成绩并列情况
select s#,c# ,score from sc
where score in (
select distinct top 3 score from sc
group by c#,score
)
order by score desc
26、查询每门课程被选修的学生数
select c# as 课程号,count(c#)as 选修人数from sc
group by c#
order by count(c#) desc
27、查询出只选修了一门课程的全部学生的学号和姓名
select sc.s#,student.sname from sc inner join student on student.s#=sc.s#
group by sc.s#,student.sname
having count(c#)=1
28、查询男生、女生人数
select ssex, count(*) as 总人数from student
group by ssex
29、查询姓“张”的学生名单每天学点sql经典句子
select sname from student
where sname like '张%'
30、查询同名同性学生名单，并统计同名人数
select ssex,count(*) from student
group by ssex
31、同年出生的学生名单(注：Student表中Sage列的类型是datetime)
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select c#,avg(score)平均成绩from sc
group by c#
order by avg(score) asc
33、查询平均成绩大于的所有学生的学号、姓名和平均成绩
select top 1 student.s#,student.sname,avg(score)平均成绩from student inner join sc on student.s#=sc.s#
group by student.s#,student.sname
order by avg(score) desc
34、查询课程名称为“数据库”，且分数低于80的学生姓名和分数
select student.sname,sc.score from sc,student,course
where student.s#=sc.s# and sc.c#=course.c# and courseame='计算机基础' and sc.score<80
group by student.sname,sc.score
35、查询所有学生的选课情况；
select student.s#,sname,c# from student left join sc on student.s#=sc.s#
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select student.sname,courseame,sc.score from student,sc,course
where student.s#=sc.s# and sc.c#=course.c# and sc.score>70
order by score desc
37、查询不及格的课程，并按课程号从大到小排列
select c#,score from sc
where score<60
order by c#
38、查询课程编号为0001且课程成绩在70分以上的学生的学号和姓名；
select student.s#,sname from sc,student
where c#='0001' and score>70 and student.s#=sc.s#
39、求选了课程的学生人数
select count(s#)人数from sc
where sc.score<>0
40、查询选修“oracle”课程的学生中，成绩最高的学生姓名及其成绩
select top 1 student.sname,max(sc.score)成绩最高from student inner join sc on student.s#=sc.s# inner join course on sc.c#=course.c#
where courseame='oracle'
group by student.sname
order by max(sc.score) desc
41、查询各个课程及相应的选修人数
select courseame,count(sc.c#) as 选修人数from sc inner join course on sc.c#=course.c#
group by courseame
order by count(sc.c#) desc,courseame asc
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
43、查询每门功成绩最好的前两名
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数FROM SC t1
WHERE score IN (SELECT TOP 2 score FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#
44、统计每门课程的学生选修人数（超过人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
45、检索至少选修两门课程的学生学号
select s#, count(s#) as 选修课程数from sc
group by s#
having count(s#)>1
46、查询全部学生都选修的课程的课程号和课程名
select c#,cname from course
where c# in(select c# from sc)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select Sname from Student
where S# not in (
select S# from Course,Teacher,SC
where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='朱玉文')
48、查询两门以上不及格课程的同学的学号及其平均成绩
49、检索“”课程分数小于，按分数降序排列的同学学号
select s#,score from sc
where score<60
order by score desc
50、删除“1005”同学的“0004”课程的成绩
delete from sc
where s#='1005' and c#='0004'

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