数据库综合试验设计报告

1、武 汉 科 技 大 学数据库实验综合设计 二一五 - 二一六 学年第 一 学期学 院： 信息科学与工程学院 专 业： 电子信息工程（DB） 班 级： 1301班 学 号： 201212235049 姓 名： 谢明烨 指导老师： 陈彬 二一五年 12 月 14 日数据库综合实验任务书某商业集团公司的“人员-销售”系统的E-R图如下图所示（其中单箭头表示单联系，双箭头表示多联系）：图 “人员-销售”E-R图1. 试根据E-R所示的关系图，在SQLServer数据库系统中建立与之相应的数据表。2. 为建立的数据表中添加必要的测试数据。创建职工表workerCreate table worker(wn

2、o char(9) primary key,wname nchar(20) unique,wsex nchar(2),wbirth smallint,Wgrade char(20),)insert into workervalues(001,周楚,男,1994,3600)insert into workervalues(002,洪云志,男,1995,3950)insert into workervalues(003,李珍,女,1994,4200)insert into workervalues(004,杜姗,女,1994,4000)insert into workervalues(005,范江

3、,男,1993,5000)insert into workervalues(006,王明,男,1992,3000) 创建商店表shop Create table shop(sno char(9) primary key,Sname nchar(20),Splace nchar(20),)insert into shopvalues(1,晨光,武汉)insert into shopvalues(2,淘宝,上海)insert into shopvalues(3,京东,北京)创建聘用表employCreate table employ(Wno char(9) primary key,Sno char

4、(9),Wtime smallint,Wage smallint,Foreign key(sno)references shop(sno),)insert into employvalues(001,1,2016,2000)insert into employvalues(002,2,2017,3000)insert into employvalues(003,3,2017,3500)insert into employvalues(004,2,2018,3000)insert into employvalues(005,3,2017,4000)insert into employvalues

5、(006,1,2016,1800)创建商品表goodsCreate table goods(gno char(9) primary key,gname nchar(20),gprice smallint,Scale int)insert into goodsvalues(1001,钢笔,5,360)insert into goodsvalues(1002,圆珠笔,1,3500)insert into goodsvalues(1003,签字笔,2,9000)insert into goodsvalues(1004,毛笔,5,90)创建销售表gsalecreate table gsale(gno

6、char(9),sno char(9),gsale float,mon int,primary key(gno,sno,mon),foreign key(gno)references goods(gno),foreign key(sno)references shop(sno),)insert into gsale values('1001','1',500,1) insert into gsale values('1002','1',1000,1)insert

7、 into gsale values('1003','1',5000,1)  insert into gsale values('1001','2',600,1) insert into gsale values('1002','2',1200,1) insert into gsale values('1003','2

8、',6000,1) insert into gsale values('1004','2',150,1) insert into gsale values('1001','3',700,1) insert into gsale values('1002','3',1300,1) insert into gsale values(

9、9;1003','3',7000,1) insert into gsale values('1004','3',200,1) insert into gsale values('1001','1',600,2) insert into gsale values('1002','1',1200,2)insert into gsale

10、0;values('1003','1',6500,2)  insert into gsale values('1001','2',800,2) insert into gsale values('1002','2',1500,2) insert into gsale values('1003','2',8000,2) insert

11、 into gsale values('1004','2',200,2) insert into gsale values('1001','3',600,2) insert into gsale values('1002','3',1800,2) insert into gsale values('1003','3',9

12、000,2) insert into gsale  values('1004','3',600,2)  3. 以数据表为基础完成以下查询。A. 查询工号为“001”的员工的基本信息； select*from workerwhere wno='001'B. 查询工号为“001”的员工的工作地点和工资情况；select splace,wagefrom shop,employwhere shop.sno=employ.sno and wno='001'C. 查询商品号为“1001”的商品

13、的基本信息； select* from goodswhere gno='1001'D. 查询商品号为“1001”的商品每个月在商店号为“S001”商店内的销售额；select gsale from gsalewhere gno='1001' and sno='1' and mon=1select gsale from gsalewhere gno='1001' and sno='1' and mon=2E. 查询商品名为“圆珠笔”的商品每个月在每个商店的销售额；select gsale from

14、 gsale,goods where gname='圆珠笔' and goods.gno=gsale.gno and mon=1select gsale from gsale,goods where gname='圆珠笔' and goods.gno=gsale.gno and mon=2F. 查询整个集团“圆珠笔”每个月的销售总额；select sum(gsale)月销售额from gsale,goods whe

15、re goods.gno=gsale.gno and gname='圆珠笔' and mon=1select sum(gsale)月销售额from gsale,goods where goods.gno=gsale.gno and gname='圆珠笔' and mon=2G. 查询每个商品在每个商店的总销售额，结果至少包括商品名，单价，商店名，总销售额等信息；select gname,gprice,sname,sum(gsale)销售总额from goods,shop,g

16、salewhere shop.sno=gsale.sno and goods.gno=gsale.gnogroup by goods.gprice,shop.sname,goods.gnameH. 查询名称为“1号店”的商店人员工资情况，结果至少包括工号，姓名，工资，商店名等信息；select worker.wno,wname,wage,sname  from shop,worker,employ where shop.sno=employ.sno and worker.wno=employ.wno an

17、d shop.sno='1'I. 查询每个商店人员工资情况，结果至少包括工号，姓名，工资，商店名等信息；select worker.wno,wname,wage,sname from shop,worker,employwhere shop.sno=employ.sno and worker.wno=employ.wnoJ. 查询每个商店每月总的工资支出总额；select sno,sum(wage)月工资支出总额from employ group by sno  K. 所有商店都销售了的商品的商品信息；select gno,gname,gprice,scale  from goods where not exists(select *from shop where not exists( select *from gsale where goods.gno=gsale.gno and shop.sno=gsale.sno) L. 至少销售了一号商店所有商品的商店信息select*from sho