 Nested Queries Type I and II [message #238762] |
Sat, 19 May 2007 17:54  |
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: Nested Queries Type I and II [message #238771 is a reply to message #238762] |
Sat, 19 May 2007 21:42  |
 |
BlackSwan
Messages: 26766
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 #238850 is a reply to message #238846] |
Sun, 20 May 2007 10:01 |
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 |
 |
Littlefoot
Messages: 21823
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') |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
