Section 16 Quiz Database Programming With SQL

Section 16 Quiz

            (Answer all questions in this section)

                                                           

1.         Sequences can be used to: (Choose three)       Mark for Review

(1) Points

                                    (Choose all correct answers)   

            Generate a range of numbers and optionally cycle through them again (*)

            Set a fixed interval between successively generated numbers. (*)

            Ensure primary key values will be unique and consecutive

            Guarantee that no primary key values are unused

            Ensure primary key values will be unique even though gaps may exist (*)

2.         Evaluate this CREATE SEQUENCE statement:

CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;

Which statement is true?

 Mark for Review

(1) Points

            The statement will not execute successfully.

            The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.

            The starting value of the LINE_ITEM_ID_SEQ sequence will by -1.

            The sequence will generate sequential descending values. (*)

3.         Examine the code for creating this sequence:

CREATE SEQUENCE track_id_seq

INCREMENT BY 10

START WITH 1000 MAXVALUE 10000

What are the first three values that would be generated by the sequence?

 Mark for Review

(1) Points

            0, 1, 2

            1000, 1010, 1020 (*)

            1100, 1200, 1300

            100010011002

4.         You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?   Mark for Review

(1) Points

            Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.

            Specify a UNIQUE constraint on the CUSTOMER_ID column.

            Create a synonym.

            Create a sequence. (*)

5.         You need to retrieve the next available value for the SALES_IDX sequence.

Which would you include in your SQL statement?      Mark for Review

(1) Points

            sales_idx.CURRVAL

            sales_idx.NEXTVAL (*)

            sales_idx.NEXT

            sales_idx

6.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?   Mark for Review

(1) Points

            ALTER SEQUENCE emp_id_seq; (*)

            ALTER TABLE employees ;

            CREATE SEQUENCE emp_id_seq;

            ALTER SEQUENCE emp_id_seq.employee_id;

7.         Why do gaps in sequences occur?       Mark for Review

(1) Points

            A rollback is executed

            The system crashes

            The sequence is used in another table

            All of the above (*)

8.         You create a sequence with the following statement:

CREATE SEQUENCE my_emp_seq;

Which of the following statements about this sequence are true? (Choose two)

 Mark for Review

(1) Points

                                    (Choose all correct answers)   

            The sequence will not cache a range of numbers in memory.

            MAXVALUE is 10^27 for an ascending sequence. (*)

            When the sequence exceeds its maximum value it will continue to generate numbers starting with MINVALUE.

            MINVALUE is equal to 1. (*)

9.         The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this?        Mark for Review

(1) Points

            ALTER INDEX ln_idx ON employees(first_name);

            ALTER INDEX ln_idx TO employees(first_name);

            ALTER INDEX ln_idx TO fn_idx ON employees(first_name);

            None of the above; you cannot ALTER an index. (*)

‘;10.     User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:

CREATE PUBLIC SYNONYM emp FOR mary.emp;

User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use? (Choose two)

 Mark for Review

(1) Points

                                                           

                                    (Choose all correct answers)   

            SELECT * FROM emp; (*)

            SELECT * FROM mary.emp; (*)

            CREATE SYNONYM marys_emp FOR mary(emp);

            SELECT * FROM emp.mary;

11.       Barry creates a table named INVENTORY. Pam must be able to query the same table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?    Mark for Review

(1) Points

            A view

            A synonym (*)

            An index

            A schema

12.       You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query?            Mark for Review

(1) Points

            USER_OBJECTS

            USER_INDEXES

            USER_IND_COLUMNS (*)

            USER_TABLES

13.       The following indexes exist on the EMPLOYEES table:

A unique index on the EMPLOYEE_ID primary key column

A non-unique index on the JOB_ID column

A composite index on the FIRST_NAME and LAST_NAME columns.

If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

 Mark for Review

(1) Points

            EMP_ID only

            JOB_ID only

            DEPT_ID only

            EMP_ID and JOB_ID

            All Indexes (*)

14.       Unique indexes are automatically created on columns that have which two types of constraints?       Mark for Review

(1) Points

            NOT NULL and UNIQUE

            UNIQUE and PRIMARY KEY (*)

            UNIQUE and FOREIGN KEY

            PRIMARY KEY and FOREIGN KEY

