Pr.

1
Sample code:
CREATE TABLE users (

user_id SERIAL PRIMARY KEY,

first_name VARCHAR(100),

last_name VARCHAR(100),

email VARCHAR(255) UNIQUE NOT NULL,

password_hash VARCHAR(255) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE

CURRENT_TIMESTAMP,

status VARCHAR(50) DEFAULT 'active'

);

Output:

Column Data Type Constraints

user_id SERIAL PRIMARY KEY, auto-increment
first_name VARCHAR(100)
last_name VARCHAR(100)
email VARCHAR(255) UNIQUE, NOT NULL
password_hash VARCHAR(255) NOT NULL
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ON UPDATE
CURRENT_TIMESTAMP
status VARCHAR(50) DEFAULT 'active'
Pr.2

Sample code:
CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,

DepartmentName VARCHAR(100) NOT NULL

);

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50) NOT NULL,

DepartmentID INT,

CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES

Departments(DepartmentID)

);

ALTER TABLE Employees RENAME TO Staff;

INSERT INTO Departments (DepartmentID, DepartmentName)

VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');

INSERT INTO Staff (EmployeeID, FirstName, LastName, DepartmentID)

VALUES (101, 'John', 'Doe', 1), (102, 'Jane', 'Smith', 2), (103, 'Bob', 'Johnson', 3);

ALTER TABLE Staff ADD CONSTRAINT UQ_EmployeeName UNIQUE (FirstName,

LastName);

ALTER TABLE Staff ADD CONSTRAINT CHK_DepartmentID CHECK (DepartmentID > 0);
Output:

Departments Table:

DepartmentID DepartmentName
1 HR
2 IT
3 Finance

Staff Table:

EmployeeID FirstName LastName DepartmentID

101 John Doe 1
102 Jane Smith 2
103 Bob Johnson 3
Pr.3

Sample code:
-- Group data, aggregate functions: SUM, AVG, COUNT

SELECT

department_id,

COUNT(employee_id) AS employee_count, -- Count of employees per department

SUM(salary) AS total_salary, -- Total salary in each department

ROUND(AVG(salary), 2) AS avg_salary -- Avg. salary rounded to 2 decimal places

FROM

employees

GROUP BY

department_id;

-- Using Character Functions: UPPER, SUBSTR

SELECT

employee_id,

UPPER(first_name || ' ' || last_name) AS full_name_uppercase, -- Convert name to uppercase

SUBSTR(last_name, 1, 3) AS short_last_name -- Extract first 3 chars of last name

FROM

employees;

-- Combining Aggregate, Math, Character Functions

SELECT

department_id,

COUNT(employee_id) AS employee_count, -- Count employees per department

SUM(salary) AS total_salary, -- Total salary

ROUND(AVG(salary), 2) AS average_salary, -- Rounded average salary

 MIN(LENGTH(first_name || ' ' || last_name)) AS shortest_name_length -- Shortest name
length in dept

FROM

employees

GROUP BY

department_id;

output:

-- Group data, aggregate functions: SUM, AVG, COUNT

Department_ID Employee_Count Total_Salary Avg_Salary

10 5 40000 8000.00
20 8 60000 7500.00
30 3 35000 11666.67

-- Using Character Functions: UPPER, SUBSTR

Employee_ID Full_Name_Uppercase Short_Last_Name

101 JOHN DOE DOE
102 JANE SMITH SMI
103 ROBERT JOHNSON JOH

-- Combining Aggregate, Math, Character Functions

Department_ID Employee_Count Total_Salary Avg_Salary Shortest_Name_Length

10 5 40,000 8,000.00 8
20 8 60,000 7,500.00 9
30 3 35,000 11,666.67 10
Pr.4

Sample code:
-- Subquery Example: Find employees earning more than the average salary

SELECT employee_id, name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

-- Set Operations Example: Find employees in 'Sales' or 'Marketing'

(SELECT name FROM employees WHERE department = 'Sales')

UNION

(SELECT name FROM employees WHERE department = 'Marketing');

-- Join Example: Inner join employees and departments on department_id

SELECT e.name AS Employee, d.dept_name AS Department

FROM employees e

INNER JOIN departments d

ON e.department_id = d.department_id;

-- Left Join Example: Get all employees and their departments, even those without departments

SELECT e.name, d.dept_name

FROM employees e

LEFT JOIN departments d

ON e.department_id = d.department_id;

-- Correlated Subquery Example: Find employees earning more than the average salary in their
department

SELECT e1.name, e1.salary

FROM employees e1

WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id

= e2.department_id);
Output:

-- Subquery Example:

employee_id name salary

101 Alice 70,000
102 Bob 85,000
103 Charli 90,000
e

Set Operations Example:

name
Alice
Bob
Charli
e
Eve

-- Join Example:

Emplo Department
yee
Alice Sales
Bob Marketing
Charlie HR

-- Correlated Subquery Example:

name salary
Alice 70,000
Charlie 90,000

-- Left Join Example:

name dept_name
Alice Sales
Bob Marketing
Charlie HR
David NULL
Pr.5

Sample code:
-- 1. Create Database

CREATE DATABASE company_db;

USE company_db;

-- 2. Create Tables

CREATE TABLE departments (

department_id INT PRIMARY KEY,

dept_name VARCHAR(50) NOT NULL

);

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(100),

department_id INT,

salary DECIMAL(10, 2),

manager_id INT,

FOREIGN KEY (department_id) REFERENCES departments(department_id),

FOREIGN KEY (manager_id) REFERENCES employees(employee_id)

);

-- 3. Insert Data into Tables

INSERT INTO departments (department_id, dept_name)

VALUES (1, 'Sales'), (2, 'Marketing'), (3, 'HR');

INSERT INTO employees (employee_id, name, department_id, salary, manager_id)

VALUES

(101, 'Alice', 1, 70000, NULL),

(102, 'Bob', 2, 85000, 101),

(103, 'Charlie', 3, 90000, 101),

(104, 'David', 1, 60000, 102),

(105, 'Eve', NULL, 40000, 102); -- No department

-- 4. SQL Queries

-- Query 1: Retrieve all employees

SELECT * FROM employees;

-- Query 2: Employees earning more than 80,000

SELECT name, salary FROM employees WHERE salary > 80000;

-- Query 3: Employees with department names

SELECT e.name, d.dept_name

FROM employees e

LEFT JOIN departments d ON e.department_id = d.department_id;

-- 5. PL/SQL Procedure to Get High Earners

CREATE OR REPLACE PROCEDURE get_high_earning_employees (min_salary IN

NUMBER)

IS

CURSOR c_high_earners IS

SELECT name, salary FROM employees WHERE salary > min_salary;

BEGIN

FOR r_emp IN c_high_earners LOOP

DBMS_OUTPUT.PUT_LINE('Employee: ' || r_emp.name || ', Salary: ' || r_emp.salary);

END LOOP;

END;

/
-- Execute the PL/SQL Procedure

BEGIN

get_high_earning_employees(75000);

END;

-- 6. Advanced SQL Query

-- Employees earning more than their department's average salary

SELECT name, salary

FROM employees e1

WHERE salary > (SELECT AVG(salary)

FROM employees e2

WHERE e1.department_id = e2.department_id);

Output:

1.Retrieve all employee:

SELECT * FROM employees;

employee_id name department_id salary manager_id

101 Alice 1 70000 NULL
102 Bob 2 85000 101
103 Charlie 3 90000 101
104 David 1 60000 102
105 Eve NULL 40000 102

2. Employees earning more than 80,000

SELECT name, salary FROM employees WHERE salary > 80000;

name salary
Bob 85000
Charlie 90000
3: Employees with department names

SELECT e.name, d.dept_name

FROM employees e

LEFT JOIN departments d ON e.department_id = d.department_id;

name dept_name
Alice Sales
Bob Marketing
Charlie HR
David Sales
Eve NULL

4.Employees earning more than their department's average salary

name salary
Alice 70000
Charlie 90000
Pr.6

Sample code:
-- Create a table to log employee bonuses

CREATE TABLE employee_bonus_log (

employee_id INT,

bonus DECIMAL(10, 2),

log_date DATE DEFAULT SYSDATE

);

-- Trigger to log bonus when a new employee is inserted

CREATE OR REPLACE TRIGGER log_employee_bonus

AFTER INSERT ON employees

FOR EACH ROW

DECLARE

emp_bonus DECIMAL(10, 2);

BEGIN

-- Calculate 10% bonus for the new employee

emp_bonus := :NEW.salary * 0.10;

-- Insert the bonus into the log table

INSERT INTO employee_bonus_log(employee_id, bonus)

VALUES (:NEW.employee_id, emp_bonus);

END;

/
Output:

INSERT INTO employees (employee_id, name, department_id, salary, manager_id)

VALUES (106, 'Frank', 1, 80000, 101);

employee_id bonus log_date
106 8000.00 16-OCT-24
Pr.7

Sample code:
1. First Normal Form (1NF)

CREATE TABLE Orders (

order_id INT PRIMARY KEY,

customer_name VARCHAR(100),

product_name VARCHAR(100) -- Atomic values

);

INSERT INTO Orders (order_id, customer_name, product_name) VALUES

(1, 'Alice', 'Laptop'),

(2, 'Bob', 'Smartphone'),

(3, 'Charlie', 'Tablet'),

(4, 'David', 'Headphones');

Output:

order_id customer_name product_name

