SQL Intermediate

Question 1

COUNT

Answer 1

Returns the count of only non-null rows

Question 2

Write a query to count the number of non-null rows in the low column.

Answer 2

SELECT COUNT(low) AS low FROM tutorial.aapl_historical_stock_price

Question 3

Write a query that determines counts of every single column.

Answer 3

SELECT 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(volume) AS volume

FROM tutorial.aapl_historical_stock_price

Question 4

Write a query to calculate the average opening price

Answer 4

SELECT (SUM(open)/COUNT(open)) as avg_open
FROM tutorial.aapl_historical_stock_price

Question 5

MIN and MAX

Answer 5

Depending on the column type, MIN will return the lowest number, earliest date, or non-numerical value as close alphabetically to “A” as possible. As you might suspect, MAX does the opposite—it returns the highest number, the latest date, or the non-numerical value closest alphabetically to “Z.”

Question 6

select the minimum and maximum volumes of stock

Answer 6

SELECT MIN(volume) AS min_volume, MAX(volume) AS max_volume FROM tutorial.aapl_historical_stock_price

Question 7

What was Apple’s lowest stock price (at the time of this data collection)?

Answer 7

SELECT MIN(low)
FROM tutorial.aapl\_historical\_stock\_price

Question 8

What was the highest single-day increase in Apple’s share value?

Answer 8

SELECT MAX(close-open)
FROM tutorial.aapl\_historical\_stock\_price

Question 9

AVG

Answer 9

calculates the average of a selected group of values. It’s very useful, but has some limitations. First, it can only be used on numerical columns. Second, it ignores nulls completely.

Question 10

SELECT AVG(high) FROM tutorial.aapl_historical_stock_price WHERE high IS NOT NULL

VS

SELECT AVG(high) FROM tutorial.aapl_historical_stock_price

Answer 10

No difference as AVG ignores nulls

Question 11

Write a query that calculates the average daily trade volume for Apple stock.

Answer 11

SELECT AVG(volume)
FROM tutorial.aapl\_historical\_stock\_price

Question 12

GROUP BY

Answer 12

GROUP BY allows you to separate data into groups, which can be aggregated independently of one another.

Question 13

Calculate the total number of shares traded each month for each year. Order your results chronologically.

Answer 13

SELECT year, month, SUM(volume) as total_num_shares
FROM tutorial.aapl_historical_stock_price
GROUP BY 1, 2
ORDER BY 1, 2

Question 14

Using GROUP BY with ORDER BY

Answer 14

The order of column names in your GROUP BY clause doesn’t matter—the results will be the same regardless. If you want to control how the aggregations are grouped together, use ORDER BY.

Question 15

SELECT year, month, COUNT(*) AS count FROM tutorial.aapl_historical_stock_price GROUP BY year, month ORDER BY month, year

VS

SELECT year, month, COUNT(*) AS count FROM tutorial.aapl_historical_stock_price GROUP BY year, month ORDER BY year, month

Answer 15

2000 1 20
2001 1 21
2002 1 21
2003 1 21
2004 1 20

vs

2000 1 20
2000 2 20
2000 3 23
2000 4 19

Question 16

Write a query to calculate the average daily price change in Apple stock, grouped by year.

Answer 16

SELECT year,
AVG(close-open) AS avg_change
FROM tutorial.aapl_historical_stock_price
GROUP BY 1
ORDER BY 1

Question 17

Write a query that calculates the lowest and highest prices that Apple stock achieved each month.

Answer 17

SELECT year, month,
MAX(high) AS highest,
MIN(low) AS lowest
FROM tutorial.aapl_historical_stock_price
GROUP BY 1, 2
ORDER BY 1, 2

Question 18

find every month during which AAPL stock where the max was over $400/share.

Answer 18

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

Question 19

ORDER of SQL operations

Answer 19

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Question 20

CASE

Answer 20

The CASE statement is SQL’s way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements—SQL’s equivalent of IF/THEN in Excel. Because of this pairing, you might be tempted to call this SQL CASE WHEN, but CASE is the accepted term.

Every CASE statement must end with the END statement. The ELSE statement is optional, and provides a way to capture values not specified in the WHEN/THEN statements.

Question 21

write a query that adds a new column called is_a_senior that returns yes if the player is a senior and no if not

Answer 21

SELECT player_name, year,

CASE

WHEN year = ‘SR’ THEN ‘yes’

ELSE

‘no’

END AS is_a_senior

FROM benn.college_football_players

Question 22

Write a query that includes a column that is flagged “yes” when a player is from California, and sort the results with those players first.

Answer 22

SELECT player_name,
CASE
WHEN state = ‘CA’ THEN ‘yes’
ELSE ‘no’
END AS from_ca
FROM benn.college_football_players
ORDER BY 2 DESC

Question 23

sort players into three weight classes:

over 250

201 - 250

176 - 200

175 and under

Answer 23

SELECT player_name, weight,

CASE WHEN weight > 250 THEN ‘over 250’

WHEN weight > 200 AND weight <= 250 THEN ‘201-250’

WHEN weight > 175 AND weight <= 200 THEN ‘176-200’

ELSE ‘175 or under’ END AS weight_group

FROM benn.college_football_players

Question 24

Write a query that includes players’ names and a column that classifies them into four categories based on height. Keep in mind that the answer we provide is only one of many possible answers, since you could divide players’ heights in many ways.

Answer 24

SELECT player_name, height,

CASE

WHEN height > 74 THEN ‘over 74’

WHEN height > 72 AND height <= 74 THEN ‘73-74’

WHEN height > 70 AND height <= 72 THEN ‘71-72’

ELSE ‘under 70’

END AS height_group

FROM benn.college_football_players

Question 25

Write a query that selects all columns from benn.college_football_players and adds an additional column that displays the player’s name if that player is a junior or senior.

Answer 25

SELECT *,
CASE
WHEN year = ‘JR’ OR year = ‘SR’ THEN player_name
ELSE ‘not_jr_sr’
END AS jr_sr
FROM benn.college_football_players

Question 26

count the number of students by their year, INCLUDING null values

“hint: case”

Answer 26

SELECT

CASE

WHEN year = ‘FR’ THEN ‘FR’

WHEN year = ‘SO’ THEN ‘SO’

WHEN year = ‘JR’ THEN ‘JR’

WHEN year = ‘SR’ THEN ‘SR’

ELSE ‘No Year Data’ END AS year_group,

COUNT(1) AS count

FROM benn.college_football_players

GROUP BY 1

normally count ignores nulls, by making them a case, you can count them

Question 27

Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (Everywhere else).

Answer 27

SELECT
CASE
WHEN state IN (‘CA’, ‘OR’, ‘WA’) THEN ‘west_coast’
WHEN state = ‘TX’ THEN ‘texas’
ELSE ‘other’
END AS region,
COUNT(1) as players –count the case to get all the nulls
FROM benn.college_football_players
WHERE weight >= 300
GROUP BY 1

Question 28

Write a query that calculates the combined weight of all underclass players (FR/SO) in California as well as the combined weight of all upperclass players (JR/SR) in California.

Answer 28

SELECT
CASE
WHEN year IN (‘FR’, ‘SO’) THEN ‘fr_so’
WHEN year IN (‘JR’, ‘SR’) THEN ‘jr_sr’
ELSE NULL
END AS year_in_school,
SUM(weight) as total_weight –count the case to get all the nulls
FROM benn.college_football_players
WHERE state = ‘CA’
GROUP BY 1

Question 29

SELECT

CASE

WHEN year = ‘FR’ THEN ‘FR’

WHEN year = ‘SO’ THEN ‘SO’

WHEN year = ‘JR’ THEN ‘JR’

WHEN year = ‘SR’ THEN ‘SR’

ELSE ‘No Year Data’

END AS year_group,

COUNT(1) AS count

FROM benn.college_football_players

GROUP BY 1

pivot this not using a pivot command

Answer 29

SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr\_count,
 COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so\_count,
 COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr\_count,
 COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr\_count
 FROM benn.college\_football\_players

Output:

fr_count so_count jr_count sr_count
9665 5881 5665 5087

Question 30

Write a query that displays the number of players in each state, with FR, SO, JR, and SR players in separate columns and another column for the total number of players. Order results such that states with the most players come first.

Answer 30

SELECT state,
COUNT(CASE WHEN year = ‘FR’ THEN 1 ELSE NULL END) AS fr_count,
COUNT(CASE WHEN year = ‘SO’ THEN 1 ELSE NULL END) AS so_count,
COUNT(CASE WHEN year = ‘JR’ THEN 1 ELSE NULL END) AS jr_count,
COUNT(CASE WHEN year = ‘SR’ THEN 1 ELSE NULL END) AS sr_count,
count(*) as total_players
FROM benn.college_football_players
GROUP BY state
ORDER BY total_players DESC

Question 31

Write a query that shows the number of players at schools with school names that start with A through M, and the number at schools with names starting with N - Z.

Answer 31

SELECT

CASE

WHEN school_name < ‘n’ THEN ‘A-M’

WHEN school_name >= ‘n’ THEN ‘N-Z’

ELSE NULL

END AS school_name_group,

COUNT(1) AS players

FROM benn.college_football_players

GROUP BY 1

Question 32

For each school give the number of students whose names start with A-m and n-z

Answer 32

SELECT full_school_name,
COUNT(CASE WHEN player_name BETWEEN ‘A’ AND ‘M’ THEN 1 ELSE NULL END) AS a_m,
COUNT(CASE WHEN player_name BETWEEN ‘N’ AND ‘Z’ THEN 1 ELSE NULL END) AS n_z
FROM benn.college_football_players
GROUP BY 1

Question 33

SELECT DISTINCT year, month FROM tutorial.aapl_historical_stock_price

which values will this return unique ones for

Answer 33

both year and month

Question 34

count the unique values in the month column.

Answer 34

SELECT COUNT(DISTINCT month) AS unique_months FROM tutorial.aapl_historical_stock_price

Question 35

taking average trade volumes by month

Answer 35

SELECT month, AVG(volume) AS avg_trade_volume

FROM tutorial.aapl_historical_stock_price

GROUP BY month

ORDER BY 2 DESC

Question 36

Write a query that counts the number of unique values in the month column for each year.

Answer 36

SELECT year,
COUNT(DISTINCT month) AS unique_months
FROM tutorial.aapl_historical_stock_price
GROUP BY 1
ORDER BY 1

Question 37

Write a query that separately counts the number of unique values in the month column and the number of unique values in the year column.

Answer 37

SELECT COUNT(DISTINCT year) AS unique_years,
COUNT(DISTINCT month) AS unique_months
FROM tutorial.aapl_historical_stock_price

Question 38

Write a query that selects the school name, player name, position, and weight for every player in Georgia, ordered by weight (heaviest to lightest). Be sure to make an alias for the table, and to reference all column names in relation to the alias.

get school_name from teams

Answer 38

SELECT teams.school_name, players.player_name, players.position, players.weight
FROM benn.college_football_players players
JOIN benn.college_football_teams teams
ON players.school_name = teams.school_name
WHERE players.state = ‘GA’
ORDER BY players.weight DESC

Question 39

INNER JOIN

Answer 39

nner joins eliminate rows from both tables that do not satisfy the join condition set forth in the ON statement. In mathematical terms, an inner join is the intersection of the two tables.

Therefore, if a player goes to a school that isn’t in the teams table, that player won’t be included in the result from an inner join. Similarly, if there are schools in the teams table that don’t match to any schools in the players table, those rows won’t be included in the results either.

Question 40

Inner join with two columns of the same name

Answer 40

The results can only support one column with a given name—when you include 2 columns of the same name, the results will simply show the exact same result set for both columns even if the two columns should contain different data. You can avoid this by naming the columns individually.

Question 41

Write a query that displays player names, school names and conferences for schools in the “FBS (Division I-A Teams)” division.

Answer 41

SELECT players.player_name, players.school_name, teams.conference
FROM benn.college_football_players players
INNER JOIN benn.college_football_teams teams
ON teams.school_name = players.school_name
WHERE teams.division = ‘FBS (Division I-A Teams)’

Question 42

Outer joins

Answer 42

unmatched rows in one or both tables can be returned.

Question 43

Left Join

Answer 43

Keep everything from the left database, the first one chosen in the on statement

Question 44

Right Join

Answer 44

Question 45

get schema from postgresql

Answer 45

\d table_name or \d+ table_name

SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = ‘city’;

Question 46

loginto postgre sgl

Answer 46

psql -U postgres -W

postgres is the name of the user

for a specific database

psql -d database -U user -W

Question 47

switch to the table you want

Answer 47

postgres=# \c dvdrental Password for user postgres: You are now connected to database “dvdrental” as user “postgres”.

Question 48

list all available databases in postgre

Answer 48

\l

Question 49

list all available tables

Answer 49

\dt

Question 50

Write a query that performs an inner join between the tutorial.crunchbase_acquisitions table and the tutorial.crunchbase_companies table, but instead of listing individual rows, count the number of non-null permalink rows in each table.

Answer 50

SELECT COUNT(companies.permalink) AS companies_rowcount, COUNT(acquisitions.company_permalink) AS acquisitions_rowcount FROM tutorial.crunchbase_companies companies JOIN tutorial.crunchbase_acquisitions acquisitions ON companies.permalink = acquisitions.company_permalink

Question 51

SELECT COUNT(companies.permalink), COUNT(acquisitions.company_permalink)
FROM tutorial.crunchbase_companies companies
LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
ON companies.permalink = acquisitions.company_permalink

VS

– Returns first 100 rows from tutorial.crunchbase_companies
SELECT COUNT(companies.permalink), COUNT(acquisitions.company_permalink)
FROM tutorial.crunchbase_companies companies
JOIN tutorial.crunchbase_acquisitions acquisitions
ON companies.permalink = acquisitions.company_permalink

Answer 51

The first will return counts of all values in the left table and only counts of rows in the right table that are equal to the left.

The second will return the counts of only rows that are in both table

Question 52

Count the number of unique companies (don’t double-count companies) and unique acquired companies by state. Do not include results for which there is no state data, and order by the number of acquired companies from highest to lowest.

Answer 52

SELECT companies.state_code, COUNT(DISTINCT companies.permalink) AS unique_companies, COUNT(DISTINCT acquisitions.company_permalink) AS unique_companies_acquired

FROM tutorial.crunchbase_companies companies

LEFT JOIN tutorial.crunchbase_acquisitions acquisitions

ON companies.permalink = acquisitions.company_permalink

WHERE companies.state_code IS NOT NULL GROUP BY 1 ORDER BY 3 DESC

Question 53

SELECT companies.permalink AS companies_permalink, companies.name AS companies_name, acquisitions.company_permalink AS acquisitions_permalink, acquisitions.acquired_at AS acquired_date FROM tutorial.crunchbase_companies companies LEFT JOIN tutorial.crunchbase_acquisitions acquisitions ON companies.permalink = acquisitions.company_permalink AND acquisitions.company_permalink != ‘/company/1000memories’ ORDER BY 1

VS

SELECT companies.permalink AS companies_permalink, companies.name AS companies_name, acquisitions.company_permalink AS acquisitions_permalink, acquisitions.acquired_at AS acquired_date FROM tutorial.crunchbase_companies companies LEFT JOIN tutorial.crunchbase_acquisitions acquisitions ON companies.permalink = acquisitions.company_permalink WHERE acquisitions.company_permalink != ‘/company/1000memories’ OR acquisitions.company_permalink IS NULL ORDER BY 1

Answer 53

Compare the following query to the previous one and you will see that everything in the tutorial.crunchbase_acquisitions table was joined on except for the row for which company_permalink is ‘/company/1000memories’. It does return the row from the companies table with that permalink

You can see that the 1000memories line is not returned (it would have been between the two highlighted lines below). Also note that filtering in the WHERE clause can also filter null values, so we added an extra line to make sure to include the nulls.

Question 54

Write a query that shows a company’s name, “status” (found in the Companies table), and the number of unique investors in that company. Order by the number of investors from most to fewest. Limit to only companies in the state of New York.

Answer 54

SELECT companies.name, companies.status, COUNT(DISTINCT investments.investor_name)
FROM tutorial.crunchbase_companies companies
LEFT JOIN tutorial.crunchbase_investments investments
ON companies.permalink = investments.company_permalink
WHERE companies.state_code = ‘NY’
GROUP BY 1,2
ORDER BY 3 DESC

Question 55

Write a query that lists investors based on the number of companies in which they are invested. Include a row for companies with no investor, and order from most companies to least.

Question 56

write a query using a full join that counts the number of rows in the acquisitions table, the companies table and both tables.

Answer 56

SELECT

COUNT(CASE WHEN companies.permalink IS NOT NULL AND acquisitions.company_permalink IS NULL THEN companies.permalink ELSE NULL END) AS companies_only,

COUNT(CASE WHEN companies.permalink IS NOT NULL AND acquisitions.company_permalink IS NOT NULL THEN companies.permalink ELSE NULL END) AS both_tables,

COUNT(CASE WHEN companies.permalink IS NULL AND acquisitions.company_permalink IS NOT NULL THEN acquisitions.company_permalink ELSE NULL END) AS acquisitions_only

FROM tutorial.crunchbase_companies companies

FULL JOIN tutorial.crunchbase_acquisitions acquisitions ON companies.permalink = acquisitions.company_permalink

Question 57

