数据库复习题 考试题库（应用题）

1．设有下列四个关系模式：

S（SNO，SNAME，CITY）

P（PNO，PNAME，COLOR，WEIGHT）

J（JNO，JNAME，CITY）

SPJ（SNO，PNO，JNO，QTY）

其中，供应商表S由供应商号（SNO）、供应商姓名（SNAME）、供应商所在城市（CITY）组成，记录各个供应商的情况；零件表P由零件号（PNO）、零件名称（PNAME）、零件颜色（COLOR）、零件重量（WEIGHT）组成，记录各种零件的情况；工程项目表J由项目号（JNO）、项目名（JNAME）、项目所在城市（CITY）组成，记录各个工程项目的情况；供应情况表SPJ由供应商号（SNO）、零件号（PNO）、项目号（JNO）、供应数量（QTY）组成，记录各供应商供应各种零件给各工程项目的数量。分别用关系代数和SQL语言完成下列查询：

1）求供应工程项目号为J1工程零件的供应商号SNO

∏SNO（σJNO=‘J1’（SPJ））

SELECT SNO  FROM SPJ  WHERE  JNO=‘J1’；

2）求供应工程项目号为J1工程零件号为P1的供应商号SNO

∏SNO（σJNO=‘J1’∧PNO=‘P1’（SPJ））

SELECT  SNO   FROM  SPJ　WHERE JNO=‘J1’ AND  PNO=‘P1’；

3）求供应工程项目号为J1工程红色零件的供应商号SNO

∏SNO（σJNO=‘J1’∧COLOR=‘红’（SPJ∞P））

SELECT SNO  FROM  SPJ，P　WHERE  SPJ.PNO=P.PNO AND P.COLOR=‘红’AND JNO=‘J1’；

4）求至少使用天津供应商生产的红色零件的工程号JNO

∏JNO（SPJ）-∏JNO（σcity=‘天津’∧COLOR=‘红’（SPJ∞P∞S））

SELECT JNO  FROM  SPJ，P，S

WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND CITY<>‘天津’AND COLOR<>‘红’；

5）求至少用了S1供应商所供应的全部零件的工程号JNO（本题不需要用SQL语言完成）

∏JNO,PNO(SPJ)÷∏PNO(σSNO=‘S1’(SPJ))

2．现有如下关系模式：

雇员（员工姓名，居住城市，居住街道）　　工作（员工姓名，公司名，工资）

公司（公司名，公司所在城市）　　　　　　主管（员工姓名，主管姓名）

用关系代数完成下列查询：

1）找出所有在公司名为“firstbank”的公司工作的员工，显示员工姓名。

∏员工姓名（σ公司名=‘firstbank’（工作））

2）显示为“firstbank”公司工作的员工姓名和居住城市。

∏员工姓名，居住城市（σ公司名=‘firstbank’(雇员∞工作)）

3）找出所有为“firstbank”公司工作且工资在1000元以上的员工，显示员工姓名和工资。

∏员工姓名，工资（σ公司名=‘firstbank’∧工资>1000（工作））

4）找出每个员工工资都在1000元以上的公司，显示公司名。

∏公司名（工作）-∏公司名（σ工资<1000（工作））

5）找出主管人员Smith领导的员工姓名及员工居住的城市。

∏员工姓名，居住城市（σ主管姓名=‘Smith’（雇员∞主管））

3．设有下列关系模式：

STUDENT（NO，NAME，SEX，BIRTHDAY，CLASS）

TEACHER（NO，NAME，SEX，BIRTHDAY，PROF，DEPART）

COURSE（CNO，CNAME，TNO）

SCORE（NO，CNO，DEGREE）

其中，学生信息表STUDENT由学号（NO）、学生姓名（NAME）、性别（SEX）、出生年月日（BIRTHDAY）、班级（CLASS）组成，记录学生的情况；教师信息表TEACHER由教师号（NO）、教师姓名（NAME）、性别（SEX）、出生年月日（BIRTHDAY）、职称（FROF）、教师所在系（DEPART）组成，记录教师的情况；课程表COURSE由课程号（CNO）、课程名称（CNAME）、任课教师号（TNO）组成，记录所开课程及任课教师情况；成绩表SCORE由学生学号（NO）、课程号（CNO）、成绩（DEGREE）组成，记录学生选课情况及相应的成绩。用SQL语句实现以下操作

1）显示STUDENT表中每个学生的姓名和出生日期。

SELECT NAME，BIRTHDAY FROM STUDENT；

2）显示STUDENT表中所有姓“王”的学生记录。

SELECT * FROM STUDENT WHERE NAME LIKE　‘王%’；

3）显示成绩表中成绩在60分到80分之间的所有记录。

SELECT *　FROM SCORE WHERE DEGREE BETWEEN 60 AND 80；

