数据库期末复习

假设某大型航空公司数据库，其关系模式（下划线代表主码） 有：

航班表（航班号，起点， 终点， 机型，价格， 折扣）

乘客表（身份证号，姓名，性别，年龄， 会员等级）

订单表（航班号，身份证号，日期，座位号， 票价）

（说明： 订单表中的航班号是外码，引用航班表中航班号； 订单表中的身份证号是 外码， 引用乘客表中的身份证号。）

完成如下题目（每个题目限用一个 SQL 语句实现）：   

1 、写出“订单表”的建表语句（数据类型自己定义，需符合实际意义） 。（3 分）

 1 CREATE TABLE 订单表 (
 2     航班号 VARCHAR(10),
 3     身份证号 CHAR(18),
 4     日期 DATE,
 5     座位号 INT,
 6     票价 DECIMAL(10, 2),
 7     PRIMARY KEY (航班号, 身份证号, 日期, 座位号),
 8     FOREIGN KEY (航班号) REFERENCES 航班表(航班号),
 9     FOREIGN KEY (身份证号) REFERENCES 乘客表(身份证号)
10 );

2、新开通一条石家庄至桂林的航线，机型是中型机，航班号为 CA1008，价格为 1260， 折扣为 0.8 ，将此纪录添加至航班表（2 分）

1 INSERT INTO 航班表 (航班号, 起点, 终点, 机型, 价格, 折扣) 
2 VALUES ('CA1008', '石家庄', '桂林', '中型机', 1260, 0.8);

3 、将机型为“737”的所有航班价格降低 5%。（2 分）

1 UPDATE 航班表
2 SET 价格 = 价格 * 0.95
3 WHERE 机型 = '737';

4 、删除年龄高于 80 岁的乘客记录。（2 分）

1 DELETE FROM 乘客表
2 WHERE 年龄 > 80;

5、查询航班表中航班号第 3 位为“1”的所有航班号、起点、终点和机型。（2 分）

SELECT 航班号, 起点, 终点, 机型
FROM 航班表
WHERE 航班号 LIKE '1___';

6 、查询 2022 年 1 月期间出行的所有白金会员的身份证号、姓名、起点、终点，输 出结果按日期升序排列。 （2 分）

1 SELECT 乘客表.身份证号, 乘客表.姓名, 航班表.起点, 航班表.终点
2 FROM 乘客表
3 JOIN 订单表 ON 乘客表.身份证号 = 订单表.身份证号
4 JOIN 航班表 ON 订单表.航班号 = 航班表.航班号
5 WHERE 乘客表.会员等级 = '白金' AND 订单表.日期 BETWEEN '2022-01-01' AND '2022-01-31'
6 ORDER BY 订单表.日期 ASC;

7、按会员等级统计乘客人数，结果按人数降序排序。（3 分）

1 SELECT 会员等级, COUNT(*) AS 人数
2 FROM 乘客表
3 GROUP BY 会员等级
4 ORDER BY 人数 DESC;

8 、创建视图view1，视图表示的数据为： 2022年5月1日从北京起飞的所有航班的乘 客信息，显示航班号、身份证号、姓名、终点。（3分）

1 CREATE VIEW view1 AS
2 SELECT 航班表.航班号, 订单表.身份证号, 乘客表.姓名, 航班表.终点
3 FROM 航班表
4 JOIN 订单表 ON 航班表.航班号 = 订单表.航班号
5 JOIN 乘客表 ON 订单表.身份证号 = 乘客表.身份证号
6 WHERE 航班表.起点 = '北京' AND 订单表.日期 = '2022-05-01';

9、创建带输入参数的存储过程proc_c，查询某乘客的航班记录， 列出航班号、日期、 起点、终点。（3分）

1 CREATE PROCEDURE proc_c(IN id CHAR(18))
2 BEGIN
3     SELECT 航班表.航班号, 订单表.日期, 航班表.起点, 航班表.终点
4     FROM 订单表
5     JOIN 航班表 ON 订单表.航班号 = 航班表.航班号
6     WHERE 订单表.身份证号 = id;
7 END;

