SQL LAB at Home 8

 SQL LAB at Home 8

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

Truncate table ProductBrand

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

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

Use PictureLibrary
go
Insert into OfficeLocation(Office_ID,
Office_Manager,Office_Location)
Values(1001,'Max',Geography: : Parse('Point(-83.0086 39.95954)'))

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

With
XMLNamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
ProductModelManulnstructions'as pd)
select ProductModelID from Production.ProductModel
Where Instructions.exist('pd:root/pd:Location[@LocationID=10]')=1

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

Delete EmployeeDetails output
deleted.*Where Designation='Clerk'

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

select c.CustomerID,TerritoryID,AccountNumber,CustomerType,
 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')

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

select * from HumanResources.EmployeeDepartmentHistory
delete from HumanResources.EmployeeDepartmentHistory
where DepartmentID=3

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

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]','nverchar(20)')
as [first name], Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-work/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-work/Resume";
(/ns:Resume/ns:Skills[1]','nvarchar(max)')
as [Skills]from HumanResources.JobCandidate

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

Merge EmployeeDetail_Backup
as Target using EmployeeDetail
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?

With
XMLNamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-work/productModelManulnstructions'
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.

select * from Sales.SalesOrderDetail
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

Insert Person.Address (AddressLine1,City,StateProvinceID,
PostalCode) Values('2135 yan street',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.

create XML Schema Collection StudentInfo as
'<schema xmlns="http://www.w3.org/201/xmlschema">
<Element name ="StudentName"Type="string"/>
<Element name ="Address" type="string"/>
<Element name ="ContactNumber"type="int"/>
</schema>'