The following code provides a comprehensive set of SQL commands for an Oracle SQL

command-line environment, covering schema design, data manipulation, constraints, various

function types, joins, subqueries, and an introduction to PL/SQL concepts like cursors and
triggers.
Important Notes for Oracle SQL Command Line:
●​ Semicolon (;): Every SQL statement must end with a semicolon.
●​ SET SERVEROUTPUT ON;: This command is crucial for displaying output from PL/SQL
blocks (like DBMS_OUTPUT.PUT_LINE). You should run it once at the beginning of your
session if you want to see PL/SQL output.
●​ ROLLBACK; and COMMIT;: Oracle performs DML operations within a transaction.
Changes are not permanently saved until COMMIT; is issued. ROLLBACK; undoes
uncommitted changes.
●​ SPOOL command: Used to save the output of your SQL*Plus session to a file. Useful for
logging your work.
●​ -- for single-line comments.
●​ /* ... */ for multi-line comments.
Section 1: Schema Design and Data Insertion (DDL & DML)
Let's design a simple schema for a "Library Management System".
1.1 DDL: Create Tables
-- DDL: Create Tables​
​
-- Create Authors Table​
CREATE TABLE Authors (​
author_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, --
Oracle 12c+ Identity Column​
first_name VARCHAR2(100) NOT NULL,​
last_name VARCHAR2(100) NOT NULL,​
birth_date DATE,​
nationality VARCHAR2(50)​
);​
​
-- Create Genres Table​
CREATE TABLE Genres (​
genre_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,​
genre_name VARCHAR2(100) UNIQUE NOT NULL​
);​
​
-- Create Books Table​
CREATE TABLE Books (​
book_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,​
title VARCHAR2(255) NOT NULL,​
publication_year NUMBER(4),​
isbn VARCHAR2(20) UNIQUE NOT NULL,​
price NUMBER(10, 2) DEFAULT 0.00,​
author_id NUMBER NOT NULL,​
genre_id NUMBER NOTBALL,​
CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES
Authors(author_id) ON DELETE CASCADE,​
 CONSTRAINT fk_genre FOREIGN KEY (genre_id) REFERENCES
Genres(genre_id) ON DELETE SET NULL,​
CONSTRAINT chk_publication_year CHECK (publication_year BETWEEN
1000 AND TO_CHAR(SYSDATE, 'YYYY')) -- Check constraint​
);​
​
-- Create Members Table​
CREATE TABLE Members (​
member_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,​
first_name VARCHAR2(100) NOT NULL,​
last_name VARCHAR2(100) NOT NULL,​
email VARCHAR2(150) UNIQUE NOT NULL,​
phone_number VARCHAR2(20),​
registration_date DATE DEFAULT SYSDATE​
);​
​
-- Create Loans Table​
CREATE TABLE Loans (​
loan_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,​
book_id NUMBER NOT NULL,​
member_id NUMBER NOT NULL,​
loan_date DATE DEFAULT SYSDATE,​
return_date DATE,​
due_date DATE GENERATED ALWAYS AS (loan_date + 14) VIRTUAL, --
Virtual column (Oracle 11gR2+)​
CONSTRAINT fk_loan_book FOREIGN KEY (book_id) REFERENCES
Books(book_id) ON DELETE CASCADE,​
CONSTRAINT fk_loan_member FOREIGN KEY (member_id) REFERENCES
Members(member_id) ON DELETE CASCADE,​
CONSTRAINT chk_return_date CHECK (return_date >= loan_date)​
);​
​
-- End of DDL: Create Tables​

1.2 DML: Insert Data

