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=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
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.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
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.
No comments:
Post a Comment