1、查詢“c001”課程比“c002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)
select a.* from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score;
2含滴、查詢平均成績(jī)大于60 分的同學(xué)的學(xué)號(hào)和平均成績(jī)
select sno,avg(score) from sc
group by sno
having avg(score) >60;
3、查詢所有同學(xué)的學(xué)號(hào)丐巫、姓名谈况、選課數(shù)勺美、總成績(jī)
解法一
select a.sno,a.sname,count(b.cno),sum(b.score) from student a
join sc b on a.sno=b.sno
group by sno;
解法二
select a.*,s.sname from
(select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno
4、查詢姓“劉”的老師的個(gè)數(shù)
select count(*) from teacher
where tname like '劉%';
5碑韵、查詢沒(méi)學(xué)過(guò)“諶燕”老師課的同學(xué)的學(xué)號(hào)赡茸、姓名
#學(xué)過(guò)諶燕老師課的同學(xué)
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join(select a.cno from course a
join teacher b on
a.tno = b.tno
where tname='諶燕') c on
b.cno = c.cno;
#沒(méi)學(xué)過(guò)諶燕老師課的同學(xué)
select a.sno,a.sname from student a
where a.sno not in
(select distinct a.sno from sc a
join course b on a.cno=b.cno
join teacher c on c.tno=b.tno
where tname='諶燕');
6、查詢學(xué)過(guò)“c001”并且也學(xué)過(guò)編號(hào)“c002”課程的同學(xué)的學(xué)號(hào)祝闻、姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join sc c on b.sno=c.sno
where b.cno='c001' and c.cno='c002' and a.sno=c.sno;
7占卧、查詢學(xué)過(guò)“諶燕”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join(select a.cno from course a
join teacher b on
a.tno = b.tno
where tname='諶燕') c on
b.cno = c.cno;
8联喘、查詢課程編號(hào)“c002”的成績(jī)比課程編號(hào)“c001”課程低的所有同學(xué)的學(xué)號(hào)华蜒、姓名
解法一
select sno,sname from student
where sno in (select a.sno from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score);
解法二
select * from student st
join sc a on st.sno=a.sno
join sc b on st.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.score < b.score
9、查詢所有課程成績(jī)小于60 分的同學(xué)的學(xué)號(hào)豁遭、姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
join course c on c.cno=b.cno
where b.score <60;
10叭喜、查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名
select a.sno,a.sname,count(b.cno) from student a
left join sc b on a.sno=b.sno
group by a.sno,a.sname
having count(b.cno)<(select count(distinct cno) from course);
11蓖谢、查詢至少有一門(mén)課與學(xué)號(hào)為“s001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名
解法一
select distinct a.sno,a.sname from student a
join sc b on a.sno=b.sno
where b.cno in
(select cno from sc a
join student b on a.sno=b.sno
where a.sno='s001')
and a.sno<>'s001';
解法二
select st.* from student st,
(select distinct a.sno from
(select * from sc) a,
(select * from sc where sc.sno='s001') b
where a.cno=b.cno) h
where st.sno=h.sno and st.sno<>'s001'
12捂蕴、查詢至少學(xué)過(guò)學(xué)號(hào)為“s001”同學(xué)所有一門(mén)課的其他同學(xué)學(xué)號(hào)和姓名
此題解法我認(rèn)為與11題相同，但網(wǎng)上解法如下
select * from sc
left join student st
on st.sno=sc.sno
where sc.sno<>'s001'
and sc.cno in
(select cno from sc
where sno='s001')
13闪幽、把“SC”表中“諶燕”老師教的課的成績(jī)都更改為此課程的平均成績(jī)
#替換數(shù)據(jù)應(yīng)使用
update sc set score =
#“諶燕”老師教的課的平均成績(jī)
(select avg(score) from sc
join (course a,teacher c) on a.tno=c.tno and sc.cno=a.cno
and c.tname='諶燕'
group by sc.cno )
#where cno in
#'諶燕'老師教的課程
(select cno from course a
join teacher b on a.tno=b.tno
where b.tname='諶燕');
14啥辨、查詢和“s001”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名
select sno from sc
where cno in (select cno from sc where sno='s001')
#join sc b on a.cno= b.cno and a.sno='s001'
group by sno
having count(*)=
(select count(*) from sc where sno='s001');
15、刪除學(xué)習(xí)“諶燕”老師課的SC 表記錄
#刪除數(shù)據(jù)應(yīng)使用delete from sc where sc.cno in
(select cno from course a
join teacher b on a.tno=b.tno
where b.tname='諶燕');
16沟使、向SC 表中插入一些記錄委可，這些記錄要求符合以下條件：沒(méi)有上過(guò)編號(hào)“c002”課程的同學(xué)學(xué)號(hào)渊跋、“c002”號(hào)課的平均成績(jī)
#插入數(shù)據(jù)應(yīng)使用insert into sc(sno,cno,score)
#平均成績(jī)
select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
from student st,sc
where not exists
#沒(méi)有上過(guò)編號(hào)“c002”課程的同學(xué)學(xué)號(hào)
(select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
17腊嗡、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程ID，最高分拾酝，最低分
select cno,max(score),min(score) from sc
group by cno
18燕少、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序
select cno,avg(score),
sum(case when score>=60 then 1 else 0 end)/count(*)
as 及格率
from sc
group by cno
order by avg(score) ,及格率 desc;
19、查詢不同老師所教不同課程平均分從高到低顯示
select a.tno,a.tname,b.cname,c.cno,avg(c.score) from teacher a
join (course b,sc c) on a.tno=b.tno and b.cno=c.cno
group by c.cno
order by avg(score) desc
20蒿囤、統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
select sc.cno,c.cname,
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 c
where sc.cno=c.cno
group by sc.cno ,c.cname;
<b> 未解答 21客们、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)</b>
select b.cno,a.sno,a.sname,b.score from student a
join sc b on a.sno=b.sno
group by b.cno
order by b.score desc;
22、查詢每門(mén)課程被選修的學(xué)生數(shù)
select a.cno,b.cname,count(a.sno) from sc a,course b
where a.cno=b.cno
group by cno;
23材诽、查詢出只選修了一門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名
select a.sno,a.sname from student a
join sc b on a.sno=b.sno
group by b.sno
having count(b.cno) =1;
24底挫、查詢男生、女生人數(shù)
select ssex,count(sno) from student
group by ssex;
25脸侥、查詢姓“張”的學(xué)生名單
select * from student
where sname like '張%';
26建邓、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
select sname,count(*)from student
group by sname
having count(*)>1;
