Oracle Introduction to Oracle9i: SQL Sample Questions:

1. Examine the structure of the EMPLOYEES, DEPARTMENTS, and TAX tables.

For which situation would you use a nonequijoin query?

A) To find the number of employees working for the Administrative department and earning less then 4000.
B) To find the name, salary, and department name of employees who are not working with Smith.
C) To list the name, job id, and manager name for all the employees.
D) To find the tax percentage for each of the employees.
E) To display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned.

2. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

A) ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
B) ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C) ALTER TABLE students ADD PRIMARY KEY student_id;
D) ALTER TABLE studentsMODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E) ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

3. 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) There is no need to specify DESC because the results are sorted in descending order by default.
C) The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
D) The two statements produce identical results.

4. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

A) ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
B) ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
C) ALTER TABLE students
ADD PRIMARY KEY student_id;
D) ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
E) ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

5. 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 = 'Smoth'
WHERE employee_id = 180;
B) UPDATE employee
SET first_name = 'John', last_name = 'Smith'
WHERE employee_id = 180;
C) UPDATE employees
SET first_name = 'John'
SET last_name = 'Smith'
WHERE employee_id = 180;
D) UPDATE employee
SET first_name = 'John'
AND last_name = 'Smith'
WHERE employee_id = 180;

Solutions: