下图分别是练习中的教师表，学生表，成绩表，科目表
练习如下
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
方法一：使用自连接
selectc.sno 学号
fromsc c
inner joinsc tonc.sno=t.sno andc.cno='c001' andt.cno='c002'
wherec.score>t.score
方法二：使用相关自查询
selects.sno fromsc s
wheres.sno in (
selectc.sno fromsc c
wheres.score>c.score ands.cno='c001' andt.cno='c002'
)
2、查询平均成绩大于60分的同学的学号和平均成绩
selectc.sno,avg(score) 平均成绩
fromsc c
group byc.sno
having avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩；
selectt.sno 学号,t.sname 姓名,count(s.cno) 选课数,sum(s.score) 总成绩
fromstudent t
left joinsc sont.sno=s.sno
group byt.sno,t.sname
4、查询姓“刘”的老师的个数；
select count(tname) 数量
fromteacher t
wheret.tname like '刘%'
5、查询没学过“谌燕”老师课的同学的学号、姓名；
方法一：
5.1查询出老师的编号
selectt.tno fromteacher t
wheret.tname='谌燕'
5.2查询出老师的所有课程
selects.cno
fromcourse s
inner jointeacher tont.tno=s.tno
wheret.tname='谌燕'
5.3查询出学过“谌燕”老师课的同学的学号
selectc.sno
fromsc c
wherec.cno in (
selects.cno
fromcourse s
inner jointeacher tont.tno=s.tno
wheret.tname='谌燕'
)
group byc.sno
5.4查询出没学过“谌燕”老师课的同学的学号、姓名
selectd.sno,d.sname
fromstudent d
whered.sno not in (
selectc.sno
fromsc c
wherec.cno in (
selects.cno
fromcourse s
inner jointeacher tont.tno=s.tno
wheret.tname='谌燕'
)
)
方法二：
selectd.sno,d.sname
fromstudent d
whered.sno not in (
select distincts.sno fromstudent s
left joinsc conc.sno=s.sno
left joincourse oonc.cno=o.cno
left jointeacher tont.tno =o.tno
wheret.tname = '谌燕'
)
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
方法一：通过子查询+自连接
---.1查询学过“c001”并且也学过编号“c002”课程的同学的学号
selectc.sno
fromsc c
inner joinsc tonc.sno=t.sno
where (c.cno='c001' andt.cno='c002') or (c.cno='c002' andt.cno='c001')
group byc.sno
---.2、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
selectd.sno,d.sname
fromstudent d
whered.sno in (selectc.sno
fromsc c
inner joinsc tonc.sno=t.sno
where (c.cno='c001' andt.cno='c002') or (c.cno='c002' andt.cno='c001')
group byc.sno)
方法二：通过相关子查询
selectt.sno,t.sname fromstudent t
left joinsc sons.sno=t.sno
wheres.sno in (
selectc.sno fromsc c
wheres.cno='c001' andc.cno='c002'
)
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
---5.2查询出老师的所有课程的数量
select count(s.cno)
fromcourse s
inner jointeacher tont.tno=s.tno
wheret.tname='谌燕'
SELECT DISTINCTstudent.sno,student.sname
FROMstudent
WHEREstudent.sno IN(
SELECTsc.sno
FROMsc
wheresc.cno in (
selectcourse.cno
fromcourse
wherecourse.tno in(
selectteacher.tno fromteacher
whereteacher.tname='谌燕'
)
)
group bysc.sno
having count(distinctsc.sno)=(
select count(s.cno)
fromcourse s
inner jointeacher tont.tno=s.tno
wheret.tname='谌燕'
)
)
方法二：
selects.sno,s.sname fromstudent s
left joinsc conc.sno=s.sno
wherec.cno in(
selecto.cno fromteacher t
left joincourse oont.tno=o.tno
wheret.tname='谌燕')
group bys.sno,s.sname
having count(c.cno)=(
select count(o.cno) fromteacher t
left joincourse oont.tno=o.tno
wheret.tname='谌燕'
)
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
selectt.sno 学号,t.sname 姓名
fromstudent t
wheret.sno not in (
selectc.sno 学号
fromsc c
inner joinsc tonc.sno=t.sno andc.cno='c001' andt.cno='c002'
wherec.score>t.score)
9、查询所有课程成绩小于60分的同学的学号、姓名；
--方法一，
selects.sno 学号,s.sname 姓名
fromstudent s
full outer joinsc tont.sno=s.sno
group bys.sno,s.sname
having sum(t.score)<60
--方法二
selects.sno 学号,s.sname 姓名
fromstudent s
wheres.sno not in(
selectc.sno
fromsc c
group byc.sno
having sum(c.score)>60
)
10、查询没有学全所有课的同学的学号、姓名；
selects.sno,s.sname
fromstudent s
wheres.sno in (
--查询出满足条件的所有sno
selectc.sno
fromsc c
group byc.sno
having count(c.cno)=(
select count(o.cno) fromcourse o
)
)
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
selects.sno,s.sname
fromstudent s
full outer joinsc cons.sno=c.sno
wherec.cno in(
selectsc.cno fromsc
wheresc.sno='s001'
) andc.sno not like 's001'
group bys.sno,s.sname
12、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名；
selectt.sno,t.sname fromstudent t
inner joinsc sons.sno=t.sno
wheres.cno in (
selectc.cno fromsc c
wherec.sno='s002'
) ands.sno not like 's002'
group byt.sno,t.sname
having count(s.cno)>=(
select count(n.cno) fromsc n
wheren.sno='s002'
)
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
UPDATESC
SETSC.score=(
SELECT avg(s.score) fromsc s
inner joincourse oons.cno=o.cno
inner jointeacher tont.tno=o.tno
wheret.tname='谌燕' andsc.cno=s.cno
group bys.cno
)
wheresc.cno=(
selectc.cno fromcourse c
left jointeacher tont.tno=c.tno
wheret.tname='谌燕' andsc.cno=c.cno
)
14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
方法一：
selects.sno fromsc s
left join (select distinctc.cno fromsc cwherec.sno='s001')c1onc1.cno=s.cno
group bys.sno
having count(s.cno)=(select distinct count(c.cno) fromsc cwherec.sno='s001') ands.sno<>'s002'
15、删除学习“谌燕”老师课的SC表记录；
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；
INSERT INTOsc
(sno, cno, score)
VALUES ('s005','c001',
(select AVG(s.score)
fromsc s
group bys.cno
havings.cno='c002'
))
17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select s.cno课程ID,MAX(s.score)最高分,min(s.score)最低分
from sc s
group by s.cno
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
selects.cno 科目,avg(s.score) 平均成绩,cast(
round(cast(SUM(case whens.score>60 then 1 else 0 end) as float)/cast(count(*) as float)*100,2)
asnvarchar(10) )+'% ' 及格率
fromsc s
group bys.cno
order by avg(s.score) asc,cast(
round(cast(SUM(case whens.score>60 then 1 else 0 end) as float)/cast(count(*) as float)*100,2)
asnvarchar(10) ) desc
selects.cno 科目
,
cast(
round(cast(SUM(case whens.score>60 then 1 else 0 end) as float)/cast(count(*) as float)*100,2)
asnvarchar(10) )+'% ' 及格率
fromsc s
group bys.cno
19、查询不同老师所教不同课程平均分从高到低显示
selectt.tname 教师,c.cno 科目,avg(s.score) 平均值
fromsc s
right joincourse cons.cno=c.cno
right jointeacher tonc.tno=t.tno
group byc.cno,t.tname
order by avg(s.score) desc
-- select *
--fromsc s
--full outer joincourse cons.cno=c.cno
--full outer jointeacher tonc.tno=t.tno
--group byc.cno,t.tname
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select
c.cno 课程id,
o.cname 课程名称,
sum(case whenc.score>85 andc.score<100 then 1 else 0 end) [100-85],
sum(case whenc.score>70 andc.score<85 then 1 else 0 end) [85-70],
sum(case whenc.score>60 andc.score<70 then 1 else 0 end) [70-60],
sum(case whenc.score<60 then 1 else 0 end) [ <60]
fromsc c
joincourse oonc.cno=o.cno
group byc.cno,o.cname
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
selects.score,s.cno
fromsc s
left joinsc conc.cno=s.cno andc.score<s.score
group bys.score,s.cno
having count(c.cno)<3
order bys.cno
22、查询每门课程被选修的学生数
selecto.cname 课程,count(s.cno) 人数
fromsc s
right joincourse oons.cno=o.cno
group byo.cname
23、查询出只选修了一门课程的全部学生的学号和姓名
selects.sno 学号,s.sname 姓名
fromstudent s
inner joinsc conc.sno =s.sno
--group byc.cno, s.sno,s.sname
--having count(c.cno)=1
wherec.sno in (
selectt.sno fromsc t
group byt.sno
having count(t.sno)=1
)
24、查询男生、女生人数
selectt.ssex 性别,count(t.ssex) 人数 fromstudent t
group byt.ssex
25、查询姓“张”的学生名单
selects.sname fromstudent s
wheres.sname like '张%'
26、查询同名同性学生名单，并统计同名人数
selectt.sname,count(t.sname)
fromstudent t
left joinstudent sons.sname=t.sname ands.ssex=s.ssex
group byt.sname
having count(t.sname)>1
27、1981年出生的学生名单(注：Student表中Sage列的类型是numeric)
28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select avg(c.score) fromsc c
group byc.cno
order by avg(c.score) asc,c.cno desc
29、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
selectt.sno,t.sname,avg(s.score) fromstudent t
left joinsc sons.sno=t.sno
wheret.sno in (
selectc.sno fromsc c
group byc.sno
having avg(c.score)>60
)
group byt.sno,t.sname
30、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
selectt.sname,s.score fromstudent t
left joinsc sont.sno=s.sno
wheres.cno in(
selecto.cno fromcourse o
whereo.cname='SQL SERVER 2005'
) ands.score<60
31、查询所有学生的选课情况；
selectt.sname,count(s.cno) fromstudent t
left joinsc sont.sno=s.sno
group byt.sno,t.sname
32、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
selects.sname 姓名,o.cname 课程名称,c.score 课程成绩 fromstudent s
left joinsc conc.sno=s.sno
left joincourse oonc.cno=o.cno
group byc.sno,s.sname,o.cname,c.score
havingc.score>70
33、查询不及格的课程，并按课程号从大到小排列
selecto.cname 课程名称 fromsc s
left joincourse oono.cno=s.cno
wheres.score<60
order bys.cno desc
34、查询课程编号为c001且课程成绩在80分以上的学生的学号和姓名；
selects.sno 学号,s.sname 名称
fromstudent s
left joinsc conc.sno=s.sno
wherec.score>80 andc.cno='c001'
--
35、求选了课程的学生人数
select count(distinct s.sno) from sc s
36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
selecttop1s.sname 姓名,c.score 成绩 fromstudent s
left joinsc conc.sno=s.sno
left joincourse oono.cno=c.cno
left jointeacher tont.tno=o.tno
wheret.tname='谌燕'
order byc.score desc
37、查询各个课程及相应的选修人数
selecto.cname 课程,count(s.cno) 人数 fromsc s
left joincourse oono.cno=s.cno
group byo.cname
38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distincts.sno 学号,c.score 课程号,o.cname 学生成绩 fromstudent s
left joinsc conc.sno=s.sno
left joincourse oono.cno=c.cno
wherec.sno in
(
select distincta.sno fromsc a
left joinsc bonb.sno=a.sno andb.cno !=a.cno
wherea.score=b.score
)
39、查询每门功课成绩最好的前两名
selects.cno,s.sno fromsc s
left joinsc conc.cno=s.cno andc.score<s.score
group byc.cno,s.cno,s.sno
having count(c.cno)<3
order bys.cno desc
40、统计每门课程的学生选修人数（超过10人的课程才统计）。--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
lselect s.cno 课程号,count(s.cno) 参与人数 fromsc s
group bys.cno
having count(s.cno)>=10
order by count(s.score) desc,s.cno asc
41、检索至少选修两门课程的学生学号
selects.sno fromsc s
group bys.sno
having count(s.cno)>2
42、查询全部学生都选修的课程的课程号和课程名
selecto.cno,o.cname fromcourse o
43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
select distincts.sno,s.sname fromstudent s
left joinsc conc.sno=s.sno
left joincourse oonc.cno=o.cno
left jointeacher tont.tno=o.tno
wheret.tname='刘阳'
44、查询两门以上不及格课程的同学的学号及其平均成绩
selects.sno,avg(s.score) 平均成绩
fromsc s
wheres.sno in(
selectc.sno fromsc c
wherec.score<60
group byc.sno
having count(c.sno)>1
)
group bys.sno
45、检索“c004”课程分数小于60，按分数降序排列的同学学号
selectsc.sno 学号 fromsc
wheresc.score<60 andsc.cno='c004'
order bysc.score desc
46、删除“s002”同学的“c001”课程的成绩
delete fromsc
wheresc.sno='s002' andsc.cno='c001'