10、创建触发器trig_p，使得删除航班表中的一条记录时，同时删除订单表中该航班 相应的订票记录。（3分）

1 CREATE TRIGGER trig_p AFTER DELETE ON 航班表
2 FOR EACH ROW
3 BEGIN
4     DELETE FROM 订单表 WHERE 订单表.航班号 = OLD.航班号;
5 END;

 

在某仓库管理系统中，有两个表：KC表和CKMX表， 其结构和部分数据如下： ;

表 1 、KC 表结构和数据

材料代码	材料名称	单位	单价	库存数量
01	计算机	台	5000	10
02	电视机	台	2000	20
…	…	…	…	…

表 2 、CKMX 表结构和数据

编号	材料代码	出库日期	出库数量
1	01	2009-10-10	4
2	01	2009-11-20	3
3	02	2009-11-20	5
…	…	…	…

其中， KC 表保存当前仓库库存材料的信息，CKMX 表存放库存材料的出库名细。（例

 

如， KC 表第一行表示该仓库中现有计算机 10 台。CKMX 表第二行表示 2009 年 11 月 20 日

从仓库中出库 3 台计算机。）

完成如下题目（每个题目限用一个 SQL 语句实现）：

1 、写出创建 KC 表和 CKMX 表的 SQL 语句。（数据类型自己定义）。（3 分）

 1 CREATE TABLE KC (
 2     材料代码 CHAR(2),
 3     材料名称 VARCHAR(50),
 4     单位 VARCHAR(10),
 5     单价 DECIMAL(10, 2),
 6     库存数量 INT,
 7     PRIMARY KEY (材料代码)
 8 );
 9 
10 CREATE TABLE CKMX (
11     编号 INT PRIMARY KEY,
12     材料代码 CHAR(2),
13     出库日期 DATE,
14     出库数量 INT,
15     FOREIGN KEY (材料代码) REFERENCES KC(材料代码)
16 );

2 、向 KC 表中添加一条记录，材料代码：09，材料名称：打印机，单位：箱，单价：50，库存数量： 10（3 分）

1 INSERT INTO KC (材料代码, 材料名称, 单位, 单价, 库存数量)
2 VALUES ('09', '打印机', '箱', 50, 10);

3 、删除 CKMX 表中出库数量为空的记录。（3 分）

1 DELETE FROM CKMX WHERE 出库数量 IS NULL;

4 、查询“计算机”的出库总量。（3 分）

1 SELECT SUM(出库数量) AS 出库总量
2 FROM CKMX
3 WHERE 材料代码 = '01';

5、查询 KC 表所有材料的库存情况，并按库存数量从大到小排序。（3 分）

1 SELECT * FROM KC
2 ORDER BY 库存数量 DESC;

6 、从表 CKMX 中统计出各种材料的出库次数和出库总数量。（3 分）

1 SELECT 材料代码, COUNT(*) AS 出库次数, SUM(出库数量) AS 出库总数量
2 FROM CKMX
3 GROUP BY 材料代码;

7 、查询材料名称含“电”字， 单价大于 3000 的材料情况。（3 分）

SELECT * FROM KC
WHERE 材料名称 LIKE '%电%' AND 单价 > 3000;

8、创建视图 view_ 1 ，查询电视机的出库情况。（3 分）

1 CREATE VIEW view_1 AS
2 SELECT CKMX.编号, KC.材料名称, CKMX.出库日期, CKMX.出库数量
3 FROM CKMX
4 JOIN KC ON CKMX.材料代码 = KC.材料代码
5 WHERE KC.材料名称 = '电视机';

9 、创建带输入参数的存储过程 proc_c，根据输入参数查询指定材料代码的材料名称、单位、单价、出库日期、出库数量。（3 分）

