SQL help

[cheyenne1212]

Hey guys, I need a little help with a SQL problem.

I have two tables

                                      |----------------
--------------|                    |  CAR          |
| PERSON   |                   |                   |
|                 |                   |*carid         |
|*personid  |                  |carcost       | 
|persfname |                  | carsell       |
|perslname |                  |  cartype     |
|---------------                  -----------------

the question asks

list details of all sales showing the gross profit (selling price minus cost price.)

I know I need

Select person.*, car.*
from person, car

Now what I'm not sure of is the minus part. I know how to do AVG, SUM, COUNT etc, but am not sure how to subtract the carsell from the carcost.

I'm pretty sure it needs to be in the select statement. Just need a little reminder here.

thanks.

for some reason the | are all screwed up.

[hiddeninclouds]

DECLARE
--Explicit cursor for select statement
CURSOR info_cursor IS
*select statement here*

-- var to hold gross profit
gross_profit NUMBER;
-- var to hold person's name
person_name VARCHAR2;
-- var to hold selling price
sell_price NUMBER;
-- var to hold cost price
cost_price NUMBER;

BEGIN
OPEN info_cursor;
LOOP
FETCH info_cursor INTO person_name, sell_price, cost_price;
EXIT WHEN info_cursor%NOTFOUND;
gross_profit := sell_price - cost_price;
DBMS_OUTPUT.PUT_LINE('The gross profit for ' || person_name || ' is ' || gross_profit);
END LOOP;
CLOSE info_cursor;
END;

Hope this helps

[jdenny]

                            |--------------|
|-----------|               |  CAR         |
| PERSON    |               |              |
|           |               |*carid        |
|*personid  |               | carcost      | 
| persfname |               | carsell      |
| perslname |               |  cartype     |
|-----------|               |--------------|

First, shouldn't you have
1. a "shared" field (column) that exists in both tables, -- or --
2. another table that has the personid and carid fields (probably with some other field like salesdate, etc)
in order to join (i.e. associate) person with car?

Second, I haven't tried this (modify it as necessary) but it should work:

1. Assuming the personid field also exists in car table

select person.personid, person.perslname, car.carid, car.cartype, car.sell - car.cost 
from person, car
where person.personid = car.personid

2. Assuming there is a sales table with personid, carid, salesdate fields

select person.personid, person.perslname, car.carid, car.cartype, car.sell - car.cost 
from person, car, sales
where sales.personid = person.personid and sales.carid = car.carid

Peace always,
<jdenny>

[cheyenne1212]

Thats what I was thinking to. I know that there has to be a field that is the same in both tables, but they didn't have one. lol

So I just use the - sign to subtract?

I just couldn't remember what it was that I used.

[l3aDmOnKeY]

First, shouldn't you have
1. a "shared" field (column) that exists in both tables, -- or --
2. another table that has the personid and carid fields (probably with some other field like salesdate, etc)
in order to join (i.e. associate) person with car?

Ya never forget that if two tables are going to use similar info someway or another to create relationships between the tables.

PK----- primary Key
FK----- Forgiegn Key

the forgien key will point to the table that it relates too.

--------------------------------------------------------------------------------
|PERSON|
|*personid |
|persfname|
| perslname|

|CAR|
|*carid |
| carcost |
| carsell |
| cartype |
|personid-| <------- forgein key that relates to the PK in the persons table