1、完整版石油大学华东数据库原理实验答案sql语句实验三/*CREATE TABLE UNITNO(单位代码 VARCHAR(20), 单位名称 VARCHAR(30) );CREATE TABLE OILWELL(井号 VARCHAR(20), 井别 VARCHAR(20), 单位代码 VARCHAR(20), ); CREATE TABLE CONSTRUCTIONUNIT(施工单位名称 VARCHAR(30) );CREATE TABLE GOODSNO(物码 VARCHAR(20), 名称规格 VARCHAR(30), 计量单位 VARCHAR(20) ); CREATE TABLE CO
2、ST(单据号 VARCHAR(20), 预算单位 VARCHAR(20), 井号 VARCHAR(20), 预算金额 FLOAT, 预算人 VARCHAR(20), 预算日期 DATE, 开工日期 DATE, 完工日期 DATE, 施工单位 VARCHAR(30), 施工内容 VARCHAR(50), 材料费 FLOAT, 人工费 FLOAT, 设备费 FLOAT, 其他费用 FLOAT, 结算金额 FLOAT, 结算人 VARCHAR(20), 结算日期 DATE, 入账金额 FLOAT, 入账人 VARCHAR(20), 入账日期 DATE ); CREATE TABLE MATERIAL
3、PRICE(单据号 VARCHAR(20), 物码 VARCHAR(20), 消耗数量 BIGINT, 单价 FLOAT ); */ /*INSERT INTO UNITNO(单位代码,单位名称)VALUES(1122,采油厂);INSERT INTO UNITNO(单位代码,单位名称)VALUES(112201,采油一矿);INSERT INTO UNITNO(单位代码,单位名称)VALUES(112202,采油二矿);INSERT INTO UNITNO(单位代码,单位名称)VALUES(112201001,采油一矿一队);INSERT INTO UNITNO(单位代码,单位名称)VALU
4、ES(112201002,采油一矿二队);INSERT INTO UNITNO(单位代码,单位名称)VALUES(112201003,采油一矿三队);INSERT INTO UNITNO(单位代码,单位名称)VALUES(112202001,采油二矿一队);INSERT INTO UNITNO(单位代码,单位名称)VALUES(112202002,采油二矿二队);INSERT INTO OILWELL(井号,井别,单位代码)VALUES(y001,油井,112201001);INSERT INTO OILWELL(井号,井别,单位代码)VALUES(y002,油井,112201001);INS
5、ERT INTO OILWELL(井号,井别,单位代码)VALUES(y003,油井,112201002);INSERT INTO OILWELL(井号,井别,单位代码)VALUES(s001,水井,112201002);INSERT INTO OILWELL(井号,井别,单位代码)VALUES(y004,油井,112201003);INSERT INTO OILWELL(井号,井别,单位代码)VALUES(s002,水井,112201001);INSERT INTO OILWELL(井号,井别,单位代码)VALUES(s003,水井,112201001);INSERT INTO OILWEL
6、L(井号,井别,单位代码)VALUES(y005,油井,112201002);INSERT INTO CONSTRUCTIONUNIT(施工单位名称)VALUES(作业公司作业一队);INSERT INTO CONSTRUCTIONUNIT(施工单位名称)VALUES(作业公司作业二队);INSERT INTO CONSTRUCTIONUNIT(施工单位名称)VALUES(作业公司作业三队);INSERT INTO GOODSNO(物码,名称规格,计量单位)VALUES(wm001,材料一,吨);INSERT INTO GOODSNO(物码,名称规格,计量单位)VALUES(wm002,材料二
7、,米);INSERT INTO GOODSNO(物码,名称规格,计量单位)VALUES(wm003,材料三,桶);INSERT INTO GOODSNO(物码,名称规格,计量单位)VALUES(wm004,材料四,袋);INSERT INTO COST(单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费, 人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期)VALUES(ZY2016001,112201001,y001,10000.00,张三,2016-5-1,2016-5-4,2016-5-25,作业公司作业一队,
8、 堵漏,7000.00,2500.00,1000.00,1400.00,11900.00,李四,2016-5-26,11900.00,王五,2016-5-28); INSERT INTO COST(单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费, 人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期)VALUES(ZY2016002,112201002,y003,11000.00,张三,2016-5-1,2016-5-4,2016-5-23,作业公司作业二队, 检泵,6000.00,1500.00,1000.00,
9、2400.00,10900.00,李四,2016-5-26,10900.00,王五,2016-5-28); INSERT INTO COST(单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费, 人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期)VALUES(ZY2016003,112201002,s001,10500.00,张三,2016-5-1,2016-5-6,2016-5-23,作业公司作业二队, 调剖,6500.00,2000.00,500.00,1400.00,10400.00,李四,2016-5-26
10、,10400.00,王五,2016-5-28); INSERT INTO COST(单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费, 人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期)VALUES(ZY2016004,112202001,s002,12000.00,张三,2016-5-1,2016-5-4,2016-5-24,作业公司作业三队, 解堵,6000.00,2000.00,1000.00,1600.00,10600.00,李四,2016-5-26,10600.00,赵六,2016-5-28); INS
11、ERT INTO COST(单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费, 人工费,设备费,其他费用,结算金额,结算人,结算日期)VALUES(ZY2016005,112202002,y005,12000.00,张三,2016-5-1,2016-5-4,2016-5-28,作业公司作业三队, 防砂,7000.00,1000.00,2000.00,1300.00,11300.00,李四,2016-6-1); INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016001,wm001,200,
12、10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016001,wm002,200,10.00); INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016001,wm003,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016001,wm004,100,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016002,wm001,200
13、,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016002,wm002,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016002,wm003,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016003,wm001,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016003,wm002,200
14、,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016003,wm003,250,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016004,wm001,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016004,wm002,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016004,wm004,200
15、,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016005,wm001,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016005,wm002,200,10.00);INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES(ZY2016005,wm004,300,10.00);UPDATE COSTSET 人工费=人工费+200.00,结算金额=结算金额+200.00WHERE 单据号=zy2016005;D
16、ELETEFROM COSTWHERE 入账金额 IS NULL OR 入账人 IS NULL OR 入账日期 IS NULL;BEGIN TRANUPDATE COSTSET 人工费=人工费+200.00,结算金额=结算金额+200.00WHERE 单据号=zy2016005;DELETEFROM COSTWHERE 入账金额 IS NULL OR 入账人 IS NULL OR 入账日期 IS NULL;ROLLBACK TRAN实验四CREATE INDEX yDATE ON COST(预算日期);CREATE INDEX jDATE ON COST(结算日期); CREATE INDEX
17、 rDATE ON COST(入账日期);SELECT DISTINCT * FROM COST,UNITNOWHERE 单位名称=采油一矿二队AND 预算单位=单位代码 AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1AND 入账日期=2016-5-1 AND 结算日期=2000;SELECT DISTINCT 单据号FROM COSTWHERE 施工单位 =作业公司作业
18、二队;SELECT 单据号,施工单位FROM COST WHERE 施工单位 =作业公司作业二队UNIONSELECT 单据号,施工单位FROM COSTWHERE 施工单位 =作业公司作业一队;SELECT DISTINCT 施工单位FROM COST,OILWELL,UNITNOWHERE 预算单位=UNITNO.单位代码 AND UNITNO.单位代码=OILWELL.单位代码 AND 单位名称 LIKE 采油一矿%;CREATE TABLE 数据表(施工单位 VARCHAR(30), 年月 VARCHAR(20), 结算金额 FLOAT );INSERT INTO 数据表(施工单位,年
19、月,结算金额)(SELECT 施工单位,YEAR(结算日期)*100+MONTH(结算日期),SUM(结算金额) FROM COST GROUP BY 施工单位,YEAR(结算日期)*100+MONTH(结算日期); UPDATE COST SET 结算人=李兵 WHERE 预算单位 IN( SELECT 单位代码 FROM UNITNO WHERE 单位代码=预算单位 AND 单位名称 Like 采油一矿% );DELETE FROM COSTWHERE 预算单位 IN (SELECT 单位代码 FROM UNITNO WHERE 单位代码=预算单位 AND 单位名称 Like 采油一矿%)
20、; BEGIN TRAN UPDATE COST SET 结算人=李兵 WHERE 预算单位 IN( SELECT 单位代码 FROM UNITNO WHERE 单位代码=预算单位 AND 单位名称 Like 采油一矿% );DELETE FROM COSTWHERE 预算单位 IN( SELECT 单位代码 FROM UNITNO WHERE 单位代码=预算单位 AND 单位名称 Like 采油一矿%);ROLLBACK TRANDROP INDEX yDATE ON COST;DROP INDEX jDATE ON COST;DROP INDEX rDATE ON COST;实验五ALTE
21、R TABLE 数据表 ADD 备注 CHAR; SELECT * FROM 数据表; ALTER TABLE 数据表ALTER COLUMN 施工单位 VARCHAR(30) NOT NULL;ALTER TABLE 数据表 ADD PRIMARY KEY(施工单位); DELETE FROM 数据表; DROP TABLE 数据表 ;ALTER TABLE UNITNO ALTER COLUMN 单位代码 VARCHAR(20) NOT NULL; ALTER TABLE UNITNO ADD CONSTRAINT UNI_NUM PRIMARY KEY(单位代码); ALTER TABL
22、E OILWELL ALTER COLUMN 井号 VARCHAR(20) NOT NULL; ALTER TABLE OILWELL ADD CONSTRAINT WELL_NUM PRIMARY KEY(井号); ALTER TABLE GOODSNO ALTER COLUMN 物码 VARCHAR(20) NOT NULL; ALTER TABLE GOODSNO ADD CONSTRAINT GOOD_NUM PRIMARY KEY(物码); ALTER TABLE COST ALTER COLUMN 单据号 VARCHAR(20) NOT NULL; ALTER TABLE COST
23、 ADD CONSTRAINT COST_NUM PRIMARY KEY( 单据号); ALTER TABLE MATERIALPRICE ALTER COLUMN 单据号 VARCHAR(20) NOT NULL; ALTER TABLE MATERIALPRICE ALTER COLUMN 物码 VARCHAR(20) NOT NULL; ALTER TABLE MATERIALPRICE ADD PRIMARY KEY(单据号,物码);ALTER TABLE CONSTRUCTIONUNIT ALTER COLUMN 施工单位名称 VARCHAR(30) NOT NULL; ALTER
24、TABLE CONSTRUCTIONUNIT ADD CONSTRAINT UNI_NAME PRIMARY KEY(施工单位名称); INSERT INTO MATERIALPRICE VALUES(zy2016001,wm004,100,10);INSERT INTO MATERIALPRICE VALUES(zy2016002,NULL,200,10);ALTER TABLE OILWELLALTER COLUMN 单位代码 VARCHAR(20) NOT NULL;ALTER TABLE OILWELLADD CONSTRAINT UNI_NUM2 FOREIGN KEY(单位代码) REFERENCES UNITNO(单位代码); ALTER TABLE COST ALTER COLUMN 井号 VARCHAR(20) NOT NULL; ALTER TABLE COST ADD CONSTRAINT WELL_NUM2 FOREIGN KEY(井号) REFERENCES OILWELL(井号); ALTER TABLE MATERIALPRICE ALTER COLUMN 物码 VARCHAR(20) NOT NULL; ALTER TABLE MATERIALPRICE ADD CONSTRAINT GOOD_
