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?
select cnr, fname, lname
where cnr in(
select distinct cnr
where ordnr in(
where itemnr in(
where ignr in (
where igname in('clothes','magazines')
The output should look like this
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...).