SQL查询练习

SQL查询练习
SQL查询练习
⼀、有三个关系，试⽤关系代数表达式表⽰下列查询语句：
S(sno，sname，age，sex，sdept)
C(cno，cname，cdept，tname) tname表⽰授课⽼师名
SC(sno，cno，grade)
(1) 检索年龄⼩于22岁的男学⽣的学号与姓名。

(2) 检索学号为S3学⽣所学课程的课程名与任课教师名。

(3) 检索王⽼师所授课程的课程号、课程名。

(4) 检索⾄少选修王⽼师所授课程中⼀门课的男学⽣姓名。

(5) 检索陈同学不学的课程的课程号。

(6) 检索全部学⽣都选修的课程的课程号、课程名。

(7) 检索选修课程包含王⽼师所授课程的学⽣学号。

(8) 检索⾄少选修两门课程的学⽣学号。

(9) 查询⾄少选修了２号课程和８号课程的学⽣姓名。

(10) 查询张红的年龄。

(11) 查询李明同学不及格的课程名称。

(12) 查询选修了“计算机⽹络”的学⽣姓名。

(13) 查询“计算机⽹络”成绩在９０分以上的学⽣姓名。

⼆、设有下列四个关系模式：
S (SNO， SNAME， CITY)；
P (PNO， PNAME， COLOR， WEIGHT)；
J (JNO， JNAME， CITY)；
SPJ(SNO， PNO， JNO， QTY)。

