INF3707 Past Exams.

INF3707 Past
Exams.

, May 2019
Section A
Question 1
1. [40 Marks]
1.1. Which of the following is true of the SQL query given below? (Choose the best
answer)
SELECT col1, col2
FROM tab1
WHERE col1 = ‘A’
ORDER BY col2 DESC, col1;
a. It will display the row which has the col1 value as ‘A’ ordered by the col1 in
descending order and then col2 in the descending order.
b. The ORDER BY clause will not work as the keyword DESC should always be written
in the end of the ORDER BY clause and not in between as given in the query.
c. The above query will be sorted in descending order on the basis of col2 only
and the use of col1 in the ORDER BY clause will be discarded.
d. It will display the row which has the col1 value as ‘A’ ordered by the col1 and
then followed by col2 as the execution of the ORDER BY clause happens from the
order of the columns in the SELECT statement
1.2. What does the following query do? (Choose the best answer)
SELECT isbn, title
FROM BOOKSWHERE (pubid, category) IN
(SELECT pubid, category FROM BOOKS WHERE title like ‘%ORACLE%’)
a. It determines which publisher published a book belonging to the Oracle
category and then lists all other books published by that same publisher.
b. It lists all publishers and categories containing the value of ORACLE
c. It lists the ISBN and title of all books belonging to the same category and
having the same publisher as any book with the phrase ORACLE in its title.
d. None of the above. The query contains a multiple-row operator and because
the inner query returns only one value, the SELECT statement will fail and return
an error message
1.3. Based on the structure of the CUSTOMERS table given below, which of the following is
a valid SQL statement? (Choose the best answer)




a. CREATE PUBLIC INDEX customers_name_idx
ON customers (lastname, firstname)
b. CREATE BITMAP INDEX customers_name_idx
FOR customers (lastname)
c. CREATE INDEX customers_name_idx
ON customers (lastname, firstname)
d. CREATE B_TREE INDEX customers_name_idx
FOR customers (lastname)
1.4. Which data dictionary objects contain a column named HIDDEN_COLUMN? (Choose the
best answer)
2

, a. USER_HIDE_COLS
b. USER_TABLES
c. USER_COLUMNS
d. USER_TAB_COLS
1.5. Which of the following commands lock the JustLee database’ BOOKS table in EXCLUSIVE
mode? (Choose the best answer)
a. LOCK table BOOKS EXCLUSIVELY
b. LOCK table BOOKS IN EXCLUSIVE MODE
c. LOCK table BOOKS TO OTHER USERS
d. All the answers are correct
1.6. You successfully execute the following SQL statement:
GRANT UPDATE (Orderdate)
ON OrdersTO David
WITH GRANT OPTION;


Which two statements are true? (Choose 2 best answers that apply)
a. DAVID may grant this privilege to all users in the database, but he may not
grant it to PUBLIC
b. DAVID may grant this privilege to a role that has been granted to him
c. DAVID may revoke this privilege from users who have been granted this
privilege by him
d. The DBA may revoke this privilege from roles that have been granted this
privilege by DAVID
1.7. Which three statements are true about roles? (Choose 3 best answers that apply)
a. A password may be specified for a role only when the role is created.
b. A role may be granted both system and object privileges.
c. A role may be dropped only after it is revoked manually from all users and roles
to which it has been granted.
d. Multiple roles may be granted to a role.
e. Multiple roles granted to a user may be enabled as default roles for that user.
1.8. Which three statements are true about indexes in Oracle Database 12c? (Choose 3
best answers that apply)
a. There may be more than one index on a single column
b. Indexes may not be always speed up data access to table data
c. Multiple invisible indexes may exist on a column
d. Indexes may be created on a view
1.9. Which of the following are valid CREATE TABLE statements? (Choose 3 best answers
that apply)
a. CREATE TABLE $ORDERS
(ID NUMBER, NAME VARCHAR2(30));
b. CREATE TABLE CUSTOMER_HISTORY
(ID NUMBER, NAME VARCHAR2(30));
c. CREATE TABLE “Boat Inventory”
(ID NUMBER, NAME VARCHAR2(30));
d. CREATE TABLE workSchedule
(ID NUMBER, NAME VARCHAR2(30));
1.10. Which of the following are Data Manipulation Language statements? (Choose 2
best answers that apply)
a. SELECT
b. GRANT
c. INSERT
d. DROP
2