21-22数据库原理期末试题

8-1 基本表的建立

学生数据库有三个关系模式：学生表S（sno，sname，age）、课程表C（cno，cname，gredit）、选课表SC（sno，cno，score）。SC表中sno和cno联合作为主键，类型都是char（8），分别引用S的sno和C的cno；score是分数，类型为int，要求0<=score<=100。
1、用SQL语言写出创建SC表的语句。
2、用SQL语言在SC表的score属性上建立一个普通升序索引。
3、在S表上有一个触发事件为update或delete的触发器，请写出一条可触发该触发器执行的语句。

8-2 模式规范化

有关系模式R(A，B，C，D，E)，函数依赖集F={A->C，B->D，B->G，D->E}。
1、判断该关系模式最高属于第几范式，并说明理由。
2、按规范的分解方法将该关系模式分解为3NF，要求写出分解后每个模式的码和函数依赖集。

8-3 数据库建模

有下面的数据库信息：

图书信息包括：书号、书名、作者、数量、出版社、单价、架号。出版社信息包括：出版社号、社名、地址、电话、邮编、信箱。读者信息包括：借书证号、姓名、单位。一个出版社可以出版多种书籍，但每本书只能在一个出版社出版，应有出版日期和责任编辑。每个读者可以借阅多本图书，每本图书可以有多人借阅。借阅信息包括：借书日期、还书日期、是否续借。
1、根据以上信息，画出ER图。可省略属性，但是联系的属性不能省略。
2、根据ER图转换为关系模式的一般原则，将上面的ER图转换为关系模式，并注明码和外码。

10-1 查询学生表所有学生记录

本题目要求编写SQL语句，
检索出stu表中所有学生记录。

表结构:

定义表结构的SQL语句。如：

CREATE TABLE stu(
    sno char(4) NOT NULL,
    sname char(8) NOT NULL,
    sex tinyint NULL,
    mno char(2) NULL ,
    birdate datetime NULL,
    memo text NULL,
  PRIMARY KEY (sno)
);

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

输出样例:

请在这里给出输出样例。例如：

 答案

SELECT * FROM stu;

10-2 查询每个仓库的编号及其获得的订单数量

本题目要求编写SQL语句，
查询每个仓库的编号及其获得的订单数量。

提示：请使用SELECT语句作答。

表结构:

CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);
CREATE TABLE `orders` (
  `OrdNo` int(11),   --订单编号
  `Sid` varchar(10), --供应商编号
  `Eid` varchar(10), --职工编号
  `Pid` varchar(20), --商品编号
  `Price` decimal(10,2), --价格
  `QTY` int(11),     --订购数量
  `ordDate` date    --订单日期
);

表样例

employee表：

Eid	EName	Wno	Salary
0010	张三	A01	3600
0011	刘勇	A01	2700
0012	张立	A01	8500
0021	刘靖	A02	2500
0022	王强	A02	5600
0023	李军		5000
0031	王林		3500

orders表：

OrdNo	Sid	Eid	Pid	Price	QTY	ordDate
1	S01	0011	P01	11	23	2022-02-13
2	S02	0012	P01	12	25	2022-02-14
3	S03	0012	P03	60	55	2022-02-14

输出样例:

请在这里给出输出样例。例如：

Wno	cnt
A01	3

答案

SELECT Wno, COUNT(*) as cnt FROM employee e INNER JOIN orders o ON e.Eid = o.Eid GROUP BY Wno;

10-3 删除重量大于100的产品

本题目要求编写SQL语句，
删除重量大于100的产品。

表结构:

CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight` decimal(10, 3)    --重量
);

表样例

product表：

Pid	PName	Weight
P01	M6螺栓	30
P02	M8螺栓	100
P03	M16螺栓	140
P04	螺帽	30
P05	螺母	45
P06	垫片	10
P07	铰链	70

输出样例:

Pid	PName	Weight
P01	M6螺栓	30
P02	M8螺栓	100
P04	螺帽	30
P05	螺母	45
P06	垫片	10
P07	铰链	70

答案

delete from product where Weight > 100;

10-4 建立’A01’仓库的员工信息视图vEmp

本题目要求编写SQL语句，
建立’A01’仓库的员工信息视图vEmp。

表结构:

CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);

表样例

employee表：

Eid	EName	Wno	Salary
0010	张三	A01	3600
0011	刘勇	A01	2700
0012	张立	A01	8500
0021	刘靖	A02	2500
0031	王林		3500

输出样例:

vEmp

Eid	EName	Wno	Salary
0010	张三	A01	3600
0011	刘勇	A01	2700
0012	张立	A01	8500

答案

create view vEmp as select * from employee where Wno = 'A01';

10-5 查询所有员工中最高工资和最低工资

本题目要求编写SQL语句，
检索出employee表中所有员工中最高工资和最低工资。

提示：请使用SELECT语句作答。

表结构:

CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);

表样例

employee表：

Eid	EName	Wno	Salary
0010	张三	A01	3600
0011	刘勇	A01	2700
0012	张立	A01	8500
0021	刘靖	A02	2500
0022	王强	A02	5600
0023	李军		5000
0031	王林		3500

输出样例:

请在这里给出输出样例。例如：

max_Salary	min_Salary
8500	2500

答案

select max(Salary) as max_Salary , min(Salary) as min_Salary
from employee;

10-6 查询Students表中的所有系名，要求结果中系名不重复

本题目要求编写SQL语句，
检索出students表中所有系名的记录，要求结果中系名不重复。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE students (
 sno char(7) ,
  sname char(8) NOT NULL,
  class char(10),
  ssex char(2),
  bday date ,
  bplace char(10) ,
  IDNum char(18) ,
  sdept char(16),
  phone char(11),
  PRIMARY KEY (sno)
) ;

表样例

请在这里给出上述表结构对应的表样例。例如

students表：

sno	sname	class	ssex	bday	bplace	IDNum	sdept	phone
1311104	李嘉欣	13英语1	女	1994-05-28	山西太原	330204199405281056	人文学院	15900002211
1311105	苏有明	13英语1	男	1995-04-16	内蒙古包头	330204199504162036	人文学院	15900002222
1711101	赵薇	17物流1	女	1999-02-11	安徽合肥	330203199902110925	经管学院	15900001177
1711102	董洁	17物流1	女	1999-02-17	上海	330203199902170017	经管学院	15900001188

输出样例:

请在这里给出输出样例。例如：

sdept
人文学院
经管学院

答案

select distinct sdept from students;

10-7 查询所有姓“刘”的学生信息

本题目要求编写SQL语句，
检索出students表中所有姓“刘”的学生信息。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE students (
 sno char(7) ,
  sname char(8) NOT NULL,
  class char(10),
  ssex char(2),
  bday date ,
  bplace char(10) ,
  IDNum char(18) ,
  sdept char(16),
  phone char(11),
  PRIMARY KEY (sno)
) ;

表样例

请在这里给出上述表结构对应的表样例。例如

students表：

sno	sname	class	ssex	bday	bplace	IDNum	sdept	phone
1411301	刘嘉玲	14网络1	女	1995-07-18	河北邢台	330206199507181426	信息学院	15911114411
1311105	苏有明	13英语1	男	1995-04-16	内蒙古包头	330204199504162036	人文学院	15900002222
1711101	赵薇	17物流1	女	1999-02-11	安徽合肥	330203199902110925	经管学院	15900001177
1811301	刘烨	18信管3	男	1999-12-23	山东烟台	330206199912234634	信息学院	15911113311
1701103	董洁	17物流1	女	1999-02-17	上海	330203199902170017	经管学院	15900001188

输出样例:

请在这里给出输出样例。例如：

sno	sname	class	ssex	bday	bplace	IDNum	sdept	phone
1411301	刘嘉玲	14网络1	女	1995-07-18	河北邢台	330206199507181426	信息学院	15911114411
1811301	刘烨	18信管3	男	1999-12-23	山东烟台	330206199912234634	信息学院	15911113311

答案

select *
from students
where sname like '刘%';

10-8 统计不及格人数超过3人的课程号和人数

本题目要求编写SQL语句，
统计出sc表中不及格人数超过3人的课程号和人数，要求结果中列名分别显示“课程号”、“不及格人数”。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE sc (
 sno char(7) ,
 cno char(7) ,
  score decimal(4,1),
    point decimal(2,1),
  PRIMARY KEY (sno,cno)
) ;

表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

sno	cno	score	point
1711202	0000011	53.0	0.0
1711202	0000027	38.0	1.0
1911201	0000027	44.0	1.0
1911201	0000052	71.0	2.0
1911211	0000027	51.0	1.0
1911211	0000052	71.0	2.0
1911215	0000027	58.0	1.0

输出样例:

请在这里给出输出样例。例如：

课程号	不及格人数
0000027	4

答案

select cno as '课程号',count(sno) as '不及格人数' 
from sc 
where score < 60.0
group by cno
having count(sno) > 3;

10-9 向订单表中插入一条新记录

向订单表中插入一条新记录，订单编号是10331，顾客编号是J101，员工编号是023。

表结构:

订单（订单编号 文本型，顾客编号 文本型，员工编号 文本型,订单日期 日期型,运费 数字型）

表样例

订单表：

订单编号	顾客编号	员工编号	订单日期	运费
10325	J101	023	2018-10-7	50
10326	J102	011	2017-6-16	42
10327	Q113	121	2019-11-8	31
10328	Q114	099	2015-9-18	56
10329	B010	133	2014-1-12	48
10330	B011	023	2014-7-11	64

输出样例:

订单编号	顾客编号	员工编号	订单日期	运费
10325	J101	023	2018-10-7	50
10326	J102	011	2017-6-16	42
10327	Q113	121	2019-11-8	31
10328	Q114	099	2015-9-18	56
10329	B010	133	2014-1-12	48
10330	B011	023	2014-7-11	64
10331	J101	023

答案

insert into 订单
values('10331','J101','023',null,null)

10-10 统计每个学生的选课门数和考试总成绩，并按选课门数升序排列

本题目要求编写SQL语句，
统计``sc``表中，每个学生的选课门数和考试总成绩，并按选课门数升序排列，要求结果中列名分别显示“学号”、“选课门数”、“考试总成绩”。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE sc (
 sno char(7) ,
 cno char(7) ,
  score decimal(4,1),
    point decimal(2,1),
  PRIMARY KEY (sno,cno)
) ;

表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

sno	cno	score	point
1711202	0000011	53.0	0.0
1711202	0000027	80.0	1.0
1711202	0000052	77.0	1.0
1911201	0000027	84.0	1.0
1911201	0000052	71.0	2.0

输出样例:

请在这里给出输出样例。例如：

学号	选课门数	考试总成绩
1411101	1
1401102	3	156.0
1811102	4	296.8
1411201	7	501.6
1511211	7	487.0
1711101	7	525.6
1711202	7	510.0
1911201	7	532.2
1701102	8	635.7
1701101	8	704.1
1711102	9	705.7

 答案

select sno as '学号',count(cno) as '选课门数' , sum(score) as '考试总成绩'
from sc 
group by sno 
order by count(cno);

10-11 将员工表中编号为133的员工的性别修改为“男”

将员工表中编号为133的员工的性别修改为“男”

表结构:

员工（员工编号 文本型，姓名 文本型，性别 文本型，出生日期 日期型，入职日期 日期型，住址 文本型，电话 文本型）

表样例

员工表：

员工编号	姓名	性别	出生日期	入职日期	电话
011	陈诚瑞	男	1988-7-11	2011-7-14	15762338155
023	郭彩霞	女	1987-1-4	2010-7-10	15762348111
099	王新英	女	1978-9-21	2000-7-30	13864352920
121	范鼎毅	男	1995-4-1	2019-7-5	17864354679
133	钟鸣	女	1990-4-21	2016-7-6	18553346120

输出样例:

员工编号	姓名	性别	出生日期	入职日期	电话
011	陈诚瑞	男	1988-7-11	2011-7-14	15762338155
023	郭彩霞	女	1987-1-4	2010-7-10	15762348111
099	王新英	女	1978-9-21	2000-7-30	13864352920
121	范鼎毅	男	1995-4-1	2019-7-5	17864354679
133	钟鸣	男	1990-4-21	2016-7-6	18553346120

 答案

update 员工 set 性别 = '男' where 员工编号 = 133;

10-12 删除选修C语言课程的女生成绩记录

本题目要求编写DELETE语句，删除选修’C语言’课程的女生的选课记录。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `stu` (
  `sno` char(4) NOT NULL, 
  `sname` char(8) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,  --0为女生，1为男生。
  `mno` char(2) DEFAULT NULL,
  `birdate` datetime DEFAULT NULL,
  `memo` text,
  PRIMARY KEY (`sno`)
);
CREATE TABLE `cou` (
  `cno` char(4) NOT NULL,
  `cname` varchar(30) NOT NULL,
  `credit` smallint(6) DEFAULT NULL,
  `ptime` char(5) DEFAULT NULL,
  `teacher` char(10) DEFAULT NULL,
  PRIMARY KEY (`cno`)
)
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`cno`),
  CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
)

表样例

请在这里给出上述表结构对应的表样例。例如

stu表：

cou表：

sc表：

输出样例:

删除记录后，sc表如下：

答案

delete from sc
where sno in(
    select sno
    from stu
    where sex = 0
)and cno in(
    select cno
    from cou
    where cname = 'C语言'
)

10-13 将student表中的数计学院的学生信息插入到stu表中。

题干:

将student表中的数计学院的学生信息插入到stu表中。

提示：请使用INSERT语句作答，stu数据表已存在，结构同student一样。

表结构:

定义表结构的SQL语句如下：

CREATE TABLE student (

sno varchar(6) NOT NULL ,

sname varchar(10) ,

sex char(2) ,

nation char(2) ,

pnum char(18) ,

birth date ,

phone char(11) ,

dept varchar(20) ,

PRIMARY KEY (sno)

) ;

表样例

上述表结构对应的表样例：

student表：

输出样例:

输出样例：

stu表：

 答案

insert into stu (select * from student where dept = '数计学院');

10-14 查询商品表，先按商品分类升序排列，再按商品价格降序排列

本题目要求编写SQL语句，查询sh_goods表，先按商品分类category_id升序排列，对于相同分类的商品再按商品价格price降序排列 。

提示：请使用SELECT语句作答。

表结构:

CREATE TABLE sh_goods (
  id INT  PRIMARY KEY,                         --商品id
  category_id INT  NOT NULL DEFAULT 0 ,        -- 商品分类id
  name VARCHAR(120) NOT NULL,                  --商品名称
  keyword VARCHAR(255) NOT NULL,               -- 关键词编号
  content TEXT NOT NULL ,                      --商品详情
  price DECIMAL(10, 2)  NOT NULL DEFAULT 0 ,   --价格
  stock INT  NOT NULL DEFAULT 0,               -- 库存
  score DECIMAL(3, 2)  NOT NULL DEFAULT 0 ,    -- 用户评分
  comment_count INT  NOT NULL DEFAULT 0        -- 评论数量
) ;

表样例

sh_goods表：

输出样例:

 答案

select name ,category_id,price 
from sh_goods
order by category_id asc , price desc;

10-15 检索没被学生选修的课程编号和课程名称。

题干:

检索没被学生选修的课程编号和课程名称。

提示：请使用SELECT语句作答。

表结构:

定义表结构的SQL语句如下：
CREATE TABLE student (

sno varchar(6) NOT NULL ,

sname varchar(10) ,

sex char(2) ,

nation char(2) ,

pnum char(18) ,

birth date ,

phone char(11) ,

dept varchar(20) ,

PRIMARY KEY (sno)

) ;

CREATE TABLE course (

cno varchar(6) NOT NULL,

cname varchar(20) ,

credit int(11) ,

attribute varchar(10) ,

PRIMARY KEY (cno)

) ;

CREATE TABLE score (

sno varchar(6) NOT NULL,

cno varchar(6) NOT NULL,

term varchar(15),

grade int(11),

PRIMARY KEY (sno,cno)

) ;

表样例

student表：
 

course表：
 

score表：
 

输出样例:

 答案

select cno,cname
from course 
where cno not in(select cno from score);