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.sodocs.net/doc/6310994293.html,, https://www.sodocs.net/doc/6310994293.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.sodocs.net/doc/6310994293.html,o=https://www.sodocs.net/doc/6310994293.html,o
思路:
涉及到三个表格,教师信息表,课程信息表,以及成绩表
teacher ->计算机系教师教授的课程代码(teacher,course)teacher.no=course.tno
score-> 此课程的成绩表(course,score)https://www.sodocs.net/doc/6310994293.html,o=https://www.sodocs.net/doc/6310994293.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.sodocs.net/doc/6310994293.html,o=https://www.sodocs.net/doc/6310994293.html,o
group by https://www.sodocs.net/doc/6310994293.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.sodocs.net/doc/6310994293.html,, cno, cname, degree
FROM student,course,score
WHERE student.no=score.no AND https://www.sodocs.net/doc/6310994293.html,o=https://www.sodocs.net/doc/6310994293.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'