Formulate the following queries using SQL:

Question

(a) (1) List all publishers in alphabetical order of name.
(2) List all printing jobs for the publisher ‘Gold Press’.
(3) List the names and phone numbers of all publisher who have a rush job (jobType = ‘R’).
(4) List the dates of all the purchase orders for the publisher ‘Gold Press’.
(5) How many publisher fall into each credit code category?
(6) List all job type’s with at least three printing jobs.
(7) List the average price of all items.
(8) List all items with a price below the average price of an item.
(b) Create a view of publisher details for all publisher who have a rush printing job, excluding their credit code.

Answer 1

(a) (1) SELECT pubName
FROM Publisher
ORDER BY pubName;
(2) SELECT jobID
FROM BookJob b, Publisher p
WHERE b.pubID = p.pubID AND pubName = ‘Gold Press’;
(3) SELECT pubName, telNo
FROM BookJob b, Publisher p
WHERE b.pubID = p.pubID AND jobType = ‘R’;
(4) SELECT poID, poDate
FROM PurchaseOrder po, BookJob b, Publisher p
WHERE po.jobID = b.jobID AND b.pubID = p.pubID AND
pubName = ‘Gold Press’;
(5) SELECT creditCode, COUNT(*)
FROM Publisher
GROUP BY creditCode;
(6) SELECT jobType, COUNT(*)
FROM BookJob
GROUP BY jobType
HAVING COUNT(*) >= 3;
(7) SELECT AVG(price)
FROM Item;
(8) SELECT *
FROM Item
WHERE price < (SELECT AVG(price) FROM Item);
(b) CREATE VIEW PB (pubID, pubName, street, city, postcode, telNo)
AS SELECT p.pubID, pubName, street, city, postcode, telNo
FROM BookJob b, Publisher p
WHERE b.pubID = p.pubID AND jobType = ‘R’;