SQL LAB at Home 7.
01. Write a query to retrieve the details of the product locations where cost rate is greater than 12. In addition, the locations need to be grouped into three groups, and then ranked based on the cost rate in descending order.
select LocationID, name,CostRate,
Availability, ntile(3) over(order by CostRate desc)
as
Rank from Production.Location
Where CostRate>=12
Availability, ntile(3) over(order by CostRate desc)
as
Rank from Production.Location
Where CostRate>=12
02. Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar unit price needs to be marked with an identical value.
select salesOrderID,OrderQty,UnitPrice,Dense_Rank()
Over(Order by UnitPrice ASC)
as
Rank from sales.salesOrderDetail
Over(Order by UnitPrice ASC)
as
Rank from sales.salesOrderDetail
03. Write a query to return the current year price and the previous year price of a product with the Product ID, 715, over subsequent years.
select ProductID, Year(StarDate)
as
salesYear, ListPrice as CurrentPrice, LAG(ListPrice,1,0)
Over(Order by year(startDate))
as PreviousPrice from Production.ProductListPriceHistory
where ProductID=715
as
salesYear, ListPrice as CurrentPrice, LAG(ListPrice,1,0)
Over(Order by year(startDate))
as PreviousPrice from Production.ProductListPriceHistory
where ProductID=715
Alter Table Country
Alter Column Country char(2)
05. Delete the Recipient table from the NarrowFabrics database.
drop table Employee
06. The following statement was used to remove the Category table: DELETE TABLE Category The preceding statement displays an error and aborts. Identify the error and rectify it.
Use NarrowFabrice
go
Drop table NewProduct
Drop table Category
go
Drop table NewProduct
Drop table Category
07. Consider the following table structures.
Refer the preceding table structures for the following requirements:
1. Create the Category table in the NarrowFabrics database. Enforce the following data integrity
rules while creating the table:
The CategoryId should be the primary key.
The Category attribute should be unique but not the primary key.
The Description attribute can allow storage of NULL values.
2. Create the ProductBrand table in the NarrowFabrics database. Enforce the following data
integrity rules while creating the table:
The BrandId should be the primary key.
The BrandName should be unique but not the primary key.
3. Create the NewProduct table with the following data integrity rules in the NarrowFabrics
database:
The ProductId should be the primary key.
The Qoh of the product should be between 0 and 200.
The Photo and ProductImgPath attributes can allow storage of NULL values.
The ProductName and ProductDescription attributes should not allow NULL values.
The values of the CategoryId attribute should be present in the Category table.
4. Modify the NewProduct table to enforce the following data integrity rule in the NarrowFabrics
database:
The values entered in the BrandId attribute should be present in the ProductBrand table.
create table category
(
categoryID char(3) constraint pkategoryID primary key,
category char(20) constraint unqCategory unique,
Description varchar(100)Null
)
create table ProductBrand
(
BrandID char (3) constraint pkBrandID primary key,
BrandName char(20) constraint unqBrandName Unique,
)
create table New Product
(
ProductID char(6)Constraint pkProductID primary key,
ProductName varchar(20) not null,
ProductDescription varchar(200) not null,
categoryID char(3)constraint fkCategoryID
foreign key references Category(categoryID),
ProductRate money,
BrandID char(3),
photo image Null,
Qoh smallint constraint chkQoh check (Qoh Between 0 and 200),
productImgPath vatchar(40)Null
)
create table PictureDetail
(
EventPicID Uniqueidentifier Rowguidcol Not Null
Event Name varchar(30)not null,
Pic carbinary(Max) not null,
)
09. Create a table named LeaveDetail in the AdventureWorks database. The following table shows the structure of the LeaveDetail table.
create table LeaveDetail
(
Name varchar(50)not null,
Reason varchar(100)not null,
Number_of_days int constraint chknoday
check(Number_of_days <3)
)
10. Write a query to display the structure of the Department table.
sp_Help 'HumanResources.department'
Alter table LeaveDetail
Nocheck constraint chknoday
Insert into LeaveDetail valus('Tom','fever',4)
Alter table LeaveDetail
check constraint chknoday
Nocheck constraint chknoday
Insert into LeaveDetail valus('Tom','fever',4)
Alter table LeaveDetail
check constraint chknoday
12. Write a query to display the details of all the salespersons by using synonyms.
create Synonym spersonDetail
for Adventureworks.sales.salesperson
selrct * from spersonDetail
for Adventureworks.sales.salesperson
selrct * from spersonDetail
13. Write a query to create a rule on the GroupName column of the Department table to accept only three values: Research and Development, Sales and Marketing, and Manufacturing.
create Rule groupType
as
@gType in ('Research and Development',
'sales and Marketing',
'Manufacturing')
go
sp_bindrule 'groupType', 'HumanResorces.Department.
GroupName'
as
@gType in ('Research and Development',
'sales and Marketing',
'Manufacturing')
go
sp_bindrule 'groupType', 'HumanResorces.Department.
GroupName'
14. Write a statement to create a user-defined data type named quality. This data type should be varchar(100) and should not contain NULL values.
create type quality
from varchar(50) not null;
from varchar(50) not null;
15. AdventureWorks, Inc. has set up a new store. Insert the data into the database as shown in the following table.
Insert into sales.Customervalus(7,'s',Default, Default)
Insert into sales.store valus(29484,'car store',285,
'<StoreSurvey XMLnas="htty://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><Specialty>Road</Specialty>
<SquareFeet>7500</SquareFeet><Brands>AW</Brands><Internet>T1</Internet>
<NumberEmployees>7</NumberEmployees></storeSurvey>',Default,Default)
16. Insert the data shown in the following table into the ProductBrand table of the NarrowFabrics database.
Inser into ProductBrand valus('B01','Lee')
Inser into ProductBrand valus('B02','Wike')
Inser into ProductBrand valus('B03','Reebok')
17. The address of a vendor, Comfort Road Bicycles, has changed. You need to update the data, as shown in the following table, in the AdventureWorks database.
Update purchasing.VendorAddress
set AddressID=(select Address from Person,Address where AddressLinel='4151 Olivera'
and City='Atlanta')
from Purchasing.VendorAddress va,Purchasing.Vendor v
where va.VendorID=v.VendorID and
v.Name='Confort Road Bicycles'
