| 1. | Which Data Dictionary view holds information about the column in a view? |
| a. | | USER_VIEWS |
| b. | | USER_VIEW_COLUMNS |
| c. | | USER_TAB_COLUMNS |
| d. | | USER_ALL_COLUMNS
|
|
|
|
|
|
|
| 2. | Which of the following SQL statements should be used to remove a view called EMP_DEPT_VU from the schema? |
| a. | | DROP emp_dept_vu; |
| b. | | DELETE emp_dept_vu; |
| c. | | REMOVE emp_dept_vu; |
| d. | | DROP VIEW emp_dept_vu; |
| e. | | DELETE VIEW emp_dept_vu; |
| f. | | REMOVE VIEW emp_dept_vu;
|
|
|
|
|
|
|
| 3. | The STUDENTS table needs to be modified to add a primary key on the STUDENT_ID column. The table is currently empty. Which of the following statements will accomplish the task? |
| a. | | ALTER TABLE students ADD PRIMARY KEY student_id; |
| b. | | ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id); |
| c. | | ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id; |
| d. | | ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); |
| e. | | ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
|
|
|
|
|
|
|
| 4. | Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables given below:
EMPLOYEES
---------------------
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2 (20)
LAST_NAME VARCHAR2 (20)
HIRE_DATE DATE
NEW_EMPLOYEES
------------------------
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2 (40)
Which of the following DELETE statements is valid? |
| a. | | DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees); |
| b. | | DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees); |
| c. | | DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name ='Carrey'); |
| d. | | DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name ='Carrey'
|
|
|
|
|
|
|
| 5. | Which of the following CREATE TABLE statements will fail? |
| a. | | CREATE TABLE time1 (time1 NUMBER(9)); |
| b. | | CREATE TABLE time (time_id NUMBER(9)); |
| c. | | CREATE TABLE date (time_id NUMBER(9)); |
| d. | | CREATE TABLE time* (time_id NUMBER(9)); |
| e. | | CREATE TABLE $time (time_id NUMBER(9)); |
| f. | | CREATE TABLE datetime (time_id NUMBER(9));
|
|
|
|
|
|
|
| 6. | What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints? |
| a. | | ALTER TABLE table_name
REMOVE CONSTRAINT PRIMARY KEY CASCADE; |
| b. | | ALTER TABLE table_name
DROP PRIMARY KEY CASCADE; |
| c. | | ALTER TABLE table_name
DISABLE CONSTRAINT PRIMARY KEY CASCADE; |
| d. | | A PRIMARY KEY constraint CANNOT be removed if it has dependent constraints
|
|
|
|
|
|
|
| 7. | Which statement type would be used to remove transactions more than one year old from the TRX table? |
| a. | | DCL |
| b. | | DML |
| c. | | DDL |
| d. | | TCL |
| e. | | DRL
|
|
|
|
|
|
|
| 8. | The PRIMARY KEY constraint was disabled on the ID column in the Product table and all the values in the Product table were updated. The constraint is to be enabled and it should be verified that the new ID column values do not violate the constraint. If any of the ID column values do not conform to the constraint, an error message should be returned.
Evaluate the following statement:
ALTER TABLE product
ENABLE CONSTRAINT product_id_pk;
Which statement is true? |
| a. | | The statement will execute, but will NOT enable the PRIMARY KEY constraint |
| b. | | The statement will achieve the desired results |
| c. | | The statement will execute, but will NOT verify that values in the ID column do NOT violate the constraint |
| d. | | The statement will return a syntax error
|
|
|
|
|
|
|
| 9. | A user JOE needs to be created and this user should be allowed to create and drop tables in any schema. He should be able to create procedures and sequences only in his schema. Which script should be used to achieve these results? |
| a. | | CREATE USER joe
IDENTIFIED BY joe123;
GRANT DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO joe |
| b. | | CREATE USER joe
IDENTIFIED BY joe123;
GRANT CREATE TABLE, DROP TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO joe |
| c. | | CREATE USER joe
IDENTIFIED BY joe123;
GRANT CREATE SESSION, CREATE ANY TABLE, DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO joe |
| d. | | CREATE USER joe
IDENTIFIED BY joe123;
GRANT CREATE SESSION, DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO joe
|
|
|
|
|
|
|
| 10. | Which statement about a sequence is true? |
| a. | | A sequence can only be used to create a primary key value |
| b. | | One sequence can be used for multiple tables in the same schema |
| c. | | Creating a sequence causes sequence numbers are stored in a table |
| d. | | A sequence slows down the efficiency of accessing sequence values cached in memory
|
|
|
|
|
|
|
| 11. | The MANAGER role should be given the ability to select from insert into and modify existing rows in the STUDENT_GRADES table.
Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this? |
| a. | | GRANT select, insert, update ON student_grades TO manager; |
| b. | | GRANT select, insert, update ON student_grades TO ROLE manager |
| c. | | GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION; |
| d. | | GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION
|
|
|
|
|
|
|
| 12. | A view was created with the following command:
CREATE FORCE VIEW first_vu
AS SELECT first_name ||' '|| last_name "Employee Names"
FROM employee
ORDER BY last_name, first_name;
Which clause causes an error? |
| a. | | CREATE FORCE VIEW first_vu |
| b. | | ORDER BY last_name, first_name |
| c. | | FROM employee |
| d. | | AS SELECT first_name ||' '|| last_name "Employee Names"
|
|
|
|
|
|
|
