SQL help
Results 1 to 5 of 5

Thread: SQL help

  1. #1
    Senior Member
    Join Date
    Feb 2003
    Location
    Memphis, TN
    Posts
    3,747

    SQL help

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

    I have two tables

    Code:
                                          |----------------
    --------------|                    |  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

    Code:
    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.
    =

  2. #2
    Senior Member
    Join Date
    Dec 2003
    Location
    LA, CA
    Posts
    292
    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
    A mind full of questions has no room for answers

  3. #3
    Senior Member
    Join Date
    Jul 2002
    Posts
    339

    Re: SQL help

    Code:
                                |--------------|
    |-----------|               |  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
    Code:
    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
    Code:
    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>
    Always listen to experts. They\'ll tell you what can\'t be done and why. Then go and do it. -- Robert Heinlein
    I\'m basically a very lazy person who likes to get credit for things other people actually do. -- Linus Torvalds


  4. #4
    Senior Member
    Join Date
    Feb 2003
    Location
    Memphis, TN
    Posts
    3,747
    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.
    =

  5. #5
    Senior Member
    Join Date
    Sep 2001
    Posts
    193
    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
    [shadow]l3aDmOnKeY[/shadow]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •