SQL LAB @ HOME 12 ~ NIIT POST

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 properties window is displayed

type the Name of the trace in the Trace name in the text box

select the save to file: check box

 clear the existing connection check box

click at run

and then click at YES button 

2.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.

SELECT * FROM sys.dm_exec_sessions


SELECT * FROM sys.dm_db_file_space_usage


SELECT * FROM sys.dm_tran_current_transaction


SELECT * FROM sys.dm_exec_connections

---

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.

SELECT * FROM sys.dm_io_backup_tapes
SELECT * FROM sys.dm_io_pending_io_requests



SELECT * FROM sys.dm_io_cluster_shared_drives
SELECT * FROM sys.dm_io_virtual_file_stats
(DB_ID (N'AdventureWorks2012'), NULL);

---

4.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.

SELECT * FROM sys.dm_os_buffer_descriptors
SELECT * FROM sys.dm_os_memory_pools



SELECT * FROM sys.dm_os_child_instances
SELECT * FROM sys.dm_os_sys_info



SELECT * FROM sys.dm_os_loaded_modules
SELECT * FROM sys.dm_os_tasks



SELECT * FROM sys.dm_os_memory_clerks
SELECT * FROM sys.dm_os_workers

---

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.

SET STATISTICS IO ON
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF

---

6. 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.

CREATE STATISTICS EmployeeInfo
ON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)


sp_helpstats 'HumanResources.Employee', 'ALL'

---

7.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.

UPDATE Person.Contact
SET Lastname = 'Smith'
WHERE ContactID = 2


SELECT * FROM cdc.Person_Contact_CT

---

8.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.

ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING



UPDATE Person.Contact
SET Title = 'Mr.'
WHERE ContactID = 4



SELECT * FROM CHANGETABLE
(CHANGES Person.Contact, 0) AS CT

Read More »

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 = PayFrequency FROM INSERTED
SELECT @rate * @frq * 30 AS 'Monthly salary'
END



UPDATE HumanResources.EmployeePayHistory
SET Rate = Rate + 5
WHERE EmployeeID = 160

---

3.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.

CREATE TRIGGER UpdatetriggerEPayHistory

ON HumanResources.EmployeePayHistory

FOR UPDATE

AS

IF UPDATE (Rate)

BEGIN

DECLARE @AvgRate FLOAT

SELECT @AvgRate = AVG(Rate)

FROM HumanResources.EmployeePayHistory

IF (@AvgRate > 20)

BEGIN

PRINT 'The average value of 

       rate cannot be more than 20'

    ROLLBACK TRANSACTION

END

END

---

4.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.

CREATE TRIGGER OrderStock
ON Production.Product
FOR UPDATE
AS
DECLARE @PID VARCHAR
SELECT @PID = ProductID FROM INSERTED
IF ( (SELECT SafetyStockLevel FROM INSERTED)
<= (SELECT ReorderPoint FROM INSERTED))
BEGIN
PRINT ' The stock of ' + @PID +
' is under the reorder point'
END

---

5.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.

