 |
DPG621S - DATABASE PROGRAMMING - 2ND OPP - JAN 2024 |
|
|
1 Page 1 |
▲back to top |
nAmlBIA UnlVERSITY
OF SCIEnCE Ano TECHnOLOGY
FACULTY OF COMPUTING AND INFORMATICS
SCHOOL OF COMPUTING
DEPARTMENTOF SOFTWAREENGINEERING
QUALIFICATIONS:BACHELOROF COMPUTERSCIENCE;BACHELOROF INFORMATICS;
BACHELOROF GEOINFORMATIONTECHNOLOGY
QUALIFICATIONCODES: 07BCMS; 07BACS
07BAIT; 07BAIF; 07GITB
LEVEL: 6
COURSECODE:DPG621S
COURSENAME: DATABASEPROGRAMMING
SESSION:JANUARY2024
PAPER: {PAPER1)
DURATION: 3 HOURS
MARKS: 100
SECONDOPPORTUNITY/SUPPLEMENTARYEXAMINATION QUESTION PAPER
EXAMINER:
DR GEREONKOCHKAPUIRE
MODERATOR:
MS ROSETHA KAYS
INSTRUCTIONS
1. Answer ALL the questions.
2. Read all the questions carefully before answering.
3. Number the answers clearly
THIS QUESTION PAPERCONSISTSOF 5 PAGES(Including this front page)
|
|
2 Page 2 |
▲back to top |
Section A - Theory (40 Marks)
Question 1
Choose True or False
[10 Marks]
No
Questions
True (T)
1 The IF statement in SQL Server can be used to control the flow
of execution within a batch or stored procedure.
2
A stored procedure in SQL Server can return multiple result
sets.
3
A stored procedure can have both input and output
parameters.
4
A stored procedure can be used to encapsulate a series of SQL
statements to perform a specific task.
5 Triggers in SQL Server can be defined to execute automatically
in response to specific events, such as INSERT, UPDATE, or
DELETEstatements.
6
The parameters in a SQL Server stored procedure must be of
the same data type as the columns they reference in the
database.
7
SQL Server supports a TRY...GRAP block for structured
exception handling.
8 The ERRORstatement in SQL Server is used to explicitly raise
an exception.
9 The FETCHstatement in SQL Server is used to retrieve the next
row from the result set of a cursor.
10 The DECLARECURSORstatement in SQL Server is used to
define the characteristics of a cursor.
False (F)
Question 2
[5 Marks]
Compare Input and Output parameters in stored procedures. Provide an example for each.
Question 3
[4 Marks]
Compare the usage of stored procedures and triggers in SQL Server. When would you prefer
one over the other?
Question 4
[4 Marks]
Compare FORINSERT,FORUPDATE, and FOR DELETEtriggers in SQL Server. When would
you use each type?
Question 5
[4 Marks]
Compare local variables and parameters in terms of scope and usage within stored
procedures.
2
|
|
3 Page 3 |
▲back to top |
Question 6
[4 Marks]
Compare the use of a cursor and a regular SELECTstatement for iterating over a result set.
Question 7
[4 Marks]
Compare the FETCHNEXTstatement and the OPENstatement in the context of cursors.
Question 8
Compare implicit and explicit transactions in SQLServer.
[5 Marks]
Section B - Practical (60 Marks)
(NOTE: Use SQL Server Management System (SSMS) on your computer for this section)
Instructions:Copy and Paste your final code onto notepad and save it on the exam drive (z).
The drive is the drive with your student number.
Question 1
[4 Marks]
Scenario 1: Basic Parameterized Stored Procedure
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(S0),
Salary INT,
JobTitle VARCHAR(50)
);
INSERT INTO Employees (EmployeeID, FirstName,
VALUES
(1, 'John', 'Doe', 50000, 'Engineer'),
(2, 'Jane',
'Smith',
60000, 'Manager'),
(3, 'Bob', 'Johnson',
75000, 'Developer');
LastName, Salary, JobTitle)
Please create a stored procedure that utilizes the "Employees" table, along with the
provided sample values, to retrieve employees whose salary exceeds a specified amount.
Question 2
[5 Marks]
Scenario 2: Using IF Statement in Stored Procedure
Modify the stored procedure created in Question 1 to include a condition that filters
employees with a specific job title.
Question 3
[2 Marks]
Scenario 3: Handling Default Parameter Values
Modify the stored procedure from Question 2 to make the job title parameter optional,
assigning it a default value.
3
|
|
4 Page 4 |
▲back to top |
Question 4
[2 Marks]
Write a statement to remove a stored procedure in SQLServer.
Question 5
[8 Marks]
Scenario 5: Data Retrieval with Filtering
CREATE TABLE Staff (
StaffID INT PRIMARY KEY,
FirstName VARCHAR(SO),
LastName VARCHAR(50),
DepartmentID INT,
Department VARCHAR(50),
JobTitle NVARCHAR(SO)
);
INSERT INTO Staff (StaffID,
FirstName, LastName, DepartmentID,
Department,
JobTitle)
VALUES
(1, 'John', 'Doe', 1, 'HR', 'Manager'),
(2, 'Jane',
'Smith',
2, 'IT', 'Developer'),
( 3, 'Bob', 'Johnson',
1, 'HR', 'Analyst' ) ;
To obtain employee information from a database, utilizing the staff table, you must create a
stored procedure designed to retrieve data based on specified criteria like department and
job title. How would you structure such a stored procedure?
Question 6
[4 Marks]
Scenario 6: Automated Data Cleanup
CREATE TABLE Student (
StudentNo INT PRIMARY KEY,
RegistrationDate
DATE,
-- Add other columns as needed
);
You want to automaticallydelete recordsolder than a certain date from the table
"Student". How would you designa stored procedure to perform this task?
Question 7
[10 Marks]
Scenario 7: Looping Through a Result Set
CREATE TABLE YourTable (
ID INT PRIMARY KEY,
YourColumn INT
);
INSERT INTO YourTable (ID, YourColumn)
VALUES
( 1, 10),
( 2, 20),
(3, 30) t
( 4, 4 0) ;
Using the "YourTable" table, create a stored procedure tasked with looping through a result
set, executing a specified operation on each row. This could involve updating a column
through a calculated process. Could you outline the steps for achieving this, with an
4
|
|
5 Page 5 |
▲back to top |
emphasis on employing a cursor for handling this scenario?
Question 8
[10 Marks]
Scenario 8: Exception Handling
You are in the process of creating a stored procedure responsible for inserting data into the
table mentioned in question 6. How do you plan to manage exceptions to guarantee data
integrity? Additionally, demonstrate on your approach to incorporating transactions.
Question 9
[10 Marks]
Scenario 9: Nested IF Statements
How might you employ nested IF statements to categorize employees into various salary
ranges? Develop a procedure using the table illustrated in Question 1 to accomplish this
classification.
Question 10
[5 Marks]
Scenario 10: Basic Transaction
You have a stored procedure that needs to perform multiple updates, and you want to
ensure that either all updates succeed or none of them do. How would you use a
transaction to achieve this? Use the table indicated in Question 7.
«<<<<End of Exam Paper>>>>>
5