SQL-Manipulating Data in Table

• Exercise2

Insert the data into the AdventureWorks database in demographical manner

Insert into Sales.Stores values(29484,'Car Store',283,
'<StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">
<AnnualSales>350000</AnnualSales>
<AnnualRevenue>35000</AnnualRevenue>
<BankName>International Bank</BankName>
<BusinessType>BM</BusinessType>
<YearOpened>1980</YearOpened>
<Speciality>Road</Speciality>
<SquareFeet>7500</SquareFeet>
<Brands>AW</Brands>
<Internet>T1</Internet>
<NumberEmployees>7</NumberEmployees>
</StoreSurvey>',DEFAULT,DEFAULT)

• Exercise 3

Update the VendorAddress in AdventureWorks database

Update Purchasing.VendorAddress
set AddressID=
(Select AddressID from Person.Address where AddressLine1 ='4151 Olivera'
AND City = 'Atlanta')
from Purchasing.VendorAddress va, Purchasing.Vendor v 
where va.VendorID = v.VendorID

AND v.Name = 'Comfort Road Bicycles'

• Exercise 4

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

truncate table ProductBrand

• Exercise 6

Insert records in OfficeLocation in PictureLibrary database

USE PictureLibrary
GO
Insert into OfficeLocation(Office_ID,
Office_Manager, Office_Location)
values (1001,'Max',Geography::Parse('POINT(-83.0086 39.95954)'))

• Exercise 7

Write a query to delete all the records whose designation is clerk

USE AdventureWorks
GO
delete EmployeeDetails Output
where Designation = 'Clerk'

• Exercise 8

The users of AdventureWorks need to publish the details of all the customers and their address on the organization website

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')

• Exercise 9

The management of AdventureWorks require a list containing skills of all the candidates who have applied for a vaccany

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.microsoft.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:Name/ns:Name.Skills)[1]','nvarchar(max)') AS [Skills]
FROM HumanResources.JobCandidate

• Exercise 10

The production of a bicycle at AdventureWorks involves a number of phases in xml format

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

• Exercise 11

There is a change in the production process of the bicycle with the product modelID 7

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

• Exercise 12

The details of the two employee designed as clerk have been removed from the EmployeeDetails table

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;

• Exercise 14

Write a query to copy the records of sales with unit price greater than $2024.949 in a  new tables Sales_od

Select * into Sales_od from Sales.SalesOrderDetails where UnitPrice > 2024.949

• Exercise 15

Insert Records with the following address in the Address table of the AdventureWorks database

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

• Exercise 16

You need to create a table named Student_Details to store the details of Student in the database...

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)

)