CREATE TRIGGER Emp_Update
ON EmployeeDetails AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Time INT
SET @Time = DATENAME(HH, GETDATE())
IF @Time NOT BETWEEN 9 AND 17
BEGIN
ROLLBACK
PRINT ('The operation cannot be performed
before 9 A.M and after 5 P.M')
END
END

---

6.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.

CREATE TRIGGER Vendoracccountnum
ON Purchasing.vendor
FOR UPDATE AS
BEGIN
DELCARE @oldvalue NVARCHAR(15)
DECLARE @newvalue NVARCHAR(15)
SELECT @oldvalue = AccountNumber FROM DELETED
SELECT @newvalue = AccountNumber FROM INSERTED
PRINT 'the old account number is' +@oldvalue
PRINT 'the new account number is' +@newvalue
END

---

7.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.

CREATE TRIGGER Emp_Update_Trg
ON EmployeeDetails AFTER UPDATE
AS
BEGIN
DECLARE @OldSal MONEY
DECLARE @NewSal MONEY
SELECT @OldSal = Salary FROM DELETED
SELECT @NewSal = Salary FROM INSERTED
IF @OldSal > @NewSal
BEGIN
ROLLBACK
PRINT ('New salary cannot be less than the old salary')
END
END

---

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.

CREATE TRIGGER Emp_Nested_Trg
ON EmployeeDetails AFTER INSERT
AS
BEGIN
DECLARE @DptNo INT
SELECT @DptNo = DeptNo FROM INSERTED
IF NOT EXISTS (SELECT * FROM DeptDetails
WHERE DeptNo = @DptNo)
PRINT 'The specified DeptNo does not exist.'
ROLLBACK
END

---

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.

CREATE TRIGGER View_Insert_Trg
ON Emp_Dept
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO EmployeeDetails(EmployeeID, EmpName,
Designation, Salary, DeptNo)
SELECT EmployeeID, EmpName, Designation, Salary,
DeptNo FROM INSERTED
INSERT INTO DeptDetails(DeptNo, DeptName)
SELECT DeptNo, DeptName FROM INSERTED END

---

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.

CREATE TRIGGER View_Delete_Trg
ON Emp_Dept
INSTEAD OF DELETE
AS
BEGIN
DECLARE @DeptNo INT
DECLARE @Count INT
SELECT @DeptNo = DeptNo FROM DELETED
SELECT @Count = COUNT(*) FROM EmployeeDetails
WHERE DeptNo = @DeptNo
DELETE FROM EmployeeDetails WHERE DeptNo = @DeptNo
IF @Count = 1
DELETE FROM DeptDetails WHERE DeptNo = @DeptNo
END

Read More »

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

---

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

ANS.

CREATE PROC ProductList @name VARCHAR(50)
AS
BEGIN
PRINT 'Product details'
SELECT ProductID, ProductNumber, MakeFlag AS Availability
FROM Production.Product
WHERE Name = @name
END

EXECUTE ProductList 'Keyed Washer'

---

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

ANS.

CREATE FUNCTION Sales.DisplayCardNumber(@SalesOrderID INT)

RETURNS NVARCHAR(25)

AS 

BEGIN

DECLARE @ret NVARCHAR(25)

SELECT @ret = CardNumber

FROM Sales.SalesOrderHeader S JOIN Sales.CreditCard C

ON S.CreditCardID = C.CreditCardID

WHERE SalesOrderID = @SalesOrderID

IF (@ret IS NULL)

SET @ret = 0

RETURN @ret

END

---

5.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.

CREATE FUNCTION Sales.IndividualDetails(@format NVARCHAR(9))

RETURN @tbl_Individual Table

(CustomerID INT PRIMARY KEY,Name NVARCHAR(100))

AS BEGIN IF (@format = 'LONGNAME')

INSERT @tbl_Individual

SELECT Cu.CustomerID,FirstName+''+LastName

FROM Person.Contact AS C

JOIN Sales.Individual AS I

ON C.ContactID = I.ContactID JOIN Sales.Customer AS Cu

ON I.CustomerID = Cu.CustomerID

WHERE Cu.CustomerType = 'I'

ORDER BY LastName,FirstName

ELSE IF (@format = 'SHORTNAME')

INSERT @tbl_Individual

SELECT Cu.CustomerID,LastName FROM Person.Contact AS C

JOIN Sales.Individual AS I ON C.ContactID = I.ContactID

JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID

WHERE Cu.CustomerType = 'I'

ORDER BY LastName

RETURN

END

---

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.

DECLARE @MakeFlag BIT

SELECT @MakeFlag = MakeFlag FROM Production.Product

WHERE Name = 'Chaining Bolts'

IF @MakeFlag = 1

PRINT 'The Stock is available'

ELSE

PRINT 'The Stock is not available'

---

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.

CREATE PROCEDURE vx_DIV @Num1 INT, @Num2 INT

AS 

BEGIN

DECLARE @Div INT

    IF @Num1 < @Num2

RAISERROR ('You have entered your numbers

in the wrong way' ,16,1)

ELSE

SET @Div=@Num1/@Num2

PRINT @Div

END

---

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

ANS.

CREATE PROCEDURE prcGetCostDetail2 @ProductId INT,

@StandardCost MONEY OUTPUT

AS 

BEGIN

IF EXISTS (SELECT * FROM Production.ProductCostHistory

WHERE ProductID = @ProductId)

BEGIN

SELECT @StandardCost = StandardCost

FROM Production.ProductCostHistory

RETURN 0

END

ELSE

RETURN 1

END

Read More »

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 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:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)




3.   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:
CREATE NONCLUSTERED INDEX Idx_SalesOrderId_SalesOrderDetailId
ON Sales.SalesOrderDetail(SalesOrderId, SalesOrderDetailId)




4.  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:
UPDATE vwSalesOrderDetail
SET OrderQty = 2
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659


UPDATE vwSalesOrderDetail
SET TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659


5.  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:
CREATE FULLTEXT CATALOG CAT4 AS DEFAULT

CREATE FULLTEXT INDEX ON Sales.CreditCard(CardType)
KEY INDEX AK_CreditCard_CardNumber

SELECT * FROM Sales.CreditCard
WHERE CONTAINS (CardType, '"SuperiorCard"')


6.  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:
 CREATE FULLTEXT CATALOG CAT3 AS DEFAULT

CREATE FULLTEXT INDEX ON Sales.Store(Name)
KEY INDEX PK_Store_CustomerID

SELECT * FROM Sales.Store
WHERE FREETEXT (Name, 'Bike')


SELECT * FROM Sales.Store
WHERE CONTAINS (Name, '"Bike Store"')


7.   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:
SELECT * FROM Production.ProductDescription
WHERE CONTAINS (Description, 'road')


8.  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:
CREATE FULLTEXT INDEX ON Sales.Currency(Name)
KEY INDEX AK_Currency_Name


SELECT * FROM Sales.Currency
WHERE FreeText (Name, '"New" And "Dollar"')


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:
CREATE XML INDEX Sdx_Sales_Demographics_PATH
ON Sales.Store (Demographics)
USING XML INDEX PXML_Store_Demographics
FOR PATH


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:
SELECT Description, ProductDescriptionID
FROM Production.ProductDescription
WHERE CONTAINS (Description, ' "top" near "line"')


11.  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:
 SELECT * FROM Sales.Store WHERE
SalesPersonID = 227 AND CONTAINS (Name, 'Bike')


12.  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:
 CREATE NONCLUSTERED INDEX Idx_EmployeeId
ON HumanResources.EmployeeDepartmentHistory(EmployeeID)



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

ANS:
CREATE VIEW vsSales
AS
SELECT E.EmployeeId, D.GroupName
FROM HumanResources.Department D
INNER JOIN
HumanResources.EmployeeDepartmentHistory E
ON D.DepartmentID = E.DepartmentID


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

ANS:
CREATE VIEW vsSales
AS
SELECT P.Name, S.SalesOrderID, S.UnitPrice
FROM Production.Product P INNER JOIN
Sales.SalesOrderDetail S
ON P.ProductID = S.ProductID

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

ANS:
CREATE VIEW vwSale AS
SELECT H.CustomerID, H.SubTotal, H.TotalDue,
FROM Sales.SalesOrderDetail AS H
JOIN Sales.SalesOrderDetail AS D ON
H.SalesOrderID = D.SalesOrderID

Read More »

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 the records from the ProductBrand table in the NarrowFabrics database. Ensure that you do not delete the table.

ANS.

TRUNCATE TABLE ProductBranda

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.

USE PictureLibrary
GO
INSERT INTO OfficeLocation(Office_ID,
Office_Manager,Office_Location)
VALUES (1001, 'MAX',
GEOGRAPHY::PARSE('POINT(-83.0086 39.95954)'))

4.The production of a bicycle at AdventureWorks involves a number of phases. In each phase, the bicycle is moved to a different work center. The details of all the work centers are stored in the Production.ProductModel table. Bicycles of different types pass through different work centers, depending on the components that need to be fitted. The management wants a list of all the types of bicycles that go through work center 10. How will you generate this list?

ANS.

WITH
XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
ProductModelManuInstructions' AS pd)
SELECT ProductModelID
FROM Production.ProductModel WHERE
Instructions.exist('/pd:root/pd:Location[@LocationID=10]')=1 

5.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.

SELECT C.CustomerID, TerritoryID,
AccountNumber, CustomerType, AddressLine1, City,
StateProvinceID, PostalCode
FROM Sales.Customer C JOIN Sales.CustomerAddress CA
ON C.CustomerID = CA.CustomerID
JOIN Person.Address A
ON CA.AddressID = A.AddressID
FOR XML PATH('Customer') 

6.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.

USE AdventureWorks
GO

DELETE EmployeeDetails OUTPUT deleted.*
WHERE Designation='Clerk' 

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

ANS.

DELETE FROM HumanResources.EmployeeDepartmentHistory
WHERE DepartmentID = 5 

8.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.

SELECT JobCandidateID,
Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Name/ns:Name.First)[1]','nvarchar(20)') AS [First Name],
Resume.value('declare namespace
ns="http://schemas.microsft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:name/ns:Name.Last)[1]','nvarchar(20)') AS [Last Name],
Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Skills)[1]','nvarchar(max)') AS [Skills]
FROM HumanResources.JobCandidate 

