 |
DPG621S - DATABASE PROGRAMMING - 1st OPP - NOV 2025 |
|
|
1 Page 1 |
▲back to top |
nAmlBIA unlVERSITY
OF SC IEnCE Ano TECHn □ LOGY
FACULTY OF COM PUTING AN D IN FORM ATICS
DEPARTM ENT OF SOFTWARE ENGINEERING
QUALI FICATION: BACHELOR OF GEOINFORMATION TECHNOLOGY; BACHELOR OF COMPUTER
SCIENCE; BACHELOR OF INFORMATICS
QUALIFICATION CODE: 07BGEI; 07BCMS;
07BAIT
LEVEL: 6
COURSE CODE: DPG621S
COURSE NAME: DATABASE PROGRAMMING
SESSION: OCTOBER 2025
DURATION: 3 HOURS
PAPER: SESSION 1
MARKS: 100
FIRST OPPORTUNITY EXAM INATION QUESTION PAPER
EXAM I N ERS:
DR GEREON KOCH KAPUIRE, MS MINIKUEE KASAON A, MR ANDREW HITJITEVI
TJIRARE, MR NYASHA HARMONY MUSIYARIRA
M ODERATOR: MS ROSETHA KAYS
INSTRUCTIONS TO STUDENTS
1. Answer ALL the questions.
2. Read all the questions carefully before answering.
3. Number the answers clearly
THIS QUESTION PAPER CONSISTS OF 4 PAGES (Including this front page)
|
|
2 Page 2 |
▲back to top |
Section 1
True/False Questions
(10 Marks, 1 mark each)
1. A stored procedure in SQL Server can accept input parameters to make it reusable.
2. A trigger can be fired manually by executing it with an EXEC statement.
3. You can use a WHILE loop inside a stored procedure to repeatedly execute
statements.
4. The TRY...CATCH block in SQL Server allows you to handle errors in procedures or
triggers.
5. BEGIN TRANSACTION must always be followed by a COMMIT or ROLLBACK to
complete the transaction.
6. A procedure with parameters cannot contain variables.
7. An AFTER trigger executes before the DML statement modifies the table.
8. IF...ELSE constructs in a procedure allow conditional execution of SQL statements.
9. ROLLBACK inside a transaction will undo all changes made since the last COMMIT.
10. A stored procedure can return multiple result sets from SELECT statements.
Section 2 - Practical (90 M arks)
Use SQL Server Management Studio (SSMS) on your computer to complete this section .
Instructions:
Copy and paste your final code into the designated space for each question on eLearning.
Ensure you also keep a copy of your work in Notepad as a backup in case of any technical issues.
Use the scenario and table provided below to answer all the questions.
Scenario:
A Classroom Management System helps teachers ensure lessons run smoothly and track
students' behavior. You are required to use stored procedures in SQL Server to manage,
analyze, and report data for this system.
CREATE TABLE STUDENT BEHAVIOR (
Student ID INT PRIMARY KEY,
FirstName VARCHAR(S0),
LastNarne VARCHAR(S0),
GradeLevel VARCHAR(l0),
BehaviorStatus VARCHAR(20),
DateReported DATE
);
2
|
|
3 Page 3 |
▲back to top |
Question 1
(3 Marks)
Create a procedure insert_sample_students to insert two sample records into
STU DENT_BEHAVIOR.
Question 2
(7 Marks)
Create a procedure add_student_behavior using variables to insert a new record.
Question 3
(7 Marks]
Create a procedure add_student_behavior_param using parameters. Invoke the procedure.
Question 4
Create a procedure show_all_students that retrieves all student records.
(2 Marks]
Question 5
(6 Marks)
Create a procedure find_student_by_status that takes a status as a parameter and retrieves
matching students. Invoke the procedure.
Question 6
[S Marks)
Create a procedure update_behavior_status that updates a student's behavior based on ID.
Invoke the procedure.
Question 7
(9 Marks)
Create a procedure conditional_update that prints a message depending on the student's
current status.
Question 8
Create a procedure remove_student to delete a student by ID.
(3 Marks)
Question 9
(3 Marks]
Create a procedure count_by_status that counts how many students have each behavior status.
Question 10
(4 Marks)
Create a procedure get_recent_reports to display students reported after a given date. Invoke
the procedure.
Question 11
(4 Marks)
Create a procedure transaction_behavior_update that updates a record inside a transaction and
rolls back.
Question 12
(4 Marks)
Create a procedure display_top_student that prints the name of the student with the smallest
Student_lD.
Question 13
(7 Marks]
Create a procedure cursor_display_students that prints all student names using a cursor. Invoke
the procedure.
3
|
|
4 Page 4 |
▲back to top |
..
Question 14
[4 Marks]
Create a procedure find_students_by_grade that accepts a grade level as input. Invoke the
procedure.
Question 15
Create a procedure delete_all_disruptive that deletes all disruptive students.
[3 Marks]
Question 16
[4 Marks]
Create a procedure display_summary that prints total stud ents and average grade level
(numeric).
Question 17
[2 Marks]
Create a procedure reset_status_all to update all statuses to 'Pending Review'.
Question 18
Create a procedure clear_table to delete all rows from the table.
[2 Marks]
Question 19
[4 Marks]
Create a trigger notify_behavior_insert that fires after insertion into STUDENT_BEHAVIOR and
prints "New student behavior recorded." Test the trigger.
Question 20
[7 Marks]
Create a procedure safe_add_student that inserts a student inside a TRY...CATCH block. Print
success or error message. Invoke the procedure.
«End of Examination Question Paper»
4