数据库设计/SQL 实验室及解答
下载以下脚本：订单和数据
图 C.1. 订单和数据的 ERD。
- 使用脚本 OrdersAndData.sql 创建表并添加订单和数据 ERD 在图 C.1 中的数据。
- 创建一个名为 Orders 的数据库。修改脚本以集成 PK 和引用完整性。显示包含步骤 3 中给出的约束的修改后的 CREATE TABLE 语句。
- 添加以下约束
- tblCustomers 表：Country - 默认值为加拿大
- tblOrderDetails：Quantity - > 0
- tblShippers：CompanyName 必须是唯一的。
- tblOrders：ShippedDate 必须大于订单日期。
CREATE DATABASE Orders
Go
Use Orders
Go
Use Orders
Go
CREATE TABLE [dbo].[tblCustomers]
[CustomerID] nvarchar(5) NOT NULL,
[CompanyName] nvarchar(40) NOT NULL,
[ContactName] nvarchar(30) NULL,
[ContactTitle] nvarchar(30) NULL,
[Address] nvarchar(60) NULL,
[City] nvarchar(15) NULL,
[Region] nvarchar(15) NULL,
[PostalCode] nvarchar(10) NULL,
[Country] nvarchar(15) NULL
Constraint df_country DEFAULT ‘Canada’,
[Phone] nvarchar(24) NULL,
[Fax] nvarchar(24) NULL,
Primary Key (CustomerID)
);
CREATE TABLE [dbo].[tblSupplier] (
[SupplierID] int NOT NULL,
[Name] nvarchar(50) NULL,
[Address] nvarchar(50) NULL,
[City] nvarchar(50) NULL,
[Province] nvarchar(50) NULL,
Primary Key (SupplierID)
);
CREATE TABLE [dbo].[tblShippers] (
[ShipperID] int NOT NULL,
[CompanyName] nvarchar(40) NOT NULL,
Primary Key (ShipperID),<
CONSTRAINT uc_CompanyName UNIQUE (CompanyName)
);
CREATE TABLE [dbo].[tblProducts] (
[ProductID] int NOT NULL,
[SupplierID] int NULL,
[CategoryID] int NULL,
[ProductName] nvarchar(40) NOT NULL,
[EnglishName] nvarchar(40) NULL,
[QuantityPerUnit] nvarchar(20) NULL,
[UnitPrice] money NULL,
[UnitsInStock] smallint NULL,
[UnitsOnOrder] smallint NULL,
[ReorderLevel] smallint NULL,
[Discontinued] bit NOT NULL,
Primary Key (ProductID),
Foreign Key (SupplierID) References tblSupplier
);
CREATE TABLE [dbo].[tblOrders] (
[OrderID] int NOT NULL,
[CustomerID] nvarchar(5) NOT NULL,
[EmployeeID] int NULL,
[ShipName] nvarchar(40) NULL,
[ShipAddress] nvarchar(60) NULL,
[ShipCity] nvarchar(15) NULL,
[ShipRegion] nvarchar(15) NULL,
[ShipPostalCode] nvarchar(10) NULL,
[ShipCountry] nvarchar(15) NULL,
[ShipVia] int NULL,
[OrderDate] smalldatetime NULL,
[RequiredDate] smalldatetime NULL,
[ShippedDate] smalldatetime NULL,
[Freight] money NULL
Primary Key (OrderID),
Foreign Key (CustomerID) References tblCustomers,
Foreign Key (ShipVia) References tblShippers,
Constraint valid_ShipDate CHECK (ShippedDate > OrderDate)
);
CREATE TABLE [dbo].[tblOrderDetails] (
[OrderID] int NOT NULL,
[ProductID] int NOT NULL,
[UnitPrice] money NOT NULL,
[Quantity] smallint NOT NULL,
[Discount] real NOT NULL,
Primary Key (OrderID, ProductID),
Foreign Key (OrderID) References tblOrders,
Foreign Key (ProductID) References tblProducts,
Constraint Valid_Qty Check (Quantity > 0)
);
Go
1. 显示客户列表及其在 2014 年生成的订单。显示客户 ID、订单 ID、订单日期和订购日期。
Use Orders
Go
SELECT CompanyName, OrderID, RequiredDate as ‘订单日期’, OrderDate as ‘订购日期’
FROM tblcustomers JOIN tblOrders on tblOrders.CustomerID = tblCustomers.CustomerID
WHERE Year(OrderDate) = 2014
2. 使用 ALTER TABLE 语句，在 tblcustomer 中添加一个新字段（Active）。将其默认为 True。
ALTER TABLE tblCustomers
ADD Active bit DEFAULT (‘True’)
3. 显示 2012 年 9 月 1 日之前购买的所有订单。显示公司名称、订购日期和订单总额（包括运费）。
SELECT tblOrders.OrderID, OrderDate as ‘订购日期’, sum(unitprice*quantity*(1-discount))+ freight as ‘总成本’
FROM tblOrderDetails join tblOrders on tblOrders.orderID = tblOrderDetails.OrderID
WHERE OrderDate < ‘September 1, 2012’
GROUP BY tblOrders.OrderID, freight, OrderDate
4. 显示通过 Federal Shipping 发货的所有订单。显示 OrderID、ShipName、ShipAddress 和 CustomerID。
SELECT OrderID, ShipName, ShipAddress, CustomerID
FROM tblOrders join tblShippers on tblOrders.ShipVia = tblShippers.ShipperID
WHERE CompanyName= ‘Federal Shipping’
5. 显示所有在 2011 年没有进行过购买的客户。
SELECT CompanyName
FROM tblCustomers
WHERE CustomerID not in
( SELECT CustomerID
FROM tblOrders
WHERE Year(OrderDate) = 2011
)
6. 显示从未订购过的所有产品。
SELECT ProductID from tblProducts
Except
SELECT ProductID from tblOrderDetails
OR
SELECT Products.ProductID,Products.ProductName
FROM Products LEFT JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
WHERE [Order Details].OrderID IS NULL
7. 显示居住在伦敦的客户的 OrderID。使用子查询。显示 CustomerID、CustomerName 和 OrderID。
SELECT Customers.CompanyName,Customers.CustomerID,OrderID
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CompanyName IN
(SELECT CompanyName
FROM Customers
WHERE City = ‘London’)
8. 显示由供应商 A 和供应商 B 供应的产品。显示产品名称和供应商名称。
SELECT ProductName, Name
FROM tblProducts JOIN tblSupplier on tblProducts.SupplierID = tblSupplier.SupplierID
WHERE Name Like ‘Supplier A’ or Name Like ‘Supplier B’
9. 显示所有装在箱子里的产品。显示产品名称和 QuantityPerUnit。
SELECT EnglishName, ProductName, QuantityPerUnit
FROM tblProducts
WHERE QuantityPerUnit like ‘%box%’
ORDER BY EnglishName
1. 创建一个 Employee 表。主键应该是 EmployeeID（自动编号）。添加以下字段：LastName、FirstName、Address、City、Province、Postalcode、Phone、Salary。显示 CREATE TABLE 语句和五个员工的 INSERT 语句。将 Employee 表连接到 tblOrders。显示创建表、设置约束和添加员工的脚本。
Use Orders
CREATE TABLE [dbo].[tblEmployee](
EmployeeID Int IDENTITY NOT NULL ,
FirstName varchar (20) NOT NULL,
LastName varchar (20) NOT NULL,
Address varchar (50),
City varchar(20), Province varchar (50),
PostalCode char(6),
Phone char (10),
Salary Money NOT NULL,
Primary Key (EmployeeID)
Go
INSERT into tblEmployees
Values (‘Jim’, ‘Smith’, ‘123 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J6’, ‘2506155989’, ‘20.12’),
(‘Jimmy’, ‘Smithy’, ‘124 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J7’, ‘2506155984’, ‘21.12’),
(‘John’, ‘Smore’, ’13 Fake’, ‘Terrace’, ‘BC’, ‘V4G5J6’, ‘2506115989’, ‘19.12’),
(‘Jay’, ‘Sith’, ’12 Fake’, ‘Terrace’, ‘BC’, ‘V8G4J6’, ‘2506155939’, ‘25.12’),
(‘Jig’, ‘Mith’, ’23 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J5’, ‘2506455989’, ‘18.12’);
Go
2. 在 tblOrders 表中添加一个名为 TotalSales 的字段。显示 DDL – ALTER TABLE 语句。
ALTER TABLE tblOrders
ADD Foreign Key (EmployeeID) references tblEmployees (EmployeeID)
3. 使用 UPDATE 语句，根据订单明细表为每个订单添加总销售额。
UPDATE tblOrders
Set TotalSales = (select sum(unitprice*quantity*(1-discount))
FROM tblOrderDetails
WHERE tblOrderDetails.OrderID= tblOrders.OrderID
GROUP BY OrderID