數(shù)據(jù)庫sql查詢語句練習(xí)2015.6.6修正

1、現(xiàn)在有一教學(xué)管理系統(tǒng)，具體的關(guān)系模式如下：Student (no, name, sex, birthday, class)Teacher (no, name, sex, birthday, prof, depart)Course (cno, cname, tno)Score (no, cno, degree)其中表中包含如下數(shù)據(jù)：Course表：Score表：Student表：Teacher表：根據(jù)上面描述完成下面問題：（注意：注意保存腳本，尤其是DDL和DML，以便進(jìn)行數(shù)據(jù)還原）DDL1. 寫出上述表的建表語句。命令：DML2. 給出相應(yīng)的INSERT語句來完成題中給出數(shù)據(jù)的插入。命令：單表

2、查詢3. 以class降序輸出student的所有記錄（student表全部屬性）命令： select *from student order by class desc;4. 列出教師所在的單位depart（不重復(fù)）。命令： select distinct depart from teacher; 5. 列出student表中所有記錄的name、sex和class列命令： select name,sex,class from student;6. 輸出student中不姓王的同學(xué)的姓名。命令： 方法一：select name from student where name not in (s

3、elect name from student where name like'王%');方法二： select name from student where name not like'王%'7. 輸出成績?yōu)?5或86或88或在60-80之間的記錄（no,cno,degree）命令：select no,cno,degree from score where degree=85 or degree=86 or degree=88 or degree between 60 and 80; 8. 輸出班級(jí)為95001或性別為女 的同學(xué)（student表全部屬性）命

4、令：select *from student where class='95001' or sex='女'9. 以cno升序、degree降序輸出score的所有記錄。（score表全部屬性）命令： select *from score order by cno asc,degree desc;10. 輸出男生人數(shù)及這些男生分布在多少個(gè)班級(jí)中命令： select count(*) as 男生人數(shù),count(distinct class) as 班級(jí)數(shù) from student where sex='男'11. 列出存在有85分以上成績的課程編號(hào)

5、。命令： select distinct cno from score where degree>85; 12. 輸出95001班級(jí)的學(xué)生人數(shù)命令： select count(*) from student where class=95001;13. 輸出3-105號(hào)課程的平均分命令：select avg(degree) from score where cno='3-105'14. 輸出student中最大和最小的birthday日期值命令：select max(birthday),min(birthday) from student;15. 顯示95001和95004

