Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

WHY ARE THESE INVALID SQL QUERIES WORKING?

Posted on 2010-09-20
5
Medium Priority
?
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
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…

721 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