15.       The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER NOT NULL, Primary Key

LAST_NAME VARCHAR2 (20)

FIRST_NAME VARCHAR2 (20)

DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table

HIRE_DATE DATE DEFAULT SYSDATE

SALARY NUMBER (8,2) NOT NULL

On which column is an index automatically created for the EMPLOYEES table?

 Mark for Review

(1) Points

            DEPARTMENT_ID

            HIRE_DATE

            EMPLOYEE_ID (*)

            LAST_NAME

            SALARY

1.         Evaluate this CREATE SEQUENCE statement:

CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;

Which statement is true?

 Mark for Review

(1) Points

            The sequence will start with 1. (*)

            The sequence preallocates values and retains them in memory.

            The sequence has no maximum value.

            The sequence will continue to generate values after reaching its maximum value.

2.         In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?          Mark for Review

(1) Points

            MAXVALUE

            CACHE

            NOCACHE (*)

3.         You need to retrieve the next available value for the SALES_IDX sequence.

Which would you include in your SQL statement?      Mark for Review

(1) Points

            sales_idx.CURRVAL

            sales_idx.NEXT

            sales_idx

            sales_idx.NEXTVAL (*)

4.         Evaluate this statement:

CREATE SEQUENCE sales_item_id_seq

START WITH 101 MAXVALUE 9000090 CYCLE;

Which statement about this CREATE SEQUENCE statement is true?

 Mark for Review

(1) Points

            The sequence will generate sequence numbers starting with 101, but will not reuse numbers.

            The statement fails because no INCREMENT BY value is specified.

            The sequence will generate decrementing sequence numbers starting at 101.

            The sequence will reuse numbers and will start with 101. (*)

5.         You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:

ALTER TABLE manufacturers

MODIFY (location_id NUMBER(6));

Which statement about the LOCATION_ID_SEQ sequence is true?

 Mark for Review

(1) Points

            The sequence is unchanged. (*)

            The current value of the sequence is reset to zero.

            The sequence is deleted and must be recreated.

            The current value of the sequence is reset to the sequence's START WITH value.

6.         Which dictionary view would you query to display the number most recently generated by a sequence?            Mark for Review

(1) Points

            USER_CURRVALUES

            USER_TABLES

            USER_SEQUENCES (*)

            USER_OBJECTS

7.         Why do gaps in sequences occur?       Mark for Review

(1) Points

            A rollback is executed

            The system crashes

            The sequence is used in another table

            All of the above (*)

8.         Nextval and Currval are known as column aliases. True or False?      Mark for Review

(1) Points

            True

            False (*)

9.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review

(1) Points

            A PRIMARY KEY constraint

            A FOREIGN KEY constraint

            An index (*)

            A CHECK constraint

10.       Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?      Mark for Review

(1) Points

            DROP INDEX last_name_idx(last_name);

            ALTER TABLE employees

DROP INDEX last_name_idx;

            DROP INDEX last_name_idx(employees.last_name);

            DROP INDEX last_name_idx;(*)

11.       The following indexes exist on the EMPLOYEES table:

A unique index on the EMPLOYEE_ID primary key column

A non-unique index on the JOB_ID column

A composite index on the FIRST_NAME and LAST_NAME columns.

If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

 Mark for Review

(1) Points

            EMP_ID only

            JOB_ID only

            DEPT_ID only

            EMP_ID and JOB_ID

            All Indexes (*)

12.       All tables must have indexes on them otherwise they cannot be queried. True or False?         Mark for Review

(1) Points

            True

            False (*)

13.       Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?   Mark for Review

(1) Points

            CREATE UNRESTRICTED SYNONYM emp FOR EMPLOYEES

            CREATE PUBLIC SYNONYM emp FOR EMPLOYEES (*)

            CREATE SHARED SYNONYM emp FOR EMPLOYEES

            CREATE SYNONYM emp FOR EMPLOYEES

14.       What is the correct syntax for creating an index?        Mark for Review

