SQL for dummies

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


SELECT pub_name
FROM publishers
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)
FROM Publisher
GROUP BY pub_name;


SELECT description, AVG (price)
FROM items;

SELECT description, price
FROM items
WHERE price <
          (SELECT AVG(price)
           FROM items);

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;
Who is Participating?

Improve company productivity with a Business Account.Sign Up

mcmahon_sConnect With a Mentor Commented:
1) Fine but you do not need to specify ASC in the order by statement. Ascending is the default. NOTE: You would be better off specifying to find Gold Press based on there pub_id since it is the key to the table. In a table with many records this will be significantly faster.

2) You need an additional statement in your WHERE clause to tell it how to join the two tables eg. WHERE p.pub_name = 'Gold Press' AND p.pub_id = b.pub_id

3) same as 2

4) OK

5) Not correct. Your statement will give you a count of 1 for each publisher as you have grouped by pub_name. You are trying to find out how many of each credit code so group by that. eg:
SELECT credit_code, COUNT (pub_name)
FROM Publisher
GROUP BY credit_code;

6) SELECT job_type
FROM bookjobs
GROUP BY job_type
HAVING count(job_id) >= 3

7) Your statement will give you an average for each item eg.
book1       10
book2        8

You want an average of all items so:
SELECT AVG (price)
FROM items

8) OK

9) You do not need to list the two fields b.job_type, b.job_id in the select statement. You can use them in your where statement without having to list them in the select clause. To create a view use:

CREATE VIEW PublisherView AS
SELECT p.pub_id, p.pub_name, p.street, p.city, p.post_code, p.phone_no
FROM Publishers p, Bookjobs b
WHERE job_type = 'R' AND b.pub_id = p.pub_id;

To test them you need MS SQL Server or MS Access
dbnewbieAuthor Commented:
i don't have MS SQL Server, but i do have Access. i'm not sure where to put the SQL code in Access. In Access, I've gone to View - SQL View, but the SQL code looks a little different.
Yes, the syntax required in Access vs SQL server can be slightly different. For most of the queries you have listed here there will however be no differences. The last create view statement will probably not work but the others should be OK.

If you have MS Access 2000 then install the MSDE which is on the CD but not installed by default. This is, for all purposes a version SQL Server without some of the nice bits and the syntax will be the same.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.