Solved

SQL for dummies

Posted on 2001-06-08
3
792 Views
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)?

thanx.

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

// MY SQL STATEMENTS

1
SELECT pub_name
FROM publishers
ORDER BY pub_names ASC;

2
SELECT b.job_id, p.pub_name
FROM Bookjobs b, Publisher p
WHERE p.pub_name = ?Gold Press?;

3
SELECT p.pub_name, p.phone_no, b.job_type
FROM Publishers p, Bookjobs b
WHERE b.job_type = ?R?;

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

5
SELECT pub_name, COUNT (credit_code)
FROM Publisher
GROUP BY pub_name;

6
???

7
SELECT description, AVG (price)
FROM items;

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

9
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;
0
Comment
Question by:dbnewbie
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
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.
BOOK      AVG PRICE
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
0
 

Author Comment

by:dbnewbie
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.
0
 
LVL 4

Expert Comment

by:mcmahon_s
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now