INF3701 EXAM PACK 2024/2025

lOMoAR cPSD| 47677026

, lOMoAR cPSD| 47677026




INF3707/OCT
INF3707/OCT

UNIVERSITY EXAMINATIONS




OCTOBER/NOVEMBER 2023

INF3707

DATABASE DESIGN AND IMPLEMENTATION




Open Rubric

, lOMoAR cPSD| 47677026




INF3707/OCT




Question 1 59 marks
Write down the correct answer on your answer sheet, for example 1.1 – 1. Marks for each question are provided
in brackets, for example (2 marks). Answer all questions.

1.1. You need to create a table for a banking application. One of the columns in the table
has the following requirements:
• You want a column in the table to store the duration of the credit period.
• The data in the column should be stored in a format such that it can be easily added and
subtracted with date data type without using conversion functions.
• The maximum period of the credit provision in the application is 30 days.
• The interest has to be calculated for the number of days an individual has taken a credit for.

Which data type would you use for such a column in the table? (2 marks)
1. Date
2. Timestamp
3. Interval Year to Month
4. Interval day to second


1.2. Based on the myTable provided below,

myTable:
Name Null? Type
--------- -------- ------
USER# NOT NULL NUMBER(4)
LASTNAME NOT NULL VARCHAR2(10)
FIRSTNAME NOT NULL VARCHAR2(10)

Which of the following SELECT statements display a list of names from myTable? (2 marks)
1. Select user names from myTable;
2. Select names from myTable;
3. Select firstname, lastname from myTable;
4. Select first_name, last_name from myTable;
1.3. Which statement adds a constraint that ensures the Lastname column of myTable always holds a value?
(2 marks)
myTable:
Name Null? Type
------ ----- ------
USER# NUMBER(4)
LASTNAME VARCHAR2(10)
FIRSTNAME VARCHAR2(10)

, lOMoAR cPSD| 47677026




INF3707/OCT
1. Alter table myTable add constraint lastname_nn check user_name
is not null;
2. Alter table myTable modify constraint user_name_nn check
lastname is not null;
3. Alter table myTable modify lastname constraint user_name_nn
not null;
4. Alter table myTable modify last_name constraint user_name_nn
is not null;




1.4. Examine the CUSTOMER table created below:

Create table
customer( customer# number
primary key, first_name
varchar2(25), last_name
varchar2(25));
Which one of the following insert statements is valid? (2 marks)

1. Insert into customer values (null, john, ‘smith’);
2. Insert into customer values (first_name, last_name) values
(‘john’, ‘smith’);
3. Insert into customer (first_name, last_name, customer#) values
(1000, ‘john’,’smith’);
4. Insert into customer values (1000, ‘john’, ‘smith’);

1.5. Examine the BOOKS table of the JustLee Books database below:




You want to display 5 percent of the books with the highest retail price in the Books table of the JustLee
Books database. Which query will generate the required result? (2 marks)

1. Select isbn, title, retail from books order by retail fetch 5
percent rows only;
2. select isbn, title, retail from books order by retail desc fetch
first 5 percent rows only;
3. select isbn, title, retail from books order by retail desc fetch
first 5 percent rows only with ties;
4. select isbn, title, retail from books order by retail desc fetch
5 percent rows only;