sql lab @ home 12
1. You are a database administrator at AdventureWorks, Inc. You have been notified by the users
that the database works very slowly during peak business hours. You have decided to monitor the
T-SQL statements to identify the statements that are taking time to execute. How will you monitor the server
ANS.
open sql server profiler
connect to the server dialog box is displayed
click at New Trace
connect to the sql server
the trace...
Wednesday, 26 July 2017
SQL LAB @ HOME 11 ~ NIIT POST
sql lab @ home11
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.
CREATE TRIGGER updTrigger
ON HumanResources.EmployeePayHistory FOR UPDATE
AS
BEGIN
DECLARE @rate AS MONEY
DECLARE @frq AS INT
SELECT @rate = Rate,
@frq...
SQL LAB @ HOME 10 ~ NIIT POST
sql lab @ home 10
1.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.
DECLARE @avg_rate INT
SELECT @avg_rate = AVG(rate)
FROM HumanResources.EmployeePayHistory
SELECT * FROM HumanResources.EmployeePayHistory
WHERE Rate < @avg_rate
GO
2.Create a function that returns the shipment date of a particular order. (Use...
SQL LAB @ HOME 9 ~ NIIT POST
sql lab @ home 9
1. 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:
CREATE UNIQUE NONCLUSTERED INDEX Idx_CustomerID
ON Sales.Store(CustomerID)
2. The SalesOrderDetail and SalesOrderHeader tables store the details of...
SQL LAB @ HOME 8 ~ NIIT POST
sql 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:
CREATE TABLE PictureDetails(EventPicID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,EventName VARCHAR(30) NOT NULL,PIC VARBINARY(MAX) NOT NULL)
2.Delete all...
Subscribe to:
Posts (Atom)