Oracle Introduction to Oracle9i: SQL Sample Questions:

1. The DBA issues this SQL command:
CREATE USER scott IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

A) only the CONNECT privilege
B) all the privileges of a default user
C) no privileges
D) only the SELECT privilege

2. In which two cases would you use an outer join? (Choose two.)

A) Only when the tables have a primary key/foreign key relationship.
B) The tables being joined have NOT NULL columns.
C) The columns being joined have NULL values.
D) The tables being joined have only matched data.
E) The tables being joined have both matched and unmatched data.
F) The tables being joined have only unmatched data.

3. Which four statements correctly describe functions that are available in SQL? (Choose four)

A) TRUNCATE rounds the column, expression, or value to n decimal places.
B) NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
C) TRIM trims the heading of trailing characters (or both) from a character string.
D) NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.
E) INSTR returns the numeric position of a named character.
F) NVL2 returns the first non-null expression in the expression list.
G) DECODE translates an expression after comparing it to each search value.

4. Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

A) SELECT student_id, marks, ROWNUM "Rank"
FROM (SELECT student_id, marks
FROM students
ORDER BY marks DESC)
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL';
B) SELECT student_ id, marks, ROWNUM "Rank"
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks DESC;
C) SELECT student_id, marks, ROWNUM "Rank"
FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND
'31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks DESC);
D) SELECT student_id, marks, ROWID "Rank"
FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL'
ORDER BY marks;

5. Evaluate the set of SQL statements:
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); ROLLBACK; DESCRIBE DEPT
What is true about the set?

A) The ROLLBACK statement frees the storage space occupies by the DEPT table.
B) The DESCRIBE DEPT statement displays the structure of the DEPT table.
C) The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
D) The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

Solutions: