1. Write a query to retrieve the details of the product locations where cost rate is greater than 12. In
addition, the locations need to be grouped into three groups, and then ranked based on the cost
rate in descending order. (Use the AdventureWorks database)
Ans.
Ans. https://drive.google.com/open?id=0B08RKBSts8D8UjFra04yd01FQjg
Ans.
2. Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar
unit price needs to be marked with an identical value. (Use the AdventureWorks database)
Ans.
3. Write a query to return the current year price and the previous year price of a product with the
Product ID, 715, over subsequent years. (Use the AdventureWorks database)
Ans.
4. The Recipient table and the Country table in the NarrowFabrics database do not have the same
data type for the CountryId attribute. The following tables show the sample structure of the two
tables.
Alter the Recipient or Country table so that they have the same data type for the CountryId
attribute.
Ans .
5. Delete the Recipient table from the NarrowFabrics database.
6. Consider the following table structures.
Refer the preceding table structures for the following requirements:
1. Create the Category table in the NarrowFabrics database. Enforce the following data integrity
rules while creating the table:
The CategoryId should be the primary key.
The Category attribute should be unique but not the primary key.
The Description attribute can allow storage of NULL values.
2. Create the ProductBrand table in the NarrowFabrics database. Enforce the following data
integrity rules while creating the table:
The BrandId should be the primary key.
The BrandName should be unique but not the primary key.
3. Create the NewProduct table with the following data integrity rules in the NarrowFabrics
database:
The ProductId should be the primary key.
The Qoh of the product should be between 0 and 200.
The Photo and ProductImgPath attributes can allow storage of NULL values.
The ProductName and ProductDescription attributes should not allow NULL values.
The values of the CategoryId attribute should be present in the Category table.
4. Modify the NewProduct table to enforce the following data integrity rule in the NarrowFabrics
database:
The values entered in the BrandId attribute should be present in the ProductBrand table.
7. The following statement was used to remove the Category table:
DELETE TABLE Category
The preceding statement displays an error and aborts. Identify the error and rectify it. (Use
the NarrowFabrics database)
8. Write the SQL statement to create a table named PictureDetails in the PictureLibrary database.
The following table shows the structure of the PictureDetails table.
9. Create a table named LeaveDetail in the AdventureWorks database. The following table shows
the structure of the LeaveDetail table.
10. The check constraint is applied on the Number_of_days column of the LeaveDetail table. This
check constraint ensures that the value inserted for the Number_of_days column is less than
three. However, the leave of four days has been approved for John based on some valid reasons.
The HR Manager wants to update the leave details for John but fails to assign the value, 4, in the
Number_of_days column. As a database developer, what will you do to perform the preceding
task? (Use the AdventureWorks database)
11. Write a query to display the structure of the Department table. (Use the AdventureWorks
database)
12. Write a query to display the details of all the salespersons by using synonyms. (Use the
AdventureWorks database)
13. Write a query to create a rule on the GroupName column of the Department table to accept only
three values: Research and Development, Sales and Marketing, and Manufacturing. (Use the
AdventureWorks database)
14. Write a statement to create a user-defined data type named quality. This data type should be
varchar(100) and should not contain NULL values.
15. AdventureWorks, Inc. has set up a new store. Insert the data into the database as shown in the
following table.
16. Insert the data shown in the following table into the ProductBrand table of the NarrowFabrics
database.
17. The address of a vendor, Comfort Road Bicycles, has changed. You need to update the data, as
shown in the following table, in the AdventureWorks database.
