PART A - (20 marks)

This question comprises 20 multiple choice questions. Each question is worth 1 mark. Choose the option which you think is correct. MARK THE CORRECT ANSWER ON THE EXAMINATION ANSWER SHEET PROVIDED. FIRST READ THE INSTRUCTIONS ON THE SHEET.
Contents of the BOOKS table

Question 1
Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table?
a) SELECT * FROM books;
b) SELECT title, retail, cost profit FROM books;
c) SELECT title, retail-cost FROM books;
d) all of the above
Structure of the ORDERS table

Question 2
Which of the following is a valid SELECT statement?
a) SELECT order#, || ' was shipped on ' || shipdate;
b) SELECT order# || ' was shipped on ' || shipdate;
c) SELECT order# || - was shipped on - || shipdate;
d) none of the above
Question 3
Which of the following is a valid table name?
a) MYTABLE
b) DISTINCT
c) SELECT
d) CHAR
Question 4
Which command instructs Oracle10g to create a new table?
a) CREATE NEW TABLE
b) CREATE TABLE...FROM
c) CREATE TABLE
d) ALTER TABLE
Question 5
Which of the following keywords uses a subquery to create a new table using existing database tables?
a) ALTER TABLE
b) GENERATE TABLE
c) CREATE TABLE...AS
d) CREATE TABLE...FROM
Question 6
Which of the following commands will drop any columns marked as unused from the TABLEA table?
a) DROP COLUMN FROM tablea WHERE column_status = UNUSED;
b) ALTER TABLE tablea DROP UNUSED COLUMNS;
c) ALTER TABLE tablea DROP (unused);
d) DROP UNUSED COLUMNS:
Question 7
When the WHERE clause contains multiple types of operators, which of the following is resolved first?
a) arithmetic operations
b) comparison operators
c) logical operators
d) union operators
Contents of the ORDERS table

Question 8
Based upon the contents of the ORDERS table, which of the following SQL statements will display the number of days between the date an order was placed and the date it was shipped?
a) SELECT order#, TO_CHAR(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
b) SELECT order#, MONTHS_BETWEEN(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
c) SELECT order#, ADD_MONTHS(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
d) SELECT order#, DAYS_BETWEEN(shipdate-orderdate, '99') FROM orders
WHERE shipdate IS NOT NULL;
Question 9
Based on the contents of the ORDERS table, which of the following SELECT statements will determine the number of orders placed by each customer?
a) SELECT COUNT(DISTINCT(customer#))
FROM orders;
b) SELECT COUNT(*)
FROM orders;
c) SELECT customer#, COUNT(customer#)
FROM orders
GROUP BY customer#;
d) none of the above

Question 10
Which of the following can only be used to link tables that have a common column?
a) FULL OUTER JOIN
b) JOIN...ON
c) NATURAL JOIN
d) CROSS JOIN

Structure of the CUSTOMERS table

Structure of the ORDERS table

Structure of the ORDERITEMS table

Structure of the BOOKS table
Question 11
Which of the following SQL statements will display the names of all customers who have purchased a copy of E-BUSINESS THE EASY WAY?
a) SELECT lastname, firstname
FROM customers NATURAL JOIN books
WHERE title LIKE '%BUSI%';
b) SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE title LIKE '%BUSI%';
c) SELECT lastname, firstname
FROM customers c, books b, orders o, orderitems oi
WHERE c.customer# = o.customer# AND o.order# = oi.order# AND
oi.isbn = b.isbn AND title LIKE '%BUSI%';
d) none of the above
Question 12
Which of the following SQL statements will display the title of all books that have had multiple copies requested in a single order?
a) SELECT title
FROM books NATURAL JOIN orderitems
WHERE qty 1;
b) SELECT title
FROM books JOIN orderitems
WHERE qty 1;
c) SELECT title
FROM books JOIN orderitems ON(isbn) JOIN orders ON(order#)
WHERE qty 1;
d) SELECT title
FROM books JOIN orderitems USING(isbn);
Question 13
Which of the following functions can be used to convert a character string to upper-case letters?
a) UPPER
b) UP
c) UPPERCASE
d) UPPERCAP

Question 14
Which of the following functions will convert the first letter of each word to an upper-case letter and the remaining letters of the word to lower-case letters?
a) INITIALCAPITAL
b) INITIALCAP
c) UPPERFIRST
d) none of the above
Question 15
Which of the following can be used to replace a specific set of characters with another set of characters?
a) SUBSTR
b) REPLACE
c) FNDRPLCE
d) none of the above
Question 16
Which of the following is the name assigned to a dummy table in Oracle10g?
a) DUMMY
b) DUM
c) DUAL
d) EMPTY
Contents of the CUSTOMERS table

