I need quotes when specifying a date value.
SELECT *
FROM purchases
WHERE purchased_at <= "2018-11-01";
Pick up rows whose values contain "pudding":
SELECT *
FROM purchases
WHERE name like "%pudding%";
You can use NOT:
SELECT *
FROM purchases
WHERE NOT character_name="Ken";
Another example of NOT:
SELECT *
FROM purchases
WHERE NOT name like "%pudding%";
SELECT *
FROM purchases
WHERE price IS NOT NULL;
Ordering observations
SELECT *
FROM purchases
WHERE character_name = "Ken"
ORDER BY price DESC;
NOT
SELECT *
FROM purchases
WHERE NOT character_name = "Ken";
SELECT *
FROM purchases
WHERE price IS NULL;
SELECT *
FROM purchases
ORDER BY price DESC
LIMIT 5;
Counting the row of observations
This counts non-missing values.
SELECT COUNT(name)
FROM purchases;
This counts the number of rows.
SELECT COUNT(*)
FROM purchases;
Using the where statement:
SELECT COUNT(*)
FROM purchases
WHERE character_name="Ken"
;
Picking up the observation whose value is a maximum value
SELECT name, max(price)
FROM purchases
WHERE character_name="Ken"
;
GET THE SUM PER GROUP
SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
;
SELECT COUNT(*), purchased_at
FROM purchases
GROUP BY purchased_at;
SELECT SUM(price), purchased_at
FROM purchases
WHERE character_name="Ken"
GROUP BY purchased_at
;
SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
HAVING sum(price) > 20;
This picks up cases with a condition (in this case, whoever had a higher score than the guy Will).
SELECT name
FROM players
WHERE goals > (
-- Write an SQL statement below to get Will's score
SELECT goals
FROM players
WHERE name = "Will"
)
;
SELECT name,goals
FROM players
WHERE goals > (
SELECT AVG(goals)
FROM players
)
;
Using AS:
SELECT name AS "180 cm or taller"
FROM players
WHERE height >= 180
;
SELECT SUM(goals) AS "total team score"
FROM players
;
SELECT *
FROM countries
WHERE rank < (
SELECT rank
FROM countries
WHERE name="Japan"
)
;
Merging two tables:
SELECT *
FROM players
-- Add a name to the combined table
JOIN countries
-- Add a join condition
ON players.country_id = countries.id
;
SELECT players.name, countries.name
FROM players
JOIN countries
ON players.country_id = countries.id
;
SELECT countries.name, SUM(goals)
FROM players
JOIN countries
ON players.country_id = countries.id
GROUP BY countries.name
;
SELECT *
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;
SELECT players.name AS "player name", teams.name AS "team (last year)"
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;
SELECT *
FROM players
LEFT JOIN teams
ON players.previous_team_id =teams.id
;
SELECT players.name AS "player name", teams.name AS "team (last year)"
FROM players
LEFT JOIN teams
ON players.previous_team_id = teams.id
;
SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
LEFT JOIN teams
ON players.previous_team_id = teams.id
;
SELECT players.name AS "Player name", players.height AS "height"
FROM players
WHERE height > (
SELECT AVG(height)
FROM players
)
select name, price
from items
order by price desc
;
-- get all rows that contain the string "shirt"
SELECT *
FROM items
WHERE name like "%shirt%"
;
SELECT name, price, MAX(price - cost)
FROM items;
SELECT name, price
FROM items
WHERE price > (
SELECT price
FROM items
WHERE name = "grey hoodie"
);