sqlquery7.sql

Problem Statement:
Write a SQL query to find the max quantity (Qty) supplied by each status. The tables (Supplier, SP and Product) should be joined in the same order as stated in bracket.

Note: Name the column with max quantity as 'm'

Information about the table:
Table Product:
+------+----------+--------+--------+
| Pno  | Pname    | Colour | Weight |
+------+----------+--------+--------+
|    1 | pen      | red    |      5 |
|    2 | pencil   | blue   |     10 |
|    3 | sharpner | red    |      3 |
|    4 | eraser   | white  |      6 |
|    5 | stapler  | green  |     40 |
|    6 | whitener | white  |     15 |
+------+----------+--------+--------+

Table Supplier:
+------+----------+------------+--------+
| Sno  | Sname    | City       | Status |
+------+----------+------------+--------+
|    1 | rachit   | jalandhar  | active |
|    2 | sahil    | amritsar   | active |
|    3 | anjali   | bareilly   | active |
|    4 | priyanka | delhi      | active |
|    5 | krishna  | jaipur     | active |
|    6 | kashish  | chandigarh | active |
+------+----------+------------+--------+

Table SP:
+------+------+------+
| Sno  | Pno  | Qty  |
+------+------+------+
|    1 |    6 |   10 |
|    2 |    4 |    8 |
|    3 |    3 |   15 |
|    4 |    5 |    6 |
|    5 |    1 |   11 |
|    6 |    2 |   20 |
+------+------+------+

Note: Write keywords of syntax in uppercase alphabets.

Solution:
SELECT s.status, MAX(sp.qty) AS m FROM Supplier AS s 
INNER JOIN SP
ON s.sno = sp.sno
GROUP BY s.status;

Output:
+----------+------+
| status   | m    |
+----------+------+
| active   |   20 |
| inactive |    8 |
+----------+------+