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) Only the SELECT privilege.
D) No privileges.

2. Examine these statements:
CREATE ROLE registrar;
GRANT UPDATE ON student_grades TO registrar;
GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?

A) It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
B) It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
C) The set of statements contains an error and does not work.
D) It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
E) It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
F) It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.

3. Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?

A) 50 and 45.9
B) 50 and 45.93
C) 46 and 45
D) 45.95 and 45.93
E) 45 and 45.93
F) 46 and 45.93

4. You want to display the titles of books that meet these criteria:
1.Purchased before January 21, 2001
2.Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently
bought book.
Which statement should you use?

A) SELECT book_title
FROM books
WHERE price between 500 and 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase_date;
B) SELECT book_title
FROM books
WHERE (price < 500 OR price > 900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;
C) SELECT book_title
FROM books
WHERE price < 500 or > 900
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date DESC;
D) SELECT book_title
FROM books
WHERE price IN (500,900)
AND purchase_date < '21-JAN-2001'
ORDER BY purchase date ASC;

5. Examine the structures of the EMPLOYEES and TAX tables.

You need to find the percentage tax applicable for each employee. Which SQL statement would you use?

A) You cannot find the information because there is no common column between the two tables.
B) SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary
C) SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
D) SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary > t.min_salary, tax_percent

Solutions: