Home » SQL & PL/SQL » SQL & PL/SQL » Nested Queries Type I and II
icon5.gif  Nested Queries Type I and II [message #238762] Sat, 19 May 2007 17:54 Go to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
I'm working through a practice exam, this final is this wed, and I have run into a bit of trouble when it comes to nested queries. I only have 4 questions on this subject, but they count for a lot and according to the teacher the questions on the exam will be similar. I've skimmed through the book and understand the first part of most questions since there just basic queries. Anyway I'm at a loss. The tables are attached. I'm not really having a problem displaying the column, but can't figure out the nested loops.


First question is, using a type II nested query list customer number, first name, last name, and city who has a balance > $150 and placed an order in Feb 2007.

I know it starts
SELECT CustNo, Custfirstname, Custlastname, Custcity
FROM Customer
then i get confused how to use the where clause with a nested loop.

The next question is similar to the first. Using a type one nested query list the product number, name, and price with a price greater than $150 that were ordered in Jan 2007 by customers with a balance > $400

I leave the last two off for now because if i can get help on these two then i should be able to figure out the last 2. Any help would be much appreciated.

[Updated on: Sat, 19 May 2007 19:06]

Report message to a moderator

Re: Help with practice exam! [message #238763 is a reply to message #238762] Sat, 19 May 2007 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
It would help you to get more meaningful responses if you actually read & followed the STICKY posts at the top of this forum.
Re: Nested Queries Type I and II [message #238765 is a reply to message #238762] Sat, 19 May 2007 19:10 Go to previous messageGo to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
fixed.
Re: Nested Queries Type I and II [message #238767 is a reply to message #238762] Sat, 19 May 2007 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>fixed.
Really?

Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.

Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.

Provide your expected result set.
Re: Help with practice exam! [message #238768 is a reply to message #238763] Sat, 19 May 2007 20:47 Go to previous messageGo to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
no i meant i fixed the title Smile
Re: Nested Queries Type I and II [message #238769 is a reply to message #238762] Sat, 19 May 2007 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
You're On Your Own!
Re: Nested Queries Type I and II [message #238770 is a reply to message #238769] Sat, 19 May 2007 21:30 Go to previous messageGo to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
I'm just confused on how to use the nested query to make a column between orderline and product that would give a customer's balance. This is what i have so far. If you want me to load some sample data i will.

SELECT CustNo, CustFirstName, CustLastName, CustCity
FROM Customer
WHERE EXITS
       (SELECT * FROM Order
        WHERE ordDate BETWEEN '1-FEB-2007' AND '31-FEB-2007');


If you don't want to help thats fine Sad
Re: Nested Queries Type I and II [message #238771 is a reply to message #238762] Sat, 19 May 2007 21:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
1) write a query that list customer number, first name, last name, and city who has a balance > $150

2) write a query for all orders placed in Feb 2007.

3) figure out necessary join condition to combine the 2 above

4) remember that strings are enclosed by single quote marks; such as:
SELECT 'This is not a date data type 31-FEB-2007' from dual

[Updated on: Sat, 19 May 2007 22:10] by Moderator

Report message to a moderator

Re: Nested Queries Type I and II [message #238835 is a reply to message #238771] Sun, 20 May 2007 07:58 Go to previous messageGo to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
Thanks for the input. I was stressing over one aspect of a table, but then realized customerbalance was included in customer. LoL.
Re: Nested Queries Type I and II [message #238845 is a reply to message #238835] Sun, 20 May 2007 09:40 Go to previous messageGo to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
Think i finally got it Smile

 
SELECT custno, custfirstname, custlastname, custcity, custbal
from ocustomer
where custbal > 150
and exists
(select custno
from oorder
where oorder.custno = ocustomer.custno
and orddate BETWEEN '1-FEB-2007' AND '19-FEB-2007') 
Re: Nested Queries Type I and II [message #238846 is a reply to message #238762] Sun, 20 May 2007 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
SELECT 'This is not a date data type 31-FEB-2007' from dual
You should ALWAYS use TO_DATE function
Re: Nested Queries Type I and II [message #238850 is a reply to message #238846] Sun, 20 May 2007 10:01 Go to previous messageGo to next message
Shinigami
Messages: 7
Registered: May 2007
Junior Member
I'm not sure how to use that function correctly, but I figured since the nested query is acting as a loop that inputing a date past the last date in the loop, in this case the 19th, would return an error.
Re: Nested Queries Type I and II [message #238864 is a reply to message #238850] Sun, 20 May 2007 12:35 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm? Why would it return an error? It would not if you added only one day to the last "date" boundary (see Anacedent's previous post for a reason my "date" is in quotes), but it would return an error if you added 11 days to it.

Anacedent said that '19-feb-2007' is A STRING, not a date. If your NLS settings were different, you'd get an error. When working with dates, always use the TO_DATE function:
AND orddate BETWEEN TO_DATE('1-FEB-2007',  'dd-mon-yyyy') AND
                    TO_DATE('19-FEB-2007', 'dd-mon-yyyy')
Previous Topic: Getting Errors in PL/SQL Collections code Execution
Next Topic: ERROR in format
Goto Forum:
  


Current Time: Sat Dec 03 14:24:59 CST 2016

Total time taken to generate the page: 0.12073 seconds