数据库期末考试练习

原创已于 2022-08-06 15:53:16 修改 · 334 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2021-12-23 11:30:33 首次发布

/***********************************************************
学号：
姓名：
班级：

注意：1.在每个题目下面写答案，不要随意调整题目顺序
2.答题空白大小可以调整
***********************************************************/

/*******************************************/
/*** 一、基础知识题 ***/
/*******************************************/

/*假如要设计一个教师授课数据库，其中包括教师实体，教师有教师工号，教师姓名，教师性别和教师职称等信息；课程实体，有课程编号，课程名称，学时，学分等信息；教材实体，有教材ISBN，教材名称，出版社，作者等信息。一个教师可以担任多门课程，一门课程可由多名教师教授，一个教师可以选用多种教材，一种教材可以被多名教师选用，请根据以上说明完成以下题目：*/

/*1.根据以上分析，画出该数据库的E-R图（概念模型结构）；*/

/*2.将上面绘制的E-R图转换成关系模式。*/

/*************************************************************************************/
/***二、数据定义题 ***/
/*************************************************************************************/

/*1.按照上述表结构，使用SQL命令创建Tstudent表（学生信息表）。*/
USE xsgl;

CREATE TABLE Tstudent
(
sno CHAR(12) NOT NULL ,
sname VARCHAR(10) NOT NULL,
ssex enum('男','女') not NULL,
sbirthday date not NULL,
spolite VARCHAR(10),
saddress VARCHAR(30),
sphone VARCHAR(12),
PRIMARY KEY(sno)
)

ALTER TABLE Tstudent DROP sxx;

INSERT into Tstudent
VALUES
('2008010001','王琳琳','女','1990-03-14','党员','河南省淮阳县','18538656761'),
('2008010002','江军','男','1989-04-21','非党员','河南省太康县','18538533153'),
('2008010003','沈俊逸','男','1991-01-03','党员','河南省南华县','12345678977'),
('2008010004','赵妞','女','1990-05-12','非党员','河南省周口市','32156486531');

/*2.根据上述表结构说明，使用SQL命令创建Tscore表（课程信息表）。*/

CREATE TABLE Tscore
(
sno CHAR(12) NOT NULL ,
kno CHAR(6) NOT NULL ,
score FLOAT,
PRIMARY KEY(sno,kno)
)

INSERT INTO Tscore
VALUES
('2008010001','A08001',89),
('2008010001','A08002',94),
('2008010001','A08003',58),
('2008010002','A08001',88),
('2008010003','A08001',79);

/*3.为学生信息表（Tstudent）添加一个字段，字段名sxx，表示学生所在学校，varchar(50)类型，默认值为“周口师范学院”。*/
ALTER TABLE Tstudent add sxx VARCHAR(50) DEFAULT '周口师范学院';

/*4.修改学生信息表（Tstudent），将政治面貌（spolite）的数据类型修改为varchar(20)。*/
ALTER TABLE Tstudent MODIFY spolite VARCHAR(20);

/*5.为成绩表（Tscore）添加一个外键约束，成绩表（Tscore）的学号(sno)参照学生信息表(Tstudent)的学号(sno)。*/
ALTER TABLE Tscore
ADD FOREIGN KEY Tscore(sno) REFERENCES Tstudent(sno);

/*6. 创建学生成绩信息查询的视图，视图名为View_SGQuery，其中包含学生学号、课程编号、课程名称、学分和成绩信息。*/
CREATE VIEW View_SGQuery
AS SELECT Tstudent.sno,Tscore.kno,kname,credit,score
FROM Tstudent JOIN Tscore
ON Tstudent.sno=Tscore.sno
JOIN tcourse
ON tcourse.kno=Tscore.kno

SHOW CREATE View View_SGQuery

/*******************************************/
/*** 三、数据查询题 ***/
/*******************************************/

/*1.查询学生信息表（Tstudent）中第一条学生的基本信息。*/
SELECT * FROM Tstudent LIMIT 0,1;

/*2.查询所有课程的课程名称和学分。*/
SELECT kname,credit FROM tcourse

/*3.查询性别为“女”并且是党员的学生的基本信息。*/
SELECT *
FROM Tstudent
WHERE ssex='女' AND spolite IS NOT null;

/*4.查询姓名中带“琳”字的学生的所有信息。*/
SELECT *
FROM Tstudent
where sname like '%琳%';

/*5.查询选修了课程编号为“A08002”的学生的学号、姓名、性别、出生日期信息，并按照出生日期升序排列。*/
SELECT Tstudent.sno,sname,ssex,sbirthday
FROM Tstudent JOIN Tscore
ON Tstudent.sno=Tscore.sno
WHERE kno='A08002'
ORDER BY sbirthday;

/*6.查询每个学生的学号、姓名以及该学生目前所有选修课程的平均成绩信息。*/
SELECT Tstudent.sno,sname,avg(score)
FROM Tstudent join Tscore
ON Tstudent.sno=Tscore.sno

/*7.查询'200801010001'学生成绩最高的课程名称。*/
SELECT kno,MAX(score)
FROM Tscore
WHERE sno='200801010001';

/*****************************************/
/***四、数据管理题）***/
/*****************************************/

/*1.用SQL 命令向学生信息表（Tstudent）添加一条记录，学号：200801010005，姓名：张峰林，性别：男，出生日期：1992-09-21，政治面貌：党员，家庭住址：河南省周口市。*/
INSERT INTO Tstudent
VALUES
('200801010005','张峰林','男','1992-09-21','党员','河南省周口市',NULL,NULL)

/*2.2.用SQL 命令将所有不及格的成绩更新为60。*/
UPDATE Tscore
SET score=60
where score<60;

/*3.用SQL 命令创建用户xh1，密码123456，并授予tstudent表上select权限。*/
CREATE USER
'xh1'@'localhost' IDENTIFIED BY'123456'
GRANT SELECT ON Tstudent TO'xh1'@'localhost';

/*4.用SQL命令将Tstudent表数据进行备份，文件存放在D盘下,名字为student，类型为.xls。*/
SELECT* FROM Tstudent INTO OUTFILE'student.xls';

/****************************************************************/
/***五、编程题 ***/
/****************************************************************/

/*1. 创建存储过程。
功能：根据输入的类型输出相应的男生人数或女生人数。
存储过程名：CountNumBysex
输入参数：1 、2、其他
输出信息：如果输入type=1，输出男学生的人数，如果输入type=2，输出女学生的人数，如果输入其他的数字，输出Error Input!*/

/*2.创建触发器。
功能：当学生选课时向成绩表添加一条记录时，自动将该门课程的已选人数增加1。
触发器名字：AddCourseTrigger*/
CREATE TRIGGER AddCourseTrigger AFTER INSERT
ON Tstudent FOR EACH ROW
BEGIN