9.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.

MERGE EmployeeDetails_Backup AS TARGET
USING EmployeeDetails AS SOURCE
ON (TARGET.EmployeeID = SOURCE.EmployeeID)
WHEN MATCHED AND
TARGET.Designation <> SOURCE.Designation
THEN UPDATE SET
TARGET.Designation = SOURCE.Designation
WHEN NOT MATCHED THEN
INSERT VALUES (SOURCE.EmployeeID,
SOURCE.EmpName,SOURCE.Designation,
SOURCE.Salary,SOURCE.DeptNo)
WHEN NOT MATCHED BY SOURCE THEN
DELETE; 

10.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.

WITH
XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/2004/07
/adventure-works/ProductModelManuInstructions'AS pd
)
UPDATE Production.ProductModel SET
Instructions.modify('delete(/pd:root/pd:Location)[1]')
WHERE ProductModelID = 7 

11.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.

SELECT * INTO Sales_od FROM Sales.SalesOrderDetail
WHERE UnitPrice > 2024.994 

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.

INSERT Person.Address(AddressLine1,City,StateProvinceID,
PostalCode) VALUES('2125 Yan Street','Bothell',79,'98011')

13.     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.

CREATE XML SCHEMA COLLECTION StudentInfo AS

'<schema xmlns="http://www.w3.org/2001/XMLSchema">

<element name="StudentName" type="string"/>

<element name="Address" type="string"/>

<element name="ContactNumber" type="int"/>

</schema>'

CREATE TABLE Student_details

(

StudentID INT,

StudentInfo XML(StudentInfo)

)

Read More »