For this homework assignment you will need to utilize the Classic Models informa

Question

For this homework assignment you will need to utilize the Classic Models information located here m/dm assicM Your instructor will provide access to this particular database in SQL Server. I only need to see the SQL you write to answer each of these queries. Because have access to the same dataset that you do, I can run the queries myself to see the results they provide. Be prepared to hand this in at the beginning of class and remember to put your name and student ID in the header of the document. Write the SQL to answer the following queries. Single entity a. What is the average percentage markup of the MSRP on buyPrice? b. How many distinct products does Classic Models sell? c. Report the name and city of customers who don't have sales representatives? d. What are the names of executives with VP or Manager in their title? Use the CONCAT function to combine the employee's first name and last name into a single field for reporting. e. Which orders have a value greater than $5,000? 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

Explanation / Answer

a) Select MSRP/buyprice as avg from Productsb

b) select distinct(productcode) from products where productname=’classicmodel’;

c) select c.customername,c.city from customers c where c.customernumber not in(select c1.customernumber from customers c1 where c1.salesRepEmployeeNumber= c.salesRepEmployeeNumber)

This will retrieve all customer numbers who will have sales representative and hence when not in is used it will be removed

d) select concat(lastname,firstname) as name from employees where jobtitle like ‘VP’ or ‘Manager’

e) select ordernumber from orderdetails where (quantityordered*priceeach)>5000

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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