期中考试参考答案
1、设有如下关系,
写出实现如下功能的 SQL 语句:
S(Sno,Sname,SEX,AGE,DEPT) C(Cno, CN) SC(Sno, Cno,GRADE)
(1) 查询计科系 18-20 岁的男生;
SELECT * FROM S WHERE DEPT = '计科' AND AGE BETWEEN 18 AND 20 AND SEX = '男';
(2) 查询选修了“数据结构”程的学生名单;
SELECT S.Sname
FROM S
JOIN SC ON S.Sno = SC.Sno
JOIN C ON SC.Cno = C.Cno
WHERE C.CN = '数据结构';
(3) 查询每门课程的平均分,按照从高到低排序
SELECT C.CN, AVG(GRADE) AS AverageGrade
FROM SC
JOIN C ON SC.Cno = C.Cno
GROUP BY C.CN
ORDER BY AverageGrade DESC;
(4) 查询有三门以上课程不及格的学生;
SELECT S.Sno, S.Sname
FROM S
JOIN SC ON S.Sno = SC.Sno
WHERE GRADE < 60
GROUP BY S.Sno, S.Sname
HAVING COUNT(*) >= 3;
(5) 查询平均成绩最高的学生;
SELECT top 1 S.Sno, S.Sname
FROM S
JOIN SC ON S.Sno = SC.Sno
GROUP BY S.Sno, S.Sname
ORDER BY AVG(GRADE) DESC
(6) 查询每位同学高于自己平均成绩的课程
SELECT S.Sno, C.CN
FROM S
JOIN SC ON S.Sno = SC.Sno
JOIN C ON SC.Cno = C.Cno
WHERE SC.GRADE > (SELECT AVG(GRADE) FROM SC WHERE Sno = S.Sno);
(7) 查询选课人数少于 15 位同学的课程;
SELECT C.CN
FROM C
JOIN SC ON C.Cno = SC.Cno
GROUP BY C.CN
HAVING COUNT(DISTINCT SC.Sno) < 15;
(8)查询全部同学都选修了的课程:(关系代数)
π_CNO(C) - π_CNO(π_SNO(SC) ⨝ S) = ∅
(9) 将李敏同学的成绩增加 10%;
UPDATE SC
SET GRADE = GRADE * 1.1
WHERE Sno = (SELECT Sno FROM S WHERE Sname = '李敏');
(10)将 student 表修改姓名的权限和删除权限授予 U1,并允许 U1 转授
GRANT UPDATE, DELETE ON student TO U1 WITH GRANT OPTION;
2、设有如下关系表;
Ware(wno,wname,adress),Product(Pno, Pname, Speci, Price, Corp, Place)Storage(wno,pno,num, Ls)。其中,Ware 为仓库,wno 为仓库编号,wname 为仓库名称,adress 为仓库地址。Product为商品表,Pno 为商品编号,Pname 为商品名,Speci 为规格型号,Price 为价格,Corp 为公司place 为产地。Storage 为存放表,num 为数量,Ls 为货架。请完成以下 SQL 语句:
(11)查询“美的电器公司”生产的“空气加湿器”;
SELECT *
FROM Product
WHERE Corp = '美的电器公司' AND Pname = '空气加湿器';
(12) 查询名称中带有“护肤”的商品;
SELECT *
FROM Product
WHERE Pname LIKE '%护肤%';
(13)统计每座仓库中存放的电冰箱数量;
SELECT Ware.wno, Ware.wname, COALESCE(SUM(Storage.num), 0) AS TotalNumOfRefrigerators
FROM Ware
LEFT JOIN Storage ON Ware.wno = Storage.wno
LEFT JOIN Product ON Storage.pno = Product.Pno
WHERE Product.Pname = '电冰箱'
GROUP BY Ware.wno, Ware.wname;
(14) 查询产地为“北京”的“台式计算机”最高价;
SELECT MAX(Product.Price) AS MaxPrice
FROM Product
WHERE Corp = '北京' AND Pname = '台式计算机';
(15)统计所有仓库存放商品的总货价。
SELECT SUM(Product.Price * Storage.num) AS TotalValue
FROM Storage
JOIN Product ON Storage.pno = Product.Pno;
二、创建触发器
1.在 Storage 表上创建一个触发器,名称为 S-U,规定每座仓库不同编号的商品的数量不超过 1000;
CREATE TRIGGER S_U
ON Storage
AFTER INSERT, UPDATE
AS
BEGIN
declare @Count int;
declare @wno int;
declare @pno int;
select @wno=wno,@pno=pno from inserted;
SELECT @Count=COUNT(DISTINCT pno) FROM Storage where wno=@wno
print @wno
print @Count
IF @Count>1000
BEGIN
delete from Storage where wno=@wno and pno=@pno
END
END;
表结构
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for ware
-- ----------------------------
DROP TABLE IF EXISTS `ware`;
CREATE TABLE `ware` (
`wno` int(11) NOT NULL,
`wname` varchar(50) DEFAULT NULL,
`adress` varchar(100) DEFAULT NULL,
PRIMARY KEY (`wno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of ware
-- ----------------------------
INSERT INTO `ware` VALUES ('1', '开心仓库', '大理');
INSERT INTO `ware` VALUES ('2', '不开心仓库', '昆明');
INSERT INTO `ware` VALUES ('3', '快乐仓库', '海南');
INSERT INTO `ware` VALUES ('4', '五号仓库', '天津');
INSERT INTO `ware` VALUES ('5', '默默仓库', '莆田');
/*
Navicat MySQL Data Transfer
Source Server : root
Source Server Version : 50726
Source Host : localhost:3306
Source Database : table_show
Target Server Type : MYSQL
Target Server Version : 50726
File Encoding : 65001
Date: 2023-11-29 19:40:00
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for storage
-- ----------------------------
DROP TABLE IF EXISTS `storage`;
CREATE TABLE `storage` (
`wno` int(11) DEFAULT NULL,
`pno` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`ls` varchar(50) DEFAULT NULL,
KEY `wno` (`wno`),
KEY `pno` (`pno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of storage
-- ----------------------------
INSERT INTO `storage` VALUES ('1', '1', '5', '货架1');
INSERT INTO `storage` VALUES ('1', '2', '10', '货架2');
INSERT INTO `storage` VALUES ('1', '3', '8', '货架3');
INSERT INTO `storage` VALUES ('1', '4', '3', '货架4');
INSERT INTO `storage` VALUES ('2', '1', '3', '货架3');
INSERT INTO `storage` VALUES ('2', '5', '2', '货架5');
INSERT INTO `storage` VALUES ('2', '6', '4', '货架6');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`pno` int(11) NOT NULL,
`pname` varchar(50) DEFAULT NULL,
`speci` varchar(50) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`corp` varchar(50) DEFAULT NULL,
`place` varchar(50) DEFAULT NULL,
PRIMARY KEY (`pno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', '空气加湿器', '高档', '100.00', '美的电器公司', '杭州');
INSERT INTO `product` VALUES ('2', '电冰箱1', '大型', '50.00', '腾讯', '上海');
INSERT INTO `product` VALUES ('3', '护肤品', '高档', '30.00', '迪奥', '北京');
INSERT INTO `product` VALUES ('4', '荣耀电冰箱', '低档', '800.00', '公司3', '产地1');
INSERT INTO `product` VALUES ('5', '商品5', '电冰箱', '900.00', '公司4', '产地2');
INSERT INTO `product` VALUES ('6', '台式计算机', '商品', '1500.00', '公司5', '北京');
INSERT INTO `product` VALUES ('7', '商品7', '空调', '5000.00', '公司A', '上海');
INSERT INTO `product` VALUES ('8', '护肤品', 'sk', '777.00', '公司B', '海南');
三、分析题(仅供参考)
1、设有关系模式R(试卷编号 A,试题编号 B,内容 C,难度D,知识点 E,题型 F,答案 G,出题教师H,所属系 K),试回答下列问题: (1)写出模式R 的关键码和基本依赖集 (2) R是第几范式?为什么? (3) 将R分解为高一级范式。
关系模式 R 的关键码和基本依赖集: (1)关键码:试卷编号 A 和 试题编号 B 构成关系模式 R 的关键码。 基本依赖集:关键码中的属性对应的函数依赖关系,即 {A, B} → {C, D, E, F, G, H, K}。 (2)R∈2NF,因为有传递关系所以不是3NF (3) R1(试卷编号 A, 试题编号 B, 内容 C, 难度 D, 知识点 E, 题型 F, 答案 G, 所属系 K) R2(试卷编号 A, 试题编号 B, 出题教师 H)
2、设有关系模式R(ABCDE),F是R上成立的FD 集,F=[AB-C,C-D,D-E},分解p-{ABC,CD,DE)是否是一个无损分解?是否保持函数依赖?试说明理由。