Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
sql commands for 1st Bravo SQL challenge
SELECT title, rating FROM movies ORDER BY rating LIMIT 50;
SELECT title FROM movies
WHERE rating IS NULL
ORDER BY title;
SELECT title FROM movies
WHERE synopsis ILIKE '%thrilling%';
SELECT movies.title, movies.year, movies.rating FROM movies
JOIN genres ON movies.genre_id = genres.id
WHERE genres.name = 'Science Fiction & Fantasy' AND movies.year BETWEEN 1980 AND 1989
ORDER BY movies.rating DESC;
SELECT movies.title, actors.name, movies.year FROM movies
JOIN cast_members ON movies.id = cast_members.movie_id
JOIN actors ON cast_members.actor_id = actors.id
WHERE cast_members.character = 'James Bond'
ORDER BY movies.year;
SELECT movies.title, movies.year, genres.name FROM movies
JOIN genres ON movies.genre_id = genres.id
JOIN cast_members ON movies.id = cast_members.movie_id
JOIN actors ON actors.id = cast_members.actor_id
WHERE actors.name = 'Julianne Moore'
ORDER BY genres.name, movies.title;
SELECT movies.title, movies.year, studios.name FROM movies
LEFT OUTER JOIN studios ON movies.studio_id = studios.id
JOIN genres ON movies.genre_id = genres.id
WHERE genres.name = 'Horror'
ORDER BY movies.year
LIMIT 5;
-----------------------------------------------------------------
CREATE TABLE amounts
(id serial, recipe_id integer NOT NULL, ingredient_id integer NOT NULL, unit_type varchar(15) NOT NULL, unit_quantity real NOT NULL);
CREATE TABLE recipes
(id serial, name varchar(250) NOT NULL, servings integer, expected_time integer);
CREATE TABLE ingredients
(id serial, name varchar(250) NOT NULL);
INSERT INTO recipes (name, servings, expected_time)
VALUES ('Green Eggs & Ham', 2, 25);
INSERT INTO recipes (name)
VALUES ('Fried Green Tomatoes');
INSERT INTO recipes (name, servings)
VALUES ('Martini', 1);
INSERT INTO ingredients (name) VALUES ('green eggs');
INSERT INTO ingredients (name) VALUES ('ham');
INSERT INTO ingredients (name) VALUES ('large green tomatoes');
INSERT INTO ingredients (name) VALUES ('eggs');
INSERT INTO ingredients (name) VALUES ('milk');
INSERT INTO ingredients (name) VALUES ('breadcrumbs');
INSERT INTO ingredients (name) VALUES ('vegetable oil');
INSERT INTO ingredients (name) VALUES ('gin');
INSERT INTO ingredients (name) VALUES ('dry vermouth');
INSERT INTO ingredients (name) VALUES ('lemon peel');
INSERT INTO ingredients (name) VALUES ('olive');
DROP TABLE amounts;
ALTER TABLE recipes ADD PRIMARY KEY (id);
ALTER TABLE ingredients ADD PRIMARY KEY (id);
CREATE TABLE amounts (id serial, recipe_id integer NOT NULL, ingredient_id integer NOT NULL, unit_type varchar(50), unit_quant real, PRIMARY KEY (id), FOREIGN KEY (recipe_id) REFERENCES recipes(id),
FOREIGN KEY (ingredient_id) REFERENCES ingredients(id));
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (1, 1, 'egg(s)', 4);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (1, 2, 'pound(s)', 0.5);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (2, 3, 'tomato(es)', 3);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (2, 4, 'egg(s)', 2);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (2, 6, 'cup(s)', 0.5);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (2, 7, 'quart(s)', 1);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (3, 8, 'ounce(s)', 2);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (3, 9, 'ounce(s)', 1);
INSERT INTO amounts (recipe_id, ingredient_id, unit_type, unit_quant)
VALUES (3, 10, 'peel', 1);
INSERT INTO ingredients (name) VALUES ('Vodka');
UPDATE amounts SET unit_quant = 3 WHERE id = 8;
UPDATE amounts SET ingredient_id = 12 WHERE id = 8;
DELETE FROM amounts WHERE id < 3;
DELETE FROM recipes WHERE name ILIKE '%Eggs &%';
DELETE FROM ingredients WHERE id < 3;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment