Quiz

Could you please explain the first question in class if time allows?
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.
In the HAVING section of the Groups and Subqueries reading, the example says we are finding sales averages by month, but the query uses SUM (sales). I was wondering why it uses SUM instead of AVG(sales)?

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.)
My question is since WHERE and HAVING only differ because HAVING can filter by properties only groups have while WHERE cannot, can we still use a non-group-only boolean (that would typically be in a WHERE instead) in a HAVING line? And is there any reason not to?
So, you mean filtering in the HAVING instead of WHERE, like:
```
    SELECT count(*) FROM person
    GROUP BY month(birthdate)
    HAVING name like "A%";
```
versus
```
    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;
```
versus
```
    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.
What is a good way of visualizing subqueries? I think I mostly understand conceptually, but I would like to see a visual representation.

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.
For the query (Actors that have at least 2 actor credits), I am still very confused as to why we need the name when we didn't even end up using it in the subquery. Could you explain how just by having it in the first line helps with the situation where 2 actors have the same name and 2 actor credits?
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.
In the reading, you say subqueries and joins do many of the same things. Is there anything a subquery can do that a join absolutely cannot do, i.e. did the addition of subqueries increase the expressiveness of the language?

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!
I am confused by the EXIST and NOT EXIST example in the reading and do not understand how they work.

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.
Is there a limit on how many nested queries we can have inside each other?

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.)
Could you please go over CTEs? Thank you!
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.
Can you explain more about the correlated subquery and how it uses the outer query.

Yes, we have an example in class today.
I don't have any question! (Several like this)

Great!

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

nm	name
17	Ashley Yang
23	Ashley Yang
38	Awkwafina