Preparatory Lab -1 for SAD

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
2. Database Design
3. DDL – Data Definition Language
4. DML – Data Manipulation Language
5. SQL Operators
6. SQL JOIN Operations
7. SQL Functions
8. Stored Procedures
9. User-Defined Functions
10. Database Triggers
11. Automatic Fee Calculation
12. Hands-on Challenges

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

1. Display all CSE students.
2. Find students whose CGPA is greater than 3.50.
3. Display students in descending order of CGPA.
4. Count the total number of students in each department.

Challenge 2: Course Registration

1. Display all students with their registered courses.
2. Find the number of students registered in each course.
3. Display students who have not registered for any course.
4. Calculate the total registered credits for each student.

Challenge 3: PL/SQL Programming

1. Create a procedure to update a student's CGPA.
2. Create a function to return the number of registered courses for a student.
3. Create a procedure to display a student's fee information.
4. 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.