4）显示“男”教师及其所上的课程。（显示该教师的姓名和所上的课程名）

SELECT TEACHER.NAME，COURSE.CNAME FROM TEACHER，COURSE

WHERE　TEACHER.NO=COURSE.TNO AND SEX=‘男’；

5）选出和“李军”同学同性别并同班的学生姓名。

SELECT NAME FROM STUDENT

WHERE SEX=（SELECT SEX FROM STUDENT WHERE NAME=‘李军’）AND

CLASS=（SELECT CLASS FROM STUDENT WHERE NAME=‘李军’）；

6）向STUDENT表中插入一条学生记录。（学号：999，姓名：程功，性别：男，出生年月日：10/01/80，班级：95035）

INSERT INTO STYDENT VALUES(‘999’,‘程功’，‘男’，‘10/01/80’,‘95035’)；

7）在STUDENT表中将学号为“999”的学生的班号改为“95031”

UPDATE STUDENT SET=‘95031’WHERE NO=‘999’；

8）在STUDENT表中删除学号为“999”的学生记录。

DELETE FROM STUDENT WHERE NO=‘999’；

4．设有下列四个关系模式：

S（SNO，SNAME，CITY）

P（PNO，PNAME，COLOR，WEIGHT）

J（JNO，JNAME，CITY）

SPJ（SNO，PNO，JNO，QTY）

其中，供应商表S由供应商号（SNO）、供应商姓名（SNAME）、供应商所在城市（CITY）组成，记录各个供应商的情况；零件表P由零件号（PNO）、零件名称（PNAME）、零件颜色（COLOR）、零件重量（WEIGHT）组成，记录各种零件的情况；工程项目表J由项目号（JNO）、项目名（JNAME）、项目所在城市（CITY）组成，记录各个工程项目的情况；供应情况表SPJ由供应商号（SNO）、零件号（PNO）、项目号（JNO）、供应数量（QTY）组成，记录各供应商供应各种零件给各工程项目的数量。  用SQL语言完成下列操作：

①找出工程项目J2使用的各种零件的名称及其数量

SELECT PNAME，QTY  FROM  SPJ, P   WHERE  SPJ.PNO=P.PNO AND JNO=‘J2’；

②找出所有零件的名称、颜色、重量

SELECT PNAME, COLOR, WEIGHT  FROM  P；

③找出上海厂商供应的所有零件号码

SELECT DISTINCT PNO FROM  SPJ

WHERE  SNO  IN　( SELECT  SNO　FROM  S　WHERE  CITY=’上海’)；

④从供应商关系中删除S2（供应商号）的记录，并从供应情况关系中删除相应的记录

DELETE  FROM  S　WHERE  SNO=‘S2’；

DELETE  FROM  SPJ  WHERE  SNO=‘S2’；

⑤请将（S2，P4，J6，200）插入供应情况关系

INSERT INTO  SPJ  VALUS（‘S2’，‘P4’，‘J6’，200）；

5．设有3个关系：

S（S#，SNAME，AGE，SEX）

SC（S#，C#，CNAME）

C（C#，CNAME，TEACHER）

试用关系代数表达式表示下列查询语句：

1）检索LIU老师所授课程的课程号和课程名。

∏C#，CNAME(σTEACHER=‘LIU’(C))

2）检索年龄大于23岁的男学生的学号和姓名。

∏S#，SNAME(σAGE>‘23’∧SEX=‘M’(S))

3）检索学号为S3学生所学课程的课程名与任课教师名。

∏CNAME，TEACHER(σS#=‘S3’(SC∞C))

4）检索至少选修LIU老师所授课程中一门课的女学生姓名。

∏SNAME(σSEX=‘F’∧TEACHER=‘LIU’(S∞SC∞C))

5）检索WANG同学不学的课程的课程名。

∏C#(C)-ΠC#(σSNAME=‘WANG’(S∞SC)

6）检索全部学生都选修的课程的课程号与课程名。

∏C#,CNAME(C∞(πS#，C#(SC) ÷ πS#(S)))

7）检索选修课程包含LIU老师所授全部课程的学生学号。

∏S#，C#(SC)÷∏C#(σTEACHER=‘LIU’(C))

6．对于教学数据库的3个基本表

S（S#，SNAME，AGE，SEX）

SC（S#，C#，CNAME）

C（C#，CNAME，TEACHER）试用关系代数表达式表示下列查询语句：

1 ) 检索LIU老师所授课程的课程号和课程名。

SELECT C#，CNAME　FROM C　WHERE TEACHER= ‘LIU’；

2）检索年龄大于23岁的男学生的学号和姓名。

SELECT S#，SNAME　FROM S　WHERE AGE>23 AND SEX=‘M’；

