石油大学(华东)数据库原理实验答案(sql语句).doc

实验三
/*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 COST(单据号 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 MATERIALPRICE(单据号 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(单位代码,单位名称)VALUES('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');
INSERT 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 OILWELL(井号,井别,单位代码)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','材料二','米');
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','作业公司作业一队',
'堵漏','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','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','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');

INSERT 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','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','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','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','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 COST
SET 人工费=人工费+200.00,结算金额=结算金额+200.00
WHERE 单据号='zy2016005';

DELETE
FROM COST
WHERE 入账金额 IS NULL OR 入账人 IS NULL OR 入账日期 IS NULL;

BEGIN TRAN

UPDATE COST
SET 人工费=人工费+200.00,结算金额=结算金额+200.00
WHERE 单据号='zy2016005';

DELETE
FROM COST
WHERE 入账金额 IS NULL OR 入账人 IS NULL OR 入账日期 IS NULL;

ROLLBACK TRAN

实验四
CREATE INDEX yDATE ON COST(预算日期);
CREATE INDEX jDATE ON COST(结算日期);

CREATE INDEX rDATE ON COST(入账日期);

SELECT DISTINCT * FROM COST,UNITNO
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28' AND 预算金额 IS NOT NULL AND 预算日期 IS NOT NULL AND 预算人 IS NOT NULL;

SELECT DISTINCT * FROM COST,UNITNO
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28' AND 结算金额 IS NOT NULL AND 结算人 IS NOT NULL AND 结算日期 IS NOT NULL;

SELECT DISTINCT MATERIALPRICE.单据号,物码,消耗数量,单价
FROM COST,MATERIALPRICE,UNITNO
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28'AND MATERIALPRICE.单据号=COST.单据号;

SELECT DISTINCT * FROM COST,UNITNO
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28' AND 入账金额 IS NOT NULL AND 入账人 IS NOT NULL AND 入账日期 IS NOT NULL;

SELECT 预算总额=SUM(DISTINCT 预算金额)
FROM UNITNO,COST
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28';

SELECT 结算总额=SUM(DISTINCT 结算金额)
FROM UNITNO,COST
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28';

SELECT 入账总额=SUM(DISTINCT 入账金额)
FROM UNITNO,COST
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28';

SELECT 一矿入账总额=SUM(DISTINCT 入账金额)
FROM UNITNO,COST
WHERE 单位名称 LIKE '采油一矿%'AND 预算单位=单位代码 AND 入账日期>='2016-5-1'AND 入账日期<='2016-5-28';

SELECT DISTINCT 入账人
FROM COST
WHERE 入账人 IS NOT NULL;

SELECT DISTINCT 单据号
FROM COST
WHERE 结算日期>='2016-5-1' AND 结算日期<='2016-5-28' AND 入账金额 IS NULL AND 结算金额 IS NOT NULL ;

SELECT DISTINCT 单据号,入账金额
FROM COST,UNITNO
WHERE 单位名称='采油一矿二队'AND 预算单位=单位代码
ORDER BY 入账金额 DESC;

SELECT 单位名称,结算金额总和=SUM(DISTINCT 结算金额),项目数=COUNT(DISTINCT 结算金额)
FROM COST,UNITNO
WHERE 预算单位=单位代码
GROUP BY 单位名称;

SELECT distinct 单据号,物码,消耗数量,单价
FROM MATERIALPRICE
WHERE 物码 IN(
SELECT 物码
FROM GOODSNO
WHERE 名称规格='材料三')
AND 单价*消耗数量>=2000;

SELECT DISTINCT 单据号
FROM COST
WHERE 施工单位 ='作业公司作业二队';

SELECT 单据号,施工单位
FROM COST
WHERE 施工单位 ='作业公司作业二队'
UNION
SELECT 单据号,施工单位
FROM COST
WHERE 施工单位 ='作业公司作业一队';

SELECT DISTINCT 施工单位
FROM COST,OILWELL,UNITNO
WHERE 预算单位=UNITNO.单位代码 AND UNITNO.单位代码=OILWELL.单位代码 AND 单位名称 LIKE '采油一矿%';

CREATE TABLE 数据表(施工单位 VARCHAR(30),
年月 VARCHAR(20)
结算金额 FLOAT
);

INSERT INTO 数据表(施工单位,年月,结算金额)
(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 COST
WHERE 预算单位 IN
(SELECT 单位代码
FROM UNITNO
WHERE 单位代码=预算单位 AND 单位名称 Like '采油一矿%');

BEGIN TRAN

UPDATE COST
SET 结算人='李兵'
WHERE 预算单位 IN(
SELECT 单位代码
FROM UNITNO
WHERE 单位代码=预算单位 AND 单位名称 Like '采油一矿%'
);

DELETE
FROM COST
WHERE 预算单位 IN(
SELECT 单位代码
FROM UNITNO
WHERE 单位代码=预算单位 AND 单位名称 Like '采油一矿%');

ROLLBACK TRAN

DROP INDEX yDATE ON COST;
DROP INDEX jDATE ON COST;
DROP INDEX rDATE ON COST;

实验五

ALTER 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 TABLE 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
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 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 OILWELL
ALTER COLUMN 单位代码 VARCHAR(20) NOT NULL;
ALTER TABLE OILWELL
ADD 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_NUM2 FOREIGN KEY(物码) REFERENCES GOODSNO(物码);

INSERT INTO OILWELL(井号,井别,单位代码)VALUES('y007','油井','112203002');
INSERT INTO MATERIALPRICE(单据号,物码,消耗数量,单价)VALUES('zy2016007','wm006','100','10.00');

UPDATE COST
SET 施工单位='作业公司作业五队'
WHERE 单据号='ZY2016001';

DELETE
FROM UNITNO
WHERE 单位名称='采油二矿二队' AND 单位代码='112202002';

UPDATE GOODSNO
SET 物码='wm04'
WHERE 物码='wm004';

BEGIN TRAN

UPDATE COST
SET 施工单位='作业公司作业五队'
WHERE 单据号='ZY2016001';

DELETE
FROM UNITNO
WHERE 单位名称='采油二矿二队' AND 单位代码='112202002';

UPDATE GOODSNO
SET 物码='wm04'
WHERE 物码='wm004';

ROLLBACK TRAN

ALTER TABLE UNITNO
ALTER COLUMN 单位名称 VARCHAR(30) NOT NULL;
ALTER TABLE UNITNO ADD UNIQUE(单位名称);

ALTER TABLE OILWELL
ALTER COLUMN 单位代码 VARCHAR(20) NOT NULL;
ALTER TABLE OILWELL
ADD CONSTRAINT OIL_CHECK CHECK(井别 IN('油井','水井'));

ALTER TABLE GOODSNO
ALTER COLUMN 名称规格 VARCHAR(30) NOT NULL;
ALTER TABLE GOODSNO ADD UNIQUE(名称规格);

ALTER TABLE GOODSNO
ALTER COLUMN 计量单位 VARCHAR(20) NOT NULL;

ALTER TABLE MATERIALPRICE
ALTER COLUMN 消耗数量 BIGINT NOT NULL;

ALTER TABLE MATERIALPRICE
ALTER COLUMN 单价 FLOAT NOT NULL;

ALTER TABLE COST
ALTER COLUMN 预算人 VARCHAR(20) NOT NULL;

BEGIN TRANSACTION
GO
CREATE VIEW COST_PRICE
AS
SELECT COST.*,物码,消耗数量,单价
FROM COST,MATERIALPRICE
where COST.单据号=MATERIALPRICE.单据号;

SELECT 单据号,物码
FROM COST_PRICE
WHERE 物码='wm002';

SELECT 单据号,单价
FROM COST_PRICE
WHERE 单据号='ZY2016001'and 物码='wm001';

GO
CREATE VIEW YUSUAN
AS
SELECT 单据号,预算单位,井号,预算金额,预算人,预算日期
FROM COST;

INSERT
INTO YUSUAN
VALUES('zy2016008','112202002','y005',10000,'张三', '2016-07-02');

ROLLBACK */

