数据库系统原理与设计实验答案

SET DATEFORMAT YMD

--选择数据库

USE OrderDB

--实验一：简单查询

--1.1 查询所有业务部门的员工姓名、职务、薪水。

SELECT employeeName,headShip,salary

FROM Employee

--1.2 查询名字中含有“有限”的客户姓名和所在地。

SELECT customerName,address

FROM Customer

--1.3 查询出姓“王”并且姓名的最后一个字为“成”的员工。

SELECT *

FROM Employee

WHERE employeeName LIKE '王%成'

--1.4 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。

SELECT employeeName AS 姓名, department AS 所属部门, headShip AS 职称, address AS 住址, CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' END AS 性别

FROM Employee

--1.5 在表sales 中挑出销售金额大于等于5000元的订单。

SELECT *

FROM OrderMaster

WHERE orderSum>5000

--1.6 选取订单金额最高的前10%的订单数据。

SELECT TOP(10) PERCENT *

FROM OrderMaster

ORDER BY orderSum

--或

SELECT TOP 10 PERCENT orderNo,SUM(quantity*price) AS totalPrice

FROM OrderDetail

GROUP BY orderNO

ORDER BY SUM(quantity*price) DESC

--1.7 查询出职务为“职员”或职务为“科长”的女员工的信息。

SELECT *

FROM Employee

WHERE sex='女' AND (headShip='职员' OR headShip='科长')

--1.8 查找订单金额高于4000的所有客户编号。

SELECT customerNo

FROM OrderMaster

WHERE orderSum>4000

--1.9 选取编号介于C20050001~C20050004的客户编号、客户名称、客户地址。

SELECT customerNo,customerName,address

FROM Customer

WHERE customerNo BETWEEN 'C20050001' AND 'C20050004'

--1.10 找出同一天进入公司服务的员工。

SELECT *

FROM Employee

ORDER BY hireDate

--1.11 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。

SELECT *

FROM OrderMaster

WHERE orderSum >(

SELECT MAX(orderSum)

FROM OrderMaster

WHERE salerNo='E2005002' AND orderDate='2008-1-9'

)

--或

SELECT *

FROM OrderMaster

WHERE orderSum >ALL(

SELECT orderSum

FROM OrderMaster

WHERE salerNo='E2005002' AND orderDate='2008-1-9'

)

--1.12 查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。

SELECT customerNo,orderNo,orderSum

FROM OrderMaster

WHERE orderNo IN(

SELECT o1.orderNo

FROM OrderDetail o1,OrderDetail o2

WHERE o1.orderNo=o2.orderNo

AND o1.productNo IN(

SELECT productNo

FROM Product

WHERE productName='52倍速光驱'

) AND o2.productNo IN(

SELECT productNo

FROM Product

WHERE productName='17寸显示器'

)

--1.13 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。

SELECT employeeName,sex,department,headShip

FROM Employee

WHERE department IN(

SELECT department

FROM Employee

)

WHERE employeeName='陈诗杰'

--1.14 查询每种商品的商品编号、商品名称、订货数量和订货单价。

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail,Product

WHERE OrderDetail.productNo=Product.productNo

--1.15 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail,Product

WHERE OrderDetail.productNo=Product.productNo

AND price>400

--1.16 分别用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。

--左外连接

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail LEFT JOIN Product ON OrderDetail.productNo=Product.productNo

WHERE price>400

--另一种理解

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail LEFT JOIN Product ON OrderDetail.productNo=Product.productNo AND price>400

--右外连接

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail RIGHT JOIN Product ON OrderDetail.productNo=Product.productNo

WHERE price>400

--另一种理解

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail RIGHT JOIN Product ON OrderDetail.productNo=Product.productNo AND price>400

--完整外部连接

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail FULL JOIN Product ON OrderDetail.productNo=Product.productNo

WHERE price>400

--另一种理解

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail FULL JOIN Product ON OrderDetail.productNo=Product.productNo AND price>400

--1.17 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用yyyy-mm-dd 格式显示。

SELECT employeeNo,employeeName,sex=CASE sex WHEN 'M' THEN ' 男' WHEN 'F' THEN ' 女' END,productName,quantity,price,quantity*price AS money,orderDate=ISNULL(CONVERT(char(10),orderDate,120),'日期不详')

FROM Employee,OrderMaster,OrderDetail,Product

WHERE Employee.employeeNo=OrderMaster.salerNo

AND OrderMaster.orderNo=OrderDetail.orderNo

AND OrderDetail.productNo=Product.productNo

--1.18 查找在2008年3月中有销售记录的客户编号、名称和订单总额。

SELECT Customer.customerNo,customerName,totalPrice=SUM(orderSum)

FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

AND YEAR(orderDate)=2008 AND MONTH(orderDate)=3

GROUP BY Customer.customerNo,customerName

--1.19 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为yyyy-mm-dd ，按客户编号排序，同一客户再按订单金额降序排序输出。

SELECT

Customer.customerNo,customerName,orderDate=CONVERT(char(10),orderDate,120),orderSum FROM Customer LEFT JOIN OrderMaster ON Customer.customerNo=OrderMaster.customerNo ORDER BY Customer.customerNo,orderSum DESC

--1.20 查找16M DRAM 的销售情况，要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额，其中性别用“男”、“女”表示。

SELECT employeeName, sex=CASE sex WHEN 'M' THEN ' 男' WHEN 'F' THEN ' 女' END, orderDate=CONVERT(char(10),orderDate,120),quantity,money=quantity*price

FROM Employee,OrderMaster,OrderDetail,Product

WHERE Employee.employeeNo=OrderMaster.salerNo

AND OrderMaster.orderNo=OrderDetail.orderNo

AND OrderDetail.productNo=Product.productNo

AND productName='16M DRAM'

--1.21 查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。

SELECT Employee.employeeNo,employeeName,sex=CASE sex WHEN 'M' THEN ' 男' WHEN 'F' THEN ' 女' END,productName,quantity,price,totalPrice=quantity*price,orderDate=CONVERT(char(10),orderDate,120)

FROM Employee,OrderMaster,OrderDetail,Product

WHERE Employee.employeeNo=OrderMaster.salerNo

AND OrderMaster.orderNo=OrderDetail.orderNo

AND OrderDetail.productNo=Product.productNo

--1.22 查询客户姓名为“客户丙”所购货物的客户名称、订单金额、订货日期和电话号码。 SELECT customerName,orderSum,orderDate,telephone

FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

AND customerName='客户丙'

--1.23 找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。

SELECT orderNo,orderSum

FROM Employee,OrderMaster

WHERE Employee.employeeNo=OrderMaster.salerNo

AND sex='M' AND orderSum>=2000

--1.24 查询来自上海市的客户的姓名、电话、订单号及订单金额。

SELECT customerName,telephone,orderNo,orderSum

FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

AND address LIKE '上海市%'

--实验二：复杂查询

--2.1 查找有销售记录的客户编号、名称和订单总额。

SELECT Customer.customerNo,customerName,SUM(orderSum) AS totalPrice

FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

GROUP BY Customer.customerNo,customerName

--2.2 在订单明细表中查询订单金额最高的订单。

FROM OrderDetail

WHERE orderNo IN(

SELECT orderNo

FROM OrderDetail

GROUP BY orderNo

HAVING SUM(quantity*price)>=ALL(

SELECT SUM(quantity*price)

FROM OrderDetail

GROUP BY orderNo

)

--2.3 查询没有订购商品的客户编号和客户名称。

SELECT customerNo,customerName

FROM Customer

WHERE customerNo NOT IN(

SELECT customerNo

FROM OrderMaster

)

--2.4 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。

SELECT productNo,orderNo,quantity,quantity*price AS totalPrice

FROM OrderDetail

WHERE productNo IN(

SELECT productNo

FROM OrderDetail

GROUP BY productNo

HAVING COUNT(*)>=3

)

ORDER BY quantity DESC

--2.5 使用子查询查找“16M DRAM ”的销售情况，要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额，其中性别用“男”、“女”表示。

SELECT employeeName,sex=CASE sex WHEN 'M' THEN ' 男' WHEN 'F' THEN ' 女' END,orderDate,quantity,totalPrice=quantity*price

FROM Employee,OrderMaster,OrderDetail

WHERE Employee.employeeNo=OrderMaster.salerNo

AND OrderMaster.orderNo=OrderDetail.orderNo

AND productNo IN(

SELECT productNo

FROM Product

WHERE productName='16M DRAM'

--2.6 查询sales 表中订单金额最高的订单号及订单金额。

SELECT orderNo,orderSum

FROM OrderMaster

WHERE orderSum>=ALL(

SELECT orderSum

FROM OrderMaster

)

--2.7 计算出一共销售了几种商品。

SELECT COUNT(DISTINCT productNo) AS productNum

FROM OrderDetail

--2.8 显示OrderDetail 表中每种商品的订购金额总和，并且依据销售金额由大到小排序输出。 SELECT productNo,SUM(quantity*price) AS totalPrice

FROM OrderDetail

GROUP BY productNo

ORDER BY SUM(quantity*price) DESC

--2.9 查找销售总额少于1000元的销售员编号、姓名和销售额。

SELECT employeeNo,employeeName,SUM(orderSum) AS totalPrice

FROM Employee,OrderMaster

WHERE Employee.employeeNo=OrderMaster.salerNo

GROUP BY employeeNo,employeeName

HAVING SUM(orderSum)

--2.10 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。

SELECT Employee.employeeNo,employeeName,SUM(orderSum) AS totalPrice

FROM Employee,OrderMaster

GROUP BY Employee.employeeNo,employeeName

HAVING SUM(orderSum)

ORDER BY SUM(orderSum) DESC

--2.11 在Employee 表中查询薪水超过员工平均薪水的员工信息。

SELECT *

FROM Employee

WHERE salary>=(

SELECT AVG(salary)

FROM Employee

)

--2.12 计算每一种商品的销售数量、平均销售单价和总销售金额。

SELECT productNo,SUM(quantity) AS totalNum,SUM(price*quantity)/SUM(quantity) AS

avgPrice,SUM(price*quantity) AS totalPrice

FROM OrderDetail

GROUP BY productNo

--2.13 查找至少有3次销售的业务员名单和销售日期。

SELECT salerNo,orderDate

FROM OrderMaster

WHERE salerNo IN(

SELECT salerNo

FROM OrderMaster

GROUP BY salerNo

HAVING COUNT(*)>=3

)

--2.14 用存在量词查找没有订货记录的客户名称。

SELECT customerNo

FROM Customer

WHERE NOT EXISTS(

SELECT *

FROM OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

)

--2.15 查询订单中所订购的商品数量没有超过10个的客户编号和客户名称。

SELECT customerNo,customerName

FROM Customer

WHERE customerNo IN(

SELECT customerNo

FROM OrderMaster

WHERE orderNo IN(

SELECT orderNo

FROM OrderDetail

WHERE quantity

)

--2.16 在销售明细表中按商品编号进行汇总，统计每种商品的销售数量和金额。 SELECT productNo,SUM(quantity) AS totalNum,SUM(quantity*price) AS totalPrice

FROM OrderDetail

GROUP BY productNo

--2.17 按客户编号统计每个客户2008年2月的订单总金额。

SELECT customerNo,SUM(orderSum) AS totalPrice

FROM OrderMaster

WHERE YEAR(orderDate)=2008 AND MONTH(orderDate)=2

GROUP BY customerNo

--2.18 查找订单金额高于8000的所有客户编号。

SELECT customerNo

FROM OrderMaster

WHERE orderSum>=8000

--2.19 显示每种商品的销售金额总和，并以销售金额由大到小输出。

SELECT productNo,SUM(price*quantity) AS totalPrice

FROM OrderDetail

GROUP BY productNo

ORDER BY SUM(price*quantity)

--2.20 查找销售金额最大的客户名称和总货款。

SELECT customerName,SUM(orderSum) AS totalPrice

FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

GROUP BY customerName

HAVING SUM(orderSum)>=ALL(

SELECT SUM(orderSum)

FROM OrderMaster

GROUP BY customerNo

)

--2.21 查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。 SELECT x.customerNo,customerName,p.productNo,productName,quantity,quantity*price AS totalPrice

FROM Customer x,OrderMaster y,OrderDetail z,Product p

WHERE x.customerNo=y.customerNo

AND y.orderNo=z.orderNo

AND z.productNo=p.productNo

AND EXISTS(

SELECT *

FROM OrderMaster a,OrderDetail b

WHERE a.orderNo=b.orderNo

AND a.orderNo IN(

SELECT orderNo

FROM OrderMaster c

WHERE c.customerNo=x.customerNo

)

GROUP BY a.customerNo

HAVING COUNT(DISTINCT productNo)>=3

)

ORDER BY x.customerNo

--2.22 找出目前业绩超过232000元的员工编号和姓名。

SELECT Customer.customerNo,customerName

FROM Customer,OrderMaster

WHERE Customer.customerNo=OrderMaster.customerNo

GROUP BY Customer.customerNo,customerName

HAVING SUM(orderSum)>=232000

--2.23 找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序输出。

SELECT customerNo,SUM(orderSum) AS totalPrice

FROM OrderMaster

GROUP BY customerNo

HAVING SUM(orderSum)>=4000

ORDER BY SUM(orderSum)

--2.24 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列。 SELECT customerNo,SUM(orderSum) AS totalPrice

FROM OrderMaster

GROUP BY customerNo

ORDER BY SUM(orderSum) DESC

--2.25 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELECT customerNo,productNo,SUM(quantity) AS totalNum,SUM(quantity*price)/SUM(quantity) AS avgPrice

FROM OrderMaster,OrderDetail

WHERE OrderMaster.orderNo=OrderDetail.orderNo

GROUP BY customerNo,productNo

ORDER BY customerNo,productNo

--2.26 查询业绩最好的业务员号、业务员名及其总销售金额。

SELECT Employee.employeeNo,employeeName,SUM(orderSum) AS totalPrice

FROM Employee,OrderMaster

WHERE Employee.employeeNo=OrderMaster.salerNo

GROUP BY Employee.employeeNo,employeeName

HAVING SUM(orderSum)>=ALL(

SELECT SUM(orderSum)

FROM OrderMaster

GROUP BY salerNo

)

--2.27 查询订购的商品至少包含了订单“[1**********]1”中所订购商品的订单。 SELECT orderNo

FROM OrderMaster

WHERE NOT EXISTS(

SELECT *

FROM OrderDetail a

WHERE orderNo='[1**********]1'

AND NOT EXISTS(

SELECT *

FROM OrderDetail b

WHERE b.orderNo=OrderMaster.orderNo

AND b.productNo=a.productNo

)

--2.28 求每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。

SELECT Product.productNo,productName,SUM(quantity) AS totalNum,SUM(quantity*price) AS totalPrice

FROM Product,OrderDetail

WHERE Product.productNo=OrderDetail.productNo

GROUP BY Product.productNo,productName

ORDER BY Product.productNo

--2.29 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。 SELECT customerNo,customerName,address

FROM Customer

WHERE customerNo IN(

SELECT customerNo

FROM OrderMaster

GROUP BY customerNo

HAVING SUM(orderSum)>=ALL(

SELECT SUM(orderSum)

FROM OrderMaster

GROUP BY customerNo

)

--2.30 查询销售金额最高的销售员编号、订单编号、订单日期和订单金额。

SELECT salerNo,orderNo,orderDate,orderSum

FROM OrderMaster

WHERE orderSum=(

SELECT MAX(orderSum)

FROM OrderMaster

)

--实验三：数据定义操作

--3.1 创建OrderDB 数据库和表

--略, 参见OrderDB.sql

--3.2 在创建基本表时设置合理的主、外键约束。

--略, 参见OrderDB.sql

--3.3 表结构的修改：

--3.3.1 修改客户表结构，要求客户名称和客户电话属性为NOT NULL。

ALTER TABLE Customer

ALTER COLUMN customerName varchar(40) NOT NULL

ALTER TABLE Customer

ALTER COLUMN telephone varchar(20) NOT NULL

--3.3.2 修改员工表结构，要求员工姓名和电话属性为NOT NULL。

ALTER TABLE Employee

ALTER COLUMN employeeName varchar(10) NOT NULL

ALTER TABLE Employee

ALTER COLUMN telephone varchar(20) NOT NULL

--3.3.3 修改订单主表结构，要求发票号码属性为NOT NULL。

ALTER TABLE OrderMaster

ALTER COLUMN invoiceNo varchar(10) NOT NULL

--3.4 创建基本表时，同时完成以下索引。

--3.4.1 在员工表中按所得薪水建立一个非聚集索引salaryIdx 。

CREATE INDEX salaryIdx

ON Employee(salary)

--3.4.2 在订单主表中，首先按订单金额的升序，然后按业务员编号的降序建立一个非聚集索引salenosumIdx 。

CREATE INDEX salenosumIdx

ON OrderMaster(orderSum, salerNo DESC)

--3.5 创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、客户地址、建立日期。

CREATE VIEW view_Customer_shanghai

SELECT customerNo,customerName,address--,createDate

FROM Customer

WHERE address LIKE '上海%'

WITH CHECK OPTION

--实验四：数据更新操作

--4.1 对表的基本操作有以下内容。

--4.1.1 分别给这五张表添加元组信息，要求员工表、客户表、商品表各插入5个元组，订单主表8个元组，订单明细表20个元组。

--略, 参见OrderDB.sql

--4.1.2 将作废订单（发票号码5197791779）由订单明细表中删除。

DELETE OrderDetail

WHERE orderNo=(

SELECT orderNo

FROM OrderMaster

WHERE invoiceNo='5197791779'

)

--4.1.3 将上海的客户住址全都改为深圳。

UPDATE Customer

SET address='深圳'

WHERE address='上海'

--4.1.4 将工作满2周年的员工薪水上调5%，工作满5周年的员工薪水上调8%。UPDATE Employee

SET salary=salary*1.05

WHERE DATEDIFF(YEAR,hireDate,GETDATE())>=2

--WHERE YEAR(GETDATE())-YEAR(hireDate)>2 OR YEAR(GETDATE())=2 MONTH(hireDate)

UPDATE Employee

SET salary=salary*1.08

WHERE DATEDIFF(YEAR,hireDate,GETDATE())>=5

--4.1.5 将客户c20090001在2009年1月购买的所有商品单价打9折。

UPDATE OrderDetail

SET price=price*0.9

WHERE orderNo=(

SELECT orderNo

FROM OrderMaster

WHERE customerNo='c20090001'

AND YEAR(orderDate)=2009

AND MONTH(orderDate)=1

)

--4.1.6 根据订单明细表，修改订单主表的订单金额信息。

UPDATE OrderMaster AND

SET orderSum=(

SELECT SUM(price*quantity)

FROM OrderDetail

WHERE orderNo=OrderMaster.orderNo

)

--4.2 对视图的基本操作有以下内容。

--4.2.1 对视图添加一条记录数据（注意：分别查看Customer 表和该视图的结果）。 INSERT view_Customer_shanghai VALUES('C20120001','红三环卷烟厂', '滁州市')

INSERT view_Customer_shanghai VALUES('C20120002','上海卷烟厂',' 上海市')

--4.2.2 删除视图中所有姓“王”的客户数据。

DELETE view_Customer_shanghai

WHERE customerName LIKE '王%'

--4.2.3 通过视图修改表内某一客户的姓名。

UPDATE view_Customer_shanghai

SET customerName='上海永久自行车股份有限公司'

WHERE customerNo='C20120002'

--4.2.4 对员工表和订单主表创建一个视图，该视图包含相同业务员的编号、姓名、订单号、订单金额。

CREATE VIEW view_Employee_OrderMaster

SELECT TOP 100 PERCENT employeeNo,employeeName,orderNo,orderSum

FROM Employee,OrderMaster

WHERE Employee.employeeNo=OrderMaster.salerNo

ORDER BY employeeNo

--4.2.5 将上述视图中订单号为[1**********]8的记录的订单金额改为60000。

UPDATE view_Employee_OrderMaster

SET orderSum=60000

WHERE orderNo='[1**********]8'

--4.2.6 给上述视图添加一条记录数据。

INSERT view_Employee_OrderMaster VALUES('E2012001', '任正非', '[1**********]1', 60000)

--4.2.7 删除上述视图。

DROP VIEW view_Employee_OrderMaster

--实验六：安全性定义与检查

--请完成下面的实验内容。

--(1) 分别创建登录账号和用户账号john,mary （注意服务器角色的设置）。

sp_addlogin 'john'

sp_grantdbaccess 'john','john'

--sp_revokedbaccess 'john'

--sp_droplogin 'john'

sp_addlogin 'mary'

sp_grantdbaccess 'mary','mary'

--(2) 将员工表的所有权限给全部用户。

GRANT ALL ON Employee TO PUBLIC

--(3) 创建角色r1,r2，将订单明细表所有列的SELECT 权限、UNIT_PRICE列的UPDATE 权限给r1。

sp_addrole 'r1'

sp_addrole 'r2'

GRANT SELECT ON OrderDetail TO r1

GRANT UPDATE ON OrderDetail(price) TO r1

--(4) 收回全部用户对员工表的所有权限。

REVOKE ALL ON Employee TO PUBLIC

--(5) 将john,mary 两个用户赋予r1角色。

sp_addrolemember 'r1','john'

sp_addrolemember 'r1','mary'

--(6) 收回john 对订单明细表所有列的SELECT 权限。

REVOKE SELECT ON OrderDetail TO john

--(7) 在当前数据库中删除角色r2。

sp_droprole r2

--实验七：完整性定义与检查

--重新创建OrderDB 数据库中5张基本表，要求完成以下完整性约束。

--(1) 分别为每张表合理建立主、外键约束。

--注：OrderDB 在创建时已经设置了合理的主、外键约束，以下示例语句仅仅给出了在基本表中没有设置主、外键的情况下增加主、外键约束的方法。

ALTER TABLE Employee ADD CONSTRAINT PK_Employee PRIMARY KEY(employeeNo)

ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN KEY(department) REFERENCES Department

--(2) 员工表：员工姓名、电话属性为NOT NULL ；员工编号构成：年流水号，共8位，第1位为E ，如E2008001，年份取雇佣日期的年份；性别：f 表示女，m 表示男。

ALTER TABLE Employee ALTER COLUMN telephone varchar(20) NOT NULL

ALTER TABLE Employee ADD CONSTRAINT CK_Employee_employeeNo CHECK(employeeNo LIKE '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

ALTER TABLE Employee ADD CONSTRAINT CK_Employee_sex CHECK(sex='f' OR sex='m')

--(3) 商品表：商品编号、商品名称、商品类别、建立日期设为NOT NULL ；商品编号构成：年流水号，共9位，第1位为P ，如P20080001，年份取建立日期的年份。

ALTER TABLE Product ALTER COLUMN productClass varchar(20) NOT NULL

ALTER TABLE Product ADD CONSTRAINT CK_Product_productNo CHECK(productNo LIKE '[P][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

--(4) 客户表：客户编号、客户名称、客户电话设为NOT NULL ；客户编号构成：年流水号，共9位，第1位为C ，如C20080001，年份取建立日期的年份。

ALTER TABLE Customer ALTER COLUMN telephone varchar(20) NOT NULL

ALTER TABLE Customer ADD CONSTRAINT CK_Customer_customerNo CHECK(customerNo LIKE '[C][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

--(5) 订单主表：订单编号的构成：年月日流水号，共12位，如[1**********]1；订单编号、客户编号、员工编号、发票号码设为NOT NULL；业务员必须是员工；

--订货日期和出货日期的默认值为系统当前日期；订单金额默认值为0；发票号码建立UNIQUE 约束。

ALTER TABLE OrderMaster ADD CONSTRAINT CK_OrderMatser_orderNo CHECK(orderNo LIKE '[1-2][0-9][0-9][0-9][0-1][0-9][0-1][0-9][0-9][0-9][0-9][0-9]')

ALTER TABLE OrderMaster ALTER COLUMN invoiceNo char(10) NOT NULL

ALTER TABLE OrderMaster ADD CONSTRAINT FK_OrderMaster_Employee FOREIGN KEY(salerNo) REFERENCES Employee(employeeNo)

ALTER TABLE OrderMaster ADD CONSTRAINT DF_OrderMaster_orderDate DEFAULT(GETDATE()) FOR orderDate

ALTER TABLE OrderMaster ADD CONSTRAINT DF_OrderMaster_orderSum DEFAULT(0) FOR orderSum

ALTER TABLE OrderMaster ADD CONSTRAINT UQ_OrderMaster_invoiceNo UNIQUE(invoiceNo)

--实验八：游标与存储过程

--(1) 利用游标查找所有女业务员的基本情况。

DECLARE cur_Employee_female SCROLL CURSOR

FOR

SELECT * FROM Employee WHERE sex='F'

OPEN cur_Employee_female

FETCH NEXT FROM cur_Employee_female

WHILE (@@FETCH_STATUS-1)

BEGIN

FETCH NEXT FROM cur_Employee_female

END

CLOSE cur_Employee_female

DEALLOCATE cur_Employee_female

--(2) 创建一个游标，逐行显示表Customer 的记录，要求按

--' 客户编号'+'------'+'客户名称'+'----------------'+'客户地址'+'--------------------------'+

--' 客户电话'+'------'+'客户邮编'+'------'格式输出，并且用WHILE 结构来测试游标的函数@@Fetch_Status的返回值。

DECLARE @customerNo char(9),@customerName varchar(40),@address varchar(40), @telephone varchar(20),@zip char(6)

DECLARE cur_Customer CURSOR

FOR

SELECT customerNo,customerName,address,telephone,zip

FROM Customer

PRINT '客户编号'+'------'+'客户名称'+'----------------'+'客户地址'+'--------------------------'+ ' 客户电话'+'------'+'客户邮编'+'------'

OPEN cur_Customer

FETCH cur_Customer INTO @customerNo,@customerName,@address,@telephone,@zip WHILE(@@FETCH_STATUS=0)

BEGIN

PRINT CONVERT(char(14),@customerNo) + CONVERT(char(24),@customerName) + CONVERT(char(34),@address)

+ CONVERT(char(14),@telephone) + CONVERT(char(6),@zip)

FETCH cur_Customer INTO @customerNo,@customerName,@address,@telephone,@zip END

CLOSE cur_Customer

DEALLOCATE cur_Customer

--(3) 利用游标修改OrderMaster 表中orderSum 的值。

DECLARE @orderNo char(12),@orderSum numeric(9,2)

DECLARE cur_OrderMaster CURSOR

FOR

SELECT orderNo,orderSum

FROM OrderMaster

OPEN cur_OrderMaster

FETCH cur_OrderMaster INTO @orderNo,@orderSum

WHILE (@@FETCH_STATUS=0)

BEGIN

--PRINT CONVERT(char(15),@orderNo) + CONVERT(char(15),@orderSum)

UPDATE OrderMaster SET orderSum=@orderSum+20000 WHERE CURRENT OF cur_OrderMaster

FETCH cur_OrderMaster INTO @orderNo,@orderSum

END

CLOSE cur_OrderMaster

DEALLOCATE cur_OrderMaster

--(4) 利用游标显示出OrderMaster 表中每一个订单所对应的明细数据信息。

DECLARE @orderNO char(12),@customerNo char(9),@salerNo char(8),@orderDate datetime DECLARE cur_OrderMaster CURSOR

FOR

SELECT orderNo,customerNo,salerNo,orderDate

FROM OrderMaster

OPEN cur_OrderMaster

FETCH cur_OrderMaster INTO @orderNo,@customerNo,@salerNO,@orderDate

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT CONVERT(char(15),'订单号') + CONVERT(char(15),'客户号') + CONVERT(char(15),'销售员号') + CONVERT(char(15),'销售日期')

PRINT CONVERT(char(15),@orderNo) + CONVERT(char(15),@customerNo) + CONVERT(char(15),@salerNO) + CONVERT(char(15),@orderDate,111)

PRINT CONVERT(char(15),'产品号') + CONVERT(char(15),'数量') + CONVERT(char(15),'单价') --DECLARE @orderNO char(12)='[1**********]1'

DECLARE @productNo char(9),@quantity int,@price numeric(7,2)

DECLARE cur_OrderDetail CURSOR

FOR

SELECT productNo,quantity,price

FROM OrderDetail

WHERE orderNo=@orderNo

OPEN cur_OrderDetail

FETCH cur_OrderDetail INTO @productNo,@quantity,@price

WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT CONVERT(char(15),@productNo) + CONVERT(char(15),@quantity) + CONVERT(char(15),@price)

FETCH cur_OrderDetail INTO @productNo,@quantity,@price

END

CLOSE cur_OrderDetail

DEALLOCATE cur_OrderDetail

PRINT ''

FETCH cur_OrderMaster INTO @orderNo,@customerNo,@salerNo,@orderDate

END

CLOSE cur_OrderMaster

DEALLOCATE cur_OrderMaster

--(5) 利用存储过程，给Employee 表添加一条业务部门员工的信息。

CREATE PROCEDURE proc_Employee1

@employeeNo char(8),

@employeeName varchar(10),

@sex char(1),

@birthday datetime,

@address varchar(50),

@telephone varchar(20),

@hireDate datetime,

@department varchar(30),

@headShip varchar(10),

@salary numeric(8,2)

INSERT Employee VALUES(@employeeNo,@employeeName,@sex,@birthday,@address,@telephone,

@hireDate,@department,@headShip,@salary)

--EXEC proc_Employee1 'E2012001', ' 吕布', 'M', '1970-11-6',' 陕西省五原市阳关大道10号', NULL,'1990-11-18',' 保卫科',' 科长',5000.00

--(6) 利用存储过程输出所有客户姓名、客户订购金额及相应业务员的姓名。

CREATE PROCEDURE proc_OrderMaster

SELECT orderNo,customerName,orderSum,employeeName

FROM OrderMaster,Customer,Employee

WHERE OrderMaster.customerNo=Customer.customerNo

AND OrderMaster.salerNo=Employee.employeeNo

--EXEC proc_OrderMaster

--(7) 利用存储过程查找某员工的员工编号、订单编号、销售金额。

--IF EXISTS(SELECT name FROM sysobjects WHERE name='proc_Employee3' AND type='P') IF OBJECT_ID ( 'proc_Employee2', 'P' ) IS NOT NULL

DROP PROCEDURE proc_Employee2

CREATE PROCEDURE proc_Employee2

@employeeName varchar(10)

SELECT employeeNo,orderNo,orderSum

FROM Employee,OrderMaster

WHERE Employee.employeeNo=OrderMaster.salerNo

AND employeeName=@employeeName

--EXEC proc_Employee2 '张小梅'

--(8) 利用存储过程查找姓“李”并且职称为“职员”的员工编号、订单编号、销售金额。 CREATE PROCEDURE proc_Employee3

@employeeName varchar(10),

@headShip varchar(10)

SELECT employeeNo,employeeName,orderNo,orderSum

FROM Employee,OrderMaster

WHERE Employee.employeeNo=OrderMaster.salerNo

AND employeeName LIKE @employeeName

AND headShip=@headShip

--EXEC proc_Employee3 '李%','职员'

--(9) 请使用游标和循环语句编写一个存储过程proSearchCustomer ，根据客户编号，查找该客户

--的名称、地址以及所有与该客户有关的销售记录，销售记录按商品分组输出。 CREATE PROCEDURE proSearchCustomer

@customerNo char(9)

DECLARE @customerName varchar(40)

DECLARE @address varchar(40)

SELECT @customerName=customerName,@address=address

FROM Customer

WHERE customerNo=@customerNo

PRINT CONVERT(char(15),@customerNo) + CONVERT(char(40),@customerName) + CONVERT(char(40),@address)

'==================================================================================='

PRINT CONVERT(char(15),'订单号') + CONVERT(char(12),'商品号') + CONVERT(char(40),'商品名') +

CONVERT(char(10),'商品数量') + CONVERT(char(10),'商品单价')

DECLARE @orderNo char(12),

@productNo char(9),

@productName varchar(40),

@quantity int,

@price numeric(7,2)

DECLARE cur_OrderMaster CURSOR

FOR

SELECT orderNo

FROM OrderMaster

WHERE customerNo=@customerNo

OPEN cur_OrderMaster

FETCH cur_OrderMaster INTO @orderNo

WHILE (@@FETCH_STATUS=0)

BEGIN

DECLARE cur_OrderDetail CURSOR

FOR

SELECT Product.productNo,productName,quantity,price

FROM OrderDetail,Product

WHERE orderNo=@orderNo AND OrderDetail.productNo=Product.productNo ORDER BY Product.productNo

OPEN cur_OrderDetail

FETCH cur_OrderDetail INTO @productNo,@productName,@quantity,@price WHILE (@@FETCH_STATUS=0)

BEGIN

PRINT CONVERT(char(15),@orderNo) + CONVERT(char(12),@productNO) + CONVERT(char(40),@productName) +

CONVERT(char(10),@quantity) + CONVERT(char(10),@price)

FETCH cur_OrderDetail INTO @productNo,@productName,@quantity,@price END

CLOSE cur_OrderDetail

DEALLOCATE cur_OrderDetail

FETCH cur_OrderMaster INTO @orderNo

END

CLOSE cur_OrderMaster

DEALLOCATE cur_OrderMaster

EXEC proSearchCustomer 'C20050001'

--实验九：触发器

--请完成下面的实验内容。

--(1) 设置一个触发器，该触发器仅允许dbo 用户可以删除Employee 表内数据，否则出错。 --IF OBJECT_ID ('dbo.trig_Employee', 'TR') IS NOT NULL

IF EXISTS(SELECT name FROM sysobjects WHERE name='trig_Employee' AND type='TR') DROP TRIGGER trig_Employee;

CREATE TRIGGER trig_Employee

ON Employee

FOR DELETE

IF USER'dbo'

BEGIN

RAISERROR ('不是dbo 用户，不允许删除Employee 表内数据！！！', 16, 1) ROLLBACK TRANSACTION END

--(2) 在OrderMaster 表中创建触发器，插入数据时要先检查Employee 表中是否存在和Employee 表同样值的业务员编号，

--如果不存在则不允许插入。

CREATE TRIGGER trig_OrderMaster

ON OrderMaster

FOR INSERT

IF NOT EXISTS(

SELECT *

FROM inserted

WHERE salerNo IN(

SELECT employeeNo

FROM Employee

)

BEGIN

RAISERROR ('违反外键约束，不允许插入！！！' ,16, 1)

ROLLBACK TRANSACTION

END

--说明：此题的约束已在OrderMaster 表salerNo 属性的外键约束中定义，本题仅起到练习作用，

--测试时可先将外键约束解除

--(3) 级联更新：当更新Customer 表中customerNo 列的值时，同时更新OrderMaster 表中的customerNo 列的值，

--并且一次只能更新一行。

CREATE TRIGGER trig_Customer

ON Customer

FOR UPDATE

IF UPDATE(customerNo)

BEGIN

DECLARE @row_cnt int

SELECT @row_cnt=COUNT(*) FROM deleted

IF @row_cnt>1

BEGIN

RAISERROR ('此更新操作可能会更新多条客户表数据！！！' ,16 ,1)

ROLLBACK TRANSACTION

END

UPDATE OrderMaster

SET customerNo=(SELECT customerNo FROM inserted)

WHERE customerNo=(SELECT customerNo FROM deleted)

END

--说明：此题的约束已在OrderMaster 表customerNo 属性的外键约束中定义，在更新Customer 表的customerNo 属性时会提示级联更新错误，

--测试时可先将外键约束解除

--(4) 对Product 表写一个UPDATE 触发器。

--当用户更新Product 表的数据时，触发器自动将该操作者的名称和操作时间记录在一张表内，以便跟踪。

--分析：解决这个问题可以分三步走。

--① 建立跟踪表。

CREATE TABLE TraceProduct(

userid char(10) NOT NULL,

operateDate datetime NOT NULL,

operateType char(10) NOT NULL,

CONSTRAINT PK_TraceProduct PRIMARY KEY(userid,operateDate)

)

--② 建立触发器。

CREATE TRIGGER trig_Product

ON Product

FOR UPDATE

IF EXISTS(SELECT * FROM inserted)

INSERT TraceProduct VALUES(USER, GETDATE(), 'UPDATE')

--③ 验证。

--实验十：事务处理

--请完成下面的实验内容。

--① 一新客户订购了P20060003商品一件，请定义一事务完成数据库更新任务。

--说明：题目说明不清，一新客户是不是说还要为这个客户创建一条客户信息？还要为这个客户的这次订购创建一条订单信息？

--为简便起见，模仿P138[例7.1]

BEGIN TRANSACTION trans_InsertOrder

INSERT OrderDetail VALUES('[1**********]2', 'P20060003', 1, 270.00)

IF @@ERROR!=0

BEGIN

PRINT '插入操作错误！'

RETURN

END

UPDATE OrderMaster

SET orderSum=orderSum+quantity*price

FROM OrderMaster a, (SELECT orderNo,quantity,price

FROM OrderDetail

WHERE orderNo='[1**********]2' AND productNo='P20060003') b WHERE a.orderNo='[1**********]2' AND b.orderNo=a.orderNo

IF @@ERROR!=0

BEGIN

ROLLBACK TRANSACTION trans_InsertOrder

PRINT '更新操作错误！'

RETURN

END

COMMIT TRANSACTION trans_InsertOrder

--② 业务员E2005003因故辞职，要求删除该业务员在数据库中的全部信息。请定义 --一事务完成数据库更新任务。

--说明：因为某人的辞职就删除其相关信息会导致业务数据的丢失，是不合理的，可以为员工定义一个在岗状态项。

--另：应定义一职员号变量实现程序通用性。

--测试时可先将外键约束解除

BEGIN TRANSACTION trans_DeleteEmployee

DELETE OrderDetail WHERE orderNo IN(SELECT orderNo FROM OrderMaster WHERE salerNo='E2005003')

IF @@ERROR!=0

BEGIN

PRINT 'OrderDetail表删除操作错误！'

RETURN

END

DELETE OrderMaster WHERE salerNo='2005'

IF @@ERROR!=0

BEGIN

PRINT 'OrderMaster表删除操作错误！'

ROLLBACK TRANSACTION trans_DeleteEmployee

RETURN

END

DELETE Employee WHERE employeeNo='E2005003' IF @@ERROR!=0

BEGIN

PRINT 'Employee表删除操作错误！'

ROLLBACK TRANSACTION trans_DeleteEmployee RETURN

END

COMMIT TRANSACTION trans_DeleteEmployee