Oracle SQL and PL/SQL: Hands-on Workshop for SAD course.
Workshop Overview: This practical workshop introduces Oracle SQL and PL/SQL through a university database management system. Participants will design relational tables, perform SQL operations, retrieve data using JOINs, and implement stored procedures, functions, and triggers.
Duration: Two Lab Sessions (3 Hours Each)
Database: Oracle Database
Learning Approach: Demonstration, hands-on practice, and problem-solving.
Table of Contents
1. Workshop Objectives
After completing this workshop, participants should be able to:
- Create relational database tables using DDL commands.
- Insert, update, delete, and retrieve records using SQL.
- Understand primary keys, foreign keys, and table relationships.
- Write SQL queries using JOINs and aggregate functions.
- Develop stored procedures and user-defined functions.
- Implement triggers for automatic database operations.
- Build a basic university fee management process using PL/SQL.
2. University Database Design
Throughout this workshop, we will develop a simplified university database. It will manage students, courses, course registrations, student fees, and fee payments.
2.1 Database Tables
| Table | Purpose |
|---|---|
| STUDENTS | Stores student information. |
| COURSES | Stores course information. |
| COURSE_REGISTRATIONS | Connects students and courses. |
| STUDENT_FEES | Stores each student's fee summary. |
| FEE_PAYMENTS | Stores individual fee payment transactions. |
2.2 Relationship Diagram
STUDENTS
|
| 1 : Many
|
COURSE_REGISTRATIONS
|
| Many : 1
|
COURSES
STUDENTS 1 -------- 1 STUDENT_FEES
|
| 1 : Many
|
FEE_PAYMENTS
The relationship between STUDENTS and COURSES is Many-to-Many (M:N). A student can register for multiple courses, and each course can have multiple students. The COURSE_REGISTRATIONS table resolves this relationship.
3. DDL – Data Definition Language
DDL commands define and modify database structures. Common commands include CREATE, ALTER, DROP, and TRUNCATE.
3.1 Create the STUDENTS Table
CREATE TABLE students (
student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(100) NOT NULL,
email VARCHAR2(100) UNIQUE,
department VARCHAR2(50),
cgpa NUMBER(3,2),
contact_no VARCHAR2(20),
admission_date DATE
);3.2 Create the COURSES Table
CREATE TABLE courses (
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(100) NOT NULL,
credit NUMBER(3,1) NOT NULL
);3.3 Create the COURSE_REGISTRATIONS Table
CREATE TABLE course_registrations (
registration_id NUMBER PRIMARY KEY,
student_id NUMBER NOT NULL,
course_id NUMBER NOT NULL,
registration_date DATE DEFAULT SYSDATE,
semester VARCHAR2(30) NOT NULL,
CONSTRAINT fk_reg_student
FOREIGN KEY (student_id)
REFERENCES students(student_id),
CONSTRAINT fk_reg_course
FOREIGN KEY (course_id)
REFERENCES courses(course_id),
CONSTRAINT uq_student_course_sem
UNIQUE (student_id, course_id, semester)
);3.4 Create the STUDENT_FEES Table
CREATE TABLE student_fees (
student_id NUMBER PRIMARY KEY,
total_fee NUMBER(12,2) NOT NULL,
paid_amount NUMBER(12,2) DEFAULT 0,
due_amount NUMBER(12,2) NOT NULL,
CONSTRAINT fk_fee_student
FOREIGN KEY (student_id)
REFERENCES students(student_id)
);3.5 Create the FEE_PAYMENTS Table
CREATE TABLE fee_payments (
payment_id NUMBER PRIMARY KEY,
student_id NUMBER NOT NULL,
payment_amount NUMBER(12,2) NOT NULL,
payment_date DATE DEFAULT SYSDATE,
CONSTRAINT fk_payment_student
FOREIGN KEY (student_id)
REFERENCES student_fees(student_id),
CONSTRAINT chk_payment_amount
CHECK (payment_amount > 0)
);
Important: A primary key uniquely identifies each record. A foreign key establishes a relationship between tables. A junction table is used to implement a Many-to-Many relationship.
4. DML – Data Manipulation Language
DML commands allow us to insert, update, and delete records.
4.1 Insert Student Records
INSERT INTO students VALUES
(101, 'Rahim Ahmed', 'rahim@gmail.com',
'CSE', 3.75, '01711111111', DATE '2024-01-15');
INSERT INTO students VALUES
(102, 'Karim Hasan', 'karim@gmail.com',
'CSE', 3.60, '01722222222', DATE '2024-01-15');
INSERT INTO students VALUES
(103, 'Nusrat Jahan', 'nusrat@gmail.com',
'EEE', 3.90, '01733333333', DATE '2024-02-01');4.2 Insert Course Records
INSERT INTO courses VALUES (201, 'Database Systems', 3);
INSERT INTO courses VALUES (202, 'Data Structures', 3);
INSERT INTO courses VALUES (203, 'Software Engineering', 3);
INSERT INTO courses VALUES (204, 'Computer Networks', 3);4.3 Register Students in Courses
INSERT INTO course_registrations
VALUES (1, 101, 201, SYSDATE, 'Spring 2026');
INSERT INTO course_registrations
VALUES (2, 101, 202, SYSDATE, 'Spring 2026');
INSERT INTO course_registrations
VALUES (3, 102, 201, SYSDATE, 'Spring 2026');
INSERT INTO course_registrations
VALUES (4, 103, 203, SYSDATE, 'Spring 2026');4.4 Update a Student Record
UPDATE students
SET cgpa = 3.85
WHERE student_id = 101;4.5 Delete a Student Record
DELETE FROM students
WHERE student_id = 103;Note: This DELETE will fail if the student has related records in other tables, because of the foreign key constraints. Delete dependent records first or design an appropriate deletion policy.
5. SQL Operators
5.1 Comparison Operators
SELECT * FROM students WHERE cgpa > 3.50;
SELECT * FROM students WHERE department = 'CSE';
SELECT * FROM students WHERE cgpa BETWEEN 3.50 AND 4.00;5.2 Logical Operators
SELECT * FROM students
WHERE department = 'CSE'
AND cgpa >= 3.50;
SELECT * FROM students
WHERE department = 'CSE'
OR department = 'EEE';
SELECT * FROM students
WHERE NOT department = 'CSE';5.3 IN, LIKE, and IS NULL
SELECT * FROM students
WHERE department IN ('CSE', 'EEE');
SELECT * FROM students
WHERE student_name LIKE 'R%';
SELECT * FROM students
WHERE email IS NULL;6. SQL JOIN Operations
JOIN operations retrieve related information from multiple tables.
6.1 INNER JOIN
SELECT s.student_name, c.course_name
FROM students s
INNER JOIN course_registrations r
ON s.student_id = r.student_id
INNER JOIN courses c
ON r.course_id = c.course_id;6.2 LEFT JOIN
SELECT s.student_name, r.course_id
FROM students s
LEFT JOIN course_registrations r
ON s.student_id = r.student_id;6.3 Find Students Registered in Database Systems
SELECT s.student_id, s.student_name, c.course_name
FROM students s
JOIN course_registrations r
ON s.student_id = r.student_id
JOIN courses c
ON r.course_id = c.course_id
WHERE c.course_name = 'Database Systems';7. SQL Built-in and Aggregate Functions
7.1 Aggregate Functions
SELECT COUNT(*) AS total_students
FROM students;
SELECT AVG(cgpa) AS average_cgpa
FROM students;
SELECT MAX(cgpa) AS highest_cgpa,
MIN(cgpa) AS lowest_cgpa
FROM students;
SELECT department, COUNT(*) AS total
FROM students
GROUP BY department;7.2 String and Date Functions
SELECT UPPER(student_name)
FROM students;
SELECT LOWER(email)
FROM students;
SELECT LENGTH(student_name)
FROM students;
SELECT SYSDATE FROM dual;
SELECT student_name,
ROUND(cgpa, 1) AS rounded_cgpa
FROM students;8. Stored Procedures
A stored procedure is a named PL/SQL program stored in the database. It can perform operations such as inserting records, validating data, and updating information.
8.1 Procedure to Register a Course
CREATE OR REPLACE PROCEDURE register_course (
p_registration_id IN NUMBER,
p_student_id IN NUMBER,
p_course_id IN NUMBER,
p_semester IN VARCHAR2
)
AS
BEGIN
INSERT INTO course_registrations (
registration_id,
student_id,
course_id,
registration_date,
semester
)
VALUES (
p_registration_id,
p_student_id,
p_course_id,
SYSDATE,
p_semester
);
DBMS_OUTPUT.PUT_LINE('Course registered successfully.');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE(
'Duplicate registration or registration ID.'
);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END;
/8.2 Execute the Procedure
SET SERVEROUTPUT ON;
BEGIN
register_course(5, 102, 202, 'Spring 2026');
END;
/9. User-Defined Functions
A user-defined function returns a value and can be used in SQL statements or PL/SQL programs.
9.1 Calculate Total Registered Credits
CREATE OR REPLACE FUNCTION get_total_credits (
p_student_id IN NUMBER
)
RETURN NUMBER
AS
v_total NUMBER;
BEGIN
SELECT NVL(SUM(c.credit), 0)
INTO v_total
FROM course_registrations r
JOIN courses c
ON r.course_id = c.course_id
WHERE r.student_id = p_student_id;
RETURN v_total;
END;
/9.2 Execute the Function
SELECT get_total_credits(101) AS total_credits
FROM dual;10. Database Triggers
A trigger is a stored PL/SQL program that automatically executes when a specified database event occurs. For example, a trigger can respond to INSERT, UPDATE, or DELETE operations.
10.1 Trigger Events
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- AFTER DELETE
10.2 Example: Automatically Update Fee Summary
Whenever a payment is inserted, updated, or deleted, the trigger will automatically adjust the student's paid amount and due amount.
CREATE OR REPLACE TRIGGER trg_fee_calculation
AFTER INSERT OR UPDATE OR DELETE
ON fee_payments
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE student_fees
SET paid_amount = paid_amount + :NEW.payment_amount,
due_amount = due_amount - :NEW.payment_amount
WHERE student_id = :NEW.student_id;
ELSIF UPDATING THEN
UPDATE student_fees
SET paid_amount = paid_amount
- :OLD.payment_amount
+ :NEW.payment_amount,
due_amount = due_amount
+ :OLD.payment_amount
- :NEW.payment_amount
WHERE student_id = :NEW.student_id;
ELSIF DELETING THEN
UPDATE student_fees
SET paid_amount = paid_amount - :OLD.payment_amount,
due_amount = due_amount + :OLD.payment_amount
WHERE student_id = :OLD.student_id;
END IF;
END;
/11. Complete Fee Management Example
11.1 Initialize a Student's Fee Record
INSERT INTO student_fees
(student_id, total_fee, paid_amount, due_amount)
VALUES (101, 50000, 0, 50000);11.2 Create a Payment Procedure
CREATE OR REPLACE PROCEDURE receive_payment (
p_payment_id IN NUMBER,
p_student_id IN NUMBER,
p_amount IN NUMBER
)
AS
v_due NUMBER;
BEGIN
IF p_amount <= 0 THEN
RAISE_APPLICATION_ERROR(
-20001, 'Payment must be greater than zero.'
);
END IF;
SELECT due_amount
INTO v_due
FROM student_fees
WHERE student_id = p_student_id
FOR UPDATE;
IF p_amount > v_due THEN
RAISE_APPLICATION_ERROR(
-20002, 'Payment exceeds the outstanding due.'
);
END IF;
INSERT INTO fee_payments (
payment_id,
student_id,
payment_amount,
payment_date
)
VALUES (
p_payment_id,
p_student_id,
p_amount,
SYSDATE
);
DBMS_OUTPUT.PUT_LINE('Payment received successfully.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(
-20003, 'Student fee record not found.'
);
END;
/11.3 Test the Fee Management System
SET SERVEROUTPUT ON;
-- First payment: 20,000
BEGIN
receive_payment(1, 101, 20000);
END;
/
-- Second payment: 10,000
BEGIN
receive_payment(2, 101, 10000);
END;
/
-- Update second payment to 15,000
UPDATE fee_payments
SET payment_amount = 15000
WHERE payment_id = 2;
-- Delete first payment
DELETE FROM fee_payments
WHERE payment_id = 1;
-- Check the final fee summary
SELECT * FROM student_fees
WHERE student_id = 101;11.4 Expected Results
| Operation | Paid Amount | Due Amount |
|---|---|---|
| Initial fee | 0 | 50,000 |
| Payment of 20,000 | 20,000 | 30,000 |
| Payment of 10,000 | 30,000 | 20,000 |
| Update second payment to 15,000 | 35,000 | 15,000 |
| Delete first payment | 15,000 | 35,000 |
Important: The trigger updates the fee summary automatically. Do not manually update the paid_amount and due_amount values for these payment operations. The trigger runs within the same transaction as the originating INSERT, UPDATE, or DELETE statement. Avoid COMMIT inside triggers.
12. Hands-on Practice Challenges
Challenge 1: Student Information
- Display all CSE students.
- Find students whose CGPA is greater than 3.50.
- Display students in descending order of CGPA.
- Count the total number of students in each department.
Challenge 2: Course Registration
- Display all students with their registered courses.
- Find the number of students registered in each course.
- Display students who have not registered for any course.
- Calculate the total registered credits for each student.
Challenge 3: PL/SQL Programming
- Create a procedure to update a student's CGPA.
- Create a function to return the number of registered courses for a student.
- Create a procedure to display a student's fee information.
- Test the fee trigger using INSERT, UPDATE, and DELETE.
13. Suggested Lab Session Plan
| Session | Topics | Duration |
|---|---|---|
| Session 1 | Database design, DDL, DML, operators, JOINs, SQL functions | 3 Hours |
| Session 2 | Procedures, functions, triggers, fee management system, challenges | 3 Hours |
Final Workshop Outcome: Participants will develop a practical understanding of Oracle SQL and PL/SQL by implementing a small university database system, including course registration and automatic fee management.