sqlquery6.sql

Problem Statement:
Write an SQL query to find the number of products supplied by each colour.

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-1: Name the column containing number of products as nop.
Note-2: Write keywords of syntax in uppercase alphabets.

Solution:
SELECT p.colour, count(p.pno) AS nop FROM product AS p 
INNER JOIN sp 
ON p.pno = sp.pno
GROUP BY p.colour;

Output:
+--------+-----+
| colour | nop |
+--------+-----+
| white  |   2 |
| red    |   2 |
| green  |   1 |
| blue   |   1 |
+--------+-----+