?
Solved

SQL for dummies

Posted on 2001-06-08
3
Medium Priority
?
860 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
[X]
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
3 Comments
 
LVL 4

Accepted Solution

by:
mcmahon_s earned 200 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

770 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