查询练习

SQL查询练习

一、有三个关系，试用关系代数表达式表示下列查询语句：

S(sno，sname，age，sex，sdept)

C(cno，cname，cdept，tname) tname表示授课老师名

SC(sno，cno，grade)

(1) 检索年龄小于22岁的男学生的学号与姓名。

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

(3) 检索JIANG老师所授课程的课程号、课程名。

(4) 检索至少选修JIANG老师所授课程中一门课的男学生姓名。

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

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

(7) 检索选修课程包含JIANG老师所授课程的学生学号。

(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，“is”）22．删除“李军”同学的所有信息。

23．为信息系所有男生建一视图vs_ism。

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

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

SELECT sname，ssex FROM student WHERE sbirth BETWEEN 25 AND 30；26．查询姓“欧阳”的学生。

SELECT * FROM student WHERE sname like ‘欧阳%’；

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

SELECT * FROM student WHERE sdept IN (‘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的社会团体的名称和负责人