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
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
