POSTGRES SQL

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

1.2 Database Entities, Relationships, and Characteristics The components of the TPC-H database are defined to consist of eight separate and individual tables (the Base Tables). The relationships between columns of these tables are illustrated in Figure 2: The TPC-H Schema. Figure 2: The TPC-H Schema PART (P) SF*200.000 PARTKEY PARTSUPP (PS) SF*800.000 PARTKEY LINEITEM (L_) SF*6.000.000 ORDERKEY ORDERS (0) SF*1.500.000 ORDERKEY NAME SUPPKEY PARTKEY CUSTKEY MFGR AVAILQTY SUPPKEY ORDERSTATUS BRAND SUPPLYCOST LINENUMBER TOTALPRICE TYPE COMMENT QUANTITY ORDERDATE SIZE EXTENDEDPRICE ORDER- PRIORITY CONTAINER CUSTOMER (C_) SF*150,000 CUSTKEY DISCOUNT CLERK RETAILPRICE TAX NAME SHIP- PRIORITY COMMENT RETURNFLAG ADDRESS LINESTATUS COMMENT NATIONKEY SUPPLIER (S) SF*10,000 SUPPKEY SHIPDATE PHONE COMMITDATE ACCTBAL NAME RECEIPTDATE ADDRESS MKTSEGMENT SHIPINSTRUCT COMMENT NATIONKEY SHIPMODE PHONE COMMENT ACCTBAL NATION (N_) 25 NATIONKEY REGION (R_) COMMENT NAME REGIONKEY REGIONKEY NAME COMMENT COMMENT Legend: The parentheses following each table name contain the prefix of the column names for that table; The arrows point in the direction of the one-to-many relationships between tables; The number/formula below each table name represents the cardinality (number of rows) of the table. Some are factored by SF, the Scale Factor, to obtain the chosen database size. The cardinality for the LINEITEM table is approximate (see Clause 4.2.5). 1.2 Database Entities, Relationships, and Characteristics The components of the TPC-H database are defined to consist of eight separate and individual tables (the Base Tables). The relationships between columns of these tables are illustrated in Figure 2: The TPC-H Schema. Figure 2: The TPC-H Schema PART (P) SF*200.000 PARTKEY PARTSUPP (PS) SF*800.000 PARTKEY LINEITEM (L_) SF*6.000.000 ORDERKEY ORDERS (0) SF*1.500.000 ORDERKEY NAME SUPPKEY PARTKEY CUSTKEY MFGR AVAILQTY SUPPKEY ORDERSTATUS BRAND SUPPLYCOST LINENUMBER TOTALPRICE TYPE COMMENT QUANTITY ORDERDATE SIZE EXTENDEDPRICE ORDER- PRIORITY CONTAINER CUSTOMER (C_) SF*150,000 CUSTKEY DISCOUNT CLERK RETAILPRICE TAX NAME SHIP- PRIORITY COMMENT RETURNFLAG ADDRESS LINESTATUS COMMENT NATIONKEY SUPPLIER (S) SF*10,000 SUPPKEY SHIPDATE PHONE COMMITDATE ACCTBAL NAME RECEIPTDATE ADDRESS MKTSEGMENT SHIPINSTRUCT COMMENT NATIONKEY SHIPMODE PHONE COMMENT ACCTBAL NATION (N_) 25 NATIONKEY REGION (R_) COMMENT NAME REGIONKEY REGIONKEY NAME COMMENT COMMENT Legend: The parentheses following each table name contain the prefix of the column names for that table; The arrows point in the direction of the one-to-many relationships between tables; The number/formula below each table name represents the cardinality (number of rows) of the table. Some are factored by SF, the Scale Factor, to obtain the chosen database size. The cardinality for the LINEITEM table is approximate (see Clause 4.2.5)