Introduction to Oracle9i: SQL (1Z0-007) Free Practice Test

Question 1

Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?

A. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';

B. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

C. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';

D. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

E. SELECT COUNT(*) FROM employees WHERE last_name='Smith';

Correct Answer: B

Question 2

You need to perform these tasks:
1.Create and assign a MANAGER role to Blake and Clark
2.Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark
Which set of SQL statements achieves the desired results?

A. CREATE ROLE manager;
GRANT create table, create voew
TO manager;
GRANT manager ROLE TO BLAKE,CLARK;

B. GRANT manager ROLE TO BLAKE,CLARK;
GRANT create table, create voew
TO BLAKE CLARK;
***MISSING***

C. CREATE ROLE manager;
GRANT create table, create view
TO manager;
GRANT manager TO BLAKE,CLARK;

Correct Answer: C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 3

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 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 studentsMODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

E. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Correct Answer: E

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 4

Examine the data in the EMPLOYEES table.

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column.
Evaluate this DELETE statement:
DELETE employee_id, salary, job_id FROM employees WHERE dept_id = 90;
Why does the DELETE statement fail when you execute it?

A. You cannot delete the JOB_ID column because it is a NOT NULL column.

B. You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.

C. You cannot specify column names in the DELETE clause of the DELETE statement.

D. There is no row with dept_id 90 in the EMPLOYEES table.

Correct Answer: C

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 5

Which three statements about subqueries are true? (Choose three.)

A. A main query can have more than one subquery.

B. A subquery can have more than one main query.

C. The subquery and main query can retrieve data from different tables.

D. Multiple columns or expression can be compared between the subquery and main query.

E. Only one column or expression can be compared between the subquery and main query.

F. The subquery and main query must retrieve data from the same table.

Correct Answer: A,C,E

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 6

Exhibit:

Examine the data in the EMPLOYEES table.
Examine the subquery:
SELECT last_name FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id);
Which statement is true?

A. The SELECT statement does not work because the column specified in the GROUP BY clause is not in the SELECT list.

B. The SELECT statement does not work because the GROUP BY clause should be in the
main query and not in the subquery.

C. The SELECT statement does not work because there is no HAVING clause.

D. The SELECT statement is syntactically accurate.

Correct Answer: D

Welcome to TestSimulate

Oracle Introduction to Oracle9i: SQL (1Z0-007) Free Practice Test