Utilize the Classic Cars information located here -http://richardtwatson.com/dm6

Question

Utilize the Classic Cars information located

here -http://richardtwatson.com/dm6e/Reader/ClassicModels.html.

Your instructor has provided a database on SQL Server called ClassicModel. You will use this database to answer these questions. I only need to see the SQL you write to answer each of these queries.

General Queries

f. Who is at the top of the organization (i.e., reports to no one)?
g. Who reports to William Patterson?
h. List all the products purchased by Herkku Gifts.
i. Compute the commission for each sales representative, assuming the commission is 5% of the cost of an order. Sort by employee last name and first name.
j. What is the difference in days between the most recent and oldest order date in the Orders file?
k. Compute the average time between order date and ship date for each customer ordered by the largest difference.
l. What is the value of orders shipped in August 2004? (HINT -> http://www.w3schools.com/sql/sql_dates.asp).
m. List the employees who report to those employees who report to Diane Murphy. Use the CONCAT function to combine the employee's first name and last name into a single field for reporting.

Explanation / Answer

--f. Who is at the top of the organization (i.e., reports to no one)?
SELECT employeeNumber,lastName,firstName From Employees
WHERE reportsTo IS NULL;

--g. Who reports to William Patterson?
--assuming William = lastName & Patterson=Patterson.please reverse if required
SELECT employeeNumber,lastName,firstName From Employees
WHERE reportsTo =(Select employeeNumber From Employees WHERE firstName='William' and lastName='Patterson');

--h. List all the products purchased by Herkku Gifts.
SELECT p.ProductCode,p.ProductName FROM Products p INNER JOIN OrderDetails od on p.ProductCode=od.ProductCode
INNER JOIN Orders o On od.OrderNumber=o.OrderNumber
INNER JOIN Customers c on o.CustomerNumber=c.CustomerNumber;

--i. Compute the commission for each sales representative, assuming the commission is 5% of the cost of an order.
-- Sort by employee last name and first name.
SELECT e.lastName,e.firstName,SUM(od.priceEach*QuantityOrdered*0.05) AS commission FROM OrderDetails od
INNER JOIN Orders o On od.OrderNumber=o.OrderNumber
INNER JOIN Customers c on o.CustomerNumber=c.CustomerNumber
INNER JOIN EMployees e on c.SalesRepEmployeeNumber=e.Employeenumber
GROUP BY c.SalesRepEmployeeNumber,e.firstname,e.lastname
Order BY e.lastName,e.firstName;

--j. What is the difference in days between the most recent and oldest order date in the Orders file?
SELECT DATEDIFF(dy, MIN(orderdate), MAx(orderdate)) As Days FROM Orders;

--k. Compute the average time between order date and ship date
--for each customer ordered by the largest difference.
SELECT AVG(DATEDIFF(dy, orderdate, Shippeddate)) As AvgDays FROM Orders;
SELECT AVG(DATEDIFF(hh, orderdate, Shippeddate)) As Avghours FROM Orders;

--l. What is the value of orders shipped in August 2004? (HINT -> http://www.w3schools.com/sql/sql_dates.asp).
SELECT COUNT(OrderNumber) FROM Orders WHere Shippeddate
DATEPART(MM, Shippeddate)=8 and DATEPART(YY, Shippeddate)=2004;

--m. List the employees who report to those employees who report to Diane Murphy.
-- Use the CONCAT function to combine the employee's first name and last name into a single field for reporting.
--assuming William = lastName & Patterson=Patterson.please reverse if required
SELECT * From Employees where reportsTo in(SELECT employeeNumber From Employees where reportsTo in
(Select employeeNumber From Employees WHERE lastName='Murphy' and firstName='Diane'));

Please comment if any clarification required or any changes required.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.