Name : Harsh A Patel
Enrollment : SR23BSCS006
Div :SY CS_B
Subject : ADBMS
Example 1:
TABLE CREATION:
CREATE TABLE Libraries (
LibraryID NUMBER PRIMARY KEY,
LibraryName VARCHAR2(100),
Location VARCHAR2(100)
);
INSERT INTO Libraries (LibraryID, LibraryName, Location)
VALUES (1, 'Central Library', 'Downtown');
INSERT INTO Libraries (LibraryID, LibraryName, Location)
VALUES (2, 'Westside Branch', 'Westside');
INSERT INTO Libraries (LibraryID, LibraryName, Location)
VALUES (3, 'East End Library', 'East End');
CREATE TABLE Books (
BookID NUMBER PRIMARY KEY,
Title VARCHAR2(200),
Author VARCHAR2(100),
Genre VARCHAR2(50)
);
INSERT INTO Books (BookID, Title, Author, Genre) VALUES
(101, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction');
INSERT INTO Books (BookID, Title, Author, Genre) VALUES
(102, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction');
INSERT INTO Books (BookID, Title, Author, Genre) VALUES
(103, '1984', 'George Orwell', 'Dystopian');
INSERT INTO Books (BookID, Title, Author, Genre) VALUES
(104, 'Moby-Dick', 'Herman Melville', 'Adventure');
INSERT INTO Books (BookID, Title, Author, Genre) VALUES
(105, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction');
CREATE TABLE BorrowRecords (
BorrowID NUMBER PRIMARY KEY,
LibraryID REFERENCES Libraries(LibraryID),
BookID REFERENCES Books(BookID),
BorrowDate DATE,
ReturnDate DATE
);
INSERT INTO BorrowRecords VALUES (1001,1,
101,'08/01/2024', '08/15/2024');
INSERT INTO BorrowRecords VALUES
(1002,1,103,'08/05/2024', '08/20/2024');
INSERT INTO BorrowRecords VALUES (1003,2,
102,'08/10/2024', '08/25/2024');
INSERT INTO BorrowRecords VALUES (1004,3,
104,'08/12/2024', '08/22/2024');
INSERT INTO BorrowRecords VALUES (1005,2, 105,'
08/15/2024', '08/25/2024');
PL/SQL BLOCK:
DECLARE
CURSOR outercursor IS
SELECT LibraryID,LibraryName
FROM Libraries;
lib_id Libraries.LibraryID%TYPE;
lib_name Libraries.LibraryName%TYPE;
CURSOR innercursor IS
SELECT b.Title,br.BookID
FROM Books b
JOIN BorrowRecords br
ON b.BookID=br.BookID
WHERE br.LibraryID=lib_id;
b_title Books.Title%TYPE;
b_id BorrowRecords.BookID%TYPE;
BEGIN
OPEN outercursor;
LOOP
FETCH outercursor INTO lib_id,lib_name;
EXIT WHEN outercursor%NOTFOUND;
dbms_output.put_line('Library Name: '||lib_name);
OPEN innercursor;
LOOP
FETCH innercursor INTO b_title,b_id;
EXIT WHEN innercursor%NOTFOUND;
dbms_output.put_line('Book Title: '||b_title||', Book ID:
'||b_id);
END LOOP;
CLOSE innercursor;
dbms_output.put_line(' ');
END LOOP;
CLOSE outercursor;
END;
/
OUTPUT:
Library Name: Central Library
Book Title: The Great Gatsby, Book ID: 101
Book Title: 1984, Book ID: 103
Library Name: East End Library
Book Title: Moby-Dick, Book ID: 104
Library Name: Westside Branch
Book Title: The Catcher in the Rye, Book ID: 105
Book Title: To Kill a Mockingbird, Book ID: 102
Statement processed.
Example 2:
TABLE CREATION :
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(100)
);
INSERT INTO Students (StudentID, StudentName) VALUES
(1, 'John Doe');
INSERT INTO Students (StudentID, StudentName) VALUES
(2, 'Jane Smith');
INSERT INTO Students (StudentID, StudentName) VALUES
(3, 'Alice Johnson');
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(100),
StudentID REFERENCES Students(StudentID)
);
INSERT INTO Courses (CourseID, CourseName, StudentID)
VALUES (101, 'Mathematics', 1);
INSERT INTO Courses (CourseID, CourseName, StudentID)
VALUES (102, 'Physics', 1);
INSERT INTO Courses (CourseID, CourseName, StudentID)
VALUES (103, 'Chemistry', 2);
INSERT INTO Courses (CourseID, CourseName, StudentID)
VALUES (104, 'Biology', 3);
INSERT INTO Courses (CourseID, CourseName, StudentID)
VALUES (105, 'History', 3);
PL/SQL BLOCK:
DECLARE
CURSOR student_cursor IS
SELECT StudentID, StudentName FROM Students;
v_StudentID Students.StudentID%TYPE;
v_StudentName Students.StudentName%TYPE;
CURSOR course_cursor IS
SELECT CourseID, CourseName
FROM Courses
WHERE StudentID = v_StudentID;
v_CourseID Courses.CourseID%TYPE;
v_CourseName Courses.CourseName%TYPE;
BEGIN
OPEN student_cursor;
LOOP
FETCH student_cursor INTO v_StudentID, v_StudentName;
EXIT WHEN student_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Student: ' || v_StudentName);
OPEN course_cursor;
LOOP
FETCH course_cursor INTO v_CourseID, v_CourseName;
EXIT WHEN course_cursor%NOTFOUND;
IF v_CourseName = 'Physics' THEN
UPDATE Courses
SET CourseName = 'Advanced Physics'
WHERE CourseID = v_CourseID;
DBMS_OUTPUT.PUT_LINE(' Course ' ||
v_CourseName || ' updated to Advanced Physics');
ELSE
DBMS_OUTPUT.PUT_LINE(' Course: ' ||
v_CourseName);
END IF;
END LOOP;
CLOSE course_cursor;
END LOOP;
CLOSE student_cursor;
END;
/
OUTPUT:
Student: Alice Johnson
Course: Biology
Course: History
Student: John Doe
Course: Mathematics
Course Physics updated to Advanced Physics
Student: Jane Smith
Course: Chemistry
1 row(s) updated.
