I need someone proficient in Microsoft SQL Server to assist me in writing some q
ID: 3731475 • Letter: I
Question
I need someone proficient in Microsoft SQL Server to assist me in writing some queries that REQUIRE JOINING TABLES based on the following set of tables and requirements below:
Feel free to submit a partial answer, I just need help solving whatever you can! Thank you for your time
tblAddress AddressID tblCompany CompanylD CompanyName BillingAddressID IsCustomer CustomerVenderSince DateLastModified tblProduct 9 ProductiD City StateProvince PostalCode Country ProductName CurrentStandardUnitPrice tblOrderLine tblCustomerOrder tblEmployee OrderNo EmployeelD LastName FirstName UserName UserPassword Phone SupervisorlD JobPositionlD OrderNo ProductID QuantityOrdered UnitPriceCharged DiscountRate QuantityPicked OrderDate FilledYesNo CompanylD ShippingAddressID EmployeelDTookOrder EmployeelDFilledOrder - tblJobPosition JobPositionID JobTitleExplanation / Answer
Below are the SQL queries for the requirement asked in the questions
Requirement A
List EmployeeId, EmployeeName, JobPositionId and JobName displaying the basic details of employee
SQL CODE
Select
E.EmployeeId,
E.FirstName + ', ' + E.LastName As EmployeeFullName,
J.JobPositionId,
J.JobTitle
FROM tblEmployee As E
Inner Join tblJobPosition As J On E.JobPositionId = J.JobPositionId
Where E.EmployeeId = 50
Requirement B
List EmployeeId and SupervisorName for the dropdown
SQL CODE
Select
E.EmployeeId,
E.LastName + ', ' + E.FirstName + ': ' + J.JobTitle As SupervisorName,
FROM tblEmployee As E
Inner Join tblJobPosition As J On E.JobPositionId = J.JobPositionId
Order By E.LastName, E.FirstName
Requirement C
List the orders that partially filed by employee 75 or doesn't fill by any
SQL CODE
Select
C.OrderNo,
convert(varchar(10), cast(C.OrderDate as date), 101) As OrderDate,
C.CompanyID,
CP.CompanyName,
C.FilledYesNo,
C.EmployeeIdFilledOrder,
COALESCE((E.LastName + ', ' + E.FirstName), 'None') AS EmployeeAssignedToFill,
COUNT(OL.OrderNo) As NoOfProducts
FROM tblCustomerOrder As C
Inner Join tblCompany As CP On CP.CompanyID = C.CompanyID
Inner Join tblOrderLine As OL On OL.OrderNo = C.OrderNo
Outer Join tblEmployee As E On E.EmployeeId = C.EmployeeIdFilledOrder
Where C.FilledYesNo Is Null And (C.EmployeeIdFilledOrder IS Null OR C.EmployeeIdFilledOrder = 75)
Group By C.OrderNo,
C.OrderDate,
C.CompanyID,
CP.CompanyName,
C.FilledYesNo,
C.EmployeeIdFilledOrder,
E.LastName,
E.FirstName
Order By C.OrderNo
Requirement D
Fetch the order detail for order no 610
SQL CODE
Select
C.OrderNo,
convert(varchar(10), cast(C.OrderDate as date), 101) As OrderDate,
C.CompanyID,
CP.CompanyName,
CP.BillingAddressID,
(A.AddressLine1 + ' ' + A.AddressLine2 + ' ' + A.City + ', ' + A.StateProvince + ' ' + A.PostalCode) As BillingAddress
C.ShippingAddressID,
C.EmployeeIdTookOrder,
(E.FirstName + ' ' + E.LastName + ' Phone: ' + SUBSTRING(E.Phone, 7, 4)) AS OrderTakenBy,
C.EmployeeIdFilledOrder
FROM tblCustomerOrder As C
Inner Join tblCompany As CP On CP.CompanyID = C.CompanyID
Inner Join tblOrderLine As OL On OL.OrderNo = C.OrderNo
Inner Join tblEmployee As E On E.EmployeeId = C.EmployeeIdFilledOrder
Inner Join tblAddress As A On A.AddressId = CP.BillingAddressID
Where C.OrderNo = 610
Requirement E
Fetch the orders and the quantity picked details
SQL CODE
Select
C.OrderNo,
P.ProductID,
P.ProductName,
OL.UnitPriceCharged,
OL.DiscountRate,
COALESCE(OL.QuantityPicked, 0) As QuantityPicked
FROM tblCustomerOrder As C
Inner Join tblOrderLine As OL On OL.OrderNo = C.OrderNo
Inner Join tblProduct As P On P.ProductID = OL.ProductID
Where C.OrderNo = 610
Order By P.ProductID
Requirement F
Get the supervisor and employee detail
SQL CODE
Select
E.EmployeeId As EID,
E.LastName + ', ' + E.FirstName As EmployeeName,
E.JobPositionId As EJobPositionID,
S.EmployeeId As SID,
S.LastName + ', ' + S.FirstName As SupervisorName,
S.JobPositionId As SJobPositionID,
FROM tblEmployee As E
FULL OUTER JOIN tblEmployee As S On E.EmployeeID = S.SupervisorID
Order By S.LastName, E.LastName
Requirement G
Get the companies which never ordered
SQL CODE
Select
C.CompanyID,
C.CompanyName,
CO.OrderNo
FROM tblCompany As C
FULL Outer Join tblCustomerOrder As CO On CO.CompanyID = C.CompanyID
Where CO.OrderNo IS NULL
Requirement H
Get comapny id and name which doesn't ordered any item
SQL CODE
Select
C.CompanyID,
C.CompanyName
FROM tblCompany As C
WHERE CompanyID NOT IN (Select
DISTINCT
C.CompanyID
FROM tblCompany As C
Inner Join tblCustomerOrder As CO On CO.CompanyID = C.CompanyID)