SQL Flashcards
Like
- Used to search for a pattern in a field % match zero, one, or many characters
~~~
SELECT name
FROM people
WHERE name LIKE ‘Ade%’;
~~~ - match a single character
~~~
SELECT name
FROM people
WHERE name LIKE E_’;
~~~
Not like
SELECT name FROM people WHERE name NOT LIKE 'A.%';
Wildcard position
SELECT name FROM people WHERE name LIKE '%г*;
Returns = A.J. Langer
SELECT name FROM people WHERE name LIKE _ _t%';
returns = Anthony
Where, In
WHERE, IN SELECT title FROM films WHERE release_year IN (1920, 1930, 1940);
Missing values
Missing values
* COUNT (field_name) includes only non-missing values
* COUNT (*) includes missing values
null
* Missing values:
* Human error
* Information not available
Is Not Null
IS NOT NULL
~~~
SELECT COUNT (*) AS no_birthdates
FROM people
WHERE birthdate IS NOT NULL;
~~~
Summarizing data: Aggregate functions
AVG() , SUM() , MIN() , MAX() , COUNT ()
Aggregate functions vs. arithmetic
Aggregate functions : aggregates columns
Arithmetic functions : aggregates rows
Order of execution
- Step 1: FROM
- Step 2: WHERE
- Step 3: SELECT (aliases are defined here)
- Step 4: LIMIT
Round
ROUND (number_to_round, decimal_places)
~~~
SELECT ROUND (AVG (budget), 2) AS avg_budget
FROM films
WHERE release_year >= 2010;
~~~
HAVING
SELECT release_year, COUNT (title) As title_count FROM films GROUP BY release_year HAVING COUNT(title) > 10;
Aggregate functions have to be in this order, group by and then having. You can’t use count
USING
Inner join of presidents and prime_ministers, joining on country SELECT p1.country, pl.continent, prime_minister, president FROM prime_ministers AS p1 INNER JOIN presidents AS p2 USING (country);
ON
Inner join of presidents and prime_ministers, joining on country SELECT prime_ministers.country, prime_ministers.continent, prime_minister, president FROM prime_ministers INNER JOIN presidents ON prime_minsters.country = presidents. country;
Chaining Joins
SELECT p1. country, p1. continent, president, prime_minister, pm_start FROM prime ministers as p1 INNER JOIN presidents as p2 USING(country) INNER JOIN prime_minister_terms as p3 USING (prime_minister);
Chaining joins + and statements
SELECT name, e.year, fertility_rate, unemployment_rate FROM countries AS c INNER JOIN populations AS p ON c. code = p. country_code INNER JOIN economies AS e ON c. code = e. code -- Add an additional joining condition such that you are also joining on year and p.year = e.year
Cross Join
Returns all possible combinations
Union Syntax
Returns unique values
SELECT * FROM left table UNION SELECT * FROM right_table;
Union All Syntax
Returns duplicate values
~~~
SELECT *
FROM left_table
UNION ALL
SELECT *
FROM right_table;
~~~
Subqueries in where clause - semi join
SELECT
president,
country,
continent
FROM
presidents
WHERE
country IN
(
SELECT
country
FROM
states
WHERE
indep _year < 1800
)
;
SELECT country, president FROM presidents WHERE continent LIKE '%America' AND country NOT IN (SELECT country FROM states WHERE indep_year < 1800);SELECT * FROM populations Filter for only those populations where life expectancy is 1.15 times higher than average WHERE life _expectancy > 1.15 * (SELECT AVG (life_expectancy) FROM populations WHERE year = 2015) AND year=2015;
Subqueries in select clause
SELECT DISTINCT continent, (SELECT COUNT (*) FROM monarchs WHERE states.continent = monarch.continent) AS monarch_count FROM states;
Subqueries in from clause - self join
SELECT DISTINCT monarchs.continent, sub.most_recent FROM monarchs, (SELECT continent, MAX(indep_year AS most_recent FROM states GRAUP BY continent) AS sub WHERE monarchs.continent = sub.continent
Example 2
select name, country_code, city_proper_pop, metroarea_pop, (city_proper_pop / metroarea_pop 100 as city perc from cities where name IN ( select capital where continent like %Furone%' or continent LIKE %America') and metroarea_pop is not null order by city_perc DESC limit 10
example 3
~~~
SELECT
– Select country name and the count match IDs
c. AS country_name,
COUNT(sub) AS matches
FROM country AS c
– Inner join the subquery onto country
– Select the country id and match id columns
inner join (SELECT country_id, id
FROM match
– Filter the subquery by matches with 10+ goals
WHERE (home_goal + away_goal) >= 10 ) AS sub
ON c.country = sub.id
GROUP BY country_name;
~~~
CASE WHEN
SELECT
CASE
WHEN hometeam_id = 10189 THEN 'FC Schalke 04'
WHEN hometeam_id = * 9823 THEN 'FC Bayern Munich'
ELSE 'Other' END AS home team,
COUNT(id) AS total_matches
FROM matches germany
GROUP BY the CASE STATEMENTTrue or false: Subqueries in where can only return one column
True
True or false: subqueries in select return a single aggregate value
True
When Condition
SELECT date, hometeam_id, awayteam_id,
CASE WHEN hometeam_id = 8455 AND home_goal > away_goal
THEN 'Chelsea home win!'
WHEN awayteam_id = 8455 AND home_goal < away_goal
THEN 'Chelsea away win!'
ELSE 'Loss or tie :(' END AS outcome
FROM match
WHERE hometeam_id = 8455 OR awayteam_id = 8455;Case when in select statement
SELECT season, COUNT(CASE WHEN hometeam id = 8650 AND home_goal > away_goal THEN id END) AS home_wins FROM match GROUP BY season;
Window Function : Over clause
SELECT date, (home_goal + away_goal) AS goals, AVG (home_goal + away_goal) OVER() AS overall_avg FROM match WHERE season = '2011/2012';
string_agg
Enter STRING AGG
* STRING_AGG (column, separator) takes all the values of a column and concatenates them, with separator in between each value
STRING_AGG (Letter, ‘, “) transforms this…
Over with a partition
SELECT date, (home_goal + away_goal) AS goals, AVG (home_goal + away_goal) OVER(PARTITION BY season) AS season_avg FROM match;
