1、1、查詢以DB_開頭，且倒數(shù)第三個字符為s的課程的詳細情況select *from coursewhere cname like DB_%s_2、查詢名字中第二個字為“陽”的學生姓名和學號及選修的課程號、課程名select student.sno ,student.sname ,o,cnamefrom student,course,scwhere sname like _陽%and student.sno=sc.sno and sc.Cno=o3、列出選修了數(shù)學或大學英語的學生學號、姓名、select student.sno,sname,sdept,sc.Cno,c

2、name,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sno in(select sc.sno from sc,course where (cname=大學英語or cname=數(shù)學)and sc.Cno=ogroup by sc.sno)select student.sno,sname,sdept,cno,gradefrom student,scwhere Cno in (select Cno from coursewhere cname=數(shù)學or cnam

3、e=大學英語)and sc.sno=student.sno4、查詢缺少成績的所有學生的詳細情況；select *from student,scwhere Grade is null and student.sno=sc.sno5、查詢與張力(假設姓名唯一)年齡不同的所有學生的信息；select *from studentwhere sage (select sagefrom studentwhere sname=張力)6、查詢所選課程的平均成績大于張力的平均成績的學生學號、姓名及平均成績select student.sno,sname,平均成績=AVG(grade)from student ,

4、scwhere student.sno=sc.snogroup by student.sno,snamehaving AVG(Grade)(select AVG(Grade)from sc,student where sname=張力and student.sno=sc.sno)7、按照“學號，姓名，所在院系，已修學分”的順序列出學生學分的獲得情況。其中已修學分為考試已經及格的課程學分之和；select student.sno,sname,sdept,已修學分=SUM(ccredit)from student,sc,coursewhere Grade60and student.sno=sc.s

5、no and sc.Cno=ogroup by student.sno,sname,sdept8、列出只選修一門課程的學生的學號、姓名、院系及成績select student.sno,sname,sdept,Cno,gradefrom student,scwhere student.sno=sc.sno and sc.sno in (select sno from sc group by sc.sno having COUNT(distinct Cno)=1)9、查找選修了至少一門和張力選修課程一樣的學生的學號、姓名及課程號；select student.sno,sname,

6、cnofrom student,scwhere student.sno=sc.sno and sc.sno in(select sc.sno from sc ,student where cno in(select cno from student,sc where sname=張力and student.sno=sc.sno) )10、只選修“數(shù)據(jù)庫”和“數(shù)據(jù)結構”兩門課程的學生的基本信息；select student.sno,snamefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sn

7、o in(select sc.sno from sc,course where (cname=數(shù)據(jù)庫or cname=數(shù)學)and sc.Cno=ogroup by sc.snohaving COUNT(*)=2)group by student.sno,snamehaving COUNT(*)=211、至少選修“數(shù)據(jù)庫”或“數(shù)據(jù)結構”課程的學生的基本信息；只包括其中一門或兩門：select student.sno,sname,sdept,sc.Cno,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and

8、sc.Cno=o and sc.sno in(select sc.sno from sc,course where (cname=數(shù)據(jù)庫or cname=數(shù)據(jù)結構)and sc.Cno=o )兩門課全部包括：select student.sno,sname,sdept,sc.Cno,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sno in(select sc.sno from sc,course where (cname=數(shù)據(jù)庫o

9、r cname=數(shù)據(jù)結構)and sc.Cno=ogroup by sc.snohaving COUNT(sc.Cno)=2)12、列出所有課程被選修的詳細情況，包括課程號、課程名、學號、姓名及成績select o,cname,student.sno,sname,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=oorder by o13、查詢只被一名學生選修的課程的課程號、課程名select o,cnamefrom sc,coursew

10、here sc.Cno=ogroup by sc.Cno,cnamehaving COUNT(sc.sno)=114、檢索所學課程包含學生張向東所學課程的學生學號、姓名至少包含一門張向東所選都的課程：select student.sno,sname,cno from student,scwhere student.sno=sc.sno and student.sno in (select sno from sc,course where sc.Cno=o and sc.Cno in (select cno from sc,student where snam

11、e=張向東and student.sno=sc.sno)包括張向東所選的全部課程：select *from studentwhere sno in (select sno from sc,course where sc.Cno=o and sc.Cno in (select cno from sc,student where sname=張向東and student.sno=sc.sno)group by sc.snohaving COUNT(*)=(select COUNT(cno)from sc,student where sname=張向東and student.sno

12、=sc.sno)15、使用嵌套查詢列出選修了“數(shù)據(jù)結構”課程的學生學號和姓名select sno,snamefrom studentwhere sno in (select sno from sc,course where cname=數(shù)據(jù)結構and sc.Cno=o)16、使用嵌套查詢查詢其它系中年齡小于CS系的某個學生的學生姓名、年齡和院系；select sname,sage,sdeptfrom studentwhere sageany(select sage from student where sdept=CS )and sdeptCS17、使用ANY、ALL 查詢，

13、列出其他院系中比CS系所有學生年齡小的學生select *from studentwhere sageall(select sage from student where sdept=CS)and sdeptCS;18、分別使用連接查詢和嵌套查詢，列出與張力在一個院系的學生的信息；select *from studentwhere sdept in(select sdept from student where sname=張力)19、使用集合查詢列出CS系的學生以及性別為女的學生名單select *from studentwhere sdept=CSintersectselect *from studentwhere ssex=女20、使用集合查詢列出CS系的學生與年齡不大于19歲的學生的交集、差集select *from studentwhere sdept=CSintersectselect *from studentwhere sage19select *from studentwhere sdept=CSexceptselect *from studentwhere sage1921、使用集合查詢列出選修課程1的