NATIONAL OPEN UNIVERSITY OF NIGERIA

UNIVERSITY VILLAGE, PLOT 91, CASDASTRAL ZONE, NNAMDI AZIKIWE EXPRESS WAY, JABI, ABUJA.
FACULTY OF SCIENCES
JANUARY 2021 EXAMINATION

DAM401: ORACLE DATABASE  Credit: 2 units
TIME ALLOWED: 2 Hours
INSTRUCTION: Answer Question 1 and any other THREE (3) Questions

1a) Define DML. Give five examples of DML commands(4 ½ marks)
1b) Discuss the various data dictionary views you are familiar with (7 ½ marks)
1c) Write short notes on the following:
i. User-defined data type (3 marks)
ii. Index (3 marks)
iii. Cluster (3 marks)
1d) (i) What is deadlock? (2 marks)
(ii) Explain how Oracle handles deadlock situations (2 marks)

2a) Briefly describe the following stating examples where necessary:
i) DML locks (datalocks) (2 marks)
ii) DDL locks (dictionary locks) (2 marks)
iii) Internal locks and latches (2 marks)
2b)Extensively discuss the operations of Share Row Exclusive Table Locks (SRX)(6 marks)
2c) ) State the three common uses of Triggers (3 marks)

3a) Produce the SQL commands that accomplish the following tasks:
i. Completely remove a table named tbl (1 mark)
ii. Change the structure of a table named tbl by adding a column named col2(1 mark)
iii. Amend the content of table tbl by changing a record with ID=123. Set a new value of field fld as fld*2 (1 mark)
iv. Query table tbl for records with field values fld > 10(1 mark)
v. Create a view named vw1 from record of a table tbl with field fld =20(1 mark)
vi. Display all the content of table tbl (1 mark)

3b)Outline any six data types supported by Oracle database (3 marks)
3c) The execution of a SQL statement goes through a number of stages. Discuss (6 marks)

4a) Explain the task performed by each of the following SQL codes:

i. DELETE FROM emp WHERE empno=0001;(2 marks)

ii. INSERT INTO customers VALUES(1,'John'); (2 marks)

iii. DELETE FROM emp WHERE last_name='Don';(2 marks)

iv. SELECT first_name, last_name FROM emp (2 marks)

4b)Explain the file formats/types generated by Data Pumps (7 marks)

5a)Discuss how SQL Loader can be used to upload data into Oracle databases (8 marks)

5b) Explain the following PL/SQL code: 1 CREATE PACKAGE numb 2 AS 3 FUNCTION odd_even (v1 NUMBER) RETURN VARCHAR2; 4 PROCEDURE ins_ints (v1 in NUMBER); 5 END num; 6/ (7 marks)