Oracle Introduction to Oracle9i: SQL Sample Questions:

1. Which three statements correctly describe the functions and use of constraints? (Choose three.)

A) Constraints prevent the deletion of a table if there are dependencies.
B) Constraints prevent the deletion of an index if there are dependencies.
C) Constraints provide data independence.
D) Constraints make complex queries easy.
E) Constraints enforce rules at the view level.
F) Constraints enforce rules at the table level.

2. Examine the data in the EMPLOYEES and DEPARTMENTS tables:

Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables:
CREATE TABLE departments
(department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(30));
CREATE TABLE employees
(EMPLOYEE_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER REFERENCES
departments(department_id),
MGR_ID NUMBER REFERENCES
employees(employee id),
MGR_ID NUMBER REFERENCES
employees(employee id),
JOB_ID VARCHAR2(15).
SALARY NUMBER);
ON the EMPLOYEES,
On the EMPLOYEES table, EMPLOYEE_ID is the primary key.
MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.
DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table.
On the DEPARTMENTS table, DEPARTMENT_ID is the primary key.
Examine this DELETE statement:
DELETE
FROM departments
WHERE department id = 40;
What happens when you execute the DELETE statement?

A) The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the rows in the EMPLOYEES table are deleted.
B) Only the row with department ID 40 is deleted in the DEPARTMENTS table.
C) The statement fails because there are no columns specifies in the DELETE clause of the DELETE statement.
D) The statement fails because there are child records in the EMPLOYEES table with department ID 40.
E) The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.
F) The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.

3. Which are DML statements? (Choose all that apply)

A) DROP...
B) CREAT..
C) DELETE...
D) UPDATE...
E) COMMIT...
F) MERGE...

4. Examine the statement:
GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION;
Which two are true? (Choose two.)

A) It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.
B) It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.
C) It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.
D) MANAGER must be a role.
E) It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.
F) It allows the MANAGER to pass the specified privileges on to other users.

5. Evaluate these two SQL statements:
SELECT last_name, salary , hire_date
FROM EMPLOYEES
ORDER BY salary DESC;
SELECT last_name, salary, hire_date
FROM EMPLOYEES
ORDER BY 2 DESC;
What is true about them?

A) The second statement returns a syntax error.
B) The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
C) The two statements produce identical results.
D) There is no need to specify DESC because the results are sorted in descending order by default.

Solutions: