Exam 1z0-071 All QuestionsBrowse all questions from this exam
Go to Exam
Question 217

Examine these statements and results:

How many rows are retrieved by the last query?

    Correct Answer: C

    The final query retrieves 14 rows. A Global Temporary Table (GTT) has session-specific data, and by default, it deletes rows on commit unless specified otherwise. Here, the initial 'INSERT INTO t_emp SELECT * FROM emp' inserts 14 rows, which are then committed and removed. The second insertion also adds 14 rows, leading the count to be 14 when the final query is executed since these rows belong to the same session and have not been committed yet.

Discussion
ArslanAltafOption: C

If you haven't mentioned on commit preserve / Delete, the default behavior of system is to delete all the rows of GTT. So, once commit is complete, rows are deleted. Again, Insert command will copy all the rows from EMP which is 14 C is correct. Tested as well

McromeoOption: C

C is correct

WingLOption: B

Tested. create table PRODUCTS (PROD_ID NUMBER, PROD_NAME VARCHAR2(50), PROD_CATEGORY VARCHAR2(50), PROD_MIN_PRICE NUMBER, PROD_UNIT_OF_MEASURE VARCHAR2(50)) INSERT into PRODUCTS VALUES(101,'Envoy','Hardware',6000,'Nos.') ; INSERT into PRODUCTS VALUES(102,'Y Box','Electronics',9000,'') ; INSERT into PRODUCTS VALUES(103,'DVD','Software/Other',2000,'Nos.') ; INSERT into PRODUCTS VALUES(104,'Documentation','Software/Other',4000,'') ; select * from PRODUCTS create global temporary table t_pro as select * from PRODUCTS insert into t_pro select * from PRODUCTS commit insert into t_pro select * from PRODUCTS select count(*) from t_pro

lucemqy

You are seeing 0 probably because you are in APEX and APEX commit each line

Mcromeo

I tested your script select count(*) from t_pro give 0 row

jm9999Option: C

I think the default for GTT is 'on commit delete rows'. So after the commit there would be 0 rows but then after the next insert there would be 14.

jm9999

The interesting thing on this one: It appears as though 'create global temporary table table_name1 as select * from table_name2' creates an empty table even when table_name2 contains rows.

lucemqy

When you create a table there's implicit COMMIT and GTT default is delete on commit so the table should be 0 rows