Write the SQL to answer the following queries. General Queries: f. Who is at the
ID: 3571747 • Letter: W
Question
Write the SQL to answer the following 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?
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.
Payments check Number Date payment amount t customerNumber Indexes Customers custome Number custome Name contactLastName contactFirstName phone addressLine addressLine2 state postalCode country sales RepEmployeeNumber creditLimit Indexes Orders orderNumber order Date requiredDate shipped Dat status comments CustomerNumber Indexes Offices. office Code phone addressLine addressLine2 state Count postalCode territory Indexes Employees employeeNumber lastName firstName extension email reportsTo jobTitle office Code Indexes OrderDetails v t orderNumber t productcode quantity ordered priceEach orderLineNumber Indexes Product Lines productLine textDescription html Description image Indexes Boss of employee Products productCode productName productScale productVendor productDescription quantitylnStock buy Price MSRP productLine IndexesExplanation / Answer
f. Select employeeNumber from Employees where reportsTo=NULL;
g. Select employeeNumber from Employees where reportsTo='William Patterson';
h. Select productName from Products where productCode IN(Select productCode from OrderDetails where orderNumber IN (Select OrderNumber from Order where customerNumber In(Select customerNumber from Customers where customerName='Herkku Gifts')))
i. Select commission as p.amount*0.05 from Payments where customerNumber In(Select customerNumber from Customers where salesRepEmployeeNumber IN(Select employeeNumber from Employees order by lastName, firstName));
j. Select mostRecent as min(orderDate), olderst as max(orderDate) from Orders
k. Select avg(shippedDate-orderDate) from Orders group by customerNumber
l. Select amount from Payments where customerNumber IN (Select customerNumber from Customers where customerNumber In(Select customerNumber from Orders where shippedDate between '01-08-2004' and '31-08-2004'));
m. Select CONCAT(firstName,lastName) from Employees where reportsTo='Diane Murphy';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.