实验六

BEGIN TRANSACTION
INSERT INTO COST VALUES('zy2016006','112202002','y005',10000,'张三', '07-01-2016' ,'07-04-2016','07-25-2016','作业公司作业一队','堵漏',7000,2500,1000,1400,11900,'李四','07-26-2016',11900,'王五','07-28-2016');
IF @@ERROR<>0
BEGIN
PRINT '第一条插入出错'
ROLLBACK
END
ELSE
BEGIN
PRINT '第一条插入成功'
COMMIT TRANSACTION
END

BEGIN TRANSACTION
insert into MATERIALPRICE values('zy2016006','wm001',200,10);
IF @@ERROR<>0
BEGIN
PRINT '第二条插入出错'
ROLLBACK
END
ELSE
BEGIN
PRINT '第二条插入成功'
COMMIT TRANSACTION
END

BEGIN TRANSACTION
insert into MATERIALPRICE values('zy2016006','wm002',200,10);
IF @@ERROR<>0
BEGIN
PRINT '第三条插入出错'
ROLLBACK
END
ELSE
BEGIN
PRINT '第三条插入成功'
COMMIT TRANSACTION
END

BEGIN TRANSACTION
insert into MATERIALPRICE values('zy2016006','wm003',200,10);
IF @@ERROR<>0
BEGIN
PRINT '第四条插入出错'
ROLLBACK
END
ELSE
BEGIN
PRINT '第四条插入成功'
COMMIT TRANSACTION
END