1 CREATE PROCEDURE proc_c(IN 材料代码 CHAR(2))
2 BEGIN
3     SELECT KC.材料名称, KC.单位, KC.单价, CKMX.出库日期, CKMX.出库数量
4     FROM KC
5     JOIN CKMX ON KC.材料代码 = CKMX.材料代码
6     WHERE KC.材料代码 = 材料代码;
7 END;

10 、创建触发器 trig_p，使得插入 KC 表中的一条记录时， 默认单位为“台”。（3 分）

1 CREATE TRIGGER trig_p BEFORE INSERT ON KC
2 FOR EACH ROW
3 BEGIN
4     IF NEW.单位 IS NULL THEN
5         SET NEW.单位 = '台';
6     END IF;
7 END

 

假设教学管理系统数据库有3个基本表：

S（ S# ，SNAME，AGE，SEX）

SC（ S#，C#， CNAME）

C（ C# ，CNAME，TEACHER）  

（说明：SC 基本表中的 S# 是外码，引用了 S 基本表的 S# ；SC 基本表中的 C# 是外码，引用了 C 基本表的 C# ）

完成如下题目（每个题目限用一个SQL语句实现）：

1) 写出SC表的建表语句（数据类型自己定义）。（3分）

1 CREATE TABLE SC (
2     S# CHAR(10),
3     C# CHAR(10),
4     PRIMARY KEY (S#, C#),
5     FOREIGN KEY (S#) REFERENCES S(S#),
6     FOREIGN KEY (C#) REFERENCES C(C#)
7 ); 

2) 检索‘LIU’老师所授课程的课程号和课程名。（3分）

1 SELECT C.C#, C.CNAME
2 FROM C
3 WHERE C.TEACHER = 'LIU';

3) 检索年龄大于23岁的男学生的学号和姓名。（3分）

1 SELECT S.S#, S.SNAME
2 FROM S
3 WHERE S.AGE > 23 AND S.SEX = '男';

4) 检索学号为‘S3’的学生所学课程的课程名与任课教师名。（3分）

1 SELECT C.CNAME, C.TEACHER
2 FROM SC
3 JOIN C ON SC.C# = C.C#
4 WHERE SC.S# = 'S3';

5) 检索至少选修‘LIU’老师所授课程中一门课的女学生姓名。（3分）

1 SELECT DISTINCT S.SNAME
2 FROM S
3 JOIN SC ON S.S# = SC.S#
4 JOIN C ON SC.C# = C.C#
5 WHERE S.SEX = '女' AND C.TEACHER = 'LIU';

6) 检索‘WANG’同学不学的课程的课程名。（3分）

1 SELECT C.CNAME
2 FROM C
3 LEFT JOIN SC ON C.C# = SC.C#
4 WHERE SC.S# IS NULL AND SC.S# != 'WANG';

7) 检索至少选修两门课的学生学号。（3分）

1 SELECT SC.S#
2 FROM SC
3 GROUP BY SC.S#
4 HAVING COUNT(SC.C#) >= 2;

8) 检索全部学生都选修的课程的课程号与课程名。（3分）

 1 SELECT C.C#, C.CNAME
 2 FROM C
 3 WHERE NOT EXISTS (
 4     SELECT S.S#
 5     FROM S
 6     WHERE NOT EXISTS (
 7         SELECT SC.S#
 8         FROM SC
 9         WHERE SC.C# = C.C# AND SC.S# = S.S#
10     )
11 );

9) 检索选修课程包含‘LIU’老师所授课程的学生学号。（3分）

1 SELECT SC.S#
2 FROM SC
3 JOIN C ON SC.C# = C.C#
4 WHERE C.TEACHER = 'LIU';

10) 用触发器实现删除学生，则删除该学生所选修的课程。（3分）

 3 CREATE TRIGGER DeleteStudent
 4 BEFORE DELETE ON S
 5 FOR EACH ROW
 6 BEGIN
 7     DELETE FROM SC WHERE SC.S# = OLD.S#;
 8 END