Some Test Subqueries

CREATE TABLE NATION  (
    N_NATIONKEY  INT NOT NULL,
    N_NAME       CHAR(25) NOT NULL,
    N_REGIONKEY  INT NOT NULL,
    N_COMMENT    VARCHAR(152)
);

CREATE TABLE REGION  (
    R_REGIONKEY  INT NOT NULL,
    R_NAME       CHAR(25) NOT NULL,
    R_COMMENT    VARCHAR(152)
);

CREATE TABLE PART  (
    P_PARTKEY     INT NOT NULL,
    P_NAME        VARCHAR(55) NOT NULL,
    P_MFGR        CHAR(25) NOT NULL,
    P_BRAND       CHAR(10) NOT NULL,
    P_TYPE        VARCHAR(25) NOT NULL,
    P_SIZE        INT NOT NULL,
    P_CONTAINER   CHAR(10) NOT NULL,
    P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL
);

CREATE TABLE SUPPLIER (
    S_SUPPKEY     INT NOT NULL,
    S_NAME        CHAR(25) NOT NULL,
    S_ADDRESS     VARCHAR(40) NOT NULL,
    S_NATIONKEY   INT NOT NULL,
    S_PHONE       CHAR(15) NOT NULL,
    S_ACCTBAL     DECIMAL(15,2) NOT NULL,
    S_COMMENT     VARCHAR(101) NOT NULL
);

CREATE TABLE PARTSUPP (
    PS_PARTKEY     INT NOT NULL,
    PS_SUPPKEY     INT NOT NULL,
    PS_AVAILQTY    INT NOT NULL,
    PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
    PS_COMMENT     VARCHAR(199) NOT NULL
);

CREATE TABLE CUSTOMER (
    C_CUSTKEY     INT NOT NULL,
    C_NAME        VARCHAR(25) NOT NULL,
    C_ADDRESS     VARCHAR(40) NOT NULL,
    C_NATIONKEY   INT NOT NULL,
    C_PHONE       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL
);

CREATE TABLE ORDERS (
    O_ORDERKEY       INT NOT NULL,
    O_CUSTKEY        INT NOT NULL,
    O_ORDERSTATUS    CHAR(1) NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    O_ORDERDATE      DATE NOT NULL,
    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    O_CLERK          CHAR(15) NOT NULL,
    O_SHIPPRIORITY   INT NOT NULL,
    O_COMMENT        VARCHAR(79) NOT NULL
);

CREATE TABLE LINEITEM (
    L_ORDERKEY      INT NOT NULL,
    L_PARTKEY       INT NOT NULL,
    L_SUPPKEY       INT NOT NULL,
    L_LINENUMBER    INT NOT NULL,
    L_QUANTITY      DECIMAL(15,2) NOT NULL,
    L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
    L_DISCOUNT      DECIMAL(15,2) NOT NULL,
    L_TAX           DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG    CHAR(1) NOT NULL,
    L_LINESTATUS    CHAR(1) NOT NULL,
    L_SHIPDATE      DATE NOT NULL,
    L_COMMITDATE    DATE NOT NULL,
    L_RECEIPTDATE   DATE NOT NULL,
    L_SHIPINSTRUCT  CHAR(25) NOT NULL,
    L_SHIPMODE      CHAR(10) NOT NULL,
    L_COMMENT       VARCHAR(44) NOT NULL
);

-- Insert test data into Customer table
INSERT INTO customer (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment)
VALUES
  (1, 'Customer1', 'Address1', 1, '123-456-7890', 1000.50, 'Segment1', 'Comment1'),
  (2, 'Customer2', 'Address2', 2, '234-567-8901', 2000.75, 'Segment2', 'Comment2'),
  (3, 'Customer3', 'Address3', 3, '345-678-9012', 3000.00, 'Segment3', 'Comment3');

-- Insert test data into Orders table
INSERT INTO orders (o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment)
VALUES
  (1, 1, 'F', 100.25, '2024-04-01', 'Low', 'Clerk1', 1, 'Comment1'),
  (2, 2, 'O', 200.50, '2024-04-02', 'Medium', 'Clerk2', 2, 'Comment2'),
  (3, 3, 'F', 300.75, '2024-04-03', 'High', 'Clerk3', 3, 'Comment3');

-- Insert test data into Lineitem table
INSERT INTO lineitem (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment)
VALUES
  (1, 1, 1, 1, 10, 100.00, 0.05, 0.02, 'R', 'Shipped', '2024-04-05', '2024-04-06', '2024-04-07', 'Take Care', 'Air', 'Comment1'),
  (2, 2, 2, 2, 20, 200.00, 0.10, 0.03, 'N', 'Pending', '2024-04-06', '2024-04-07', NULL, 'Standard', 'Comment2'),
  (3, 3, 3, 3, 30, 300.00, 0.15, 0.04, 'R', 'Shipped', '2024-04-07', '2024-04-08', '2024-04-09', 'Handle with Care', 'Express', 'Comment3');

