Chapter 10

All database data is stored in:

A. All data is stored in tables. Even data about the tables you create is stored automatically by Oracle SQL in a set of system-defined and system-maintained tables.

Which of the following SQL statements can always be executed on any VIEW object? (Choose all that apply.)

A. The SELECT statement can be used against any view.

Now review the following SQL code: 01 CREATE OR REPLACE VIEW SHIP_CAP_PROJ AS 02 SELECT SHIP_ID, 03 TO_CHAR(CAPACITY,'999,999'), 04 PROJECT_COST 05 FROM SHIPS JOIN PROJECTS 06 USING (SHIP_ID) 07 WHERE (PROJECT_COST * 2) < 100000; What will result from an attempt to execute this SQL code?

A. The error on line 3 is the failure to give the expression a column alias. The VIEW does not assign a name to the second column, so the attempt to create the view fails.

Review the illustration from question 5 and the following SQL code: CREATE OR REPLACE VIEW PROJECTS_ROLLUP AS SELECT SHIP_NAME, CAPACITY, COUNT(PROJECT_ID) NUM_PROJECTS, ROUND(SUM(DAYS)) TOTAL_DAYS FROM SHIPS A JOIN PROJECTS B ON A.SHIP_ID = B.SHIP_ID GROUP BY SHIP_NAME, CAPACITY; What can be said of this code?

A. The syntax for creating the view is correct, and any view can—at a minimum—work with a SELECT statement. But an INSERT will not work with this view since it consists of aggregate rows, as defined by the GROUP BY clause in the view's SELECT statement.

Review the following SQL code: 01 DROP TABLE PO_BOXES; 02 CREATE TABLE PO_BOXES (PO_BOX_ID NUMBER(3), PO_BOX_NUMBER VARCHAR2(10)) 03 ENABLE ROW MOVEMENT; 04 INSERT INTO PO_BOXES VALUES (1, 'A100'); 05 INSERT INTO PO_BOXES VALUES (2, 'B100'); 06 COMMIT; 07 EXECUTE DBMS_LOCK.SLEEP(30); 08 DELETE FROM PO_BOXES; 09 COMMIT; 10 EXECUTE DBMS_LOCK.SLEEP(30); Which of the following statements could be added as line 11 and recover the deleted rows from the PO_BOXES table?

A. This is the correct syntax—the TO TIMESTAMP clause with the expression that starts with the current date and time and subtracts an interval of 45 seconds.

Choose the best answer from the choices below. An index:

B. An index can potentially speed up the WHERE clause of any DML statement, including the UPDATE statement. Comparisons of equality are ideal.

Review this code: DROP SEQUENCE PROJ_ID_SEQ#; CREATE SEQUENCE PROJ_ID_SEQ# START WITH 1 INCREMENT BY 2; SELECT PROJ_ID_SEQ#.CURRVAL FROM DUAL; What will result from these SQL statements?

B. Since the sequence was just created, NEXTVAL must be referenced before CURRVAL. This is also true if you were to log off and end the session and then log back in to restart the session—the first reference for existing sequences must be NEXTVAL.

The database object that stores lookup information to speed up querying in tables is:

B. The INDEX stores data for speeding up querying.

Review this code: DROP TABLE SHIPS CASCADE CONSTRAINTS; DROP SEQUENCE PROJ_ID_SEQ#; CREATE TABLE SHIPS (SHIP_ID NUMBER PRIMARY KEY, LENGTH NUMBER); CREATE SEQUENCE PROJ_ID_SEQ# START WITH 1 INCREMENT BY 4; INSERT INTO SHIPS (SHIP_ID, LENGTH) VALUES (PROJ_ID_SEQ#.NEXTVAL, 'NOT A NUMBER'); INSERT INTO SHIPS (SHIP_ID, LENGTH) VALUES (PROJ_ID_SEQ#.NEXTVAL, 750); COMMIT; Note that the first INSERT statement is attempting to enter a string literal of 'NOT A NUMBER' into a column declared with a numeric data type. Given that, what will be the result of these SQL statements?

B. There will be one row in the table. The reason is that the first INSERT will fail because of the attempt to enter a character string into a numeric column. In the first failed INSERT statement, the PROJ_ID_SEQ# sequence generator will be invoked, and the NEXTVAL reference will use up the first number in the sequence, which will be 1. The second INSERT will succeed and grab the second number in the sequence, which will be 5.

Review the following series of SQL statements: CREATE TABLE SUPPLIES_01 ( SUPPLY_ID NUMBER(7), SUPPLIER VARCHAR2(30), ACCT_NO VARCHAR2(50)); CREATE INDEX IX_SU_01 ON SUPPLIES_01(ACCT_NO); DROP TABLE SUPPLIES_01; CREATE TABLE SUPPLIES_02 ( SUPPLY_ID NUMBER(7), SUPPLIER VARCHAR2(30), ACCT_NO VARCHAR2(50)); CREATE INDEX IX_SU_02 ON SUPPLIES_02(ACCT_NO,SUPPLIER); Assuming there are no objects already in existence named SUPPLIES_01 or SUPPLIES_02 prior to the execution of the preceding statements, what database objects will result from these statements?

B. While all the statements will execute successfully, the first DROP statement will drop the table SUPPLIES_01, which will cause the index IX_SU_01 to be dropped as well. In other words, the DROP TABLE SUPPLIES_01 statement has the effect of dropping the table SUPPLIES_01 as well as the index IX_SU_01. The tables SUPPLIES_02 and IX_SU_02 will remain at the end.

An invisible index is an index on one or more columns in a table:

C. The index is updated for any DELETE statements performed on the table. Invisible indexes are still maintained, even though they are invisible.

Which of the following keywords cannot be used with the CREATE SEQUENCE statement?

D. JOIN is used in a SELECT statement that connects two or more tables. But it is not used in a CREATE SEQUENCE statement, even though its ultimate purpose may be to support the integrity of joins.

A SEQUENCE is

D. None of the above. A SEQUENCE is a counter that is useful for populating primary keys but can be used for any purpose the developer wishes, or not.

Review the following SQL code: 01 CREATE TABLE PO_BOXES (PO_BOX_ID NUMBER(3), PO_BOX_NUMBER VARCHAR2(10)) 02 ENABLE ROW MOVEMENT; 03 INSERT INTO PO_BOXES VALUES (1, 'A100'); 04 INSERT INTO PO_BOXES VALUES (2, 'B100'); 05 COMMIT; 06 DROP TABLE PO_BOXES; 07 COMMIT; 08 PURGE TABLE PO_BOXES; 09 COMMIT; What statement will recover the PO_BOXES table after these statements are executed?

D. None of the above. The PURGE statement on line 8 prevents any recovery from being possible. PURGE cleans out the recycle bin of the objects specified in the PURGE statement, from which FLASHBACK TABLE recovers objects.

Review the illustration from question 5 and the following SQL code: 01 CREATE OR REPLACE VIEW MAJOR_PROJECTS AS 02 SELECT PROJECT_ID, SHIP_ID, PROJECT_NAME, PROJECT_COST 03 FROM PROJECTS 04 WHERE PROJECT_COST > 10000; 05 06 INSERT INTO MAJOR_PROJECTS 07 (PROJECT_ID, SHIP_ID, PROJECT_NAME, PROJECT_COST) 08 VALUES 09 ((SELECT MAX(PROJECT_ID)+1 FROM PROJECTS), 10 (SELECT MAX(SHIP_ID) FROM SHIPS), 11 'Small Project', 12 500); What will result from an attempt to execute these two SQL statements?

D. The CREATE and INSERT statements will successfully execute.