RDBMS Lab@Home 12

1. You are a database administrator at AdventureWorks, Inc. As a part of regular maintenance, you need to check the following details:
Current sessions
File space usage
Current transactions
Current connections
How will you perform this task?

Ans.

2.  You are a database administrator at AdventureWorks, Inc. You want to check the information about the operating system resources that are specific to SQL Server. How will you do this?

Ans. 

3. You are a database administrator at AdventureWorks, Inc. The following statements are executed to check the state of I/O operations: 

Select * sys.dm_db_backup_tapes 

Select * from sys.dm_db_pending_io_requests 

Select * from sys.dm_db_cluster_shared_drives 

Select * from sys.dm_db_virtual_files_stats

 However, the preceding statements generated an error. Identify the error and provide the solution.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8UUFnZlN3d291Mmc

4. You are a database administrator at AdventureWorks, Inc. and want to generate an effective execution plan on the Employee table. For this, create a statistics on the Employee table. In addition, show the list of all the statistics maintained on the Employee table?

Ans. https://drive.google.com/open?id=0B08RKBSts8D8YlR1Y1FoOUR6UVk

5.You are a database administrator at AdventureWorks, Inc. You want to get the information regarding the amount of disk activity generated for the Employee table. How will you perform this task?

Ans. https://drive.google.com/open?id=0B08RKBSts8D8WGp6bU81dGlrOFk

6. An employee having contact ID as 2 got married and her last name has been changed to Smith. You have to make the required changes in the Contact table and track these changes by using the CDC method. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8U0I0U0s2VzljS00

7. An employee having contact ID as 4 has changed his title from (Sr.) to (Mr.). You need to update its record into Contact table and track these changes by using the track changing method. (Use the AdventureWorks database)

Ans.https://drive.google.com/open?id=0B08RKBSts8D8TXh5TGQtRkg5UDQ

RDBMS Lab@Home 11

1. The management of AdventureWorks Incorporation wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of that employee should be displayed. John, the Database Developer at AdventureWorks, has been asked to resolve this problem. Help John to find an appropriate solution.

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8WlpaVjVURUJxYms

2.The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?

Ans. https://drive.google.com/open?id=0B08RKBSts8D8bHYwQUw3UnYyTEU


3. Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8bzV4c2x2TFkzWTQ

4. Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8eEdLVllldVJIZnc

5. The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8Snh1QUpPUFNBWTA

6. Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8TGRfaWVIWWhPUG8

7.Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8V3dDNnlxU3YxZ3c

8. Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8eDlYN1gyXzllNXc

9. Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8eWdkQVJDNTZfTEU

10. Consider the following statement:
 CREATE VIEW Emp_Dept
 AS
 SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName FROM EmployeeDetails E INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8VU91S0pBYlJzUUE

11. John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8V205SFAtdmdCMmc

RDBMS Lab@Home 10

1. Create a function that returns the shipment date of a particular order. (Use the AdventureWorks database)

Ans.

2. Create a batch that finds the average pay rate of the employees and then lists the details of the employees who have a pay rate less than the average pay rate. (Use the AdventureWorks database)

Ans. 

3. Create a function that returns the credit card number for a particular order. (Use the AdventureWorks database)

Ans. 

4. Create a stored procedure that accepts the name of a product and display its ID, number, and availability. (Use the AdventureWorks database)

Ans.

5. Create a user-defined function that accepts the account number of a customer and returns the customer's name from the Depositor table. Further, ensure that after creating the function, user is not able to alter or drop the Depositor table. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8RVlkR2N4cVpRZGc

6. Create a function that returns a table containing the ID and the name of the customers who are categorized as individual customers (CustomerType = 'I'). The function should take one parameter. The parameter value can be either Shortname or Longname. If the parameter value is Shortname, only the last name of the customer will be retrieved. If the parameter value is Longname, then the full name will be retrieved. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8b0s5Q2lCZnltNVk

7. Create a batch to check the availability of the product, Chaining Bolts, in the stock. If the product is available, display a message, 'The Stock is available'. If the product is not available, display a message, 'The Stock is not available'. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8dWNNa0dZNjQyNWc

8.Create a stored procedure that accepts two numbers, num1 and num2, and displays the result after dividing these two numbers. In addition, num1 should always be greater than num2. If num1 is less than num2, generate a user-defined error message, 'You have entered your numbers in the wrong way'.

Ans, https://drive.google.com/open?id=0B08RKBSts8D8TmdyQzFFaVVwRTA

9. Create a stored procedure that returns the standard cost of a given product. (Use the AdventureWorks database)

Ans. 

RDBMS Lab@Home 9

1. The SalesOrderDetail and SalesOrderHeader tables store the details of the sales orders.
 You have created a backup of the SalesOrderDetail and SalesOrderHeader tables in the SalesOrderDetail_Backup and SalesOrderHeader_Backup tables. 
