DEV Community

Cover image for ORACLE-LIVE SQL
SRIRAM PG
SRIRAM PG

Posted on

ORACLE-LIVE SQL


-- Drop old tables if already exist
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Enrollments_KPR';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Courses_KPR';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Students_KPR';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-- Create Students table
CREATE TABLE Students_KPR (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(50),
Dept VARCHAR2(30),
Age NUMBER
);

-- Create Courses table
CREATE TABLE Courses_KPR (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50),
Credits NUMBER
);

-- Create Enrollments table
CREATE TABLE Enrollments_KPR (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER,
CourseID NUMBER,
Grade CHAR(1),
FOREIGN KEY (StudentID) REFERENCES Students_KPR(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses_KPR(CourseID)
);

-- Insert sample students
INSERT INTO Students_KPR VALUES (114, 'Ram', 'CSE', 19);
INSERT INTO Students_KPR VALUES (116, 'Sri', 'IT', 18);

-- Insert sample courses
INSERT INTO Courses_KPR VALUES (201, 'DBMS', 4);
INSERT INTO Courses_KPR VALUES (202, 'Networking', 3);
INSERT INTO Courses_KPR VALUES (203, 'Python', 5);

-- Insert enrollments
INSERT INTO Enrollments_KPR VALUES (301, 114, 201, 'A');
INSERT INTO Enrollments_KPR VALUES (302, 116, 202, 'B');
INSERT INTO Enrollments_KPR VALUES (303, 114, 203, 'A');

Top comments (0)