SQL - Day 2

Alright, so Day 1 completed.

Today let's deep dive into various other useful functionalities of SQL.

AGGREGATE FUNCTIONS

SELECT MIN(year) from movies; //finds the minimum year

SELECT COUNT(*) from movies; //counts number of rows

SELECT AVG(rankscore) from movies;

SELECT SUM(rankscore) from movies;

GROUP BY

What is the number of movies that came each year?

SELECT year,COUNT(year) FROM movies GROUP BY year;

Now, if you want this sorted by count, what should you do?

SELECT year,COUNT(year) year_count FROM movies GROUP BY year ORDER BY year_count DESC; //Using alias "year_count"

It is often used with min, max, sum, avg, count

HAVING CLAUSE

SELECT year,COUNT(year) year_count FROM movies GROUP BY year HAVING year>2000 ORDER BY year_count DESC ;

Having is usually used by Group by - not mandatory though

Having without group by is similar to where condition

Having vs Where?

HAVING is applied on groups and WHERE is applied on individual rows

HAVING is applied after grouping and WHERE is applied before groups are created

Order of execution:

1. Where condition

2. Group By

3. Aggr function (Count, min, max etc)

4. Having clause

Day 2 done, yay!