INF3707 Past
Exams.100% CORRECT questions, answers, workings and explanations. for assistance. 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...
, 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
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller LOVELY01. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $2.80. You're not tied to anything after your purchase.
