Codecademy SQL
SQL
Codecademy SQL
Manipulation
Data Type Includes:
SELECT from a Table
Set LIMIT
CREATE TABLE
INSERT to a Table
Add Coln
UPDATE Value
Query
Comparison Operators
LIKE Operator
BETWEEN Operator
AND and OR Operator
ORDER
Aggregate Functions
COUNT
GROUP BY
Having
SUM
MAX and MIN and Average
DISTINCT
CASE
Multiple Tables
Primary Keys
Select from Multiple tables
Join (Combine)
Use AS to Rename
UNION
Manipulation
Clauses perform specific tasks in SQL. By convention, clauses are written in capital
letters. Clauses can also be referred to as commands.
Data Type Includes:
1. Integer: a positive or negative whole number
2. Text: a text string
3. Date: the date formatted as YYYY-MM-DD for the year, month, and day
4. Real: a decimal value
SELECT from a Table
* stands for all. e.g. select all from contacts .
SELECT * FROM contacts; /* will show in result */
SELECT * FROM celebs; /* will show in result */
SELECT DISTINCT genre FROM movies; /* each value only select once
*/
Display colns w/ different names
SELECT year AS "Year", west AS "West"
FROM tutorial.us_housing_units
SELECT year AS m_year, west AS west_bala /* new parameter */
FROM tutorial.us_housing_units
Set LIMIT
To save space
SELECT *
FROM tutorial.us_housing_units
LIMIT 100
CREATE TABLE
Create a table named such in the database.
CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);
INSERT to a Table
Add 1 more row to the end.
INSERT INTO celebs (id, name, age) VALUES (2,'Beyonce Knowles', 3
3);
INSERT INTO celebs (id, name, age) VALUES (3, 'Jeremy Lin', 26);
INSERT INTO celebs (id, name, age) VALUES (4, 'Taylor Swift', 26);
SELECT * FROM celebs;
SELECT name FROM celebs; /* a closer look at name */
Add Coln
Add a coln that contains value type TEST
ALTER TABLE celebs ADD COLUMN twitter_handle TEST;
UPDATE Value
WHERE indicates condition
UPDATE celebs SET age=22 WHERE id=2;
UPDATE celebs SET twitter_handle = '@taylorswift13' WHERE id = 4;
/* update */
DELETE FROM celebs WHERE twitter_handle IS NULL; /* delete */
Query
Comparison Operators
The same as C++, except we use = in SQL instead of == .
<> functions the same as != and it is more widely used so <> is recommended.
SELECT * FROM movies WHERE imdb_rating > 8;
LIKE Operator
Works like regular expression
The _ means you can substitute any (one) individual character here without
breaking the pattern.
SELECT * FROM movies WHERE name LIKE 'Se_en';
% is a wildcard character that matches zero or more missing letters in the pattern.
SELECT * FROM movies WHERE name LIKE 'a%'; /* any string starts wit
h a or A*/
SELECT * FROM movies WHERE name LIKE '%man%'; /* any string contain
s man (not case sentisive)*/
BETWEEN Operator
This statement filters the result set to only include movies with names that begin
with letters A up to but not including J.
SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';
SELECT * FROM movies WHERE year BETWEEN 1990 and 2000;
AND and OR Operator
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre =
'comedy';
SELECT * FROM movies WHERE genre = 'comedy' OR year < 1980;
ORDER
SELECT * FROM movies ORDER BY imdb_rating DESC;
SELECT * FROM movies ORDER BY imdb_rating ASC;
SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3; /* only show
the first 3 rows */
Aggregate Functions
COUNT
COUNT() is a function that takes the name of a column as an argument and counts the
number of rows where the column is not NULL .
SELECT COUNT(*) FROM fake_apps;
Exercise: find the non-NA count of each coln.
SELECT COUNT(date) as dates,
COUNT(year) as year,
COUNT(month) as month,
COUNT(open) as open,
COUNT(high) as high,
COUNT(low) as low,
COUNT(close) as close,
COUNT(id) as id
FROM tutorial.aapl_historical_stock_price
GROUP BY
SELECT price, COUNT(*) FROM fake_apps GROUP BY price;
Here we need price and the count of each group of price.
SELECT price, COUNT(*) FROM fake_apps WHERE downloads > 20000 GROUP
BY price;
Ex: average daily price change of each year
SELECT AVG(close-open), year
FROM tutorial.aapl_historical_stock_price
GROUP BY year
ORDER BY year
Having
The WHERE clause wont sometimes since it doesnt allow you to filter on aggregate
columnsthats where the HAVING clause comes in
SELECT year,
month,
MAX(high) AS month_high
FROM tutorial.aapl_historical_stock_price
GROUP BY year, month
HAVING MAX(high) > 400
ORDER BY year, month
SUM
SELECT SUM(downloads) FROM fake_apps;
SELECT category, SUM(downloads) FROM fake_apps GROUP BY category;
Exercise: find the average opening price
SELECT SUM(open)/COUNT(open) AS avg_open_price
FROM tutorial.aapl_historical_stock_price
MAX and MIN and Average
SELECT MAX(downloads) FROM fake_apps;
SELECT name, category, MAX(downloads) FROM fake_apps GROUP BY categ
ory;
SELECT MIN(downloads) FROM fake_apps;
SELECT AVG(downloads) FROM fake_apps;
SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps
GROUP BY price; /* round by 2 digits */
DISTINCT
SELECT year, COUNT(DISTINCT month)
FROM tutorial.aapl_historical_stock_price
GROUP BY year
ORDER BY year
CASE
SELECT player_name,
year,
CASE WHEN year = 'SR' THEN 'yes'
ELSE NULL END AS is_a_senior
FROM benn.college_football_players
Multiple Tables
Primary Keys
A primary key serves as a unique identifier for each row or record in a given table. It is
useful when combining 2 tables.
In this coln there is no NULL
Each value is unique.
CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT);
Here artist_id is a foreign key.
Select from Multiple tables
SELECT albums.name, albums.year, artists.name FROM albums, artists;
Join (Combine)
(INNER) JOIN : Returns records that have matching values in both tables
LEFT (OUTER) JOIN : Return all records from the left table, and the matched
records from the right table
RIGHT (OUTER) JOIN : Return all records from the right table, and the matched
records from the left table
FULL (OUTER) JOIN : Return all records when there is a match in either left or right
table
1. JOIN (INNER JOIN`): if there is a match, add together.
SELECT * FROM
albums JOIN artists /* starting combining */
ON albums.artist_id = artists.id; /* combining condition */
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
SELECT players.player_name, players.school_name, teams.conference F
ROM
benn.college_football_players players
JOIN benn.college_football_teams teams
ON players.school_name = teams.school_name
WHERE teams.division = 'FBS (Division I-A Teams)'
2. LEFT JOIN ( LEFT OUTER JOIN ) if there is a match, add together. If there is not,
still add but not match. It will return every row in the left table, and if the join
condition is not met, NULL values are used to fill in the columns from the right
table.
SELECT * FROM
albums LEFT JOIN artists
ON albums.artist_id = artists.id;
3. RIGHT JOIN
4. FULL OUTER JOIN
5. Self Join
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerN
ame2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
Use AS to Rename
It can be confusing when two tables have a column with the same name.
SELECT
albums.name AS 'Album',
albums.year,
artists.name AS 'Artist'
FROM
albums
JOIN artists ON
albums.artist_id = artists.id
WHERE
albums.year > 1980;
UNION
The UNION operator is used to combine the result-set of two or more SELECT
statements.
Each SELECT statement within UNION must have the same number of columns.
The columns must also have similar data types.
The columns in each SELECT statement must also be in the same order.
The UNION operator selects only distinct values by default.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
To allow duplicate values, use UNION ALL :
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
or
positively correlated
not correlated
negatively correlated
positively correlated
(inconclusive)
no correlation
(inconclusive)
negatively correlated