6、班全體學(xué)生的全部個(gè)人信息（不包括選課）。（student表全部屬性）命令： select *from student where class=95001 or class=95004;聚合查詢16. 輸出至少有5個(gè)同學(xué)選修的并以3開頭的課程的課程號(hào)，課程平均分，課程最高分，課程最低分。命令：方法一：Select cno,avg(degreee),max(degreee),min(degreee) from score where cno in(select cno from score where cno like "3%" group by cno having coun

7、t(cno)>=5);方法二：select cno,avg(degree),max(degree),min(degree) from score group by cno having cno like '3%' and count(cno)>=5;17. 輸出所選修課程中最低分大于70分且最高分小于90分的學(xué)生學(xué)號(hào)及學(xué)生姓名命令： 子查詢：Select no,name from student where no in(select no from score group by no having min(degree)>70 and max(degree)&

8、lt;90);多表查詢：select student.no,name from student join score on student.no=score.no group by student.no,name having max(score.degree)<90 and min(score.degree)>70;18. 顯示所教課程選修人數(shù)多于5人的教師姓名命令： Select name from teacher where no in (select tno from course where cno in(select cno from score group by cn

9、o having count(cno)>5);19. 輸出95001班級(jí)所選課程的課程號(hào)和平均分命令：select cno,avg(degree) from score,student where score.no=student.no and class=95001 group by cno;20. 輸出至少有兩名男同學(xué)的班級(jí)編號(hào)。命令：select class from student where sex='男' group by class having count(sex)>=2;多表查詢21. 列出與108號(hào)同學(xué)同年出生的所有學(xué)生的學(xué)號(hào)、姓名和生日命令：s

10、elect no,name,birthday from student where year(birthday)=(select year(birthday) from student where no=108);22. 列出存在有85分以上成績的課程名稱命令： select cname from course where cno in( select cno from score where degree>85);23. 列出“計(jì)算機(jī)系”教師所教課程的成績表（課程編號(hào)，課程名，學(xué)生名，成績）。命令： select o,cname,,degreee from te

11、acher join course on teacher.no=course.tno join score on o=o join student on score.no=student.no where teacher.depart='計(jì)算機(jī)系'24. 列出所有可能的“計(jì)算機(jī)系”與“電子工程系”不同職稱的教師配對信息，要求輸出每個(gè)老師的姓名（name）和（職稱）命令：select ,f,,f from (select name,prof,depart from teacher where depart='計(jì)算機(jī)系'

12、or depart='電子工程系') a join (select name,prof,depart from teacher where depart='電子工程系' or depart='計(jì)算機(jī)系') b on not f=f and not a.depart=b.depart;25. 列出所有處于不同班級(jí)中，但具有相同生日的學(xué)生，要求輸出每個(gè)學(xué)生的學(xué)號(hào)和姓名。命令：select a.no,,b.no, from student a join student b on not a.class=b.c

13、lass and a.birthday=b.birthday;26. 顯示張三教師任課的學(xué)生姓名，課程名，成績命令：select ,cname,degree from teacher join course on teacher.no=course.tno join score on o=o join student on score.no=student.no where ='張三'27. 列出所講課已被選修的教師的姓名和系別命令：select distinct name,depart from teacher join cou

14、rse on teacher.no=course.tno join score on o=o;28. 輸出所有學(xué)生的name、no和degree。（degree為空的不輸出和為空的輸出兩種情況）。命令：方法一：select name,student.no,degree from student left join score on student.no=score.no;方法二：select name,student.no,degree from student join score on student.no=score.no;29. 列出所有任課教師的name和depart。（從課程選修和

15、任課兩個(gè)角度考慮）命令： 方法一：select distinct name,depart from teacher join course on teacher.no=course.tno; 方法二：select distinct name,depart from teacher join course on teacher.no=course.tno join score on o=o;30. 輸出男教師所上課程名稱。命令： select cname from teacher join course on teacher.no=course.tno where teacher.sex=

16、9;男'31. 出與“李軍”同性別的所有同學(xué)的name。命令：select name from student where sex=(select sex from student where name='李軍');32. 輸出選修“數(shù)據(jù)結(jié)構(gòu)”課程的男同學(xué)的成績。命令：select degree from score join student on score.no=student.no join course on o=o where sex='男' and cname='數(shù)據(jù)結(jié)構(gòu)'33. 列出選修編號(hào)為3-105課程并且該門課程成績比

17、課程 3-111的最高分要高的cno,no和degree。命令：select o,score.no,degree from course join score on o=So join student on score.no=student.no where o='3-105' and score.degree>(select max(degree) from score where o='3-111');子查詢34. 輸出score中成績最高的學(xué)號(hào)和課程號(hào)命令： select no,cno from score where degree in(selec

18、t max(degree) from score);35. 輸出選修3-105課程，其成績高于109號(hào)同學(xué)在此課程所得成績的所有同學(xué)的學(xué)號(hào)，姓名。命令：select student.no,name from student join score on student.no =score.no where o='3-105' and degree>(select degree from score where no='109' and cno='3-105');36. 列出成績比該課程平均成績低的同學(xué)的學(xué)號(hào)，成績和該門課的平均成績命令：37. 列出沒有實(shí)際授課的教師的姓名和系別命令：38. 列出選修了編號(hào)為3-105課程且其成績高于4-109課程最高成績的同學(xué)的 課程編號(hào)，學(xué)號(hào)和成績命令：select cno,no,degree from score where cno='3-105