SQL语言章节练习答案
SQL 语言章节练习答案
一、设有如下关系模式： student(SNO, SNAME , SEX ,BIRTHDAY, CLASS) teacher(TNO,TNAME,SEX,BIRTHDAY,PRO F,DEPART) PROF 为职称，DEPART 为系别 course(CNO, CNAME, TNO) score(SNO, CNO, DEGREE) DEGREE 为成绩 写出实现以下各题功能的SQL 语句： 1.查询至少有2名男生的班号；——（难） SELECT CLASS FROM student WHERE SEX='男' GROUP BY CLASS HAVING COUNT(*)>=2; Having 语句用来对结果集进行附加筛选，通常与group by 语句一起使用。 ************************************************************* 2.查询不姓“王”的同学记录；——（易） SELECT * FROM student WHERE NAME NOT LIKE '王%' 3.查询每个学生的姓名和年龄；——（难） SELECT NAME,2007-year(BIRTHDAY) FROM student 4.查询学生中最大和最小的birthday 日期值；——（中） SELECT MAX(BIRTHDAY), MIN(BIRTHDAY) FROM student 5.查询学生表的全部记录并按班号和年龄从大到小的顺序；——（中） SELECT * FROM student ORDER BY CLASS,BIRTHDAY DESC 6.查询男教师及其所上的课程；——（中） SELECT https://www.360docs.net/doc/6b10994293.html,, https://www.360docs.net/doc/6b10994293.html,ame FROM teacher , course WHERE teacher.no=course.tno and teacher.sex='男' 7.查询最高分同学的学号，课程号和成绩；
——（中） SELECT * FROM score WHERE degree= ( SELECT max(degree) FROM score ) 满足嵌套子查询的条件（最后投影的属性列可以在一个表格中给出） **************************************88 8.查询和“李军”同性别并同班的所有同学的姓名；——（中） SELECT NAME FROM student WHERE sex= ( SELECT sex FROM student WHERE name='李军' ) and class= ( SELECT class FROM student WHERE name='李军' ) 要习惯用多重条件的复合运算来做题 9.查询选修“数据库系统概论”课程的男同学的成绩表；——（中） SELECT * FROM score WHERE no IN ( SELECT no
FROM student
WHERE sex='男')
and
cno=
(
SELECT cno
FROM course
WHERE cname='数据库系统概论'
)
10查询所有未讲课的教师的姓名和所在系别；——（难）
S ELECT name, depart
FROM teacher
WHERE NOT EXISTS
(SELECT *
FROM course
WHERE course.tno=teacher.no
)
11.查询“计算机系”教师所教课程的成绩表；——（难）
SELECT *
FROM score , teacher , course WHERE teacher.depart='计算机系'
and teacher.no=course.tno
and https://www.360docs.net/doc/6b10994293.html,o=https://www.360docs.net/doc/6b10994293.html,o
思路：
涉及到三个表格，教师信息表，课程信息表，以及成绩表
teacher ->计算机系教师教授的课程代码（teacher,course）teacher.no=course.tno
score-> 此课程的成绩表（course,score）https://www.360docs.net/doc/6b10994293.html,o=https://www.360docs.net/doc/6b10994293.html,o
+ 限制条件 teacher.depart='计算机系'
缺一不可！！！！！！！！
12.查询选修“5623”课程，且成绩高于“001”号同学成绩的所有同学的记录；——（难）
SELECT *
FROM student , score
WHERE student.no=score.no
and cno='5623' and degree>
(
SELECT degree
FROM score
WHERE no='001'
and cno='5623'
)
13.查询最低分大于70，最高分小于90的学生的学号；——（中）
SELECT no
FROM score
GROUP BY no
HAVING min(degree)>70
and max(degree)<90
14.查询成绩在60到100之间的所有记录；——（中）
SELECT *
FROM score
WHERE degree BETWEEN 60 AND 100
15.查询成绩比该课程平均成绩低的同学的成绩表；——（相关子查询）（难）
SELECT *
FROM score a
WHERE degree <
(
SELECT avg(degree)
FROM score b
WHERE https://www.360docs.net/doc/6b10994293.html,o=https://www.360docs.net/doc/6b10994293.html,o
group by https://www.360docs.net/doc/6b10994293.html,o
)
16.查询所有女教师和女同学的姓名、性别和生日；——（中）
SELECT name, sex, birthday
FROM teacher
WHERE sex='女'
UNION
SELECT name, sex, birthday
FROM student
WHERE sex='女'
17.查询“计算机系”和“无线电系”的教师的姓名和职称；——（中）
SELECT name, prof
FROM teacher
WHERE depart='计算机系' OR depart='无线电系’'
18. 建立一个学生课程成绩视图V-SSC（NO, NAME, CNO, CNAME, DEGREE）；
CREATE VIEW V-SSC
AS
SELECT student.no,https://www.360docs.net/doc/6b10994293.html,, cno, cname, degree
FROM student，course，score
WHERE student.no=score.no AND https://www.360docs.net/doc/6b10994293.html,o=https://www.360docs.net/doc/6b10994293.html,o
19从视图V-SSC上查询平均成绩在80分以上的同学的NAME, CNAME 和DEGREE。SELECT NAME, CNAME, DEGREE
FROM V-SSC
GROUP BY NO
HAVING AVG(DEGREE)>80
二、设有如下关系模式：——（中）
图书关系B（图书编号B#，图书名T，作者A，出版社P）；
读者关系R（借书证号C#，读者名N，读者地址D）；
借阅关系L（C#，B#，借书日期E，还书标志BZ）；
BZ=NULL 表示未还；
写出实现以下各题功能的SQL语句：1.查询“工业出版社”出版的图书名，用关系代数表达式描述关系查询，并
写出查询语言。
))
(
(
'
'
B
P
T工业出版社
=
∏σ
Select T
from B
Where P = ’工业出版社’
2.将书号为B5的图书的出版社改为“工业出版社”
Update B
Set P=’工业出版社’
Where B# = ’B5’
3.查询2007年以前借书的读者名字
Select N , T
From R , L
Where E <’2007’
AND L.C#=R.C#
4.将读者“孙祥”的借书信息从L表中删除。Delete from L Where C# = ( Select C# From R
Where N=’孙祥’);
三、设有关系模式：
SB( SN , SNAME, CITY) 其中SB表示供应商，SN为供应商号，SNAME为供应商名字，
CITY为供应商所在城市；
PB(PN,
JB( JN, JNAME, JCITY)
为工程所在城市；
1.取出所有工程的全部细节；——（易）SELECT *
FROM JB
2.取出所在城市为上海的所有工程的全部细节；——（易）
SELECT *
FROM JB
WHERE JCITY='上海'
3.取出重量最轻的零件代号；——（难）
S ELECT PN
FROM PB
WHERE WEIGHT=
(
SELECT MIN(WEIGHT)
FROM PB
)
4.取出为工程J01提供零件的供应商代号；——（易）
SELECT SN
FROM SPJB
WHERE JN='J01'
5.取出为工程J01提供零件P01的供应商代号；——（易）
SELECT SN
FROM SPJB
WHERE JN='J01'
AND PN='P01'
6.取出由供应商0101提供零件的工程名称；——（易）
SELECT JNAME
FROM JB,SPJB
WHERE SN='0101'
AND SPJB.JN=JB.JN
7.取出供应商0101提供的零件的颜色；——（易）
SELECT COLOR
FROM PB,SPJB
WHERE SN='0101'
AND SPJB.PN=PB.PN
8.取出为工程J01或J02提供零件的供应商代号；——（中）
SELECT SN
FROM SPJB
WHERE JN='J01' OR JN='J02'
9.取出为工程J01提供黑色零件的供应商代号；——（易）
S ELECT SN
FROM SPJB,PB
WHERE COLOR='黑色'
AND JN='J01'
AND PB.PN=SPJB.PN
10.取出为所在城市为上海的工程提供零件的供应商代号；——（易）
SELECT SN
FROM SPJB,JB
WHERE JCITY='上海'
AND JB.JN=SPJB.JN
11.取出为所在城市为上海或北京的工程提供黑色零件的供应商代号；——（中）SELECT SN
FROM PB, JB, SPJB
WHERE COLOR='黑色'
AND JCITY IN ('上海','北京')
AND PB.PN=SPJB.PN
AND JB.JN=SPJB.JN
涉及到城市，颜色，供应商，需要的表有三个PB, JB, SPJB（两个连接条件）
+ 两个限制条件COLOR='黑色' + JCITY IN ('上海','北京')
12.取出供应商与工程所在城市相同的供应商提供的零件代号；——（中）
SELECT PN
FROM SB, JB , SPJB
WHERE SB.CITY=JB.JCITY
AND SB.SN=SPJB.SN
AND JB.JN=SPJB.JN
SB---SPJB-----JB
13.取出上海的供应商提供给上海的任一工程的零件的代号；——（难）
SELECT PN
FROM SB, SPJB, JB
WHERE SB.CITY='上海'
AND JB.JCITY='上海'
AND SB.SN=SPJB.SN
AND JB.JN=SPJB.JN
多表连接选择条件 = 连接条件 + 限制条件
14.取出上海供应商不提供任何零件的工程的代号；——（难）
SELECT JN
FROM SPJB
WHERE JN NOT IN
(SELECT SPJB.JN
FROM SB,SPJB
WHERE SB.SN=SPJB.SN AND SB.CITY='上海')
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!
取出上海供应商提供的零件的工程代号，用NotIn
15.取出这样一些供应商代号，它们能够提供至少一种由黑色零件的供应商提供的零件；——（难）
SELECT SPJB.SN
FROM SB,SPJB
WHERE SPJB.PN IN
(
SELECT SPJB.PN
FROM SPJB,PB
WHERE PB.PN=SPJB.PN AND PB.COLOR='黑色'
)
先取出黑色零件的代码，再用集合IN
16.取出由供应商0101提供零件的工程的代号；——（易）
SELECT JN
FROM SPJB
WHERE SN='0101'