云南农业大学-期中考试A卷-数据库系统概论答案(仅供参考)

期中考试参考答案

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)是否是一个无损分解?是否保持函数依赖?试说明理由。

  • 6
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值