SQL_Query_Exercises

sql_query_exercises.md

MOVIES

1. What are the top 50 worst rated movies? The results should include the movie title and rating and be sorted by the worst rating first.

SELECT title, rating FROM movies WHERE rating IS NOT NULL ORDER BY movies.rating ASC LIMIT 50;

2. What movies do not have a rating? The results should include just the movie titles in sorted order.

SELECT title FROM movies WHERE rating IS NULL ORDER BY title ASC;

3. What movies have the word "thrilling" in their synopsis? The results should just include the movie title.

SELECT title FROM movies WHERE movies.synopsis LIKE '%thrilling%';

4. What were the highest rated 'Science Fiction & Fantasy' movies released in the 80's? The results should include the movie title, the year released, and rating sorted by highest rating first.

SELECT title, year, rating FROM movies JOIN genres ON movies.genre_id = genres.id WHERE genres.id = 17 AND movies.year < 1990 AND movies.year > 1979 ORDER BY movies.rating DESC;

5. What actors have starred as James Bond? The results should include the actor name, movie title, year released, and be sorted by year in ascending order (earliest year appears first).

SELECT actors.name, movies.title, movies.year 
  FROM cast_members 
    JOIN movies ON cast_members.movie_id = movies.id 
    JOIN actors ON cast_members.actor_id = actors.id
  WHERE character LIKE '%James Bond%'
  ORDER BY movies.year ASC;

6. What movies has Julianne Moore starred in? The results should include the movie title, year released, and name of the genre, sorted by genre first and then movie title.

SELECT movies.title AS movie, movies.year AS year, genres.name AS genre FROM cast_members JOIN actors ON cast_members.actor_id = actors.id JOIN movies ON movies.id = cast_members.movie_id JOIN genres ON movies.genre_id = genres.id WHERE actors.name LIKE '%Julianne Moore%' ORDER BY genre, title ASC;

7. What were the five earliest horror movies and what studios produced them? Include the movie title, year released, and studio name (if any) in the results sorted by year.

SELECT movies.title AS movie, movies.year AS year, studios.name AS studio FROM movies JOIN genres ON movies.genre_id = genres.id LEFT OUTER JOIN studios ON movies.studio_id = studios.id WHERE genres.name ILIKE '%horror%' ORDER BY year LIMIT 5;

RECIPES

$&gt;createdb recipies $&gt;psql recipies

CREATE TABLE recipes ( id serial, name VARCHAR(200) NOT NULL, servings integer, total_time_in_minutes integer, directions VARCHAR(1000) NOT NULL );

CREATE TABLE ingredients ( id serial, name VARCHAR(200) NOT NULL, recipe_id integer NOT NULL );

INSERT INTO ingredients (name, recipe_id) VALUES ('4 green eggs', 1), ('1/2 lb ham', 1), ('3 large green tomatoes', 2), ('2 eggs', 2), ('1/2 cup milk', 2), ('1/2 cup breadcrumbs', 2), ('1 quart vegetable oil', 2), ('2 ounces of gin', 3), ('1 ounce of dry vermouth', 3), ('(optional) lemon peel or olive', 3);

INSERT INTO recipes (name, servings, total_time_in_minutes, directions) VALUES ('Green Eggs and Ham', 2, 25, '1. Cook the eggs. 2. Cook the ham. 3. Combine.'), ('Fried Green Tomatoes',NULL,NULL, '1. Slice the tomatoes a half-inch thick. 2. Whisk eggs and milk together. 3. Dip tomatoes in egg mixture and then breadcrumbs. 4. Heat oil in a large skillet. 5. Fry the tomatoes in the oil.'), ('Martini', 1,NULL, '1. Pour all ingredients into mixing glass with ice cubes. 2. Stir well. 3. Strain in chilled martini cocktail glass. 4. Squeeze oil from lemon peel onto the drink, or garnish with olive.');

UPDATE ingredients SET name = '3 oz vodka' WHERE name LIKE '%gin%';

DELETE FROM recipes WHERE id = 1; DELETE FROM ingredients WHERE recipe_id = 1;