实验目的
- 掌握复杂SQL语句
- 掌握E-R模型
实验内容
1、针对以下关系模式:
E企业（企业编号，企业名）
M工厂（厂编号，厂名，厂址，所属企业编号）
S职工（职工号，姓名，性别，聘期，工资(注意定义为货币类型)，进厂时间，体重，所属厂编号）
*品（产品编号，产品名，规格说明）
P生产（生产编号，厂编号，产品编号，数量，生产日期）
创建好数据库，在SQLserver Management Studio中按照顺序打开并执行以下三个*.sql文件：
1-DatabaseFHP建库.sql
2-DatabaseFHP建表.sql
3-DatabaseFHP添加数据.sql
按要求执行以下查询，写出查询语句，并且把查询结果抓图
1.查询“大宝集团”下属分厂的情况
SELECT Fid,Fname,Faddress,Factory.Eid,Ename
FROM Factory,Enterprise
WHERE Enterprise.Eid=Factory.Eid and Enterprise.Eid='京006'
2.查询生产“TV set”的工厂信息和产品名称
SELECT Factory.Fid,Fname,Faddress,Eid,Gname
FROM Goods,Produce,Factory
WHERE Goods.Gcomment='thisis a tv set'and Produce.Gid=Goods.Gid and Produce.Fid=Factory.Fid
3.查询生产“camera”的企业集团信息和产品名称
SELECT Enterprise.Ename,Enterprise.Eid,Gname
FROM Goods,Produce,Factory,Enterprise
WHERE Goods.Gcomment='thisis a camera'and Produce.Gid=Goods.Gid and Produce.Fid=Factory.Fid and Enterprise.Eid=Factory.Eid
4.查询08年（包含08年）以后引进过男职工的工厂信息
SELECT Factory.Fid,Fname,Faddress
FROM Factory,Worker
WHERE Wsex='male'and Factory.Fid=Worker.Fid and Wcomedate>='2008-1-1'
GROUP BY Factory.Fid,Fname,Faddress
5.查询产量为4000的产品详情
预查询：
查询各产品的工厂号、产品号、合计产量
SELECT Fid,Gid,sum(Pcount) FROM Produce GROUP BY Fid,Gid
HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
SELECT Goods.Gid,Gname,Gcomment,sum(Pcount)
FROM Produce,Goods
GROUP BY Fid,Goods.Gid,Gname,Gcomment
Having sum(Pcount) = 4000
6.查询其他厂中比clothing1厂所有职工进厂时间都晚的职工信息
SELECT *
FROM Worker
WHERE Wcomedate>(SELECT Max(Worker.Wcomedate) FROM Worker,Factory WHERE Worker.Fid=Factory.Fid and Factory.Fname='clothing1')
2、某医院病房计算机管理系统中需要管理一下信息。
科室：科室名、科室地址、科室电话、医生姓名
病房：病房号、床位号、所属科室
医生：工作证号、姓名、性别、职称、所属科室名
病人：病历号、姓名、性别、诊断记录、主管医生、病房号
其中，一个科室有多个病房、多名医生，一个病房只属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。
根据以上需求分析的情况，画出该计算机管理系统中有关信息的E-R图。
附件一：
--建立数据库
create database DatabaseFHP;附件二：
--打开数据库
use DatabaseFHP;
--建立企业表
create table Enterprise(
Eid nchar(10) primary key,
Ename varchar(20) not null);
--建立工厂表
create table Factory(
Fid nchar(10) primary key,
Fname varchar(20)not null,
Faddress varchar(30),
Eid nchar(10) foreign key references Enterprise(Eid));
--建立职工表
create table Worker(
Wid nchar(10) primary key,
Wname varchar(20) not null,
Wsex char(10) constraint S1 check(Wsex='male'or Wsex='female'),
Wstaytime int not null default 3,
Wsalary money,
Wcomedate smalldatetime constraint W2 check(Wcomedate between '20000101' and '20091231'),
Wweight numeric(4,1) constraint W3 check(Wweight between 30.0 and 120.0),
Fid nchar(10) foreign key references Factory(Fid));
--建立商品表
create table Goods(
Gid nchar(10) primary key,
Gname varchar(20)not null,
Gcomment varchar(20));
--建立生产表
create table Produce(
Produceid int IDENTITY(1,1) primary key,
Fid nchar(10)not null foreign key references Factory(Fid),
Gid nchar(10)not null foreign key references Goods(Gid),
Pcount int,
Pdatetime datetime not null DEFAULT GETDATE());
--建立有关索引
create unique index IndexWname on Worker(Wname);附件三：
--向企业表中插入数据
insert into Enterprise(Eid,Ename)
values('京001','甜蜜蜜candy集团');
insert into Enterprise(Eid,Ename)
values('京002','形式toy企业');
insert into Enterprise(Eid,Ename)
values('京003','王芳时尚制衣集团');
insert into Enterprise(Eid,Ename)
values('京004','李丽电子集团');
insert into Enterprise(Eid,Ename)
values('京005','刘青玩具集团');
insert into Enterprise(Eid,Ename)
values('京006','大宝集团');
insert into Enterprise(Eid,Ename)
values('京007','强盛五金集团');
--向工厂表中插入数据
insert into Factory(FID,Fname,Faddress,Eid)
values('abc东01','candy1','place1','京001');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc东02','candy2','place2','京001');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc西01','wine1','place11','京002');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc南01','clothing1','place111','京003');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc南02','clothing2','place222','京003');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc北01','pc1','place1','京004');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc北02','pc2','place2','京005');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc中01','milk1','place1','京006');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc中02','milk2','place4','京006');
insert into Factory(FID,Fname,Faddress,Eid)
values('abc国01','camera','place3','京007');
--向职工表中插入数据
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s001','王络','male',3,'20050505',112.1,'abc南02');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s002','王春露','female',6,'20000505',42,'abc北02');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s003','徐房','male',3,'20010102',52.1,'abc北02');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s004','韩春','female',3,'20070505',61.1,'abc国01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s014','刘春光','female',3,'20010505',41.1,'abc中02');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s005','韩春侠','female',3,'20070505',61.1,'abc中01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s006','王法','male',3,'20070501',91.1,'abc西01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s007','张磊','male',6,'20080305',46.2,'abc东01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s008','张海亚','female',3,'20070502',61.1,'abc国01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s009','王建勋','male',9,'20080505',111.1,'abc东01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s010','马甲','male',3,'20060505',84.2,'abc北01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s011','赵凤菊','female',3,'20090505',41.1,'abc南01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s012','刘彻','male',3,'20091105',41.1,'abc北01');
insert into Worker(Wid,Wname,Wsex,Wstaytime,Wcomedate,Wweight,FID)
values('s013','刘高','male',4,'20010505',111.1,'abc南01');
--向产品表中插入数据
insert into Goods(Gid,Gname)
values('1000','cotton candy');
insert into Goods(Gid,Gname)
values('1001','hard candy');
insert into Goods(Gid,Gname)
values('1002','soft candy');
insert into Goods(Gid,Gname)
values('1003','alcohol candy');
insert into Goods(Gid,Gname)
values('1004','soap candy');
insert into Goods(Gid,Gname)
values('2001','canon');
insert into Goods(Gid,Gname)
values('2002','aigo');
insert into Goods(Gid,Gname)
values('2003','lenovo');
insert into Goods(Gid,Gname)
values('3001','philips');
insert into Goods(Gid,Gname)
values('3002','LG');
insert into Goods(Gid,Gname)
values('3003','sumsung');
insert into Goods(Gid,Gname)
values('3004','panisonic');
insert into Goods(Gid,Gname)
values('4001','ibm');
insert into Goods(Gid,Gname)
values('4002','polo');
--向生产表中插入数据
insert into Produce(Fid,Gid,Pcount)
values('abc东02','1000',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc中02','1003',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc东02','1000',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc东02','2003',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc国01','1000',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc中02','3003',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc北01','1002',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc北01','3003',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc西01','3001',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc南02','3003',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc北02','2001',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc北02','2003',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc北02','4001',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc国01','4002',4000);
insert into Produce(Fid,Gid,Pcount)
values('abc国01','4001',2000);
insert into Produce(Fid,Gid,Pcount)
values('abc国01','4002',4000);
--补填一些数据
--把所有职工的工资设为1800元
update Worker set wsalary=1800.0;
update goods set Gcomment='thisis a candy' where gid like '1%';
update goods set Gcomment='thisis a camera' where gid like '2%';
update goods set Gcomment='thisis a tv set' where gid like '3%';
update goods set Gcomment='thisis a computer' where gid like '4%';