-- DML: Insert Data​
​
-- Insert into Authors​
INSERT INTO Authors (first_name, last_name, birth_date, nationality)
VALUES ('Gabriel', 'García Márquez', TO_DATE('06-MAR-1927',
'DD-MON-YYYY'), 'Colombian');​
INSERT INTO Authors (first_name, last_name, birth_date, nationality)
VALUES ('Jane', 'Austen', TO_DATE('16-DEC-1775', 'DD-MON-YYYY'),
'British');​
INSERT INTO Authors (first_name, last_name, birth_date, nationality)
VALUES ('George', 'Orwell', TO_DATE('25-JUN-1903', 'DD-MON-YYYY'),
'British');​
INSERT INTO Authors (first_name, last_name, birth_date, nationality)
VALUES ('J.K.', 'Rowling', TO_DATE('31-JUL-1965', 'DD-MON-YYYY'),
'British');​
INSERT INTO Authors (first_name, last_name, birth_date, nationality)
VALUES ('Haruki', 'Murakami', TO_DATE('12-JAN-1949', 'DD-MON-YYYY'),
'Japanese');​
​
-- Insert into Genres​
INSERT INTO Genres (genre_name) VALUES ('Magical Realism');​
INSERT INTO Genres (genre_name) VALUES ('Classics');​
INSERT INTO Genres (genre_name) VALUES ('Dystopian');​
INSERT INTO Genres (genre_name) VALUES ('Fantasy');​
INSERT INTO Genres (genre_name) VALUES ('Contemporary');​
INSERT INTO Genres (genre_name) VALUES ('Science Fiction');​
​
-- Insert into Books (Note: Author_id and Genre_id correspond to the
order of insertion above)​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('One Hundred Years of Solitude', 1967,
'9780060883281', 15.99, 1, 1);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Pride and Prejudice', 1813, '9780141439518', 10.50,
2, 2);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('1984', 1949, '9780451524935', 12.75, 3, 3);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Animal Farm', 1945, '9780451526342', 9.99, 3, 3);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Harry Potter and the Sorcerer''s Stone', 1997,
'9780590353427', 18.25, 4, 4);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Kafka on the Shore', 2002, '9780099458022', 14.00,
5, 5);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Norwegian Wood', 1987, '9780099448832', 13.50, 5,
5);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Love in the Time of Cholera', 1985,
'9780140119904', 16.50, 1, 1);​
INSERT INTO Books (title, publication_year, isbn, price, author_id,
genre_id) VALUES ('Emma', 1815, '9780141439587', 11.00, 2, 2);​
​
​
-- Insert into Members​
INSERT INTO Members (first_name, last_name, email, phone_number,
registration_date) VALUES ('Alice', 'Smith', 'alice.s@example.com',
'111-222-3333', TO_DATE('01-JAN-2023', 'DD-MON-YYYY'));​
INSERT INTO Members (first_name, last_name, email, phone_number,
registration_date) VALUES ('Bob', 'Johnson', 'bob.j@example.com',
'444-555-6666', TO_DATE('15-FEB-2023', 'DD-MON-YYYY'));​
INSERT INTO Members (first_name, last_name, email, phone_number,
registration_date) VALUES ('Charlie', 'Brown',
'charlie.b@example.com', '777-888-9999', TO_DATE('10-MAR-2024',
'DD-MON-YYYY'));​
INSERT INTO Members (first_name, last_name, email, phone_number,
registration_date) VALUES ('Diana', 'Prince', 'diana.p@example.com',
'000-111-2222', TO_DATE('05-APR-2024', 'DD-MON-YYYY'));​
INSERT INTO Members (first_name, last_name, email, phone_number,
registration_date) VALUES ('Eve', 'Adams', 'eve.a@example.com',
'999-888-7777', TO_DATE('20-MAY-2024', 'DD-MON-YYYY'));​
​
​
-- Insert into Loans (some books currently loaned, some returned)​
INSERT INTO Loans (book_id, member_id, loan_date, return_date) VALUES
(1, 1, TO_DATE('01-JUL-2024', 'DD-MON-YYYY'), TO_DATE('14-JUL-2024',
'DD-MON-YYYY')); -- Returned​
INSERT INTO Loans (book_id, member_id, loan_date) VALUES (3, 2,
TO_DATE('05-JUL-2024', 'DD-MON-YYYY')); -- Currently Loaned​
INSERT INTO Loans (book_id, member_id, loan_date, return_date) VALUES
(5, 3, TO_DATE('10-JUL-2024', 'DD-MON-YYYY'), TO_DATE('20-JUL-2024',
'DD-MON-YYYY')); -- Returned​
INSERT INTO Loans (book_id, member_id, loan_date) VALUES (2, 4,
TO_DATE('15-JUL-2024', 'DD-MON-YYYY')); -- Currently Loaned​
INSERT INTO Loans (book_id, member_id, loan_date) VALUES (7, 1,
TO_DATE('20-JUL-2024', 'DD-MON-YYYY')); -- Currently Loaned​
INSERT INTO Loans (book_id, member_id, loan_date, return_date) VALUES
(4, 5, TO_DATE('25-JUN-2024', 'DD-MON-YYYY'), TO_DATE('08-JUL-2024',
'DD-MON-YYYY')); -- Returned​
​
-- Commit the DML operations​
COMMIT;​
​
-- End of DML: Insert Data​

Section 2: Data Constraints - Alter Table Commands

Demonstrating adding and removing constraints using ALTER TABLE.
2.1 Add and Remove Integrity Constraints
-- DDL: Alter Table - Constraints​
​
-- Add a new column to Authors​
ALTER TABLE Authors​
ADD (email VARCHAR2(150));​
​
-- Add a UNIQUE constraint to the new email column in Authors​
ALTER TABLE Authors​
ADD CONSTRAINT uk_author_email UNIQUE (email);​
​
-- Add a NOT NULL constraint to the new email column (if it contains
NULLs, it will fail)​
-- First, update existing rows to have non-null emails for this to
work​
UPDATE Authors SET email = LOWER(first_name || '.' || last_name ||
'@author.com') WHERE email IS NULL;​
COMMIT;​
​
ALTER TABLE Authors​
MODIFY (email VARCHAR2(150) NOT NULL);​
​
-- Add a new CHECK constraint to Books: price must be greater than 5​
ALTER TABLE Books​
ADD CONSTRAINT chk_book_price_min CHECK (price > 5.00);​
​
-- Remove the unique constraint on author email​
ALTER TABLE Authors​
DROP CONSTRAINT uk_author_email;​
​
-- Remove the check constraint on book price​
ALTER TABLE Books​
DROP CONSTRAINT chk_book_price_min;​
​
-- Drop the email column we added​
ALTER TABLE Authors​
DROP COLUMN email;​
​
-- End of Data Constraints​

