数据库题集二和参考 答案.docVIP

PAGE PAGE 4 班级 学号 姓名 重修标记创建一个名为CS的超市销售数据库，该数据库中有3张表，超市表C(Cno,Cname,CITY),其属性分别表示超市号，超市名，超市所在城市。商品表P(Pno, Pname, COLOR, PRICE),其属性分别表示商品号，商品名，商品颜色，商品单价。销售表CP(Cno, Pno,QTY)，其属性分别表示超市号，商品号和销售量。一家超市可销售多种商品，一种商品可被多家超市销售。试用SQL语言实现如下操作： 班级 学号 姓名 重修标记 C+学号 超市表 Cno Cname CITY 111 南昌旺中旺 南昌 112 上海沃尔玛 上海 P+学号 商品表 Pno Pname COLOR PRICE 1 压力锅 银灰 400 2 牛奶 乳白 50 CP+学号 销售表 Cno Pno QTY 111 1 60 112 1 100 一、表的创建（共12分） 分别创建以上3张表。要求：超市表以Cno为主码，Cname不重名；商品表以Pno为主码；销售表是以（Cno,Pno）为主码，Cno,Pno分别为外码。（每张表4分） CREATE TABLE C (Cno int primary key, Cname varchar(50) UNIQUE, CITY varchar(20)); 框架全（列名、数据类型对）2分；每个约束各1分。 CREATE TABLE P (Pno int primary key, Pname varchar(30),COLOR varchar(20),PRICE FLOAT); 框架全（列名、数据类型对）3分；约束1分。 CREATE TABLE CP (Cno int,Pno int, QTY INT, primary key(Pno,Cno),Foreign Key(Pno)REFERENCES P(Pno), Foreign Key(Cno)REFERENCES C (Cno)); 框架全（列名、数据类型对）1分；每个约束各1分。 二、数据的增删改（共22分。） 插入以上3张表中的数据。（6分） INSERT INTO C VALUES(111,南昌旺中旺,南昌); INSERT INTO C VALUES(112,上海沃尔玛, 上海); INSERT INTO P VALUES(1,压力锅,银灰, 400); INSERT INTO P VALUES(2,牛奶,乳白,50); INSERT INTO CP VALUES(111,1,60); INSERT INTO CP VALUES(112,1,100); 查找超市名最后一个字是’旺’的超市信息。（3分） SELECT * FROM C WHERE Cname LIKE %旺; 查找所有商品信息，并将结果按PRICE升序排列。（3分） SELECT * FROM P ORDER BY PRICE;升序为ASC默认，降序是DESC 查找所有超市的超市号、超市名称及其销售总数量。（4分） SELECT o,Cname,SUM(QTY) FROM C,CP WHERE O=O GROUP BY o,Cname; 将所有商品的价格涨10%。（3分） UPDATE P SET PRICE=PRICE*1.1 删除‘牛奶’商品的信息。（3分） DELETE FROM CP WHERE Pno IN(SELECT PNO FROM P WHERE Pname=牛奶); DELETE FROM P WHERE Pname=牛奶; 三、数据库安全性设定（共10分，每小题2分） 创建名为L1的登录，密码为‘123’。 create login L1 with password=123; 为CS超市数据库创建一个与L1登录相对应的用户L1。 create user L1 FOR LOGIN L1; 以L1登录名重新连接登录数据库，查看是否能查询CP表？ 否 以sa或windows身份验证重连数据库，将CP表的查询权限授予L1用户。 GRANT select on c to L1; 以L1登录名重新连接登录数据库，查看是否能查询CP表？ 是 四、设计存储过程和触发器（共16分，每小题8分） 在CP数据库中创建存储过程P_CP_QTY, 以商品号为参数，计算查询出此种商品的销售总量。存储过程创建后，用代码执行该存储过程，查看计算结果是否正确。 CREATE PROCEDURE P_CP_QTY(@pno INT,@qty INT OUTPUT) AS SELECT @qty=S