--
-- 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 database
\c tpch
-- -----------------------------------------------------------------------------

--
\echo
\echo Query #1
--
-- Show the customer name, order date, and line items for order number 3.
--
-- Schema: c_name, o_orderdate, l_partkey, l_suppkey, l_quantity, l_extendedPrice
--  Order: l_linenumber


--
\echo
\echo Query #2
--
-- Show the part name, supply cost, and retail price of each line item.
--
-- Schema: l_orderkey, l_partkey, l_suppkey, p_name, ps_supplycost, p_retailprice
--  Order: l_orderkey, l_linenumber


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


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


--
\echo
\echo Query #5
--
-- 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
\echo Query #6
--
-- Get the min, max, and average supply cost of each part. The average supply cost
-- must be rounded to 2 decimal places.
--
-- Schema: p_name, p_retailprice, min_supplycost, max_supplycost, avg_supplycost
--  Order: p_partkey


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


--
\echo
\echo Query #8
--
-- Find orders with a total profit of over $375,000. The profit of a single
-- lineitem is defined as quantity * (retail price - supply cost). The profit
-- of an order is the sum of its lineitem profits. For each order, show also
-- the number of lineitems, the total quantity of items (rounded to 0 decimal
-- places), and the total profit of the order (rounded to 2 decimal places).
--
-- Schema: l_orderkey, num_lineitems, total_quantity, total_profit
--  Order: total_profit (descending), total_quantity

