SQL for dummies
Posted on 2001-06-08
i'm trying to leard SQL and tried working on a problem i found in a book. i tried formulation the SQL statements as asked by the problem (i'm posting them at the bottom). i need 2 things:
1. can someone take a look at my SQL statements and tell me if i'm doing anything wrong.
2. how can i test to see if these SQL statement work (is there a software i can use to test them out)?
NOTE: for some reason, some of the single quotation marks are being displayed as question marks. u'll see what i mean below.
// THE PROBLEM
The following tables form part of a database held in a Relational Database Management System for a
printing company that handles printing jobs for book publishers:
Publishers (pub_id, pub_name, street, city, post_code, phone_no, credit_code)
Bookjobs (job_id, pub_id, job_date, description, job_type)
Purchase_Orders (job_id, po_id, po_date)
PO_Items (job_id, po_id, item_id, quantity)
Items (item_id, description, on_hand, price)
Publishers contains publisher details and pub_id is the key.
Bookjobs contains details of the printing jobs (books or part books) and job_id is the key.
Purchase_Orders A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. This table contains details of the purchase orders for each job and the key is job_id/po_id. Each printing job may have several purchase orders assigned to it.
PO_Items Each purchase order (PO) may contain several PO items. This table contains details of the PO items and job_id/po_id/item_id form the key.
Items contains details of the materials which appear in po_items, and the key is item_id.
Formulate the following queries using SQL:
(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 publishers who have a rush job (job_type = 'R').
(4) List the dates of all the purchase orders for the publisher 'Gold Press'.
(5) How many publishers 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.
(9) Create a view of publisher details for all publishers who have a rush printing job, excluding their
// MY SQL STATEMENTS
ORDER BY pub_names ASC;
SELECT b.job_id, p.pub_name
FROM Bookjobs b, Publisher p
WHERE p.pub_name = ?Gold Press?;
SELECT p.pub_name, p.phone_no, b.job_type
FROM Publishers p, Bookjobs b
WHERE b.job_type = ?R?;
SELECT po.po_date, p.pub_name
FROM Purchase_Orders po, Bookjobs b, Publishers p
WHERE pub_name = ?Gold Press? AND po.job_id = b.job_id AND b.pub_id = p.pub_id;
SELECT pub_name, COUNT (credit_code)
GROUP BY pub_name;
SELECT description, AVG (price)
SELECT description, price
WHERE price <
SELECT p.pub_id, p.pub_name, p.street, p.city, p.post_code, p.phone_no, b.job_type, b.job_id,
FROM Publishers p, Bookjobs b
WHERE job_type = ?R? AND b.pub_id = p.pub_id;