Intro to SQL

Tips

1. Use Aliasing.

2. Be Consisten when writing sql queries.

Selecting

we can peform arithmetic operations with select as printing simple statements.

select "Welcome TO SQL! <3" as result;

select (1 + 2) as result;

select (4.0 / 3.0) as result;

• SELECTING Single, or Multiple columns

SELECT title, release_year, country
FROM films;

-- or

SELECT * from films;

• SELECTING Distinct values

DISTINCT is used to returns DISTINCT (Not repeated values)

SELECT DISTINCT role from roles 

• SELECTING With Counting

select count(*) from people;
select count(birthdate) from people;
select count(distinct name) from poeple;

Filtering

1. Filtering Numerical Values

2. Filtering Textual Values

• = equal

• <> not equal

• < less than

• > greater than

• <= less than or equal to

• >= greater than or equal to

-- Get The number of films before 2000

select count(*) from films where release_year < 2000

-- Get the number of spanish films or english films before 2000 and after 1990 with the gross greater than 2000

select count(*) from films 
where (language = 'Spanish' or language = 'English')
and (release_year <= 2000 and release_year >= 1990 )
and gross > 2000

• BETWEEN

t's important to remember that BETWEEN is inclusive, meaning the beginning and end values are included in the results!

-- GET title and release of spanish or french filmes released between 1990 and 2000 inclusively with budget more than 20000

select title, release_year from films 
where release_year BETWEEN 1990 AND 2000 
and budget > 20000
and (language = 'Spanish' OR language = 'French')

• IN The IN operator allows you to specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions!

-- Get the title and release year of all films released in 1990 or 2000 that were longer than two hours. Remember, duration is in minutes

select title, release_year from films
where release_year IN (1990, 2000) and duration > 120

• IS NULL and IS NOT NULL Filtering based on null values, null represents a missing values

--GET the number of films which don't have a language associated with them.

select count(*) from films where language IS NULL

• LIKE and NOT LIKE

Filter using regular expression, this is the most powerful filtering methods, as it gives you many options and more control of filteration process

-- Get the names of all people whose names begin with 'B'.

select name from people where name is like 'B%'

-- Get the names of people whose names have 'r' as the second letter.

select name from people where name is like '_r%'

--Get the names of people whose names don't start with A

select name from people where name is not like 'A%'

Aggregate functions

• SUM(column)

• AVG(column)

• MIN(column)

• MAX(column)

-- GET average, longest, shortest, total duration of films

select sum(duration) as total, AVG(duration) as average, MIN(duration) as shortest, MAX(duration) as longest from films 

Commonly aggregate functions are combined with ~HERE Clause

-- Get the total amount grossed by all films made in the year 2000 or later.

select sum(gross) from filmes where release_year >= 2000

-- get the title and duration in hours and average duration for all films.

select title, duration / 60.0 as duration_hours, avg(duration) as average_duration from films 

-- GET the percentage of people who are no longer alive

select (deathdate) * 100.0 / count(*) as percentage_dead from people

-- GET the number of years between the newest films and oldest films

select MAX(release_year) - MIN(release_year) as difference from films

-- GET the number of decades the films table covers

select (MAX(release_year) - MIN(release_year)) / 10.0 as number_of_decades from films

Sorting And Grouping

we can sort by single, or multiple columns descending or ascending for the case of multiple columns, it will sort by the first one then the next one and so far.

The order of columns Is important

• ORDER BY (DESC or ASC)

-- Get the birth date and name for every person, in order of when they were born.

select birthdate, name from people order by birthdate

-- Get all details for all films except those released in 2015 and order them by duration on descending order

select * from films where release_year <> 2015 order by duration desc

• GROUP BY When sometimes to aggregate results Suppose you want to count the number of males and females of your company

select count(*) from employees
GROUP BY sex;

sex	count
male	15
female	19

NOTES

• Commonly, GROUP BY is used with aggregate functions like COUNT() or MAX(). Note that GROUP BY always goes after the FROM clause.

• SQL will return an error if you try to SELECT a field that is not in your GROUP BY clause without using it to calculate some kind of value about the entire group

Get the release year and average duration of all films, grouped by release year.

select release_year, avg(duration) from films group by release_year

Get the IMDB score and count of film reviews grouped by IMDB score in the reviews table.

select imdb_score, count(*) from films group by imdb_score

imdb_score	count
5.7	117
8.7	11
9	2

-- Get the release year and lowest gross earnings per release year.

select release_year, min(gross) from films 
group by release_year;

-- Get the release year, country, and highest budget spent making a film for each year, for each country. Sort your results by release year and country.

select release_year, country, max(budget) from films group by release_year, country order by release_year, country

• Having Aggregate functions can't be used on WHERE clause If we need to filter based on aggreagete funtion result, we need to use HAVING

-- write a query that returns the average budget and average gross earnings for films in each year after 1990, if the average budget is greater than $60 million.

SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross
FROM films
WHERE release_year > 1990
GROUP BY release_year
HAVING AVG(budget) > 60000000
ORDER BY avg_gross DESC;

-- Get the country, average budget, and average gross take of countries that have made more than 10 films. Order the result by country name, and limit the number of results displayed to 5. You should alias the averages as avg_budget and avg_gross respectively.

select country, avg(budget) as avg_budget, avg(gross) as avg_gross, country
from films
group by country
having count(title) > 10
order by country
limit 5