SQL LAB at Home 6

SQL LAB at Home 6

01. Write a query to display EmployeeID of those employees who belong to the department, Tool Design.

select EmployeeID from HumanResources.EmployeeDepartmetHistory
where DepartmantID in (select DepartmentID from HumanResources.Department
where name ='Tool Design')





02. Write a query to display the credit card number of Catherine Abel.

select CaedNumber from sales.creditCard
where creditCardID=(select creditCardID from sales.contactCreditCard where contactID=
(select contactID from Person.Contact where FirstName='Catherine' and LastName='Abe1'))





03. Write a query to display the sales order IDs and the order detail IDs along with the total value of those orders that have a total value greater than the average of the total value for the order ID.

select salesOrderDetailID,salesOrderID,LineTotal from sales.salesOrderdetail s1
where s1.lineTotal > (select AVG(s2.Linetotal) from
sales.salesOrderDetail s2
where s1.SalesOrderID=s2.salesOrderID)





04. Write a query to display the first name and the last name of the customers along with their credit card identification number.

select firstName, Lastname, B.CreditCardID
from Person.contact A Cross apply
(select * from sales.contactcreditcard B Where B.contactID=A,contactID)B





05. Write a query to display CreditCardID, CardNumber, and ExpYear of the credit cards that do not expire in the year 2005 and 2007.

select creditCardID, cardnumber, ExpYear
from sales.creditcard Expcept (select creditcardID,cardNumber,Expyear
from sales.creditcard
where Expyear=2005 or Expyear=2007)





06. Write a query to display the sales person ID and the total number of sales orders processed by each sales person. In addition, display the commision earned by each sales person. Commision is given to each sales person at the rate of $ 10 per order. Exclude the orders where sales person ID is null. The details of the sales person who has got the maximum commision should be displayed first in the result set.

With sales_Cte(salesPersonID, totalsalesOrder)
as
(
select salesPersonID, Count(salesorderID)
from sales.salesOrderHeader
where salesPersonID is not Null
Group by salesPersonID
)
selectr salespersonID,TotalSalesOrder,Commission= TotalSalesOrder*10
from sales_Cte
OrderBy TotalsalesOrder desc

Http://sonugiri.blogspot.in


07. Create a table named Recipient in the NarrowFabrics database to store the details of the recipients to whom the orders are dispatched. The following table provides the structure of the Recipient table.
















create table Recipient
(
Ordernumber char(6),
FirstName varchar(20),
lastname varchar(20),
Address varchar(50),
City char(15),
state char(15),
CountryCode char(3),
ZipCode char(10),
phone char(15)
)





08. NarrowFabrics, Inc. is a leading cloth manufacturing organization that manufactures and delivers clothes to the distributors. Being a database developer at NarrowFabrics, you need to create a database that will store the various database objects, such as tables, views, and indexes, used by the organization. Write the SQL statements that you can use to create a database named NarrowFabrics.

create Database NarrowFabrics





09. LiveChain, Inc. is a leading media company that organizes events and provides photography services. After each event, the company stores the pictures taken in the event and delivers the pictures to the client on request. As a database developer, you have been assigned the task to create a database named PictureLibrary that the company can use to store the pictures.

create Database PictureLibrary
on
Primary (Name =PictureLibrary,FileName= 'c:\Data\PictureLibrary.mdf'),
FileGroup fileStreamGroup Contains fileStram(name = videoLibrary_Data,
fileName= 'c:\Data\pictureLibrary_Data')
Log on (Name=Log1,
fileName='C:\data\pictureLibrary.ldf')





10. Create a table named Country in the NarrowFabrics database to store the country details. The following table provides the structure of the Country table.








Create table Conutry
(
CountryID varchar(2),
Country char(25)
)





11. You need to store the details of the offices in the central database, PictureLibrary. Write the SQL statement to create the OfficeLocation table in the PictureLibrary database. The following table shows the structure of the OfficeLocation table.










Use PictureLibrary
Create table OfficeLocation
(
Office_ID int not Null,
Office_Manger varchar(30) not Null,
Office_Lication Geography not Null
)





12. John is working with Client Network Ltd as the Database Administrator. He wants to create a database to store the details of all the employees working in the organization. He needs to ensure that while saving, the data is placed in multiple file groups so that he is able to effectively manage the backup and restore operations. What should John do to perform this task?

Create table Empdetail
on Primary
(name ='EmpDetail_Primary',
fileName='D:\Data\EmpDetail_Prm.mdf',
Size=5MB,
Maxsize= 10MB,
fileGrowth =1MB),
Filegroup Empdetail_Fg

(Name ='EmpDetail_Dat1',
file= 'D:\Data\EmpDetail_1.ndf',
size =5MB, Maxsize=10MB,
FileGrowth=1MB),
(Name = 'EmpDetail_Dat2',
fileName = 'D:\Data\EmpDetail_2.ndf',
Size =5MB,
Maxsize=10MB,
FileGrowth =1MB)
Log on
(Name ='EmpDetail_Log',
FileName='D:\Data\EmpDetaiL_ldf',
Size=5MB, Maxsize=10MB,
FileGrowth=1MB)
Go







13. Write a query to make EmpDetail_FG as the default filegroup for the EmpDetail database.

Alte Database EmpDetail
Modify FileGroup empDetail_FG Default
go