• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

WHY ARE THESE INVALID SQL QUERIES WORKING?

Hi-  I'm wondering if anyone can explain to me why these functions are working correctly with the mySQL command line?  They were given as examples of invalid SQL but I tested them out w/ some fake data and I'm getting correct answers.

SCHEMA:
Hotel(hotelNo, hotelName, city)
Room(roomNo, hotelNo, type, price)
Booking(hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest(guestNo, guestName, guestAddress)

CREATE VIEW HotelBookingCount(hotelNo, bookingCount) AS
SELECT h.hotelNo, COUNT(*) FROM Hotel h, Room r, Booking b
WHERE h.hotelNo=r.hotelNo AND r.roomNo=b.roomNo
GROUP BY h.hotelNo;


Functions:
a) SELECT MIN(bookingCount) FROM HotelBookingCount;
should be Invalid – bookingCount is based on an aggregate function, so cannot be used within another aggregate function. (according to the textbook)
b) SELECT COUNT(*) FROM HotelBookingCount;
should be invalid for the same reason
c) SELECT hotelNo FROM HotelBookingCount WHERE bookingCount>1000;
should be Invalid – bookingCount is based on an aggregate function, so cannot be used within WHERE clause. (according to the textbook)


they all compiled & returned correct answers.


0
xanabobana
Asked:
xanabobana
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a) is not incorrect, because from that point of view (aka outside the view), it's not longer an aggregate function/column.b) same as for a)c) same as for a)to explain: as you use the VIEW in the FROM clause, all the "aggregates" are no longer at the same "level"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what you would have as "invalid" would be like this:a) select min(sum(A)) from yourtableb) select count(sum(B)) from yourtablec) select sum(B) x from yourtable where x > 100
0
 
xanabobanaAuthor Commented:
so perhaps the textbook means that they would be invalid if written out based on the underlying tables?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what it "means" is that you cannot combine aggregated functions on the same level.

you could write, for example, like this, where the column "x" is "created" in a subquery (inline view):

select max(x)
  from  ( select min(col1) x from yourtable group by col2 )

Open in new window

0
 
QlemoDeveloperCommented:
Creating views has been a heavily used feature in the "early days" to get around the "no nested aggregated functions" restriction. Nowadays most DBMS allow for CTE (Common Table Extension) and/or "ad-hoc views".
A CTE is (simplified) a temporary view defintion for the scope of a query:

with x as (select a, sum(b) from c group by a)
select * from x;

Ad-hoc views are similar, but constructed in the FROM clause:
select * from (select a, sum(b) from c group by a) x;

Both allow for a complex SQL including grouping, which you can access just like any other table. Creating a view is exactly the same, but done permanently, while the code shown above defines temporary views only available in the scope of the (single) SQL.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now