实施人员SQL题目答案V1.0.doc

根据要求写出相关的SQL语句,语法只要符合SQLServer,DB2,Oracle其中的任意一种即可
如下所示采购入库单
主表SCM_StockInHead

BILLID,系统字段, INTEGER类型;BILLDATE,单据日期,Date类型;No,单据编号,VARCHAR类型,长度50;WAREHOUSE,仓库, INTEGER类型;NOTES,备注,VARCHAR类型,长度200
明细表 SCM_StockInDtl

BILLDTLID,系统字段, INTEGER类型,BILLID, 系统字段, INTEGER类型,ROWNO,序号, 系统字段, INTEGER类型,MATERIAL,物料, INTEGER类型,AMOUNT,入库数量,DECIMAL类型,精度4,PRICE,入库单价,DECIMAL类型,精度2,MONEY,金额,DECIMAL类型,精度2
1．写出SCM_StockInHead, SCM_StockInDtl两个表的建表SQL语句
CREATE TABLE SCM_STOCKINHEAD (BILLID INTEGER,NO VARCHAR(50),Billdate Date, Warehouse INTEGER, NOTES VARCHAR(200))
CREATE TABLE SCM_STOCKINDTL (BILLDTLID INTEGER,BILLID INTEGER,ROWNO INTEGER,Material INTEGER, Amount DECIMAL(18,4),Price DECIMAL(18,2),Money DECIMAL(18,2))

2．如上图所示,写出插入这些数据的SQL语句（主表和明细表各两条记录即可）
Insert into SCM_STOCKINHEAD(BILLID, NO, Billdate, Warehouse, NOTES)Values(116724,'CGRKD000001','2009-02-01',10000,''),(116728,'CGRKD000002','2009-02-01',10000,'')
Insert into SCM_STOCKINDTL(BILLDTLID,BILLID, ROWNO, MATERIAL,AMOUNT,PRICE,MONEY)Values(116725, 116724,1,10102,10.1234,120.23,1217.14), (116726, 116724,2,10103,123.23,23.00,2834.29)

3．写出将单据编号为’CGRKD000004’这张单据的明细表的单价字段的值更新为30.0000的SQL语句
Update SCM_StockInDtl Set Price=30 Where BillID in ( Select BillID from SCM_StockInHead Where NO='CGRKD000004')

4．写出删除单据编号为’ CGRKD000005’这张单据的主表和明细表数据的SQL语句,注意删除顺序
Delete from SCM_StockInDtl Where BillID in (Select BillID from SCM_StockInHead Where NO='CGRKD000005')
Delete from SCM_StockInHead Where NO='CGRKD000005'

5．写出为SCM_StockInHead表加1列,列名为Adddress,为VARCHAR类型,长度100的SQL语句
Alter Table SCM_StockInHead Add Adddress Varchar(100)

6．写出给SCM_StockInHead表BILLID创建唯一性索引的SQL语句
create unique index I_BIllID on SCM_StockInHead(BillID)

7．写出一句SQL语句得到如下结果

Select h.BillID,Billdtlid,Billdate,no,Warehouse,Notes,Rowno,Material,Amount,Price,Money from SCM_StockInDtl d left join SCM_StockInHead h On h.billID=d.billID

8．写出一句SQL语句得到如下结果（Amount,Money做统计,BillDate,No做分组,根据BillDate倒序）

select BillDate,NO,Sum(Amount) as TotalAmount,Sum(Money) as TotalMoney from SCM_StockInDtl d left join SCM_StockInHead h On h.billID=d.billID Group by BillDate,NO order by BillDate Desc

9．写一句SQL语句符合如下要求:查询单据日期在2009年2月份,并且物料不等于10102和10103的的记录,SQL语句中要包含”Not in”
Select * from SCM_StockInDtl d left join SCM_StockInHead h On h.billID=d.billID Where Billdate Between '2009-02-01' and '2009-02-28' and Material not in(10102,10103)

10．写一句SQL语句显示采购入库单中明细数在两条记录以上记录,如下图所示

Select h.BillID,BillDate,NO,Count(*) as Count from SCM_StockInDtl d left join SCM_StockInHead h On h.billID=d.billID Group by h.BillID,BillDate,NO having Count(*)>2

11．写一句SQL语句显示采购入库单金额最大的明细,如下图所示

Select h.BillID,BillDate,NO,Max(Money) as Money from SCM_StockInDtl d left join SCM_StockInHead h On h.billID=d.billID Group by h.BillID,BillDate,NO

12．如下所示销售出库单
主表 SCM_SaleOutHead

明细表 SaleOutDtl

写一句SQL语句将采购入库单与销售出库单做联合查询得到如下记录

Select h.BillID,Billdtlid,Billdate,no,Warehouse,Notes,Rowno,Material,Amount,Price,Money from SCM_StockInDtl d left join SCM_StockInHead h On h.billID=d.billID
union all
Select h.BillID,Billdtlid,Billdate,no,Warehouse,Notes,Rowno,Material,Amount,Price,Money from SCM_SaleoutDtl d left join SCM_SaleoutHead h On h.billID=d.billID
第 3 页 共 3 页