正大考试
2020-2021-1
2019级计算机科学与技术专业 数据库原理与应用期末考试模拟试卷
考试要求
- 下载考试客户端,登录考试系统,输入学号、姓名,一定确保正确。
- 一定先做客观题,提交后,再做主观题。
- 主观题答题要求:编写项目,完成下列功能,并在考试系统中按要求提交答案代码及截图文件,截图为一定存为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’);
- 创建一个触发器,当删除课程表中的记录时,将授课信息表中对应的该门课程的相关记录一并删除。并对触发器进行验证(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这一行报错。