(1) Points

            CREATE index_name INDEX ON table_name.column_name;

            CREATE INDEX ON table_name(column_name);

            CREATE OR REPLACE INDEX index_name ON table_name(column_name);

            CREATE INDEX index_name ON table_name(column_name); (*)

15.       The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER NOT NULL, Primary Key

LAST_NAME VARCHAR2 (20)

FIRST_NAME VARCHAR2 (20)

DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table

HIRE_DATE DATE DEFAULT SYSDATE

SALARY NUMBER (8,2) NOT NULL

On which column is an index automatically created for the EMPLOYEES table?

 Mark for Review

(1) Points

            LAST_NAME

            EMPLOYEE_ID (*)

            DEPARTMENT_ID

            HIRE_DATE

            SALARY

1.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?      Mark for Review

(1) Points

            CREATE d_sum SYNONYM

FOR dept_sum_vu;

            CREATE SYNONYM d_sum

FOR dept_sum_vu;(*)

            CREATE SYNONYM d_sum

ON dept_sum_vu;

            UPDATE dept_sum_vu

ON SYNONYM d_sum;

2.         What kind of INDEX is created by Oracle when you create a primary key?   Mark for Review

(1) Points

            UNIQUE INDEX (*)

            NONUNIQUE INDEX

            INDEX

            Oracle cannot create indexes automatically.

3.         Indexes can be used to speed up queries. True or False?         Mark for Review

(1) Points

            True (*)

            False

4.         When creating an index on one or more columns of a table, which of the following statements are true?

(Choose two)   Mark for Review

(1) Points

                                    (Choose all correct answers)   

            You should create an index if one or more columns are frequently used together in a join condition. (*)

            You should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows. (*)

            You should create an index if the table is very small.

            You should always create an index on tables that are frequently updated.

5.         The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table?   Mark for Review

(1) Points

            CREATE PUBLIC SYNONYM cust ON mary.customers;

            CREATE PUBLIC SYNONYM cust FOR mary.customers;(*)

            CREATE SYNONYM cust ON mary.customers FOR PUBLIC;

            CREATE SYNONYM cust ON mary.customers;

GRANT SELECT ON cust TO PUBLIC;

6.         Which of the following best describes the function of an index?        Mark for Review

(1) Points

            An index can run statement blocks when DML actions occur against a table.

            An index can reduce the time required to grant multiple privileges to users.

            An index can prevent users from viewing certain data in a table.

            An index can increase the performance of SQL queries that search large tables. (*)

7.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review

(1) Points

            A PRIMARY KEY constraint

            An index (*)

            A CHECK constraint

            A FOREIGN KEY constraint

8.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?   Mark for Review

(1) Points

            ALTER TABLE employees ;

            CREATE SEQUENCE emp_id_seq;

            ALTER SEQUENCE emp_id_seq; (*)

            ALTER SEQUENCE emp_id_seq.employee_id;

9.         The ALTER SEQUENCE statement can be used to:    Mark for Review

(1) Points

            Change the maximum value to a lower number than was last used

            Change the amount a sequence increments each time a number is generated (*)

            Change the START WITH value of a sequence

            Change the name of the sequence

10.       A sequence is a database object. True or False?          Mark for Review

(1) Points

            True (*)

            False

11.       You issue this statement:

ALTER SEQUENCE po_sequence INCREMENT BY 2;

Which statement is true?

 Mark for Review

(1) Points

            Sequence numbers will be cached.

            Future sequence numbers generated will increase by 2 each time a number is generated. (*)

            If the PO_SEQUENCE sequence does not exist, it will be created.

            The statement fails if the current value of the sequence is greater than the START WITH value.

12.       Sequences can be used to: (Choose three)       Mark for Review

(1) Points

                                    (Choose all correct answers)   

            Generate a range of numbers and optionally cycle through them again (*)

            Guarantee that no primary key values are unused

            Set a fixed interval between successively generated numbers. (*)

            Ensure primary key values will be unique even though gaps may exist (*)

            Ensure primary key values will be unique and consecutive

13.       Evaluate this CREATE SEQUENCE statement:

CREATE SEQUENCE line_item_id_seq CYCLE;

Which statement is true?

 Mark for Review

