公眾號(hào)
事業(yè)單位
當(dāng)前位置：student(s#,sname,sage,ssex) 學(xué)生表
course(c#,cname,t#) 課程表
sc(s#,c#,score) 成績(jī)表
teacher(t#,tname) 教師表
問(wèn)題：
1、查詢“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；
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、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
select s#,avg(score)
from sc
group by s# having avg(score) >60;
3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；
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、查詢姓“李”的老師的個(gè)數(shù)；
select count(distinct(tname))
from teacher
where tname like '李%';
5、查詢沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名；
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、查詢學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名；
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、查詢學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；
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、查詢課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名；
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、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名；
select s#,sname
from student
where s# not in (select student.s# from student,sc where s.s#=sc.s# and score>60);
10、查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；
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、查詢至少有一門課與學(xué)號(hào)為“1001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；
select s#,sname from student,sc where student.s#=sc.s# and c# in select c# from sc where s#='1001';
12、查詢至少學(xué)過(guò)學(xué)號(hào)為“001”同學(xué)所有一門課的其他同學(xué)學(xué)號(hào)和姓名；
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”表中“葉平”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；
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”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名；
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、刪除學(xué)習(xí)“葉平”老師課的sc表記錄；
delect sc
from course ,teacher
where course.c#=sc.c# and course.t#= teacher.t# and tname='葉平';
16、向sc表中插入一些記錄，這些記錄要求符合以下條件：沒(méi)有上過(guò)編號(hào)“003”課程的同學(xué)學(xué)號(hào)、2、
號(hào)課的平均成績(jī)；
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、按平均成績(jī)從高到低顯示所有學(xué)生的“數(shù)據(jù)庫(kù)”、“企業(yè)管理”、“英語(yǔ)”三門的課程成績(jī)，按如下形式顯示： 學(xué)生id,,數(shù)據(jù)庫(kù),企業(yè)管理,英語(yǔ),有效課程數(shù),有效平均分
select s# as 學(xué)生id
,(select score from sc where sc.s#=t.s# and c#='004') as 數(shù)據(jù)庫(kù)
,(select score from sc where sc.s#=t.s# and c#='001') as 企業(yè)管理
,(select score from sc where sc.s#=t.s# and c#='006') as 英語(yǔ)
,count(*) as 有效課程數(shù), avg(t.score) as 平均成績(jī)
from sc as t
group by s#
order by avg(t.score)
18、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程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#
group by ir.c#
);
19、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序
select t.c# as 課程號(hào),max(course.cname)as 課程名,isnull(avg(score),0) as 平均成績(jī)
,100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分?jǐn)?shù)
from sc t,course
where t.c#=course.c#
group by t.c#
order by 100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) desc
20、查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用1行顯示): 企業(yè)管理（001），馬克思（002），oo¨ （003），數(shù)據(jù)庫(kù)（004）
select sum(case when c# ='001' then score else 0 end)/sum(case c# when '001' then 1 else 0 end) as 企業(yè)管理平均分
,100 * sum(case when c# = '001' and score >= 60 then 1 else 0 end)/sum(case when c# = '001' then 1 else 0 end) as 企業(yè)管理及格百分?jǐn)?shù)
,sum(case when c# = '002' then score else 0 end)/sum(case c# when '002' then 1 else 0 end) as 馬克思平均分
,100 * sum(case when c# = '002' and score >= 60 then 1 else 0 end)/sum(case when c# = '002' then 1 else 0 end) as 馬克思及格百分?jǐn)?shù)
,sum(case when c# = '003' then score else 0 end)/sum(case c# when '003' then 1 else 0 end) as uml平均分
,100 * sum(case when c# = '003' and score >= 60 then 1 else 0 end)/sum(case when c# = '003' then 1 else 0 end) as uml及格百分?jǐn)?shù)
,sum(case when c# = '004' then score else 0 end)/sum(case c# when '004' then 1 else 0 end) as 數(shù)據(jù)庫(kù)平均分
,100 * sum(case when c# = '004' and score >= 60 then 1 else 0 end)/sum(case when c# = '004' then 1 else 0 end) as 數(shù)據(jù)庫(kù)及格百分?jǐn)?shù)
from sc
21、查詢不同老師所教不同課程平均分從高到低顯示
select max(z.t#) as 教師id,max(z.tname) as 教師姓名,c.c# as 課程ｉｄ,max(c.cname) as 課程名稱,avg(score) as 平均成績(jī)
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、查詢?nèi)缦抡n程成績(jī)第 3 名到第 6 名的學(xué)生成績(jī)單：企業(yè)管理（001），馬克思（002），uml （003），數(shù)據(jù)庫(kù)（004）
[學(xué)生id],[學(xué)生姓名],企業(yè)管理,馬克思,uml,數(shù)據(jù)庫(kù),平均成績(jī)
select distinct top 3
sc.s# as 學(xué)生學(xué)號(hào),
student.sname as 學(xué)生姓名 ,
t1.score as 企業(yè)管理,
t2.score as 馬克思,
t3.score as uml,
t4.score as 數(shù)據(jù)庫(kù),
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
top 15 with 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、統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):課程id,課程名稱,[100-85],[85-70],[70-60],[ <60]
select sc.c# 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 -]
from sc,course
where sc.c#=course.c#
group by sc.c#,cname;
24、查詢學(xué)生平均成績(jī)及其名次
select 1+(select count( distinct 平均成績(jī))
from (select s#,avg(score) as 平均成績(jī)
from sc
group by s#
) as t1
where 平均成績(jī) > t2.平均成績(jī)) as 名次,
s# as 學(xué)生學(xué)號(hào),平均成績(jī)
from (select s#,avg(score) 平均成績(jī)
from sc
group by s#
) as t2
order by 平均成績(jī) desc;
25、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)
select t1.s# as 學(xué)生id,t1.c# as 課程id,score as 分?jǐn)?shù)
from sc t1
where score in (select top 3 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;
26、查詢每門課程被選修的學(xué)生數(shù)
select c#,count(s#) from sc group by c#;
27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名
select sc.s#,student.sname,count(c#) as 選課數(shù)
from sc ,student
where sc.s#=student.s# group by sc.s# ,student.sname having count(c#)=1;
28、查詢男生、女生人數(shù)
select count(ssex) as 男生人數(shù) from student group by ssex having ssex='男';
select count(ssex) as 女生人數(shù) from student group by ssex having ssex='女'；
29、查詢姓“張”的學(xué)生名單
select sname from student where sname like '張%';
30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
select sname,count(*) from student group by sname having count(*)>1;
31、1981年出生的學(xué)生名單(注：student表中sage列的類型是datetime)
select sname, convert(char (11),datepart(year,sage)) as age
from student
where convert(char(11),datepart(year,sage))='1981';
32、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列
select c#,avg(score) from sc group by c# order by avg(score),c# desc ;
33、查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
select sname,sc.s# ,avg(score)
from student,sc
where student.s#=sc.s# group by sc.s#,sname having avg(score)>85;
34、查詢課程名稱為“數(shù)據(jù)庫(kù)”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
select sname,isnull(score,0)
from student,sc,course
where sc.s#=student.s# and sc.c#=course.c# and course.cname='數(shù)據(jù)庫(kù)'and score <60;
35、查詢所有學(xué)生的選課情況；
select sc.s#,sc.c#,sname,cname
from sc,student,course
where sc.s#=student.s# and sc.c#=course.c# ;
36、查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)；
select distinct student.s#,student.sname,sc.c#,sc.score
from student,sc
where sc.score>=70 and sc.s#=student.s#;
37、查詢不及格的課程，并按課程號(hào)從大到小排列
select c# from sc where scor e <60 order by c# ;
38、查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';
39、求選了課程的學(xué)生人數(shù)
select count(*) from sc;
40、查詢選修“葉平”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)
select student.sname,score
from student,sc,course c,teacher
where student.s#=sc.s# and sc.c#=c.c# and c.t#=teacher.t# and teacher.tname='葉平' and sc.score=(select max(score)from sc where c#=c.c# );
41、查詢各個(gè)課程及相應(yīng)的選修人數(shù)
select count(*) from sc group by c#;
42、查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)
select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c# <>b.c# ;
43、查詢每門功成績(jī)最好的前兩名
select t1.s# as 學(xué)生id,t1.c# as 課程id,score as 分?jǐn)?shù)
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、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò)10人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列
select c# as 課程號(hào),count(*) as 人數(shù)
from sc
group by c#
order by count(*) desc,c#
45、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)
select s#
from sc
group by s#
having count(*) > = 2
46、查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名
select c#,cname
from course
where c# in (select c# from sc group by c#)
47、查詢沒(méi)學(xué)過(guò)“葉平”老師講授的任一門課程的學(xué)生姓名
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、查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)
select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score <60 group by s# having count(*)>2)group by s#;
49、檢索“004”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)
select s# from sc where c#='004'and score <60 order by score desc;
50、刪除“002”同學(xué)的“001”課程的成績(jī)
delete from sc where s#='001'and c#='001';
公考類
招聘類
各類考試
