1 
/*  Beer Database Queries */
   2 
-- 1.  Display each beer’s name and style name.  A beer should be displayed regardless of whether a style name exists or not.
   3 
SELECT beer_name,
   4 
       style_name
   5 
FROM beers
   6 
LEFT JOIN styles ON  beers.style_id = styles.style_id
   7 
 
   8 
 
   9 
-- 2.  Display each beer’s name, category name, color example, and style name, for all beers that have values for category name, color example, and style name.
  10 
SELECT beer_name,
  11 
       category_name,
  12 
       examples,
  13 
       style_name
  14 
FROM beers
  15 
INNER JOIN categories ON categories.category_id = beers.cat_id
  16 
INNER JOIN colors ON colors.lovibond_srm = beers.srm
  17 
INNER JOIN styles ON styles.style_id = beers.style_id;
  18 
 
  19 
 
  20 
-- 3.  Display each brewer’s name along with the minimum, maximum, and average alcohol by volume (ABV) of its beers.  Exclude any beers with an ABV of zero.  Show the brewers with the highest average ABV first.
  21 
SELECT name,
  22 
       COUNT(beers.brewery_id) AS "Beers Brewed",
  23 
       MAX(abv) AS "Max ABV",
  24 
       MIN(abv) AS "Min ABV",
  25 
       ROUND(AVG(abv),2) AS "Avg ABV"
  26 
FROM breweries
  27 
INNER JOIN beers ON beers.brewery_id = breweries.brewery_id    
  28 
      AND beers.abv >= 0
  29 
GROUP BY name, beers.brewery_id
  30 
ORDER BY AVG(abv) desc;
  31 
 
  32 
 
  33 
-- 4.  Display all beer names that (1) belong to a category with a name containing “LagerEsomewhere in the name and (2) have an alcohol by volume (ABV) of eight or greater.  Show the beer names in alphabetical order.
  34 
SELECT beer_name
  35 
FROM beers
  36 
INNER JOIN categories ON categories.category_id = beers.cat_id
  37 
WHERE category_name LIKE '%Lager%' AND ABV >= 8.0
  38 
ORDER BY beer_name asc;
  39 
 
  40 
 
  41 
-- 5.  Display each brewer’s name and how many beers they have associated with their brewery. Only include brewers that are located outside the United States and have at least 10 different beers.  Show the brewers with the most beers first.  If there is a tie in number of beers, then sort by the brewersEnames.
  42 
SELECT name,
  43 
       COUNT(breweries.brewery_id) AS "Beers Brewed"
  44 
FROM breweries
  45 
INNER JOIN beers ON beers.brewery_id = breweries.brewery_id
  46 
      AND breweries.country <> 'United States'
  47 
GROUP by name
  48 
HAVING COUNT(breweries.brewery_id) > 10
  49 
ORDER BY COUNT(breweries.brewery_id) desc, name;
  50 
  51 
 
  52 
 
  53 
/*  Movies Database Queries */
  54 
-- 6.  Display the name of all movies that have an IMDB rating of at least 8.0, with more than 100,000 IMDB votes, and were released from 2007 to 2013.  Show the movies with the highest IMDB ratings first.
  55 
SELECT film_title
  56 
FROM relmdb.movies
  57 
WHERE imdb_rating >= 8.0
  58 
      AND imdb_votes >= 100000
  59 
      AND film_year BETWEEN 2007 AND 2013
  60 
ORDER BY imdb_rating DESC;
  61 
 
  62 
 
  63 
-- 7.  Display each movie’s title and total gross, where total gross is USA gross and worldwide gross combined. Exclude any movies that do not have values for either USA gross or worldwide gross.  Show the highest grossing movies first.
  64 
SELECT film_title,
  65 
        (worldwide_gross + usa_gross) AS total_gross
  66 
FROM relmdb.movies
  67 
WHERE worldwide_gross IS NOT NULL
  68 
      AND usa_gross IS NOT NULL
  69 
ORDER BY total_gross desc;
  70 
 
  71 
 
  72 
-- 8.  Display how many movies have an MPAA rating of G, PG, PGI13, and R.  Show the results in alphabetical order by MPAA rating.
  73 
SELECT mpaa_rating AS "MPAA Rating",
  74 
       COUNT(*) AS "# of Movies"
  75 
FROM relmdb.movies
  76 
WHERE mpaa_rating = 'G'
  77 
      OR mpaa_rating = 'PG'
  78 
      OR mpaa_rating = 'PG-13'
  79 
      OR mpaa_rating = 'R'
  80 
GROUP BY mpaa_rating
  81 
ORDER BY mpaa_rating
  82 
 
  83 
 
  84 
-- 9.  Display the titles of any movies where Tom Hanks or Tim Allen were cast members.  Each movie title should be shown only once.
  85 
SELECT DISTINCT film_title
  86 
FROM relmdb.movies
  87 
INNER JOIN relmdb.casts ON movies.film_id = casts.film_id
  88 
      AND (cast_member ='Tim Allen' OR cast_member = 'Tom Hanks');
  89 
 
  90 
SELECT film_title
  91 
FROM relmdb.movies
  92 
INNER JOIN relmdb.casts ON movies.film_id = casts.film_id
  93 
      AND (cast_member ='Tim Allen' OR cast_member = 'Tom Hanks')
  94 
GROUP BY film_title;
  95 
 
  96 
SELECT film_title
  97 
FROM relmdb.movies
  98 
INNER JOIN relmdb.casts ON movies.film_id = casts.film_id
  99 
WHERE cast_member = 'Tim Allen' OR cast_member = 'Tom Hanks')
 100 
GROUP BY film_title;
 101 
 
 102 
SELECT film_title
 103 
FROM relmdb.movies, relmdb.casts
 104 
WHERE casts.film_id = movies.film_id
 105 
AND (cast_member = 'Tom Hanks' OR cast_member = 'Tim Allen')
 106 
GROUP BY film_title;
 107 
 
 108 
SELECT DISTINCT film_title
 109 
FROM relmdb.movies, relmdb.casts
 110 
WHERE casts.film_id = movies.film_id
 111 
AND (cast_member = 'Tom Hanks' OR cast_member = 'Tim Allen');
 112 
 
 113 
 
 114 
-- 10. For each movie display its movie title, year, and how many cast members were a part of the movie.  Exclude movies with five or fewer cast members.  Display movies with the most cast members first, followed by movie year and title.
 115 
SELECT film_title,
 116 
       film_year,
 117 
       COUNT (casts.film_id) AS "Cast Size"
 118 
FROM relmdb.casts
 119 
INNER JOIN relmdb.movies ON movies.film_id = casts.film_id
 120 
GROUP by film_title, film_year, casts.film_id
 121 
HAVING COUNT (casts.film_id) > 5
 122 
ORDER BY COUNT (casts.film_id) desc, film_year asc, film_title asc;