Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Ryan Leadenham
Ryan Leadenham
139 Points

The instruction for how to find the ID of the room with the max capacity is incorrect

SELECT *, MAX(capacity) FROM rooms; will give you a random row and then the maximum capacity value from the table. This query does not cause them to be tied together. Sometimes the random row that is pulled back HAPPENS to be the right one, but not always. The proper query would be:

SELECT * FROM rooms WHERE capacity = (SELECT MAX(capacity) FROM rooms);

This query can return multiple rows. ORDER BY and LIMIT can be applied to control this.

I'm not sure if this tutorial is using a special flavor of SQL, but it's my understanding that what I describe is the standard for SQL and I know for a fact it is true of MySQL and MariaDB. If this example uses something special, that should be noted.

1 Answer

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

You're right about not all SQL engines performing exactly the same. This course uses SQLite, which is what the supplied "SQL Playground" runs, and it reliably always returns the first row that has the value matching what the aggregate produces. And you're also right about seeing only one row even if there is more than one with the maximum value. But in this simple example table, there is only one. Try the exercise using the playground and you'll see the result is not random.

In addition to behaviors, databases also differ in keyword and function names. I find myself constantly translating as I move between SQLite, MySql, SQL Server, and Oracle. I believe this is mentioned in one or more of the courses, but then for simplicity the instruction is all intentionally compatible with SQLite, as it is provided in the playground and the challenges with the course.