综合练习(二)-习题

综合练习（二）综合练习（二） 一、用一、用 SQL 语句创建如下三张表：学生表（语句创建如下三张表：学生表（Student） 、课程表（、课程表（Course）和学生）和学生 选课表（选课表（SC） ，三张表结构如下：，三张表结构如下： 注：一、二两题需要将命令放入注：一、二两题需要将命令放入*.sql 脚本中，然后使用脚本进行创建表及数脚本中，然后使用脚本进行创建表及数 据的录入据的录入 Student 表结构表结构 列列 名名说说 明明数据类型数据类型约约 束束 sno 学号字符串，长度为 7主键 sname姓名字符串，长度为 10非空 ssex性别字符串，长度为 2取男或女 sage年龄整数取值 1545 sdept所在系字符串，长度为 20默认为计算机系 Course 表结构表结构 列列 名名说说 明明数据类型数据类型约约 束束 cno课程号字符串，长度为 10主键 cname课程名字符串，长度为 20非空 ccredit学分整数取值大于 0 semster学期整数取值大于 0 period学时整数取值大于 0 SC 表结构表结构 列列 名名说说 明明数据类型数据类型约约 束束 sno学号字符串，长度为 7主键，引用 Student 的外键 cno课程号字符串，长度为 10主键，引用 Course 的外键 grade成绩整数取值 0100 答案： 1、Student 表 CREATE TABLE Student ( sno VARCHAR2(7) PRIMARY KEY, sname VARCHAR2 (10) NOT NULL, ssex VARCHAR2 (2) CHECK(ssex=男 OR ssex=女), sage NUMBER(2) CHECK(sage=15 AND sage0), semester NUMBER(2) CHECK(semester0), period NUMBER(3) CHECK(period0), CONTRAINT course_cno_pk PRIMARY KEY(cno) ) 3、SC 表 CREATE TABLE SC( sno char(7) NOT NULL, cno char(10) NOT NULL, grade tinyint CHECK(grade=0 AND grade 9512102c0178 9512102c0366 9512103c0482 9512104c0675 三、完成如下查询三、完成如下查询 1、查询全体学生的学号和姓名查询全体学生的学号和姓名 SELECT sno, sname from Student 2、查询全体学生的姓名、学号和所在系查询全体学生的姓名、学号和所在系 SELECT sname, sno, sdept from Student 3、查询全体学生的姓名及其出生年份查询全体学生的姓名及其出生年份 分析：由于 Student 表中只记录了学生的年龄，而没有记录学生的出生年 份，所以需要经过计算得到学生的出生年份，即用当前年减去年龄，得到 出生年份。 SELECT sname, 2011 sage FROM Student 或 SELECT sname, to_number(to_char(sysdate,yyyy) sage FROM Student 4、查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为 “Year of Birth”常量值常量值 SELECT snamem, Year of Birth, to_number(to_char(sysdate,yyyy) sage FROM Student 5、在选课表（在选课表（SC）中查询有哪些学生选修了课程，并列出学生的学号）中查询有哪些学生选修了课程，并列出学生的学号 SELECT sno FROM SC 在结果集中会有许多重复的行（实际上一个学生选修了多少门课程，其学号 就会在结果集中重复出现多少次） 。 使用 DISTINCT 关键字就可以去掉结果集中的重复行。 去掉结果集中的重复行 SELECT DISTINCT sno FROM SC 6、查询计算机系全体学生的姓名查询计算机系全体学生的姓名 SELECT sname FROM Student WHEER sdept=计算机系 7、查询所有年龄在查询所有年龄在 20 岁以下的学生的姓名及年龄岁以下的学生的姓名及年龄 SELECT sname, sage FROM Student WHEER sage3 32、查询选课门数等于或大于查询选课门数等于或大于 4 门的学生的平均成绩和选课门数门的学生的平均成绩和选课门数 SELECT sno, AVG(grade) 平均成绩, COUNT(cno) 修课门数 FROM SC GROUP BY sno HAVING COUNT(cno) = 4 33、查询每个学生的情况及其选课的情况、查询每个学生的情况及其选课的情况 当不给表指定别名时，需要使用原表名当不给表指定别名时，需要使用原表名 SELECT Student.sno, sname, ssex, sge, sdept, cno, grade FROM Student ,SC WHERE Student.sno=SC.sno 34、查询计算机系学生的选课情况，要求列出学生的名字、所修课的课程号和成、查询计算机系学生的选课情况，要求列出学生的名字、所修课的课程号和成 绩。绩。 SELECT sname, cno, grade FROM Student s , SC a WHERE s.sno=a.sno AND s.sdept = 计算机系 35、查询信息系选修查询信息系选修 Java 课程的学生的成绩，要求列出学生姓名、课程名和成绩课程的学生的成绩，要求列出学生姓名、课程名和成绩 此查询涉及了三张表，每连接一张表，就需要加入一个连接条件用于去掉产生的笛 卡尔积。 在该查询中，虽然所要查询的列和记录的选择条件均与 SC 表无关，但还是用了三张 表，原因是 Student 和 Course 表没有可以进行连接的列（语义相同的列） ，因此，这 两张表的连接需要借助于第三张表SC 表。 SELECT sname, cname, grade FROM Student s , SC a, Course c WHERE s.sno=a.sno AND o=o AND s.sdept=信息系 AND ame=Java 36、查询学生的选课情况，包括选修课程的学生和没有选修课程的学生查询学生的选课情况，包括选修课程的学生和没有选修课程的学生 如果 Student 表中有没有选课的学生时，也同样输出 Student 表中的数据，但是最后 输出列的 cno 和 grade 的值均为 NULL，因为在 SC 表中没有与之对应的记录，即对 于不满足连接条件的结果在相应的列上放置 NULL 值。 SELECT s.sno, sname, cno, grade FROM Student s, SC a WHERE s.sno = a.sno(+) 37、查询与查询与“刘晨刘晨”在同一个系的学生在同一个系的学生 分析：实际的查询过程如下： a. 确定“刘晨”所在的系，即执行子查询： SELECT sdept FROM Student WHERE sname=刘晨 b. 在子查询的结果中查找所有在此系学习的学生： SELECT sno, sname, sdept FROM Student WHERE sdept IN (计算机系) 通过结果可以看到，查询结果中也有“刘晨”。如果不希望“刘晨”出现在查询结果中， 如何实现？ SELECT sno, sname, sdept FROM Student WHERE sdept IN (SELECT sdept FROM Student WHERE sname=刘晨) AND sname !=刘晨 注意：这里的外层注意：这里的外层 sname !=刘晨刘晨不需要使用表名前缀，因为对于外层查询不需要使用表名前缀，因为对于外层查询 来说，其表名没有二义性。来说，其表名没有二义性。 38、查询成绩大于查询成绩大于 90 分的学生的学号和姓名分的学生的学号和姓名 SELECT sno, sname FROM Student WHERE sno IN (SELECT sno FROM SC WHERE grade 90) 使用多表连接的方式实现： SELECT sno, sname FROM Student s , SC a WHERE s.sno=a.sno AND a.grade 90 39、查询选修了查询选修了“数据库基础数据库基础”课程的学生的学号和姓名课程的学生的学号和姓名 SELECT sno, sname FROM Student WHERE sno IN ( SELECT sno FROM SC WHERE cno IN (SELECT cno FROM Course WHERE cname=数据库基础) 使用多表连接方式实现：使用多表连接方式实现： SELECT s.sno, sname FROM Student s, SC a, Course c WHERE s.sno=a.sno AND o=o AND ame=数据库基础 40、查询选修了课程查询选修了课程“c02”且成绩高于此课程的平均成绩的学生的学号和成绩且成绩高于此课程的平均成绩的学生的学号和成绩 SELECT sno, grade FROM SC WHERE cno=c02 AND grade ( SELECT AVG(grade) FROM SC WHERE cno=c02) 41、查询选修了课程、查询选修了课程“c01”的学生的姓名和所在系的学生的姓名和所在系 SELECT sname, sdept FROM Student WHERE sno IN ( SELECT sno FROM SC WHERE cno=c01) 42、查询数学系成绩在、查询数学系成绩在 80 分以上的学生的学号、姓名分以上的学生的学号、姓名 SELECT sno, sname FROM Student WHERE sno IN (SELECT sno FROM SC WHERE grade 80) AND sdept=数学系 43、查询计算机系考成绩最高的学生的姓名、查询计算机系考成绩最高的学生的姓名 S