数据库：第7-8周作业

P60 3，4

3.首先建立第2题中要求的表，表3-2代码如下：

CREATE TABLE 服装关系 (
服装编号 VARCHAR(15) PRIMARY KEY,
品牌 VARCHAR(10) NOT NULL,
型号 VARCHAR(10) NOT NULL,
颜色 VARCHAR(5),
价格 INTEGER
);
INSERT INTO 服装关系
VALUES
('W001','佐丹奴','YH287','蓝',239),
('W002','佐丹奴','JK946','绿',189),
('W003','李宁','LI143','红',499),
('W004','美津浓','U7896','紫',699);

结果如下：

表3-3代码如下：

CREATE TABLE 顾客关系 (
顾客编号 VARCHAR(15) PRIMARY KEY,
姓名 VARCHAR(10),
性别 VARCHAR(5),
年龄 INTEGER,
电话 VARCHAR(15)
);
INSERT INTO 顾客关系
VALUES
('C001','张珊','女',30,'13987475638'),
('C002','李驷','男',25,'13324678967'),
('C003','王武','男',40,'13178453678');

结果如下：

表3-4代码如下：

CREATE TABLE 购买记录关系 (
服装编号 VARCHAR(15),
顾客编号 VARCHAR(15),
购买日期 DATE,
数量 INTEGER,
PRIMARY KEY(服装编号,顾客编号),
FOREIGN KEY(服装编号) REFERENCES 服装关系(服装编号),
FOREIGN KEY(顾客编号) REFERENCES 顾客关系(顾客编号)
);
INSERT INTO 购买记录关系
VALUES
('W001','C001',20100214,1),
('W002','C001',20100214,1),
('W003','C001',20100302,2),
('W003','C002',20100306,1),
('W004','C003',20100306,1);

结果如下：

（1）查询所有“佐丹奴”服装的服装ID、型号、颜色和价格。

（2）查询购买了“美津浓”服装的顾客姓名和电话。

（3）查询没有购买任何服装的顾客的信息。

（4）查询购买过所有“佐丹奴”服装的顾客信息。

4.设有一个简单的教学管理数据库，其关系模式为：
学生（学号、姓名、专业、年龄）
课程（课程号、课程名、学分、先行课课程号）
选修（学号、课程号、成绩）

首先建立表，

三表的初始数据为：

用SQL完成如下查询：
（1）查询“通信工程“专业的所有学生信息。

SELECT * FROM 学生 WHERE 专业 = '通信工程';

（2）查询所有课程的课程号和学分。

SELECT 课程号,学分 FROM  课程;

（3）查询学生张珊所选课程的课程号和成绩。

SELECT 课程号,成绩 FROM 选修 WHERE 学号=(SELECT 学号 FROM 学生 WHERE 姓名='张珊');

（4）查询选了所有课程的学生的学号。

SELECT 学号 FROM 学生
WHERE NOT EXISTS(SELECT * FROM 课程
WHERE NOT EXISTS(SELECT * FROM 选修
WHERE 学号=学生.学号 AND 课程号=课程.课程号));

（5）查询没有选课的学生的信息。

SELECT * FROM 学生 WHERE 学号 NOT IN(SELECT 学号 FROM 选修);

（6）查询选修了“数据库原理”的先行课的学生学号。

SELECT 学号 FROM 选修 
WHERE 课程号 IN(SELECT 课程号 FROM 课程 
WHERE 课程.先行课课程号=(SELECT 课程号 FROM 课程 
WHERE 课程.课程名='数据库原理'));

（7）删除学号为“9501”的同学的所有选课记录。

DELETE FROM 选修 WHERE 学号='9501';

（8）修改张林“数据库原理”课程的成绩为89。

UPDATE 选修 SET 成绩=89 
WHERE 学号=(SELECT 学号 FROM 学生 WHERE 姓名='张林')
AND 课程号=(SELECT 课程号 FROM 课程 
WHERE 课程名='数据库原理');

（9）建立选修了“数据库原理”的所有学生的学号、姓名及成绩视图。

CREATE VIEW V
AS SELECT 学生.学号,姓名,成绩 FROM 学生,选修,课程 
WHERE 学生.学号=选修.学号 AND 选修.课程号=课程.课程号 AND 课程名='数据库原理';

视图为：

（10）在小题9的视图基础上，查询成绩大于90的相关信息。

SELECT * FROM V WHERE 成绩>90;