Solved

WHY ARE THESE INVALID SQL QUERIES WORKING?

Posted on 2010-09-20
5
326 Views
Last Modified: 2012-05-10
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
Comment
Question by:xanabobana
  • 3
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33719764
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33719778
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
 

Author Comment

by:xanabobana
ID: 33719822
so perhaps the textbook means that they would be invalid if written out based on the underlying tables?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33719858
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 33720624
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now