write query using SQL 99 for : table location(locid,city,state,country), table p

Question

write query using SQL 99 for :
table location(locid,city,state,country), table products(pid,pname,catogory,price), table sales(pid,timeid,locid,totalsales)
1.Find percentage changes total monthly sales for each location?
2.Find percentage changes total quarterly sales for each product?
3.Find average daily sales over the preceding 30 days for each product?
4. for each week find the max moving average of sales over the preceding for weeks?
5. find the top 3 locations rank by total sales?
6. find the top 3 locations rank by cumulative sales, for every month over the past year?
7. rank all location by total sales over past year and for each location print difference in total sales relative to location behind it?

This question is from Ramakrishnan 25.5

Explanation / Answer

Dear,

 

You didnt provide the tables,

 

Assumed that Totalsales given per month.

 

calculate % for each month and show the result.

 

Location wise generated

 

Try this

SELECT T.month, S.totalsales, L.state, PERCENT_RANK(s.totalsales) OVER W AS Percent_Change

FROM Sales S, Times T, Locations L

WHERE s.timeid=T.timeid AND s.locid=L.locid

WINDOW W AS (PARTITION BY L.state

                               ORDER BY T.month

                               RANGE BETWEEN INTERVAL ‘1’ MONTH PRECEDING

                               AND INTERVAL ‘1’ MONTH FOLLOWING)

 

1.

 

 

 

SELECT L.city, T.month, S.totalsales AS Month_TotalSales, (totalsales/sum(totalsales)) * 100 AS Percentage_Change
FROM Sales S, Time T, Location L
WHERE S.timeid=T.timeid AND S.locid=L.locid

 

 

 

Order by L.city

 

 

 

Cut-off value c is chosen by optimizer c-cost entered by User.

 

Top priorities 1,2 and 3 selected

5. SELECT L.locid, L.city, L.state, L.country
FROM Sales S, Locations L
WHERE S.locid=L.locid AND S.locid=1 AND S.locid=2 AND s.locid=3
AND S.sales > c
ORDER BY S.sales DESC

please post remaining questions in another post

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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