SQL語句練習(xí)及答案

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. 寫出上述表的建表語句。命令：create table Student(no nvarchar(5),n

2、ame nvarchar(5),sex nvarchar(1),birthday datetime,class nvarchar(5)DML2. 給出相應(yīng)的INSERT語句來完成題中給出數(shù)據(jù)的插入。命令：單表查詢3. 以class降序輸出student的所有記錄（student表全部屬性）命令：select * from Student order by class desc4. 列出教師所在的單位depart（不重復(fù)）。命令：select distinct depart from teacher5. 列出student表中所有記錄的name、sex和class列命令：select name,

3、sex,class from student6. 輸出student中不姓王的同學(xué)的姓名。命令：select name from Student where name not like '王%'7. 輸出成績?yōu)?5或86或88或在60-80之間的記錄（no,cno,degree）命令：select * from Score where degree=85 or degree=86 or degree=88 or degree between 60 and 808. 輸出班級為95001或性別為女 的同學(xué)（student表全部屬性）命令：select * from Student

4、 where class='95001' or sex='女'9. 以cno升序、degree降序輸出score的所有記錄。（score表全部屬性）命令：select * from Score order by cno,degree desc10. 輸出男生人數(shù)及這些男生分布在多少個班級中命令：select COUNT(*),COUNT(distinct class) from Student where sex='男'11. 列出存在有85分以上成績的課程編號。命令：select distinct cno from Score where de

5、gree>8512. 輸出95001班級的學(xué)生人數(shù)命令：select COUNT(*) from Student where class='95001'13. 輸出3-105號課程的平均分命令：select AVG(DEGREE) from Score where cno='3-105'14. 輸出student中最大和最小的birthday日期值命令：select MAX(birthday),MIN(birthday) from student15. 顯示95001和95004班全體學(xué)生的全部個人信息（不包括選課）。（student表全部屬性）命令：se

6、lect * from Student where class='95001' or class='95004'聚合查詢16. 輸出至少有5個同學(xué)選修的并以3開頭的課程的課程號，課程平均分，課程最高分，課程最低分。命令：select cno,AVG(degree),MAX(degree),MIN(degree) from Score where cno like '3%'group by cno having COUNT(no)>517. 輸出所選修課程中最低分大于70分且最高分小于90分的學(xué)生學(xué)號及學(xué)生姓名命令：select studen

7、t.no, from Score inner join Student on student.no=score.no group by student.no,name having (MIN(degree)>70 and MAX(degree)<90)18. 顯示所教課程選修人數(shù)多于5人的教師姓名命令：select name from Score inner join course on o=o inner join Teacher on Teacher.no=course.tno group by teacher.no,name having COUNT(

8、*)>519. 輸出95001班級所選課程的課程號和平均分命令：select cno,AVG(degree) from Student inner join Score on student.no=score.no where student.class='95001' group by cno20. 輸出至少有兩名男同學(xué)的班級編號。命令：select class from Student inner join Score on student.no=score.no where sex='男' group by class having COUNT(di

9、stinct student.no)>1多表查詢21. 列出與108號同學(xué)同年出生的所有學(xué)生的學(xué)號、姓名和生日命令：select no,name,birthday from Student where year(birthday)=(select YEAR(birthday) from Student where no =108)22. 列出存在有85分以上成績的課程名稱命令：select cname from course inner join Score on o=o group by cname having MAX(degree)>8523. 列出“計算機(jī)系”教師所教課程的

10、成績表（課程編號，課程名，學(xué)生名，成績）。命令：select o,cname,,DEGREE from Student inner join Score on student.no=score.no inner join course on o=o inner join Teacher on course.tno=Teacher.no where teacher.depart='計算機(jī)系'24. 列出所有可能的“計算機(jī)系”與“電子工程系”不同職稱的教師配對信息，要求輸出每個老師的姓名（name）和（職稱）命令：select ,ex1.pr

