實(shí)驗(yàn)六答案數(shù)據(jù)庫(kù)綜合查詢

1、1、查詢以 DB_開(kāi)頭，且倒數(shù)第三個(gè)字符為s的課程的詳細(xì)情況select * from course where cname like DB_%s_2、查詢名字中第二個(gè)字為“陽(yáng)”的學(xué)生姓名和學(xué)號(hào)及選修的課程號(hào)、課程名 select , ,cname from student ,course,scwhere sname like _陽(yáng) %and = and =3、列出選修了數(shù)學(xué)或大學(xué)英語(yǔ)的學(xué)生學(xué)號(hào)、姓名、 select ,sname,sdept,cname,grade from student ,sc,course where = and =andin (select from sc,cours

2、e where (cn ame-大學(xué)英語(yǔ)or cn ame=數(shù)學(xué))a nd = group by select ,sname ,sdept ,cno,gradefrom student ,scwhere Cnoin (select Cnofrom coursewhere cname =數(shù)學(xué)or cname=大學(xué)英語(yǔ))and =4、查詢?nèi)鄙俪煽?jī)的所有學(xué)生的詳細(xì)情況； select * from student ,scwhere Grade is null and =5、查詢與張力 (假設(shè)姓名唯一 )年齡不同的所有學(xué)生的信息； select * from student where sage(se

3、lect sage from student where sname= 張力 )6、查詢所選課程的平均成績(jī)大于張力的平均成績(jī)的學(xué)生學(xué)號(hào)、姓名及平均成績(jī)select ,sname,平均成績(jī)=AVG(grade)from student ,sc where = group by ,snamehav in g AVG(Grade)(select AVG(Grade)from sc,stude nt where sn ame=張力and-7、按照“學(xué)號(hào)，姓名，所在院系，已修學(xué)分”的順序列出學(xué)生學(xué)分的獲得情況。 其中已修學(xué)分為考試已經(jīng)及格的課程學(xué)分之和；select ,sname,sdept,已修學(xué)分=

4、SUM(ccredit) from student ,sc,coursewhere Grade60and =and = group by ,sname,sdept8、列出只選修一門課程的學(xué)生的學(xué)號(hào)、姓名、院系及成績(jī)select ,sname,sdept,Cno,gradefrom student ,sc where =and in (selectsno from scgroup by having COUNT(distinct Cno)=1)9、查找選修了至少一門和張力選修課程一樣的學(xué)生的學(xué)號(hào)、姓名及課程號(hào)； select ,sname,cno from student ,sc where =

5、andin(select from sc , studentwhere cno in(select eno from student ,sc where sname=張力and =)10、只選修“數(shù)據(jù)庫(kù)”和“數(shù)據(jù)結(jié)構(gòu)”兩門課程的學(xué)生的基本信息； select ,sname from student ,sc,course where = and =andin (select from sc,course where (cn ame=數(shù)據(jù)庫(kù)or c name=數(shù)學(xué))a nd = group byhaving COUNT(*)=2)group by ,snamehaving COUNT(*)= 21

6、1、至少選修“數(shù)據(jù)庫(kù)”或“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生的基本信息； 只包括其中一門或兩門：select ,sname,sdept,cname,grade from student ,sc,course where = and =andin(select from sc,course where (cname =數(shù)據(jù)庫(kù) or cname =數(shù)據(jù)結(jié)構(gòu) )and =) 兩門課全部包括：select ,sname,sdept,cname,grade from student ,sc,coursewhereand =andin(select from sc,course where (cname =數(shù)據(jù)庫(kù) or

7、 cname =數(shù)據(jù)結(jié)構(gòu) )and = group byhaving COUNT=2)12、列出所有課程被選修的詳細(xì)情況，包括課程號(hào)、課程名、學(xué)號(hào)、姓名及成績(jī) select ,cname,sname,gradefrom student ,sc,coursewhere =and = order by13、查詢只被一名學(xué)生選修的課程的課程號(hào)、課程名 select ,cname from sc,course where = group by ,cname having COUNT-114、檢索所學(xué)課程包含學(xué)生張向東所學(xué)課程的學(xué)生學(xué)號(hào)、姓名至少包含一門張向東所選都的課程：select ,sname,c

8、no from student,scwhere =andin (select sno from sc,coursewhere - andin (select cno from sc,student where sname=張向東and =)包括張向東所選的全部課程：select *from student where sno in (select sno from sc,coursewhere =and in (select cno from sc,student where sname-張向東and - group byhaving COUNT(*)=(select COUNl(cno)fr

9、om sc,student where sname-張向東and =)15、使用嵌套查詢列出選修了“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生學(xué)號(hào)和姓名select sno,snamefrom student where sno in (selectsno from sc,course where cname=數(shù)據(jù)結(jié)構(gòu)and =16、使用嵌套查詢查詢其它系中年齡小于CS 系的某個(gè)學(xué)生的學(xué)生姓名、年齡和院系；select sname,sage,sdept from student where sageany(select sage from student where sdept=CS)and sdeptCS17、使

10、用ANY ALL查詢，列出其他院系中比CSS所有學(xué)生年齡小的學(xué)生 select * from student where sageall(select sagefrom student where sdept=CS)and sdeptCS;18、分別使用連接查詢和嵌套查詢，列出與張力在一個(gè)院系的學(xué)生的信息；select * from studentwhere sde pt in (select sde pt from stude nt where sn ame=張力)19、使用集合查詢列出CS系的學(xué)生以及性別為女的學(xué)生名單 select * from student where sdept =CS intersect select * from student where ssex=女20、使用集合查詢列出CS系的學(xué)生與年齡不大于19歲的學(xué)生的交集、差集 select * from student where sdept =CS intersect select * from student where sage19 select * from student where sdept =CS except select * from student where sage1921、 使用集合查詢列出選修課程 1的學(xué)生集合與選修課程 2的學(xué)生集合的交集； s