数据库第三章SQL课后习题及答案

第8题和第9题题目

第8题表格

create database xiti8
go
use xiti8
go

create table S(
    sno varchar(20) primary key,
    sname varchar(20),
    city varchar(20))
go

create table P(
    pno varchar(20) primary key,
    pname varchar(20),
    color varchar(20),
    weight varchar(20))
go

create table J(
    jno varchar(20) primary key,
    jname varchar(20),
    city varchar(20))
go

create table SPJ(
    sno varchar(20),
    pno varchar(20),
    jno varchar(20),
    qty int
)
go

insert into S values('s1','精益','天津');
insert into S values('s2','万胜','北京');
insert into S values('s3','东方','北京');
insert into S values('s4','丰泰窿','上海');
insert into S values('s5','万通','武汉');

insert into P values('s1','螺母','红',12);
insert into P values('s2','螺栓','绿',17);
insert into P values('s3','螺丝刀','蓝',14);
insert into P values('s4','螺丝刀','红',14);
insert into P values('s5','凸轮','蓝',40);
insert into P values('s6','螺丝钉','黄',20);

insert into J values('j1','三建','北京');
insert into J values('j2','一汽','长春');
insert into J values('j3','弹簧厂','天津');
insert into J values('j4','造船厂','天津');
insert into J values('j5','机车厂','唐山');
insert into J values('j6','电配厂','武汉');

insert into SPJ values('s1','p1','j1',200);
insert into SPJ values('s1','p1','j3',100);
insert into SPJ values('s1','p1','j4',700);
insert into SPJ values('s1','p2','j1',400);
insert into SPJ values('s2','p3','j2',200);
insert into SPJ values('s2','p3','j4',500);
insert into SPJ values('s2','p3','j5',400);
insert into SPJ values('s2','p5','j1',400);
insert into SPJ values('s2','p5','j2',100);
insert into SPJ values('s3','p1','j1',200);
insert into SPJ values('s3','p3','j1',200);
insert into SPJ values('s4','p5','j1',100);
insert into SPJ values('s5','p6','j2',200);
insert into SPJ values('s5','p6','j4',500);

第8题答案：

update P set pno='p1' where pname='螺母';
update P set pno='p2' where pname='螺栓';
update P set pno='p3' where pname='螺丝刀' and color='蓝';
update P set pno='p4' where pname='螺丝刀' and color='红';
update P set pno='p5' where pname='凸轮';
update P set pno='p6' where color='黄';
--先运行如上更新的SQL代码。
--1.求所有供应商供应项目的零件号pno.
select distinct pno
from SPJ
--2.求供应项目j3零件p1的供应商号sno
select sno
from SPJ
where jno='j3' and pno='p1'
go
--3.求供应项目j1零件的供应商号及姓名
select S.sno,sname
from SPJ,S
where S.sno=SPJ.sno and jno='j1'
go
--4.求供应项目j5红色零件的供应商号及姓名
select S.sno,sname
from S,SPJ
where jno='j1'and pno in(select pno from P where color='红')   --j1换j5
and SPJ.sno=S.sno
go
--5.求没用武汉供应商生产的螺丝钉的项目号jno
select jno
from SPJ
where not exists (select * from S where city='武汉' and S.sno=SPJ.sno)
go

第9题表格：

CREATE TABLE E (
    职工号 INT PRIMARY KEY,
    姓名 VARCHAR(50),
    性别 VARCHAR(10),
    职务 VARCHAR(50),
    家庭地址 VARCHAR(100),
    部门号 INT
);
go
CREATE TABLE D (
    部门号 INT PRIMARY KEY,
    部门名称 VARCHAR(50),
    地址 VARCHAR(100),
    电话 VARCHAR(20)
);
go
CREATE TABLE B (
    保健号 INT PRIMARY KEY,
    职工号 INT,
    检查日期 DATE,
    健康情况 VARCHAR(100)
);
go
INSERT INTO E (职工号, 姓名, 性别, 职务, 家庭地址, 部门号)
VALUES (1, '张三', '男', '经理', '北京市', 1),
       (2, '李四', '女', '助理', '上海市', 2),
       (3, '王五', '男', '职员', '广州市', 1);
go
INSERT INTO D (部门号, 部门名称, 地址, 电话)
VALUES (1, '销售部', '北京市', '010-12345678'),
       (2, '人力资源部', '上海市', '021-98765432');
go
INSERT INTO B (保健号, 职工号, 检查日期, 健康情况)
VALUES (1, 1, '2022-01-01', '健康'),
       (2, 2, '2022-02-01', '健康'),
       (3, 3, '2022-03-01', '健康');
go

第9题答案：

insert into D values (3,'财务部','武汉市','031-12345678');
insert into D values (4,'办公室','南京市','041-23456789');
insert into E values (4,'王敏','女','科长','南京市',4);
insert into E values (5,'李刚','男','科长','武汉市',4);
insert into E values (6,'周晓','女','会计','北京市',3);
insert into E values (7,'黄小雨','女','会计','南宁市',3);
insert into E values (8,'周南','男','职员','长春市',1);
insert into B values (4,4,'2022-04-01','良好');
insert into B values (5,5,'2022-05-01','良好');
insert into B values (6,6,'2022-06-01','一般');
insert into B values (7,7,'2022-07-01','良好');
insert into B values (8,8,'2022-08-01','差');
go

--先运行如上插入的SQL代码。

--1.查找所有女科长的姓名和家庭地址
select 姓名,家庭地址
from E
where 性别='女' and 职务='科长'
go
--2.查找部门名称为“办公室”的科长姓名和家庭住址
select 姓名,家庭地址
from E,D
where 部门名称='办公室' and E.部门号=D.部门号
go
--3.查找部门名称为“财务部”、健康状况为“良好”的职工姓名和家庭地址
select 姓名,家庭地址
from D,B,E
where 部门名称='财务部' and 健康情况='良好' and D.部门号=E.部门号
and B.职工号=E.职工号
go
--4.删除职工关系表中职工号为“3”的记录
delete from E where 职工号=3
go
--insert into E values (3, '王五', '男', '职员', '广州市', 1);

--5.将职工号为“3”的职工的健康状况改为“一般”
update B set 健康情况='一般' where 职工号=3
go
--6.建立健康状况为“差”的职工情况的视图
create view healthzhuangkuang as select * from E
where 职工号 in (select 职工号 from B where 健康情况='差' and B.职工号=E.职工号)
go
--select * from healthzhuangkuang