SQL Query questions

[Tekk]

Well how to make properly functioning SQL statements is harder than I expected. I'm having trouble with this one question in my homework:

Im supposed to find the group of customers who have reserved rooms that cost more than $250. The attatched diagram shows the relationships that are present. Im supposed to list Customer_ID, Stay_Number, Reservation_Date, Status_Code, Stay_Begin_Date, Stay_End_Date, Room_Number and Room_Rate.

Im guessing a nested Select might be appropriate here but Im having trouble with the logic of those still. I know how to pull out the room numbers with a rate of >= 250 and I know how to pull out the other info but its connecting them in the statement that Im having trouble with. Any help?

This is in Oracle 8i Lite btw

[Tekk]

hehe...also, tell me whats sloppy about this query. It kinda works and it fulfills the question but I was still wanting to get the room rates to add up all together. Meaning that each stay had multiple rooms and obviously each room had a different rate. So I wanted the stay numbers to just list the TOTAL COST of the stay (just add up all the cost of the rooms and show that final number). I thought SUM might work but it didn't.

[vass0922]

HEY!
You're cutting out the table name of the table with "Stay_Number" as the PK lol

Work with me here lol

If you can get that it would help a little bit
Until then I'll try to use a BS name

Mind you I dont' know oracle joins they're different than what I'm used to with T SQL joins but you should be able to get the idea

[vass0922]

Ok.. like I said this is in TSQL so the join syntax is slightly different. I'm using all INNER JOINs because you only want rooms to show up that the customer paid for.

Now the SUM function only works while using a GROUP.

SO for all records that are exact same it will group up and sum those records.

For instance here is what I came up with.
Your mileage may vary

SELECT Customer.CustomerID, Reservation.Stay_Number,
       Reservation.Reservation_Date, Reservation.Stay_Status,
       Reservation.Stay_Begin_Date, Reservation.Stay_End_Date,
       Room_Reserved.Room_Number, Room.Room_Number,
       SUM(Room.Room_Rate)
FROM Customer
  INNER JOIN Reservation
    ON Customer.Customer_ID = Reservation.Customer_ID
  INNER JOIN Room_Reserved
    ON Room_Reserved.Stay_Number = Reservation.Stay_Number
  INNER JOIN Room
    ON Room.Room_Number = Room_Reserved.Room_Number
GROUP BY Customer.CustomerID, Reservation.Stay_Number,
       Reservation.Reservation_Date, Reservation.Stay_Status,
       Reservation.Stay_Begin_Date, Reservation.Stay_End_Date,
       Room_Reserved.Room_Number, Room.Room_Number,
       Room.Room_Rate
HAVING Room_Rate >= 250

[Tekk]

Even tho the joins kinda confuse me more it still helped me find a different way to solve another problem that I thought I had already finished. Thanks Vas Knew you would be the first to reply..hehe. I'm sure I'll be posting again soon!

[vass0922]

I think
FROM Table2
INNER JOIN Table1
ON Table1.ColumnName = Table2.ColumnName

is just like Oracles

WHERE table1.ColumnName = Table2.COlumnName

You can do this in T-SQL as well, but it changes the execution of the query and makes it a little slower I do believe. The INNER JOIN syntax is preferred in sql server.

You could also change the query so it does use a subquery but don't think its necessary.. sometimes I can't SEE the structure as well until I've played with the data a bit.

SELECT Customer.CustomerID, Reservation.Stay_Number,
       Reservation.Reservation_Date, Reservation.Stay_Status,
       Reservation.Stay_Begin_Date, Reservation.Stay_End_Date,
       Room_Reserved.Room_Number, Room.Room_Number,
       SUM(Room.Room_Rate)
FROM Customer
  INNER JOIN Reservation
    ON Customer.Customer_ID = Reservation.Customer_ID
  INNER JOIN Room_Reserved
    ON Room_Reserved.Stay_Number = Reservation.Stay_Number
  INNER JOIN Room
    ON Room.Room_Number = Room_Reserved.Room_Number
GROUP BY Customer.CustomerID, Reservation.Stay_Number,
       Reservation.Reservation_Date, Reservation.Stay_Status,
       Reservation.Stay_Begin_Date, Reservation.Stay_End_Date,
       Room_Reserved.Room_Number, Room.Room_Number,
       Room.Room_Rate
WHERE Room_Number IN (SELECT Room_Number FROM Room WHERE Room_Rate >= 250)

Now what I'm not sure of is if you have to use WHERE or HAVING with subqueries
When you use GROUP BY typically you use HAVING... I've never seen it with a subquery though so dunno

[Tekk]

hehe...well I'll let you know what happens...I'll try to post my solution a little later tonight or tomorrow to give you an idea of what I was doing. I got it to work, whether its EXACTLY what the teacher wants or not is the question.

She's a little bit weird when it comes to the output too because she wanted us to use this "Column Date_Attribute Format A11" which I idiotically didn't do EVERY TIME I started SQL+ (apparently it has to be entered EVERY time SQL+ is started...lame as hell in my opinion) and apparently there couldn't be any wrapping on the page which was almost impossible based upon the length of the data....oh well!!!! Im not a big fan of this business anyway. Im excited for my "Multimedia applications for the web" class next quarter. Plus I get to learn VB/VBScript in Server App Developement class.

[vass0922]

Ugh.. I hated sql+ ... HATED it lol

The editor is CRAP ... best bet is to find a good text editor and hten just use command line to execute the sql.

That was my biggest beef with oracle was the editor.. I do realy like query analyzer for sql server it works pretty well (although could really use parentheses matching ) lol

Good luck
Stop on by when you need vb/vbs help

[Pexster]

For the column formatting, just put something like this above your SQL in a text file --

column STAY_ENDING_DATE heading "End Date" format a11

In your SQL, use

to_char(stay_ending_date, 'DD-MON-YYYY')

On top of everything, set your page and line sizes, e.g.

set pagesize 66
set linesize 120 (or however many you need)

Anyway, if you save all this stuff in a text file, you won't need to keep typing stuff every time you login. You'll just run the file from your prompt, as in --

SQL>@my_textfile.sql;

Sorry if I'm pointing out the obvious, but am not clear from some of your comments.