SQL - QUESTION 2

Sample data stored in these tables is given below: SHOES
+------+----------------+--------+------+--------+--------+------+
| Code | Name | type | size | cost | margin | Qty |
+------+----------------+--------+------+--------+--------+------+
| 1001 | School Canvas | School | 6 | 132.50 | 2.00 | 1200 |
| 1002 | School Canvas | School | 7 | 135.50 | 2.00 | 800 |
| 1003 | School Canvas | School | 8 | 140.75 | 2.00 | 600 |
| 1011 | School Leather | School | 6 | 232.50 | 2.00 | 2200 |
| 1012 | School Leather | School | 7 | 270.00 | 2.00 | 1280 |
| 1013 | School Leather | School | 8 | 320.75 | NULL | 1100 |
| 1101 | Galaxy | Office | 7 | 640.00 | 3.00 | 200 |
| 1102 | Galaxy | Office | 8 | 712.00 | 3.00 | 500 |
| 1103 | Galaxy | Office | 9 | 720.00 | 3.00 | 400 |
| 1201 | Tracker | Sports | 6 | 700.00 | NULL | 280 |
| 1202 | Tracker | Sports | 7 | 745.25 | 3.50 | NULL |
| 1203 | Tracker | Sports | 8 | 800.50 | 3.50 | 600 |
| 1204 | Tracker | Sports | 9 | 843.00 | NULL | 860 |
+------+----------------+--------+------+--------+--------+------+

CUSTOMERS
ORDERS
Aggregate Functions
+-----------+----------------+------------------------+--------------------+----------+
| Cust_Code | name | address | Phone | Category |
+-----------+----------------+------------------------+--------------------+----------+
| C001 | Novelty Shoes | Raja Nagar, Bhopal | 4543556, 97878989 | A |
| C002 | Aaram Footwear | 31, Mangal Bazar, Agra | NULL | B |
| C003 | Foot Comfort | New Market, Saharanpur | 51917142, 76877888 | B |
| C004 | Pooja Shoes | Janak Puri, New Delhi | 61345432, 98178989 | A |
| C005 | Dev Shoes | Mohan Nagar, Ghaziabad | NULL | C |
+-----------+----------------+------------------------+--------------------+----------+
+----------+-----------+-----------+-----------+------------+-------------+
| order_no | cust_code | Shoe_Code | order_qty | order_date | target_date |
+----------+-----------+-----------+-----------+------------+-------------+
| 1 | C001 | 1001 | 200 | 2008-12-10 | 2008-12-15 |
| 2 | C001 | 1002 | 200 | 2008-12-10 | 2008-12-15 |
| 3 | C003 | 1011 | 150 | 2009-01-08 | 2009-01-13 |
| 4 | C002 | 1012 | 250 | 2009-01-08 | 2009-01-13 |
| 5 | C001 | 1011 | 400 | 2009-01-10 | 2009-01-15 |
| 6 | C002 | 1101 | 300 | 2009-01-10 | 2009-01-15 |
| 7 | C004 | 1201 | 200 | 2009-01-10 | 2009-01-15 |
| 8 | C005 | 1102 | 350 | 2009-01-10 | 2009-01-15 |
| 9 | C001 | 1103 | 225 | 2009-01-13 | 2009-01-18 |
| 10 | C002 | 1203 | 200 | 2009-01-14 | 2009-01-19 |
+----------+-----------+-----------+-----------+------------+-------------+

To find the highest selling price of any type of shoe rounded to decimal places.

SELECT
ROUND(MAX(cost),2)
AS "Max. SP" FROM
shoes;

To find the total order quantity

SELECT SUM(order_qty)
FROM orders;

To find the the total value(Quanitity x Cost) of Shoes of type'Office' present in the inventory

SELECT SUM(cost*qty)
FROM shoes
WHERE type ='Office';

we are interested in viewing only those groups' output for which the total quantity is more than 1500 (SUM(Qty) > 1500).

SELECT type, SUM(qty) FROM shoes
GROUP BY type HAVING SUM(qty) > 1500;
+--------+----------+
| type | SUM(qty) |
+--------+----------+
| School | 7180 |
| Sports | 1740 |
+--------+----------+

The management of the shoe factory may want to know what is the total quantity of shoes, of sizes other than 6, of various types. i.e., what is the total quantity of shoes (of sizes other than 6) of type School, Office, and Sports each.Moreover, the report is required only for those groups for which the total
quantity is more than 1500.

The management may also want to know what is the maximum, minimum, and average margin of each type of shoes. But in this reports shoes of sizes 6 and 7 only should be included. Report is required only for those groups for which the minimum margin is more than 2.

The statements and their outputs corresponding to above requirements are given below:
SELECT type, SUM(qty) FROM shoes
WHERE size <> 6 Checks individual row
GROUP BY type HAVING sum (qty) > 1500; Checks individual group
+--------+----------+
| type | SUM(qty) |
+--------+----------+
| School | 3780 |
+--------+----------+
SELECT type, MIN(margin), MAX(margin), AVG(margin) FROM shoes
WHERE size in (6,7)
GROUP BY type having MIN(margin) > 2;
+--------+-------------+-------------+-------------+
| type | MIN(margin) | MAX(margin) | AVG(margin) |
+--------+-------------+-------------+-------------+
| Office | 3.00 | 3.00 | 3.000000 |
| Sports | 3.50 | 3.50 | 3.500000 |