Skip to content

Introduction to SQL

Here you can access the books table used in the course.

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameas
books
variable
-- Add your own queries here
SELECT * 
FROM books

Explore Datasets

Use the books table to explore the data and practice your skills!

  • Select only the title column.
  • Alias the title column as book_title.
  • Select the distinct author names from the author column.
  • Select all records from the table and limit your results to 10.

Intermediate SQL

# Filtering Records

# Where

# >    <   =   >=   <=     <> (not equal))

# OR , AND , BETWEEN

# using sql in text 

# LIKE -> ('Ade%') = Adel; Adelaide; ... % match zero, one , or many character    ;  ('Ev_') = Eve _ match a single character

# NOT LIKE
# IN
-- Select the names that start with B
WHERE name LIKE ('B%')
-- Select the names that have r as the second letter
WHERE name LIKE ('_r%')
# Null values

WHERE field IS NULL
WHERE field IS NOT NULL

Aggregate Functions

AVG(), SUM(), MIN(), MAX(), COUNT()

WHERE

Round(number_to roung, decimal_places)

Execution -. FROM -> WHERE -> SELECT -> LIMIT.

Sorting and Grouping

Spinner
DataFrameas
df
variable
Order By column Asc;
Order By column DESC;

Groupng data
GROUP BY Column

The Sequence
Group by
having
order by

HAVING

WHERE filters individual records, HAVING filters grouped records

-- Select the country and distinct count of certification as certification_count
select country, count(distinct certification) as certification_count
from films
-- Group by country
group by country
-- Filter results to countries with more than 10 different certifications
having count(distinct certification) > 10
# O having precisa reescrever o código não dá para chamar o alias.
Spinner
DataFrameas
df1
variable
-- JOining Data in SQL

-- same name in two tables we can use -> USING(COLUMN)

-- One-to-many relationships
-- One-to-one relationships
-- many-to-many relationships

-- SELF JOIN
Self joins are very useful for comparing data from one part of a table with another part of the same table. Suppose you are interested in finding out how much the populations for each country changed from 2010 to 2015. You can visualize this change by performing a self join.
select p1.country_code, p1.size, p2.size as size2010, p2.size as size2015
-- Join populations as p1 to itself, alias as p2, on country code
from populations as p1
inner join populations as p2
on p1.country_code = p2.country_code
SELECT
FROM
UNION
SELECT
FROM
-- UNION UNION ALL

INTERSECT
except

Inner join or join
left join
right join
full join
cross join
semi join anti join
self join
union all
intersect
except
subqueries inside select
subqueries inside where
subqueries inside from
Spinner
DataFrameas
df2
variable
-- Intermediate
-- CASE
-- Case contains when, then and else statement, finished with end as alias

COUNT(CASE WHEN THEN END) AS ALIAS

Subqueries
	Comparing groups to summarized values
	Reshaping data
	combining data that cannot be joined
	
subqueries in where return only one column.

In SELECT
	Returns a single value
	used in mathematical calculations
‌
‌
‌