(1) Points

            The sequence cannot be used with more than one table.

            The sequence preallocates values and retains them in memory.

            The sequence cannot generate additional values after reaching its maximum value.

            The sequence will continue to generate values after the maximum sequence value has been generated. (*)

14.       Evaluate this CREATE SEQUENCE statement:

CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;

Which statement is true?

 Mark for Review

(1) Points

            The sequence will start with 1. (*)

            The sequence will continue to generate values after reaching its maximum value.

            The sequence has no maximum value.

            The sequence preallocates values and retains them in memory.

15.       You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:

ALTER TABLE manufacturers

MODIFY (location_id NUMBER(6));

Which statement about the LOCATION_ID_SEQ sequence is true?

 Mark for Review

(1) Points

            The current value of the sequence is reset to the sequence's START WITH value.

            The sequence is deleted and must be recreated.

            The current value of the sequence is reset to zero.

            The sequence is unchanged. (*)

1.         Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?     Mark for Review

(1) Points

            True

            False (*)

2.         Which of the following best describes the function of the NEXTVAL virtual column?          Mark for Review

(1) Points

            The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.

            The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.

            The NEXTVAL virtual column increments a sequence by a predetermined value. (*)

            The NEXTVAL virtual column displays only the physical locations of the rows in a table.

3.         Evaluate this CREATE SEQUENCE statement:

CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;

Which statement is true?

 Mark for Review

(1) Points

            The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.

            The starting value of the LINE_ITEM_ID_SEQ sequence will by -1.

            The statement will not execute successfully.

            The sequence will generate sequential descending values. (*)

4.         Why do gaps in sequences occur?       Mark for Review

(1) Points

            A rollback is executed

            The system crashes

            The sequence is used in another table

            All of the above (*)

5.         When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?           Mark for Review

(1) Points

            LOAD

            NOCYCLE

            NOCACHE

            CACHE (*)

            MEMORY

6.         Which pseudocolumn returns the latest value supplied by a sequence?           Mark for Review

(1) Points

            NEXT

            CURRENT

            NEXTVAL

            CURRVAL (*)

7.         Which statement would you use to remove the EMP_ID_SEQ sequence?       Mark for Review

(1) Points

            ALTER SEQUENCE emp_id_seq;

            DROP SEQUENCE emp_id_seq; (*)

            REMOVE SEQUENCE emp_id_seq;

            DELETE SEQUENCE emp_id_seq;

8.         Evaluate this statement:

CREATE SEQUENCE line_item_id_seq

MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;

What will be the first five numbers generated by this sequence?

 Mark for Review

(1) Points

            130120110100130

            The fifth number cannot be generated.

            100110120130100

            The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)

9.         The CLIENTS table contains these columns:

CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY

LAST_NAME VARCHAR2(15)

FIRST_NAME VARCHAR2(10)

CITY VARCHAR2(15)

STATE VARCHAR2(2)

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:

CREATE INDEX clients

ON address_index (city, state);

Which result does this statement accomplish?

 Mark for Review

(1) Points

            An index named CLIENTS is created on the CITY and STATE columns.

            An index named CLIENTS_INDEX is created on the CLIENTS table.

            An error message is produced, and no index is created. (*)

            An index named ADDRESS_INDEX is created on the CITY and STATE columns.

10.       In SQL what is a synonym?     Mark for Review

(1) Points

            A table with the same number of columns as another table

            A table with the same name as another view

            A different name for a table, view, or other database object (*)

            A table that must be qualified with a username

11.       Indexes can be used to speed up queries. True or False?         Mark for Review

(1) Points

            True (*)

            False

12.       Barry creates a table named INVENTORY. Pam must be able to query the same table. Barry wants to enable Pam to query the table without being required to specify the table's schema. Which of the following should Barry create?    Mark for Review

(1) Points

            An index

            A view

            A synonym (*)

            A schema

13.       The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER NOT NULL, Primary Key

LAST_NAME VARCHAR2 (20)

FIRST_NAME VARCHAR2 (20)

DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table

HIRE_DATE DATE DEFAULT SYSDATE

SALARY NUMBER (8,2) NOT NULL

On which column is an index automatically created for the EMPLOYEES table?

 Mark for Review

