Database Programming with SQL-Section 16 Quiz

Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

1. Which pseudocolumn returns the latest value supplied by a sequence? Mark for Review
(1) Points


CURRVAL (*)


NEXTVAL


CURRENT


NEXT



Correct Correct


2. 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 (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


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

(Choose all correct answers)


Guarantee that no primary key values are unused


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


Ensure primary key values will be unique and consecutive


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


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



Incorrect Incorrect. Refer to Section 16 Lesson 1.


4. The ALTER SEQUENCE statement can be used to: Mark for Review
(1) Points


Change the name of the sequence


Change the START WITH value of a sequence


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


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



Incorrect Incorrect. Refer to Section 16 Lesson 1.


5. Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;

What does this statement accomplish?

 Mark for Review
(1) Points


It displays the next available 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.


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


It resets the current value of the PO_ITEM_ID_SEQ sequence.



Incorrect Incorrect. Refer to Section 16 Lesson 1.


Page 1 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

6. CURRVAL is a pseudocolumn used to extract successive sequence numbers from a specified sequence. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


7. Which dictionary view would you query to display the number most recently generated by a sequence? Mark for Review
(1) Points


USER_OBJECTS


USER_SEQUENCES (*)


USER_CURRVALUES


USER_TABLES



Correct Correct


8. Which keyword is used to modify a sequence? Mark for Review
(1) Points


Update


Create


Change


Alter (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


9. Indexes can be used to speed up queries. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


10. 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 SYNONYM emp FOR EMPLOYEES


CREATE SHARED SYNONYM emp FOR EMPLOYEES


CREATE PUBLIC SYNONYM emp FOR EMPLOYEES (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 2 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

11. Which of the following statements best describes indexes and their use? Mark for Review
(1) Points


They are just copies of data in no particular order.


They contain the column value and pointers to the data in the table, but the data is sorted. (*)


They contain all the rows and columns from the table


None of the above



Incorrect Incorrect. Refer to Section 16 Lesson 2.


12. In SQL what is a synonym? Mark for Review
(1) Points


A table with the same name as another view


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


A table with the same number of columns as another table


A table that must be qualified with a username



Incorrect Incorrect. Refer to Section 16 Lesson 2.


13. Which statement about an index is true? Mark for Review
(1) Points


Creating an index will always improve query performance.


An index can only be created on a single table column.


An index created on multiple columns is called a composite or concatenated index. (*)


Creating an index reorders the data in the underlying table.



Incorrect Incorrect. Refer to Section 16 Lesson 2.


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 (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


15. 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 the table is very small.


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 always create an index on tables that are frequently updated.



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 3 of 3 Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

1. 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 current value of the sequence is reset to the sequence's START WITH value.


The sequence is deleted and must be recreated.



Incorrect Incorrect. Refer to Section 16 Lesson 1.


2. 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



Correct Correct


3. Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;

What does this statement accomplish?

 Mark for Review
(1) Points


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


It resets the current value of the PO_ITEM_ID_SEQ sequence.


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.



Incorrect Incorrect. Refer to Section 16 Lesson 1.


4. Which keyword is used to remove a sequence? Mark for Review
(1) Points


Remove


Delete


Revoke


Drop (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


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

(Choose all correct answers)


Guarantee that no primary key values are unused


Ensure primary key values will be unique and consecutive


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


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


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



Incorrect Incorrect. Refer to Section 16 Lesson 1.


Page 1 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

6. A sequence is a database object. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


7. The ALTER SEQUENCE statement can be used to: Mark for Review
(1) Points


Change the START WITH value of a sequence


Change the name of the sequence


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


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



Correct Correct


8. 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 (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


9. Indexes can be used to speed up queries. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


10. Which one of the following statements about indexes is true? Mark for Review
(1) Points


An index is never created for a unique constraint.


An index must be created by a database administrator when a PRIMARY KEY constraint is created.


An index is created automatically when a PRIMARY KEY constraint is created. (*)


An index cannot be created before a PRIMARY KEY constraint is created.



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 2 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

11. The EMPLOYEES table contains these columns:
EMP_ID NOT NULL, Primary Key
SSNUM NOT NULL, Unique
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPT_ID NUMBER Foreign Key to DEPT_ID column of the DEPARTMENTS table
SALARY NUMBER(8,2)

You execute this statement:

CREATE INDEX emp_name_idx
ON employees(last_name, first_name);

Which statement is true?

 Mark for Review
(1) Points


The statement fails because of a syntax error.


The statement creates a composite unique index.


The statement creates a function-based index.


The statement creates a composite non-unique index. (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


12. 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;
(*)



DROP INDEX last_name_idx(last_name);


DROP INDEX last_name_idx(employees.last_name);


ALTER TABLE employees
DROP INDEX last_name_idx;



Correct Correct


13. What would you create to make the following statement execute faster?
SELECT *
FROM employees
WHERE LOWER(last_name) = 'chang';

 Mark for Review
(1) Points


A synonym


An index, either a normal or a function_based index (*)


A composite index


Nothing; the performance of this statement cannot be improved.



Incorrect Incorrect. Refer to Section 16 Lesson 2.


14. Which of the following statements best describes indexes and their use? Mark for Review
(1) Points


They are just copies of data in no particular order.


They contain the column value and pointers to the data in the table, but the data is sorted. (*)


They contain all the rows and columns from the table


None of the above



Correct Correct


15. For which column would you create an index? Mark for Review
(1) Points


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 (*)


A column that is updated frequently



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 3 of 3 Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

1. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
(1) Points


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


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


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


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



Incorrect Incorrect. Refer to Section 16 Lesson 1.


2. 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.



Incorrect Incorrect. Refer to Section 16 Lesson 1.


3. The ALTER SEQUENCE statement can be used to: Mark for Review
(1) Points


Change the START WITH value of a sequence


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


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


Change the name of the sequence



Correct Correct


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 (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


5. What is the most common use for a Sequence? Mark for Review
(1) Points


To logically represent subsets of data from one or more tables


To generate primary key values (*)


To improve the performance of some queries


To give an alternative name for an object



Incorrect Incorrect. Refer to Section 16 Lesson 1.


Page 1 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

6. 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 zero.


The sequence is deleted and must be recreated.


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


The sequence is unchanged. (*)



Correct Correct


7. Which pseudocolumn returns the latest value supplied by a sequence? Mark for Review
(1) Points


CURRVAL (*)


NEXTVAL


CURRENT


NEXT



Correct Correct


8. 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


NOCACHE


CACHE (*)


MEMORY


LOAD


NOCYCLE



Incorrect Incorrect. Refer to Section 16 Lesson 1.


9. 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 a composite index on (salary,12).


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


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


Create an index on (salary).



Incorrect Incorrect. Refer to Section 16 Lesson 2.


10. 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 error message is produced, and no index is created. (*)


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


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


An index named CLIENTS_INDEX is created on the CLIENTS table.



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 2 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

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 (*)



Correct Correct


12. What would you create to make the following statement execute faster?
SELECT *
FROM employees
WHERE LOWER(last_name) = 'chang';

 Mark for Review
(1) Points


A synonym


An index, either a normal or a function_based index (*)


A composite index


Nothing; the performance of this statement cannot be improved.



Correct Correct


13. 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



Incorrect Incorrect. Refer to Section 16 Lesson 2.


14. Which statement about an index is true? Mark for Review
(1) Points


An index can only be created on a single table column.


An index created on multiple columns is called a composite or concatenated index. (*)


Creating an index reorders the data in the underlying table.


Creating an index will always improve query performance.



Correct Correct


15. In SQL what is a synonym? Mark for Review
(1) Points


A table that must be qualified with a username


A table with the same number of columns as another table


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


A table with the same name as another view



Correct Correct


Previous Page 3 of 3 Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

1. When creating a sequence, which keyword or option specifies the minimum sequence value? Mark for Review
(1) Points


NOMAXVALUE


MAXVALUE


MINVALUE (*)


CYCLE



Incorrect Incorrect. Refer to Section 16 Lesson 1.


2. Nextval and Currval are known as column aliases. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


3. A sequence is a window through which data can be queried or changed. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


4. 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)


MINVALUE is equal to 1. (*)


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


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


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



Incorrect Incorrect. Refer to Section 16 Lesson 1.


5. Which is the correct syntax for specifying a maximum value in a sequence? Mark for Review
(1) Points


Maximumvalue


Maxvalue (*)


Max_value


Maxval



Incorrect Incorrect. Refer to Section 16 Lesson 1.


Page 1 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

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


CURRVAL (*)


NEXTVAL


NEXT


CURRENT



Correct Correct


7. 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



Correct Correct


8. 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 (*)



Correct Correct


9. 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 SYNONYM cust ON mary.customers;
GRANT SELECT ON cust TO PUBLIC;


CREATE PUBLIC SYNONYM cust ON mary.customers;


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



CREATE SYNONYM cust ON mary.customers FOR PUBLIC;



Incorrect Incorrect. Refer to Section 16 Lesson 2.


10. Unique indexes are automatically created on columns that have which two types of constraints? Mark for Review
(1) Points


NOT NULL and UNIQUE


PRIMARY KEY and FOREIGN KEY


UNIQUE and PRIMARY KEY (*)


UNIQUE and FOREIGN KEY



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 2 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

11. 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;


SELECT index_name, table_name, uniqueness
FROM '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';



Incorrect Incorrect. Refer to Section 16 Lesson 2.


12. 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 (*)



Correct Correct


13. Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created? Mark for Review
(1) Points


A FOREIGN KEY constraint


A CHECK constraint


A PRIMARY KEY constraint


An index (*)



Correct Correct


14. 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. (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


15. All tables must have indexes on them otherwise they cannot be queried. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Previous Page 3 of 3 Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

1. 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 a function_based index on ((salary * 12) > 100000).


Create an index on (salary).


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


Create a composite index on (salary,12).



Correct Correct


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 error message is produced, and no index is created. (*)


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


An index named CLIENTS_INDEX is created on the CLIENTS table.


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



Correct Correct


3. 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 (*)



Correct Correct


4. Which of the following statements best describes indexes and their use? Mark for Review
(1) Points


They are just copies of data in no particular order.


They contain the column value and pointers to the data in the table, but the data is sorted. (*)


They contain all the rows and columns from the table


None of the above



Correct Correct


5. In SQL what is a synonym? Mark for Review
(1) Points


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


A table with the same name as another view


A table with the same number of columns as another table


A table that must be qualified with a username



Correct Correct


Page 1 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

6. Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created? Mark for Review
(1) Points


A FOREIGN KEY constraint


A PRIMARY KEY constraint


A CHECK constraint


An index (*)



Correct Correct


7. Which one of the following statements about indexes is true? Mark for Review
(1) Points


An index is never created for a unique constraint.


An index cannot be created before a PRIMARY KEY constraint is created.


An index is created automatically when a PRIMARY KEY constraint is created. (*)


An index must be created by a database administrator when a PRIMARY KEY constraint is created.



Correct Correct


8. 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.



Correct Correct


9. Which keyword is used to remove a sequence? Mark for Review
(1) Points


Revoke


Delete


Remove


Drop (*)



Correct Correct


10. 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 (*)


1100, 1200, 1300


0, 1, 2



Incorrect Incorrect. Refer to Section 16 Lesson 1.


Previous Page 2 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

11. Which of the following best describes the function of the CURRVAL virtual column? Mark for Review
(1) Points


The CURRVAL virtual column will increment a sequence by a specified value.


The CURRVAL virtual column will return a value of 1 for a parent record in a hierarchical result set.


The CURRVAL virtual column will display either the physical locations or the logical locations of the rows in the table.


The CURRVAL virtual column will display the integer that was most recently supplied by a sequence. (*)



Incorrect Incorrect. Refer to Section 16 Lesson 1.


12. 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)


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


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


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


MINVALUE is equal to 1. (*)



Correct Correct


13. Which dictionary view would you query to display the number most recently generated by a sequence? Mark for Review
(1) Points


USER_TABLES


USER_CURRVALUES


USER_OBJECTS


USER_SEQUENCES (*)



Correct Correct


14. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
(1) Points


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


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


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


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



Correct Correct


15. 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



Correct Correct


Previous Page 3 of 3 Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

1. You must use a synonym to access another users table. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


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. (*)



Correct Correct


3. It is possible to have an indexed column in a table where a value in the table column does not exist in the index. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


4. Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created? Mark for Review
(1) Points


An index (*)


A FOREIGN KEY constraint


A PRIMARY KEY constraint


A CHECK constraint



Correct Correct


5. 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)


CREATE SYNONYM marys_emp FOR mary(emp);


SELECT * FROM emp; (*)


SELECT * FROM emp.mary;


SELECT * FROM mary.emp; (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


Page 1 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

6. In SQL what is a synonym? Mark for Review
(1) Points


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


A table with the same name as another view


A table that must be qualified with a username


A table with the same number of columns as another table



Correct Correct


7. 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 private synonym on the EMPLOYEES table that you own.


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 owned by user Sam. (*)



Incorrect Incorrect. Refer to Section 16 Lesson 2.


8. 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 (*)



Correct Correct


9. When creating a sequence, which keyword or option specifies the minimum sequence value? Mark for Review
(1) Points


NOMAXVALUE


MAXVALUE


MINVALUE (*)


CYCLE



Correct Correct


10. 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 (*)



Correct Correct


Previous Page 2 of 3 Next Summary


Test: Section 16 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 16 Quiz
(Answer all questions in this section)

11. 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


1100, 1200, 1300


1000, 1010, 1020 (*)


0, 1, 2


100010011002



Correct Correct


12. Which keyword is used to modify a sequence? Mark for Review
(1) Points


Change


Create


Update


Alter (*)



Correct Correct


13. CURRVAL is a pseudocolumn used to extract successive sequence numbers from a specified sequence. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


14. Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;

What does this statement accomplish?

 Mark for Review
(1) Points


It displays the next available value of the PO_ITEM_ID_SEQ sequence.


It displays 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.


It resets the current value of the PO_ITEM_ID_SEQ sequence.



Correct Correct


15. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
(1) Points


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


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


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


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



Correct Correct


Previous Page 3 of 3 Summary