Quizzuri Oracle

SQL Programming #14

Posted by Feroviarul on

Oracle Database Programming with SQL Section 14

1.         Sequences can be used to: (Choose three)
(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?
♦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?
♦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?
♦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?
♦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?
♦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?
♦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)
(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?
♦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)
(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?
♦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?
♦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?
♦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?
♦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?
♦DEPARTMENT_ID
♦HIRE_DATE
♦EMPLOYEE_ID (*)
♦LAST_NAME
♦SALARY
16.         Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
Which statement is true?
♦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.
17.         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?
♦MAXVALUE
♦CACHE
♦NOCACHE (*)
18.         You need to retrieve the next available value for the SALES_IDX sequence.
Which would you include in your SQL statement?
♦sales_idx.CURRVAL
♦sales_idx.NEXT
♦sales_idx
♦sales_idx.NEXTVAL (*)
19.         Evaluate this statement:
CREATE SEQUENCE sales_item_id_seq
START WITH 101 MAXVALUE 9000090 CYCLE;
Which statement about this CREATE SEQUENCE statement is true?
♦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. (*)
20.         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?
♦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.
21.         Which dictionary view would you query to display the number most recently generated by a sequence?
♦USER_CURRVALUES
♦USER_TABLES
♦USER_SEQUENCES (*)
♦USER_OBJECTS
22.         Why do gaps in sequences occur?
♦A rollback is executed
♦The system crashes
♦The sequence is used in another table
♦All of the above (*)
23.         Nextval and Currval are known as column aliases. True or False?
♦True
♦False (*)
24.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?
♦A PRIMARY KEY constraint
♦A FOREIGN KEY constraint
♦An index (*)
♦A CHECK constraint
25.       Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?
♦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;(*)
26.       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?
♦EMP_ID only
♦JOB_ID only
♦DEPT_ID only
♦EMP_ID and JOB_ID
♦All Indexes (*)
27.       All tables must have indexes on them otherwise they cannot be queried. True or False?
♦True
♦False (*)
28.       Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?
♦CREATE UNRESTRICTED SYNONYM emp FOR EMPLOYEES
♦CREATE PUBLIC SYNONYM emp FOR EMPLOYEES (*)
♦CREATE SHARED SYNONYM emp FOR EMPLOYEES
♦CREATE SYNONYM emp FOR EMPLOYEES
29.       What is the correct syntax for creating an index?
♦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); (*)
30.       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?
♦LAST_NAME
♦EMPLOYEE_ID (*)
♦DEPARTMENT_ID
♦HIRE_DATE
♦SALARY
31.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?
            ♦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;
32.         What kind of INDEX is created by Oracle when you create a primary key?
♦UNIQUE INDEX (*)
♦NONUNIQUE INDEX
♦INDEX
♦Oracle cannot create indexes automatically.
33.         Indexes can be used to speed up queries. True or False?
♦True (*)
♦False
34.         When creating an index on one or more columns of a table, which of the following statements are true?
(Choose two)       (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.
35.         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?
♦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;
36.         Which of the following best describes the function of an index?
♦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. (*)
37.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?
♦A PRIMARY KEY constraint
♦An index (*)
♦A CHECK constraint
♦A FOREIGN KEY constraint
38.         Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?
♦ALTER TABLE employees ;
♦CREATE SEQUENCE emp_id_seq;
♦ALTER SEQUENCE emp_id_seq; (*)
♦ALTER SEQUENCE emp_id_seq.employee_id;
39.         The ALTER SEQUENCE statement can be used to:
♦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
40.       A sequence is a database object. True or False?
♦True (*)
♦False
41.       You issue this statement:
ALTER SEQUENCE po_sequence INCREMENT BY 2;
Which statement is true?
♦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.
42.       Sequences can be used to: (Choose three)       (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
43.       Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq CYCLE;
Which statement is true?
♦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. (*)
44.       Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
Which statement is true?
♦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.
45.       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?
♦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. (*)
46.         Creating a sequence with NOCACHE ensures that all numbers in the sequence’s range will be used successfully. True or False?
♦True
♦False (*)
47.         Which of the following best describes the function of the NEXTVAL virtual column?
            ♦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.
48.         Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;
Which statement is true?
♦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. (*)
49.         Why do gaps in sequences occur?
♦A rollback is executed
♦The system crashes
♦The sequence is used in another table
♦All of the above (*)
50.         When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?
♦LOAD
♦NOCYCLE
♦NOCACHE
♦CACHE (*)
♦MEMORY
51.         Which pseudocolumn returns the latest value supplied by a sequence?
♦NEXT
♦CURRENT
♦NEXTVAL
♦CURRVAL (*)
52.         Which statement would you use to remove the EMP_ID_SEQ sequence?
♦ALTER SEQUENCE emp_id_seq;
♦DROP SEQUENCE emp_id_seq; (*)
♦REMOVE SEQUENCE emp_id_seq;
♦DELETE SEQUENCE emp_id_seq;
53.         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?
♦130120110100130
♦The fifth number cannot be generated.
♦100110120130100
♦The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)
54.         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?
♦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.
55.       In SQL what is a synonym?
♦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
56.       Indexes can be used to speed up queries. True or False?
♦True (*)
♦False
57.       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?
♦An index
♦A view
♦A synonym (*)
♦A schema
58.       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?
♦EMPLOYEE_ID (*)
♦LAST_NAME
♦HIRE_DATE
♦SALARY
♦DEPARTMENT_ID
59.       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?
♦EMP_ID only
♦JOB_ID only
♦DEPT_ID only
♦EMP_ID and JOB_ID
♦All Indexes (*)
60.       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?
♦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). (*)
61.         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?
            ♦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’;