(1) Points

            EMPLOYEE_ID (*)

            LAST_NAME

            HIRE_DATE

            SALARY

            DEPARTMENT_ID

14.       The following indexes exist on the EMPLOYEES table:

A unique index on the EMPLOYEE_ID primary key column

A non-unique index on the JOB_ID column

A composite index on the FIRST_NAME and LAST_NAME columns.

If the EMPLOYEES table is dropped, which indexes are automatically dropped at the same time?

 Mark for Review

(1) Points

            EMP_ID only

            JOB_ID only

            DEPT_ID only

            EMP_ID and JOB_ID

            All Indexes (*)

15.       You want to speed up the following query by creating an index:

SELECT * FROM employees WHERE (salary * 12) > 100000;

Which of the following will achieve this?

 Mark for Review

(1) Points

            Create an index on (salary).

            Create a function_based index on ((salary * 12) > 100000).

            Create a composite index on (salary,12).

            Create a function-based index on (salary * 12). (*)

1.         Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?  Mark for Review

(1) Points

            SELECT index_name, table_name, uniqueness

FROM user_indexes

WHERE index = EMPLOYEES;

            CREATE index_name, table_name, uniqueness

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

            SELECT index_name, table_name, uniqueness

FROM 'EMPLOYEES';

            SELECT index_name, table_name, uniqueness

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

(*)

2.         Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?      Mark for Review

(1) Points

            DROP INDEX last_name_idx(last_name);

            ALTER TABLE employees

DROP INDEX last_name_idx;

            DROP INDEX last_name_idx(employees.last_name);

            DROP INDEX last_name_idx;

(*)

3.         Indexes can be used to speed up queries. True or False?         Mark for Review

(1) Points

            True (*)

            False

4.         As user Julie, you issue this statement:

CREATE SYNONYM emp FOR sam.employees;

Which task was accomplished by this statement?

 Mark for Review

(1) Points

            You created a public synonym on the EMPLOYEES table owned by user Sam.

            You created a public synonym on the EMP table owned by user Sam.

            You created a private synonym on the EMPLOYEES table that you own.

            You created a private synonym on the EMPLOYEES table owned by user Sam. (*)

5.         For which column would you create an index?           Mark for Review

(1) Points

            A column that is updated frequently

            A column that is infrequently used as a query search condition

            A column which has only 4 distinct values.

            A column with a large number of null values (*)

6.         You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task? Mark for Review

(1) Points

            CREATE INDEX fl_idx ON employees(first_name);

CREATE INDEX fl_idx ON employees(last_name);

            CREATE INDEX fl_idx

ON employees(first_name || last_name);

            CREATE INDEX fl_idx

ON employees(first_name), employees(last_name);

            CREATE INDEX fl_idx

ON employees(first_name,last_name);

(*)

7.         What kind of INDEX is created by Oracle when you create a primary key?   Mark for Review

(1) Points

            UNIQUE INDEX (*)

            NONUNIQUE INDEX

            INDEX

            Oracle cannot create indexes automatically.

8.         When creating a sequence, which keyword or option specifies the minimum sequence value?           Mark for Review

(1) Points

            CYCLE

            MINVALUE (*)

            MAXVALUE

            NOMAXVALUE

9.         You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?   Mark for Review

(1) Points

            Specify a UNIQUE constraint on the CUSTOMER_ID column.

            Create a sequence. (*)

            Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.

            Create a synonym.

10.       Evaluate this statement:

CREATE SEQUENCE sales_item_id_seq

START WITH 101 MAXVALUE 9000090 CYCLE;

Which statement about this CREATE SEQUENCE statement is true?

 Mark for Review

(1) Points

            The sequence will generate sequence numbers starting with 101, but will not reuse numbers.

            The sequence will reuse numbers and will start with 101. (*)

            The statement fails because no INCREMENT BY value is specified.

            The sequence will generate decrementing sequence numbers starting at 101.

11.       Creating a sequence with NOCACHE ensures that all numbers in the sequence's range will be used successfully. True or False?     Mark for Review

(1) Points

            True

            False (*)

12.       When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?           Mark for Review

