Week 3 - SQL problem 2

Part 1

If we want to know which site has the largest area, it’s tempting to say

SELECT Site_name, MAX(Area) FROM Site;

Wouldn’t that be great? But DuckDB gives an error. And right it should! This query is conceptually flawed. Please describe what is wrong with this query. Don’t just quote DuckDB’s error message— explain why DuckDB is objecting to performing this query.

To help you answer this question, you may want to consider:

• To the database, the above query is no different from

  • SELECT Site_name, AVG(Area) FROM Site
  • SELECT Site_name, COUNT(*) FROM Site
  • SELECT Site_name, SUM(Area) FROM Site

  In all these examples, the database sees that it is being asked to apply an aggregate function to a table column.

• When performing an aggregation, SQL wants to collapse the requested columns down to a single row. (For a table-level aggregation such as requested above, it wants to collapse the entire table down to a single row. For a GROUP BY, it wants to collapse each group down to a single row.)

Part 2

Time for plan B. Find the site name and area of the site having the largest area. Do so by ordering the rows in a particularly convenient order, and using LIMIT to select just the first row. Your result should look like:

┌──────────────┬────────┐
│  Site_name   │  Area  │
│   varchar    │ float  │
├──────────────┼────────┤
│ Coats Island │ 1239.1 │
└──────────────┴────────┘

Please submit your SQL.

Part 3

Do the same, but use a nested query. First, create a query that finds the maximum area. Then, create a query that selects the site name and area of the site whose area equals the maximum. Your overall query will look something like:

SELECT Site_name, Area FROM Site WHERE Area = (SELECT ...);