Category: SQL

Reference:

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p0o4a5ac71mcchn1kc1zhxdnm139.htm

 

FULL JOIN puts all variables in two tables together regardless of any conditions (e.g., values missing, etc.).

 

 

 

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"
);