Database AdventureWorks :Download
Chapter 1 Exercise-1
Identify the entities, relationships, and attributes in the following ERdiagram.
Identify the entities, relationships, and attributes in the following ERdiagram.
【Solution】
Entities: EMPLOYEE, DEPARTMENT
Attributes of EMPLOYEE: EMP_NO, NAME, ADDRESS
Attributes of DEPARTMENT: DEPT_NAME, CAPACITY
Relationship: WORKS
Chapter 2 Exercise-1
Depict the relationships between the following entities.
【Solution】
【Solution】
1. Mother and a Child -bring-up
2. University and Faculty - have
3. Airplane and Passengers - board
Chapter 3 Exercise-1
Normalize the following relation.
(OrderNo, OrderDate, TotalCost, ToyID,ToyName,FirstName,LastName,Address)
【Solution】
(OrderNo,ToyID,FirstName,LastName,OrderDate,TotalCost)
(ToyID,ToyName)
(FirstName,LastName,Address)
Chapter 3 Exercise-2
Normalize the following data structure.
【Solution】
(EMP-CODE,EMP_NAME,ADDRESS)
(EMP_CODE,DEPARTMENT_CODE)
(DEPARTMENT_CODE,DEPARTMENT_NAME,DEPARTMENT_HEAD)
Querying and Managing Data Using SQL Server 2005
Chapter 2 Exercise-1
Display the details of all the customers.
【Solution】
Display the details of all the customers.
【Solution】
SELECT * FROM Sales. Customer
Chapter 2 Exercise-2
Display the ID, type, number and expiry year of all the credit carde in the following format.
Credit Card ID
Credit Card Type
Credit Card Number
Expiry Year
【Solution】
SELECT 'Credit Card ID' = CreditCardID, 'Credit Card Type' = CardType, 'Credit Card Number' = CardNumber, 'Expiry Year' = ExpYear from Sales.CreditCard
Chapter 2 Exercise-3
Display the customer ID and the account number of all the customer who live in the Territory ID 4.
Display the customer ID and the account number of all the customer who live in the Territory ID 4.
【Solution】
SELECT CustomerID, AccountNumber FROM Sales. Customer WHERE
TerritoryID = 4
Chapter 2 Exercise-4
Display all the details of the sales orders that have a cost exceeding $2000.
【Solution】
SELECT * FROM Sales.SalesOrderDetail WHERE LineTotal > 2000.00
Chapter2 Exercise5
Display the sales order details of the product named ‘Cable Lock’.
【Solution】
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID=843
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID=843
Chapter2 Exercise6
Display the list of all the orders placed on June 06, 2004.
【Solution】
SELECT * FROM Sales.SalesOrderHeader WHERE OrderDate = '06-06-2004'
Chapter2 Exercise7
Display a report of all the orders in the following format. Order ID Order Quantity Unit Price Total Cost.
【Solution】
SELECT 'Order ID' = SalesOrderID, 'Order Quantity' = OrderQty,'Unit Price' = UnitPrice, 'Total Cost' = OrderQty * UnitPrice FROM Sales.SalesOrderDetail
Chapter2 Exercise8
Display a list of all the sales orders in the price range of $2000 to $2100.
【Solution】
SELECT * FROM Sales.SalesOrderDetail WHERE LineTotal BETWEEN 2000 AND 2100
Chapter2 Exercise9
Display the name, country region code and sales year to date for the territory with Territory ID as 1.
Display the name, country region code and sales year to date for the territory with Territory ID as 1.
【Solution】
SELECT Name, CountryRegionCode, SalesYTD FROM Sales.SalesTerritory WHERE TerritoryID = 1
Chapter2 Exercise10
Display the details of the orders that have a tax amount of more than $10000.
【Solution】
SELECT * FROM Sales.SalesOrderHeader WHERE TaxAmt >= 10000
Chapter2 Exercise11
Display the sales territory details of Canada, Fance, and Germany.
【Solution】
SELECT * FROM Sales. SalesTerritory WHERE Name IN ('Canada', 'France', 'Germany')
Chapter2 Exercise12
Generate a report that contains the IDs of sales persons living in the territory with TerritoryID as 2 or 4 id required in the following format.
Sales Person ID Territory ID
【Solution】
SELECT 'Sales Person ID'=SalesPersonID, 'Territory ID' = TerritoryID FROM Sales.SalesTerritoryHistory WHERE TerritoryID = 2 OR TerritoryID = 4
Chapter2 Exercise13
Display the details of the vista credit cards that are expiring in the year 2006.
【Solution】
SELECT * FROM Sales.CreditCard WHERE CardType = 'Vista' AND ExpYear = '2006'
Chapter2 Exercise14
Display the details of all the orders that were shipped after July 12, 2004.
【Solution】
SELECT * FROM Sales.SalesOrderHeader WHERE ShipDate > '2004-07-12'
Chapter2 Exercise15
Display the orders placed on July 01, 2001 that have a total cost of more than $10000 in the following format.
Order Number Order Date Status Total Cost
【Solution】
SELECT 'Order ID' = SalesOrderID, 'Total Due' = TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC
Chapter2 Exercise16
Display the details of the orders that have been placed by customers online.
【Solution】
SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 0
Chapter2 Exercise17
Display the order ID and the total amount due of all the sales orders in the following format.Ensure that the order with the highest price is at the top of the list.
Order ID Total Due
【Solution】
SELECT 'Order ID' = SalesOrderID, 'Total Due' = TotalDue FROM Sales.SalesOrderHeader ORDER BY TotalDue DESC
Chapter2 Exercise18
Display the order ID and the tax amount for the sales orders that are less than $2000.The data should be diaplayed in the ascending order.
【Solution】
SELECT SalesOrderID, TaxAmt FROM Sales.SalesOrderHeader WHERE TotalDue < 2000 ORDER BY TotalDue ASC
Chapter2 Exercise19
Display the order number and the total value of the order in ascending order of the total value.
【Solution】
SELECT SalesOrderID, ProductID, LineTotal FROM Sales.SalesOrderDetail ORDER BY LineTotal ASC
Chapter2 Exercise20
Display the maximum, minimum and the average rate of sales orders.
【Solution】
SELECT 'Maximum' = MAX(TotalDue), 'Minimum' = MIN(TotalDue),'Average'= AVG(TotalDue) FROM Sales.SalesOrderHeader
Chapter2 Exercise21
Display the total value of all the orders put together.
【Solution】
SELECT 'Total Value of all Orders' = SUM(TotalDue) FROM Sales.SalesOrderHeader
Chapter2 Exercise22
Display the Order ID of the top five orders based on the total amount due in the year 2001.
【Solution】
SELECT TOP 5 SalesOrderID FROM Sales.SalesOrderHeader WHERE Datepart(yyyy,OrderDate) = 2001 ORDER BY TotalDue DESC
Chapter2 Exercise23
Display the details of all the currencied that have the word ‘Dollar’ in their name.
【Solution】
SELECT * FROM Sales.Currency WHERE Name LIKE '%Dollar%'
Chapter2 Exercise24
Display all territories whose names begin with ‘N’.
【Solution】
SELECT * FROM Sales.SalesTerritory WHERE Name LIKE 'N%'
Chapter2 Exercise25
Display the SalesPersonID, the TerritoryID and the sales quota for those sales persons who have been assigned a sales quota.The data should be displayed in the following format
Sales Person ID Territory ID Sales Quota
【Solution】
SELECT 'Sales Person ID' = SalesPersonID, 'TerritoryID'= TerritoryID, 'Sales Quota' = SalesQuota FROM Sales.SalesPerson WHERE SalesQuota IS NOT NULL
Chapter2 Exercise26
What will be the output of the following code written to display the total order value for each order?
【Solution】
select salesorderid,ProductID, sum(linetotal)as total
from sales.salesorderdetail group by salesorderid,productid
Chapter2 Exercise27
You can place an order for more than one product. Display a report containing the product ID and the total cost of products for the product ID whose total cost is more than $10000.
【Solution】
SELECT ProductID, Sum(LineTotal) as Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING Sum(LineTotal) > 10000
Chapter2 Exercise28
The following SQL query containing the COMPUTE BY clause generates errors.What are possible causes of such errors?
SELECT ProductID, SUM(LineTotal) AS 'Total' FROM Sales.SalesOrderDetail group BY productid
【Solution】
SELECT * from Sales.SalesOrderDetail
SELECT ProductID, SUM (LineTotal) as 'Total' from Sales.SalesOrderDetail
Group by cube (ProductID)
Chapter2 Exercise29
Display the top three sales persons based on the bonus.
【Solution】
SELECT TOP 3* FROM Sales.SalesPerson ORDER BY bonus DESC
Chapter2 Exercise30
Display the details of those stores, which have Bike in their name.
【Solution】
SELECT * FROM Sales.Store WHERE Name LIKE '%bike%'
Chapter2 Exercise31
Display the total amount collected from the orders for each order date.
【Solution】
SELECT OrderDate, SUM(TotalDue) FROM Sales.SalesOrderHeader GROUP BY OrderDate
Chapter2 Exercise32
Display the total unit price and the total amount collected after selling the products, 774 and 777. In addition, calculate the total amount collected from these two products.
【Solution】
SELECT ProductID, UnitPrice, LineTotal FROM Sales.SalesOrderDetail
WHERE ProductID IN (777, 774)
ORDER BY ProductID, UnitPrice, LineTotal
COMPUTE SUM(UnitPrice), SUM(LineTotal) BY ProductID
COMPUTE Sum(UnitPrice), Sum(LineTotal)
Chapter2 Exercise33
Display the sales order ID and the maximum and minimum values of the order based on the sales order ID. In addition, ensure that the order amount is greater than $5000.
【Solution】
SELECT SalesOrderID, MIN(LineTotal) as ‘Minimum’ ,
MAX(LineTotal) as ‘Maximum’
FROM Sales.SalesOrderDetail
WHERE LineTotal > 5000
GROUP BY SalesOrderID
Chapter2 Exercise34
A report containing the sales order ID and the average value of the total amount , which is greater than $5000 is required in the following format.
Sales Order ID Average Value
【Solution】
SELECT SalesOrderID, 'Average Value' = Avg(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(LineTotal) > 5000
Chapter2 Exercise35
Display the different types of credit cards used for purchasing products.
【Solution】
SELECT DISTINCT CardType FROM Sales.CreditCard
Chapter2 Exercise36
Display the customer ID, name and sales person ID for all the stores.According to the requirement, only first 15 letters of the customer name should be displayed.
【Solution】
SELECT CustomerID, Name = LEFT(Name, 15), SalesPersonID FROM Sales.Store
Chapter2 Exercise37
Chapter2 Exercise37
Display all orders in the following format.
Order Number
Total Due
Day of Order
Week Day
【Solution】
SELECT 'Order Number' = SalesOrderID, 'Total Due' = TotalDue,
'Day of Order' = TEPART(dd, OrderDate), 'Week Day' =
'Day of Order' = TEPART(dd, OrderDate), 'Week Day' =
DATEPART(dw, OrderDate) FROM Sales.SalesOrderHeader
Chapter2 Exercise38
Display SalesOrderID, OrderQty and UnitPrice from the SalesOrderDetail table where a similar unit price needs to be marked with an identical value.
【Solution】
SELECT SalesOrderID, OrderQty,UnitPrice, DENSE_RANK() OVER(ORDER BY UnitPrice ASC)AS RANK FROM Sales.SalesOrderDetail
Chapter2 Exercise39
Display the EmployeeID and the HireDate of the employees from the Employee table.The 9 month and the year need to be diaplayed.
【Solution】
SELECT EmployeeID, Month = DATENAME (mm, HireDate), Year = DATENAME
(yy, HireDate) FROM HumanResources.Employee
P.3.27 Practice Questions Exercises 5
Write a query to display the SalesPersonID, SalesQuota, QuotaDate, and Bonus of all the sales person of AdventureWorks,who earnn a bonus more than 3000.
【Solution】
SELECT spqh.SalesPersonID,spqh.SalesQuota,spqh.QuotaDate,sp.Bonus FROM Sales.SalesPersonQuotaHistory spqh JOIN Sales.SalesPerson sp ON spqh.SalesPersonID=sp.SalesPersonID WHERE sp.Bonus>3000
OR
SELECT spqh.SalesPersonID,spqh.SalesQuota,spqh.QuotaDate,sp.Bonus FROM Sales.SalesPersonQuotaHistory spqh, Sales.SalesPerson sp WHERE sp.Bonus>3000 and spqh.SalesPersonID=sp.SalesPersonID
Exercises 8
Write a query to display the EmployeeID, Title, and Rate of the employees of AdventureWorks, who are working as Design Engineers.
【Solution】
SELECT e.EmployeeID, e.Title, eph.Rate FROM HumanResources.Employee e JOIN HumanResources.EmployeePayHistory eph ON e.EmployeeID=eph.EmployeeID WHERE e.Title='Design Engineer'
OR
SELECT e.EmployeeID, e.Title, eph.Rate FROM HumanResources.Employee e ,HumanResources.EmployeePayHistory eph WHERE e.Title='Design Engineer' and e.EmployeeID=eph.EmployeeID
Chapter3 Exercise1
Write a query to display the sales person ID of all the sales persons and name of the territories to which they belong.
【Solution】
SELECT SalesPersonID, Name FROM Sales.SalesPerson JOIN Sales.SalesTerritory ON SalesPerson.TerritoryID = SalesTerritory.TerritoryID
Chapter3 Exercise2
Write a query to display the sales person ID ,territory ID, and territory name of all sales persons in the following format.
Person ID Terrritory ID Name
【Solution】
SELECT 'Person ID'= SalesPersonID, 'Territory ID'= s.TerritoryID, Name FROM Sales.SalesPerson s JOIN Sales.SalesTerritory st on s.TerritoryID = st.TerritoryID
Chapter3 Exercise3
Write a query to display the sales order ID ,the product ID, and order date for all products in the following format.
Order ID Product ID Order Date
【Solution】
SELECT 'Order ID' = h.SalesOrderID, 'Product ID'= d.ProductID, 'Order Date' = OrderDate FROM Sales.SalesOrderHeader h join Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
Chapter3 Exercise4
Write a query to display the sales person ID and territory names
for all sales persons.If a sales person does not belong to any territory, NULL should be diaplayed.
Sales Person ID Territory Name
268 NULL
275 Northeast
【Solution】
SELECT 'Sales Person ID' = SalesPersonID, 'Territory Name' = Name FROM Sales.SalesPerson LEFT OUTER JOIN Sales.SalesTerritory ON SalesPerson.TerritoryID = SalesTerritory.TerritoryID
Chapter3 Exercise5
Write a query to display the sales order ID, territory ID, month and year of order in the following format.
SalesOrderID Territory Name Month Year
【Solution】
SELECT SalesOrderID, 'Territory Name' = Name, Month = Datename(mm, OrderDate), Year = Datename(yy, OrderDate) FROM Sales.SalesOrderHeader s JOIN Sales.SalesTerritory t on s.TerritoryID = t.TerritoryID
Chapter3 Exercise6
Write a query to display the order number, territory name, order date and the quarter in which each order was placed, in the following format.
SalesOrderID Territory Name OrderDate Quarter
43659 Southeast 2001-05-20 00:00:00.000
【Solution】
SELECT SalesOrderID, 'Territory Name' = Name, OrderDate, Quarter = datepart (qq, OrderDate) FROM Sales.SalesOrderHeader s JOIN Sales.SalesTerritory t on s.TerritoryID = t.TerritoryID
Chapter3 Exercise7
Write a query to display the total amount due of all the sales orders rounded off to a whole number. In addition, display the sales order ID and the type of credit card through which the payment was made.
【Solution】
SELECT SalesOrderID, CardType, 'Total Due' = Round (TotalDue, 0) FROM Sales.SalesOrderHeader s join Sales.CreditCard c on s.CreditCardID = c.CreditCardID
Chapter3 Exercise8
Write a query to display all the country region codes along with the corresponding territory IDs.
【Solution】
SELECT c.CountryRegionCode, TerritoryID FROM Sales.SalesTerritory s RIGHT OUTER JOIN Sales.CountryRegionCurrency c on s.CountryRegionCode = c.CountryRegionCode
Chapter3 Exercise9
Write a query to display the total amount due of all the orders in the following format.
Order Value
The total amount due for the sales order ID : 436559 is $27231.55
The total amount due for the sales order ID : 43660 is $1716.18
【Solution】
SELECT 'Order Value' = 'The total amount due for the Sales Order ID: ' + CONVERT(Varchar(10),SalesOrderID) + ' is $' + CONVERT(Varchar(10), TotalDue) FROM Sales.SalesOrderHeader
Chapter3 Exercise10
Write a query to display the order date along with the sales order ID and territory name.The order date should be displayed in the dd/mm/yyyy format.
【Solution】
SELECT SalesOrderID, Name, Convert(Char(10), OrderDate, 103) as ‘Char Date’ FROM Sales.SalesOrderHeader s JOIN Sales.SalesTerritory t on t.TerritoryID = s.TerritoryID
Chapter3 Exercise11
Write a query to display the order ID and the territory name of the orders where the month of order is May and year is 2004.
【Solution】
SELECT SalesOrderID, Name FROM Sales.SalesOrderHeader s JOIN Sales.SalesTerritory t ON s.TerritoryID = t.TerritoryID WHERE DATENAME(mm, OrderDate) = 'May' and DATEPART (yy, OrderDate)=2004
Chapter3 Exercise12
Write a query to display the contact ID of the customers that have the ‘Vista’ credit card.
【Solution】
SELECT ContactID, CardType FROM Sales.ContactCreditCard JOIN Sales.CreditCard ON ContactCreditCard.CreditCardID = CreditCard.CreditCardID WHERE CardType = 'Vista'
Chapter3 Exercise13
Write a query to display the sales order IDs of the orders received from the Northeast territory.
【Solution】
SELECT SalesOrderID FROM Sales.SalesOrderHeader where TerritoryID = (SELECT TerritoryID FROM Sales.SalesTerritory WHERE Name = 'Northeast')
Chapter3 Exercise14
A report containing the sales order ID of those orders where the total value is greater than the average of the total value of all the order is required.
【Solution】
SELECT SalesOrderID, TotalDue FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT Avg(TotalDue) FROM Sales.SalesOrderHeader)
Chapter3 Exercise15
Write a query to display the order ID , the order detail ID and the total value of those orders where the total value is greater than the maximum of the total value of order ID 43662.
Write a query to display the order ID , the order detail ID and the total value of those orders where the total value is greater than the maximum of the total value of order ID 43662.
【Solution】
SELECT SalesOrderID, SalesOrderDetailID, LineTotal FROM Sales.SalesOrderDetail WHERE LineTotal >ALL (SELECT LineTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43662)
Chapter3 Exercise16
Write a query to display the order IDs and the credit card IDs of those cards which are expiring in the year 2007.
【Solution】
SELECT SalesOrderID, CreditCardID FROM Sales.SalesOrderHeader WHERE CreditCardID IN (SELECT CreditCardID FROM Sales.CreditCard WHERE ExpYear = 2007)
Chapter3 Exercise17
Write a query to display the credit card number of Catherine Abel.
【Solution】
SELECT CardNumber FROM Sales.CreditCard Where CreditCardID = (SELECT CreditCardID FROM Sales.ContactCreditCard Where ContactID = (SELECT ContactID FROM Person.Contact Where FirstName = 'Catherine' and LastName = 'Abel'))
Chapter3 Exercise18
Write a query to display the details of those orders for which no discount was offered.
【Solution】
SELECT * FROM Sales.SalesOrderDetail where SpecialOfferID = (SELECT SpecialOfferID FROM Sales.SpecialOffer WHERE Type = 'No Discount')
Chapter3 Exercise19
Write a query to display the order IDs and the order detail IDs along with the total value of those 14 orders that have a total value greater than the average of the total value for the order ID.
【Solution】
SELECT SalesOrderDetailID, SalesOrderID, LineTotal FROM Sales.SalesOrderDetail s1 WHERE s1.LineTotal > (SELECT AVG(s2.LineTotal) FROM Sales.SalesOrderDetail s2 Where s1.SalesOrderID = s2.SalesOrderID)
Chapter3 Exercise20
Write a query to display the sales order IDs of the orders that have been paid through Superior Card.
Write a query to display the sales order IDs of the orders that have been paid through Superior Card.
【Solution】
SELECT SalesOrderID FROM Sales.SalesOrderHeader Where CreditCardID in (SELECT CreditCardID FROM Sales.CreditCard Where CardType = 'SuperiorCard')
Chapter3 Exercise21
Write a query to display the average rate of the Australian Dollar,where the currency rate date is 1st July, 2004.
【Solution】
SELECT AverageRate FROM Sales.CurrencyRate Where CurrencyRateDate = '2004-07-01' and ToCurrencyCode = (SELECT CurrencyCode FROM Sales.Currency Where Name = 'Australian Dollar')
Practice Questions 3.
Write a query to display the employee ID and the designation of the employees whose pay rate is greater than 40.
【Solution】
SELECT e.EmployeeID, Title FROM HumanResources.Employee e,HumanResources.EmployeePayHistory eph WHERE e.EmployeeID=eph.EmployeeID AND Rate>40
【Solution】
SELECT e.EmployeeID, Title FROM HumanResources.Employee e,HumanResources.EmployeePayHistory eph WHERE e.EmployeeID=eph.EmployeeID AND Rate>40
Practice Questions 8.
Write a query to display the EmployeeID, Title, and Rate of the employees of AdventureWorks, who are working as Design Engineers.
【Solution】
【Solution】
SELECT e.EmployeeID, Title, Rate FROM HumanResources.Employee e,HumanResources.EmployeePayHistory eph WHERE e.EmployeeID=eph.EmployeeID AND Title='Design Engineer'
Chapter4 Exercise5
Consider the following table structures.
New Product
Attribute Name Data Type
ProductId char(6)
ProductName varchar(20)
ProductDescription Varchar(250)
CategoryID Char(3)
ProductRate Money
BrandID Char (3)
Photo image
Qoh Smallest
ProductImgPath Varchar(50)
Category
Attribute Name Data Type
CategoryID Char(3)
Category Char(20)
Description Varchar(100)
Category
Attribute Name Data Type
BrandID Char(3)
BrandName Char(20)
Refer to these table structures for the following problems：
1. Create the Category table. Enforce the following data integrity rules while creating the table:
a) The category id should be the primary key.
b) The Category attribute should be unique but not the primary key.
c) The description of the categories attribute can allow storage of NULL values.
2. Create the ProductBrand table. Enforce the following data integrity rules while creating the table:
a) The brand id should be the primary key.
b) The brand name should be unique but not the primary key.
3. Create the NewProduct table with the following data integrity rules:
a) The product id should be the primary key.
b) The quantity on hand(QoH) of the product should be between 0 and 200.
c) The Photo and ProductImgPath attributes can allow storage of NULL values.
d) The ProductName and ProductDescription attributes should not allow NULL values.
e) The values of the CategoryId attribute should be present in the Category table.
4. Modify the NewProduct table to enforce the following data integrity rules:
a. The values entered in the BrandId attribute should be present in the ProductBrand table.
【Solution】
1.Sql scripts as following :
CREATE TABLE Category
(
CategoryID char(3) CONSTRAINT pkCategoryID PRIMARY KEY,
Category char(20) CONSTRAINT unqCategory UNIQUE,
Description varchar(100) NULL
)
2. Sql scripts as following
CREATE TABLE ProductBrand
(
BrandID char(3) CONSTRAINT pkBrandID PRIMARY KEY,
BrandName char(20) CONSTRAINT unqBrandName UNIQUE,
)
3. Sql scripts as following
CREATE TABLE NewProduct
(
ProductID char(6) CONSTRAINT pkProductID PRIMARY KEY,
ProductName varchar(20) NOT NULL,
ProductDescription varchar(250) NOT NULL,
CategoryID char(3)CONSTRAINT fkCategoryID FOREIGN KEY REFERENCES Category(CategoryID) ,
ProductRate money, BrandID char(3),
Photo image NULL,
Qoh smallint CONSTRAINT chkQoh CHECK(Qoh BETWEEN 0 AND 200),
ProductImgPath varchar(50) NULL
)
4. Sql scripts as following
ALTER TABLE NewProduct ADD CONSTRAINT fkBrandID FOREIGN KEY (BrandID) REFERENCES ProductBrand(BrandID)
Chapter5 Exercise1
Insert the following data in the ProductBrand table of the AdventureWorks database.
BrandID BrandName
B01 Lee
B02 Nike
B03 Reebok
Insert into ProductBrand (BrandID, BrandName) values(‘B01’, ‘Lee’) Insert into ProductBrand(BrandID, BrandName) values(‘B02’, ‘Nike’) Insert into ProductBrand(BrandID, BrandName) values(‘B03’, ‘reebok’)
Chapter5 Exercise3
The address of a vendor, Comfort Road Bicycles, has changed. You need to update the following data in the AdventureWorks database.
Address 4151 Olivera
City Atlanta
StateProvinceID 17
PostalCode 30308
【Solution】
UPDATE Person.Address SET AddressLine1='4151 Olivera',City='Atlanta', StateProvinceID=17, PostalCode='30308' FROM Sales.Store ss, Sales.CustomerAddress sc,Person.Address pa WHERE ss.CustomerID=sc.CustomerID AND sc.AddressID=pa.AddressID AND ss.Name=’Comfort Road Bicycles’
Chapter5 Exercise4
Delete all the records from the ProductBrand table. Ensure that you do not delete the table.
Delete all the records from the ProductBrand table. Ensure that you do not delete the table.
【Solution】
Delete from ProductBrand
Chapter5 Exercise5
The users of AdventureWorks need to publish the details of all the customers and their address on the organizations website. To perform this task, you need to retrieve the data in the XML format.
【Solution】
【Solution】
SELECT pa.AddressLine1,pa.AddressLine2,pa.City,pa.StateProvinceID,pa.PostalCode FROM Sales.Customer sc,Sales.CustomerAddress sca,Person.Address pa WHERE sc.CustomerID=sca.CustomerID AND sca.AddressID=pa.AddressID FOR XML RAW,ELEMENTS （？）
SELECT ss.Name, pa.AddressLine1, pa.AddressLine2, pa.City, pa.StateProvinceID, pa.PostalCode FROM Sales.Store ss,Sales.CustomerAddress sca,Person.Address pa WHERE ss.CustomerID=sca.CustomerID AND sca.AddressID=pa.AddressID FOR XML RAW,ELEMENTS
Chapter6 Exercise2
The Store table is often queried. The queries are based on the CustomerID attribute and take long time to execute. Optimize the execution of the queries. In addition, ensure that the CustomerID attribute does not contain duplicate values.
【Solution】
【Solution】
select * into Store from Sales.Store create unique index IX_CID on Store(CustomerID) select CustomerID from Store
Chapter6 Exercise4
A view has been defined as followings:
CREATE VIEW vwSalesOrderDetail AS SELECT oh.SalesOrderID,TerritoryID,TotalDue,OrderQty,ProductID FROM Sales.SalesOrderHeader oh JOIN Sales.SalesOrderDetail od ON oh.SalesOrderId=od.SalesOrderID
The following update command gives an error when you update the OrderQty and TerritoryID attributes. UPDATE vwSalesOrderDetail SET OrderQty=2,TerritoryID=4 FROM vwSalesOrderDetail WHERE SalesOrderID=43659
【Solution】
Reason:
Because the data being modified through a single update statement is in two tables. Instead of a single UPDATE statement, you need to execute two UPDATE statements for each table.
UPDATE vwSalesOrderDetail SET TerritoryID=4 WHERE SalesOrderID=43659 UPDATE vwSalesOrderDetail SET OrderQty=2 WHERE SalesOrderID=43659
Chapter7 Exercise3
Create a function that returns the credit card number for a particular order.
CREATE FUNCTION Sales.fnCreditCard(@CardType nvarchar(50)) RETURNS table AS RETURN (SELECT CardNumber FROM Sales.CreditCard WHERE CardType=@CardType)
Invoke:
SELECT * FROM Sales.fnCreditCard('SuperiorCard')
【Solution】
CREATE FUNCTION Sales.DisplayCardNumber(@SalesOrderID INT)
RETURNS NVARCHAR(25)
AS
BEGIN
DECLARE @ret NVARCHAR(25)
SELECT @ret = CardNumber
FROM Sales.SalesOrderHeader S JOIN Sales.CreditCard C
ON S.CreditCardID = C.CreditCardID
WHERE SalesOrderID = @SalesOrderID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
Chapter7 Exercise4
Create a function that returns a table containing the customer ID and the name of the customers who are categorized as individual customers(CustomerType=’I’). The name of the customers will be based on the format of Shortname(only the last name) and Longname(Full name).
【Solution】
CREATE FUNCTION Sales.IndividualDetails(@format NVARCHAR(9))
RETURN @tbl_Individual Table
(CustomerID INT PRIMARY KEY,Name NVARCHAR(100))
AS BEGIN IF (@format = 'LONGNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,FirstName+''+LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName,FirstName
ELSE IF (@format = 'SHORTNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,LastName FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName
RETURN
END
Chapter8 Exercise2
The management of AdventureWorks, Inc. wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of the employee should be displayed. John, a database developer at AdventureWorks, has been asked to resolve this problem. Help John to find out appropriate solution.
Monthly salary=Rate*Pay Frequency*30
【Solution】
To accomplish this task, John needs to create an update trigger on the HumenResorces.EmployeePayHistory. To create the trigger, he needs to executes the following statements.
CREATE TRIGGER trgUpdateRate ON HumanResources.EmployeePayHistory FOR UPDATE
AS
IF UPDATE(Rate)
BEGIN
DECLARE @MonthlySalary float,@Ra float,@PF tinyint
SELECT @Ra=Rate,@PF=PayFrequency FROM Inserted
SELECT @MonthlySalary=@Ra*@PF*30 PRINT @MonthlySalary
END
UPDATE HumanResources.EmployeePayHistory SET Rate=20 WHERE EmployeeID=1