In this task you must operate on the original state of a sample benchmark TPC-HR database....

  

Transcribed Image Text:

In this task you must operate on the original state of a sample benchmark TPC-HR database. It is explained at the end of Prologue section how to return to the original state of the database. Consider the following SELECT statements. (1) SELECT O_ORDERKEY, O_ORDERDATE, C_CUSTKEY FROM ORDERS LEFT OUTER JOIN CUSTOMER (2) SELECT * FROM PART ON ORDERS.O_CUSTKEY CUSTOMER.C_CUSTKEY; WHERE P_PARTKEY = 101 INTERSECT SELECT * FROM PART WHERE P_NAME = 'bolt'; (3) SELECT P_NAME FROM PART P WHERE (SELECT COUNT(*) FROM PART WHERE P.P_PARTKEY PART.P_PARTKEY) > 5; (4) SELECT C_NAME, C_ADDRESS FROM CUSTOMER WHERE C_NAME = (SELECT DISTINCT C_NAME FROM CUSTOMER WHERE C_NAME = 'James'); (5) SELECT DISTINCT C_CUSTKEY, C_NAME FROM CUSTOMER CROSS JOIN ORDERS; An objective of this task is to rewrite each one of the statements listed below into an equivalent SELECT statement and such that its processing costs are lower than the processing costs of the original SELECT statement. Implement SQL script solution3.sql that performs the following actions. (1) First, the script finds the query processing plans for each one of SELECT statements listed above. Use SQL script showplan.sql to list the processing plans. (2) Next, the script finds query processing plans the improved SELECT statements such that the processing costs of each improved statement are lower than the processing costs of the original one. Use SQL script showplan.sql to list the processing plans. When processing an improved SQL script solution3.sql you must put the following SQLC1 statements SPOOL solution3 SET ECHO ON SET FEEDBACK ON SET LINESIZE 300 SET PAGESIZE 300 at the beginning of each SQL script implemented and the following statement at the end of the script SPOOL OFF A report from processing of the script must have NO syntax errors! The script must be processed with SQLC1 options ECHO and FEEDBACK set to ON such that all SQL statements processed are included in the report! A report from processing of the script must have NO syntax errors ! Deliverables A file solution 3.1st that contains a report from the processing of a script solution3.sql. In this task you must operate on the original state of a sample benchmark TPC-HR database. It is explained at the end of Prologue section how to return to the original state of the database. Consider the following SELECT statements. (1) SELECT O_ORDERKEY, O_ORDERDATE, C_CUSTKEY FROM ORDERS LEFT OUTER JOIN CUSTOMER (2) SELECT * FROM PART ON ORDERS.O_CUSTKEY CUSTOMER.C_CUSTKEY; WHERE P_PARTKEY = 101 INTERSECT SELECT * FROM PART WHERE P_NAME = 'bolt'; (3) SELECT P_NAME FROM PART P WHERE (SELECT COUNT(*) FROM PART WHERE P.P_PARTKEY PART.P_PARTKEY) > 5; (4) SELECT C_NAME, C_ADDRESS FROM CUSTOMER WHERE C_NAME = (SELECT DISTINCT C_NAME FROM CUSTOMER WHERE C_NAME = 'James'); (5) SELECT DISTINCT C_CUSTKEY, C_NAME FROM CUSTOMER CROSS JOIN ORDERS; An objective of this task is to rewrite each one of the statements listed below into an equivalent SELECT statement and such that its processing costs are lower than the processing costs of the original SELECT statement. Implement SQL script solution3.sql that performs the following actions. (1) First, the script finds the query processing plans for each one of SELECT statements listed above. Use SQL script showplan.sql to list the processing plans. (2) Next, the script finds query processing plans the improved SELECT statements such that the processing costs of each improved statement are lower than the processing costs of the original one. Use SQL script showplan.sql to list the processing plans. When processing an improved SQL script solution3.sql you must put the following SQLC1 statements SPOOL solution3 SET ECHO ON SET FEEDBACK ON SET LINESIZE 300 SET PAGESIZE 300 at the beginning of each SQL script implemented and the following statement at the end of the script SPOOL OFF A report from processing of the script must have NO syntax errors! The script must be processed with SQLC1 options ECHO and FEEDBACK set to ON such that all SQL statements processed are included in the report! A report from processing of the script must have NO syntax errors ! Deliverables A file solution 3.1st that contains a report from the processing of a script solution3.sql.