(1) Points

            NOCYCLE

            NOCACHE

            CACHE (*)

            LOAD

            MEMORY

13.       A sequence is a database object. True or False?          Mark for Review

(1) Points

            True (*)

            False

14.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?   Mark for Review

(1) Points

                                                           

                                   

            True (*)

            False

15.       CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?      Mark for Review

(1) Points

            True (*)

            False

1.         You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query?            Mark for Review

(1) Points

            USER_INDEXES

            USER_OBJECTS

            USER_TABLES

            USER_IND_COLUMNS (*)

2.         The CLIENTS table contains these columns:

CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY

LAST_NAME VARCHAR2(15)

FIRST_NAME VARCHAR2(10)

CITY VARCHAR2(15)

STATE VARCHAR2(2)

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:

CREATE INDEX clients

ON address_index (city, state);

Which result does this statement accomplish?

 Mark for Review

(1) Points

            An index named CLIENTS is created on the CITY and STATE columns.

            An index named CLIENTS_INDEX is created on the CLIENTS table.

            An index named ADDRESS_INDEX is created on the CITY and STATE columns.

            An error message is produced, and no index is created. (*)

3.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?      Mark for Review

(1) Points

            UPDATE dept_sum_vu

ON SYNONYM d_sum;

            CREATE SYNONYM d_sum

ON dept_sum_vu;

            CREATE d_sum SYNONYM

FOR dept_sum_vu;

            CREATE SYNONYM d_sum

FOR dept_sum_vu;

(*)

4.         All tables must have indexes on them otherwise they cannot be queried. True or False?         Mark for Review

(1) Points

            True

            False (*)

5.         As user Julie, you issue this statement:

CREATE SYNONYM emp FOR sam.employees;

Which task was accomplished by this statement?

 Mark for Review

(1) Points

            You created a public synonym on the EMP table owned by user Sam.

            You created a private synonym on the EMPLOYEES table that you own.

            You created a private synonym on the EMPLOYEES table owned by user Sam. (*)

            You created a public synonym on the EMPLOYEES table owned by user Sam.

6.         Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?  Mark for Review

(1) Points

            SELECT index_name, table_name, uniqueness

FROM 'EMPLOYEES';

            SELECT index_name, table_name, uniqueness

FROM user_indexes

WHERE index = EMPLOYEES;

            SELECT index_name, table_name, uniqueness

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

(*)

            CREATE index_name, table_name, uniqueness

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

7.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?           Mark for Review

(1) Points

            An index (*)

            A CHECK constraint

            A PRIMARY KEY constraint

            A FOREIGN KEY constraint

8.         Examine the code for creating this sequence:

CREATE SEQUENCE track_id_seq

INCREMENT BY 10

START WITH 1000 MAXVALUE 10000

What are the first three values that would be generated by the sequence?

 Mark for Review

(1) Points

            100010011002

            1000, 1010, 1020 (*)

            0, 1, 2

            1100, 1200, 1300

9.         In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?          Mark for Review

(1) Points

            NOCACHE (*)

            CACHE

            MAXVALUE

10.       Which keyword is used to modify a sequence?           Mark for Review

(1) Points

            Alter (*)

            Update

            Change

            Create

11.       Evaluate this statement:

SELECT po_itemid_seq.CURRVAL

FROM dual;

What does this statement accomplish?

 Mark for Review

(1) Points

            It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)

            It displays the next available value of the PO_ITEM_ID_SEQ sequence.

            It resets the current value of the PO_ITEM_ID_SEQ sequence.

            It sets the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID column.

12.       Which is the correct syntax for specifying a maximum value in a sequence?  Mark for Review

(1) Points

            Maxval

            Max_value

            Maxvalue (*)

            Maximumvalue

13.       Which statement would you use to remove the EMP_ID_SEQ sequence?       Mark for Review

(1) Points

            DELETE SEQUENCE emp_id_seq;

            DROP SEQUENCE emp_id_seq; (*)

            REMOVE SEQUENCE emp_id_seq;

            ALTER SEQUENCE emp_id_seq;

14.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?   Mark for Review

(1) Points

            True (*)

            False

15.       A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False?     Mark for Review

(1) Points

            True (*)

            False