資料庫設計/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