A delicious appetizer of SQL-ey goodness
10 results with information on all columns
SELECT *
FROM tutorial.us_housing_units
LIMIT 10
Housing starts in the Midwest
SELECT midwest
FROM tutorial.us_housing_units
All housing starts in December since 1985
SELECT *
FROM tutorial.us_housing_units
WHERE month = 12 AND year >= 1985
All housing starts in the second half of the year since 1990
SELECT *
FROM tutorial.us_housing_units
WHERE month > 6 AND year >= 1990
All rows where housing starts were above 30,000 in the South region
SELECT *
FROM tutorial.us_housing_units
WHERE south > 30
The sum of housing starts across all regions for each row
SELECT year, month, month_name, south + west + midwest + northeast AS sum
FROM tutorial.us_housing_units
All rows where the sum of all housing starts is above 70,000 Note: You can't use an alias in a WHERE clause.
SELECT year, month, month_name, south + west + midwest + northeast AS sum
FROM tutorial.us_housing_units
WHERE south + west + midwest + northeast > 70
All rows where the sum of all housing starts is between 50-80k
SELECT year, month, month_name, south + west + midwest + northeast AS sum
FROM tutorial.us_housing_units
WHERE south + west + midwest + northeast > 50 AND south + west + midwest + northeast > 80
The average of all housing starts across all regions for each row
SELECT year, month, month_name, (south + west + midwest + northeast)/4 AS avg
FROM tutorial.us_housing_units
All rows where the housing starts in the South are above the sum of the other three regions
SELECT *
FROM tutorial.us_housing_units
WHERE south > (west + northeast + midwest)
The percentage of housing starts that occur in each region since 1990 Note: Use an alias to title the new columns appropriately
SELECT year, month, month_name, south/(south + west + midwest + northeast) AS south, west/(south + west + midwest + northeast) AS west, midwest/(south + west + midwest + northeast) AS midwest, northeast/(south + west + midwest + northeast) AS northeast
FROM tutorial.us_housing_units
WHERE year > 1990
All rows where Elvis Presley had a song on the top 100 charts
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist='Elvis Presley'
All rows where the artist's name contained "Tony" (not case sensitive)
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE upper(artist) ILIKE '%TONY%'
All rows where the song title contained the word "love" in any way
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE song_name ILIKE '%love%'
All rows where the artist's name begins with the letter "A"
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist LIKE 'A%'
The top 3 songs from each year between 1960-1969
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 3 AND year BETWEEN 1960 AND 1969
All rows where either Elvis Presley, The Rolling Stones, or Van Halen were the artist
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist = 'Elvis Presley' OR artist = 'Rolling Stones' OR artist = 'Van Halen'
Which artist has had the most appearances on the top 100 list? (Tie Elvis/Madonna, 36)
SELECT artist, COUNT(artist)
FROM tutorial.billboard_top_100_year_end
GROUP BY artist
ORDER BY COUNT(artist) DESC
Which artist has had the most #1 hits? How many? (Beatles, 2)
SELECT artist, COUNT(artist)
FROM tutorial.billboard_top_100_year_end
WHERE year_rank = 1
GROUP BY artist
ORDER BY COUNT(artist) DESC
All rows from 1970 where the songs were ranked 10-20th
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 1970 AND year_rank BETWEEN 10 AND 20
All rows from the 1990's where Madonna was not ranked 10-100th
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year BETWEEN 1990 AND 1999 AND NOT (artist LIKE 'Madonna' AND year_rank >= 10)
All rows from 1985 which do not include Madonna or Phil Collins in the group.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 1985 AND NOT (artist = 'Madonna' OR artist = 'Phil Collins')
All number 1 songs in the data set.
SELECT song_name
FROM tutorial.billboard_top_100_year_end
WHERE year_rank = 1
All rows where the artist is not listed
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist IS NULL
All of Madonna's top 100 hits ordered by their ranking (1 to 100)
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist = 'Madonna'
ORDER BY year_rank
All of Madonna's top 100 hits ordered by their ranking within each year
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist = 'Madonna'
ORDER BY year, year_rank
Every number 1 song since 1990 followed by every number 2 song since 1990 and number 3 song since 1990. (Hint: Multiple ordering)
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank BETWEEN 1 and 3 AND year >= 1990
ORDER BY year_rank, year
----------------------------- tutorial.billboard_top_100_year_end
What is the highest position ever reached by Phil Collins?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank BETWEEN 1 and 3 AND year >= 1990
ORDER BY year_rank, year
What is the average position reached by Michael Jackson?
SELECT AVG(year_rank)
FROM tutorial.billboard_top_100_year_end
WHERE artist = 'Michael Jackson'
Madonna's average position when she actually reached the top 10
SELECT AVG(year_rank)
FROM tutorial.billboard_top_100_year_end
WHERE artist = 'Madonna' AND year_rank BETWEEN 1 AND 10
List the top 10 artists based on their number of appearances on this list (and what that number is) since 1985
SELECT artist, COUNT(artist)
FROM tutorial.billboard_top_100_year_end
GROUP BY artist
ORDER BY COUNT(artist) DESC
LIMIT 10
The total count of top 10 hits written by either Elvis, Madonna, the Beatles, or Elton John
SELECT artist, COUNT(artist)
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 10 AND (artist = 'Elvis Presley' OR artist = 'Madonna' OR artist = 'Beatles' OR artist = 'Elton John')
GROUP BY artist
------------------------------------tutorial.aapl_historical_stock_price
The count of days when Apple traded in a range that was larger than $5
SELECT COUNT(date)
FROM tutorial.aapl_historical_stock_price
WHERE 5 < (high - low)
The highest daily trading range that Apple stock achieved in 2012
SELECT MAX(high - low)
FROM tutorial.aapl_historical_stock_price
WHERE year = 2012
The average price for all days when Apple's trading volume exceeded 10,000,000 shares.
SELECT AVG((high + low)/2)
FROM tutorial.aapl_historical_stock_price
WHERE volume > 10000000
The number of trading days in each month of the year 2012
SELECT month, COUNT(date)
FROM tutorial.aapl_historical_stock_price
WHERE year = 2012
GROUP BY month
ORDER BY month
The maximum price Apple traded at during each year of the data set
SELECT year, MAX(high)
FROM tutorial.aapl_historical_stock_price
GROUP BY year
ORDER BY year
The average price and trading volume on each calendar month across the full data set (this should return only 12 rows, one for each month!)
SELECT month, AVG((high + low) / 2) as AvgPrice, AVG(volume) as AvgVol
FROM tutorial.aapl_historical_stock_price
GROUP BY month
ORDER BY month
The average price for each month and year of data since 2008, ordered by years descending and months ascending.
SELECT year, month, AVG((high + low) / 2) as AvgPrice
FROM tutorial.aapl_historical_stock_price
WHERE year >= 2008
GROUP BY year, month
ORDER BY year, month
The average price of days with a trading volume above 25,000,000 shares (just 1 row)
SELECT AVG((high + low) / 2) as Avg_Price
FROM tutorial.aapl_historical_stock_price
WHERE volume > 25000000
The average price on all months with an average daily trading volume above 10,000,000 shares.
SELECT year, month, AVG((high + low) / 2) as AVERAGE
FROM tutorial.aapl_historical_stock_price
GROUP BY year, month
HAVING AVG(volume) > 10000000
The lowest and highest prices that Apple stock achieved between 2005 and 2010 (inclusive).
SELECT MAX(high), MIN(low)
FROM tutorial.aapl_historical_stock_price
WHERE year BETWEEN 2005 AND 2010
The average daily trading range in months where the stock moved more than $25 (open of month to close of month)
SELECT year, month, AVG(high - low)
FROM tutorial.aapl_historical_stock_price
group by year, month
HAVING ABS(SUM(close - open)) > 25
All months in the second half of the year where average daily trading volume was below 10,000,000.
SELECT year, month
FROM tutorial.aapl_historical_stock_price
WHERE month > 6
GROUP BY year, month
HAVING AVG(volume) < 10000000
A list of all calendar months by average daily trading volume (so only 12 rows), sorted from highest to lowest.
SELECT month, AVG(volume)
FROM tutorial.aapl_historical_stock_price
GROUP BY month
ORDER BY AVG(volume) DESC
Count how many unique months there are in the data set (should equal 12)
SELECT count(DISTINCT month)
FROM tutorial.aapl_historical_stock_price
Count how many unique years there are in the data set
SELECT count(DISTINCT year)
FROM tutorial.aapl_historical_stock_price
Count how many unique prices there are in the data set NOT COMPLETE
SELECT Count(DISTINCT open) + COUNT(DISTINCT close) + COUNT(DISTINCT high) + COUNT(DISTINCT low)
FROM tutorial.aapl_historical_stock_price
Return the percentage of unique "open" prices compared to all open prices in the data set
SELECT CAST(Count(DISTINCT open) as float) / CAST(COUNT(open) as float)
FROM tutorial.aapl_historical_stock_price
A listing of all months by their average daily trading volume and a classification that puts this volume into the following categories: "Low" = below 10MM, "Medium" = 10-25 MM, "High" = above 25MM
SELECT year, month, CASE WHEN AVG(volume) > 10000000 THEN 'LOW'
WHEN AVG(volume) BETWEEN 10000000 AND 25000000 THEN 'MEDIUM'
ELSE 'HIGH' END AS rating
FROM tutorial.aapl_historical_stock_price
GROUP BY year, month
A listing of average monthly price plus which quarter of the year they are in (e.g. "Q2" or "Q4").
SELECT year, month, AVG((high + low) /2), CASE WHEN month <= 3 THEN 'Q1'
WHEN month BETWEEN 4 and 6 THEN 'Q2'
WHEN month BETWEEN 7 and 9 THEN 'Q3'
ELSE 'Q4' END AS Q
FROM tutorial.aapl_historical_stock_price
GROUP BY year, month
This same listing filtered for only Q4 (use the new column not the months explicitly as part of this filtering).
SELECT year, month, av
FROM (
SELECT year, month, AVG((high + low) /2) as av, CASE WHEN month <= 3 THEN 'Q1'
WHEN month BETWEEN 4 and 6 THEN 'Q2'
WHEN month BETWEEN 7 and 9 THEN 'Q3'
ELSE 'Q4' END AS Q
FROM tutorial.aapl_historical_stock_price
GROUP BY year, month) anything
GROUP BY year, month, av, q
HAVING Q = 'Q4'
-------------------benn.college_football_players and benn.college_football_teams
The most common home town of football players
The total number of players in each of their Freshmen, Sophomore, Junior or Senior years (4 rows) The total number of players in each position The average height of quarterbacks The average height of each position Return 100 football players and which conference they play for The heaviest football player in the SEC The top 5 heaviest football players in each conference The most common home state of players by conference The average height of football players in each conference The count of football players in the top 100 of weight who belong to each division All players whose home state is Kansas but who went to a school in Missouri