*1.
Student Management System using SQL & PL/SQL*
*Theory:*
A Database Management System (DBMS) is software that allows users to define, create, maintain, and
control access to databases. In relational databases, data is stored in tables consisting of rows and
columns. Each row is called a tuple and each column an attribute.
*Key Concepts:*
1. *Keys*: Primary Key uniquely identifies each record. Foreign Key establishes relationship between two
tables and maintains referential integrity.
2. *Normalization*: Process of organizing data to reduce redundancy. Common normal forms are 1NF,
2NF, 3NF, and BCNF.
3. *SQL Commands*:
- DDL (Data Definition Language): `CREATE`, `ALTER`, `DROP` – defines structure.
- DML (Data Manipulation Language): `INSERT`, `UPDATE`, `DELETE`, `SELECT` – manipulates data.
- DCL (Data Control Language): `GRANT`, `REVOKE` – controls access.
4. *PL/SQL*: Procedural Language extension for SQL used in Oracle. It adds programming constructs like
loops, conditions, and exception handling. A PL/SQL block has `DECLARE`, `BEGIN`, `EXCEPTION`, `END`
sections.
5. *Stored Procedure*: A named PL/SQL block stored in the database and called when needed. Improves
performance and reusability.
6. *Trigger*: A special procedure that automatically executes when an event like `INSERT`, `UPDATE`, or
`DELETE` occurs on a table. Used for validation, auditing, or enforcing business rules.
*Importance*: In real-world applications like college ERP, banking, or hospitals, DBMS ensures data
consistency, security, and quick retrieval. For a Student Management System, SQL helps store student
details, marks, and attendance, while PL/SQL automates grade calculation and validation checks. Triggers
prevent invalid data like marks >100 from being entered.
-- -- DDL: Create Tables
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Course VARCHAR(20),
Sem INT
);
CREATE TABLE Marks (
RollNo INT,
Subject VARCHAR(30),
Marks INT CHECK (Marks BETWEEN 0 AND 100),
FOREIGN KEY (RollNo) REFERENCES Students(RollNo) ON DELETE CASCADE
);
-- DML: Insert Data
INSERT INTO Students VALUES (101, 'Amit Kumar', 'BCA', 5);
INSERT INTO Students VALUES (102, 'Neha Singh', 'BCA', 5);
INSERT INTO Students VALUES (103, 'Ravi Sharma', 'BCA', 5);
INSERT INTO Marks VALUES (101, 'DBMS', 85);
INSERT INTO Marks VALUES (101, 'Java', 78);
INSERT INTO Marks VALUES (102, 'DBMS', 92);
INSERT INTO Marks VALUES (102, 'Java', 88);
INSERT INTO Marks VALUES (103, 'DBMS', 35);
INSERT INTO Marks VALUES (103, 'Java', 32);
-- Queries
-- 1. Topper
SELECT [Link], SUM([Link]) AS Total
FROM Students s JOIN Marks m ON [Link] = [Link]
GROUP BY [Link], [Link] ORDER BY Total DESC LIMIT 1;
-- 2. Failed in more than 2 subjects
SELECT [Link] FROM Students s
WHERE [Link] IN (
SELECT RollNo FROM Marks WHERE Marks < 40
GROUP BY RollNo HAVING COUNT(*) > 2
);
-- PL/SQL: Procedure to display grade
CREATE OR REPLACE PROCEDURE GetGrade(rno IN INT) IS
total_marks INT;
grade CHAR(1);
BEGIN
SELECT SUM(Marks) INTO total_marks FROM Marks WHERE RollNo = rno;
IF total_marks >= 180 THEN grade := 'A';
ELSIF total_marks >= 150 THEN grade := 'B';
ELSIF total_marks >= 120 THEN grade := 'C';
ELSE grade := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('RollNo ' || rno || ' Grade: ' || grade);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No marks found for RollNo ' || rno);
END;
/
-- Trigger: Prevent marks > 100
CREATE OR REPLACE TRIGGER trg_check_marks
BEFORE INSERT OR UPDATE ON Marks
FOR EACH ROW
BEGIN
IF :[Link] > 100 OR :[Link] < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Marks must be between 0 and 100');
END IF;
END;
/