1.
a) Create an employee database table, add constraints (primary key, unique, check,
Not null), insert rows, update and delete rows using SQL DDL and DML commands.
Create Table
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gender CHAR(1) CHECK (gender IN ('M', 'F')),
salary DECIMAL(10,2) CHECK (salary >= 0)
);
Insert Rows
INSERT INTO Employee VALUES (1, 'Alice', 'alice@mail.com', 'F', 50000);
INSERT INTO Employee VALUES (2, 'Bob', 'bob@mail.com', 'M', 60000);
Update Row
UPDATE Employee SET salary = 65000 WHERE emp_id = 2;
Delete Row
DELETE FROM Employee WHERE emp_id = 1;
b) Write a PL SQL programs to hike the employee salary, those who are all working
under the production department by using where clause
BEGIN
UPDATE Employee
SET salary = salary * 1.10
WHERE department = 'Production';
END;
/
2)a) Create a set of tables for student database, add foreign key
constraints and incorporate referential integrity.
CREATE TABLE Department (
dept_id INT PRIMARY KEY
);
CREATE TABLE Student (
student_id INT PRIMARY KEY,
dept_id INT REFERENCES Department(dept_id)
);
CREATE TABLE Course (
course_id INT PRIMARY KEY
);
CREATE TABLE Enrollment (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
b) Write PL SQL Triggers for insertion, deletion operations in a student database table.
Insert Trigger
CREATE TRIGGER trg_ins
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserted: ' || :NEW.student_id);
END;
Delete Trigger
CREATE TRIGGER trg_del
AFTER DELETE ON Student
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Deleted: ' || :OLD.student_id);
END;
3. a) Write a query to display all books details related to ‘Database Management
Systems’ by using where clause.
-- Create Books table
CREATE TABLE Books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(50),
publisher VARCHAR(50),
price DECIMAL(8, 2)
);
-- Insert sample data
INSERT INTO Books VALUES (1, 'Database Management Systems', 'Ramakrishnan',
'McGraw Hill', 750.00);
INSERT INTO Books VALUES (2, 'Database Management Systems', 'Korth', 'McGraw Hill',
680.00);
INSERT INTO Books VALUES (3, 'Operating Systems', 'Silberschatz', 'Wiley', 850.00);
INSERT INTO Books VALUES (4, 'Computer Networks', 'Tanenbaum', 'Pearson', 900.00);
-- Query to fetch all books with title 'Database Management Systems'
SELECT * FROM Books
WHERE title = 'Database Management Systems';
b) Write a PL SQL Trigger program to update books details in library management
system after purchasing books
CREATE TABLE Books (book_id INT PRIMARY KEY, quantity INT);
CREATE TABLE Purchases (purchase_id INT PRIMARY KEY, book_id INT,
purchased_qty INT);
CREATE TRIGGER trg_update_quantity
AFTER INSERT ON Purchases
FOR EACH ROW
BEGIN
UPDATE Books SET quantity = quantity + :NEW.purchased_qty WHERE book_id
= :NEW.book_id;
END;
/
4. a) Create a Customer, Saving_account, Loan_account table in banking database by
using DDL and DML commands
CREATE TABLE Customer (
cust_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(100)
);
CREATE TABLE Saving_account (
account_no INT PRIMARY KEY,
cust_id INT,
balance DECIMAL(12, 2),
interest_rate DECIMAL(4, 2),
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
);
CREATE TABLE Loan_account (
loan_id INT PRIMARY KEY,
cust_id INT,
loan_amount DECIMAL(12, 2),
interest_rate DECIMAL(4, 2),
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
);
INSERT INTO Customer VALUES (1, 'John Doe', '123 Elm Street');
INSERT INTO Customer VALUES (2, 'Jane Smith', '456 Oak Avenue');
INSERT INTO Saving_account VALUES (1001, 1, 5000.00, 3.5);
INSERT INTO Saving_account VALUES (1002, 2, 10000.00, 4.0);
INSERT INTO Loan_account VALUES (2001, 1, 15000.00, 7.5);
INSERT INTO Loan_account VALUES (2002, 2, 25000.00, 8.0);
b) Write a procedure to insert 50 records for each table in above banking database
CREATE OR REPLACE PROCEDURE insert_50_records IS
BEGIN
FOR i IN 1..50 LOOP
-- Insert into Customer
INSERT INTO Customer (cust_id, name)
VALUES (i, 'Customer_' || i);
-- Insert into Saving_account
INSERT INTO Saving_account (account_no, cust_id, balance)
VALUES (1000 + i, i, 1000 + i * 100);
-- Insert into Loan_account
INSERT INTO Loan_account (loan_id, cust_id, loan_amount)
VALUES (2000 + i, i, 5000 + i * 200);
END LOOP;
COMMIT;
END;
BEGIN
insert_50_records;
END;
5.a) Create a student table, subject_ mark table, result table using DDL, DML
commands and also compute the minimum, maximum, total, average marks in the
above database.
-- Create tables
CREATE TABLE Student (student_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Subject_Mark (student_id INT, subject VARCHAR(50), marks INT);
CREATE TABLE Result (student_id INT PRIMARY KEY, total INT, avg DECIMAL(5,2));
-- Insert data
INSERT INTO Student VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Subject_Mark VALUES
(1, 'Math', 85), (1, 'Science', 90),
(2, 'Math', 70), (2, 'Science', 60);
-- Insert total and average
INSERT INTO Result
SELECT student_id, SUM(marks), AVG(marks)
FROM Subject_Mark GROUP BY student_id;
-- Show min, max, total, avg
SELECT MIN(marks), MAX(marks), SUM(marks), AVG(marks)
FROM Subject_Mark;
b) Write a user defined function to update and release the student’s result with
percentage.
ALTER TABLE Result ADD percentage DECIMAL(5,2);
CREATE FUNCTION update_percentage(p_id INT) RETURN DECIMAL IS
t INT; c INT; p DECIMAL(5,2);
BEGIN
SELECT SUM(marks), COUNT(*) INTO t, c FROM Subject_Mark WHERE student_id =
p_id;
p := (t / (c * 100.0)) * 100;
UPDATE Result SET percentage = p WHERE student_id = p_id;
RETURN p;
END;
SELECT update_percentage(1) FROM dual;
6 .a) Create railway reservation database using DDL, DML commands and also display
the train information using sub queries.
-- Tables
CREATE TABLE Train (train_id INT PRIMARY KEY, name VARCHAR(50), source
VARCHAR(50), dest VARCHAR(50));
CREATE TABLE Passenger (pid INT PRIMARY KEY, name VARCHAR(50), age INT);
CREATE TABLE Reservation (rid INT PRIMARY KEY, train_id INT, pid INT, seat INT);
-- Data
INSERT INTO Train VALUES (101, 'Express1', 'CityA', 'CityB'), (102, 'Express2', 'CityB',
'CityC');
INSERT INTO Passenger VALUES (1, 'Alice', 30), (2, 'Bob', 45);
INSERT INTO Reservation VALUES (1, 101, 1, 12), (2, 102, 2, 18);
-- Subquery: Trains booked by passengers age > 40
SELECT * FROM Train WHERE train_id IN (
SELECT train_id FROM Reservation WHERE pid IN (
SELECT pid FROM Passenger WHERE age > 40
)
);
b) Write PL SQL Triggers to display available seats after a successful reservation and
also display the available seats before reservation
CREATE TABLE Train (id INT PRIMARY KEY, avail INT);
CREATE TABLE Reservation (id INT PRIMARY KEY, train_id INT, seat_no INT);
INSERT INTO Train VALUES (1, 98);
CREATE OR REPLACE TRIGGER before_reservation
BEFORE INSERT ON Reservation
FOR EACH ROW
DECLARE v INT;
BEGIN
SELECT avail INTO v FROM Train WHERE id = :NEW.train_id;
DBMS_OUTPUT.PUT_LINE('Before: ' || v);
END;
CREATE OR REPLACE TRIGGER after_reservation
AFTER INSERT ON Reservation
FOR EACH ROW
DECLARE v INT;
BEGIN
UPDATE Train SET avail = avail -1 WHERE id = :NEW.train_id;
SELECT avail INTO v FROM Train WHERE id = :NEW.train_id;
DBMS_OUTPUT.PUT_LINE('After: ' || v);
END;
INSERT INTO Reservation VALUES (1,1,5);
7. a) Create a database for toy manufacturing company and display the products based
on the cost using having clause.
-- Create product table
CREATE TABLE Product (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
cost DECIMAL(10,2)
);
-- Insert sample data
INSERT INTO Product VALUES
(1, 'Toy Car', 150.00),
(2, 'Doll', 200.00),
(3, 'Puzzle', 150.00),
(4, 'Board Game', 300.00),
(5, 'Action Figure', 200.00);
-- Display products grouped by cost having cost > 150
SELECT cost, COUNT(*) AS product_count
FROM Product
GROUP BY cost
HAVING cost > 150;
b) Write a procedure to insert and update the records in the above database.
CREATE TABLE Product (id INT PRIMARY KEY, name VARCHAR(50), cost
DECIMAL);
INSERT INTO Product VALUES (1,'Toy Car',150), (2,'Doll',200);
CREATE OR REPLACE PROCEDURE upsert_product(p_id INT, p_name VARCHAR,
p_cost DECIMAL) IS
BEGIN
MERGE INTO Product p
USING (SELECT p_id AS id, p_name AS name, p_cost AS cost FROM dual) vals
ON (p.id = vals.id)
WHEN MATCHED THEN UPDATE SET name = vals.name, cost = vals.cost
WHEN NOT MATCHED THEN INSERT VALUES (vals.id, vals.name, vals.cost);
COMMIT;
END;
-- Example call
BEGIN
upsert_product(3,'Puzzle',180);
END;
8. Write a DCL command to allow the user to create, insert, update and delete
operation and also disallow the particular user to perform delete operation.
GRANT CREATE, INSERT, UPDATE, DELETE TO user1;
REVOKE DELETE FROM user1;
b) Create XML database to conduct online quiz and declare the quiz results
<Quiz>
<Q id="1">What is 2+2?<A>3</A><A>4</A><A>5</A><Answer>4</Answer></Q>
<Q id="2">Earth is flat?<Answer>False</Answer></Q>
<Results>
<R user="User1" score="2" status="Pass"/>
<R user="User2" score="1" status="Fail"/>
</Results>
</Quiz>
9. a) Write and execute complex transaction in a larger database and also realize TCL
commands.
BEGIN TRANSACTION;
SAVEPOINT sp_before;
UPDATE Accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 200 WHERE account_id = 2;
IF (SELECT balance FROM Accounts WHERE account_id = 1) < 0
ROLLBACK TO sp_before;
ELSE
COMMIT;
b) Create Document, column and graph based data using NOSQL database tools.
// MongoDB (Document)
db.products.insertOne({id:1, name:"Toy Car", price:150});
// Cassandra (Column)
CREATE TABLE products (id int PRIMARY KEY, name text, price decimal);
INSERT INTO products (id, name, price) VALUES (1, 'Toy Car', 150);
// Neo4j (Graph)
CREATE (p:Product {id:1, name:'Toy Car', price:150});
10.a) Create and insert records in student table and course table and also display the
records using different types of join operation.
-- Create tables
CREATE TABLE Student (student_id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE Course (course_id INT PRIMARY KEY, student_id INT, course_name
VARCHAR(50));
-- Insert data
INSERT INTO Student VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Course VALUES (101, 1, 'Math'), (102, 1, 'Science'), (103, 2, 'History');
-- INNER JOIN: students with courses
SELECT s.name, c.course_name
FROM Student s
JOIN Course c ON s.student_id = c.student_id;
-- LEFT JOIN: all students and their courses (if any)
SELECT s.name, c.course_name
FROM Student s
LEFT JOIN Course c ON s.student_id = c.student_id;
-- RIGHT JOIN: all courses and their students (if any)
SELECT s.name, c.course_name
FROM Student s
RIGHT JOIN Course c ON s.student_id = c.student_id;
-- FULL OUTER JOIN: all students and courses, matched or not
SELECT s.name, c.course_name
FROM Student s
FULL OUTER JOIN Course c ON s.student_id = c.student_id;
b) Write a procedure to display month’s name while passing the month as number
parameter. (Example: if pass parameter as 1 it should display as January
CREATE OR REPLACE PROCEDURE show_month(p NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(
CASE p
WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March'
WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September'
WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December'
ELSE 'Invalid'
END);
END;
BEGIN
show_month(1);
END;
SAMPLE VIVA QUESTIONS
1. Employee Table and Salary Hike
Q1: What constraints are added to the Employee table?
A1: PRIMARY KEY, UNIQUE, CHECK, and NOT NULL constraints.
Q2: How do you increase salary for employees in the production department?
A2: Use a PL/SQL block with UPDATE and WHERE department = 'Production'.
Q3: What does the CHECK constraint on gender do?
A3: Ensures gender is only ‘M’ or ‘F’.
Q4: Can two employees have the same email?
A4: No, email has a UNIQUE constraint.
Q5: How do you delete an employee record?
A5: Use DELETE FROM Employee WHERE emp_id = [value].
2. Student Database with Foreign Keys and Triggers
Q1: What is referential integrity?
A1: It ensures foreign key values match primary key values in referenced tables.
Q2: Which tables have foreign key constraints in the student database?
A2: Student (dept_id), Enrollment (student_id, course_id).
Q3: What does the insert trigger do?
A3: Prints a message with the inserted student_id.
Q4: When is the delete trigger activated?
A4: After a row is deleted from the Student table.
Q5: How do you define a composite primary key?
A5: By declaring multiple columns together as PRIMARY KEY, e.g. (student_id, course_id).
3. Books Table Query and Trigger for Purchase Updates
Q1: How do you select books titled ‘Database Management Systems’?
A1: Using SELECT * FROM Books WHERE title = 'Database Management Systems';.
Q2: What is the purpose of the purchase trigger?
A2: To update book quantity after purchase.
Q3: What columns are mandatory in the Books table?
A3: book_id (primary key), title, author, publisher, price.
Q4: How does the trigger update the book quantity?
A4: It adds the purchased quantity to the existing quantity.
Q5: Can the trigger run on updates?
A5: No, it is defined to run AFTER INSERT on Purchases only.
4. Banking Database Tables and Insert Procedure
Q1: Which tables are created in the banking database?
A1: Customer, Saving_account, Loan_account.
Q2: How is a foreign key used in Saving_account?
A2: cust_id references Customer(cust_id).
Q3: What does the insert_50_records procedure do?
A3: Inserts 50 records into Customer, Saving_account, and Loan_account tables.
Q4: How are account numbers generated in the procedure?
A4: As 1000 + loop index (i).
Q5: What command commits the inserts in the procedure?
A5: COMMIT;
5. Student, Subject_Mark, Result Tables and UDF for Percentage
Q1: How do you calculate total and average marks?
A1: Using SUM and AVG aggregate functions grouped by student_id.
Q2: What is the purpose of the update_percentage function?
A2: To compute and update the student’s result percentage.
Q3: Which tables store marks and results?
A3: Subject_Mark stores marks; Result stores totals, averages, and percentages.
Q4: How is the percentage calculated?
A4: (Total marks / (number of subjects * 100)) * 100.
Q5: How do you update the Result table with percentage?
A5: Using an UPDATE statement inside the function.
6. Railway Reservation Database and Seat Availability Triggers
Q1: What tables are created in the railway reservation system?
A1: Train, Passenger, Reservation.
Q2: How do you show trains booked by passengers over 40?
A2: Using nested subqueries filtering Passenger.age > 40.
Q3: When does the before_reservation trigger run?
A3: Before a new reservation is inserted.
Q4: What does the after_reservation trigger do?
A4: Decreases available seats and displays new availability.
Q5: How are available seats updated after reservation?
A5: By subtracting 1 from the Train.avail column.
7. Toy Manufacturing Company Database and Procedure
Q1: What does the HAVING clause do in the products query?
A1: Filters groups having cost greater than 150.
Q2: What columns are in the Product table?
A2: product_id, product_name, cost.
Q3: What does the upsert_product procedure do?
A3: Inserts or updates product records.
Q4: How is the merge operation used in the procedure?
A4: Matches existing product by id to update or inserts new if no match.
Q5: How do you call the upsert_product procedure?
A5: Using a BEGIN...END block with parameters.
8. DCL Commands and XML Quiz Database
Q1: How do you grant insert and update privileges to a user?
A1: GRANT INSERT, UPDATE TO username;
Q2: How do you revoke delete privilege from a user?
A2: REVOKE DELETE FROM username;
Q3: What XML element stores quiz questions?
A3: <Q> elements inside <Quiz> root.
Q4: How are quiz results recorded in XML?
A4: Inside <Results> element with <R> children for each user.
Q5: What is the purpose of the <Answer> tag in XML?
A5: To store the correct answer for each question.
9. Complex Transaction with TCL Commands and NoSQL Data
Q1: What TCL commands are used in the transaction?
A1: SAVEPOINT, ROLLBACK TO, COMMIT.
Q2: What happens if balance is negative after transfer?
A2: Transaction rolls back to savepoint.
Q3: How is a document stored in MongoDB?
A3: As a JSON-like object with db.collection.insertOne().
Q4: What type of NoSQL database is Cassandra?
A4: Column-based database.
Q5: What type of NoSQL database is Neo4j?
A5: Graph database.
10. Student and Course Tables with Join Queries and Month Procedure
Q1: What types of joins are used to display student-course data?
A1: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
Q2: How do you show courses for all students including those without courses?
A2: Use LEFT JOIN.
Q3: How does the show_month procedure work?
A3: Prints month name based on input number using CASE.
Q4: What happens if an invalid month number is passed?
A4: Displays ‘Invalid’.
Q5: How do you call the show_month procedure?
A5: Use BEGIN show_month(1); END;
