Intermediate SQL tutorial for you.

If you need the datasets used in this tutorial please reach out to me through email. Datasets are not free. 

 Count() --count the number of values in a field/column

Count(*) -- number of records in a table. 

Distinct() -- for all the unique values in a field. 

Count (distinct Birthdays) -- count the number of unique 

Select count(birthdate) as count_birthdate

from people; 

-- Count the languages and countries represented in the films table

Select count (language) as count_languages,

count (country) as count_countries

from films; 

-- Return the unique countries from the films table

select DISTINCT country from films; 

## Debugging SQL code

> misspelling

> incorrect Capitalization of data

> incorrect or missing punctuation in functions or data fields

##SQL order of execution

***Very Important for you to understand ***

1. From and Joins : since these two forms the basis of the query
2. Where : Filters out the rows
3. Group By : Grouping values based on the column specified in the Group By clause
4. Having : Filters out the grouped rows
5. Select
6. Distinct : Rows with duplicate values in the column marked as Distinct are discarded
7. Order By : Rows are sorted based on Order By clause
8. Limit, Offset : Finally the limit or offset is applied

Filtering with WHERE:

WHERE color = 'green'

Remember, this will be a single quotation.

Unlike python, this matters.

Operator	Description
<	less than / Before
>	greater than / After
<=	less than or equal to
>=	greater than or equal to
=	equal
<> or !=	not equal

-- Select film_ids and imdb_score with an imdb_score over 7.0

SELECT film_id,

        imdb_score

FROM reviews

WHERE imdb_score > 7.0;

 

-- Count the records with at least 100,000 votes

Select count (*) as films_over_100K_votes

from reviews 

where num_votes >= 100000;

-- Count the Spanish-language films

Select count(language) as count_spanish

from films

where language = 'Spanish'; 

### Multiple criteria operator

AND. OR . Between . . . .

>>>if your query has multiple criteria operators with multiple

clauses containing Criteria Operators,

don't forget to use double parenthesis.

Example:

SELECT title

FROM films

WHERE (release_year = 1994 OR release_year = 1995)

    AND (certificate = 'PG' OR certificate = 'R');

>>> Between 1994 and 2000, is inclusive of the years.

>>> Between is more powerful and behaves like a normal function,

independent of other functions

>>> SELECT title

FROM films

WHERE release_year

BETWEEN 1994 AND 2000 AND country = 'UK';

-- Select the title and release_year 

for all German-language films released before 2000

and then Update the query to see all 

German-language films released after 2000

SELECT title,

    release_year

FROM films

WHERE release_year < 2000 AND language = 'German';

SELECT title, release_year

FROM films

WHERE release_year < 2000

    AND language = 'German';

-- Select all records for German-language films released after 2000

 and before 2010

Select * 

From films

Where (release_year > 2000 and release_year < 2010)

and (language = 'German');

-- Find the title and year of films from the 1990 or 1999

SELECT title, 

    release_year

from films

WHERE release_year = 1990 OR release_year = 1999;

-- Add a filter to see only English or Spanish-language films

SELECT title, release_year

FROM films

WHERE (release_year = 1990 OR release_year = 1999)

AND (language = 'English' OR language = 'Spanish');

SELECT title, release_year, gross

FROM films

WHERE (release_year = 1990 OR release_year = 1999)

    AND (language = 'English' OR language = 'Spanish')

-- Filter films with more than $2,000,000 gross income

    AND gross > 2000000;

Difference in Between and Before-After:

-- Select all records for German-language films released after 2000

 and before 2010

Select * 

From films

Where (release_year > 2000 and release_year < 2010)

and (language = 'German');

-- Select the title and release_year for films released between 1990 and 2000

Select title

    release_year

FROM films

WHERE release_year

BETWEEN 1990 AND 2000;

FILTERING TEXTS:

LIKE, NOT LIKE, IN >>Your three bosses.

% zero, one or many characters

_ means single character

WHERE name LIKE 'Ade%'

WHERE name LIKE 'Ev_'

WHERE name LIKE '_ _t%'

WHERE release_year IN(1920, 1930, 1940)

-- read this as release_year column contains any of 1920, 1930, 1940

-- this is similar to writing 3 OR conditions

-- Select the names that start with K

SELECT name

FROM people

WHERE name LIKE 'K%';

SELECT name

FROM people

-- Select the names that have r as the second letter

WHERE name LIKE '_r%'; 

SELECT name

FROM people

-- Select names that don't start with A

WHERE name NOT LIKE 'A%'; 

-- Find the title and release_year for all films over two hours in length released in 1990 and 2000

Select title, 

release_year

FROM films

WHERE duration > 120

AND release_year IN (1990, 2000); 

-- Find the title and language of all films in English, Spanish, and French

SELECT title,

language

FROM films

WHERE language IN ('English', 'Spanish', 'French'); 

-- did you notice that I have put all the

all the language in single quote?

-- Find the title, certification, and language all films certified NC-17 or R that are in English, Italian, or Greek

SELECT title,

certification,

language

FROM films

WHERE certification IN('NC-17', 'R')

AND language IN('English', 'Italian', 'Greek');

SELECT COUNT(DISTINCT title) AS nineties_english_films_for_teens

FROM films

-- Filter to release_years to between 1990 and 1999

WHERE release_year BETWEEN 1990 AND 1999

-- Filter to English-language films

    AND language = 'English'

-- Narrow it down to G, PG, and PG-13 certifications

    AND certification IN('G', 'PG', 'PG-13');

NULL values will make you go CRAZY

if you are new:

IS NULL / IS NOT NULL

-- List all film titles with missing budgets

SELECT title as no_budget_info

FROM films

WHERE budget IS NULL; 

SELECT COUNT(*) AS count_language_known

FROM films

WHERE language IS NOT NULL; 

COUNT() VS IS NOT NULL:

Select count(rookies)

from humans

where rookies IS NOT NULL;

Count (distinct Birthdays) -- count the number of un