Sem-2 Practical Practice Question3
Create following tables and apply appropriate constraints.
Student (Rno, Studname, DOB)
Subject (Sub code, Sub name)
Result (Rno, Sub_ code, Marks)
Create above tables with following constraints:
1. Declare primary key and foreign keys in corresponding tables
2. Marks must be between 0 and 100
Insert 5 records in each table.
SOLUTION.
✅ SQL Script to Create Tables and Apply Constraints.
-- Create Student table
CREATE TABLE Student (
Rno INT PRIMARY KEY,
Studname VARCHAR(50) NOT NULL,
DOB DATE NOT NULL
);
-- Create Subject table
CREATE TABLE Subject (
Sub_code VARCHAR(10) PRIMARY KEY,
Sub_name VARCHAR(50) NOT NULL
);
-- Create Result table
CREATE TABLE Result (
Rno INT,
Sub_code VARCHAR(10),
Marks INT CHECK (Marks BETWEEN 0 AND 100),
PRIMARY KEY (Rno, Sub_code),
FOREIGN KEY (Rno) REFERENCES Student(Rno),
FOREIGN KEY (Sub_code) REFERENCES Subject(Sub_code)
);
OUTPUT :-
✅ Insert Sample Records.
-- Insert 5 records into Student table
INSERT INTO Student (Rno, Studname, DOB) VALUES(1, 'Alice', '10-APR-2022');
INSERT INTO Student (Rno, Studname, DOB) VALUES(2, 'Bob', '06-MAR-2023');
INSERT INTO Student (Rno, Studname, DOB) VALUES(3, 'Charlie', '10-Feb-2024');
INSERT INTO Student (Rno, Studname, DOB) VALUES(4, 'Diana', '12-Nov-2021');
INSERT INTO Student (Rno, Studname, DOB) VALUES(5, 'Ethan', '09-Dec-2022');
-- Insert 5 records into Subject table
INSERT INTO Subject (Sub_code, Sub_name) VALUES ('S101', 'Mathematics');
INSERT INTO Subject (Sub_code, Sub_name)VALUES ('S102', 'Computer Science');
INSERT INTO Subject (Sub_code, Sub_name)VALUES ('S103', 'Physics');
INSERT INTO Subject (Sub_code, Sub_name)VALUES ('S104', 'English');
INSERT INTO Subject (Sub_code, Sub_name)VALUES ('S105', 'Chemistry');
-- Insert 5 records into Result table
INSERT INTO Result (Rno, Sub_code, Marks) VALUES (1, 'S101', 88);
INSERT INTO Result (Rno, Sub_code, Marks) VALUES (2, 'S102', 76);
INSERT INTO Result (Rno, Sub_code, Marks) VALUES (3, 'S103', 91);
INSERT INTO Result (Rno, Sub_code, Marks) VALUES (4, 'S104', 67);
INSERT INTO Result (Rno, Sub_code, Marks) VALUES (5, 'S105', 82);
Write a PL/SQL code block to calculate and display total marks, percentage and class (Distinction, First class, Second Class, Pass class, Fail) of all students.
DECLARE
CURSOR student_cursor IS
SELECT Rno FROM Student;
v_rno Student.Rno%TYPE;
v_total NUMBER;
v_percentage NUMBER;
v_class VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('Rno | Total Marks | Percentage | Class');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
FOR student_record IN student_cursor LOOP
v_rno := student_record.Rno;
-- Calculate total marks of the student
SELECT SUM(Marks) INTO v_total
FROM Result
WHERE Rno = v_rno;
-- Calculate percentage
v_percentage := (v_total / 500) * 100;
-- Determine class
IF v_percentage >= 70 THEN
v_class := 'Distinction';
ELSIF v_percentage >= 60 THEN
v_class := 'First Class';
ELSIF v_percentage >= 50 THEN
v_class := 'Second Class';
ELSIF v_percentage >= 40 THEN
v_class := 'Pass Class';
ELSE
v_class := 'Fail';
END IF;
-- Display result
DBMS_OUTPUT.PUT_LINE(v_rno || ' | ' || v_total || ' | ' ||
ROUND(v_percentage, 2) || '% | ' || v_class);
END LOOP;
END;
