| 1. | Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2 (25)
LAST_NAME VARCHAR2 (25)
HIRE_DATE DATE
Which UPDATE statement is valid? |
| a. | | UPDATE employees SET first_name = 'John' SET last_name='Smith' WHERE employee_id = 180; |
| b. | | UPDATE employees SET first_name = 'John', SET last_name ='Smith' WHERE employee_id = 180; |
| c. | | UPDATE employees SET first_name = 'John' AND last_name ='Smith' WHERE employee_id = 180; |
| d. | | UPDATE employees SET first_name = 'John', last_name ='Smith' WHERE employee_id = 180;
|
|
|
|
|
|
|
| 2. | What does the TRUNCATE statement do? |
| a. | | Removes the table |
| b. | | Removes all rows from a table |
| c. | | Shortens the tale to 10 rows |
| d. | | Removes all columns from a table
|
|
|
|
|
|
|
| 3. | Which statement will user KING use to create a private synonym when referencing the EMPLOYEE table existing in user JOHN's schema? |
| a. | | CREATE SYNONYM emp
FOR John.employee; |
| b. | | CREATE PUBLIC emp SYNONYM
FOR John.employee; |
| c. | | CREATE PUBLIC SYNONYM emp
FOR John.KING; |
| d. | | CREATE PRIVATE SYNONYM emp
FOR John.employee;
|
|
|
|
|
|
|
| 4. | The following statement was issued:
REVOKE REFERENCES
ON ITEMS
FROM John
CASCADE CONSTRAINTS;
Which of the following tasks were accomplished by executing this statement? |
| a. | | The ability to create a FOREIGN KEY constraint on the ITEMS table was revoked from user John |
| b. | | The ability to create a PRIMARY KEY constraint was revoked from user John |
| c. | | All the FOREIGN KEY constraints on the ITEMS table created by user John were removed |
| d. | | All the PRIMARY KEY constraints created by user John were removed |
| e. | | The ability to create any constraints was revoked from user John |
| f. | | All the constraints created by user John were removed
|
|
|
|
|
|
|
| 5. | Which statement regarding DML statement functionality is true? |
| a. | | UPDATE can update multiple columns in one table |
| b. | | INSERT must contain a VALUES clause |
| c. | | MERGE will delete rows that do NOT exist in either table |
| d. | | UPDATE will add rows to a table if an INTO clause is specified
|
|
|
|
|
|
|
| 6. | With which three constructs could a view be created that would prevent a user from deleting rows in the base table on which the view is based? |
| a. | | Inline view |
| b. | | Subquery |
| c. | | group function |
| d. | | GROUP BY clause |
| e. | | DISTINCT keyword
|
|
|
|
|
|
|
| 7. | Evaluate the following SQL script:
CREATE USER hr IDENTIFIED BY hr01;
CREATE ROLE manager;
CREATE ROLE clerk;
GRANT clerk TO manager;
CREATE ROLE director;
GRANT manager TO director;
GRANT director TO hr
/
How many roles will the HR users have access to? |
| a. | | 1 |
| b. | | 0 |
| c. | | 2 |
| d. | | 3
|
|
|
|
|
|
|
| 8. | A SQL SELECT statement containing the ROWNUM pseudocolumn, an ORDER BY clause, and a subquery was created. What construct was created? |
| a. | | A Complex View |
| b. | | A Top-n Query |
| c. | | A Materialized View |
| d. | | A Hierarchical Report
|
|
|
|
|
|
|
| 9. | A view was created containing groups of data, it does NOT allow DML operations, and does not contain a subquery. Which type of view was created? |
| a. | | Inline |
| b. | | Simple |
| c. | | Complex |
| d. | | Explicit
|
|
|
|
|
|
|
| 10. | Which privilege can only be granted to a user and NOT to a role? |
| a. | | DELETE |
| b. | | ALTER |
| c. | | INSERT |
| d. | | EXECUTE |
| e. | | REFERENCES
|
|
|
|
|
|
|
| 11. | Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES:
---------------------
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2 (25)
LAST_NAME VARCHAR2 (25)
HIRE_DATE DATE
NEW_EMPLOYEES:
---------------------------
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2 (60)
Which MERGE statement is valid? |
| a. | | MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name); |
| b. | | MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name); |
| c. | | MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name); |
| d. | | MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.! last_name);
|
|
|
|
|
|
|
| 12. | Evaluate the following DELETE statement:
DELETE employee_id, salary, job_id FROM employees
WHERE dept_id = 40;
Why does the DELETE statement fail when executed? |
| a. | | There is no row with dept_id 90 in the EMPLOYEES table |
| b. | | The JOB_ID column cannot be deleted because it is a NOT NULL column |
| c. | | Column names were not specified in the DELETE clause of the DELETE statement |
| d. | | The EMPLOYEE_ID column cannot be deleted because it is the primary key of the table
|
|
|
|
|
|
|