其中，供应商关系S由供应商号(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成。

零件关系P由零件号(PNO)、零件名称(PNAME)、零件颜⾊(COLOR)、零件重量(WEIGHT)组成，⽤于记录各种零件的情况。

项⽬关系J由项⽬号(JNO)、项⽬名称(JNAME)、项⽬所在城市(CITY) 组成。

供应情况关系SPJ由供应商号(SNO)、零件号(PNO)、项⽬号(JNO)、供应数量(QTY) 组成。

试⽤关系代数表达式完成以下操作：
(1) 检索供应项⽬J2零件的供应商号(SNO)。

(2) 检索供应项⽬J2零件P2的供应商号(SNO)。

(3) 检索供应项⽬J2⿊⾊零件的供应商姓名(SNAME)。

(4) 检索没有使⽤天津供应商⽣产的⿊⾊零件的项⽬号(JNO)。

(5) 检索使⽤了S1供应所供应的全部零件的项⽬名称(JNAME)。

三、对student、course、scg三个表⽤SQL完成以下操作：
student(sno，sname，age，sex，sdept)
course(cno，cname，pcno，ccredit，cdept，tname)
scg(sno，cno，grade)
1．创建关系表stu1(sno，sname，ssex，sbirth，sdept)。

2．对student按姓名建⽴索引。

3．查询信息系所有年龄不⼤于21岁的⼥⽣。

4．查询1982年出⽣的男⽣的姓名。

5．查询信息系、⾦融系所有姓“王”的同学的姓名和年龄。

6．查询姓“王”的男同学的⼈数。

7．查询2号课程的最低分。

8．查询总分最⾼的学⽣的学号。

9．查询每个同学的平均分。

10．查询每个同学所选修的课程门数。

11．查询“计算机⽹络”90分以上的同学的姓名。

12．查询每门课程的课程名及选修⼈数。

13．查询选修了全部课程的学⽣姓名。

14．查询总学分已超过40学分的学⽣学号、姓名、总学分。

15．查询⾄少选修了学⽣“20021710233”选修的全部课程的学⽣姓名。

16．查询其他系中⽐“cs”系任⼀学⽣年龄都⼩的学⽣名单。

17．查询每⼀个同学的学号、姓名、选修的课程名及分数。

18．查询信息系学⽣或年龄⼩于20岁的学⽣。

19．查询信息系学⽣与年龄⼩于20岁学⽣的交集。

20．查询信息系学⽣与年龄⼩于20岁学⽣的差集。

21．向student增⼀新⽣（“20021710146”，“王飞”，“男”，12/22/1985，“jsj”）22．删除“李军”同学的所有信息。

23．为信息系所有男⽣建⼀视图vs_ism。

24．利⽤视图vs_ism查询信息系“王强”同学。

25．查询年龄在25-30之间的学⽣姓名及性别。

26．查询姓“欧阳”的学⽣。

27．查询信息系is，数学系ma和计算机系cs的学⽣。

28．检索蒋炎焱⽼师所授课程的课程号和课程名。

29．检索年龄⼤于23岁的男学⽣的学号和姓名。

30．检索⾄少选修蒋炎焱⽼师所授课程中⼀门课程的⼥学⽣姓名。

31．检索wang同学不学的课程的课程号。

32．检索⾄少选修两门课程的学⽣学号。

33．检索全部学⽣都选修的课程的课程号与课程名。

34．检索选修课程包含蒋炎焱⽼师所授课程的学⽣学号。

35．统计有学⽣选修的课程门数。

36．求选修5号课程的学⽣的平均年龄。

37．求蒋炎焱⽼师讲授的每门课程的学⽣平均成绩。

38．统计每门课程的学⽣选修⼈数(超过10⼈的课程才统计)。

要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列。

39．检索学号⽐wang同学⼤，⽽年龄⽐他⼩的学⽣姓名。

40．检索姓名以wang打头的所有学⽣的姓名和年龄。

41．在scg中检索成绩为空值的学⽣学号和课程号。

42．求年龄⼤于⼥同学平均年龄的男学⽣姓名和年龄。

43．求年龄⼤于所有⼥同学年龄的男学⽣姓名和年龄。

44．在基本表student中检索每⼀门课程成绩都⼤于等于80分的学⽣学号、姓名和性别，并把检索到的值送往另⼀个已存在的基本表student2(sno，sname，sex)。

45．在基本表scg中删除尚⽆成绩的选课元组。

46．把wang同学的选课和成绩全部删去。

47．把选修maths课不及格的成绩全改为空值。

48．把低于总平均成绩的⼥同学成绩提⾼5%。

49．在基本表scg中修改3号课程的成绩，若成绩⼩于等于75分时提⾼5%，若成绩⼤于75分时提⾼4%(⽤两个UPDA TE语句实现)。

四、假设⼯程⼀零件数据库中有五个基本表：
供应商supplier (供应商号，姓名，所在城市，联系电话)
⼯程project (⼯程号，⼯程名，负责⼈，预算，⽇期)
零件part (零件号，零件名，规格，产地，颜⾊)；
⼯程零件p_p (⼯程号，零件号，数量)
采购p_s(零件号，供应商号，数量)
试⽤SQL语句完成下列操作：
1．试⽤SQLDDL语句定义上述五个基本表，并说明主键和外键。

2. 查找预算在5000-10000元之间的⼯程的信息，并将结果按预算降序排列。

3. 找出使⽤供应商si所供零件的⼯程号。

4. 找出⼯程项⽬j2使⽤的各种零件名称及其数量。

5. 找出上海⼚商供应的所有零件号。

6. 找出使⽤上海产的零件的⼯程名称。

7. 找出没有使⽤天津产零件的⼯程号。

8. 把全部红⾊零件的颜⾊改成蓝⾊。

9．将由供应商s5供给⼯程号为j4的零件p6改为由s3供应，并作其他必要的修改。

10．从供应商关系中删除s2的记录，并从⼯程零件关系中删除相应的记录。

11. 找出天津市供应商的姓名和电话。

12．试将project、p_p、part三个基本表的⾃然联接定义为⼀个视图VIEW1，将part、p_s、supplier三个基本表的⾃然联接定义为⼀个视图VIEW2。

13．在上述两个视图的基础上，检索上海的供应商所供应的零件的编号和名字。

14．在上述两个视图的基础上，检索项⽬所⽤零件的供应商编号和名字。

五、设职⼯⼀社团数据库有三个基本表：
职⼯(职⼯号，姓名，年龄，性别)；
社会团体(社团号，名称，负责⼈，活动地点)
参加(职⼯号，社团号，参加⽇期)
其中：
（1）职⼯表的主码为职⼯号。

（2）社会团体表的主码为社团号；外码为负责⼈，被参照表为职⼯表，对应属性为职⼯号。

（3)参加表的职⼯号和社团号为主码；职⼯号为外码，其被参照表为职⼯表，对应属性为职⼯号；社团号为外码，其被参照表为社会团体表，对应属性为社团号。

试⽤SQL语句表达下列操作：
1．定义职⼯表、社会团体表和参加表，并说明其主码和参照关系。

2．建⽴下列两个视图：
社团负责⼈(社团号，名称，负责⼈职⼯号，负责⼈姓名，负责⼈性别)；
参加⼈情况(职⼯号，姓名，社团社团号，社团名称，参加⽇期)。

3．查找参加唱歌队或篮球队的职⼯号和姓名。

4．查找没有参加任何社会团体的职⼯情况。

5．查找参加了全部社会团体的职⼯情况。

6．查找参加了职⼯号为“101”的职⼯所参加的全部社会团体的职⼯号。

7．求每个社会团体的参加⼈数。

8．求参加⼈数最多的社会团体的名称和参加⼈数。

9．求参加⼈数超过1的社会团体的名称和负责⼈。