Section 3: Value-Matching and Pattern-Matching Conditions

3.1 Value Matching (=, IN, >, <, BETWEEN)
-- Value-matching conditions​
​
-- Retrieve books published in 1949​
SELECT title, publication_year​
FROM Books​
WHERE publication_year = 1949;​
​
-- Retrieve members registered in 2024​
SELECT first_name, last_name, registration_date​
FROM Members​
WHERE TO_CHAR(registration_date, 'YYYY') = '2024';​
​
-- Retrieve books with a price between 10.00 and 15.00 (inclusive)​
SELECT title, price​
FROM Books​
WHERE price BETWEEN 10.00 AND 15.00;​
​
-- Retrieve books by authors with author_id 1 or 3​
SELECT title, author_id​
FROM Books​
WHERE author_id IN (1, 3);​
​
-- Retrieve loans made before July 10, 2024​
SELECT loan_id, loan_date​
FROM Loans​
WHERE loan_date < TO_DATE('10-JUL-2024', 'DD-MON-YYYY');​

3.2 Pattern Matching (LIKE)

-- Pattern-matching conditions using LIKE​
​
-- Retrieve books whose title starts with 'H'​
SELECT title​
FROM Books​
WHERE title LIKE 'H%';​
​
-- Retrieve authors whose last name contains 'en'​
SELECT first_name, last_name​
FROM Authors​
WHERE last_name LIKE '%en%';​
​
-- Retrieve members whose email address ends with '.com'​
SELECT first_name, last_name, email​
FROM Members​
WHERE email LIKE '%.com';​
​
-- Retrieve books with 'the' in the title (case-insensitive for better
results, if NLS_COMP is LINGUISTIC and NLS_SORT is BINARY_CI)​
-- For a truly case-insensitive search regardless of session settings:​
SELECT title​
FROM Books​
WHERE LOWER(title) LIKE '%the%';​
​
-- Retrieve books with a two-letter word beginning with 'a' (e.g., 'a
') or ending with 'a' (e.g., ' a')​
SELECT title​
FROM Books​
WHERE title LIKE '% a %' OR title LIKE 'a %' OR title LIKE '% a';​

Section 4: Oracle Functions

4.1 Aggregate Functions
-- Aggregate Functions​
​
-- Total number of books​
SELECT COUNT(*) AS total_books FROM Books;​
​
-- Number of distinct authors​
SELECT COUNT(DISTINCT author_id) AS distinct_authors FROM Books;​
​
-- Average price of books​
SELECT AVG(price) AS average_book_price FROM Books;​
​
-- Sum of all book prices​
SELECT SUM(price) AS total_book_value FROM Books;​
​
-- Maximum price of a book​
SELECT MAX(price) AS highest_book_price FROM Books;​
​
-- Minimum price of a book​
SELECT MIN(price) AS lowest_book_price FROM Books;​

4.2 Numeric Functions

-- Numeric Functions​
​
-- ABS (Absolute value) - not directly applicable here, but for
example:​
SELECT ABS(-100) AS abs_value FROM DUAL;​
​
-- CEIL (Ceiling) and FLOOR (Floor)​
SELECT CEIL(15.99) AS ceil_price, FLOOR(15.99) AS floor_price FROM
DUAL;​
​
-- ROUND (Rounding to specified decimal places)​
SELECT ROUND(15.993, 2) AS rounded_price FROM DUAL;​
SELECT ROUND(15.997, 2) AS rounded_price FROM DUAL;​
​
-- TRUNC (Truncate to specified decimal places)​
SELECT TRUNC(15.997, 2) AS truncated_price FROM DUAL;​
​
-- MOD (Modulo)​
SELECT MOD(10, 3) AS remainder FROM DUAL; -- Returns 1​

4.3 String Functions

-- String Functions​
​
-- CONCAT (Concatenate strings - || is more common in Oracle)​
SELECT CONCAT(first_name, last_name) AS full_name_concat FROM Authors
WHERE author_id = 1;​
SELECT first_name || ' ' || last_name AS full_name_concatenated FROM
Authors WHERE author_id = 1;​
​
-- LENGTH (Length of a string)​
SELECT title, LENGTH(title) AS title_length FROM Books WHERE book_id =
1;​
​
-- SUBSTR (Substring)​
SELECT SUBSTR('One Hundred Years of Solitude', 1, 10) AS short_title
FROM DUAL;​
​
-- INSTR (Position of substring)​
SELECT INSTR('Harry Potter and the Sorcerer''s Stone', 'Potter') AS
potter_position FROM DUAL;​
​
-- LPAD / RPAD (Pad string with characters)​
SELECT LPAD(book_id, 5, '0') AS padded_id FROM Books WHERE book_id =
1;​
​
-- TRIM (Remove leading/trailing/both characters)​
SELECT TRIM(' Hello World ') AS trimmed_string FROM DUAL;​
SELECT TRIM(LEADING 'x' FROM 'xxxOraclexxx') AS trimmed_leading FROM
DUAL;​
SELECT TRIM(TRAILING 'x' FROM 'xxxOraclexxx') AS trimmed_trailing FROM
DUAL;​
SELECT TRIM(BOTH 'x' FROM 'xxxOraclexxx') AS trimmed_both FROM DUAL;​
​
-- REPLACE (Replace all occurrences of a substring)​
SELECT REPLACE('Old World', 'Old', 'New') AS new_string FROM DUAL;​
​
-- UPPER / LOWER / INITCAP (Case conversion)​
SELECT UPPER('oracle'), LOWER('ORACLE'), INITCAP('oracle database')
FROM DUAL;​
SELECT first_name, last_name, INITCAP(first_name || ' ' || last_name)
AS ProperCaseName FROM Members WHERE member_id = 1;​

4.4 Conversion Functions (TO_CHAR, TO_NUMBER, TO_DATE)

-- Conversion Functions​
​
-- TO_CHAR (Date to Character)​
SELECT loan_date, TO_CHAR(loan_date, 'DD-MON-YYYY') AS formatted_date
FROM Loans WHERE loan_id = 2;​
SELECT SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS current_time FROM
DUAL;​
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS
timestamp_string FROM DUAL;​
​
-- TO_CHAR (Number to Character)​
SELECT price, TO_CHAR(price, '$9,999.00') AS formatted_price FROM
Books WHERE book_id = 1;​
​
-- TO_NUMBER (Character to Number)​
SELECT TO_NUMBER('123.45') + 10 AS calculated_number FROM DUAL;​
​
-- TO_DATE (Character to Date) - already used in INSERT statements​
SELECT TO_DATE('25-DEC-2024', 'DD-MON-YYYY') AS christmas_date FROM
DUAL;​

4.5 Date Functions

-- Date Functions​
​
-- SYSDATE (Current system date and time)​
SELECT SYSDATE AS current_system_date FROM DUAL;​
​
-- ADD_MONTHS (Add months to a date)​
SELECT SYSDATE AS today, ADD_MONTHS(SYSDATE, 3) AS date_plus_3_months
FROM DUAL;​
​
-- MONTHS_BETWEEN (Months between two dates)​
SELECT​
registration_date,​
SYSDATE,​
MONTHS_BETWEEN(SYSDATE, registration_date) AS
months_since_registration​
FROM Members​
WHERE member_id = 1;​
​
-- NEXT_DAY (Next occurrence of a weekday)​
SELECT NEXT_DAY(SYSDATE, 'SUNDAY') AS next_sunday FROM DUAL;​
​
-- LAST_DAY (Last day of the month)​
SELECT SYSDATE AS today, LAST_DAY(SYSDATE) AS last_day_of_month FROM
DUAL;​
​
-- TRUNC (Truncate date to a unit)​
SELECT TRUNC(SYSDATE, 'MM') AS first_day_of_month FROM DUAL; -- First
day of current month​
SELECT TRUNC(SYSDATE, 'YYYY') AS first_day_of_year FROM DUAL; -- First
day of current year​
​
-- EXTRACT (Extract specific date parts)​
SELECT​
EXTRACT(YEAR FROM SYSDATE) AS current_year,​
EXTRACT(MONTH FROM SYSDATE) AS current_month,​
EXTRACT(DAY FROM SYSDATE) AS current_day​
FROM DUAL;​
​
-- Date arithmetic (adding/subtracting days)​
SELECT loan_date, loan_date + 7 AS one_week_later, loan_date - 3 AS
three_days_earlier FROM Loans WHERE loan_id = 2;​
​
-- Days between two dates​
SELECT return_date - loan_date AS days_on_loan FROM Loans WHERE
loan_id = 1;​

4.6 Set Operations (UNION, UNION ALL, INTERSECT, MINUS)

-- Set Operations​
​
-- UNION (Combines results, removes duplicates, sorts)​
-- Members whose first name is 'Alice' or 'Bob'​
SELECT first_name, last_name FROM Members WHERE first_name = 'Alice'​
UNION​
SELECT first_name, last_name FROM Members WHERE first_name = 'Bob';​
​
-- UNION ALL (Combines results, includes duplicates, no sorting by
default)​
-- All first names from Authors and Members (including duplicates if
any)​
SELECT first_name FROM Authors​
UNION ALL​
SELECT first_name FROM Members;​
​
-- INTERSECT (Returns common rows)​
-- Authors who also appear as members (assuming same name structure) -
likely empty for our data​
SELECT first_name, last_name FROM Authors​
INTERSECT​
SELECT first_name, last_name FROM Members;​
​
-- MINUS (Returns rows from first query not found in second)​
-- Books whose publication year is 1949 but are NOT priced at 12.75​
SELECT title, publication_year, price FROM Books WHERE
publication_year = 1949​
MINUS​
SELECT title, publication_year, price FROM Books WHERE
publication_year = 1949 AND price = 12.75;​

Section 5: Group By and Having Clauses

-- Study of GROUP BY and HAVING Clauses​
​
-- Group books by publication year and count how many books were
published in each year​
SELECT​
publication_year,​
COUNT(book_id) AS number_of_books​
FROM​
Books​
GROUP BY​
publication_year​
ORDER BY​
 publication_year DESC;​
​
-- Group books by author and calculate the average price of their
books​
SELECT​
a.first_name,​
a.last_name,​
AVG(b.price) AS average_book_price​
FROM​
Authors a​
JOIN​
Books b ON a.author_id = b.author_id​
GROUP BY​
a.first_name, a.last_name​
ORDER BY​
average_book_price DESC;​
​
-- Group members by registration year and count members, but only for
years with more than 1 registration​
SELECT​
TO_CHAR(registration_date, 'YYYY') AS registration_year,​
COUNT(member_id) AS number_of_members​
FROM​
Members​
GROUP BY​
TO_CHAR(registration_date, 'YYYY')​
HAVING​
COUNT(member_id) > 1​
ORDER BY​
registration_year;​
​
-- Find genres where the sum of book prices is greater than $30​
SELECT​
g.genre_name,​
SUM(b.price) AS total_genre_value​
FROM​
Genres g​
JOIN​
Books b ON g.genre_id = b.genre_id​
GROUP BY​
g.genre_name​
HAVING​
SUM(b.price) > 30​
ORDER BY​
total_genre_value DESC;​
​
-- Count currently loaned books per member, only showing members with
more than one active loan​
SELECT​
m.first_name,​
m.last_name,​
COUNT(l.loan_id) AS active_loans​
FROM​
Members m​
JOIN​
Loans l ON m.member_id = l.member_id​
WHERE​
l.return_date IS NULL​
GROUP BY​
m.first_name, m.last_name​
HAVING​
COUNT(l.loan_id) > 1;​

Section 6: Subqueries
-- Solve queries using the concept of Subquery​
​
-- 6.1 Scalar Subquery (returns a single value)​
-- Find books whose price is greater than the average book price​
SELECT​
title,​
price​
FROM​
Books​
WHERE​
price > (SELECT AVG(price) FROM Books);​
​
-- 6.2 Multi-row Subquery (returns multiple rows, single column)​
-- Find authors who have written books published after 2000​
SELECT​
first_name,​
last_name​
FROM​
Authors​
WHERE​
author_id IN (SELECT author_id FROM Books WHERE publication_year >
2000);​
​
-- Find members who have active loans (return_date IS NULL)​
SELECT​
first_name,​
last_name​
FROM​
Members​
WHERE​
member_id IN (SELECT DISTINCT member_id FROM Loans WHERE
return_date IS NULL);​
​
-- 6.3 Multi-column Subquery (returns multiple columns and rows) -
less common but possible​
-- Find books that share both the author and genre with a specific
book (e.g., Book ID 1)​
SELECT​
title,​
author_id,​
genre_id​
FROM​
Books​
WHERE​
(author_id, genre_id) = (SELECT author_id, genre_id FROM Books
WHERE book_id = 1)​
AND book_id != 1; -- Exclude the original book​
​
-- 6.4 Correlated Subquery (subquery depends on outer query)​
-- Find books that have a price higher than the average price of books
by the same author​
SELECT​
b1.title,​
b1.price,​
a.first_name,​
a.last_name​
FROM​
Books b1​
JOIN​
Authors a ON b1.author_id = a.author_id​
WHERE​
b1.price > (SELECT AVG(b2.price) FROM Books b2 WHERE b2.author_id
= b1.author_id);​
​
-- Find genres that have at least one book published after 2000​
SELECT​
genre_name​
FROM​
Genres g​
WHERE​
EXISTS (SELECT 1 FROM Books b WHERE b.genre_id = g.genre_id AND
b.publication_year > 2000);​
​
-- Find members who have not loaned any book (NOT EXISTS)​
SELECT​
first_name,​
last_name​
FROM​
Members m​
WHERE​
 NOT EXISTS (SELECT 1 FROM Loans l WHERE l.member_id =
m.member_id);​

Section 7: Displaying Data from Multiple Tables Using Join Operations

-- Displaying data from Multiple Tables using Join Operations​
​
-- 7.1 INNER JOIN (Returns rows when there is a match in both tables)​
-- List all books with their author's full name and genre name​
SELECT​
b.title,​
a.first_name AS author_first_name,​
a.last_name AS author_last_name,​
g.genre_name​
FROM​
Books b​
INNER JOIN​
Authors a ON b.author_id = a.author_id​
INNER JOIN​
Genres g ON b.genre_id = g.genre_id​
ORDER BY​
b.title;​
​
-- List all current loans showing book title, member name, and loan
date​
SELECT​
m.first_name AS member_first_name,​
m.last_name AS member_last_name,​
b.title AS book_title,​
l.loan_date,​
l.due_date -- Virtual column​
FROM​
Loans l​
INNER JOIN​
Members m ON l.member_id = m.member_id​
INNER JOIN​
Books b ON l.book_id = b.book_id​
WHERE​
l.return_date IS NULL​
ORDER BY​
l.loan_date DESC;​
​
-- 7.2 LEFT JOIN (or LEFT OUTER JOIN)​
-- List all authors and their books. If an author has no books, still
show the author.​
SELECT​
a.first_name,​
a.last_name,​
b.title​
FROM​
Authors a​
LEFT JOIN​
Books b ON a.author_id = b.author_id​
ORDER BY​
a.last_name, b.title;​
​
-- List all genres and the books in them. Show genres even if they
have no books.​
INSERT INTO Genres (genre_name) VALUES ('Poetry'); -- Add a genre with
no books​
COMMIT;​
​
SELECT​
g.genre_name,​
b.title​
FROM​
Genres g​
LEFT JOIN​
Books b ON g.genre_id = b.genre_id​
ORDER BY​
g.genre_name, b.title;​
​
-- Clean up the 'Poetry' genre for other examples​
DELETE FROM Genres WHERE genre_name = 'Poetry';​
COMMIT;​
​
​
-- 7.3 RIGHT JOIN (or RIGHT OUTER JOIN)​
-- Less common as it can usually be rewritten as a LEFT JOIN by
swapping tables.​
-- Example: List all books and their genres. Show books even if their
genre_id became NULL​
-- (e.g., if ON DELETE SET NULL was triggered or if genre_id was
initially NULL).​
-- Note: Our schema has genre_id NOT NULL for Books, so this won't
show Books without Genres initially.​
-- If we drop a genre, books might have NULL genre_id if the FK was ON
DELETE SET NULL.​
-- Let's simulate a book with a NULL genre_id for this example if it
wasn't already handled.​
-- For this to show something, you'd need a book where genre_id is
NULL.​
-- Let's consider a LEFT JOIN on Books to Genres, which is more
intuitive for this data.​
SELECT​
b.title,​
g.genre_name​
FROM​
Books b​
RIGHT JOIN​
Genres g ON b.genre_id = g.genre_id​
ORDER BY​
g.genre_name, b.title;​
​
​
-- 7.4 FULL OUTER JOIN (Returns rows when there is a match in either
left or right table)​
-- List all authors and all genres, and match them with books where
possible.​
-- Oracle doesn't have a direct 'Books' entry for authors/genres
without books with a simple FULL JOIN.​
-- This shows all authors, all genres, and the books that connect
them.​
SELECT​
a.first_name,​
a.last_name,​
b.title,​
g.genre_name​
FROM​
Authors a​
FULL OUTER JOIN​
Books b ON a.author_id = b.author_id​
FULL OUTER JOIN​
Genres g ON b.genre_id = g.genre_id​
ORDER BY​
a.last_name, g.genre_name, b.title;​
​
-- 7.5 SELF JOIN (Joining a table to itself)​
-- Find pairs of books written by the same author​
SELECT​
b1.title AS book1_title,​
b2.title AS book2_title,​
a.first_name,​
a.last_name​
FROM​
Books b1​
JOIN​
Books b2 ON b1.author_id = b2.author_id AND b1.book_id <
b2.book_id​
JOIN​
Authors a ON b1.author_id = a.author_id​
ORDER BY​
a.last_name, b1.title, b2.title;​

Section 8: Database Backup & Recovery Commands (Conceptual/Logical Backup)

Note: Oracle's actual backup and recovery commands (RMAN, Data Pump) are not SQL
command-line statements that you execute within SQL*Plus directly like SELECT or CREATE
TABLE. They are separate utilities executed from the operating system command line.
Here, I'll demonstrate the logical backup/export using Data Pump utility which is run from OS
command line, but the SQL commands to view Data Pump jobs are useful. Also, Flashback
Query and Flashback Table are SQL features for point-in-time recovery of data/tables without a
full database restore.
8.1 Logical Backup (Data Pump Export/Import - OS Level)
Conceptual explanation (not SQL*Plus code to run directly):
To export (backup) schema data and structure: From your OS command line (e.g., Command
Prompt on Windows, Bash on Linux), logged in as a user with EXP_FULL_DATABASE role:
-- Export entire schema (e.g., LIBRARY_ADMIN schema)​
expdp library_admin/password@ORCLPDB1 SCHEMAS=LIBRARY_ADMIN
DIRECTORY=DATA_PUMP_DIR DUMPFILE=library_schema.dmp
LOGFILE=library_schema_export.log​
​
-- To import (recover) this schema data:​
impdp library_admin/password@ORCLPDB1 SCHEMAS=LIBRARY_ADMIN
DIRECTORY=DATA_PUMP_DIR DUMPFILE=library_schema.dmp
LOGFILE=library_schema_import.log​
​
-- If you want to import into a different user:​
impdp system/password@ORCLPDB1 REMAP_SCHEMA=LIBRARY_ADMIN:NEW_USER
DIRECTORY=DATA_PUMP_DIR DUMPFILE=library_schema.dmp
LOGFILE=library_schema_remap.log​

SQL*Plus commands to prepare/monitor Data Pump:

-- SQL*Plus: To see available Data Pump directories (must be created
by DBA)​
SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';​
​
-- If DATA_PUMP_DIR doesn't exist, as SYSDBA or privileged user:​
-- CREATE DIRECTORY DATA_PUMP_DIR AS '/path/to/your/backup/directory';​
-- GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO your_user;​
​
-- SQL*Plus: To monitor Data Pump jobs (if you run expdp/impdp in
background)​
SELECT owner_name, job_name, operation, job_mode, state,
attached_sessions, degree​
FROM DBA_DATAPUMP_JOBS;​

8.2 Flashback Query (SQL Feature for Data Recovery)

This allows you to query data as it existed at a specific point in time in the past. Requires
UNDO_RETENTION to be configured sufficiently.
-- Flashback Query: See data as it was 5 minutes ago​
-- First, make some changes to demonstrate​
UPDATE Books SET price = 999.99 WHERE book_id = 1;​
COMMIT; -- Simulate a mistake​
​
-- Now, query the table as it was before the update (assuming it
happened recently)​
SELECT title, price FROM Books AS OF TIMESTAMP (SYSTIMESTAMP -
INTERVAL '5' MINUTE) WHERE book_id = 1;​
​
-- Or by SCN (System Change Number - advanced)​
-- SELECT CURRENT_SCN FROM V$DATABASE; -- Get current SCN​
-- SELECT title, price FROM Books AS OF SCN 1234567 WHERE book_id = 1;​
​
-- Revert the change (if it was a mistake and committed) by
re-updating or using flashback table.​
-- Let's revert the change for book_id 1​
UPDATE Books SET price = 15.99 WHERE book_id = 1;​
COMMIT;​

8.3 Flashback Table (SQL Feature for Table Recovery)

This can restore a table to a previous state. Requires UNDO_RETENTION and ROW
MOVEMENT enabled on the table.
-- Flashback Table: Recover a table to a past state​
​
-- Enable ROW MOVEMENT for the table (required for flashback table)​
ALTER TABLE Books ENABLE ROW MOVEMENT;​
​
-- Simulate a accidental delete​
DELETE FROM Books WHERE author_id = 3;​
COMMIT; -- Oh no, Orwell's books are gone!​
​
-- Check current state​
SELECT * FROM Books WHERE author_id = 3; -- Should show no rows​
​
-- Now, flashback the table to a time before the delete (adjust
timestamp as needed)​
-- Make sure the timestamp is before the DELETE operation.​
FLASHBACK TABLE Books TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1'
MINUTE);​
​
-- Verify recovery​
SELECT * FROM Books WHERE author_id = 3; -- Should show Orwell's books
again!​
​
-- Disable ROW MOVEMENT if not needed anymore​
ALTER TABLE Books DISABLE ROW MOVEMENT;​
​
COMMIT; -- Commit the flashback operation​