3）检索学号为S3学生所学课程的课程名与任课教师名。

SELECT CNAME，TEACHER　FROM SC，C　WHERE SC.C# =C.C# AND S#=‘S3’；

4）检索至少选修LIU老师所授课程中一门课的女学生姓名。

连接查询方式

SELECT SNAME FORM S，SC，C

WHERE S.S# =SC.S# AND SC.C# =C.C# AND SEX=‘F’AND TEACHER=‘LIU’；

嵌套查询方式

SELECT SNAME FORM S WHERE SEX =‘F’AND S# IN

(SELECT S#  FORM SC WHERE C#　IN

(SELECT C# FORM C WHERE TEACHER =‘LIU’))；

存在量词方式

SELECT SNAME FORM S WHERE SEX=‘F’AND EXISTS

(SELECT * FORM SC WHERE SC.S# =S.S# AND EXISTS

(SELECT * FORM C WHERE C.C# =SC.C# AND TEACHER=‘LIU’))；

5）检索WANG同学不学的课程的课程名。

SELECT C#　FOME C WHERE NOT EXISTS

（SELECT * FORM S，SC WHERE S.S# =SC.S# AND SC.C# =C.C#　AND SNAME=‘WANG’）；

6）检索至少选修两门课的学生学号。

SELECT DISTINCT X.S#  FROM SC X, SC Y　WHERE X.S#=Y.S# AND X.C#!=Y.C#；

7）检索全部学生都选修的课程的课程号与课程名。

SELECT C#，CNAME FROM C WHERE NOT EXISTS

(SELECT * FROM S WHERE NOT EXISTS

(SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#))；

用集合包含

SELECT C#，CNAME　FROM C　WHERE （SELECT S# FROM SC WHERE C# =C.C# ）

CONTAINS

SELECT S# FROM S；

或

SELECT C#，CNAME　FROM C　WHERE NOT EXISTS

((SELECT S# FROM S) EXCEPT

(SELECT S# FROM SC WHERE C# =C.C#))；

8）检索选修课程包含LIU老师所授课程的学生学号。

SELECT DISTINCT S# FROM SC X WHERE NOT EXISTS

(SELECT * FROM C WHERE TEACHER=‘LIU’AND NOT EXISTS

(SELECT * FROM SC Y WHERE Y.S# =X.S# AND X.C#=C.C#))；

或：SELECT DISTINCT S#　FROM SC X　WHERE NOT EXISTS

((SELECT C# FROM C WHERE TRACHER=‘LIU’) EXCEPT

(SELECT C# FROM SC Y WHERE Y.S# =X.S#))；

7．试用SQL查询语句表达下列对上题中3个基本表S、SC、C的查询：

1）统计有学生选修的课程门数。

SELECT COUNT (DISTINCT C#)　FROM SC；

2）求选修C4课程的女学生的平均年龄。

SELECT AVG(AGE) FROM S，SC  WHERE S.S#=SC.S# AND C# =‘C’AND SEX=‘F’；

3）求LIU老师所授课程的每门课程的平均成绩。

SELECT C.C#，AVG(GRADE) FROM SC，C WHERE SC.C# =C.C# AND TEACHER=‘LIU’

GROUP BY C.C#；

4）统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECT C# ,COUNT(S#) FROM SC

GROUP BY C# HAVING COUNT(*)>10

ORDER BY 2 DESC，1；

5）检索学号比WANG同学大，而年龄比他小的学生姓名。

SELECT SNAME FROM S WHERE S#>ALL (SELECT S# S WHERE SNAME=‘WANG’)

AND AGE<ALL (SELECT AGE FROM S WHERE SNAME=‘WANG’)；

6）在表SC中检索成绩为空值的学生学号和课程号。

SELECT S#，C# FROM SC WHERE GRADE IS NULL；

7）检索姓名以L打头的所有学生的姓名和年龄。

SELECT SNAME，AGE  FROM S WHERE SNAME LIKE ‘L%’；

8）求年龄大于女同学平均年龄的男学生姓名和年龄。

SELECT SNAME，AGE FROM S WHERE SEX=‘M’AND

AGE >(SELECT AVG(AGE) FROM S　WHERE SEX=‘F’)；

9）求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECT SNAME，AGE FROM S WHERE SEX=‘M’AND

AGE >ALL(SELECT AGE FROM S　WHERE SEX=‘F’)；

8．用SQL更新语句表达对上题教学数据库中关系S、SC、C作如下更新操作：

1）往关系C中插一个课程元组。

INSERT INTO C VALUES(‘C8’，‘VC++’，‘BAO’)；

2）SC中删除尚无成绩的选课元组。

DELETE FROM SC WHERE GRADE IS NULL；

3）把选修LIU老师课程的女同学选课元组全部删去。

DELETE FROM SC WHERE S# IN　(SELECT S# FROM S WHERE SEX =‘F’)

AND C# IN(SELECT C# FROM C WHERE TEACHER=‘LIU’)；

4）把MATHS课不及格的成绩全改为60分。

UPDATE SC SET GRADE = 60

WHERE GRADE<60 AND C# IN (SELECT C# FROM C WHERE CNAME=‘MATHS’)；

5）把低于所有课程总平均成绩的女同学成绩提高5%。

UPDATE SC SET GRADE = GRADE *1.05

WHERE S# IN（SELECT S# FROM S WHERE SEX=‘F’）AND GRADE<(SELECT AVG(GRADE) FROM SC)；

6）在表SC中修改C4课程的成绩，若成绩小于等于70分提高5%，若成绩大于70分时提高4%（用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现）。

UPDATE SC　SET GRADE=GRADE * 1.04

WHERE C# ‘C4’AND GRADE>70；

UPDATE SC SET GRADE =GRADE * 1.05

WHERE C # =’C4’AND GRADE<=70;

7）在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。

UPDATE SC SET GRADE = GRADE * 1.05

WHERE GRADE<(SELECT AVG(GRADE) FROM SC)；

9．设数据库中有3个关系：

职工表EMP（E#，ENAME，AGE，SEX，ECITY），其属性分别表示职工工号、姓名、年龄、性别和籍贯。

工作表WORKS（E#，C#，SALARY），其属性分别表示职工工号、工作的公司编号和工资。

公司表 COMP（C#，CNAME，CITY），其属性分别表示公司编号、公司名称和公司所在城市。

试用SQL语言写出下列操作：

1）检索超过50岁的男职工的工号和姓名。

SELECT E#，ENAME FROM EMP  WHERE AGE>50 AND SEX=‘M’；

2）假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。

SELECT EMP.E#，ENAME FROM EMP，WORKS

WHERE EMP.E#=WORKS.E# AND SALARY>1000；

3）假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。

SELECT A.E#，A.ENAME FROM EMP A，WORKS B，WORKS C

WHERE A.E#=B.E# AND B.E#=C.E# AND B.C#=‘C4’AND C.C#=‘C8’；

4）检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。

SELECT A.E#，A.ENAME FROM EMP A，WORKS B，COMP C

WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME=‘联华公司’

AND SALARY>1000 AND SEX=‘M’；

5）假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数，显示（E#，NUM，SUM_SALARY），分别表示工号、公司数目和工资总数。

SELECT E#，COUNT(C#) AS NUM，SUM(SALARY) AS SUM_SALARY

FROM WORKS GROUP BY E#；

6）工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所以公司工作的职工工号。

SELECT X.E# FROM WORKS X WHERE NOT EXISTS

(SELECT * FROM WORKS Y WHERE E#=‘E6’AND NOT EXISTS

(SELECT * FROM WORKS Z WHERE Z.E#=X.E# AND Z.C#=Y.C#))；

7）检索联华公司中低于本公司平均工资的职工工号和姓名。

SELECT A.E#，A.ENAME FROM EMP A，WORKS B，COMP C

WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME =’联华公司’AND SALARY<

(SELECT AVG(SALARY) FROM WORKS，COMP WHERE WORKS.C#=COMP.C# AND CNAME =‘联华公司’)；8）在每个公司中为50岁以上职工加薪100元（若职工为多个公司工作，可重复加）。

UPDATE WORKS SET SALARY =SALARY+100

WHERE E# IN (SELECT E# FROM EMP WHERE AGE>50)；

10）在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。

DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)；

DELETE FROM EMP  WHERE AGE >60；

10．设有关糸模式：SB（SN，SNAME，CITY）其中，SB表示供应商，SN为供应商代号，SNAME为供应商名字，CITY为供应商所在城市，主关键字为SN。

PB(PN，PNAME，COLOR，WEIGHT) 其中PB表示零件，PN为零件代号，PNAME为零件名字，COLOR为零件颜色，WEIGHT为零件重量，主要字键为PN。

JB（JN，JNAME，CITY）其中，JB表示工程，JN为工程编号，JNAME为工程名字，CITY为工程所在城市，主关键字为JN。

SPJB（SN，PN，CITY）其中，SPJB表示供应关糸，SN是为指定工程提供零件的供应商代号，PN为所提供的零代号，JN为工程编号，OTY表示提供的零件数量，主关键字为SN，PN，JN，外关键字为SN，PN，JN。

如图所示表示供应商（S）-零件（P）-工程（J）数据库表，写出实现以下各题功能的SQL语句：

本文链接：https://cloud.tencent.com/developer/article/1557979

本文采用CC BY-NC-SA 3.0 Unported协议进行许可，转载请保留此文章链接