EXPERIMENT-1
For the given database, write SQL queries and corresponding relational algebra queries (wherever possible)
:
Author (aid, authorname, citizenship, birthyear), Book(aid, isbn, title, authorname),Topic(isbn, subject),
Branch(libname, city), Instock(isbn, libname, quantity)
a. Give all authors born after 1940.
b. Give the names of libraries in Sydney.
c. Give the cities where each book is held.
Experiment-2
For the database in question 1, write SQL queries and relational algebra queries
(wherever possible):
a. Give the title of each book on the topic of either alcohol or drugs.
b. Give the title and author of each book of which at least two copies are held in
a branch located in Melbourne.
c. Give the name of each Italian author who wrote an autobiography.
Software Used: jdoodle.com
Code:
CREATE TABLE Author (
aid INT PRIMARY KEY,
authorname VARCHAR(100) NOT NULL,
citizenship VARCHAR(50),
birthyear INT
);
CREATE TABLE Book (
aid INT,
isbn CHAR(13) PRIMARY KEY,
title VARCHAR(200) NOT NULL,
authorname VARCHAR(100)
);
CREATE TABLE Topic (
isbn CHAR(13),
subject VARCHAR(100),
PRIMARY KEY (isbn, subject)
);
CREATE TABLE Branch (
libname VARCHAR(100) PRIMARY KEY,
city VARCHAR(100)
);
CREATE TABLE Instock (
isbn CHAR(13),
libname VARCHAR(100),
quantity INT,
PRIMARY KEY (isbn, libname)
);
INSERT INTO Author (aid, authorname, citizenship, birthyear)
VALUES
(1, 'George Orwell', 'Italian', 1903),
(2, 'J.K. Rowling', 'British', 1965),
(3, 'Isaac Asimov', 'American', 1920),
(4, 'Agatha Christie', 'British', 1890),
(5, 'Chimamanda Adichie', 'Nigerian', 1977);
INSERT INTO Book (aid, isbn, title, authorname)
VALUES
(1, '9780451524935', 'autobiography 1984', 'George Orwell'),
(2, '9780747532743', 'Alcoholism', 'J.K. Rowling'),
(3, '9780553382563', 'Foundation', 'Isaac Asimov'),
(4, '9780062073488', 'Murder on the Orient Express', 'Agatha Christie'),
(5, '9780307271082', 'Half of a Yellow Sun', 'Chimamanda Adichie');
INSERT INTO Topic (isbn, subject)
VALUES
('9780451524935', 'Dystopian'),
('9780747532743', 'alcohol'),
('9780553382563', 'Science Fiction'),
('9780062073488', 'drugs'),
('9780307271082', 'Historical Fiction');
INSERT INTO Branch (libname, city)
VALUES
('Central Library', 'Sydney'),
('State Library', 'Melbourne'),
('City Library', 'Sydney'),
('National Library', 'Canberra'),
('Community Library', 'Brisbane');
INSERT INTO Instock (isbn, libname, quantity)
VALUES
('9780451524935', 'Central Library', 10),
('9780747532743', 'City Library', 8),
('9780553382563', 'State Library', 15),
('9780062073488', 'National Library', 5),
('9780307271082', 'Community Library', 12);
--Give the title of each book on the topic of either
alcohol or drugs
SELECT DISTINCT b.title
FROM Book b
JOIN Topic t ON b.isbn = t.isbn
WHERE t.subject IN ('alcohol', 'drugs');
--Give the title and author of each book of which at least two copies are held in a branch located in
Melbourne.
SELECT DISTINCT b.title, b.authorname
FROM Book b
JOIN Instock i ON b.isbn = i.isbn
JOIN Branch br ON i.libname = br.libname
WHERE br.city = 'Melbourne' AND i.quantity >= 2;
--Give the name of each Italian author who wrote an autobiography
SELECT DISTINCT a.authorname
FROM Author a
JOIN Book b ON a.aid = b.aid
WHERE a.citizenship = 'Italian' AND b.title LIKE
'%autobiography%';
Experiment-3
For the database in question 1, write SQL queries :
a. Give the total number of books in stock in the branch called
Fisher.
b. This could mean the number of different titles, or the number
of physical copies.
c. Give the total number of books in stock in the branches
located in Sydney.
Software Used: JDoodle.com
Code:
CREATE TABLE Author (
aid INT PRIMARY KEY,
authorname VARCHAR(100) NOT NULL,
citizenship VARCHAR(50),
birthyear INT
);
CREATE TABLE Book (
aid INT,
isbn CHAR(13) PRIMARY KEY,
title VARCHAR(200) NOT NULL,
authorname VARCHAR(100)
);
CREATE TABLE Topic (
isbn CHAR(13),
subject VARCHAR(100),
PRIMARY KEY (isbn, subject)
);
CREATE TABLE Branch (
libname VARCHAR(100) PRIMARY KEY,
city VARCHAR(100)
);
CREATE TABLE Instock (
isbn CHAR(13),
libname VARCHAR(100),
quantity INT,
PRIMARY KEY (isbn, libname)
);
INSERT INTO Author (aid, authorname, citizenship, birthyear)
VALUES
(1, 'George Orwell', 'Italian', 1903),
(2, 'J.K. Rowling', 'British', 1965),
(3, 'Isaac Asimov', 'American', 1920),
(4, 'Agatha Christie', 'British', 1890),
(5, 'Chimamanda Adichie', 'Nigerian', 1977);
INSERT INTO Book (aid, isbn, title, authorname)
VALUES
(1, '9780451524935', 'autobiography 1984', 'George Orwell'),
(2, '9780747532743', 'Alcoholism', 'J.K. Rowling'),
(3, '9780553382563', 'Foundation', 'Isaac Asimov'),
(4, '9780062073488', 'Murder on the Orient Express', 'Agatha Christie'),
(5, '9780307271082', 'Half of a Yellow Sun', 'Chimamanda Adichie');
INSERT INTO Topic (isbn, subject)
VALUES
('9780451524935', 'Dystopian'),
('9780747532743', 'alcohol'),
('9780553382563', 'Science Fiction'),
('9780062073488', 'drugs'),
('9780307271082', 'Historical Fiction');
INSERT INTO Branch (libname, city)
VALUES
('Central Library', 'Sydney'),
('State Library', 'Melbourne'),
('City Library', 'Sydney'),
('National Library', 'Canberra'),
('Community Library', 'Brisbane');
(‘Fisher’,’California’);
INSERT INTO Instock (isbn, libname, quantity)
VALUES
('9780451524935', 'Central Library', 10),
('9780747532743', 'City Library', 8),
('9780553382563', 'State Library', 15),
('9780062073488', 'National Library', 5),
('9780307271082', 'Community Library', 12);
(‘9780307271083’,’Fisher’,15);
Give the total number of books in stock in the branch called Fisher.
SELECT SUM(quantity) AS total_books_in_fisher
FROM Instock
WHERE libname = 'Fisher';
Total Unique Book Titles in Stock (Different Titles)
SELECT COUNT(DISTINCT isbn) AS unique_titles
FROM Instock;
Give the total number of books in stock in the branches located in Sydney.
SELECT SUM(I.quantity) AS total_books_in_sydney
FROM Instock I
JOIN Branch B ON I.libname = B.libname
WHERE B.city = 'Sydney';
EXPERIMENT-4
Create the following relations in SQL. Add a constraint in the ‘SALES’
relation that payment mode can only be ‘COD’ and ‘PAYPAL’.
1. CUSTOMER (CUST_ID, CUST_NAME, CUST_ADD)
2. SALES (#CUSTOMER_ID, ITEM_ID, PAYMENT_MODE)
For the relations created, write a query to insert data into the relations.
Insert 5 rows in each relation. Write a query to alter the data type of
CUST_ADD from varchar to char. Show the use of DDL triggers in response to
DDL events.
SOFTWARE USED: jdoodle.com
Code:
CREATE TABLE CUSTOMER (
CUST_ID INT PRIMARY KEY,
CUST_NAME VARCHAR(50),
CUST_ADD VARCHAR(100)
);
CREATE TABLE SALES (
CUSTOMER_ID INT,
ITEM_ID INT,
PAYMENT_MODE VARCHAR(10) CHECK (PAYMENT_MODE IN ('COD', 'PAYPAL')),
PRIMARY KEY (ITEM_ID),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUST_ID) ON DELETE CASCADE
);
INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, CUST_ADD) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago'),
(4, 'David', 'Houston'),
(5, 'Emma', 'San Francisco');
Select* from CUSTOMER;
INSERT INTO SALES (CUSTOMER_ID, ITEM_ID, PAYMENT_MODE) VALUES
(1, 101, 'COD'),
(2, 102, 'PAYPAL'),
(3, 103, 'COD'),
(4, 104, 'PAYPAL'),
(5, 105, 'COD');
Select* from SALES;
//ALTER THE TABLE
ALTER TABLE CUSTOMER
MODIFY CUST_ADD CHAR(100);
EXPERIMENT -5
For the given database, write SQL queries to create the following database:
Flights(flno, from, to, distance, departs),Aircraft(aid, aname, range),Certified(eid,
aid),Employees(eid, ename, salary) By definition, pilots are those employees who are
certified on at least one aircraft. An aircraft can be used for any flight provided it has
sufficient range. Pilots can pilot any flight provided they are certified
on an aircraft with sufficient range.
a.Find eid’s of pilots who are certified on some Boeing.
b.Find names of pilots who are certified on some Boeing.
c. Find aid’s of aircraft that can fly non-stop from LA to NY.
Assume you don’t already know the distance.
SOFTWARE USED: JDOODLE.COM
Code:
CREATE TABLE Flights(
flno int NOT NULL PRIMARY KEY,
start VARCHAR(50),
dest VARCHAR(50),
distance int,
departs TIMESTAMP
);
CREATE TABLE Aircraft(
aid int NOT NULL PRIMARY KEY,
aname VARCHAR(50),
range int
);
CREATE TABLE Employees(
eid INT NOT NULL PRIMARY KEY,
ename VARCHAR(50),
salary int
);
CREATE TABLE Certified(
eid int,
aid int,
FOREIGN KEY (eid) REFERENCES Employees(eid),
FOREIGN KEY(aid) REFERENCES Aircraft(aid)
);
INSERT INTO Flights (flno, start, dest, distance, departs) VALUES
(101, 'New York', 'Los Angeles', 2450, '2025-03-10 08:00:00'),
(102, 'Chicago', 'Miami', 1180, '2025-03-11 12:30:00'),
(103, 'San Francisco', 'Seattle', 680, '2025-03-12 14:15:00'),
(104, 'Dallas', 'Denver', 780, '2025-03-13 09:45:00'),
(105, 'Boston', 'Houston', 1600, '2025-03-14 07:20:00');
INSERT INTO Aircraft (aid, aname, range) VALUES
(1, 'Boeing 737', 3000),
(2, 'Airbus A320', 3300),
(3, 'Boeing 747', 8000),
(4, 'Airbus A380', 8500),
(5, 'Embraer E190', 2500);
INSERT INTO Employees (eid, ename, salary) VALUES
(1, 'John Doe', 75000),
(2, 'Jane Smith', 80000),
(3, 'Alice Johnson', 70000),
(4, 'Robert Brown', 85000),
(5, 'Emily Davis', 90000);
INSERT INTO Certified (eid, aid) VALUES
(1, 1),
(1, 3),
(2, 2),
(3, 5),
(4, 4),
(5, 3),
(5, 2);
A. Find eid’s of pilots who are certified on some Boeing
SELECT DISTINCT eid
from Certified;
B. Find names of pilots who are certified on some Boeing.
SELECT ename
from Employees INNER JOIN Certified
ON Employees.eid = Certified.eid;
C. Find aid’s of aircraft that can fly non-stop from LA to NY.
SELECT aid
from Aircraft
WHERE range>4000;
Experiment-6
For the database created in ques 5, perform the following
queries:
Software Used: Jdoodle.com
a.Find names of pilots who can operate planes with a range
greater than 3,000 miles, but are not certified on any Boeing.
SELECT DISTINCT e.ename
FROM Employees e
JOIN Certified c ON e.eid = c.eid
JOIN Aircraft a ON c.aid = a.aid
WHERE a.range > 3000
AND e.eid NOT IN (
SELECT DISTINCT c.eid
FROM Certified c
JOIN Aircraft a ON c.aid = a.aid
WHERE a.aname LIKE 'Boeing%'
);
b.Find eid of employee(s) with the highest salary.
SELECT eid
FROM Employees
WHERE salary = (SELECT MAX(salary) FROM
Employees);
c. Write a query to change the size of the column, ‘salary’.
ALTER TABLE Employees
MODIFY COLUMN salary BIGINT;
EXPERIMENT-7
For the database created in ques 5, perform the following
queries:
Software Used:Jdoodle.com
a.Find eid of employee(s) with the second highest salary.
SELECT eid
FROM Employees
WHERE salary = (
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees)
);
b.Find eid’s of employees certified on exactly three aircraft.
SELECT eid
FROM Certified
GROUP BY eid
HAVING COUNT(aid) = 3;
Experiment-8
Create a concurrent schedule and show if it is conflict serializable.
Software Used: JDoodle.com
Code:
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance INT
);
INSERT INTO accounts (id, balance)
VALUES (1, 1000), (2, 2000);
//Transaction T1:
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- R1(A)
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- W1(A)
COMMIT;
// Transaction T2:
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- R2(A)
UPDATE accounts SET balance = balance - 50 WHERE id = 1; -- W2(A)
COMMIT;
CONFLICT ANALYSIS
Conflicts arise due to same data item accessed by different transactions where at least one
is a write.
Operations Conflict Type Direction
R1(A),W2(A) Read-Write T1-T2
R2(A),W1(A) Read-Write T2-T1
W1(A),W2(A) Write-Write T1-T2
Conclusion:
Since the precedence graph contains a cycle, the schedule is not conflict serializable.
EXPERIMENT-9
Create a concurrent schedule and change it into an equivalent serial
schedule. Write statements to check if the two schedules are
equivalent or not.
Software Used: JDoodle.com
Code:
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance INT
);
//Insert sample data
INSERT INTO accounts (id, balance)
VALUES (1, 1000), (2, 2000);
// Concurrent Schedule
// Transaction T1:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- W1(A)
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- W1(B)
COMMIT;
// Transaction T2:
BEGIN;
UPDATE accounts SET balance = balance * 2 WHERE id = 1; -- W2(A)
UPDATE accounts SET balance = balance + 200 WHERE id = 2; -- W2(B)
COMMIT;
Select * from accounts
// Serial Schedule
-- Reset table to initial state
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 2000 WHERE id = 2;
-- T1
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- 1000 → 1100
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- 2000 → 1950
-- T2
UPDATE accounts SET balance = balance * 2 WHERE id = 1; -- 1100 → 2200
UPDATE accounts SET balance = balance + 200 WHERE id = 2; -- 1950 → 2150
SELECT * FROM accounts;
Concurrent Transaction Serial Transaction
Both schedules give the same final state. Hence, they are equivalent.
EXPERIMENT-10
Create a transaction to demonstrate the use of locks in all the 4
modes.
Software Used : JDoodle.com
Code:
// Transaction T1 (Reads A1, then Updates A2)
-- Transaction T1 Starts
BEGIN TRANSACTION;
-- Intent Shared lock on table
-- Shared lock on row A1
SELECT Balance FROM Accounts WHERE AccountID = 'A1';
-- Intent Exclusive lock on table
-- Exclusive lock on row A2
UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 'A2';
COMMIT;
//Transaction T2 (Reads A2, then tries to update A1)
-- Transaction T2 Starts
BEGIN TRANSACTION;
-- IS lock on Accounts table
-- S lock on row A2
SELECT Balance FROM Accounts WHERE AccountID = 'A2';
-- IX lock on Accounts table
-- X lock on row A1
UPDATE Accounts
SET Balance = Balance - 200
WHERE AccountID = 'A1';
COMMIT;
Locks in T1:
• IS lock on Accounts table before reading A1
• S lock on row A1 for the read
• IX lock on Accounts table before updating A2
• X lock on row A2 for the update
Locks in T2:
• IS lock on Accounts table before reading A2
• S lock on row A2
• IX lock on Accounts table before updating A1
• X lock on row A1
