Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

WHY ARE THESE INVALID SQL QUERIES WORKING?

Posted on 2010-09-20
5
Medium Priority
?
347 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 143

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 71

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

876 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