-- Insert test data into Part table
INSERT INTO part (p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment)
VALUES
  (1, 'Part1', 'MFGR1', 'Brand1', 'Type1', 10, 'Container1', 50.00, 'Comment1'),
  (2, 'Part2', 'MFGR2', 'Brand2', 'Type2', 20, 'Container2', 75.00, 'Comment2'),
  (3, 'Part3', 'MFGR3', 'Brand3', 'Type3', 30, 'Container3', 100.00, 'Comment3');

-- Insert test data into Supplier table
INSERT INTO supplier (s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment)
VALUES
  (1, 'Supplier1', 'Address1', 1, '111-222-3333', 5000.00, 'Comment1'),
  (2, 'Supplier2', 'Address2', 2, '222-333-4444', 6000.00, 'Comment2'),
  (3, 'Supplier3', 'Address3', 3, '333-444-5555', 7000.00, 'Comment3');

-- -- Insert test data into Nation table
INSERT INTO nation (n_nationkey, n_name, n_regionkey, n_comment)
VALUES
  (1, 'Nation1', 1, 'Comment1'),
  (2, 'Nation2', 2, 'Comment2'),
  (3, 'Nation3', 3, 'Comment3');

-- Insert test data into Region table
INSERT INTO region (r_regionkey, r_name, r_comment)
VALUES
  (1, 'Region1', 'Comment1'),
  (2, 'Region2', 'Comment2'),
  (3, 'Region3', 'Comment3');

-- Uncorrelated
-- SELECT c.c_name, (SELECT MAX(o.o_orderdate) FROM orders o)
-- AS max_order_date
-- FROM customer c;

-- Correlated
SELECT c.c_name, o.o_totalprice
FROM customer c
JOIN orders o ON c.c_custkey = o.o_custkey
WHERE o.o_totalprice = (
    SELECT MAX(o2.o_totalprice)
    FROM orders o2
    WHERE c.c_custkey = o2.o_custkey
);

-- SELECT c.c_name, o.o_orderpriority
-- FROM customer c
-- JOIN orders o ON c.c_custkey = o.o_custkey
-- WHERE o.o_orderkey = (
--     SELECT FIRST_VALUE(o2.o_orderkey)
--     FROM orders o2
--     WHERE c.c_phone = o2.o_clerk
-- );

-- Select clause subquery
-- EXPLAIN SELECT
--     c_custkey,
--     c_name,
--     (
--         SELECT AVG(o_totalprice)
--         FROM (
--             SELECT o.o_totalprice
--             FROM orders o
--             WHERE o.o_custkey = c.c_custkey
--             ORDER BY o.o_totalprice DESC
--             LIMIT 5
--         ) AS top_orders
--     ) AS avg_total_price
-- FROM customer c;

-- SELECT EXISTS (SELECT * FROM orders o);
-- SELECT 6 IN (SELECT o.o_orderkey FROM orders o);

-- More Complex
-- EXPLAIN SELECT
--     c_custkey,
--     c_name,
--     (
--         SELECT COUNT(*)
--         FROM (
--             SELECT o.o_orderkey
--             FROM orders o
--             WHERE o.o_custkey = c.c_custkey
--         ) AS distinct_orders
--     ) AS num_distinct_orders,
--     (
--         SELECT AVG(o_totalprice)
--         FROM (
--             SELECT o.o_totalprice
--             FROM orders o
--             WHERE o.o_custkey = c.c_custkey
--             ORDER BY o.o_totalprice DESC
--             LIMIT 5
--         ) AS top_orders
--     ) AS avg_top_order_priority
-- FROM customer c
-- ORDER BY num_distinct_orders DESC;


-- Complex example
-- EXPLAIN SELECT
--     c.c_custkey AS c_custkey,
--     c.c_name AS c_customer_name,
--     (
--         SELECT COUNT(DISTINCT o.o_orderkey)
--         FROM orders o
--         WHERE o.o_custkey = c.c_custkey
--     ) AS order_count,
--     (
--         SELECT AVG(l.l_quantity)
--         FROM lineitem l
--         WHERE l.l_orderkey IN (
--             SELECT o.o_orderkey
--             FROM orders o
--             WHERE o.o_custkey = c.c_custkey
--         )
--     ) AS avg_order_quantity,
--     (
--         SELECT MAX(l.l_extendedprice)
--         FROM lineitem l
--         WHERE l.l_orderkey IN (
--             SELECT o.o_orderkey
--             FROM orders o
--             WHERE o.o_custkey = c.c_custkey
--         )
--     ) AS max_order_extended_price
-- FROM
--     customer c
-- WHERE
--     c.c_nationkey = (
--         SELECT n.n_nationkey
--         FROM nation n
--         WHERE n.n_name = 'GERMANY'
--     )
-- ORDER BY
--     order_count DESC, c_customer_name ASC;