NEED HELP WRITING MICROSOFT SQL SERVER QUERIES based on tables below and require
ID: 3731494 • Letter: N
Question
NEED HELP WRITING MICROSOFT SQL SERVER QUERIES based on tables below and requirements:
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
--e
SELECT o.OrderNo,
p.ProductID,
p.ProductName,
o.UnitPriceCharged,
o.DiscountRate,
o.QuantityOrdered,
COALESCE(o.QuantityPicked,0)
FROM tblOrderLine o /*Using Alias for table name*/
INNER JOIN /*Pulling only matched columns between 2 tables*/
tblProduct p /*Using Alias for table name*/
ON o.ProductID=p.ProductID /*Join on product id which is common on both tables*/
WHERE o.OrderNo=610 /*Where condition to filter out data*/
ORDER BY p.ProductID ASC /*Sorting based on productid */
--f
SELECT e.Employeeid AS EID,
e.LastName+','+' '+e.FirstName AS EmployeeName,
--e.SupervisorID,
e.JobpositionID AS EJobPosition,
s.Employeeid AS SID,
s.SupervisorID,
s.SJobPosition
FROM tblEmployee e,tblEmployee s
WHERE e.tblEmployee=s.tblEmployee
--g
SELECT c.CompanyID,
c.CompanyName,
o.OrderNo
FROM tblCompany c
LEFT OUTER JOIN
tblCustomerOrder o
ON c.CompanyID=o.CompanyID
WHERE o.OrderNo IS NULL/*Remove this condition to get all results including not null values*/
/*Now it pull only company which never have an order*/
ORDER BY c.CompanyName ASC;
--h
SELECT CompanyID,
CompanyName
FROM tblCompany
WHERE CompanyID
NOT IN
(SELECT DISTINCT CompanyID FROM tblCustomerOrder);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.