综合练习二-习题

综合练习（二）一、用SQL语句创建如下三张表：学生表（Student）、课程表（Course）和学生选课表（SC），三张表结构如下：注：一、二两题需要将命令放入*.sql脚本中，然后使用脚本进行创建表及数据的录入Student表结构Course表结构SC表结构答案：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 sage<=45),sdept VARCHAR2 (20) DEFAULT ‘计算机系’)2、Course表CREATE TABLE Course(cno VARCHAR2 (10) NOT NULL,cname VARCHAR2 (20) NOT NULL,ccredit NUMBER(2) CHECK(ccredit>0),semester NUMBER(2) CHECK(semester>0),period NUMBER(3) CHECK(period>0),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<=100),CONTRAINT sc_sno_cno_pk PRIMARY KEY(sno,cno),CONTRAINT student_sno_fk FOREIGN KEY(sno) REFERENCE Student(sno), CONTRAINT course_cno_fk FOREIGN KEY(cno) REFERENCE Course (cno) )二、使用SQL语句分别向Student、Course、SC表中加入如下数据：Student表数据Course表数据SC 表数据三、完成如下查询1、查询全体学生的学号和姓名SELECT sno, sname from Student2、查询全体学生的姓名、学号和所在系SELECT sname, sno, sdept from Student3、查询全体学生的姓名及其出生年份分析：由于Student表中只记录了学生的年龄，而没有记录学生的出生年份，所以需要经过计算得到学生的出生年份，即用当前年减去年龄，得到出生年份。

SELECT sname, 2011 – sage FROM Student―或―SELECT sname, to_number(to_char(sysdate,’yyyy’))– sage FROM Student4、查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“Year of Birth”常量值SELECT snamem, ‘Year of Birth’, to_number(to_char(sysdate,’yyyy’))–sage FROM Student5、在选课表（SC）中查询有哪些学生选修了课程，并列出学生的学号SELECT sno FROM SC在结果集中会有许多重复的行（实际上一个学生选修了多少门课程，其学号就会在结果集中重复出现多少次）。

使用DISTINCT关键字就可以去掉结果集中的重复行。

去掉结果集中的重复行SELECT DISTINCT sno FROM SC6、查询计算机系全体学生的姓名SELECT sname FROM Student WHEER sdept=‘计算机系’7、查询所有年龄在20岁以下的学生的姓名及年龄SELECT sname, sage FROM Student WHEER sage<208、查询考试成绩不及格的学生的学号当一个学生有多门不极格课程时，只列出一个学号。

SELECT DISTINCT sno FROM SC WHERE grade<60 and grade is not null 9、查询年龄在20～23岁之间的学生的姓名、所在系和年龄SELECT sname, sdept, sageFROM StudentWHERE sage BETWEEN 20 AND 2310、查询年龄不在20～23之间的学生的姓名、所在系和年龄SELECT sname, sdept, sageFROM StudentWHERE sage NOT BETWEEN 20 AND 2311、查询信息系、数学系、和计算机系学生的姓名和性别SELECT sname, ssex,FROM StudentWH ERE sdept IN (‘信息系’,’数学系’,’计算机系’)12、查询既不属于信息系、数学系、也不属于计算机系学生的姓名和性别SELECT sname, ssex,FROM StudentWHERE sdept NOT IN (‘信息系’,’数学系’,’计算机系’)13、查询姓“张”的学生的详细信息SELECT * FROM Student WHERE sname LIKE ‘张%’14、查询学生表中姓“张”、姓“李”、姓“刘”的学生的情况SELECT * FROM Student WHERE sname LIKE ‘张%’OR sname LIKE ‘李%’OR sname LIKE ‘刘%’15、查询名字中第2个字为“小”或“大”字的学生姓名和学号SELECT sname,snoFROM StudentWHERE sname LIKE ‘_小%’ OR sname LIKE ‘_大%’16、查询所有不姓“刘”的学生SELECT sname,snoFROM StudentWHERE sname NOT LIKE ‘刘%’17、从学生表中查询学号的最后一位不是2、3、5的学生的情况SELECT *FROM StudentWHERE sno NOT LIKE ‘%2’ AND NOT LIKE ‘%3’AND NOT LIKE ‘%5’18、查询无考试成绩的学生的学号和相应的课程号SELECT sno, cnoFROM SCWHERE grade IS NULL19、查询所有有考试成绩的学生的学号和课程号SELECT sno, cnoFROM SCWHERE grade IS NOT NULL20、查询计算机系年龄在20岁以下的学生的姓名SELECT snameFROM StudentWHERE sdept =‘计算机系’ AND sage < 2021、查询选修了课程“c02”的学生的学号及其成绩，查询结果按成绩降序排列SELECT sno, grade FROM SC WHERE cno=‘c02’ORDER BY grade DESC22、查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列SELECT * FROM StudentORDER BY sdept, sage DESC23、统计学生总人数SELECT COUNT(sno) FROM Student24、统计选修了课程的学生的人数●由于一个学生可以选多门课程，应使用DISTINCT关键字去掉重复值。