(*)
62.         Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?
♦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;
(*)
63.         Indexes can be used to speed up queries. True or False?
♦True (*)
♦False
64.         As user Julie, you issue this statement:
CREATE SYNONYM emp FOR sam.employees;
Which task was accomplished by this statement?
♦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. (*)

65.         For which column would you create an index?
♦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 (*)

66.         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?
            ♦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);
(*)
67.         What kind of INDEX is created by Oracle when you create a primary key?
♦UNIQUE INDEX (*)
♦NONUNIQUE INDEX
♦INDEX
♦Oracle cannot create indexes automatically.
68.         When creating a sequence, which keyword or option specifies the minimum sequence value?
♦CYCLE
♦MINVALUE (*)
♦MAXVALUE
♦NOMAXVALUE
69.         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?
♦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.
70.       Evaluate this statement:
CREATE SEQUENCE sales_item_id_seq
START WITH 101 MAXVALUE 9000090 CYCLE;
Which statement about this CREATE SEQUENCE statement is true?
♦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.
71.       Creating a sequence with NOCACHE ensures that all numbers in the sequence’s range will be used successfully. True or False?
♦True
♦False (*)
72.       When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?
♦NOCYCLE
♦NOCACHE
♦CACHE (*)
♦LOAD
♦MEMORY
73.       A sequence is a database object. True or False?
♦True (*)
♦False

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

♦True (*)
♦False

75.       CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?
♦True (*)
♦False
76.         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?
♦USER_INDEXES
♦USER_OBJECTS
♦USER_TABLES
♦USER_IND_COLUMNS (*)
77.         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?
♦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. (*)
78.         What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?
            ♦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;
(*)
79.         All tables must have indexes on them otherwise they cannot be queried. True or False?
♦True
♦False (*)
80.         As user Julie, you issue this statement:
CREATE SYNONYM emp FOR sam.employees;
Which task was accomplished by this statement?
♦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.
81.         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?
            ♦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’;
82.         Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?
♦An index (*)
♦A CHECK constraint
♦A PRIMARY KEY constraint
♦A FOREIGN KEY constraint
83.         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?
♦100010011002
♦1000, 1010, 1020 (*)
♦0, 1, 2
♦1100, 1200, 1300
84.         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?
♦NOCACHE (*)
♦CACHE
♦MAXVALUE
85.       Which keyword is used to modify a sequence?
♦Alter (*)
♦Update
♦Change
♦Create
86.       Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;
What does this statement accomplish?
♦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.
87.       Which is the correct syntax for specifying a maximum value in a sequence?
♦Maxval
♦Max_value
♦Maxvalue (*)
♦Maximumvalue
88.       Which statement would you use to remove the EMP_ID_SEQ sequence?
♦DELETE SEQUENCE emp_id_seq;
♦DROP SEQUENCE emp_id_seq; (*)
♦REMOVE SEQUENCE emp_id_seq;
♦ALTER SEQUENCE emp_id_seq;
89.       When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?
♦True (*)
♦False
90.       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?
♦True (*)
♦False

Leave a comment