[SQL Intermdiate] Aggregate Functions - Part2

*DISTINCT
SELECT DISTINCT enables you to find the unique values in a particular column.

SELECT DISTINCT month
FROM table1

SELECT DISTINCT year, month
FROM table1

-> DISTINCT with COUNT
SELECT COUNT(DISTINCT month) AS unique_months
FROM table1

*CASE
-SQL case statement : if/then logic
-it must end with 'END' statement

SELECT player_name, year,
CASE WHEN year = 'SR' THEN 'yes'
            ELSE NULL END AS is_a_senior
FROM benn.college_football_players

*JOIN
Joining two tables and finding data from two tables with one query
-> Join is incredibly useful because keeping all the data in one table is impossible.

-> Aliases in SQL

SELECT teams.conference as conference,
AVG(players.weight) AS average_weight,
FROM table1 teams -- alias
JOIN table2 palayers -- alias
ON teams.school_name = players.school_name --  school_name is a column that is common in both                                                                                      tables : mappings
GROUP BY teams.conference
ORDER BY AVG(players.weight) DESC

ON teams.school_name = players.school_name
means
Join all rows from the players table on to rows in the teams table for which the school_name field in the players table is equal to the school_name field in the teams table.

SELECT *
FROM table1 players
JOIN table2 teams
ON teams.school_name = players.school_name

-> returns all the columns from both tables
-> If you want to return columns from one table, write
SELECT players.*

*INNER JOIN
- Multiple columns in one table with the same name
- Inner joins eliminate rows from both tables that do not satisfy the join condition set forth in the ON statement.
- Inner join is the intersection of the two tables

- Only find the shared values in both tables

SELECT players.*, teams.*,
FROM table1 players
JOIN table2 teams
ON teams.school_name = players.school_name
=> the results will simply show the exact same result set for both columns even it the two columns should contain different data

SELECT players.school_name AS players_school_name,
teams.school_name AS teams_school_name
FROM table1 players
JOIN table2 teams
ON teams.school_name = players.school_name
=> If you name two columns individually, two columns will be independent.

*OUTER JOIN
- In inner join, rows from either table that are unmatched in the other table are not returned.
- In outre join, unmatched rows in either table is returned.
1) LEFT JOIN : returns only unmatched rows from the left table
2) RIGHT JOIN : returns only unmatched rows from the right table
3) FULL OUTER JOIN : unmatched rows from both tables

-> Outer join is rarely used because switching
FROM table1
LEFT JOIN table2

produces the same result as below

FROM table2
LEFT JOIN table1

->LEFT JOIN and RIGHT JOIN can be written as LEFT OUTER JOIN and RIGHT OUTER JOIN respectively

* JOINS USING WHERE OR ON

SELECT companies.permalink AS companies_permalink,
acquisitions.company_permalink AS acquisitions_permalink
FROM table1 companies
LEFT JOIN table2 acquisitions
ON companies.permalink = acquisitions.company_permalink
AND acquisitions.company_permalink != '/company/1000memories'
ORDER BY 1

- AND works the same as WHERE in JOIN statement