--
-- Name: TYPE YOUR NAME HERE
--
-- Write your queries below each comment. Please use good style (one clause
-- per line, JOIN syntax, indentation) and make sure all queries end with a
-- semicolon. When necessary, limit the output to the first 500 results.
--
-- DO NOT MODIFY OR DELETE ANY OTHER LINES!
--

--------------------------------------------------------------------------------
-- IMDb Queries
\c imdb2016
--------------------------------------------------------------------------------

--
\echo QUERY #1
\echo
-- For the movies named Star Wars, what kind of
-- movie was it, and what year was it released?
--
-- Schema: kind varchar(15), year integer
--  Order: year, kind


--
\echo QUERY #2
\echo
-- Show all actors and actresses for The Lego Movie (2014).
--
-- Schema: real_name text, char_name text
--  Order: nr_order


--
\echo QUERY #3
\echo
-- For each tv movie in 2015 that has runtime information,
-- what is the plot of the movie? If there is no plot record
-- in the database, simply use NULL for that movie's plot.
-- Only display the first 15 results (there are 2040 total).
--
-- Schema: title text, runtime text, plot text
--  Order: descending runtime, ascending title


--
\echo QUERY #4
\echo
-- List the top 10 actors and actresses with "Smith" in their name.
-- By "top" we mean those who have been in the most movies of any
-- type. If a person plays more than one role in a particular movie,
-- then that movie should only be counted once for that person.
--
-- Schema: name text, count bigint
--  Order: descending count, ascending name


--
\echo QUERY #5
\echo
-- List all the character names that Johnny Depp has ever had as an actor.
--
-- Schema: char_name text
--  Order: char_name


--
\echo QUERY #6
\echo
-- For all movies in 2016 (of any kind), which ones have over 50 movie_info
-- records (of any type)?
--
-- Schema: title text, kind varchar(15), count bigint
--  Order: descending count, ascending title


--------------------------------------------------------------------------------
-- TPC-H Queries
\c tpch
--------------------------------------------------------------------------------

--
\echo QUERY #7
\echo
-- Which parts are supplied in the Europe region?
--
-- Schema: p_partkey integer, p_name varchar(55), p_retailprice numeric
--  Order: p_retailprice, p_partkey


--
\echo QUERY #8
\echo
-- Find the minimum cost supplier for each part.
--
-- You must use a subquery to receive full credit.
-- Hint: Find the minimum cost of each part first.
--
-- Schema: ps_partkey integer, ps_suppkey integer, min_supplycost numeric
--  Order: ps_partkey


--
\echo QUERY #9
\echo
-- Which urgent priority orders have only one line item?
--
-- Schema: o_orderkey integer, o_custkey integer, o_orderstatus char(1)
--  Order: o_orderkey


--
\echo QUERY #10
\echo
-- Which parts supplied by Canadian suppliers have never been ordered?
--
-- Hint: there won't be any line items for these (partkey, suppkey) pairs.
--
-- Schema: p_partkey integer, p_name varchar(55), p_retailprice numeric
--  Order: p_retailprice

