【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 【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

【SolutionSELECT 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.

【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.

【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.

【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