1 Alice Laptop
2 Bob Smartphone
3 Charlie Tablet
4 David Headphones

2. Second Normal Form (2NF)

CREATE TABLE Customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(100)

);

CREATE TABLE Orders (

order_id INT,
 product_id INT,

customer_id INT,

PRIMARY KEY (order_id, product_id),

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);

INSERT INTO Customers (customer_id, customer_name) VALUES (1, 'Alice'), (2, 'Bob'), (3,
'Charlie');

Output:

customer_id customer_name
1 Alice
2 Bob
3 Charlie

3. Third Normal Form (3NF)

-- Create Customers Table

CREATE TABLE Customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(100)

);

-- Create Orders Table

CREATE TABLE Orders (

order_id INT PRIMARY KEY,

customer_id INT,

product_id INT,

FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)

);
-- Insert Sample Data into Customers Table

INSERT INTO Customers (customer_id, customer_name) VALUES

(1, 'Alice'),

(2, 'Bob'),

(3, 'Charlie');

-- Insert Sample Data into Orders Table

INSERT INTO Orders (order_id, customer_id, product_id) VALUES

(101, 1, 1), -- Order by Alice

(102, 1, 2), -- Another order by Alice

(103, 2, 1), -- Order by Bob

(104, 3, 3); -- Order by Charlie

Output:

order_id customer_id product_id

101 1 1
102 1 2
103 2 1
104 3 3

4.Boyce-Codd Normal Form (BCNF)

-- Create Instructors Table

CREATE TABLE Instructors (

instructor_id INT PRIMARY KEY,

instructor_name VARCHAR(100)

);

-- Create Courses Table

CREATE TABLE Courses (

course_id INT PRIMARY KEY,

 instructor_id INT,

FOREIGN KEY (instructor_id) REFERENCES Instructors(instructor_id)

);

-- Insert Sample Data into Instructors Table

INSERT INTO Instructors (instructor_id, instructor_name) VALUES

(1, 'Dr. Smith'),

(2, 'Prof. Johnson'),

(3, 'Ms. Davis');

-- Insert Sample Data into Courses Table

INSERT INTO Courses (course_id, instructor_id) VALUES

(101, 1), -- Course taught by Dr. Smith

(102, 1), -- Another course taught by Dr. Smith

(103, 2), -- Course taught by Prof. Johnson

(104, 3); -- Course taught by Ms. Davis

Output:

instructor_id instructor_name
1 Dr. Smith
2 Prof. Johnson
3 Ms. Davis

course_id instructor_id
101 1
102 1
103 2
104 3
Pr.8

Sample code:
CREATE DATABASE HotelManagement;

USE HotelManagement;

CREATE TABLE Rooms (

room_id INT AUTO_INCREMENT PRIMARY KEY,

room_number VARCHAR(10) NOT NULL,

room_type VARCHAR(20),

price_per_night DECIMAL(10, 2),

availability_status BOOLEAN DEFAULT TRUE

);

CREATE TABLE Guests (

guest_id INT AUTO_INCREMENT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

contact_info VARCHAR(100),

email VARCHAR(100),

address TEXT

);

CREATE TABLE Reservations (

reservation_id INT AUTO_INCREMENT PRIMARY KEY,

guest_id INT,

room_id INT,

check_in_date DATE,

check_out_date DATE,

total_amount DECIMAL(10, 2),

 reservation_status VARCHAR(20),

FOREIGN KEY (guest_id) REFERENCES Guests(guest_id),

FOREIGN KEY (room_id) REFERENCES Rooms(room_id)

);

CREATE TABLE Payments (

payment_id INT AUTO_INCREMENT PRIMARY KEY,

reservation_id INT,

payment_date DATE,

payment_amount DECIMAL(10, 2),

payment_method VARCHAR(50),

FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id)

);

CREATE TABLE Employees (

employee_id INT AUTO_INCREMENT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

job_role VARCHAR(50),

contact_info VARCHAR(100),

salary DECIMAL(10, 2)

);

CREATE TABLE Services (

service_id INT AUTO_INCREMENT PRIMARY KEY,

service_name VARCHAR(50),

service_price DECIMAL(10, 2)

);

INSERT INTO Guests (first_name, last_name, contact_info, email, address)

VALUES ('John', 'Doe', '123-456-7890', 'john.doe@example.com', '123 Elm Street');

INSERT INTO Rooms (room_number, room_type, price_per_night)

VALUES ('101', 'Deluxe', 150.00);

INSERT INTO Reservations (guest_id, room_id, check_in_date, check_out_date, total_amount,

reservation_status)

VALUES (1, 1, '2024-10-20', '2024-10-25', 750.00, 'Confirmed');

INSERT INTO Payments (reservation_id, payment_date, payment_amount, payment_method)

