Can you group by things that are not columns?

How do you add new columns to a table after creating it and using it for a while?

If there are no NULL values, will COUNT(*) and COUNT(col_name) return the same values?

Instead of using subqueries that return a table inside a query, isn't it more readable to put them outside of the query and set it to a variable (where tableName as () ) to refer to in the query?

"Can you explain the following reading example. I'm having trouble understanding why it's possible to list credit, movie, and person as p2. 

select distinct p1.name
from person as p1,credit,movie,person as p2
where p1.nm=credit.nm
    and credit.tt=movie.tt
    and movie.director=p2.nm
    and p2.name = 'Clint Eastwood';"

Can we go over the Clint Eastwood example that uses join? 

"I don't think I understand the ""-- actors who have been directed by Clint Eastwood,
-- implemented as a join"" example, can we please go over this in class? Thanks."

In the "Subquery versus Join" section of the reading, could you go over the first solution (the one using JOIN)? I am having trouble figuring where/what is being joined.

Can you give another example of group by?

I read over the page on aggregate functions but it doesn't look like there's one for subtraction even though there's an aggregate function for sum. Is there any reason why there isn't a function for subtraction?


Could you explain the code for Subquery vs. Join more? I'm kind of confused by what is distinct and what isn't and how that impacts the code.


In the query that looks for actors who have been directed by Clint Eastwood (implemented as a join), why do we need two aliases for the person table (person as p1, person as p2)?
"In the SUBQUERY vs. JOIN implementation of finding actors who have been directed by Clint Eastwood,
-- actors who have been directed by Clint Eastwood,
-- implemented as a join

use wmdb;

select distinct p1.name
from person as p1,credit,movie,person as p2
where p1.nm=credit.nm
    and credit.tt=movie.tt
    and movie.director=p2.nm
    and p2.name = 'Clint Eastwood';

Why do to check the person column twice in the from clause? When would we put the same column twice in the from clause? Why do we even need to check that movie.director = p2.nm when each movie has a unique director?"

"for something like 
'from person as p1,credit,movie,person  as p2', why does p1 appear twice?"

Could you explain the uniformity across a group concept? Specifically, this sentence: "When grouping, the columns should values that are uniform (the same) for every member of the group." Does this mean that columns need to have the same amount of values for each member in the group OR have the same values?


I'm still a bit confused about exists and not exists, can we go through more examples in class?

"SELECT region, avg(sales)
FROM sales_data
GROUP BY region;
For this example, how would the dates be arranged/aggregated?"

What are some good ways to debug the code, especially when inner queries are used?