Re: Newbie on SQL

From: Tim X <timx_at_spamto.devnul.com>
Date: 07 Jul 2003 18:41:27 +1000
Message-ID: <87smpibufc.fsf@tiger.rapttech.com.au>

>>>>> "newbie1234" == newbie1234 <member32445_at_dbforums.com> writes:

 newbie1234> I am new to SQL, and taking a army course...I am looking
 newbie1234> for any help or guidance that you could provide for my
 newbie1234> question. I have completed a small portion of the problem
 newbie1234> (see below)and unfortunatly, this is an open ended
 newbie1234> question.

 newbie1234> There are two SQL statements.  One is to create a table
 newbie1234> called Balance and the other is to load the table balance

newbie1234> from two existing tables: Order and Customer. There are newbie1234> errors in both SQL statements.

 newbie1234> I have tried to tackle the SQL problem by dissecting the
 newbie1234> statement and research the key concepts like: CREATE,
 newbie1234> INSERT, SELECT, Joins and field attributes. I am somewhat
 newbie1234> confused about the concwepts.

 newbie1234> I can not run this code in Access (due to the create
 newbie1234> statement) and no access to Oracle etc..If you could
 newbie1234> possible assist me in any way so that I would be able to

newbie1234> better understand these SQL key concepts, would be newbie1234> greatly welcomed.

newbie1234> I am trying to "Pin-point" the problems with the code!

 newbie1234> My objective is to create a table called balance that
 newbie1234> includes the customer ID, last name, first name, and the
 newbie1234> customer’s total balance (the sum of price for all
 newbie1234> orders).

newbie1234> See the tables below: customer and order

 newbie1234> CUSTOMER CUSTOMER_ID LAST FIRST STREET CITY STATE ZIP
 newbie1234> ----------- ------- ------- --------- ------- -----
 newbie1234> ------ 124 Martin Dan 418 Pine Grant MI 49219 1552 Adams
 newbie1234> Joan 3435 Elm Harper TN 39805 .. ... ... ... ... ... ...
 newbie1234> ORDER ORDER_NUM CUSTOMER_ID ORDER_DATE PRICE ---------
 newbie1234> ----------- ---------- ------- 10024 5642 12/8/1999
 newbie1234> 501.89 10025 124 30/9/1999 215.95 10026 4542 11/1/2000
 newbie1234> 570.25 10027 124 12/2/2000 420.60 .. ... ... ...

 newbie1234> The result should be a table that looks like this:
 newbie1234> BALANCE CUSTOMER_ID LAST FIRST TOTAL_BALANCE -----------

newbie1234> ------- ------- ------------- 1552 Adams Joan 845.56 5642 newbie1234> Collins Chip 760.01 4542 Ira Sally 570.25 .. ... ... ...

 newbie1234> Unfortunately, there are several mistakes. Tom wants your
 newbie1234> team to find the mistakes. Begin by examining the
 newbie1234> following text in the SQL*Plus window: The following SQL
 newbie1234> commands have errors and are not yielding the correct
 newbie1234> result. Modify the following code to get the desired
 newbie1234> table above.

SQL> CREATE TABLE balance
newbie1234> 2(customer_id NUMBER(7), last CHAR(10), first CHAR(10), newbie1234> total_balance CHAR(7));

SQL> INSERT INTO balance

 newbie1234> 2 SELECT customer.customer_id, customer.last,
 newbie1234> customer.first, sum(customer.price) 3 FROM customer,
 newbie1234> orders 4 WHERE customer.customer_id = order.customer_id

newbie1234> Possible Solution:
SQL> create table customer (customer_id number(7),last char(10),first SQL> char(10));

newbie1234> Table created.

SQL> insert into customer values (1552, 'Ira', 'Sally');

newbie1234> 1 row created.

SQL> create table orders (order_id number(10),customer_id SQL> number(7),price number);

newbie1234> Table created.

SQL> insert into orders values (1,1552,570.25);

newbie1234> 1 row created.

SQL> CREATE TABLE balance
newbie1234> 2 (customer_id NUMBER(7), last CHAR(10), first CHAR(10), newbie1234> total_balance CHAR(7));

newbie1234> Table created.

SQL> insert into balance

 newbie1234>  2 select c.customer_id,c.last,c.first,sum(o.price) from
 newbie1234> customer c, orders o 3 WHERE c.customer_id =
 newbie1234> o.customer_id 4 GROUP BY c.customer_id,c.last,c.first;

newbie1234> 1 row created.

SQL> select * from balance;

newbie1234> CUSTOMER_ID LAST FIRST TOTAL_B ----------- ---------- newbie1234> ---------- ------- 1552 Ira Sally 570.25

Go back to basics - I think yor a long way off the mark with what your trying to do. You are trying to run before you can walk. Get comfortable with doing basic queries, inserts and updates. (Basic DML). Then read about creating tables etc (Basic DDL). Often the best way to start is get a very general query together and then start refining it until you get what your after.

Some hints -

Forget about creating the table. Start by trying to define an sql query which gives your data in the format you want as just a query with a column for each data item you are after.
Read up about joins and how to select from multiple tables
Check out the list of functions, such as sum() and how to use them in a query.
Check out the CREATE TABLE command - in particular, CREATE TABLE blah AS ......

Good luck.

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Mon Jul 07 2003 - 03:41:27 CDT