中国石油大学华东数据库原理实验答案sql语句.docx
《中国石油大学华东数据库原理实验答案sql语句.docx》由会员分享，可在线阅读，更多相关《中国石油大学华东数据库原理实验答案sql语句.docx（30页珍藏版）》请在冰豆网上搜索。
中国石油大学华东数据库原理实验答案sql语句
实验三
/*CREATETABLEUNITNO（单位代码VARCHAR（20）,
单位名称VARCHAR（30）
）;
CREATETABLEOILWELL（井号VARCHAR（20）,
井别VARCHAR（20）,
单位代码VARCHAR（20）,
）;
CREATETABLECONSTRUCTIONUNIT（施工单位名称VARCHAR（30）
）;
CREATETABLEGOODSNO（物码VARCHAR（20）,
名称规格VARCHAR（30）,
计量单位VARCHAR（20）
）;
CREATETABLECOST（单据号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
）;
CREATETABLEMATERIALPRICE（单据号VARCHAR（20）,
物码VARCHAR（20）,
消耗数量BIGINT,
单价FLOAT
）;
*/
/*INSERTINTOUNITNO（单位代码,单位名称）VALUES（'1122','采油厂'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112201','采油一矿'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112202','采油二矿'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112201001','采油一矿一队'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112201002','采油一矿二队'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112201003','采油一矿三队'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112202001','采油二矿一队'）;
INSERTINTOUNITNO（单位代码,单位名称）VALUES（'112202002','采油二矿二队'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'y001','油井','112201001'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'y002','油井','112201001'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'y003','油井','112201002'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'s001','水井','112201002'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'y004','油井','112201003'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'s002','水井','112201001'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'s003','水井','112201001'）;
INSERTINTOOILWELL（井号,井别,单位代码）VALUES（'y005','油井','112201002'）;
INSERTINTOCONSTRUCTIONUNIT（施工单位名称）VALUES（'作业公司作业一队'）;
INSERTINTOCONSTRUCTIONUNIT（施工单位名称）VALUES（'作业公司作业二队'）;
INSERTINTOCONSTRUCTIONUNIT（施工单位名称）VALUES（'作业公司作业三队'）;
INSERTINTOGOODSNO（物码,名称规格,计量单位）VALUES（'wm001','材料一','吨'）;
INSERTINTOGOODSNO（物码,名称规格,计量单位）VALUES（'wm002','材料二','米'）;
INSERTINTOGOODSNO（物码,名称规格,计量单位）VALUES（'wm003','材料三','桶'）;
INSERTINTOGOODSNO（物码,名称规格,计量单位）VALUES（'wm004','材料四','袋'）;
INSERTINTOCOST（单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费,
人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期）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'）;
INSERTINTOCOST（单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费,
人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期）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'）;
INSERTINTOCOST（单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费,
人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期）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'）;
INSERTINTOCOST（单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费,
人工费,设备费,其他费用,结算金额,结算人,结算日期,入账金额,入账人,入账日期）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'）;
INSERTINTOCOST（单据号,预算单位,井号,预算金额,预算人,预算日期,开工日期,完工日期,施工单位,施工内容,材料费,
人工费,设备费,其他费用,结算金额,结算人,结算日期）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'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016001','wm001','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016001','wm002','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016001','wm003','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016001','wm004','100','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016002','wm001','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016002','wm002','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016002','wm003','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016003','wm001','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016003','wm002','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016003','wm003','250','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016004','wm001','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016004','wm002','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016004','wm004','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016005','wm001','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016005','wm002','200','10.00'）;
INSERTINTOMATERIALPRICE（单据号,物码,消耗数量,单价）VALUES（'ZY2016005','wm004','300','10.00'）;
UPDATECOST
SET人工费=人工费+200.00,结算金额=结算金额+200.00
WHERE单据号='zy2016005';
DELETE
FROMCOST
WHERE入账金额ISNULLOR入账人ISNULLOR入账日期ISNULL;
BEGINTRAN
UPDATECOST
SET人工费=人工费+200.00,结算金额=结算金额+200.00
WHERE单据号='zy2016005';
DELETE
FROMCOST
WHERE入账金额ISNULLOR入账人ISNULLOR入账日期ISNULL;
ROLLBACKTRAN
实验四
CREATEINDEXyDATEONCOST（预算日期）;
CREATEINDEXjDATEONCOST（结算日期）;
CREATEINDEXrDATEONCOST（入账日期）;
SELECTDISTINCT*FROMCOST,UNITNO
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28'AND预算金额ISNOTNULLAND预算日期ISNOTNULLAND预算人ISNOTNULL;
SELECTDISTINCT*FROMCOST,UNITNO
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28'AND结算金额ISNOTNULLAND结算人ISNOTNULLAND结算日期ISNOTNULL;
SELECTDISTINCTMATERIALPRICE.单据号,物码,消耗数量,单价
FROMCOST,MATERIALPRICE,UNITNO
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28'ANDMATERIALPRICE.单据号=COST.单据号;
SELECTDISTINCT*FROMCOST,UNITNO
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28'AND入账金额ISNOTNULLAND入账人ISNOTNULLAND入账日期ISNOTNULL;
SELECT预算总额=SUM（DISTINCT预算金额）
FROMUNITNO,COST
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28';
SELECT结算总额=SUM（DISTINCT结算金额）
FROMUNITNO,COST
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28';
SELECT入账总额=SUM（DISTINCT入账金额）
FROMUNITNO,COST
WHERE单位名称='采油一矿二队'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28';
SELECT一矿入账总额=SUM（DISTINCT入账金额）
FROMUNITNO,COST
WHERE单位名称LIKE'采油一矿%'AND预算单位=单位代码AND入账日期>='2016-5-1'AND入账日期<='2016-5-28';
SELECTDISTINCT入账人
FROMCOST
WHERE入账人ISNOTNULL;
SELECTDISTINCT单据号
FROMCOST
WHERE结算日期>='2016-5-1'AND结算日期<='2016-5-28'AND入账金额ISNULLAND结算金额ISNOTNULL;
SELECTDISTINCT单据号,入账金额
FROMCOST,UNITNO
WHERE单位名称='采油一矿二队'AND预算单位=单位代码
ORDERBY入账金额DESC;
SELECT单位名称,结算金额总和=SUM（DISTINCT结算金额）,项目数=COUNT（DISTINCT结算金额）
FROMCOST,UNITNO
WHERE预算单位=单位代码
GROUPBY单位名称;
SELECTdistinct单据号,物码,消耗数量,单价
FROMMATERIALPRICE
WHERE物码IN（
SELECT物码
FROMGOODSNO
WHERE名称规格='材料三'）
AND单价*消耗数量>=2000;
SELECTDISTINCT单据号
FROMCOST
WHERE施工单位='作业公司作业二队';
SELECT单据号,施工单位
FROMCOST
WHERE施工单位='作业公司作业二队'
UNION
SELECT单据号,施工单位
FROMCOST
WHERE施工单位='作业公司作业一队';
SELECTDISTINCT施工单位
FROMCOST,OILWELL,UNITNO
WHERE预算单位=UNITNO.单位代码ANDUNITNO.单位代码=OILWELL.单位代码AND单位名称LIKE'采油一矿%';
CREATETABLE数据表（施工单位VARCHAR（30）,
年月VARCHAR（20）,
结算金额FLOAT
）;
INSERTINTO数据表（施工单位,年月,结算金额）
（SELECT施工单位,YEAR（结算日期）*100+MONTH（结算日期）,SUM（结算金额）
FROMCOST
GROUPBY施工单位,YEAR（结算日期）*100+MONTH（结算日期））;
UPDATECOST
SET结算人='李兵'
WHERE预算单位IN（
SELECT单位代码
FROMUNITNO
WHERE单位代码=预算单位AND单位名称Like'采油一矿%'
）;
DELETE
FROMCOST
WHERE预算单位IN
（SELECT单位代码
FROMUNITNO
WHERE单位代码=预算单位AND单位名称Like'采油一矿%'）;
BEGINTRAN
UPDATECOST
SET结算人='李兵'
WHERE预算单位IN（
SELECT单位代码
FROMUNITNO
WHERE单位代码=预算单位AND单位名称Like'采油一矿%'
）;
DELETE
FROMCOST
WHERE预算单位IN（
SELECT单位代码
FROMUNITNO
WHERE单位代码=预算单位AND单位名称Like'采油一矿%'）;
ROLLBACKTRAN
DROPINDEXyDATEONCOST;
DROPINDEXjDATEONCOST;
DROPINDEXrDATEONCOST;
实验五
ALTERTABLE数据表ADD备注CHAR;
SELECT*
FROM数据表;
ALTERTABLE数据表
ALTERCOLUMN施工单位VARCHAR（30）NOTNULL;
ALTERTABLE数据表ADDPRIMARYKEY（施工单位）;
DELETE
FROM数据表;
DROPTABLE数据表;
ALTERTABLEUNITNO
ALTERCOLUMN单位代码VARCHAR（20）NOTNULL;
ALTERTABLEUNITNO
ADDCONSTRAINTUNI_NUMPRIMARYKEY（单位代码）;
ALTERTABLEOILWELL
ALTERCOLUMN井号VARCHAR（20）NOTNULL;
ALTERTABLEOILWELL
ADDCONSTRAINTWELL_NUMPRIMARYKEY（井号）;
ALTERTABLEGOODSNO
ALTERCOLUMN物码VARCHAR（20）NOTNULL;
ALTERTABLEGOODSNO
ADDCONSTRAINTGOOD_NUMPRIMARYKEY（物码）;
ALTERTABLECOST
ALTERCOLUMN单据号VARCHAR（20）NOTNULL;
ALTERTABLECOST
ADDCONSTRAINTCOST_NUMPRIMARYKEY（单据号）;
ALTERTABLEMATERIALPRICE
ALTERCOLUMN单据号VARCHAR（20）NOTNULL;
ALTERTABLEMATERIALPRICE
ALTERCOLUMN物码VARCHAR（20）NOTNULL;
ALTERTABLEMATERIALPRICEADDPRIMARYKEY（单据号,物码）;
ALTERTABLECONSTRUCTIONUNIT
ALTERCOLUMN施工单位名称VARCHAR（30）NOTNULL;
ALTERTABLECONSTRUCTIONUNIT
ADDCONSTRAINTUNI_NAMEPRIMARYKEY（施工单位名称）;
INSERTINTOMATERIALPRICEVALUES（'zy2016001','wm004',100,10）;
INSERTINTOMATERIALPRICEVALUES（'zy2016002',NULL,200,10）;
ALTERTABLEOILWELL
ALTERCOLUMN单位代码VARCHAR（20）NOTNULL;
ALTERTABLEOILWELL
ADDCONSTRAINTUNI_NUM2FOREIGNKEY（单位代码）REFERENCESUNITNO（单位代码）;
ALTERTABLECOST
ALTERCOLUMN井号VARCHAR（20）NOTNULL;
ALTERTABLECOST
ADDCONSTRAINTWELL_NUM2FOREIGNKEY（井号）REFERENCESOILWELL（井号）;
ALTERTABLEMATERIALPRICE
ALTERCOLUMN物码VARCHAR（20）NOTNULL;
ALTERTABLEMATERIALPRICE
ADDCONSTRAINTGOOD_NUM2FORE
升级会员 