综合练习(二)-习题

综合练习（二）

一、用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 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 sage<20

8、查询考试成绩不及格的学生的学号

当一个学生有多门不极格课程时，只列出一个学号。

SELECT DISTINCT sno FROM SC WHERE grade<60 and grade is not null 9、查询年龄在20～23岁之间的学生的姓名、所在系和年龄

SELECT sname, sdept, sage

FROM Student

WHERE sage BETWEEN 20 AND 23

10、查询年龄不在20～23之间的学生的姓名、所在系和年龄

SELECT sname, sdept, sage

FROM Student

WHERE sage NOT BETWEEN 20 AND 23

11、查询信息系、数学系、和计算机系学生的姓名和性别

SELECT sname, ssex,

FROM Student

WH ERE sdept IN (‘信息系’,’数学系’,’计算机系’)

12、查询既不属于信息系、数学系、也不属于计算机系学生的姓名和性别

SELECT sname, ssex,

FROM Student

WHERE 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,sno

FROM Student

WHERE sname LIKE ‘_小%’ OR sname LIKE ‘_大%’

16、查询所有不姓“刘”的学生

SELECT sname,sno

FROM Student

WHERE sname NOT LIKE ‘刘%’

17、从学生表中查询学号的最后一位不是2、3、5的学生的情况

SELECT *

FROM Student

WHERE sno NOT LIKE ‘%2’ AND NOT LIKE ‘%3’

AND NOT LIKE ‘%5’

18、查询无考试成绩的学生的学号和相应的课程号

SELECT sno, cno

FROM SC

WHERE grade IS NULL

19、查询所有有考试成绩的学生的学号和课程号

SELECT sno, cno

FROM SC

WHERE grade IS NOT NULL

20、查询计算机系年龄在20岁以下的学生的姓名

SELECT sname

FROM Student

WHERE sdept =‘计算机系’ AND sage < 20

21、查询选修了课程“c02”的学生的学号及其成绩，查询结果按成绩降序排列

SELECT sno, grade FROM SC WHERE cno=‘c02’

ORDER BY grade DESC

22、查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列

SELECT * FROM Student

ORDER BY sdept, sage DESC

23、统计学生总人数

SELECT COUNT(sno) FROM Student

24、统计选修了课程的学生的人数

●由于一个学生可以选多门课程，应使用DISTINCT关键字去掉重复值。

SELECT COUNT (DISTINCT sno) FROM SC

25、计算学号为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 Student

WHERE sage = (SELECT MAX(sage) FROM Student)

29、统计每门课程的选课人数，列出课程号和人数

●分析：该语句首先将查询结果按cno的值分组，所有cno值相同的记录归为一组，然

后再用COUNT函数对每一组进行计算，求得每组的学生人数。

SELECT cno as 课程号, COUNT(sno) as 选课人数

FROM SC

GROUP BY cno

30、查询每名学生的选课门数和平均值

SELECT sno 学号, COUNT(cno) 选课门数, AVG(grade) 平均成绩

FROM SC

GROUP BY sno

31、查询选修了3门以上课程的学生的学号

●分析：先用GROUP BY按sno列进行分组，然后再用统计函数COUNT分别对每一组

进行统计，最后挑选出统计结果大于3的组的sno

SELECT sno FROM SC

GROUP BY sno

HAVING COUNT(cno) >3

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 https://www.360docs.net/doc/3c18210041.html,o=https://www.360docs.net/doc/3c18210041.html,o AND s.sdept=‘信息系’

AND https://www.360docs.net/doc/3c18210041.html,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 https://www.360docs.net/doc/3c18210041.html,o=https://www.360docs.net/doc/3c18210041.html,o AND https://www.360docs.net/doc/3c18210041.html,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、查询计算机系考成绩最高的学生的姓名

SELECT sname FROM Student s JOIN SC ON s.sno=sc.sno

WHERE sdept=‘计算机’

AND sc.grade=

( SELECT MAX(grade) FROM SC sc, Student s

WHERE s.sno=sc.sno AND s.sdept=’计算机’)

44、删除选课成绩小于50分的学生的选课记录

DELETE FROM SC WHERE grade < 50

45、将所有选修了课程“c01”的学生的成绩加10分

UPDATE SC SET grade=grade+10 WHERE cno=’c01’

46、将计算机系所有选修了“计算机文化学”课程的学生的成绩加10分

UPDATE SC SET grade=grade+10

WHERE sno IN

( SELECT sno FROM Student WHERE sdept=’计算机’)

AND cno= (SELECT cno FROM Course WHERE cname=’计算机文化学’) 47、查询总绩超过200分的学生，要求列出学号、总成绩

SELECT sno, SUM(grade)

FROM SC

GROUP BY sno

HAVING SUM(grade) > 200