#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Location
    Sweden
    Posts
    48
    Rep Power
    8

    Oracle SQL laboration help (school coure) Subquery and Join


    Hi,

    I have a introductory course in database systems (Oracle SQL) at my university and now when we have moved on to nested querys and joins I would very much appreciate some pointers form anyone who have spare time.

    In this lab I am supposed to display customer number, first name and last name of all customers that have ordered items from item group clothes or item group magazines. I have solved the lab using nested querys (it works anyway), but I am stuck at solving it using the inner join.

    Here is a diagram of the tables that we are to work with.


    And here is my nested query solution, please do leave a comment if this way is "wrong". I have a vague memory that the lecturer said that every time we need to "hop" to another table, it will cause one more query?
    Code:
    select cnr, fname, lname
    from customer
    where cnr in(
        select distinct cnr
        from customerorder
        where ordnr in(
            select ordnr
            from orderrow
            where itemnr in(
                select itemnr
                from item
                where ignr in (
                    select ignr
                    from itemgroup
                    where igname in('clothes','magazines')
                )
            )
        )
    );
    The output should look like this
    Code:
    CNR FNAME                ENAME
    --- -------------------- ---------
      3 carl                smith
      4 eric                jonsson
      6 maria               brown
    And if you know of a good Oracle PL/SQL book that is easy to grasp with good examples please leave a comment, I would like to complement the couse book (I find it to be more like a reference manual...).

    Regards,
    Marcus
    Last edited by Coldweather; October 11th, 2011 at 01:26 PM.

IMN logo majestic logo threadwatch logo seochat tools logo