SQL2 Flashcards
What will this produce
SELECT *
FROM tutorial.us_housing_units
WHERE month_name > ‘January’
Months Feb - December
What will this produce?
SELECT *
FROM tutorial.us_housing_units
WHERE month_name > ‘J’
All months with names longer then j including January
Write a query that only shows rows for which the month name is February.
SELECT *
FROM tutorial.us_housing_units
WHERE month_name = ‘February’
Write a query that only shows rows for which the month_name starts with the letter “N” or an earlier letter in the alphabet.
SELECT *
FROM tutorial.us_housing_units
WHERE month_name <= ‘O’
Write a query that adds the west and south housing as south_plus_west
Select year, month, west, south, west+south AS south_plus_west
FROM tutorial.us_housing_units
This only adds across rows, you need an aggregate function across multiple rows
select the average number of homes for south and west combined for each month and year. Order by month and year
SELECT year, month, avg(west+south) average_sw_housing
FROM tutorial.us_housing_units
GROUP BY 1,2
ORDER BY 1,2
Write a query that calculates the sum of all four regions in a separate column.
SELECT year, month, (west+south+northeast+midwest) as total_units
FROM tutorial.us_housing_units
get the south and west averages per ROW
SELECT year, month, west, south, (west + south)/2 AS south_west_avg
FROM tutorial.us_housing_units
Write a query that returns all rows for which more units were produced in the West region than in the Midwest and Northeast combined.
SELECT year, month, west, midwest, northeast
FROM tutorial.us_housing_units
WHERE west > (midwest + northeast)
Write a query that calculates the percentage of all houses completed in the United States represented by each region. Only return results from the year 2000 and later.
SELECT year, month,
(west/(west+south+northeast+midwest))*100 as west_percent,
(south/(west+south+northeast+midwest))*100 as south_percent,
(northeast/(west+south+northeast+midwest))*100 as northeast_percent,
(midwest/(west+south+northeast+midwest))*100 as midwest_percent
FROM tutorial.us_housing_units
WHERE year >=2000
ORDER BY 1,2
SQL
LIKE allows you to match similar values, instead of exact values.
allows you to match similar values, instead of exact values.
IN
allows you to specify a list of values you’d like to include.
Between
allows you to select only rows within a certain range.
IS NULL
allows you to select rows that contain no data in a given column.
AND
allows you to select only rows that satisfy two conditions.
OR
allows you to select rows that satisfy either of two conditions.
NOT
allows you to select rows that do not match a certain condition.
Select Rows where the “group” starts with Snoop
will this return a group that starts with snoop?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” LIKE ‘Snoop%’
Note: “group” appears in quotations above because GROUP is actually the name of a function in SQL. The double quotes (as opposed to single: ‘) are a way of indicating that you are referring to the column name “group”, not the SQL function. In general, putting double quotes around a word or phrase will indicate that you are referring to that column name.
Select groups that begin with Snoop while ignoring case.
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE “group” ILIKE ‘snoop%’
select any groups that contain ‘dr ke’ the space indicates a missing letter
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE artist ILIKE ‘dr_ke’
Write a query that returns all rows for which Ludacris was a member of the group. Remember that groups can contain multiple members
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” ILIKE ‘%ludacris%’
Write a query that shows all of the entries for Elvis and M.C. Hammer.
Hint: M.C. Hammer is actually on the list under multiple names, so you may need to first write a query to figure out exactly how M.C. Hammer is listed. You’re likely to face similar problems that require some exploration in many real-life scenarios.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” IN (‘Elvis Presley’,’M.C. Hammer’, ‘Hammer’)
Find all rows where the year rank is between 5 and 10.
using BETWEEN and without BETWEEN
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank BETWEEN 5 AND 10
This also includes the years 5 and 10
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank >= 5 AND year_rank <= 10
Write a query that shows the most popular 100 songs from January 1, 1985 through December 31, 1990.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “year” BETWEEN 1985 AND 1990
ORDER BY year_rank
LIMIT 100
select all rows where the artist is empty
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE artist IS NULL
WHERE artist = NULL will not work—you can’t perform arithmetic on null values.
Write a query that shows all of the rows for which song_name is null.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “song_name” IS NULL
return all rows for top-10 recordings in 2012.
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE year = 2012 AND year_rank <= 10
return all top 10 songs from 2012 where the group contains features
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE year = 2012 AND year_rank <= 10
AND “group” ILIKE ‘%feat%’
Write a query that surfaces all rows for top-10 hits for which Ludacris is part of the Group.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “year_rank” >=10
AND “group” ILIKE ‘%ludacris%’
Write a query that surfaces the top-ranked records in 1990, 2000, and 2010.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “year_rank” = 1
AND “year” IN (1990, 2000, 2010)
Write a query that lists all songs from the 1960s with “love” in the title.
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE year BETWEEN 1960 AND 1969
AND song_name ilike ‘%love%’
find all rows for songs that were either ranked 5th or have the artist ‘Gotye’
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank = 5 OR artist = ‘Gotye’
find all songs in 2013 where the group contained either Macklemore or timberlake
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND (“group” ILIKE ‘%macklemore%’ OR “group” ILIKE ‘%timberlake%’)
Write a query that returns all rows for top-10 songs that featured either Katy Perry or Bon Jovi.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “year_rank” <=10
AND (“group” ILIKE ‘%katy perry%’ OR “group” ILIKE ‘%bon jovi%’)
Write a query that returns all songs with titles that contain the word “California” in either the 1970s or 1990s.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “song_name” ILIKE ‘%california%’
AND ((“year” BETWEEN 1970 AND 1979) OR (“year” BETWEEN 1990 AND 1999))
Write a query that lists all top-100 recordings that feature Dr. Dre before 2001 or after 2009.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” ILIKE ‘%dr. dre%’
AND ((“year” <= 2000) OR (“year” >= 2010))
find all songs in 2013 that do not contain Macklemore in the group
SELECT * FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND “group” NOT ILIKE ‘%macklemore%’
Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter “a”.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “song_name” NOT ILIKE ‘%a%’
AND “year” = 2013
Write a query that shows all rows for which T-Pain was a group member, ordered by rank on the charts, from lowest to highest rank (from 100 to 1).
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” ILIKE ‘%t-pain%’
ORDER BY “year_rank” DESC
