Formulate the following queries using SQL:

(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.


(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’;

Computer Science & Information Technology

You might also like to view...

Which statement is false?

a. The shape of a binary search tree that corresponds to a set of data can vary, depending on the order in which the values are inserted into the tree. b. A node can be inserted at any point in a binary search tree. c. The process of creating a binary search tree actually sorts the data, and thus this process is called the binary tree sort. d. The binary search tree facilitates duplicate elimination.

Computer Science & Information Technology

A(n) ________ shows how all pages in the presentation will display

Fill in the blank(s) with correct word

Computer Science & Information Technology

To select a range in using the name box

A) position the mouse pointer over the column headings holding the Alt key. B) click in the name box and type the range address such as B15:D25 and then press "Enter." C) click in the first cell of the range, hold the "Ctrl" key, and then click in the last cell of the range. D) select the name box from the 2010 File menu and choose "Range."

Computer Science & Information Technology

In a small business, a department might be managed by no more than one manager, and each manager manages no more than one department. This is an example of a 1:1 relationship

Indicate whether the statement is true or false

Computer Science & Information Technology