--
-- 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 200 results.
--
-- DO NOT MODIFY OR DELETE ANY OTHER LINES!
--

--------------------------------------------------------------------------------
-- Modifications
\c sec3
--------------------------------------------------------------------------------

--
\echo QUERY #1
\echo
-- Make a copy of the UCI ML "Bank Marketing" data set.
-- Note this table will be created in your own schema.

DROP TABLE IF EXISTS client;  -- DO NOT EDIT!
CREATE TABLE client AS SELECT * FROM public.bank;  -- DO NOT EDIT!

--
\echo CHANGE #1
\echo
-- Add the following record to your client table:
--   36, services, married, tertiary, no, 474, yes, yes,
--   unknown, 22, feb, 50, 22, -1, 0, success, yes



--
\echo CHANGE #2
\echo
-- Replace all "admin." job values with "administrator".



--
\echo CHANGE #3
\echo
--
-- Delete all of the clients who did not subscribe.
-- Also delete any clients for the month of August.



--
\echo RESULTS
\echo
-- Select the table for comparison with expected output.
-- For convenience, sort by age, balance, and duration.

SELECT * FROM client ORDER BY age, balance, duration;  -- DO NOT EDIT!

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

--
\echo QUERY #2
\echo
-- List all TV series from 2015 that have "Musical" as one of its genres.
--
-- Schema: id, title
--  Order: title



--
\echo QUERY #3
\echo
-- Which video games in 2015 have fewer than three cast_info records?
--
-- Schema: title, year, count
--  Order: year, title



--------------------------------------------------------------------------------
-- TPCH Queries
\c tpch
--------------------------------------------------------------------------------

--
\echo QUERY #4
\echo
-- For each nation, how many customers have a negative account balance?
--
-- Schema: r_name, n_name, count
--  Order: n_name



--
\echo QUERY #5
\echo
-- What parts containing the word "chocolate" were ordered on or after
-- August 1, 1998 and shipped by mail?
--
-- Schema: p_name, ps_supplycost, l_quantity
--  Order: p_partkey


