【SQL开源代码栏目提醒】:网学会员为广大网友收集整理了,CH13.sql,希望对大家有所帮助!
--- P13-6
CREATE TABLE TABLE_1
(
ProductID smallint NOT NULL,
ProductName char(30) UNIQUE,
Price smallmoney,
Manufacturer char(30)
)
--- P13-10
CREATE TABLE TABLE_2
(
ProductID smallint NOT NULL Primary Key,
ProductName char(30),
Price smallmoney,
Manufacturer char(30)
)
--- P13-21
CREATE TABLE TABLE_3
(
c1 int NOT NULL Primary key,
c2 char(4),
c3 char(6),
c4 char(30)
)
CREATE INDEX MyIndex_1
ON Table_3 (c1)
CREATE INDEX MyIndex_2
ON Table_3 (c2, c3)
--- P13-23a
CREATE TABLE TABLE_4
(
ProductID smallint NOT NULL Primary Key,
ProductName char(30),
Price smallmoney,
Manufacturer char(30)
)
CREATE UNIQUE NONCLUSTERED INDEX index_3
ON TABLE_4 (ProductName)
INCLUDE (price)
WITH PAD_INDEX, FILLFACTOR=30, IGNORE_DUP_KEY
--- P13-23b
EXEC sp_helpindex table_4
--- P13-24a
DROP INDEX Table_4.index_3
--- P13-24b
CREATE TABLE MyTable
(
ProductID smallint NOT NULL Primary key,
ProductName char(30) UNIQUE,
Price smallmoney,
Manufacturer char(30)
)
EXEC sp_helpindex MyTable
--- P13-25a
DROP INDEX MyTable.PK__MyTable__7D439ABD
DROP INDEX MyTable.UQ__MyTable__7E37BEF6
--- P13-25b
ALTER TABLE MyTable DROP CONSTRAINT PK__MyTable__7D439ABD
ALTER TABLE MyTable DROP CONSTRAINT UQ__MyTable__7E37BEF6
--- P13-27
DBCC DBREINDEX (客户, PK_客户, 70)
--- P13-28
SELECT 电话
FROM 员工
WHERE = '赵飞燕'
--- P13-31
SELECT 客户名称
FROM 客户
WHERE 简要地址 Like '%仁爱路%'
--- P13-36
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL,
QUOTED_IDENTIFIER, ANSI_NULLS,
ANSI_PADDING, ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO
CREATE VIEW dbo.产品日报
WITH SCHEMABINDING
AS
SELECT 下单日期 AS 日期, 书籍编号 AS 书号,
SUM (数量) AS 每日销售量, COUNT_BIG (*) AS 每日订单数
FROM dbo.订单 INNER JOIN dbo.订购项目
ON 订单.订单编号 = 订购项目.订单编号
GROUP BY 下单日期, 书籍编号
GO
SELECT * FROM 产品日报
--- P13-37
CREATE UNIQUE CLUSTERED INDEX PK_产品日报
ON 产品日报 (日期, 书号)
--- P13-38
CREATE INDEX IX_书号
ON 产品日报(书号)
INCLUDE (日期, 每日销售量)
--- P13-39
SELECT 日期, 书号, 每日销售量, 每日订单数
FROM 产品日报
WHERE 日期 = '2005/9/11'
ORDER BY 日期
--- P13-40a
SELECT 日期, 书号, 每日销售量
FROM 产品日报
WHERE 书号 = 2
--- P13-40b
SELECT 下单日期, SUM(数量) AS 销售量
FROM 订单 INNER JOIN 订购项目
ON 订单.订单编号 = 订购项目.订单编号
GROUP BY 下单日期, 书籍编号
ORDER BY 下单日期
--- P13-40c
SELECT 下单日期, SUM(数量) AS 销售量
FROM 订单 INNER JOIN 订购项目
ON 订单.订单编号 = 订购项目.订单编号
GROUP BY 下单日期, 书籍编号
ORDER BY 书籍编号
--- P13-41a
SELECT 下单日期, SUM(数量) AS 销售量
FROM 订单 INNER JOIN 订购项目
ON 订单.订单编号 = 订购项目.订单编号
GROUP BY 下单日期
ORDER BY 下单日期 DESC
--- P13-41b
SELECT 下单日期, 书籍编号, AVG(数量) AS 订单平均销售量
FROM 订单 INNER JOIN 订购项目
ON 订单.订单编号 = 订购项目.订单编号
GROUP BY 下单日期, 书籍编号
ORDER BY 下单日期
--- P13-41c
DROP INDEX 产品日报.PK_产品日报