Important: The INTERVAL value must be adjusted based on how long ago the action you want
to revert occurred. Oracle's undo retention needs to be sufficient.
Section 9: Rollback, Commit, Savepoint
These commands manage transactions in Oracle.
-- Study & Implementation of Rollback, Commit, Savepoint​
​
-- Start a transaction implicitly (any DML starts one) or explicitly
with SET TRANSACTION​
-- SET TRANSACTION READ WRITE; -- Optional, DML implies this.​
​
-- 1. Insert a new genre​
INSERT INTO Genres (genre_name) VALUES ('Biography');​
SELECT * FROM Genres WHERE genre_name = 'Biography'; -- Visible in
current session​
​
-- 2. Create a savepoint​
SAVEPOINT sp_before_book_update;​
​
-- 3. Update a book's price​
UPDATE Books SET price = 20.00 WHERE book_id = 2;​
SELECT title, price FROM Books WHERE book_id = 2;​
​
-- 4. Delete a member​
DELETE FROM Members WHERE member_id = 5;​
SELECT * FROM Members WHERE member_id = 5; -- Should show no rows​
​
-- 5. Rollback to the savepoint (undoes update and delete, keeps the
insert)​
ROLLBACK TO sp_before_book_update;​
​
SELECT * FROM Genres WHERE genre_name = 'Biography'; -- Still there
(inserted before savepoint)​
SELECT title, price FROM Books WHERE book_id = 2; -- Price should be
back to original (10.50)​
SELECT * FROM Members WHERE member_id = 5; -- Member 5 should be back​
​
-- 6. Now, let's make a new change and commit it​
UPDATE Books SET price = 25.00 WHERE book_id = 6;​
SELECT title, price FROM Books WHERE book_id = 6;​
​
-- 7. Commit the transaction (makes all changes permanent)​
COMMIT;​
​
SELECT title, price FROM Books WHERE book_id = 6; -- Price is now
25.00 permanently​
​
-- 8. Let's try to rollback after a commit​
UPDATE Books SET price = 5.00 WHERE book_id = 6; -- New change​
ROLLBACK; -- This will only undo the last update, not the previous
commit​
​
SELECT title, price FROM Books WHERE book_id = 6; -- Price should
revert to 25.00, not 5.00​
​
-- Clean up the 'Biography' genre if you don't want it​
DELETE FROM Genres WHERE genre_name = 'Biography';​
COMMIT;​