SELECT COUNT (DISTINCT sno) FROM SC25、计算学号为9512101的学生的考试总成绩之和SELECT SUM (grade) FROM SC WHERE sno=‘9512101’26、计算课程“c01”的学生的考试平均成绩SELECT AVG (grade) FROM SC WHERE cno=‘c01’27、查询选修了课程“c01”的学生的最高分和最低分SELECT MAX (grade), MIN (grade) FROM SC WHERE cno=‘c01’28、查询年龄最大的学生的姓名SELECT sname FROM StudentWHERE sage = (SELECT MAX(sage) FROM Student)29、统计每门课程的选课人数，列出课程号和人数●分析：该语句首先将查询结果按cno的值分组，所有cno值相同的记录归为一组，然后再用COUNT函数对每一组进行计算，求得每组的学生人数。

SELECT cno as 课程号, COUNT(sno) as 选课人数FROM SCGROUP BY cno30、查询每名学生的选课门数和平均值SELECT sno 学号, COUNT(cno) 选课门数, AVG(grade) 平均成绩FROM SCGROUP BY sno31、查询选修了3门以上课程的学生的学号●分析：先用GROUP BY按sno列进行分组，然后再用统计函数COUNT分别对每一组进行统计，最后挑选出统计结果大于3的组的snoSELECT sno FROM SCGROUP BY snoHAVING COUNT(cno) >332、查询选课门数等于或大于4门的学生的平均成绩和选课门数SELECT sno, AVG(grade) 平均成绩, COUNT(cno) 修课门数FROM SCGROUP BY snoHAVING COUNT(cno) >= 433、查询每个学生的情况及其选课的情况●当不给表指定别名时，需要使用原表名SELECT Student.sno, sname, ssex, sge, sdept, cno, gradeFROM Student ,SCWHERE Student.sno=SC.sno34、查询计算机系学生的选课情况，要求列出学生的名字、所修课的课程号和成绩。

SELECT sname, cno, gradeFROM Student s , SC aWHERE s.sno=a.sno AND s.sdept = ‘计算机系’35、查询信息系选修Java课程的学生的成绩，要求列出学生姓名、课程名和成绩●此查询涉及了三张表，每连接一张表，就需要加入一个连接条件用于去掉产生的笛卡尔积。

●在该查询中，虽然所要查询的列和记录的选择条件均与SC表无关，但还是用了三张表，原因是Student和Course表没有可以进行连接的列（语义相同的列），因此，这两张表的连接需要借助于第三张表——SC表。

SELECT sname, cname, gradeFROM Student s , SC a, Course cWHERE s.sno=a.sno AND o=o AND s.sdept=‘信息系’AND ame=‘Java’36、查询学生的选课情况，包括选修课程的学生和没有选修课程的学生●如果Student表中有没有选课的学生时，也同样输出Student表中的数据，但是最后输出列的cno和grade的值均为NULL，因为在SC表中没有与之对应的记录，即对于不满足连接条件的结果在相应的列上放置NULL值。