I am working through this link and I am stuck on Q7 and Q8 on this website: http://sqlzoo.net/a3m.htm

My attempt for Q7 is:

SELECT tprod.dscr, sum(qnty), max(tpurcd.recv)
FROM tprod, tpurcd
WHERE tprod.code = tpurcd.prod
GROUP BY tprod.dscr, tpurcd.recv

Q8 attempt:

SELECT tpurcd.cust, tpurcd.recv, qnty
FROM tpurcd, tprod
WHERE tprod.code = tpurcd.prod
AND tpurcd.qnty * tprod.pric

Table info is listed here: http://sqlzoo.net/a3.htm

Any h开发者_C百科elp would be appreciated!

Using mysql

7

SELECT tprod.dscr as Description, sum(tpurcd.qnty) as Quantity, max(tpurcd.recv) as 'Most Recent Order'
FROM tprod
INNER JOIN tpurcd ON tprod.code = tpurcd.prod
GROUP BY tprod.dscr

8

SELECT tpurcd.cust as 'Customer Code', tpurcd.recv as 'Received Date', sum(tpurcd.qnty * tprod.pric) as 'Total Value'
FROM tprod
INNER JOIN tpurcd ON tprod.code = tpurcd.prod 
group by tpurcd.cust, tpurcd.recv
having sum(tpurcd.qnty * tprod.pric) > 475

.7. For each product show the description and the total number ordered and the date of the most recent order.

(you shouldn't group by tpurcd.recv)

SELECT tprod.dscr, sum(qnty), max(tpurcd.recv)
FROM tprod
INNER JOIN tpurcd ON tprod.code = tpurcd.prod
GROUP BY tprod.dscr

.8. Show the customer code, the received date and the total value of all purchase orders with a total value of at least £475.

(the expression tpurcd.qnty * tprod.pric was hanging)

SELECT tpurcd.cust, tpurcd.recv, sum(tpurcd.qnty * tprod.pric)
FROM tpurcd
INNER JOIN tprod ON tprod.code = tpurcd.prod
GROUP BY tpurcd.cust, tpurcd.recv
HAVING sum(tpurcd.qnty * tprod.pric) >= 475