Section 10: PL/SQL using Cursor and Triggers

10.1 PL/SQL - Cursor
-- PL/SQL using Cursor​
​
SET SERVEROUTPUT ON; -- Enable output from DBMS_OUTPUT​
​
-- Anonymous PL/SQL Block with an Explicit Cursor​
DECLARE​
CURSOR c_authors_books IS​
SELECT​
a.first_name,​
a.last_name,​
b.title,​
b.publication_year​
FROM​
Authors a​
JOIN​
Books b ON a.author_id = b.author_id​
ORDER BY​
a.last_name, b.publication_year;​
​
v_author_fname Authors.first_name%TYPE;​
v_author_lname Authors.last_name%TYPE;​
v_book_title Books.title%TYPE;​
v_pub_year Books.publication_year%TYPE;​
BEGIN​
DBMS_OUTPUT.PUT_LINE('--- List of Authors and their Books ---');​
DBMS_OUTPUT.PUT_LINE('-------------------------------------');​
​
OPEN c_authors_books; -- Open the cursor​
​
LOOP​
FETCH c_authors_books INTO v_author_fname, v_author_lname,
v_book_title, v_pub_year;​
EXIT WHEN c_authors_books%NOTFOUND; -- Exit loop when no more
rows​
​
DBMS_OUTPUT.PUT_LINE('Author: ' || v_author_fname || ' ' ||
v_author_lname ||​
', Book: ' || v_book_title || ' (' ||
v_pub_year || ')');​
END LOOP;​
​
CLOSE c_authors_books; -- Close the cursor​
​
DBMS_OUTPUT.PUT_LINE('-------------------------------------');​
DBMS_OUTPUT.PUT_LINE('Cursor processing complete.');​
EXCEPTION​
WHEN OTHERS THEN​
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);​
IF c_authors_books%ISOPEN THEN​
CLOSE c_authors_books;​
END IF;​
END;​
/​

