SQL數(shù)據(jù)庫(kù)原理與設(shè)計(jì)-試驗(yàn)七“查詢語(yǔ)句”答案

1、- 一 單表- 1查詢年齡在19至21歲之間的女生的學(xué)號(hào),姓名,年齡,按年齡從大到小排列。select sno,sname,sage from Student order by sage asc;- 2查詢姓名中第2個(gè)字為“明”字的學(xué)生學(xué)號(hào)、性別。select sno,ssex from Student where sname like _明%;- 3查詢 1001課程沒(méi)有成績(jī)的學(xué)生學(xué)號(hào)、課程號(hào)select sno,cno from sc where Grade is null ;- 4查詢JSJ 、SX、WL 系的年齡大于25歲的學(xué)生學(xué)號(hào),姓名，結(jié)果按系及學(xué)號(hào)排列select sno,sna

2、me from Student where Sdept in(JSJ,SX,WL) order by Sdept,sno;- 5按10分制查詢學(xué)生的sno,cno,10分制成績(jī)（1-10分為1 ，11-20分為2。90-100為10）select sno,cno,cast(Grade/10 as int) as grade from sc;- 6查詢 student 表中的學(xué)生共分布在那幾個(gè)系中。（distinct）select distinct Sdept from Student;- 7查詢0001號(hào)學(xué)生1001，1002課程的成績(jī)。select grade from sc where s

3、no=0001 and cno in(1001,1002);- 二 統(tǒng)計(jì)- 1查詢姓名中有“明”字的學(xué)生人數(shù)。select count(sno) as countnum from Student where sname like %明%;- 2計(jì)算JSJ系的平均年齡及最大年齡。select avg(sage) as avge,max(sage) as maxage from Student where Sdept in(JSJ) group by sdept;- 3查詢學(xué)生中姓名為張明、趙英的人數(shù)select count(sno) from Student where sname in(張明,

4、趙英);- 4計(jì)算每一門課的總分、平均分，最高分、最低分，按平均分由高到低排列select sum(grade) as sum, sum(grade)/count(sno) as avgg,max(Grade) as maxg,min(grade) as ming from sc group by cno;- 5 計(jì)算 1001,1002 課程的平均分。select sum(grade)/count(cno) from SC group by cno having cno in(1001,1002);- 6 查詢平均分大于80分的學(xué)生學(xué)號(hào)及平均分select sno,sum(Grade)/co

5、unt(sno) from SC group by sno having sum(Grade)/count(sno) 80- 7 統(tǒng)計(jì)選修課程超過(guò) 2 門的學(xué)生學(xué)號(hào)select sno from sc group by sno having count(cno)2- 8 統(tǒng)計(jì)有10位成績(jī)大于85分以上的課程號(hào)。select cno from sc where Grade85 group by cno having count(cno)10;- 9 統(tǒng)計(jì)平均分不及格的學(xué)生學(xué)號(hào)select sno from sc group by sno having sum(Grade)/count(sno)6

6、0;- 10 統(tǒng)計(jì)有大于兩門課不及格的學(xué)生學(xué)號(hào)select sno from sc where Grade2;- 三 連接- 1查詢 JSJ 系的學(xué)生選修的課程號(hào)select cno from sc where sno in (select sno from Student where Sdept = JSJ);- 2查詢選修1002 課程的學(xué)生的學(xué)生姓名 (不用嵌套及嵌套2種方法）select sname from Student,sc where 1=1 and Student.sno = sc.sno and cno=1002;select sname from Student join

7、 sc on cno=1002 and Student.sno = sc.sno;select sname from Student where sno in(select sno from sc where cno in(1002);- 3查詢數(shù)據(jù)庫(kù)原理不及格的學(xué)生學(xué)號(hào)及成績(jī)select Student.sno,grade from Student,sc,course where Student.sno=sc.sno and o = o and cname=數(shù)據(jù)庫(kù)原理 and sc.Grade80;select sname from Student join sc on Student.Sn

8、o = SC.Sno and sc.Grade80 and o in(select cno from course where cname=數(shù)據(jù)庫(kù)原理);- 5查詢平均分不及格的學(xué)生的學(xué)號(hào)，姓名,平均分。select max(Student.sno),max(sname),sum(Grade)/count(sc.sno) from Student,sc where Student.sno = sc.sno group by sc.sno having sum(Grade)/count(sc.sno)75);- 7查詢男學(xué)生學(xué)號(hào)、姓名、課程號(hào)、成績(jī)。(一門課程也沒(méi)有選修的男學(xué)生也要列出，不能遺漏

9、)select Student.sno,sname,cno,grade from Student left outer join sc on Student.Sno = SC.Sno and Ssex = 男;- 四 嵌套、相關(guān)及其他- 1 查詢平均分不及格的學(xué)生人數(shù)select count(sno) from Student where sno in (select sno from sc group by sno having sum(Grade)/count(sno)=all(select sum(grade)/count(sno) from sc group by sno);- *4

10、查詢沒(méi)有選修1001，1002課程的學(xué)生姓名。select sname from Student where not exists(select * from course where cno in(1001,1002) and not exists(select * from SC where cno = o and Student.sno = sc.sno)- 5 查詢1002課程第一名的學(xué)生學(xué)號(hào)（2種方法）select top 1 sno from sc where cno = 1002 order by Grade desc ;select sno from sc where cno=

11、1002 and grade =all(select grade from sc where cno=1002)- 6 查詢平均分前三名的學(xué)生學(xué)號(hào)select top 3 sno from sc order by Grade desc;- 7 查詢 JSJ 系的學(xué)生與年齡不大于19歲的學(xué)生的差集select * from Student where Sdept=JSJexceptselect * from Student where sage90 and cno=1001unionselect sno,sname from Student where sno in(select sno from sc group by sno having su