Help with a troublesome SQL query.

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 accomplish: Let GRTQ be the total quantity of green and red parts of any kinds shipped by each supplier with supplier number Si. Obtain the table containing the tuples, where GRTQ > 300. List the result in the increasing order of the total quantities.

Any bright ideas? I'll post some of my attempts shortly.

Answer 1

Try this:

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

This is the output on your sample data:

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

Answer 2

Here's your query:

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 it by computing it yourself.

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

Among these:

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

s2 supplied only p1 and p2, in quantities of 300 and 400, respectively. Total = 700

s1 supplied all parts in these quantities: 300 + 200 + 200 + 100. Total = 800