Database Programming Section 16 Quiz
Section 16 Quiz
(Answer all questions in this section)
1. 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
2. What is the most common use for a Sequence? Mark for Review
(1) Points
To give an alternative name for an object
To improve the performance of some queries
To generate primary key values (*)
To logically represent subsets of data from one or more tables
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
100010011002
1100, 1200, 1300
1000, 1010, 1020 (*)
4. Which pseudocolumn returns the latest value supplied by a
sequence? Mark for Review
(1) Points
NEXTVAL
CURRENT
NEXT
CURRVAL (*)
5. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
Which statement is true?
Mark for Review
(1) Points
The sequence will continue to generate values after reaching its
maximum value.
The sequence will start with 1. (*)
The sequence has no maximum value.
The sequence preallocates values and retains them in memory.
(Answer all questions in this section)
(Answer all questions in this section)
6. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq CYCLE;
Which statement is true?
Mark for Review
(1) Points
The sequence will continue to generate values after the maximum
sequence value has been generated. (*)
The sequence cannot generate additional values after reaching its
maximum value.
The sequence cannot be used with more than one table.
The sequence preallocates values and retains them in memory.
7. Which statement would you use to remove the EMP_ID_SEQ
sequence? Mark for Review
(1) Points
REMOVE SEQUENCE emp_id_seq;
ALTER SEQUENCE emp_id_seq;
DROP SEQUENCE emp_id_seq; (*)
DELETE SEQUENCE emp_id_seq;
8. 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 reuse numbers and will start with 101. (*)
The sequence will generate decrementing sequence numbers starting
at 101.
9. 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.
10. 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 owned by
user Sam. (*)
You created a public synonym on the EMPLOYEES table owned by
user Sam.
You created a private synonym on the EMPLOYEES table that you
own.
(Answer all questions in this section)
11. 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.mary;
SELECT * FROM emp; (*)
CREATE SYNONYM marys_emp FOR mary(emp);
SELECT * FROM mary.emp; (*)
12. 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). (*)
Create a composite index on (salary,12).
Create an index on (salary).
Create a function_based index on ((salary * 12) > 100000).
13. Indexes can be used to speed up queries. True or False? Mark
for Review
(1) Points
True (*)
False
14. What is the correct syntax for creating a private synonym d_sum
for the view DEPT_SUM_VU? Mark for Review
(1) Points
CREATE SYNONYM d_sum
ON dept_sum_vu;
CREATE SYNONYM d_sum
FOR dept_sum_vu;
(*)
CREATE d_sum SYNONYM
FOR dept_sum_vu;
UPDATE dept_sum_vu
ON SYNONYM d_sum;
15. 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.
