Re: Newbie on SQL

newbie1234 wrote:

> I am new to SQL, and taking a army course...I am looking for any help or
> guidance that you could provide for my question. I have completed a
> small portion of the problem (see below)and unfortunatly, this is an
> open ended question.
>
> There are two SQL statements. One is to create a table called Balance
> and the other is to load the table balance from two existing tables:
> Order and Customer. There are errors in both SQL statements.
>
> I have tried to tackle the SQL problem by dissecting the statement and
> research the key concepts like: CREATE, INSERT, SELECT, Joins and field
> attributes. I am somewhat confused about the concwepts.
>
> I can not run this code in Access (due to the create statement) and no
> access to Oracle etc..If you could possible assist me in any way so that
> I would be able to better understand these SQL key concepts, would be
> greatly welcomed.
>
> I am trying to "Pin-point" the problems with the code!
>
> My objective is to create a table called balance that includes the
> customer ID, last name, first name, and the customer’s total balance
> (the sum of price for all orders).
>
> See the tables below: customer and order
>
> CUSTOMER
> CUSTOMER_ID LAST FIRST STREET CITY STATE ZIP
> ----------- ------- ------- --------- ------- ----- ------
> 124 Martin Dan 418 Pine Grant MI 49219
> 1552 Adams Joan 3435 Elm Harper TN 39805
> .. ... ... ... ... ... ...
> ORDER
> ORDER_NUM CUSTOMER_ID ORDER_DATE PRICE
> --------- ----------- ---------- -------
> 10024 5642 12/8/1999 501.89
> 10025 124 30/9/1999 215.95
> 10026 4542 11/1/2000 570.25
> 10027 124 12/2/2000 420.60
> .. ... ... ...
>
> The result should be a table that looks like this:
> BALANCE
> CUSTOMER_ID LAST FIRST TOTAL_BALANCE
> ----------- ------- ------- -------------
> 1552 Adams Joan 845.56
> 5642 Collins Chip 760.01
> 4542 Ira Sally 570.25
> .. ... ... ...
>
> Unfortunately, there are several mistakes. Tom wants your team to
> find the mistakes. Begin by examining the following text in the
> SQL*Plus window:
> The following SQL commands have errors and are not yielding the correct
> result. Modify the following code to get the desired table above.
>
> SQL>CREATE TABLE balance
> 2(customer_id NUMBER(7), last CHAR(10), first CHAR(10),
> total_balance CHAR(7));
>
> SQL>INSERT INTO balance
> 2 SELECT customer.customer_id, customer.last, customer.first,
> sum(customer.price)
> 3 FROM customer, orders
> 4 WHERE customer.customer_id = order.customer_id
>
> Possible Solution:
> SQL> create table customer (customer_id number(7),last char(10),first
> SQL> char(10));
>
> Table created.
>
> SQL> insert into customer values (1552, 'Ira', 'Sally');
>
> 1 row created.
>
> SQL> create table orders (order_id number(10),customer_id
> SQL> number(7),price number);
>
> Table created.
>
> SQL> insert into orders values (1,1552,570.25);
>
> 1 row created.
>
> SQL> CREATE TABLE balance
> 2 (customer_id NUMBER(7), last CHAR(10), first CHAR(10),
> total_balance CHAR(7));
>
> Table created.
>
> SQL> insert into balance
> 2 select c.customer_id,c.last,c.first,sum(o.price) from customer
> c, orders o
> 3 WHERE c.customer_id = o.customer_id
> 4 GROUP BY c.customer_id,c.last,c.first;
>
> 1 row created.
>
> SQL> select * from balance;
>
> CUSTOMER_ID LAST FIRST TOTAL_B
> ----------- ---------- ---------- -------
> 1552 Ira Sally 570.25
>
> Any help in this is greatly appreciated...thanx newbie
>
> --
> Posted via http://dbforums.com

Tried stepping through what you posted and you have more logical errors than are worth the pursuit.

For example: You keep trying to sum the price and yet there is no price column in any table.

Take a look at what you are trying to do and carefully work out that the tables and columns exist to support it.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)