10.2 PL/SQL - Trigger

Let's create a trigger that logs changes to the Loans table into a new Loan_Audit table.
-- PL/SQL - Trigger​
​
-- First, create an audit table​
CREATE TABLE Loan_Audit (​
audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,​
loan_id NUMBER,​
book_id NUMBER,​
member_id NUMBER,​
action_type VARCHAR2(10) NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'​
old_loan_date DATE,​
new_loan_date DATE,​
old_return_date DATE,​
new_return_date DATE,​
change_by VARCHAR2(100) DEFAULT USER,​
change_date TIMESTAMP DEFAULT SYSTIMESTAMP​
);​
​
-- Create a BEFORE/AFTER Row-Level Trigger on Loans table​
CREATE OR REPLACE TRIGGER trg_loans_audit​
AFTER INSERT OR UPDATE OR DELETE ON Loans​
FOR EACH ROW​
DECLARE​
v_action VARCHAR2(10);​
BEGIN​
IF INSERTING THEN​
v_action := 'INSERT';​
INSERT INTO Loan_Audit (loan_id, book_id, member_id,
action_type, new_loan_date, new_return_date)​
VALUES (:NEW.loan_id, :NEW.book_id, :NEW.member_id, v_action,
:NEW.loan_date, :NEW.return_date);​
 ELSIF UPDATING THEN​