Write a query that joins tutorial.crunchbase_companies and tutorial.crunchbase_investments_part1 using a FULL JOIN. Count up the number of rows that are matched/unmatched as in the example above.

Answer 57

SELECT
COUNT(CASE WHEN companies.permalink IS NOT NULL AND investments.company_permalink ISNULL THEN 1 ELSE NULL END)AS company_only,
COUNT(CASE WHEN companies.permalink ISNULL AND investments.company_permalink IS NOT NULL THEN 1 ELSE NULL END)AS investment_only,
COUNT(CASE WHEN companies.permalink IS NOT NULL AND investments.company_permalink IS NOT NULL THEN 1 ELSE NULL END)AS both_tables
FROM tutorial.crunchbase_companies companies
FULL JOIN tutorial.crunchbase_investments_part1 investments
ON companies.permalink = investments.company_permalink

Question 59

What would this result in:

SELECT * FROM tutorial.crunchbase_investments_part1

UNION

SELECT * FROM tutorial.crunchbase_investments_part2

Answer 59

This would stack the two together ontop of each other.

More specifically, when you use UNION, the dataset is appended, and any rows in the appended table that are exactly identical to rows in the first table are dropped. If you’d like to append all the values from the second table, use UNION ALL.

Both tables must have the same number of columns

The columns must have the same data types in the same order as the first table

Question 60

Write a query that appends the two crunchbase_investments datasets above (including duplicate values). Filter the first dataset to only companies with names that start with the letter “T”, and filter the second to companies with names starting with “M” (both not case-sensitive). Only include the company_permalink, company_name, and investor_name columns.

Answer 60

SELECT company_permalink, company_name, investor_name
FROM tutorial.crunchbase_investments_part1
WHERE company_name ILIKE ‘t%’

UNION ALL

SELECT company_permalink, company_name, investor_name
FROM tutorial.crunchbase_investments_part2
WHERE company_name ILIKE ‘m%’

Question 61

Write a query that shows 3 columns. The first indicates which dataset (part 1 or 2) the data comes from, the second shows company status, and the third is a count of the number of investors.

Hint: you will have to use the tutorial.crunchbase_companies table as well as the investments tables. And you’ll want to group by status and dataset.

Answer 61

SELECT ‘investments_part1’ AS dataset_name, companies.status,

COUNT(DISTINCT investments.investor_permalink) AS investors

FROM tutorial.crunchbase_companies companies

LEFT JOIN tutorial.crunchbase_investments_part1 investments

ON companies.permalink = investments.company_permalink

GROUP BY 1,2

UNION ALL

SELECT ‘investments_part2’ AS dataset_name, companies.status,

COUNT(DISTINCT investments.investor_permalink) AS investors

FROM tutorial.crunchbase_companies companies

LEFT JOIN tutorial.crunchbase_investments_part2 investments

ON companies.permalink = investments.company_permalink

GROUP BY 1,2

Question 62

SELECT companies.permalink, companies.name, companies.status, COUNT(investments.investor_permalink) AS investors FROM tutorial.crunchbase_companies companies LEFT JOIN tutorial.crunchbase_investments_part1 investments ON companies.permalink = investments.company_permalink AND investments.funded_year > companies.founded_year + 5 GROUP BY 1,2, 3

Answer 62

Here’s an example using > to join only investments that occurred more than 5 years after each company’s founding year:

Question 63

couple reasons you might want to join tables on multiple foreign keys

Answer 63

accuracy

speed

joins with two keys can increase the speed due to the the way that SQL indexes

Question 64

Identify companies that received investments from great Britain following an investment from japan, using the investments database

Answer 64

SELECT DISTINCT japan_investments.company_name, japan_investments.company_permalink
FROM tutorial.crunchbase_investments_part1 as japan_investments
JOIN tutorial.crunchbase_investments_part1 as gb_investments
ON japan_investments.company_permalink = gb_investments.company_permalink
AND gb_investments.investor_country_code = ‘GBR’
AND gb_investments.funded_at > japan_investments.funded_at
WHERE japan_investments.investor_country_code = ‘JPN’
ORDER BY 1

Question 65

Convert the funding_total_usd and founded_at_clean columns in the tutorial.crunchbase_companies_clean_date table to strings (varchar format) using a different formatting function for each one.

Answer 65

SELECT CAST(funding\_total\_usd AS varchar),
founded\_at\_clean::varchar
FROM tutorial.crunchbase\_companies\_clean\_date