SQL2

Question 1

What will this produce

SELECT *
FROM tutorial.us_housing_units
WHERE month_name > ‘January’

Answer 1

Months Feb - December

Question 2

What will this produce?

SELECT *
FROM tutorial.us_housing_units
WHERE month_name > ‘J’

Answer 2

All months with names longer then j including January

Question 3

Write a query that only shows rows for which the month name is February.

Answer 3

SELECT *
FROM tutorial.us_housing_units
WHERE month_name = ‘February’

Question 4

Write a query that only shows rows for which the month_name starts with the letter “N” or an earlier letter in the alphabet.

Answer 4

SELECT *
FROM tutorial.us_housing_units
WHERE month_name <= ‘O’

Question 5

Write a query that adds the west and south housing as south_plus_west

Answer 5

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

Question 6

select the average number of homes for south and west combined for each month and year. Order by month and year

Answer 6

SELECT year, month, avg(west+south) average_sw_housing
FROM tutorial.us_housing_units
GROUP BY 1,2
ORDER BY 1,2

Question 7

Write a query that calculates the sum of all four regions in a separate column.

Answer 7

SELECT year, month, (west+south+northeast+midwest) as total_units
FROM tutorial.us_housing_units

Question 8

get the south and west averages per ROW

Answer 8

SELECT year, month, west, south, (west + south)/2 AS south_west_avg

FROM tutorial.us_housing_units

Question 9

Write a query that returns all rows for which more units were produced in the West region than in the Midwest and Northeast combined.

Answer 9

SELECT year, month, west, midwest, northeast
FROM tutorial.us_housing_units
WHERE west > (midwest + northeast)

Question 10

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.

Answer 10

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

Question 11

SQL

LIKE allows you to match similar values, instead of exact values.

Answer 11

allows you to match similar values, instead of exact values.

Question 12

IN

Answer 12

allows you to specify a list of values you’d like to include.

Question 13

Between

Answer 13

allows you to select only rows within a certain range.

Question 14

IS NULL

Answer 14

allows you to select rows that contain no data in a given column.

Question 15

AND

Answer 15

allows you to select only rows that satisfy two conditions.

Question 16

OR

Answer 16

allows you to select rows that satisfy either of two conditions.

Question 17

NOT

Answer 17

allows you to select rows that do not match a certain condition.

Question 18

Select Rows where the “group” starts with Snoop

will this return a group that starts with snoop?

Answer 18

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.

Question 19

Select groups that begin with Snoop while ignoring case.

Answer 19

SELECT * FROM tutorial.billboard_top_100_year_end

WHERE “group” ILIKE ‘snoop%’

Question 20

select any groups that contain ‘dr ke’ the space indicates a missing letter

Answer 20

SELECT * FROM tutorial.billboard_top_100_year_end

WHERE artist ILIKE ‘dr_ke’

Question 21

Write a query that returns all rows for which Ludacris was a member of the group. Remember that groups can contain multiple members

Answer 21

SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” ILIKE ‘%ludacris%’

Question 22

Write a query that returns all rows for which the first artist listed in the group has a name that begins with “DJ”.

Answer 22

SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” LIKE ‘DJ%’

Question 23

select all rows where the year_rank was 1,2 or 3

Answer 23

SELECT *

FROM tutorial.billboard_top_100_year_end

WHERE year_rank IN (1, 2, 3)

Question 24

Select rows that have the following artists:

Taylor Swift

Usher

Ludacris

Answer 24

SELECT *

FROM tutorial.billboard_top_100_year_end

WHERE artist IN (‘Taylor Swift’, ‘Usher’, ‘Ludacris’)

Question 25

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.

Answer 25

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "group" IN ('Elvis Presley','M.C. Hammer', 'Hammer')

Question 26

Find all rows where the year rank is between 5 and 10. using BETWEEN and without BETWEEN

Answer 26

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

Question 27

Write a query that shows the most popular 100 songs from January 1, 1985 through December 31, 1990.

Answer 27

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "year" BETWEEN 1985 AND 1990 ORDER BY year\_rank LIMIT 100

Question 28

select all rows where the artist is empty

Answer 28

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.

Question 29

Write a query that shows all of the rows for which song\_name is null.

Answer 29

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "song\_name" IS NULL

Question 30

return all rows for top-10 recordings in 2012.

Answer 30

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2012 AND year\_rank \<= 10

Question 31

return all top 10 songs from 2012 where the group contains features

Answer 31

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2012 AND year\_rank \<= 10 AND "group" ILIKE '%feat%'

Question 32

Write a query that surfaces all rows for top-10 hits for which Ludacris is part of the Group.

Answer 32

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "year\_rank" \>=10 AND "group" ILIKE '%ludacris%'

Question 33

Write a query that surfaces the top-ranked records in 1990, 2000, and 2010.

Answer 33

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "year\_rank" = 1 AND "year" IN (1990, 2000, 2010)

Question 34

Write a query that lists all songs from the 1960s with "love" in the title.

Answer 34

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year BETWEEN 1960 AND 1969 AND song\_name ilike '%love%'

Question 35

find all rows for songs that were either ranked 5th or have the artist 'Gotye'

Answer 35

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year\_rank = 5 OR artist = 'Gotye'

Question 36

find all songs in 2013 where the group contained either Macklemore or timberlake

Answer 36

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2013 AND ("group" ILIKE '%macklemore%' OR "group" ILIKE '%timberlake%')

Question 37

Write a query that returns all rows for top-10 songs that featured either Katy Perry or Bon Jovi.

Answer 37

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "year\_rank" \<=10 AND ("group" ILIKE '%katy perry%' OR "group" ILIKE '%bon jovi%')

Question 38

Write a query that returns all songs with titles that contain the word "California" in either the 1970s or 1990s.

Answer 38

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

Question 39

Write a query that lists all top-100 recordings that feature Dr. Dre before 2001 or after 2009.

Answer 39

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "group" ILIKE '%dr. dre%' AND (("year" \<= 2000) OR ("year" \>= 2010))

Question 40

select all songs from 2013 that are not in the second or third rank

Answer 40

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2013 AND year\_rank NOT BETWEEN 2 AND 3

Question 41

find all songs in 2013 that do not contain Macklemore in the group

Answer 41

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2013 AND "group" NOT ILIKE '%macklemore%'

Question 42

find all rows for 2013 where the artist is not empty

Answer 42

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2013 AND artist IS NOT NULL

Question 43

Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter "a".

Answer 43

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "song\_name" NOT ILIKE '%a%' AND "year" = 2013

Question 44

Write a query that returns all rows from 2010 ordered by rank, with artists ordered alphabetically for each song.

Answer 44

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year = 2012 ORDER BY "year\_rank", "artist"

Question 45

Name two different ways to comment out code in sql

Answer 45

--This comment won't affect the way the code runs /\* Here's a comment so long and descriptive that it could only fit on multiple lines. Fortunately, it, too, will not affect how this code runs. \*/

Question 46

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).

Answer 46

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE "group" ILIKE '%t-pain%' ORDER BY "year\_rank" DESC

Question 47

Write a query that returns songs that ranked between 10 and 20 (inclusive) in 1993, 2003, or 2013. Order the results by year and rank, and leave a comment on each line of the WHERE clause to indicate what that line does

Answer 47

SELECT \* FROM tutorial.billboard\_top\_100\_year\_end WHERE year\_rank BETWEEN 10 AND 20 --selects beween 10 and 20 AND (year IN (1993,2003,2013)) -- selects only 1993, 2003 and 2013 ORDER BY year, year\_rank