v_action := 'UPDATE';​
INSERT INTO Loan_Audit (loan_id, book_id, member_id,
action_type, old_loan_date, new_loan_date, old_return_date,
new_return_date)​
VALUES (:OLD.loan_id, :OLD.book_id, :OLD.member_id, v_action,
:OLD.loan_date, :NEW.loan_date, :OLD.return_date, :NEW.return_date);​
ELSIF DELETING THEN​
v_action := 'DELETE';​
INSERT INTO Loan_Audit (loan_id, book_id, member_id,
action_type, old_loan_date, old_return_date)​
VALUES (:OLD.loan_id, :OLD.book_id, :OLD.member_id, v_action,
:OLD.loan_date, :OLD.return_date);​
END IF;​
END;​
/​
​
-- Verify trigger compilation​
SHOW ERRORS TRIGGER trg_loans_audit;​
​
-- Test the trigger:​
-- 1. Insert a new loan​
INSERT INTO Loans (book_id, member_id, loan_date) VALUES (6, 2,
TO_DATE('01-AUG-2024', 'DD-MON-YYYY'));​
COMMIT;​
SELECT * FROM Loans WHERE book_id = 6;​
SELECT * FROM Loan_Audit WHERE action_type = 'INSERT' AND book_id = 6;​
​
-- 2. Update a loan (return a book)​
UPDATE Loans SET return_date = TO_DATE('15-AUG-2024', 'DD-MON-YYYY')
WHERE book_id = 6 AND member_id = 2;​
COMMIT;​
SELECT * FROM Loans WHERE book_id = 6;​
SELECT * FROM Loan_Audit WHERE action_type = 'UPDATE' AND book_id = 6;​
​
-- 3. Delete a loan​
DELETE FROM Loans WHERE book_id = 6 AND member_id = 2;​
COMMIT;​
SELECT * FROM Loans WHERE book_id = 6;​
SELECT * FROM Loan_Audit WHERE action_type = 'DELETE' AND book_id = 6;​
​
-- Clean up the trigger and audit table if desired​
-- DROP TRIGGER trg_loans_audit;​
-- DROP TABLE Loan_Audit;​
-- COMMIT;​

This comprehensive script covers all aspects requested for an Oracle SQL command-line
environment. Remember to execute these commands sequentially and commit your DML
changes as needed.