BEGIN TRANSACTION
insert into MATERIALPRICE values('zy2016006','wm004',100,10);
IF @@ERROR<>0
BEGIN
PRINT '第五条插入出错'
ROLLBACK
END
ELSE
BEGIN
PRINT '第五条插入成功'
COMMIT TRANSACTION
END

BEGIN TRANSACTION
DECLARE COST_CUR SCROLL CURSOR
FOR SELECT *
FROM COST;

OPEN COST_CUR;
DECLARE @单据号 VARCHAR(20)
DECLARE @预算单位 VARCHAR(20)
DECLARE @井号 VARCHAR(20)
DECLARE @预算金额 FLOAT
DECLARE @预算人 VARCHAR(20)
DECLARE @预算日期 DATE
DECLARE @开工日期 DATE
DECLARE @完工日期 DATE
DECLARE @施工单位 VARCHAR(30)
DECLARE @施工内容 VARCHAR(50)
DECLARE @材料费 FLOAT
DECLARE @人工费 FLOAT
DECLARE @设备费 FLOAT
DECLARE @其他费用 FLOAT
DECLARE @结算金额 FLOAT
DECLARE @结算人 VARCHAR(20)
DECLARE @结算日期 DATE
DECLARE @入账金额 FLOAT
DECLARE @入账人 VARCHAR(20)
DECLARE @入账日期 DATE

PRINT '单据号 '+'预算单位 '+'井号 '+'预算金额 '+'预算人'+' 预算日期'
+' 开工日期'+' 完工日期'+' 施工单位'+'施工内容'+'材料费'+' 人工费'
+' 设备费'+' 其它费用'+' 结算金额'+' 结算人'+'结算日期'+' 入账金额'
+' 入账人'+' 入账日期'

FETCH FIRST FROM COST_CRU
INTO @单据号,@预算单位,@井号,@预算金额,@预算人,@预算日期,
@开工日期,@完工日期,@施工单位,@施工内容,@材料费,@人工费,
@设备费,@其他费用,@结算金额,@结算人,@结算日期,@入账金额,@入账人,@入账日期

WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT +@单据号+@预算单位 +@井号 +@预算金额 +' '+@预算人+' '+@预算日期 +' '+@开工日期 +' '+@完工日期 +' '+@施工单位 +' '+@施工内容 +@材料费+@人工费 +@设备费
+@其他费用 +@结算金额 +' ' +@结算人 +' '+