sql queries
Budget: $10 – $30 USD
DEADLINE : 11/19/2022 EST
MAX BUDGET : $20 ( FOR EACH QUESTION - $5)
1. For each customer, product, month and state combination, compute (1) the customer's average sale of this product for the given month and state, (2) the customer’s average sale for the given month and state, but for all other products (3) the customer’s average sale for the given product and state, but for all other months and (4) the average sale of the product and the month but for all other states.
2. For customer, product and state, show the average sales before and after each month (e.g., February (month 2), show average sales of January (month 1) and March (month 3). For “before” January and “after” December, display <NULL>. The “YEAR” attribute is not considered for this query – for example, both January of 2017 and January of 2018 are considered January regardless of the year.
3. For each product, find the median sales quantity (assume an odd number of sales for simplicity of presentation). (NOTE – “median” is defined as “denoting or relating to a value or quantity lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability of falling above or below it.” E.g., Median value of the list {13, 23, 12, 16, 15, 9, 29} is 15.
For example, given the following sales transactions for Bread, the median quant for Bread is 3.
PRODUCT QUANT
======= =====
Bread 1
Bread 1
Bread 1
Bread 2
Bread 2
Bread 3
Bread 4
Bread 5
Bread 6
Bread 7
Bread 7
4. For customer and product, find the month by which time, 75% of the sales quantities have been purchased. Again, for this query, the “YEAR” attribute is not considered. Another way to view this query is to pretend all 10,000 rows of sales data are from the same year.
NOTE: CHECK BELOW THERE ARE DIFFERENT 4 REPORTS, AND EACH REPOSTS BELONGS TO EACH QUERY QUESTIONS THOSE ARE NOT OUTPUTS BUT YOU NEED TO CHECK THE COLUMN NAMES THEY MUST SAME THOSE REPORTS ARE JUST FOR BASIC OUTPUT FOR WHICH COLUMN ARE NEEDED.(CHECK ATTACH PDF : "REPORTS')
NOTE : TWO TYPE OF QUERIES SOLUTION WE NEEDED FOR EACH QUESTION
1) USING "WITH AS " subquery
2) USING nested subquery
NOTE : Character string data (e.g., customer name and product name) are left justified.
2. Numeric data (e.g., Maximum/minimum Sales Quantities) are right justified.
3. Only standard SQL statements and aggregate function syntaxes are used
I ALSO UPLOAD DATABASE FILE YOU MUST USE THAT
MAX BUDGET : $20 ( FOR EACH QUESTION - $5)
1. For each customer, product, month and state combination, compute (1) the customer's average sale of this product for the given month and state, (2) the customer’s average sale for the given month and state, but for all other products (3) the customer’s average sale for the given product and state, but for all other months and (4) the average sale of the product and the month but for all other states.
2. For customer, product and state, show the average sales before and after each month (e.g., February (month 2), show average sales of January (month 1) and March (month 3). For “before” January and “after” December, display <NULL>. The “YEAR” attribute is not considered for this query – for example, both January of 2017 and January of 2018 are considered January regardless of the year.
3. For each product, find the median sales quantity (assume an odd number of sales for simplicity of presentation). (NOTE – “median” is defined as “denoting or relating to a value or quantity lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability of falling above or below it.” E.g., Median value of the list {13, 23, 12, 16, 15, 9, 29} is 15.
For example, given the following sales transactions for Bread, the median quant for Bread is 3.
PRODUCT QUANT
======= =====
Bread 1
Bread 1
Bread 1
Bread 2
Bread 2
Bread 3
Bread 4
Bread 5
Bread 6
Bread 7
Bread 7
4. For customer and product, find the month by which time, 75% of the sales quantities have been purchased. Again, for this query, the “YEAR” attribute is not considered. Another way to view this query is to pretend all 10,000 rows of sales data are from the same year.
NOTE: CHECK BELOW THERE ARE DIFFERENT 4 REPORTS, AND EACH REPOSTS BELONGS TO EACH QUERY QUESTIONS THOSE ARE NOT OUTPUTS BUT YOU NEED TO CHECK THE COLUMN NAMES THEY MUST SAME THOSE REPORTS ARE JUST FOR BASIC OUTPUT FOR WHICH COLUMN ARE NEEDED.(CHECK ATTACH PDF : "REPORTS')
NOTE : TWO TYPE OF QUERIES SOLUTION WE NEEDED FOR EACH QUESTION
1) USING "WITH AS " subquery
2) USING nested subquery
NOTE : Character string data (e.g., customer name and product name) are left justified.
2. Numeric data (e.g., Maximum/minimum Sales Quantities) are right justified.
3. Only standard SQL statements and aggregate function syntaxes are used
I ALSO UPLOAD DATABASE FILE YOU MUST USE THAT