this week I need to utilize the Classic Cars information located here - http://r
ID: 3756548 • Letter: T
Question
this week I need to 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.
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 theaverage payment for Oct 2004)? Order the results by the date of the payment. You will need to use the date functions(http://www.w3schools.com/sql/sql_dates.asp ).
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.
Explanation / Answer
If you have any doubts, please give me comment...
SELECT checkNumber
FROM Payments P
WHERE amount < 2*(
SELECT AVG(amount)
FROM Payments P1
WHERE MONTH(P.paymentDate) = MONTH(P1.paymentDate) AND YEAR(P.paymentDate) = YEAR(P1.paymentDate)
)
GROUP BY MONTH(P.paymentDate), YEAR(P.paymentDate);
SELECT productName, P.productLine, ROUND((quantityInStock/sumOfStack)*100,2) AS percent
FROM Products P, (
SELECT productLine, SUM(quantityInStock) AS sumOfStack
FROM Products
GROUP BY productLine
ORDER BY productLine
) AS T
WHERE P.productLine = T.productLine
ORDER BY P.productLine, percent;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.