1. SET - SET command changes the system variables affecting the report environment

2. Commit

        Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions.

3. What is ROWID?

ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

4. Read the following code :

10. CREATE OR REPLACE PROCEDURE find_cpt
11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
12. IS
13. BEGIN
14. IF v_cost_per_ticket > 8.5 THEN
15. SELECT cost_per_ticket
16. INTO v_cost_per_ticket
17. FROM gross_receipt
18. WHERE movie_id = v_movie_id;
19. END IF;
20. END;

Which mode should be used for V_COST_PER_TICKET?

• IN        
• OUT
• RETURN
• IN OUT

5. What is an integrity constraint?

Integrity constraint is a rule that restricts values to a column in a table.

6. SPOOL - SPOOL command creates a print file of the report.

7. Character Functions

        Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.

8. Sequences

       Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

9. What is the fastest way of accessing a row in a table ?

Using ROWID
CONSTRAINTS

10. Examine this database trigger

52. CREATE OR REPLACE TRIGGER prevent_gross_modification
53. {additional trigger information}
54. BEGIN
55. IF TO_CHAR(sysdate, DY) = MON
56. THEN
57. RAISE_APPLICATION_ERROR(-20000,Gross receipts cannot be deleted on Monday);
58. END IF;
59. END;

This trigger must fire before each DELETE of the GROSS_RECEIPT table. It should fire only once for the entire DELETE statement. What additional information must you add?

•  BEFORE DELETE ON gross_receipt
•  AFTER DELETE ON gross_receipt
•  BEFORE (gross_receipt DELETE)
•  FOR EACH ROW DELETED FROM gross_receipt