Trying to write a MySQL connection request

Question

We have three tables:

p:

<`
+-----+-------+-------+--------+--------+
| pno | pname | color | weight | city   |
+-----+-------+-------+--------+--------+
| p1  | nut   | red   |     12 | London |
| p2  | bolt  | green |     17 | Paris  |
| p3  | screw | blue  |     17 | Rome   |
| p4  | screw | red   |     14 | London |
| p5  | cam   | blue  |     12 | Paris  |
| p6  | cog   | red   |     19 | London |
+-----+-------+-------+--------+--------+
`

s:


+-----+-------+--------+--------+
| sno | sname | status | city   |
+-----+-------+--------+--------+
| s1  | Smith |     20 | London |
| s2  | Jones |     10 | Paris  |
| s3  | Blake |     30 | Paris  |
| s4  | Clark |     20 | London |
| s5  | Adams |     30 | Athens |
+-----+-------+--------+--------+


sp:


+-----+-----+-----+
| sno | pno | qty |
+-----+-----+-----+
| s1  | p1  | 300 |
| s1  | p2  | 200 |
| s1  | p3  | 400 |
| s1  | p4  | 200 |
| s1  | p5  | 100 |
| s1  | p6  | 100 |
| s2  | p1  | 300 |
| s2  | p2  | 400 |
| s3  | p2  | 200 |
| s4  | p2  | 200 |
| s4  | p4  | 300 |
| s4  | p5  | 400 |
+-----+-----+-----+

What we need to do: Let GRTQ be the total number of green and red parts of any kind shipped by each supplier with supplier number Si. Get a table containing tuples where GRTQ> 300. List the result in increasing order of total number.

Any bright ideas? I will post some of my attempts soon.

Answer 1

Here is your request:

SELECT s.sno, SUM(sp.qty) as GRTQ 
FROM s, p, sp 
WHERE s.sno = sp.sno AND p.pno = sp.pno AND (p.color = "red" OR p.color="green") 
GROUP BY s.sno, s.sname 
HAVING GRTQ > 300 
ORDER BY GRTQ ASC;

The resulting output is:
+-----+------+
| sno | GRTQ |
+-----+------+
| s4  |  500 |
| s2  |  700 |
| s1  |  800 |
+-----+------+

You can verify this by calculating it yourself.

i.e. the red or green parts are p1, p2, p4, p6

Among them:

s4 is supplied only p2 and p4, in quantities of 200 and 300, respectively. Total = 500

s2 p1 p2, 300 400 . = 700

s1 : 300 + 200 + 200 + 100. = 800

Answer 2

:

SELECT
  s.sno,
  s.sname,
  SUM(sp.qty) AS `GRTQ`
FROM
  sp
INNER JOIN
  s
ON
  s.sno = sp.sno
INNER JOIN
  p
ON
  p.pno = sp.pno
WHERE
  (p.color = 'red' or p.color = 'green')
GROUP BY
  s.sno,
  s.sname
HAVING
  `GRTQ` > 300
ORDER BY
  `GRTQ` ASC

:

+------+-------+------+
| sno  | sname | GRTQ |
+------+-------+------+
| s1   | Smith |  800 |
| s2   | Jones | 1400 |
+------+-------+------+
2 rows in set (0.00 sec)