VALUES (1, '2024-10-20', 750.00, 'Credit Card');

Output:

reservation guest_i room_i check_in_d check_out_d total_amou reservation_sta

_id d d ate ate nt tus
1 1 1 2024-10-20 2024-10-25 750.00 Confirmed
Pr.9

Sample code:
CREATE DATABASE HotelManagement; USE HotelManagement;

CREATE TABLE Rooms (room_id INT AUTO_INCREMENT PRIMARY KEY, room_number

VARCHAR(10), room_type VARCHAR(20), price_per_night DECIMAL(10, 2), availability_status
BOOLEAN DEFAULT TRUE);

CREATE TABLE Guests (guest_id INT AUTO_INCREMENT PRIMARY KEY, first_name

VARCHAR(50), last_name VARCHAR(50), contact_info VARCHAR(100), email VARCHAR(100),
address TEXT);

CREATE TABLE Reservations (reservation_id INT AUTO_INCREMENT PRIMARY KEY, guest_id

INT, room_id INT, check_in_date DATE, check_out_date DATE, total_amount DECIMAL(10, 2),
reservation_status VARCHAR(20), FOREIGN KEY (guest_id) REFERENCES Guests(guest_id),
FOREIGN KEY (room_id) REFERENCES Rooms(room_id));

CREATE TABLE Payments (payment_id INT AUTO_INCREMENT PRIMARY KEY, reservation_id

INT, payment_date DATE, payment_amount DECIMAL(10, 2), payment_method VARCHAR(50),
FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id));

CREATE TABLE Employees (employee_id INT AUTO_INCREMENT PRIMARY KEY, first_name

VARCHAR(50), last_name VARCHAR(50), job_role VARCHAR(50), contact_info VARCHAR(100),
salary DECIMAL(10, 2));

CREATE TABLE Services (service_id INT AUTO_INCREMENT PRIMARY KEY, service_name

VARCHAR(50), service_price DECIMAL(10, 2));

INSERT INTO Guests (first_name, last_name, contact_info, email, address) VALUES ('John', 'Doe',
'123-456-7890', 'john.doe@example.com', '123 Elm Street');

INSERT INTO Rooms (room_number, room_type, price_per_night) VALUES ('101', 'Deluxe', 150.00);

INSERT INTO Reservations (guest_id, room_id, check_in_date, check_out_date, total_amount,

reservation_status) VALUES (1, 1, '2024-10-20', '2024-10-25', 750.00, 'Confirmed');

INSERT INTO Payments (reservation_id, payment_date, payment_amount, payment_method) VALUES

(1, '2024-10-20', 750.00, 'Credit Card');

Output:

reserv gues room roo check check total_ reserva paym payme payme
ation_ t_na _num m_t _in_d _out_ amou tion_st ent_d nt_amo nt_met
id me ber ype ate date nt atus ate unt hod
1 John 101 Delu 2024- 2024- 750.0 Confir 2024- 750.00 Credit
Doe xe 10-20 10-25 0 med 10-20 Card
Pr.9

Sample code:

CREATE DATABASE HotelManagementLOB;

USE HotelManagementLOB;

-- Table to store LOB types (CLOB, NCLOB, BLOB, BFILE)

CREATE TABLE Documents (

doc_id INT AUTO_INCREMENT PRIMARY KEY,

doc_name VARCHAR(100),

doc_description CLOB, -- Character Large Object

doc_content BLOB, -- Binary Large Object

multilingual_desc NCLOB, -- National Character Large Object

doc_file BFILE -- Binary File

);

-- Insert example data

INSERT INTO Documents (doc_name, doc_description, doc_content, multilingual_desc,

doc_file)

VALUES ('Guest Contract',

'This document contains the guest contract details',

LOAD_FILE('/path/to/image.jpg'), -- Simulating BLOB file loading

N'This is a multilingual description',

BFILENAME('my_directory', 'contract.pdf')); -- BFILE reference

-- Query to select LOB data

SELECT doc_id, doc_name, doc_description, LENGTH(doc_content) AS blob_size,

multilingual_desc, doc_file

FROM Documents;
 -- Setup for BFILE access (Assuming Directory Object already created)

CREATE OR REPLACE DIRECTORY my_directory AS '/path/to/files';

-- Example of how to use BLOB or CLOB with updates

UPDATE Documents SET doc_description = 'Updated guest contract details',

doc_content = LOAD_FILE('/path/to/newimage.jpg')

WHERE doc_id = 1;

-- Delete a document entry

DELETE FROM Documents WHERE doc_id = 1;

Output:

doc_id doc_name doc_description blob_size multilingual_desc doc_file

1 Guest This document 5242880 This is a /path/to/files/contract.

Contract contains the multilingual pdf
guest contract description