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

RDBMS Lab@Home 5

1. Write a query to display the sales person ID, territory ID, and territory name of all the sales persons in the following format. (Use the AdventureWorks database)

Ans.

2. Write a query to display the sales person ID of all the sales persons and the name of the territory to which they belong. (Use the AdventureWorks database)

Ans.  

3.Write a query to display the sales person ID and territory names for all the sales persons. If a sales person does not belong to any territory, NULL should be displayed as shown in the following format. (Use the AdventureWorks database)

Ans, 

Ans. 

4. Write a query to display the sales order ID, the product ID, and the order date for all the products in the following format. (Use the AdventureWorks database).

Ans. 

5.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. (Use the AdventureWorks database)

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

6. Write a query to display the sales order ID, territory name, month, and year of all the sales orders in the following format. (Use the AdventureWorks database)

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

7. Write a query to display the total amount due for 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. (Use the AdventureWorks database)

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

8.  Write a query to display all the country region codes along with their corresponding territory IDs. (Use the AdventureWorks database)

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

9. Write a query to display the total amount due for all the orders in the AdventureWorks database in the following format:

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

10 Write a query to display the order date along with the sales order ID and the territory name. The order date should be displayed in the dd/mm/yyyy format. (Use the AdventureWorks database)

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

11. Write a query to display the sales order ID and the territory name of the orders where the month of the order is May and the year is 2004. (Use the AdventureWorks database)

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

12. Write a query to display the contact ID of the customers that have the 'Vista' credit card. (Use the AdventureWorks database)

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

13. Write a query to display the sales order IDs of the orders received from the Northeast territory. (Use the AdventureWorks database)

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

14.  Create 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 orders. (Use the AdventureWorks database)

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

15. Write a query to display the sales order IDs of the orders that have been paid through a SuperiorCard. (Use the AdventureWorks database)

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

16.  Write a query to display the sales 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. (Use the AdventureWorks database)

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

17. Write a query to display the details of those orders for which no discount was offered. (Use the AdventureWorks database)

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

18. Write a query to display the sales order IDs and the credit card IDs of those cards which are expiring in the year 2007. (Use the AdventureWorks database)

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

19. Write a query to display the average rate of the Australian Dollar, where the CurrencyRateDate is July 1, 2004. (Use the AdventureWorks database)

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

RDBMS Lab@Home 4

1. Display EmployeeID and HireDate of the employees from the Employee table. The month and the year need to be displayed. (Use the AdventureWorks database)

Ans.

2. Consider the following
SQL query: SELECT ProductID, LineTotal AS 'Total' FROM Sales.SalesOrderDetail Group By

Cube(LineTotal) Once executed, the preceding query generates errors. Identify the possible causes of such errors and rectify the same. (Use the AdventureWorks database)

Ans.

3. Display the details of all orders in the following format. (Use the AdventureWorks database)

Ans. 

4. Write a query to display the full name of a person in a column named Person Name. (Use the AdventureWorks database)

Ans

.

5. Display the customer ID, name, and sales person ID for all the stores. According to the requirement, only first 15 letters of the customer name should be displayed. (Use the AdventureWorks database)

Ans.

6.Display a report containing the sales order ID and the average value of the total amount greater than $ 5,000 in the following format. (Use the AdventureWorks database)

Ans.

7.Display the Order ID of the top five orders based on the total amount due in the year 2001. (Use the AdventureWorks database)

Ans.

8. Display the total value of all the orders put together. (Use the AdventureWorks database)

Ans.

9.What will be the output of the following code written to display the total order value for each order? (Use the AdventureWorks database) SELECT SalesOrderID,ProductID,sum(LineTotal) FROM Sales.SalesOrderDetail GROUP BY SalesOrderID

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

10.  Display the maximum, minimum, and the average rate of sales orders. (Use the AdventureWorks database)

Ans.

11.  Display the maximum, minimum, and the average rate of sales orders. (Use the AdventureWorks database)

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

12.Consider the following SQL query containing the ROLLUP operator: 

SELECT ProductID, LineTotal AS 'Total' FROM Sales.SalesOrderDetail GROUP BY ROLLUP (ProductID)

The preceding query generates errors during execution. Identify the possible causes of such errors and rectify? (Use the AdventureWorks database)

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

13.Display the sales order ID and the maximum and minimum values of the order based on the sales order ID. In addition, ensure that the order amount is greater than $ 5,000. (Use the AdventureWorks database)

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

14. Display a report containing the product ID and the total cost of products for the product ID whose total cost is more than $ 10,000. (Use the AdventureWorks database)

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

15. Display the sum of sales amount earned by each sales person and the sum of sales amount earned by the all the salespersons. (Use the AdventureWorks database)

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

16. Display the total unit price and the total amount collected after selling the products, 774 and 777. In addition, calculate the total amount collected from these two products. (Use the AdventureWorks database)

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

17. Write a query to retrieve the list price of the products where the product price is between $ 360.00 and $ 499.00 and display the price in the following format:

 The list price of "Product Name" is "Price". (Use the AdventureWorks database)

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