Solved

WHY ARE THESE INVALID SQL QUERIES WORKING?

Posted on 2010-09-20
5
331 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 69

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

777 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