MyERP实施人员SQL题目答案

根据要求写出相关的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

第 1 页 共 3 页

NO='CGRKD000005')

Delete from SCM_StockInHead Where NO='CGRKD000005'

5．写出为SCM_StockInHead表加1列,列名为Adddress,为VARCHAR类型,长度100的SQL语句 Alter Table SCM_StockInHead Add Column 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

第 2 页 共 3 页

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 页

本文来源：https://www.bwwdw.com/article/xkxt.html