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)

)