試驗七部分答案.doc

試驗七 Sql 查詢語句目的: 掌握 Select 查詢語句。一 單表1查詢年齡在19至21歲之間的女生的學(xué)號,姓名,年齡,按年齡從大到小排列。select sno,sname,sage from studentwhere sage between 19 and 21 and ssex=女 order by sage desc2查詢姓名中第戎2個字為“明”字的學(xué)生學(xué)號、性別。select sname ,ssex from student where sname like _明%3查詢 1001課程沒有成績的學(xué)生學(xué)號、課程號select sno,cno from sc where grade is null and cno=10014查詢JSJ 、SX、WL 系的學(xué)生學(xué)號,姓名，結(jié)果按系及學(xué)號排列select sno,sname from student where sdept in (JSJ,SX,WL)order by sdept,sno5按10分制查詢學(xué)生的sno,cno,10分制成績 （1-10分 為1 ，11-20分為2 ，30-39分為3，。90-100為10）select sno , cno , grade/10.0+1 as level from sc6查詢 student 表中的學(xué)生共分布在那幾個系中。（distinct）select distinct sdept from student7查詢0001號學(xué)生1001，1002課程的成績。 Select cno from sc where sno=0001 and (cno=1001 or cno=1002)二 統(tǒng)計1查詢姓名中有“明”字的學(xué)生人數(shù)。select count(*) from student where sname like %明%2計算JSJ系的平均年齡及最大年齡。 Select avg(sage) , max(sage) from student Where sdept=JSJ3計算每一門課的總分、平均分，最高分、最低分，按平均分由高到低排列 select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno order by avg(grade) desc4 計算 1001,1002 課程的平均分。Select cno , avg(grade) from sc where cno in (1001,1002)Group by cno5 查詢平均分大于80分的學(xué)生學(xué)號及平均分select sc.sno , avg(grade) from scgroup by sc.snohaving avg(grade)806 統(tǒng)計選修課程超過 2 門的學(xué)生學(xué)號 select sno from sc group by sno having count(*)27 統(tǒng)計有10位成績大于85分以上的課程號。Select cno from scwhere grade85group by cno having count(*) =108 統(tǒng)計平均分不及格的學(xué)生學(xué)號select sno from sc group by sno having avg(grade)609 統(tǒng)計有大于兩門課不及格的學(xué)生學(xué)號select sno from sc where grade2三 連接1查詢 JSJ 系的學(xué)生選修的課程號 select cno from student,sc where student.sno=sc.sno and sdept=JSJ2查詢選修1002 課程的學(xué)生的學(xué)生姓名 (不用嵌套及嵌套2種方法）a: select sname from student,sc where student.sno = sc.sno and cno=1002b: select sname from student where sno in (select sno from sc where cno=1002)3查詢數(shù)據(jù)庫原理不及格的學(xué)生學(xué)號及成績select sno,grade from sc ,coursewhere o=o and cname=數(shù)據(jù)庫原理4查詢選修“數(shù)據(jù)庫原理”課且成績 80 以上的學(xué)生姓名(不用嵌套及嵌套2種方法）a: select sname from student , sc , course where student.sno=sc.sno and o = o andgrade80 and cname=數(shù)據(jù)庫原理b: select sname from student where sno in ( select sno from sc where grade80 and cno in ( select cno from course where cname=數(shù)據(jù)庫原理) )5查詢平均分不及格的學(xué)生的學(xué)號，姓名,平均分。select sno, max(sname) , avg(grade) as avggrade from sc , studentwhere student.sno=sc.snogroup by student.snohaving avg(grade) 75)B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=女 Group by student.sno having avg(grade)757查詢男學(xué)生學(xué)號、姓名、課程號、成績。(一門課程也沒有選修的男學(xué)生也要列出，不能遺漏)select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=男四 嵌套、相關(guān)及其他1 查詢平均分不及格的學(xué)生人數(shù)select count(*) from student where sno in (select sno from sc group by sno having avg(grade)=all ( select avg(grade) from sc group by sno )*4 查詢沒有選修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 sno=student.sno and cno=o ) )5 查詢1002課程第一名的學(xué)生學(xué)號（2種方法）a: select top 1 sno from sc cno=1002 order by grade descb: select sno from sc where cno=1002 andgrade =all (select grade from sc where cno=1002)6 查詢平均分前三名的學(xué)生學(xué)號select top 3 sno from sc group by sno order by avg(grade) desc7 查詢 JSJ 系的學(xué)生與年齡不大于19歲的學(xué)生的差集a: select * from student where sdept=JSJ and sage19b: select * from student where sdept=JSJ except select * from student where sage90unionselect sno,sname from student where sno in ( select sno from sc group by sno