To generate a report displaying the sales order ID and the total amount of all the products purchased against an order, you are using the following query: 
SELECT sd.SalesOrderID, SUM(LineTotal) AS [Total Amount] FROM Sales.SalesOrderDetail_Backup sd JOIN Sales.SalesOrderHeader_Backup sh ON sd.SalesOrderID = sh.SalesOrderID GROUP BY sd.SalesOrderID The table contains a large amount of data.
 Suggest a solution to optimize the execution of this query. (Use the AdventureWorks database)

Ans. 

2. The Store table is often queried. The queries are based on the CustomerID attribute and take a long time to execute. Optimize the execution of the queries. In addition, ensure that the CustomerID attribute does not contain duplicate values. (Use the AdventureWorks database)

Ans. 

3. A view has been defined as shown in the following statement: 

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 statement gives an error when you update the OrderQty and TerritoryID attributes: 

UPDATE vwSalesOrderDetail SET OrderQty = 2, TerritoryID = 4 FROM vwSalesOrderDetail WHERE SalesOrderID = 43659

 Identify the problem and provide the solution. (Use the AdventureWorks database)

Ans. 

4.The SalesOrderDetail table is often queried. The queries are based on the SalesOrderDetailID and SalesOrderID attributes. The execution of the queries takes a long time. Optimize the execution of the queries. In addition, check the performance of the query before and after optimizing the execution of the queries. (Use the AdventureWorks database)

Ans. 

5. The Store table contains the details of all the stores. The HR Manager of AdventureWorks, Inc. frequently queries the Store table based on the names of the stores. He wants to create the following reports:

 A report containing the details of all the stores that contain the word 'bike' in their names. A report displaying the names of all the stores containing the phrase 'Bike Store'.

 Write the query so that the result set is retrieved quickly. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8NENxa3dsYnhXcTg

6. Display the details of all the credit cards that are of type SuperiorCard. The CreditCard table contains a large amount of data. Therefore, the query takes a long time to retrieve the details of the credit card. You need to optimize the execution of the query so that the result set does not take too much time to be retrieved. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8dEVlQmpwS0NtN1E

7. Display the details of all the currencies that contain the words New and Dollar in their names. These words can be included in any order. In addition, you need to make sure that the query does not take too much time to execute. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8T3RIbzUxVWF3QWs

8. The manager of the production department wants to analyze the products that contain the exact word 'road' in their description. Write a query so that the result set does not take a long time to execute. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8T2REdkxDNHpkcHc

9. The Store table is often queried. The queries are based on the Demographics attribute and take a long time to execute. Optimize the execution of these queries. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8YmlzVlh6UHRNSmc

10.  You need to create a report displaying the details of all the products that contain the word 'top' near the word 'line' in their description. Write a query to retrieve the desired output. Write the query such that it does not take a long time to execute. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8UzV2cVM2NUZrWG8

11. The DepartmentHistory table of employees is often queried. The queries are based on the EmployeeID attribute and take a long time to execute. Optimize the execution of these queries. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8ZWxFSXhUTUxwQjA

12. Display the details of all the stores having the word 'bike' in their name. In addition, ensure that the report contains the details of those stores that have the sales person ID as 277. You need to write a query so that the result set does not take a long time to be retrieved. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8YTBwd2ZNLWJmVnM

13.  Create a view to retrieve the employee IDs along with the corresponding Group Name. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8YTUxY3k5MGJ3RTg

14. Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding Product Name. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8SEtPTXZoanB0TDg

15. Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue of the orders placed. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8RFJ6UXV2ZmdSSmM

RDBMS Lab@Home 8

1. Write an SQL statement to insert a record into the PictureDetails table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the PictureDetails table.

Ans.

2. Delete all the records from the ProductBrand table in the NarrowFabrics database. Ensure that you do not delete the table.

Ans. 

3. Write an SQL statement to insert a record into the OfficeLocation table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the OfficeLocation table.

Ans. 

4.The users of AdventureWorks need to publish the details of all the customers and their address on the organization's website. To perform this task, you need to retrieve the data in the XML format.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8ZUFDR3FrOUI5amc

5. The EmployeeDetails table contains the records of the employees. Write a query to delete the records of those employees who are designated as clerk. In addition, ensure that all the deleted rows are displayed after the execution of the query. (Use the AdventureWorks database)

Ans.https://drive.google.com/open?id=0B08RKBSts8D8azJXUEZiTjl6aFE

6. The management of AdventureWorks requires a list containing the skills of all the candidates who have applied for a vacancy. The details of all the candidates are stored in the XML format in the HumanResources.JobCandidate table. How will you display the list?

Ans. https://drive.google.com/open?id=0B08RKBSts8D8cV9DaTZyWERFLWc

7. Delete all the records from the Employee table in the AdventureWorks database where the department ID is 5.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8NEswTHhKSzVwbDA

8. The details of the two employees designated as clerk have been removed from the EmployeeDetails table. Write the SQL statement to reflect these changes made in the EmployeeDetails_Backup table. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8dXdMVGFtSkp2WHM

9. There is a change in the production process of the bicycle with the product model ID 7. Due to this change, the bicycle will not be going to work center 10. You need to update this change in the database. How will you perform this task? (Use the AdventureWorks database)

Ans.https://drive.google.com/open?id=0B08RKBSts8D8WnpXWmttVDA5ZWc

10.Write a query to copy the records of sales with unit price greater than $ 2024.994 in a new table named Sales_od. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8dEt6dlQxZnBKa1U

11. You need to create a table named Student_Details to store the details of students in the database. The structure is shown in the following table.

Ans.https://drive.google.com/open?id=0B08RKBSts8D8TlVzVlAtdWVfRDA

12. Insert a record with the following address in the Address table of the AdventureWorks database: 2125 Yan Street, Bothell-79, Postal Code-98011

Ans. https://drive.google.com/open?id=0B08RKBSts8D8QVBtM3g5eGh0R28

RDBMS Lab@Home 7

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.

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.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8enZuSWlJX2dtVTQ

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=0B08RKBSts8D8UjFra04yd01FQjg

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)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8Ym53RFlXaVpIQ2c

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.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8bjZJR0cyUUlFTW8

9. Create a table named LeaveDetail in the AdventureWorks database. The following table shows the structure of the LeaveDetail table.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8SXNTS05ieHdRalk

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)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8cF9BT2tEZ2NnWUU

11. Write a query to display the structure of the Department table. (Use the AdventureWorks database)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8NE5YeUxnTmZDTEE

12. Write a query to display the details of all the salespersons by using synonyms. (Use the AdventureWorks database)

Ans. https://drive.google.com/open?id=0B08RKBSts8D8eUh2bDRsdjlQTmM

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)

Ans.  https://drive.google.com/open?id=0B08RKBSts8D8Q3RWdm5OUnV5anc

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.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8cEtUU3JzMmVhbFU

15. AdventureWorks, Inc. has set up a new store. Insert the data into the database as shown in the following table.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8OUJJcHJmRWdqLUU

16. Insert the data shown in the following table into the ProductBrand table of the NarrowFabrics database.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8WjUxcXdOQzNYd1E

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.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8Y2tFV0N5NnN3UDg

RDBMS Lab@home 6

1. Write a query to display the credit card number of Catherine Abel. (Use the AdventureWorks database)

Ans.

2.Write a query to display EmployeeID of those employees who belong to the department, Tool Design. (Use the AdventureWorks database)

Ans.

3.Write a query to display EmployeeID of those employees who belong to the department, Tool Design. (Use the AdventureWorks database)

Ans.

4. Write a query to display the first name and the last name of the customers along with their credit card identification number. (Use the AdventureWorks database)

Ans. 

5. Write a query to display CreditCardID, CardNumber, and ExpYear of the credit cards that do not expire in the year 2005 and 2007. (Use the AdventureWorks database)

Ans.

6. Write a query to display the sales person ID and the total number of sales orders processed by each sales person. In addition, display the commision earned by each sales person. Commision is given to each sales person at the rate of $ 10 per order. Exclude the orders where sales person ID is null. The details of the sales person who has got the maximum commision should be displayed first in the result set. (Use the AdventureWorks database)

Ans.https://drive.google.com/open?id=0B08RKBSts8D8TjdwMlh5V1dKdVE

7. Create a table named Recipient in the NarrowFabrics database to store the details of the recipients to whom the orders are dispatched. The following table provides the structure of the Recipient table.

Ans.https://drive.google.com/open?id=0B08RKBSts8D8OGwxS1BTa3VGaWs

8. NarrowFabrics, Inc. is a leading cloth manufacturing organization that manufactures and delivers clothes to the distributors. Being a database developer at NarrowFabrics, you need to create a database that will store the various database objects, such as tables, views, and indexes, used by the organization. Write the SQL statements that you can use to create a database named NarrowFabrics.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8V3ZRZEpmUDlOZWc

9. Create a table named Country in the NarrowFabrics database to store the country details. The following table provides the structure of the Country table.

Ans.https://drive.google.com/open?id=0B08RKBSts8D8U3ZLcUpNMUkwcDQ

10. LiveChain, Inc. is a leading media company that organizes events and provides photography services. After each event, the company stores the pictures taken in the event and delivers the pictures to the client on request. As a database developer, you have been assigned the task to create a database named PictureLibrary that the company can use to store the pictures.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8M19HTUJyTF9GU0U

11. John is working with Client Network Ltd as the Database Administrator. He wants to create a database to store the details of all the employees working in the organization. He needs to ensure that while saving, the data is placed in multiple file groups so that he is able to effectively manage the backup and restore operations. What should John do to perform this task?

Ans. https://drive.google.com/open?id=0B08RKBSts8D8VnpLQzNKc0xsbHc

12. You need to store the details of the offices in the central database, PictureLibrary. Write the SQL statement to create the OfficeLocation table in the PictureLibrary database. The following table shows the structure of the OfficeLocation table.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8VHA5bjJXXzJrS1E

13. Write a query to make EmpDetail_FG as the default filegroup for the EmpDetail database.

Ans. https://drive.google.com/open?id=0B08RKBSts8D8LWt4M19QelN5clk