I need help with the next queries using the next table. 1. For each month, list
ID: 3709714 • Letter: I
Question
I need help with the next queries using the next table.
1. For each month, list the month, the number of different customers placing orders in that month, the total number of orders placed, and the total invoice amount (dollars), the average invoice amount (rounded to the nearest penny), and the maximum and minimum invoice amounts in that month.?
2. For customers with 3 or more orders, list the customer’s ID, the total number of orders placed by that customer, the number of different products the customer placed, the total invoice amount for that customer, and the average invoice amount (rounded to the nearest penny) for that customer. Sort the output in order by total invoice amount – highest first.
3. For products ordered by more than one different customer, list the name of the product and the total ordered quantity amount.
4. For all March orders, list the order number, customer name, agent name, product name, quantity ordered, and invoice amount (dollars). Sort the results in invoice amount order – highest amount first.
5. List the names of all customers who placed 2 or more orders for pencils through agent Brown or Gray.
CUSTOMERS A able containing inforon about customers 1t 1scnt quanticy rce Unique identifier for a custometfrownoce c005' has ceased to exist cid pO1 comb Da11400 0 p2brsh Newark 203000 5 p03 | razor | Duluth 150600 1.00 p04pn Dulu125300 1.00 pOS penl Dallas 221400 1.00 pO6 folder Dallas 123100 2.00 p07 caseNewark 100500100 cname Name of a customer city City where the customer headquarters) is located discnt Each customer has a negotiated discount oa prices 0Tip Top Duluch 10.00 c002 Basis Dallas 12.00 c003 AlliedDalla 8.0O A table containing information abour agent employees00ACME Doluch 100 06 ACMEKyo 00 AGENTS aid Unique identifier foc an agentrow aname Last name of agent city Ciy where agent is based percent Percentage commission each agent receives on each AGENTS s3 PRODUCTS A table containing information about produets for sale01SmithNew York ROERS pid pname Descriptive name of product city City where this product is warehoused quantity Quantity on hand for sale, in standard units price Wholesale price of each unit product Unique identifier for a product/row 402 JonesNeark 03 BrownTokyo 04 Gray N York c14 1011 n 001 0 p1 1000450.00 00101p01 10000.00 1019 feb 0 02 2 400180.00 1017feb3600540.00 Oasi Duluth 206 Smith Dallas Note that the same column name, city, appears in all three tables defined so far. This is not a coincidence 1023 00104 S00 450.00 1022 mar 001 05 400 720.00 1025001OS p7 800720.00 ORDERS A table containing information about orders Unique identifier for this order ordno o month h Month the orde was placed; assurne that orders starned in January of this year This customer.. c002 cid aid purchased through chis agent. pid qty dollars ...at this dollar cost 1026 may 002 a05p800 704.00 101s | ian | c003 | .03 | POS | 1200 | 1104.00 1014 03 po1200 1104.00 1021 feb 004 06p 1000 460.00 1016 ?an c006 s01 pol 1000 | 500.00 1020 leb 0003p7 600.00 1024 ma 0apo400.00 this specifie product... in this total quaniy...Explanation / Answer
/*1. For each month, list the month, the number of different customers placing orders in that month,
the total number of orders placed, and the total invoice amount (dollars), the average invoice amount (rounded to the nearest penny),
and the maximum and minimum invoice amounts in that month.?*/
SELECT o.month,count(c.cid),count(o.ordno),SUM(o.dollars),ROUND(AVG(o.dollars),0) FROM Customers c INNER JOIN Orders o
ON c.cid=o.cid
GROUP BY o.month
/*2. For customers with 3 or more orders, list the customer’s ID, the total number of orders placed by that customer,
the number of different products the customer placed, the total invoice amount for that customer,
and the average invoice amount (rounded to the nearest penny) for that customer. Sort the output in order by total invoice amount – highest first.*/
SELECT cid, Count(ordno),count(pid),SUM(dollars),ROUND(AVG(o.dollars),0) FROM Orders
GROUP BY cid
ORDER BY SUM(dollars) DESC
/*3. For products ordered by more than one different customer, list the name of the product and the total ordered quantity amount.*/
SELECT p.pname,SUM(o.dollars) FROM Orders o
INNER JOIN Products p ON o.pid=p.pid
Group by p.pname having count(o.cid)>1;
/*4. For all March orders, list the order number, customer name, agent name, product name, quantity ordered, and invoice amount (dollars).
Sort the results in invoice amount order – highest amount first.*/
SELECT o.ordno,c.cname,a.aname,o.qty,o.dollars FROM Customers c INNER JOIN Orders o
ON c.cid=o.cid INNER JOIN Products p ON o.pid=p.pid
INNER JOIN Agents a ON o.aid=a.aid
WHERE o.month='mar'
order by o.dollars DESC;
/*5. List the names of all customers who placed
2 or more orders for pencils through
agent Brown or Gray.*/
SELECT c.cname FROM Customers c INNER JOIN Orders o
ON c.cid=o.cid INNER JOIN Products p ON o.pid=p.pid
INNER JOIN Agents a ON o.aid=a.aid
WHERE a.aname in ('Gray','Brown')
GROUP by o.cid HAVING COUNT(o.ordno)>=2;