CREATEDATABASE库存管理goUSE库存管理GOCREATETABLE仓库(仓库号CHAR(5)PRIMARYKEY,城市CHAR(10),面积INTCHECK(面积>0))GOCREATETABLE职工(仓库号CHAR(5)FOREIGNKEYREFERENCES仓库,职工号CHAR(5)PRIMARYKEY,工资INTCHECK(工资>=1000AND工资<=5000)DEFAULT1200)GOCREATETABLE供应商(供应商号CHAR(5)PRIMARYKEY,供应商名CHAR(20),地址CHAR(20))GOCREATETABLE订购单(职工号CHAR(5)NOTNULLFOREIGNKEYREFERENCES职工,供应商号CHAR(5)NULLFOREIGNKEYREFERENCES供应商,订购单号CHAR(5)PRIMARYKEY,订购日期DATETIMEDEFAULTGetDate())GOINSERTINTO仓库VALUES('WH1','北京',370)INSERTINTO仓库VALUES('WH2','上海',500)INSERTINTO仓库VALUES('WH3','广州',200)INSERTINTO仓库VALUES('WH4','武汉',400)GOINSERTINTO职工VALUES('WH2','E1',1220)INSERTINTO职工VALUES('WH1','E3',1210)INSERTINTO职工VALUES('WH2','E4',1250)INSERTINTO职工VALUES('WH3','E6',1230)INSERTINTO职工VALUES('WH1','E7',1250)GOINSERTINTO供应商VALUES('S3','振华电子厂','西安')INSERTINTO供应商VALUES('S4','华通电子公司','北京')INSERTINTO供应商VALUES('S6','607厂','郑州')INSERTINTO供应商VALUES('S7','爱华电子厂','北京')GOINSERTINTO订购单VALUES('E3','S7','OR67','2002-06-23')INSERTINTO订购单VALUES('E1','S4','OR73','2002-07-28')INSERTINTO订购单VALUES('E7','S4','OR76','2002-05-25')INSERTINTO订购单VALUES('E6',NULL,'OR77',NULL)INSERTINTO订购单VALUES('E3','S4','OR79','2002-06-13')INSERTINTO订购单VALUES('E1',NULL,'OR80',NULL)INSERTINTO订购单VALUES('E3',NULL,'OR90',NULL)INSERTINTO订购单VALUES('E3','S3','OR91','2002-07-13')GOselect*from仓库select*from职工select*from订购单select*from供应商select供应商名from供应商where地址='北京'select订购单号from订购单where供应商号='S6'select*from订购单where职工号='E6'and供应商号='S6'select职工号,仓库号from职工where职工号in(select职工号from订购单where供应商号='S3')select*from职工where职工号notin(select职工号from订购单where供应商号='S3')select*from供应商where供应商号notin(select供应商号from订购单where供应商号isnotnull)select*from供应商where地址='北京'and供应商号in(select供应商号from订购单where职工号='E1')and供应商号in(select供应商号from订购单where职工号='E3')select工资from职工where职工号in(select职工号from订购单Awhere供应商号in(select供应商号from供应商where供应商名='华通电子公司'))select供应商名from供应商where供应商号notin(select供应商号from订购单where供应商号isnotnulland职工号in(select职工号from职工where工资<1220))select城市from仓库where仓库号in(select仓库号from职工where职工号in(select职工号from订购单where供应商号='S4'))select职工号from职工where仓库号in(select仓库号from仓库where城市='上海')and职工号in(select职工号from订购单where供应商号='S6')select职工号from职工where仓库号in(select仓库号from仓库where城市='广州')and职工号in(select职工号from订购单where供应商号='S6')and职工号notin(select职工号from订购单where供应商号!='S6')select职工号from职工where仓库号in(select仓库号from仓库where城市='北京')andexists(select*from订购单where供应商号='S4'and职工号=职工.职工号)andnotexists(select*from订购单where供应商号!='S4'and职工号=职工.职工号)select职工号from职工where仓库号in(select仓库号from仓库where城市='北京')and职工号in(select职工号from订购单where供应商号='S4')and职工号notin(select职工号from订购单where供应商号!='S4')select订购单号from订购单where供应商号in(select供应商号from供应商where地址='北京')and职工号in(select职工号from职工where工资>1230)selectcount(distinct仓库号)from仓库select*from仓库where面积=(select(max)面积from仓库)selectavg(面积)平均面积from仓库selectavg(面积)from仓库...
