Intermediate SQL

COUNT() - counts the number of records with a value in a field

DataFrameas

df

variable

SELECT COUNT(birthdate) AS count_birthdates
FROM people;

DataFrameas

df1

variable

SELECT COUNT(name) AS count_names, COUNT(birthdate) AS count_birthdates
FROM people;

COUNT(*) counts all the records in a table

DataFrameas

df2

variable

SELECT COUNT(*) AS total_records
FROM people;

DISTINCT returns only unique values

DataFrameas

df3

variable

SELECT COUNT(DISTINCT birthdate) AS count_distinct_birthdates
FROM people;

LIMIT- limits how many results we return

OR, AND, BETWEEN

DataFrameas

df4

variable

SELECT *
FROM coats
Where color = 'yellow' OR length = 'short';

DataFrameas

df5

variable

SELECT *
FROM coats
WHERE color = 'yellow' AND length = 'short';

DataFrameas

df6

variable

SELECT *
FROM coats
WHERE buttons BETWEEN 1 AND 5;

OR operator - Use OR when you need to satisfy at least one condition

DataFrameas

df7

variable

SELECT title
FROM films
WHERE release_year = 1994
OR release_year = 2000;

AND operator - Use AND if we need to satisfy all criteria

‌
‌
‌