--
-- 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!
--

--------------------------------------------------------------------------------
-- Connect to tpch
\c tpch
--------------------------------------------------------------------------------

--
\echo QUERY #1
\echo
-- How many suppliers does each nation have?
--
-- Schema: n_name, count
--  Order: count descending, n_name


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


--
\echo QUERY #3
\echo
-- List the cost of all parts shipped during the week of November 22, 1998.
-- Please use the correct date format in your query: http://xkcd.com/1179/
--
-- Schema: ps_partkey, ps_supplycost
--  Order: ps_supplycost descending, ps_partkey


--
\echo QUERY #4
\echo
-- Which urgent priority orders have only one line item?
--
-- Schema: o_orderkey, o_custkey, o_orderstatus
--  Order: o_orderkey


--
\echo QUERY #5
\echo
-- What is the average cost of orders made by each customer in the
-- America region?
--
-- Schema: c_custkey, c_name, avg_totalprice
--  Order: avg_totalprice


--
\echo QUERY #6
\echo
-- Which customers with an account balance over 5000 have no orders?
--
-- Schema: c_name, c_acctbal
--  Order: c_custkey


--
\echo QUERY #7
\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


--
\echo QUERY #8
\echo
-- Which parts are supplied in the Europe region?
--
-- Schema: p_partkey, p_name, p_retailprice
--  Order: p_retailprice, p_partkey


--
\echo QUERY #9
\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, p_name, p_retailprice
--  Order: p_retailprice


--
\echo QUERY #10
\echo
-- What suppliers have an inventory (total available quantity) of over
-- 125,000 parts with size over 40.
--
-- Schema: r_name, n_name, s_name, total_qty
--  Order: r_name, n_name, s_name
