Sure. Answering questions is an important use of time, so I'm glad to do it.

The question was:

If we had a table that stored data for a calendar, one row for each day, and we GROUP BY month, the result would have this many rows:

* 365 (366 in a leap year)
* 12
* 365/12
* 1
        

This is really a question about what happens when we use GROUP BY, which is that we get one result per group. In this case, we are grouping by month, and there are 12 months in the year, so we get 12 results, regardless of how many rows are in our table.

The second query is for the second example, which is "what was the total sales for each month?"

(I've revised the reading to make that more clear, since the two examples are a bit further up the document.)

So, you mean filtering in the HAVING instead of WHERE, like:

    SELECT count(*) FROM person
    GROUP BY month(birthdate)
    HAVING name like "A%";

    SELECT count(*) FROM person
    WHERE name like "A%";
    GROUP BY month(birthdate)

The second would mean something like "how many people whose names start with A" were born each month"

The first I can't make any sense of, because once we group, there won't be a meaningful "name" column, since the columns in a group result need to make sense for the group as a whole.

But maybe you mean a column that does survive the grouping, such as the month number:

    SELECT count(*) FROM person
    GROUP BY month(birthdate)
    HAVING month(birthdate) > 6;

    SELECT count(*) FROM person
    WHERE month(birthdate) > 6;
    GROUP BY month(birthdate)

These mean pretty much the same thing: how many people were born each month for birthmonths after June.

But implementationally, it makes sense to discard all the people born in the first half of the year before doing the grouping.

The query optimizer would probably to the right thing with both, but (1) that's not your only audience and (2) let's make its job easier when possible.

Interesting question. I guess I think of them as like a function call: I put the current processing on hold, go process the subquery which returns a set of data (usually table-like, but sometimes a list or a scalar), and then I continue.

So, however you visualize function calls is probably a good way to think about subqueries.

Great question. Here's the link to the context: actors with at least two credits. And here's the query:

select nm, name
from person
where nm in (
    select nm
    from credit
    group by nm
    having count(*) >= 2);

So, let's imagine we have two actors, both named Ashley Yang. One is NM=17 and the other is NM=23.

Now, suppose the credits table looks like this:

tt	nm
783	17
784	17
785	23
786	23
781	38
782	38

These are six different movies (different TT values). We don't need to know the actors names to know that all three of these actors have (at least) two acting credits each, because each of them appeared in two of these six movies.

So the subquery returns a list of (17, 23, 38 ...)

The outer query returns all the desired information about those actors, like their name. We could include their birthday and other data in the outer query, but the inner query really gets us the list of *people* that we want.

And the result might look like:

nm	name
17	Ashley Yang
23	Ashley Yang
38	Awkwafina

So, the answer is that we don't need the name for the subquery, but we want it in the results.

Important point: SQL is not executed line-by-line the way Python is. The query is parsed as a whole, turned into an execution plan, and then executed.

I didn't express that very well. It's true that MySQL didn't introduce subqueries until version 4.x, and it was not crippled before then. There was still the rest of the language, including grouping, aggregation and such, not just joins.

So, subqueries generally don't introduce a fundamentally new way of relating tables. Many subqueries can be rewritten using joins, grouping, aggregation, and other existing SQL operations. Subqueries mainly give us another—and often much more natural—way to express those computations.

Thanks for that question!

Let's take a look at it: exists and not exists

The key is that the inner query finds the acting credits for a particular person, the one from the outer query.

The EXISTS/NOT EXISTS just cares about whether that subquery is empty or not.

There's no specified limit in MySQL. Microsoft's SQL Server specifies a limit of 32. (Yikes!)

But long before you hit that limit, your head will hurt.

CTEs and such may be more comprehensible and yield better performance.

(I once wrote a working query with about 10 CTES; I was pretty pleased with myself.)

Sure, I'd be glad to. We just went over the one from the reading in the main lecture.

Here's another example I stole from datacamp.com cte

-- Standard SQL: Cleaner with CTE
WITH OrderDetails AS (
    SELECT 
        o.OrderID, 
        c.CustomerName, 
        p.Price, 
        o.Quantity, 
        o.OrderDate
    FROM Orders o
    JOIN Customers c 
        ON o.CustomerID = c.CustomerID
    JOIN Products p 
        ON o.ProductID = p.ProductID
    WHERE EXTRACT(YEAR FROM o.OrderDate) = 1954
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

The CTE results are orders from customers, such as:

            456 | Wile E. Coyote | 8.99 | 2 | 9/11/1954 
        

The price comes from the Products table, linked by the ProductID (not listed)

The customer name comes from the Customer table, linked by the CustomerID (not listed)

The quantity and date come from the Order table.

We are only interested in orders from 1954

Finally, given that, we group by CustomerName (is it unique? Maybe we should group by CustomerID), sum the orders, and report customers who spent more than $1000.

Yes, we have an example in class today.

Great!