Scribd
Upload
18 views1 page

Oracle SQL Queries With PL SQL

The document provides SQL queries and PL/SQL programs for creating and managing a library database. It includes the creation of tables for authors, libraries, books, copies, borrowers, and loans, along with sample records for each table. Additionally, it contains a SQL query to retrieve libraries that have books written by a specific author.

Uploaded by

choudharyanubhav07
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
18 views1 page

Oracle SQL Queries With PL SQL

The document provides SQL queries and PL/SQL programs for creating and managing a library database. It includes the creation of tables for authors, libraries, books, copies, borrowers, and loans, along with sample records for each table. Additionally, it contains a SQL query to retrieve libraries that have books written by a specific author.

Uploaded by

choudharyanubhav07
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Oracle-Compatible SQL Queries + PL/SQL Program

-- 1. Creating Tables with Oracle-compatible syntax

CREATE TABLE Author (


authno INT PRIMARY KEY,
authname VARCHAR2(50)
);

CREATE TABLE Library (


libno INT PRIMARY KEY,
libname VARCHAR2(50),
location VARCHAR2(50),
rooms INT
);

CREATE TABLE Book (


bookno INT PRIMARY KEY,
title VARCHAR2(100),
pages INT,
authno_fk INT REFERENCES Author(authno)
);

CREATE TABLE Copy (


copyno INT PRIMARY KEY,
libno_fk INT REFERENCES Library(libno),
bookno_fk INT REFERENCES Book(bookno),
cost NUMBER(6,2)
);

CREATE TABLE Borrower (


borrowerno INT PRIMARY KEY,
name VARCHAR2(50),
age INT
);

CREATE TABLE Loan (


copyno_fk INT REFERENCES Copy(copyno),
borrowerno_fk INT REFERENCES Borrower(borrowerno),
duedate DATE
);

-- 2. Insert Sample Records

INSERT INTO Author VALUES (1, 'J.K. Rowling');


INSERT INTO Author VALUES (2, 'Dan Brown');
INSERT INTO Author VALUES (3, 'Codd');
INSERT INTO Author VALUES (4, 'Herbert');
INSERT INTO Author VALUES (5, 'Martin');

INSERT INTO Library VALUES (1, 'Central Library', 'Kolkata', 10);


INSERT INTO Library VALUES (2, 'Tech Library', 'Delhi', 5);
INSERT INTO Library VALUES (3, 'Open Source Lib', 'Bangalore', 8);
INSERT INTO Library VALUES (4, 'Digital Lib', 'Hyderabad', 6);
INSERT INTO Library VALUES (5, 'City Library', 'Mumbai', 7);

INSERT INTO Book VALUES (101, 'Harry Potter', 300, 1);


INSERT INTO Book VALUES (102, 'Inferno', 500, 2);
INSERT INTO Book VALUES (103, 'Relational Model', 200, 3);
INSERT INTO Book VALUES (104, 'Dune', 350, 4);
INSERT INTO Book VALUES (105, 'GoT', 600, 5);

INSERT INTO Copy VALUES (1, 1, 103, 250);


INSERT INTO Copy VALUES (2, 2, 101, 400);
INSERT INTO Copy VALUES (3, 3, 102, 300);
INSERT INTO Copy VALUES (4, 4, 104, 350);
INSERT INTO Copy VALUES (5, 5, 105, 500);

INSERT INTO Borrower VALUES (1, 'Himanshu', 22);


INSERT INTO Borrower VALUES (2, 'Amit', 25);
INSERT INTO Borrower VALUES (3, 'Harman', 28);
INSERT INTO Borrower VALUES (4, 'Priya', 24);
INSERT INTO Borrower VALUES (5, 'Hitesh', 23);

INSERT INTO Loan VALUES (1, 2, TO_DATE('2024-06-01', 'YYYY-MM-DD'));


INSERT INTO Loan VALUES (2, 1, TO_DATE('2024-06-05', 'YYYY-MM-DD'));
INSERT INTO Loan VALUES (4, 5, TO_DATE('2024-06-10', 'YYYY-MM-DD'));

-- 3. SQL Queries

-- i. Libraries with books written by 'Codd'


SELECT DISTINCT L.libname, L.location

You might also like