As per the course outline, the final exam is worth 45% of your final grade and you must pass the final exam to pass the course.

The exam is all on paper.  You do nothing on a computer, so you do not have SQLDeveloper or any other tools available to check SQL syntax or test any     queries.

You are permitted TWO (2) letter sized double-sided sheets of reference notes     (a.k.a. cheat sheets).  These can include anything you want, including definitions, diagrams, and snippets of example SQL code.

There are multiple versions of the final exam.  They have the same structure and  mark allocations, but different questions of the same level of difficulty. For any    written SQL code, we are not marking for exact syntax (but you have to be            reasonably close). Some of the Multiple Choice, True/False, and Short Answer /    Completion questions ask about syntax or keywords, so you will have to be exact for those.

The structure of the exam is as follows (100 marks total):

a.   Multiple Choice: 25 questions (1 mark each): Similar in style to what you have seen on the quizzes.  They are all single answer.

b.  True/False: 10 questions (1/2 mark each): Similar in style to what you have seen on the quizzes.

c.   Short Answer / Completion: 15 questions (1 mark each): Similar in style to what you have seen on the quizzes.  Answers are 1 to 4 words, not whole sentences.

d.  Written Answer: 55 marks total, 14 questions, some with multiple parts,  ranging from 1 to 5 marks each: These are very similar to the Review and Exercise questions.  Some require you to explain a concept (e.g. Explain   what NULL means).  Others require a short snippet of SQL code to be      explained (e.g. What does NVL(gpa,0) do?).  There will be a significant    amount of SQL code to be written.  There will be an ERD diagram to be   drawn, so pencil, eraser, ruler, etc. might be a good idea.  None of the       written questions requires an essay type answer.  Also, there are no UML diagrams to be drawn on the exam.

You have 3 hours for the exam, so you should not rush but you will need to        manage your time.  If you draw a blank on a question, leave it and come back to it later.  You should have plenty of time to review your work.  Remember the     guideline of 1 mark per minute of reading / thinking / writing.

The number of marks also indicates how much you should be writing in           response.  For example, 2 marks to explain some concept will need 2 pertinent points in your answer.  For these written answers, point form is fine as long as your meaning is clear single words typically won’t get you much.  Extra things like a diagram or some example to further illustrate your answer might be a     good idea.

If you find you do not understand a question or need to make some assumption, make a note of it on the exam paper.  An incorrect answer will often gain part     marks if you explain why you answered the question that way.

The schema below is used for a number of the SQL and PL/SQL coding                  questions.  It will be included in the exam paper, so no need to place it on your     reference sheets.  You should review it and think of some questions that might be asked, such as

List all products in alphabetic order, where the quantity on hand is zero. List every customer id along with the total cost of all orders they have placed. List all customers missing a phone number.

Create a view of order product information that includes the product price. Modify the order product table so that quantity ordered must be a

positive value.

SQL> DESCRIBE CUSTOMER;

Name

Null?    Type

------------------------------------------- -------- -----------

CUST_ID

FIRST_NAME

LAST_NAME

ADDRESS

CITY

PROV

POSTAL_CODE

PHONE

CREDIT_LIMIT

NOT NULL NUMBER(6)  VARCHAR2(20) VARCHAR2(20) VARCHAR2(40) VARCHAR2(30) CHAR(2)     CHAR(6)     CHAR(10)    NUMBER(9,2)

SQL> DESCRIBE PRODUCT

Name                                       Null?

------------------------------------------ --------

NOT NULL

Null?

Type

-----------

NUMBER(6)   VARCHAR2(40) NUMBER(8)   NUMBER(7,2)

Type

------------------------------------------ -------- -----------