Write the SQL to answer the following queries. Single entity a. What is the aver
ID: 3571436 • Letter: W
Question
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 ClassicModels 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 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.
Correlated Subqueries
n. Which payments in any month and year are more than twice the average for that month and year (i.e. compare all payments in Oct 2004 with the average payment for Oct 2004)? Order the results by the date of the payment. You will need to use the date functions.
o. Report for each product, the percentage value of its stock on hand as a percentage of the stock on hand for product line to which it belongs. Order the report by product line and percentage value within product line descending. Show percentages with two decimal places
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
a)
Select avg(MSRP)
from Products
order by buyprice;
b)
select count(distinct(productName))
from Products
where productVendor='ClassicModels';
c)
select customerName, city
from Customer
where salesRepEmployeeNumber=NULL;
d)
select (firstName,' ',lastName) as name
from Employees
where jobTitle in ('VP','Manager');
e)
select orderNumber
from orderDetails
where PriceEach > 5000;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.