SQL for dummies

Posted on 2001-06-08
Last Modified: 2009-07-29
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.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;
Question by:dbnewbie
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

mcmahon_s earned 50 total points
ID: 6169801
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.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

Author Comment

ID: 6169855
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.

Expert Comment

ID: 6169874
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.

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question