When doing operation on date, say birthdate, if we don't check for columns where birthdate is NULL. Will SQL output an error?

Nope; it will ignore NULL values unless the function is about nulls. It's pretty robust that way.

Are there any other aggregate functions that we might find useful? Also, can aggregate functions besides COUNT() only work with number values, or could MAX() and MIN() for example be used on strings? (and why would you want to do this anyway)

There are others. There's CONCAT() which can concatenate strings.

Min() works on strings. Try it on the names in WMDB person table:

select min(name) from wmdb.person;

See MySQL aggregate functions

What is the difference between WHERE and HAVING, and why can't we use WHERE to filter based on functions like avg()?

HAVING filters based on a property of the group, and individuals don't necessarily have those properties. For example, a single person does not have a property of "group size".

Last time, I mentioned this example: suppose we have a table of sales records for car dealerships. Each row records the date, amount, region, etc of that sale.

Suppose we (1) group by region and (2) choose regions where the total sales are over a million dollars.

A "total sales over a million" can't be a property of an individual sale, but it can be a property of a group of sales.

Could you go through GROUP more slowly in class, I'm a little confused about that part.

I'd be glad to. Let's use the sales example: Imagine (1) sorting the rows by region (2) iterating over them, computing aggregate values like total sales, and (3) reporting/returning one result per group.

We could equally well group

by month (what's the best month for sales?), or
by dealership (what dealerships are most successful) or
by agent (who sells the most dollar value of cars, or the most cars), or
by day of the week (what are the best days for sales?). Etc.

What is the difference between "in" and "exists"

The IN operator needs a single column of results. The exists operator can have any number of columns and is just interested in whether there are any rows.

I am very confused about the logic of subqueries. Can you step through an easy one and a hard one in class?

Sure. Let's look at the Clint Eastwood query

Is there an analogy to describe sub queries and relationships between nested sub queries, like the outer and inner query. I'm having trouble understanding their usage. Also can I know more about correlated sub queries?

The way that a SQL statement is phrased is not always the same as the way it is executed. We aim for expressivity, and subqueries can help with that. Many students find the subquery version of the Clint Eastwood query easier to understand than the JOIN version.

Also can I know more about correlated sub queries?

Sure. I have a new example that I haven't added to the reading, but I may someday. Let's go look at that now.

None, this is pretty clear!

Great!