Oracle DBA 11g Chapter 7 Study Questions

What are distinguishing characteristics of heap tables? (Choose two answers.)

A heap table can store variable-length rows. Rows in a heap are in random order.

Which of the following data types are variable length? (Choose all correct answers.) -BLOB -CHAR -LONG -NUMBER -RAW -VARCHAR2

BLOB LONG NUMBER RAW VARCHAR2

When a table is created with a statement such as the following: create table newtab as select * from tab; will there be any constraints on the new table?

Check and not-null constraints will be copied, but not unique or primary keys.

Which of the following options can be applied to B*Tree indexes, but not to bitmap indexes? (Choose all correct answers.) {Compression, Descending order, Function-based key expressions, Reverse key indexing, Uniqueness, Use of compound keys}

Compression Reverse key indexing Uniqueness

Several object types share the same namespace and therefore cannot have the same name in the same schema. Which of the following object types is not in the same namespace as the others? {INDEX, PL/SQL stored procedure, Synonym, Table, View}

Index

If a table is created without specifying a schema, in which schema will it be?

It will be in the schema of the user creating it.

Which of the following statements is correct about indexes? -An index can be based on multiple columns of a table, but the columns must be of the same datatype. -An index can be based on multiple columns of a table, but the columns must be adjacent and specified in the order that they are defined in the table. -An index cannot have the same name as a table, unless the index and the table are in separate schemas. -None of the above statements is correct.

None of the above statements is correct.

Which types of constraint require an index? (List all that apply.)

PRIMARY KEY UNIQUE

Which of these is a defining characteristic of a complex view, rather than a simple view? (Choose one or more correct answers.) -Restricting the projection by selecting only some of the table's columns -Naming the view's columns with column aliases -Restricting the selection of rows with a WHERE clause -Performing an aggregation -Joining two tables

Performing an aggregation Joining two tables

What are distinguishing characteristics of a public synonym rather than a private synonym? (List two correct answers.)

Public synonyms can be accessed by name without a schema name qualifier. Public synonyms can have the same names as tables or views.

Which of the following is not supported by Oracle as an internal data type? (Choose the best answer.) -CHAR -FLOAT -INTEGER -STRING

STRING

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT. This insert fails with an error: SQL> insert into dept_view values('SUPPORT','OXFORD'); insert into dept_view values('SUPPORT','OXFORD') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO") What might be the problem?

The INSERT violates a constraint on the detail table.

Study this view creation statement: create view dept30 as select department_id,employee_id,last_name from employees where department_id=30 with check option; What might make the following statement fail? update dept30 set department_id=10 where employee_id=114;

The WITH CHECK OPTION will reject any statement that changes the DEPARTMENT_ID.

Study these statements: create table tab1 (c1 number(1), c2 date); alter session set nls_date_format='dd-mm-yy'; insert into tab1 values (1.1,'31-01-07'); Will the insert succeed?

The insert will succeed.

A transaction consists of two statements. The first succeeds, but the second (which updates several rows) fails partway through because of a constraint violation. What will happen?

The second statement will be rolled back completely, and the first will remain uncommitted.

Consider this statement: create table t1 as select * from regions where 1=2; What will be the result?

The table T1 will be created but no rows inserted because the condition returns FALSE.

Consider these three statements: create synonym s1 for employees; create public synonym s1 for departments; select * from s1; Which of the following statements is correct? (Choose the best answer.) A. The second statement will fail because an object S1 already exists. B. The third statement will show the contents of EMPLOYEES. C. The third statement will show the contents of DEPARTMENTS. D. The third statement will show the contents of the table S1, if such a table exists in the current schema.

The third statement will show the contents of EMPLOYEES.

Consider these three statements: create view v1 as select department_id, department_name,last_name from departments join employees using (department_id); select department_name,last_name from v1 where department_id=20; select d.department_name,e.last_name from departments d, employees e where d.department_id=e.department_id and d.department_id=20; The first query will be quicker than the second because (choose the best answer): A. The view has already done the work of joining the tables. B. The view uses ISO standard join syntax, which is faster than the Oracle join syntax used in the second query. C. The view is precompiled, so the first query requires less dynamic compilation than the second query. D. There is no reason for the first query to be quicker.

There is no reason for the first query to be quicker.

A view and a synonym are created as follows: create view dept_v as select * from dept; create synonym dept_s for dept_v; Subsequently the table DEPT is dropped. What will happen if you query the synonym DEPT_S ?

There will be an error because the view will be invalid.

A sequence is created as follows: create sequence seq1 maxvalue 50; If the current value is already 50, when you attempt to select SEQ1.NEXTVAL what will happen?

There will be an error.

Data in temporary tables has restricted visibility. If a user logs on as HR and inserts rows into a temporary table, to whom will the rows be visible?

To no session other than the one that did the insert

Where does the data in a temporary table get written to disk?

To the user's temporary tablespace

You create a sequence as follows: create sequence seq1 start with 1; After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated. How can you do this?

You must drop and re-create the sequence.

Which of these statements will fail because the table name is not legal? (Choose two answers.) -create table "SELECT" (col1 date); -create table "lowercase" (col1 date); -create table number1 (col1 date); -create table 1number(col1 date); -create table update(col1 date);

create table 1number(col1 date); create table update(col1 date);