Question 17
Based on the contents of the CUSTOMERS table, which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?
a) SELECT customer# FROM customers WHERE referred =
(SELECT referred FROM customers WHERE customer# = 1003);
b) SELECT customer# FROM customers WHERE referred EXISTS
(SELECT referred FROM customers WHERE customer# = 1003);
c) SELECT customer# FROM customers WHERE NVL(referred, 0) =
(SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
d) SELECT customer# FROM customers WHERE NVL(referred,0) =
(SELECT referred FROM customers WHERE customer# = 1003);
Question 18
Based on the contents of the CUSTOMERS table, what will be the output of the following query:
select customer#||substr(firstname, 1,1)||lastname
from customers
where state in ('WA','NY')
and referred is not null
or lastname like ('_U%')
a) 1001, BONITA, MORALES
b) 1010JLUCAS
c) 1010, JAKE, LUCAS
d) No rows selected
e) None of the above
Question 19
Based on the contents of the CUSTOMERS table, what will be the output of the following query
select distinct state
from customers
where exists
(select referred
from customers cust2
where customers.state = cust2.state
group by state
having count(referred) 1)
a) 3
b) FL, CA, WY, TX
c) CA
d) FL
e) None of the above

Question 20
Based on the contents of the CUSTOMERS table, what will be the output of the first row of the following query
select cust1.lastname || ' was referred by ' || cust2.lastname
from customers cust1, customers cust2
where cust1.referred = cust2.customer#
order by cust1.lastname asc
a) DAUM was referred by LUCAS
b) GIANA was referred by SMITH
c) NGUYEN was referred by CRUZ
d) SMITH was referred by SMITH
e) None of the above
End of Part A
PART B - (65 marks)
ANSWER THIS SECTION IN YOUR EXAMINATION ANSWER BOOKLET
This question has three parts. Use the table and / or the ERD below to answer all parts.
Contents of the ORDERS table:

JUST LEE BOOKS ERD

Question 1 (30 marks)
Write SQL queries that meet the following specifications. Do not show output. All questions are worth three marks.
a) Display the order number and customer number for all orders. Sort by order number within customer number.
b) Display full details for all rows where the shipping street contains the string MAIN.
c) Display the title for each book and the profit margin for that book (profit = retail – cost). Your query should produce output in the following format. The book title earned a profit of profit , for example: The book Easy SQL earned a profit of $30.
d) Display full details for all books that cost less than thirty dollars and are in the FITNESS category OR cost more than thirty dollars and are in the SELF HELP category.
e) Display the customer id and the average length of time elapsed between an order being placed and the actual shipment of that order (shipment time) for all orders placed by that customer. Display the average length of time in months rounded to the nearest month.
f) Display complete details for the order/s with the earliest shipdate.
g) Using a set operator, display the customer number for all customers who placed an order during March, 2002 and who were referred by another customer. The column called referred may contain null values.
h) For each order item, display the order#, customer last name, item# and quantity.
i) Using a set operator, display the order number for all orders that have no items.
j) Display the order number and total quantity ordered for all orders that have a total quantity greater than 120.

Question 2 (50 marks)
Write ONE PL/SQL program (anonymous block) that provides a report of all books published by specific publishers that have sold more than one thousand copies. The report should insert into an existing table the publisher name and the total number of copies of all books sold for that publisher (subject to specific business rules). The detailed specification is as follows:
• Using an explicit cursor, retrieve all books from the books table that were published by publishers 2, 6 or 7.
• Books with a retail price greater than $1 should be selected for further processing. This operation must be done using the IF statement.
• Using a second explicit cursor (with appropriate parameters), count the number of copies that the book has sold. Store the total count in a local variable named BOOKCOUNT and the total count of all books for a particular publisher in PUBCOUNT.
• If the book has sold one thousand or less copies, move to the next book. If no book has sold one thousand or more copies, exit the program and display the following message: There are no high sales books for these publishers.
• If a book has sold one thousand copies or more, retrieve details about the publisher. Use an explicit cursor.
• Into a table named HIGHCOUNT, insert the publisher name and the total count of all books sold for that publisher. This table has three columns as follows: publisher name, pubcount and rundate.
• Create an exception handler which fires when no rows are found. The exception handler should output the following message to the screen: No rows found.
Important Notes:
• You must use ONLY explicit cursors.
• The program must use ONE block only. Do NOT write a block to perform each task.
• Do not use any implicit cursors, table joins, subqueries, set operators, grouping functions or SQL functions (e.g. SUM).
PART C - (15 marks)
ANSWER THIS SECTION IN YOUR EXAMINATION ANSWER BOOKLET
Part C
You are required to answer two (2) questions.
This section is worth a total of fifteen (15) marks.
Please record your answers in the examination answer booklet provided.
Question 1 (5 Marks)
Describe the purpures of the SGA and what role it plays in Oracle RDBMS system. (approximately 2 paragraphs)
Question 2 (10 Marks)
As the data kept in a RDBMS is property of the organisation the Database Administrator must protect and maintain the database system. Therefore, they are the main drivers in how the data I used and who has access to it.
Discuss this view of the database systems and the responsibility of the database administrator and problems/befits to the organisation by enforcing this perception. (About one page)