数据库试题

正大考试

2020-2021-1
2019级计算机科学与技术专业 数据库原理与应用期末考试模拟试卷

考试要求

1. 下载考试客户端，登录考试系统，输入学号、姓名，一定确保正确。
2. 一定先做客观题，提交后，再做主观题。
3. 主观题答题要求：编写项目，完成下列功能，并在考试系统中按要求提交答案代码及截图文件，截图为一定存为JPG格式。

一、 客观题见考试系统（1*10）

二、 应用题（90分）
说明：根据教师授课情况建立数据库。教师表记录所有教师的基本信息，课程表记录每门课程的基本情况，每位教师可以教授多门课程，每个课程可以有多名授课教师。根据题目要求写出对应的SQL命令，并截图。
1.新建用户，用户名为你的姓名（例如：zhangsan），密码为123。（4分）

CREATE USER zhangsan@localhost IDENTIFIED BY ‘123’;
2.创建数据库jxgl，使新建用户此数据库上拥有建表、建视图、建触发器、建存储过程等所有权限。（4分）
CREATE DATABASE jxgl;
mysql -u zhangsan -p******\在cmd中执行
GRANT ALL PRIVILEGES ON jxgl.* TO zhangsan@localhost;
3.利用新建用户连接数据库（如：zhangsan_conn），并在新用户模式下，按照要求及说明建立下列所示的表。（4*3分）。
t_teacher（教师表）
字段 类型 说明
name varchar(12) 姓名（非空）
tea_no varchar(4) 教师编号，主键
sex varchar(3) 性别：男/女
t_birth date 出生日期

t_course(课程表)
字段 类型 说明
course_no varchar(6) 课程编号(主键)
course_name varchar(50) 课程名称(唯一)
course_class int(2) 1：必修；2：选修

t_teacher_course（任课表）
字段 类型 说明
tea_no varchar(4) 教师编号（外键）
course_no varchar(6) 课程编号
class_hour int(2) 课时数（默认32学时）

添加测试数据：

教师表测试数据：
begin
insert into t_teacher values(‘李立’,‘t001’,‘男’,'5-9月-88 ');
insert into t_teacher values(‘张山’,‘t002’,‘男’,‘5-3月-81’);
insert into t_teacher values(‘章明明’,‘t003’,‘女’,‘12-5月-65’);
insert into t_teacher values(‘王静一’,‘t004’,‘女’,‘25-9月-85’);
insert into t_teacher values(‘刘青’,‘t005’,‘男’,‘15-3月-75’);
insert into t_teacher values(‘赵新’,‘t006’,‘男’,‘25-12月-78’);
insert into t_teacher values(‘吴敏’,‘t007’,‘女’,‘13-4月-64’);
end;
课程表测试数据：
begin
insert into t_course values(‘c00001’,‘数学’,1);
insert into t_course values(‘c00002’,‘语文’,1);
insert into t_course values(‘c00003’,‘自然与科学’,1);
insert into t_course values(‘c00004’,‘科学与文化’,1);
insert into t_course values(‘c00005’,‘英语’,1);
insert into t_course values(‘c00006’,‘国际象棋’,2);
insert into t_course values(‘c00007’,‘书法’,2);
end;

教师授课表测试数据：
begin
insert into t_teacher_course values(‘t001’,‘c00001’,64);
insert into t_teacher_course values(‘t001’,‘c00002’,64);
insert into t_teacher_course values(‘t001’,‘c00003’,32);
insert into t_teacher_course values(‘t002’,‘c00001’,64);
insert into t_teacher_course values(‘t002’,‘c00004’,48);
insert into t_teacher_course values(‘t003’,‘c00002’,64);
insert into t_teacher_course values(‘t003’,‘c00003’,32);
insert into t_teacher_course values(‘t004’,‘c00005’,32);
insert into t_teacher_course values(‘t005’,‘c00006’,32);
insert into t_teacher_course values(‘t007’,‘c00007’,48);
end;

CREATE TABLE t_teacher
(
NAME VARCHAR(12) NOT NULL,
tea_no VARCHAR(4) PRIMARY KEY,
sex VARCHAR(3) CHECK(sex IN (‘M’,‘F’)),
t_birth DATE
);
CREATE TABLE t_course
(
course_no VARCHAR(16) PRIMARY KEY,
course_name VARCHAR(50) UNIQUE,
course_class INT(2) CHECK(course_class IN (1,2))
);
CREATE TABLE t_teacher_course
(
tea_no VARCHAR(4),
course_no VARCHAR(6),
class_hour INT(2) DEFAULT 32
);
ALTER TABLE t_teacher_course
ADD CONSTRAINT FK_ID FOREIGN KEY(tea_no)
REFERENCES t_teacher(tea_no);

INSERT INTO t_teacher VALUES(‘lili’,‘t001’,‘M’,‘1988-9-5’);
INSERT INTO t_teacher VALUES(‘zhangshan’,‘t002’,‘M’,‘1981-3-5’);
INSERT INTO t_teacher VALUES(‘zhangmm’,‘t003’,‘F’,‘1965-5-12’);
INSERT INTO t_teacher VALUES(‘wangjingyi’,‘t004’,‘F’,‘1985-9-25’);
INSERT INTO t_teacher VALUES(‘liuqing’,‘t005’,‘M’,‘1975-3-15’);
INSERT INTO t_teacher VALUES(‘zhaoxin’,‘t006’,‘M’,‘1978-12-25’);
INSERT INTO t_teacher VALUES(‘wumin’,‘t007’,‘F’,‘1964-4-13’);

INSERT INTO t_course VALUES(‘c00001’,‘math’,1);
INSERT INTO t_course VALUES(‘c00002’,‘chinese’,1);
INSERT INTO t_course VALUES(‘c00003’,‘natureAscience’,1);
INSERT INTO t_course VALUES(‘c00004’,‘scienceAculture’,1);
INSERT INTO t_course VALUES(‘c00005’,‘english’,1);
INSERT INTO t_course VALUES(‘c00006’,‘chess’,2);
INSERT INTO t_course VALUES(‘c00007’,‘calligraphy’,2);

INSERT INTO t_teacher_course VALUES(‘t001’,‘c00001’,64);
INSERT INTO t_teacher_course VALUES(‘t001’,‘c00002’,64);
INSERT INTO t_teacher_course VALUES(‘t001’,‘c00003’,32);
INSERT INTO t_teacher_course VALUES(‘t002’,‘c00001’,64);
INSERT INTO t_teacher_course VALUES(‘t002’,‘c00004’,48);
INSERT INTO t_teacher_course VALUES(‘t003’,‘c00002’,64);
INSERT INTO t_teacher_course VALUES(‘t003’,‘c00003’,32);
INSERT INTO t_teacher_course VALUES(‘t004’,‘c00005’,32);
INSERT INTO t_teacher_course VALUES(‘t005’,‘c00006’,32);
INSERT INTO t_teacher_course VALUES(‘t007’,‘c00007’,48);

4.修改任课表（t_teacher_course），为其设置主键约束，并为“任课表”的课程编号设置外键约束。（5分）
ALTER TABLE t_teacher_course
ADD CONSTRAINT PK_ID PRIMARY KEY(tea_no,course_no);
5.为“教师表”增加一个联系电话属性列，要求唯一。(5分)
ALTER TABLE t_teacher
ADD lxdh INT(11);
ALTER TABLE t_teacher
ADD CONSTRAINT tter_lxdh UNIQUE(lxdh);
6. 查询课程名涉及“科学”的所有必修课程信息；（5分）
SELECT course_name FROM t_course
WHERE (course_name LIKE ‘science%’ OR course_name LIKE ‘%science’) AND course_class=1;
7.查询每位教师的编号及授课总学时，结果按授课学时降序排序；（5分）
SELECT tea_no,SUM(class_hour) AS sum_hour
FROM t_teacher_course
GROUP BY tea_no
ORDER BY sum_hour DESC;
8. 查询年龄在50岁以上的教师姓名、编号及出生日期。（5分）
SELECT name,tea_no,t_birth FROM t_teacher
WHERE (YEAR(CURDATE())-YEAR(t_birth)) > 50;
9. 将“语文”课程的课时数在原有的基础上提升10%。（5分）
UPDATE t_teacher_course
SET class_hour = class_hour * 1.1
WHERE course_no=(SELECT course_no FROM t_course WHERE course_name=‘chinese’);
10. 查询每门课程的课程号及该门课程的授课教师人数、授课平均课时数。（5分）
SELECT course_no,count(tea_no) AS ct_course,AGV(class_hour) AS agv_class
FROM t_teacher_course
GROUP BY course_no;
11.创建一个关于“教师授课情况”的视图，包括所有教师的姓名、编号、所授课程的课时和名称，要求视图中体现“参与授课和未授课”的所有教师情况，运行结果截图显示视图内容。（5分）

CREATE VIEW teacher_course
AS SELECT t.name,t.tea_no,class_hour,course_name
FROM t_teacher t LEFT JOIN t_teacher_course tc ON t.tea_no=tc.tea_no
LEFT JOIN t_course c ON c.course_no=tc.course_no;

SELECT * FROM teacher_course;
12. 查询授课门数在3门（含3门）及以上的教师姓名及授课门数。（5分）
/*
SELECT A.NAME,COUNT(C.course_no)
FROM t_teacher A,t_teacher_course C
WHERE A.tea_no = C.tea_no
GROUP BY C.tea_no
HAVING COUNT(C.course_no) >= 3;
*/
13. 查询学生名以及对应的选修课程名以及授课教师名。（10分）
删掉
14. 创建一个存储过程，根据教师编号，输出该教师的授课总课时。执行该存储过程（以“t005”为例,给出运行结果截图）（10分）
DELIMITER@@
CREATE PROCEDURE sum_class(
IN stea_no VARCHAR(4)
)

BEGIN
SELECT SUM(class_hour) AS sum_course FROM t_teacher_course
WHERE stea_no = tea_no
GROUP BY tea_no;
END@@

DELIMITER ;
CALL sum_class(‘t005’);

15. 创建一个触发器，当删除课程表中的记录时，将授课信息表中对应的该门课程的相关记录一并删除。并对触发器进行验证（10分）
    CREATE TRIGGER del_class
    BEFORE DELETE
    ON t_course
    FOR EACH ROW
    BEGIN
    DELETE FROM t_teacher_course WHERE course_no=OLD.course_no;
    END
    我后来又打了一遍，这样是对的，但在我的电脑上，我需要这样

DELIMITER@@
CREATE TRIGGER mytrigger
BEFORE DELETE ON t_course FOR EACH ROW
BEGIN
DELETE FROM t_teacher_course WHERE course_no = OLD.course_no;
END@@

我的end需要后面有个东西，我也不知道为什么，不然在end这一行报错。