Xem mẫu
Instructor Inputs
Solutions to Exercises
This session includes exercises of Chapter 5 and Chapter 6.
Chapter 5
Exercise 1
Insert the following data in the ProductBrand table of the AdventureWorks database.
BrandID
B01
B02
B03
BrandName
Lee
Nike
Reebok
Solution
You need to execute the following insert statements to create the ProductBrand table and store three records in the ProductBrand table.
Create Table ProductBrand (
BrandID varchar(4), BrandName varchar(20) )
INSERT INTO ProductBrand VALUES (‘B01’, ‘Lee’) INSERT INTO ProductBrand VALUES (‘B02’, ‘Nike’) INSERT INTO ProductBrand VALUES (‘B03’, ‘Reebok’)
Exercise 2
AdventureWorks, Inc. has set up a new store. Insert the following data into the database:
Store Name – Car Store
Sales Person ID – 283
Demographics -
¤NIIT Instructor Inputs 9.3
350000 35000 International Bank BM 1980 Road 7500 AW T1 7
Tip
A store in AdventureWorks is treated like a customer. Therefore, you need to first create a record in the customer table by storing the territory id and specifying the customer type as `S`. Then you need to add the store details in the Store table.
Solution
Generate the customerId for the new store by storing the details in the Customer table as follows:
INSERT INTO Sales.Customer VALUES (7,`S`, DEFAULT, DEFAULT)
Store the details of the new store in the Store table as follows:
INSERT INTO Sales.Store VALUES(29484, `Car store`, 285,
`
350000 35000 International Bank BM 1980 Road 7500 AW T1 7
`, DEFAULT, DEFAULT)
9.4 Instructor Inputs ¤NIIT
Exercise 3
The address of a vendor, Comfort Road Bicycles, has changed. You need to update the following data in the AdventureWorks database.
Address
City
StateProvinceID
PostalCode
4151 Olivera
Atlanta
17
30308
Solution
To update the data, write the following statements in the Query Editor window of the Microsoft SQL Server Management Studio window:
Update Purchasing.VendorAddress set AddressID =
(Select AddressID from Person.Address Where AddressLine1 = `4151 Olivera`
AND City = `Atlanta`)
FROM Purchasing.VendorAddress va, Purchasing.Vendor v WHERE va.VendorID = v.VendorID
AND v.Name = `Comfort Road Bicycles`
Exercise 4
Delete all the records from the ProductBrand table. Ensure that you do not delete the table.
Solution
To delete all the records, write the following statements in the Query Editor window of the Microsoft SQL Server Management Studio window:
Truncate table ProductBrand
¤NIIT Instructor Inputs 9.5
...
- tailieumienphi.vn
nguon tai.lieu . vn