11、of,,f from Teacher ex1,Teacher ex2 where ex1.depart='計算機(jī)系' and ex2.depart='電子工程系' and f!=f25. 列出所有處于不同班級中，但具有相同生日的學(xué)生，要求輸出每個學(xué)生的學(xué)號和姓名。（提示：使用datediff函數(shù)，具體用法可以參考：）命令：select ex1.no,,ex2.no, from Student ex1 inner join Student ex2 on ex1.birthda

12、y=ex2.birthday where ex1.class!=ex2.classselect ex1.no,,ex2.no, from Student ex1 ,Student ex2 where ex1.class!=ex2.classand datediff(day,ex1.birthday,ex2.birthday )=026. 顯示張三教師任課的學(xué)生姓名，課程名，成績命令：select ,cname,DEGREE from Student inner join Score on student.no=score.no inner

13、 join course on o=o inner join Teacher on course.tno=teacher.no where ='張三'27. 列出所講課已被選修的教師的姓名和系別命令：select distinct ,depart from Score inner join course on o=o inner join Teacher on course.tno=Teacher.no28. 輸出所有學(xué)生的name、no和degree。（degree為空的不輸出和為空的輸出兩種情況）。命令：select stud

14、,student.no,DEGREE from Student inner join Score on student.no=score.noselect ,student.no,DEGREE from Student left join Score on student.no=score.no29. 列出所有任課教師的name和depart。（從課程選修和任課兩個角度考慮）命令：(課程選修)select distinct ,departfrom scoreleft join course on o=oleft join teac

15、her on course.tno=teacher.no（任課）select distinct ,depart from teacher inner join course on Teacher.no=course.tno30. 輸出男教師所上課程名稱。命令：select cname from Teacher inner join course on Teacher.no=course.tno where teacher.sex='男'31. 出與“李軍”同性別的所有同學(xué)的name。命令：select name from Student where se

16、x=(select sex from Student where name='李軍')32. 輸出選修“數(shù)據(jù)結(jié)構(gòu)”課程的男同學(xué)的成績。命令：select DEGREE from Student inner join Score on student.no=score.no where sex='男'33. 列出選修編號為3-105課程并且該門課程成績比課程 3-111的最高分要高的cno,no和degree。命令：select cno,student.no,DEGREE from Student inner join Score on student.no=sc

17、ore.no where cno='3-105' and degree>(select MAX(degree) from Score where cno='3-111')子查詢34. 輸出score中成績最高的學(xué)號和課程號命令：select no,cno from Score where degree=(select MAX(degree) from Score)35. 輸出選修3-105課程，其成績高于109號同學(xué)在此課程所得成績的所有同學(xué)的學(xué)號，姓名命令：select student.no,name from Student inner join Sc

18、ore on student.no=score.no where cno='3-105' and degree>(select degree from Student inner join Score on student.no=score.no where student.no=109 and cno='3-105')36. 列出成績比該課程平均成績低的同學(xué)的學(xué)號，成績和該門課的平均成績命令：select no,DEGREE,temp.avgdegree from Score inner join (select cno,AVG(degree) avgd

19、egree from Score group by cno) as temp on o=o where degree<avgdegree37. 列出沒有實際授課的教師的姓名和系別命令：select distinct name,depart from Teacher left join course on Teacher.no=course.tno left join Score on o=o where score.no is null38. 列出選修了編號為3-105課程且其成績高于4-109課程最高成績的同學(xué)的 課程編號，學(xué)號和成績命令：select cno,student.no,D

20、EGREE from Student inner join Score on student.no=score.no where cno='3-105' and degree>(select MAX(degree) from Score where cno='4-109')39. *列出符合下述條件的所有可能的同學(xué)配對（sno1,sname1,sno2,sname2,difference）。其中要求學(xué)號為sno1的sname1同學(xué)的所學(xué)課程的平均分大于學(xué)號為sno2的sname2同學(xué)的所學(xué)課程平均分，兩個同學(xué)的課程平均分的差值difference為（sno1同學(xué)平均分-sno2同學(xué)平均分）命令：select ex1.no,,ex2.no