(1) Points
True (*)
False
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
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
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
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
1. Which of the following best describes the function of the CURRVAL virtual column?
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 the integer that was most recently supplied by a sequence.
The CURRVAL virtual column will increment a sequence by a specified value.
The CURRVAL virtual column will display either the physical locations or the logical locations of the rows in the table.
2.Which is the correct syntax for specifying a maximum value in a sequence? Mark for Review
(1) Points
Maxvalue (*)
Max_value
Maximumvalue
Maxval
3. Which of the following best describes the function of the CURRVAL virtual column? Mark for Review
(1) Points
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 the integer that was most recently supplied by a sequence. (*)
The CURRVAL virtual column will increment a sequence by a specified value.
The CURRVAL virtual column will display either the physical locations or the logical locations of the rows in the table.
Comments
Post a Comment