Question

几个简单英语sql问题，在线等答案！！！

1. Replace the query below with an equivalent query that is simpler, does not use UNION and does use an OUTER JOIN: (2 marks)
SELECT Code,COUNT(NbrItemsRequested) AS TotalItems
FROM Product AS p INNER JOIN SalesOrderProduct AS sop
ON p.PID = sop.PID
GROUP BY Code
HAVING COUNT(NbrItemsRequested) < 2
UNION
SELECT Code, 0
FROM Product
WHERE PID NOT IN (SELECT PID
FROM SalesOrderProduct);
2. Formulate three queries to list the order Number of every “Placed” order that includes a request for one or more Cookware items. (3 marks)
i. One query that uses IN twice and does not use EXISTS.
ii. One query that uses EXISTS once and IN once.
iii. One query that uses EXISTS twice and does not use IN.
3. In your own words, describe the result produced by the query below. (1.5 marks)
SELECT Code
FROM Product AS p
WHERE NOT EXISTS (SELECT *
FROM Customer AS c
WHERE State = 'QLD'
AND EXISTS (SELECT *
FROM SalesOrder AS so
WHERE so.CID = c.CID
AND EXISTS (SELECT *
FROM SalesOrderProduct AS sop
WHERE sop.SOID = so.SOID
AND sop.PID = p.PID)));
4. In your own words, describe the result produced by the query below. (1.5 marks)
SELECT Code
FROM Product AS p
WHERE NOT EXISTS (SELECT *
FROM Customer AS c
WHERE State = 'QLD'
AND NOT EXISTS (SELECT *
FROM SalesOrder AS so
WHERE so.CID = c.CID
AND EXISTS (SELECT *
FROM SalesOrderProduct AS sop
WHERE sop.SOID = so.SOID
AND sop.PID = p.PID)));
5. Advanced: Formulate an equivalent query to the one given in Question 4 above. Your query must not use EXISTS or IN. (1 bonus mark)

Answer 1

可以远程的话联系我！