a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the...

 

      

Transcribed Image Text:

a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the file TPCHR, CREATE TABLE REGION( R_REGIONKEY NUMBER (12) NOT NULL, R_NAME CHAR (25) NOT NULL, NOT NULL, R COMMENT VARCHAR (152) CONSTRAINT REGION_PKEY PRIMARY KEY (R_REGIONKEY), CONSTRAINT REGION CHECK CHECK (R_REGIONKEY >= 0)); CREATE TABLE NATION ( N_NATIONKEY NUMBER (12) NOT NULL, N_NAME CHAR(25) NOT NULL, N REGIONKEY NUMBER (12) NOT NULL, N COMMENT VARCHAR (152) NOT NULL, CONSTRAINT NATION_PKEY PRIMARY KEY (N_NATIONKEY), CONSTRAINT NATION_FKEY1 FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY), CONSTRAINT NATION_CHECK1 CHECK (N_NATIONKEY >= 0)); CREATE TABLE PART ( P PARTKEY P NAME P MFGR P_BRAND P TYPE NUMBER (12) NOT NULL, VARCHAR (55) NOT NULL, VARCHAR (25) NOT NULL, CHAR(10) NOT NULL, VARCHAR (25) NOT NULL, P SIZE NUMBER (12) NOT NULL, P CONTAINER CHAR(10) NOT NULL, P RETAILPRICE NUMBER (12,2) NOT NULL, NOT NULL, P COMMENT VARCHAR (23) CONSTRAINT PART PEKEY PRIMARY KEY (P_PARTKEY), CONSTRAINT PART_CHECK1 CHECK (P_PARTKEY >= 0), CONSTRAINT PART CHECK2 CHECK (P SIZE >= 0), CONSTRAINT PART_CHECK3 CHECK (P RETAIL PRICE >= 0)); CREATE TABLE SUPPLIER ( S_SUPPKEY S NAME NUMBER (12) NOT NULL, S ADDRESS S NATIONKEY S PHONE S ACCTBAL S COMMENT CHAR(25) NOT NULL, VARCHAR (40) NOT NULL, NUMBER (12) NOT NULL, CHAR (15) NOT NULL, NUMBER (12,2) NOT NULL, VARCHAR (101) NOT NULL, CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY), CONSTRAINT SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT SUPPLIER_CHECK1 CHECK (S_SUPPKEY >= 0)); CREATE TABLE PARTSUPP ( PS PARTKEY NUMBER (12) NOT NULL, PS SUPPKEY NUMBER (12) NOT NULL, PS AVAILOTY NUMBER (12) NOT NULL, PS_SUPPLY COST NUMBER (12,2) NOT NULL, PS COMMENT VARCHAR (199) NOT NULL, CONSTRAINT PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY), CONSTRAINT PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY) REFERENCES PART (P_PARTKEY), CONSTRAINT PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY), CONSTRAINT PARTSUPP CHECK1 CHECK (PS PARTKEY >= 0), CONSTRAINT PARTSUPP CHECK2 CHECK (PS AVAILOTY >= 0), CONSTRAINT PARTSUPP_CHECK3 CHECK (PS SUPPLYCOST >= 0)); CREATE TABLE CUSTOMER ( C_CUSTKEY C NAME NUMBER (12) NOT NULL, VARCHAR (25) NOT NULL, C ADDRESS VARCHAR (40) NOT NULL, C NATIONKEY NUMBER (12) NOT NULL, C PHONE CHAR(15) NOT NULL, C ACCTBAL NUMBER (12,2) NOT NULL, C MKTSEGMENT CHAR(10) NOT NULL, C COMMENT VARCHAR (117) NOT NULL, CONSTRAINT CUSTOMER_PKEY PRIMARY KEY (C_CUSTKEY), CONSTRAINT CUSTOMER FKEY1 FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT CUSTOMER_CHECK1 CHECK (C_CUSTKEY >= 0)); CREATE TABLE ORDERS ( O ORDERKEY O CUSTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, O ORDERSTATUS CHAR(1) NOT NULL, O TOTALPRICE NUMBER (12,2) NOT NULL, O ORDERDATE DATE O_ORDERPRIORITY CHAR (15) O CLERK NOT NULL, NOT NULL, CHAR (15) NOT NULL, O SHIPPRIORITY NUMBER (12) NOT NULL, O COMMENT VARCHAR (79) NOT NULL, CONSTRAINT ORDERS PKEY PRIMARY KEY (O_ORDERKEY), CONSTRAINT ORDERS FKEY1 FOREIGN KEY (O CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY), CONSTRAINT ORDER_CHECKI CHECK (O_TOTALPRICE >= 0)); CREATE TABLE LINEITEM( L_ORDERKEY L PARTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, L SUPPKEY NUMBER (12) NOT NULL, L LINENUMBER NUMBER (12) NOT NULL, L QUANTITY NUMBER (12,2) NOT NULL, DATE NUMBER (12,2) L_EXTENDEDPRICE NUMBER (12,2) NOT NULL, L DISCOUNT L TAX NUMBER (12,2) NOT NULL, NOT NULL, L_RETURNFLAG CHAR(1) L LINESTATUS CHAR (1) NOT NULL, NOT NULL, NOT NULL, NOT NULL, L RECEIPT DATE L SHIPMODE DATE NOT NULL, NOT NULL, NOT NULL, L SHIPDATE L COMMITDATE DATE L SHIPINSTRUCT CHAR(25) L COMMENT CHAR(10) NOT NULL, VARCHAR (44) CONSTRAINT LINEITEM PKEY PRIMARY KEY (L_ORDERKEY, L LINENUMBER), CONSTRAINT LINEITEM_FKEY1 FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY), CONSTRAINT LINEITEM_FKEY2 FOREIGN KEY (L_PARTKEY) REFERENCES PART (P_PARTKEY), CONSTRAINT LINEITEM_FKEY3 FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS SUPPKEY), CONSTRAINT LINEITEM_FKEY4 FOREIGN KEY (L_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY), CONSTRAINT LINEITEM_CHECK1 CHECK (L_QUANTITY >= 0), CONSTRAINT LINEITEM_CHECK2 CHECK (L_EXTENDEDPRICE >= 0), CONSTRAINT LINEITEM CHECK3 CHECK (L TAX >= 0), CONSTRAINT LINEITEM_CHECK4 CHECK (L DISCOUNT BETWEEN 0.00 AND 1.00) ); In addition, the following indexes were created: CREATE INDEX A1TASK3IDX1 ON ORDERS (O_CLERK, O_ORDERSTATUS); CREATE INDEX A1TASK3IDX2 ON LINEITEM(L_ORDERKEY); For each one of the 10 types of different query or data manipulation statements listed below, provide one SQL statement consistent with the respective type and such that processing of SQL statement benefits from at least one of the indices over the relational tables ORDERS and LINEITEM. i. Projection queries (SELECT without WHERE clause). Selection queries (SELECT with WHERE claluse). ii. iii. iv. V. Join queries (join of two or more relational tables). Queries processed by accessing only an index. Group by query (SELECT with GROUP BY clause and aggregation function). vi. Antijoin query (SELECT with NOT IN, NOT EXISTS). vii. viii. Simple aggregation queries (SELECT with MAX, MIN, SUM, functions). Update statement. ix. Delete statement. X. Query with sorting (SELECT with ORDER BY clause). (4.0 marks) a. Consider the TPCHR benchmark database created through processing of CREATE TABLE statements include in the file TPCHR, CREATE TABLE REGION( R_REGIONKEY NUMBER (12) NOT NULL, R_NAME CHAR (25) NOT NULL, NOT NULL, R COMMENT VARCHAR (152) CONSTRAINT REGION_PKEY PRIMARY KEY (R_REGIONKEY), CONSTRAINT REGION CHECK CHECK (R_REGIONKEY >= 0)); CREATE TABLE NATION ( N_NATIONKEY NUMBER (12) NOT NULL, N_NAME CHAR(25) NOT NULL, N REGIONKEY NUMBER (12) NOT NULL, N COMMENT VARCHAR (152) NOT NULL, CONSTRAINT NATION_PKEY PRIMARY KEY (N_NATIONKEY), CONSTRAINT NATION_FKEY1 FOREIGN KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY), CONSTRAINT NATION_CHECK1 CHECK (N_NATIONKEY >= 0)); CREATE TABLE PART ( P PARTKEY P NAME P MFGR P_BRAND P TYPE NUMBER (12) NOT NULL, VARCHAR (55) NOT NULL, VARCHAR (25) NOT NULL, CHAR(10) NOT NULL, VARCHAR (25) NOT NULL, P SIZE NUMBER (12) NOT NULL, P CONTAINER CHAR(10) NOT NULL, P RETAILPRICE NUMBER (12,2) NOT NULL, NOT NULL, P COMMENT VARCHAR (23) CONSTRAINT PART PEKEY PRIMARY KEY (P_PARTKEY), CONSTRAINT PART_CHECK1 CHECK (P_PARTKEY >= 0), CONSTRAINT PART CHECK2 CHECK (P SIZE >= 0), CONSTRAINT PART_CHECK3 CHECK (P RETAIL PRICE >= 0)); CREATE TABLE SUPPLIER ( S_SUPPKEY S NAME NUMBER (12) NOT NULL, S ADDRESS S NATIONKEY S PHONE S ACCTBAL S COMMENT CHAR(25) NOT NULL, VARCHAR (40) NOT NULL, NUMBER (12) NOT NULL, CHAR (15) NOT NULL, NUMBER (12,2) NOT NULL, VARCHAR (101) NOT NULL, CONSTRAINT SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY), CONSTRAINT SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT SUPPLIER_CHECK1 CHECK (S_SUPPKEY >= 0)); CREATE TABLE PARTSUPP ( PS PARTKEY NUMBER (12) NOT NULL, PS SUPPKEY NUMBER (12) NOT NULL, PS AVAILOTY NUMBER (12) NOT NULL, PS_SUPPLY COST NUMBER (12,2) NOT NULL, PS COMMENT VARCHAR (199) NOT NULL, CONSTRAINT PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY), CONSTRAINT PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY) REFERENCES PART (P_PARTKEY), CONSTRAINT PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY), CONSTRAINT PARTSUPP CHECK1 CHECK (PS PARTKEY >= 0), CONSTRAINT PARTSUPP CHECK2 CHECK (PS AVAILOTY >= 0), CONSTRAINT PARTSUPP_CHECK3 CHECK (PS SUPPLYCOST >= 0)); CREATE TABLE CUSTOMER ( C_CUSTKEY C NAME NUMBER (12) NOT NULL, VARCHAR (25) NOT NULL, C ADDRESS VARCHAR (40) NOT NULL, C NATIONKEY NUMBER (12) NOT NULL, C PHONE CHAR(15) NOT NULL, C ACCTBAL NUMBER (12,2) NOT NULL, C MKTSEGMENT CHAR(10) NOT NULL, C COMMENT VARCHAR (117) NOT NULL, CONSTRAINT CUSTOMER_PKEY PRIMARY KEY (C_CUSTKEY), CONSTRAINT CUSTOMER FKEY1 FOREIGN KEY (C_NATIONKEY) REFERENCES NATION (N_NATIONKEY), CONSTRAINT CUSTOMER_CHECK1 CHECK (C_CUSTKEY >= 0)); CREATE TABLE ORDERS ( O ORDERKEY O CUSTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, O ORDERSTATUS CHAR(1) NOT NULL, O TOTALPRICE NUMBER (12,2) NOT NULL, O ORDERDATE DATE O_ORDERPRIORITY CHAR (15) O CLERK NOT NULL, NOT NULL, CHAR (15) NOT NULL, O SHIPPRIORITY NUMBER (12) NOT NULL, O COMMENT VARCHAR (79) NOT NULL, CONSTRAINT ORDERS PKEY PRIMARY KEY (O_ORDERKEY), CONSTRAINT ORDERS FKEY1 FOREIGN KEY (O CUSTKEY) REFERENCES CUSTOMER (C_CUSTKEY), CONSTRAINT ORDER_CHECKI CHECK (O_TOTALPRICE >= 0)); CREATE TABLE LINEITEM( L_ORDERKEY L PARTKEY NUMBER (12) NOT NULL, NUMBER (12) NOT NULL, L SUPPKEY NUMBER (12) NOT NULL, L LINENUMBER NUMBER (12) NOT NULL, L QUANTITY NUMBER (12,2) NOT NULL, DATE NUMBER (12,2) L_EXTENDEDPRICE NUMBER (12,2) NOT NULL, L DISCOUNT L TAX NUMBER (12,2) NOT NULL, NOT NULL, L_RETURNFLAG CHAR(1) L LINESTATUS CHAR (1) NOT NULL, NOT NULL, NOT NULL, NOT NULL, L RECEIPT DATE L SHIPMODE DATE NOT NULL, NOT NULL, NOT NULL, L SHIPDATE L COMMITDATE DATE L SHIPINSTRUCT CHAR(25) L COMMENT CHAR(10) NOT NULL, VARCHAR (44) CONSTRAINT LINEITEM PKEY PRIMARY KEY (L_ORDERKEY, L LINENUMBER), CONSTRAINT LINEITEM_FKEY1 FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY), CONSTRAINT LINEITEM_FKEY2 FOREIGN KEY (L_PARTKEY) REFERENCES PART (P_PARTKEY), CONSTRAINT LINEITEM_FKEY3 FOREIGN KEY (L_PARTKEY, L_SUPPKEY) REFERENCES PARTSUPP (PS_PARTKEY, PS SUPPKEY), CONSTRAINT LINEITEM_FKEY4 FOREIGN KEY (L_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY), CONSTRAINT LINEITEM_CHECK1 CHECK (L_QUANTITY >= 0), CONSTRAINT LINEITEM_CHECK2 CHECK (L_EXTENDEDPRICE >= 0), CONSTRAINT LINEITEM CHECK3 CHECK (L TAX >= 0), CONSTRAINT LINEITEM_CHECK4 CHECK (L DISCOUNT BETWEEN 0.00 AND 1.00) ); In addition, the following indexes were created: CREATE INDEX A1TASK3IDX1 ON ORDERS (O_CLERK, O_ORDERSTATUS); CREATE INDEX A1TASK3IDX2 ON LINEITEM(L_ORDERKEY); For each one of the 10 types of different query or data manipulation statements listed below, provide one SQL statement consistent with the respective type and such that processing of SQL statement benefits from at least one of the indices over the relational tables ORDERS and LINEITEM. i. Projection queries (SELECT without WHERE clause). Selection queries (SELECT with WHERE claluse). ii. iii. iv. V. Join queries (join of two or more relational tables). Queries processed by accessing only an index. Group by query (SELECT with GROUP BY clause and aggregation function). vi. Antijoin query (SELECT with NOT IN, NOT EXISTS). vii. viii. Simple aggregation queries (SELECT with MAX, MIN, SUM, functions). Update statement. ix. Delete statement. X. Query with sorting (SELECT with ORDER BY clause). (4.0 marks)