Dr.

SNS RAJALAKSHMI COLLEGE OF ARTS &

SCIENCE (AUTONOMOUS)
Re-accredited with ‘A+’ Grade by NAAC,
Recognized by UGC & Approved by AICTE, New Delhi
and
Affilicated to Bharathiar
University, COIMBATORE-
641049

2023 -2024

DEPARTMENT OF COMPUTER
SCIENCE (GRAPHICS AND CREATIVE
DESIGN )

Certified that this Bonafide record Work done by

Name:

Class:

Subject:
Dr. SNS RAJALAKSHMI COLLEGE OF ARTS &
SCIENCE (AUTONOMOUS)
Re-accredited with ‘A+’ Grade by NAAC,
Recognized by UGC & Approved by AICTE, New Delhi
and
Affilicated to Bharathiar
University, COIMBATORE-
641049

DEPARTMENT OF COMPUTER
SCIENCE (GRAPHICS AND CREATIVE
DESIGN )

Certified that this Bonafide record Work done by

Mr./Ms.

Register Number:-

23UCU559: COMPUTER LABORATORY – IV : Relational Database

Management System

Staff- In charge HOD

Submitted for B.Sc(GCD) Degree Fourth Semester Practical Examination

held on:

INTERNAL EXAMINER EXTERNAL EXAMINER

INDEX

S.No DATE CONCEPTS PAGE SIGNATURE

No.

1 DDL & DML Commands

2 SQL Special Operators

3 Aggregate Functions

4 Functions

5 SQL Joins

6 Sub Queries

7 DCL and TCL Commands

8 Sequences and Views

9 Exception Handling

10 Triggers
ExNo:1(A)
1. DDL Commands
Date:

AIM:

ALGORITHM:
1. DDL Commands

PROGRAM:
--Create Table
SQL> CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50), LastName VARCHAR(50), Age INT,
Department VARCHAR(50));

Table created.
--Insert Data into Table
SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,
Age, Department)VALUES (1, 'John', 'Doe', 30, 'HR');

1 row created.

SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,

Age, Department)VALUES (2, 'Jane', 'Smith', 28, 'IT');

1 row created.

SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,

Age, Department)VALUES (3, 'Bob', 'Johnson', 35, 'Finance');

1 row created.

SQL> SELECT * FROM Employees;

OUTPUT:
EmployeeID FirstName LastName Age Department
1 John Doe 30 HR
2 Jane Smith 28 IT
3 Bob Johnson 35 Finance

--Table Alter
SQL> ALTER TABLE Employees ADD Email VARCHAR(100);

Table altered.
SQL> SELECT * FROM Employees;

EmployeeID FirstName LastName Age Department Email

1 John Doe 30 HR
2 Jane Smith 28 IT
3 Bob Johnson 35 Finance

---TRUNCATE TABLE
SQL> TRUNCATE TABLE Employees;

Table truncated.

SQL> SELECT * FROM

Employees; no rows selected

--- DROP TABLE

SQL> DROP TABLE

Employees; Table dropped.

ExNo:1(B)
2. DML Commands
Date:

AIM:

ALGORITHM:
1. DML Commands

PROGRAM:
--Create Table

SQL> CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName

VARCHAR(50), LastName VARCHAR(50), Age INT, Grade VARCHAR(2)
);

Table created.

--Insert Data into Table

SQL> INSERT INTO Students (StudentID, FirstName, LastName, Age,
Grade) VALUES (1, 'Alice', 'Johnson', 20, 'A') ;

1 row created.

SQL> INSERT INTO Students (StudentID, FirstName, LastName,

Age, Grade)VALUES (2, 'Bob', 'Smith', 22, 'B');

1 row created.

SQL> INSERT INTO Students (StudentID, FirstName, LastName, Age, Grade) values
(3, 'Charlie', 'Brown', 21, 'C');

1 row created.

SQL> SELECT * FROM Students;

STUDENTID FIRSTNAME LASTNAME AGE GR

1 Alice Johnson 20 A
2 Bob Smith 22 B
3 Charlie Brown 21 C

--Update Table
SQL> UPDATE
Students 2 SET Grade =
'A'
3 WHERE StudentID = 2;

1 row updated.
SQL> SELECT * FROM Students;

STUDENTID FIRSTNAME LASTNAME AGE GR

1 Alice Johnson 20 A
2 Bob Smith 22 A
3 Charlie Brown 21 C

-- INSERT INTO (additional record)

SQL> INSERT INTO Students (StudentID, FirstName, LastName, Age,
Grade) VALUE (4, 'David', 'Williams', 23, 'B');

1 row created.

-- SELECT to view the new data

SQL> SELECT * FROM Students;

STUDENTID FIRSTNAME LASTNAME AGE GR

1 Alice Johnson 20 A
2 Bob Smith 22 A
3 Charlie Brown 21 C
4 David Williams 23 B

-- DELETE
SQL> DELETE FROM Students
2 WHERE StudentID = 3;

1 row deleted.

SQL> SELECT * FROM Students;

STUDENTID FIRSTNAME LASTNAME AGE GR

1 Alice Johnson 20 A
2 Bob Smith 22 A
4 David Williams 23 B
SQL> UPDATE
Students 2 SET AGE =
25
3 WHERE StudentID = 1;

1 row updated.

SQL> SELECT * FROM Students;

STUDENTID FIRSTNAME LASTNAME AGE GR

1 Alice Johnson 25 A
2 Bob Smith 22 A
4 David Williams 23 B
ExNo:2
SQL SPECIAL
Date: OPERATORS

AIM:

ALGORITHM:
2. SQL SPECIAL OPERATORS

PROGRAM:
--Create Table

SQL> CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName

VARCHAR(50), Price DECIMAL(8, 2), Category VARCHAR(50) );

Table created.

--Insert Data into Table

SQL> INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (1, 'Laptop', 1200.00, 'Electronics') ;

1 row created.

SQL> INSERT INTO Products (ProductID, ProductName, Price, Category)

2 VALUES (2, 'Smartphone', 800.00, 'Electronics')

; 1 row created.

SQL> INSERT INTO Products (ProductID, ProductName, Price, Category)

2 VALUES (3, 'Desk Chair', 150.00,

'Furniture'); 1 row created.

SQL> INSERT INTO Products (ProductID, ProductName, Price, Category)

2 VALUES (4, 'Coffee Table', 200.00,

'Furniture'); 1 row created.

SQL> INSERT INTO Products (ProductID, ProductName, Price, Category)

2 VALUES (5, 'Running Shoes', 80.00,

'Apparel'); 1 row created.

-- SELECT using LIKE operator

SQL> SELECT * FROM Products WHERE ProductName LIKE 'Desk%';

PRODUCTID PRODUCTNAME PRICE CATEGORY

3 Desk Chair 150 Furniture

-
- SELECT using IN operator

SQL> SELECT * FROM Products WHERE Category IN ('Electronics', 'Furniture');

PRODUCTID PRODUCTNAME PRICE CATEGORY

1 Laptop 1200 Electronics
2 Smartphone 800 Electronics
3 Desk Chair 150 Furniture
4 Coffee Table 200 Furniture

-- SELECT using BETWEEN operator

SQL> SELECT * FROM Products WHERE Price BETWEEN 100.00 AND 500.00;

PRODUCTID PRODUCTNAME PRICE CATEGORY

3 Desk Chair 150 Furniture
4 Coffee Table 200 Furniture

-- SELECT using IS NULL operator

SQL> SELECT * FROM Products WHERE Category IS

NULL; no rows selected

-- SELECT with ORDER BY

SQL> SELECT * FROM Products ORDER BY Price DESC;

PRODUCTID PRODUCTNAME PRICE CATEGORY

1 Laptop 1200 Electronics
2 Smartphone 800 Electronics
3 Desk Chair 150 Furniture
4 Coffee Table 200 Furniture

5 Running Shoes 80 Apparel

ExNo:3
AGGREGATE FUNCTIONS
Date:

AIM:

ALGORITHM:
3. AGGREGATE FUNCTIONS

PROGRAM:

-- CREATE TABLE
SQL> CREATE TABLE Sales ( SaleID INT PRIMARY KEY, ProductName
VARCHAR(50), Quantity INT, Price DECIMAL(8, 2));

Table created.

-- INSERT INTO
SQL> INSERT INTO Sales (SaleID, ProductName, Quantity,
Price) 2 VALUES (1, 'Laptop', 2, 1200.00);

1 row created.

SQL> INSERT INTO Sales (SaleID, ProductName, Quantity,

Price) 2 VALUES (2, 'Smartphone', 3, 800.00);

1 row created.

SQL> INSERT INTO Sales (SaleID, ProductName, Quantity,

Price) 2 VALUES (3, 'Desk Chair', 1, 150.00);

1 row created.

SQL> INSERT INTO Sales (SaleID, ProductName, Quantity,

Price) 2 VALUES (4, 'Coffee Table', 2, 200.00);

1 row created.

SQL> INSERT INTO Sales (SaleID, ProductName, Quantity,

Price) 2 VALUES (5, 'Running Shoes', 5, 80.00);

1 row created.

SQL> Select * from Sales;

SALEID PRODUCTNAME QUANTITY PRICE

1 Laptop 2 1200
2 Smartphone 3 800
3 Desk Chair 1 150
4 Coffee Table 2 200
5 Running Shoes 5 80
-- SELECT with COUNT
SQL> SELECT COUNT(*) AS TotalSales FROM Sales;

TOTALSALES
5

-- SELECT with SUM

SQL> SELECT SUM(Quantity) AS TotalQuantity, SUM(Price) AS TotalRevenue
FROM Sales;

TOTALQUANTITY TOTALREVENUE
13 2430

-- SELECT with AVG

SQL> SELECT AVG(Price) AS AveragePrice FROM Sales;

AVERAGEPRICE

486

-- SELECT with MIN

SQL> SELECT MIN(Price) AS MinPrice FROM Sales;

MINPRICE
80

-- SELECT with MAX

SQL> SELECT MAX(Price) AS MaxPrice FROM Sales;

MAXPRICE
1200
ExNo:4

Date: FUNCTIONS

AIM:

ALGORITHM:
4. FUNCTIONS

PROGRAM:

-- CREATE TABLE
SQL> CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50), LastName VARCHAR(50), Salary DECIMAL(10,
2));

Table created.

-- INSERT INTO
SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,
Salary) VALUES (1, 'John', 'Doe', 50000.50);

1 row created.

SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,

Salary) VALUES (2, 'Jane', 'Smith', 60000.75);

1 row created.

SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,

Salary) VALUES (3, 'Bob', 'Johnson', 75000.25);

1 row created.

SQL> INSERT INTO Employees (EmployeeID, FirstName, LastName,

Salary) VALUES (4, 'Sam', 'John', 90000.25);

1 row created.

-- SELECT with mathematical functions

SQL> SELECT ROUND(Salary, 1) AS RoundedSalary, ABS(Salary) AS
AbsoluteSalary FROM Employees;

ROUNDEDSALARY ABSOLUTESALARY

50000.5 50000.5
60000.7 60000.75
75000.2 75000.25
90000.2 90000.25
-- SELECT with custom function using CASE
SQL> SELECT FirstName, LastName, Salary, CASE
WHEN Salary < 60000 THEN 'Low' WHEN Salary >= 60000 AND Salary < 80000
THEN 'Medium' ELSE 'High' END AS SalaryCategory FROM Employees;

FIRSTNAME LASTNAME SALARY SALARY

------------------- --------------------- ---------- ------
John Doe 50000.5 Low

Jane Smith 60000.75 Medium

Bob Johnson 75000.25 Medium

Sam John 90000.25 High

ExNo:5
SQL JOINS
Date:

AIM:

ALGORITHM:
5. SQL JOINS
PROGRAM:

--Table Create
SQL> CREATE TABLE States ( StateID INT PRIMARY KEY, StateName
VARCHAR(50) );

Table created.

SQL> CREATE TABLE Cities ( CityID INT PRIMARY KEY, CityName

VARCHAR(50), StateID INT, FOREIGN KEY (StateID)
REFERENCES
States(StateID));

Table created.

-- INSERT DATA into States table

SQL> INSERT INTO States (StateID, StateName) VALUES (1,

'Maharashtra'); 1 row created.

SQL> INSERT INTO States (StateID, StateName) VALUES (2, 'Gujarat');

1 row created.

SQL> INSERT INTO States (StateID, StateName) VALUES (3, 'Tamil

Nadu'); 1 row created.

-- INSERT DATA into Cities table

SQL> INSERT INTO Cities (CityID, CityName, StateID) VALUES (101, 'Mumbai',

1); 1 row created.

SQL> INSERT INTO Cities (CityID, CityName, StateID) VALUES (102, 'Pune',

1); 1 row created.

SQL> INSERT INTO Cities (CityID, CityName, StateID) VALUES (103, 'Ahmedabad',

2); 1 row created.

SQL> INSERT INTO Cities (CityID, CityName, StateID) VALUES (104, 'Surat',

2); 1 row created.

SQL> INSERT INTO Cities (CityID, CityName, StateID) VALUES (105, 'Chennai', 3);

1 row created.
SQL> INSERT INTO Cities (CityID, CityName, StateID) VALUES (106, 'Coimbatore', 3);

1 row created.

-- INNER JOIN
SQL> SELECT Cities.CityName, States.StateName FROM Cities INNER JOIN States
ON Cities.StateID = States.StateID;

CITYNAME STATENAME
-
- Mumbai
Maharashtra

Pune Maharashtra

Ahmedabad Gujarat

Surat Gujarat

Chennai Tamil Nadu

Coimbatore Tamil

Nadu 6 rows selected.

-- LEFT JOIN
SQL> SELECT Cities.CityName, States.StateName FROM Cities LEFT JOIN States ON
Cities.StateID = States.StateID;

CITYNAME STATENAME
-
Mumbai Maharashtra

Pune Maharashtra

Ahmedabad Gujarat

Surat Gujarat

Chennai Tamil Nadu

Coimbatore Tamil
Nadu 6 rows selected.
-- RIGHT JOIN
SQL> SELECT Cities.CityName, States.StateName FROM Cities RIGHT JOIN States
ON Cities.StateID = States.StateID;

CITYNAME STATENAME
Mumbai Maharashtra

Pune Maharashtra

Ahmedabad Gujarat

Surat Gujarat

Chennai Tamil Nadu

Coimbatore Tamil

Nadu 6 rows selected.

-- FULL OUTER JOIN (Not supported in all databases)

SQL> SELECT Cities.CityName, States.StateName 2 FROM Cities
3 FULL OUTER JOIN States ON Cities.StateID = States.StateID;

CITYNAME STATENAME
-
Mumbai Maharashtra

Pune Maharashtra

Ahmedabad Gujarat

Surat Gujarat

Chennai Tamil Nadu

Coimbatore Tamil

Nadu 6 rows selected.

ExpNo:6
SUB QUERIES
Date:

AIM:

ALGORITHM:
6. SUB QUERIES

PROGRAM

-- CREATE TABLES
SQL> CREATE TABLE Countries ( CountryID INT PRIMARY KEY, CountryName
VARCHAR(50) );

SQL> CREATE TABLE Cities ( CityID INT PRIMARY KEY, CityName VARCHAR(50),
CountryID INT, Population INT, FOREIGN KEY (CountryID) REFERENCES
Countries(CountryID) );

-- INSERT DATA into Countries table

SQL> INSERT INTO Countries (CountryID, CountryName) VALUES (1, 'United
States');

1 row created.

SQL> INSERT INTO Countries (CountryID, CountryName) VALUES (2, 'United Kingdom');

1 row created.

SQL> INSERT INTO Countries (CountryID, CountryName) VALUES (3, 'India');

1 row created.

-- INSERT DATA into Cities table

SQL> INSERT INTO Cities (CityID, CityName, CountryID, Population) VALUES

(101, 'New York', 1, 8500000);

1 row created.

SQL> INSERT INTO Cities (CityID, CityName, CountryID, Population) VALUES

(102, 'London', 2, 8200000);

1 row created.

SQL> INSERT INTO Cities (CityID, CityName, CountryID, Population) VALUES

(103, 'Mumbai', 3, 12400000);

1 row created.
SQL> INSERT INTO Cities (CityID, CityName, CountryID, Population) VALUES
(104, 'Los Angeles', 1, 3980000);
1 row created.

SQL> INSERT INTO Cities (CityID, CityName, CountryID, Population) VALUES

(105, 'Delhi', 3, 28700000);

1 row created.

SQL> INSERT INTO Cities (CityID, CityName, CountryID, Population) VALUES

(106, 'Manchester', 2, 550000);

1 row created.
SQL> select * from Countries;

COUNTRYID COUNTRYNAME
1 United States
2 United Kingdom
3 India

SQL> select * from Cities;

CITYID CITYNAME COUNTRYID POPULATION

101 New York 1 8500000

102 London 2 8200000

103 Mumbai 3 12400000

104 Los Angeles 1 3980000

105 Delhi 3 28700000

106 Manchester 2 550000

-- SUBQUERY TO GET COUNTRIES WITH POPULATION GREATER
THAN

SQL> SELECT CountryName 2 FROM Countries 3 WHERE CountryID IN (

SELECT CountryID FROM Cities WHERE Population > (SELECT
AVG(Population) FROM Cities) );

COUNTRYNAME

- India
ExpNo:7(A)
DCL & TCL
Date: COMMANDS

AIM:

ALGORITHM:
7. DCL COMMANDS

PROGRAM:

SQL> CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName

VARCHAR(50), LastName VARCHAR(50), Age INT, GPA DECIMAL(3,
2));

Table created.

-- INSERT DATA
SQL> INSERT INTO Students (StudentID, FirstName, LastName, Age,
GPA) VALUES (1, 'John', 'Doe', 20, 3.5);

1 row created.

SQL> INSERT INTO Students (StudentID, FirstName, LastName, Age,

GPA) VALUES (2, 'Jane', 'Smith', 22, 3.9);

1 row created.

SQL> INSERT INTO Students (StudentID, FirstName, LastName, Age,

GPA) VALUES (3, 'Bob', 'Johnson', 21, 3.2);

1 row created.

SQL> SELECT * FROM Students;

STUDENTID FIRSTNAME LASTNAME AGE GPA

- - -
1 John Doe 20 3.5

2 Jane Smith 22 3.9

3 Bob Johnson 21 3.2

--COMMAND:GRANT
SQL>GRANT SELECT ON employees TO username;

--INPUT:
SELECT * FROM employees;
--OUTPUT:
employee_id | employee_name | salary
+- -+
1 | John Doe | 50000.00
2 | Jane Smith | 60000.00
3 | Bob Johnson | 75000.00

--COMMAND:REVOKE
REVOKE SELECT ON employees FROM username;
ExpNo:7(B)
TCL COMMANDS
Date:

AIM:

ALGORITHM:
7. TCL COMMANDS

PROGRAM:
--Create TABLE:
SQL>CREATE TABLE products ( product_id INT PRIMARY KEY, product_name
VARCHAR(50), price DECIMAL(8, 2) );

SQL> BEGIN;

--INSERT VALUE:
SQL>INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200.00);

1 row created.

SQL>INSERT INTO products (product_id, product_name, price) VALUES

(2, 'Smartphone', 500.00);

1 row created.

SQL>INSERT INTO products (product_id, product_name, price) VALUES (3,

'Headphones', 80.00);

1 row created.

INPUT:
SQL> SELECT * FROM
products; SQL> COMMIT;

OUTPUT:
product_id product_name price
1 Laptop 1200
2 Smartphone 500
ExpNo:8
SEQUENCES AND VIEWS
Date:

AIM:

ALGORITHM:
8. SEQUENCES AND VIEWS

PROGRAM:
--Create Table

SQL> CREATE TABLE EMPLOYEE ( EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50), LastName VARCHAR(50), Department
VARCHAR(50), Salary DECIMAL(10, 2) );

Table created.

--Insert Data into Table

SQL> INSERT INTO EMPLOYEE (EmployeeID, FirstName, LastName, Department,
Salary) VALUES (1, 'John', 'Doe', 'HR', 50000.00);

1 row created.

SQL> INSERT INTO EMPLOYEE (EmployeeID, FirstName, LastName,

Department, Salary)VALUES (2, 'Jane', 'Smith', 'IT', 60000.00);

1 row created.

SQL> INSERT INTO EMPLOYEE (EmployeeID, FirstName, LastName,

Department, Salary)VALUES (3, 'Bob', 'Johnson', 'Finance', 75000.00);

1 row created.

--Sequence command
SQL> CREATE SEQUENCE emp_sequence
START WITH 1001 INCREMENT BY 1 NOCACHE NOCYCLE;

Sequence created.

SQL> INSERT INTO EMPLOYEE (EmployeeID, FirstName, LastName,

Department, Salary) VALUES (emp_sequence.NEXTVAL, 'Alice', 'Williams',
'Marketing', 55000.00);

1 row created.

SQL> CREATE VIEW EmployeeSummary AS SELECT EmployeeID, FirstName,

LastName, Department, Salary FROM EMPLOYEE WHERE Salary > 60000.00;

View created.
SQL> SELECT * FROM EmployeeSummary;

EMPLOYEEID FIRSTNAME LASTNAME DEPARTMENT SALARY

3 Bob Johnson Finance 75000
ExNo:9
EXCEPTION
Date: HANDLING

AIM:

ALGORITHM:
9. EXCEPTION HANDLING

PROGRAM:

-- CREATE TABLE
CREATE TABLE SALES ( SaleID INT PRIMARY KEY, ProductName
VARCHAR(50), Quantity INT, Price DECIMAL(8, 2));

SQL> CREATE TABLE SALES ( SaleID INT PRIMARY KEY, ProductName

VARCHAR(50), Quantity INT, Price DECIMAL(8, 2));

Table created.

SQL> INSERT INTO SALES (SaleID, ProductName, Quantity, Price) VALUES (1,
'Laptop', 'abc', 1200.00);

VALUES (1, 'Laptop', 'abc', 1200.00)

*
ERROR at line
2:
ORA-01722: invalid number

SQL> BEGIN TRY

INSERT INTO SALES (SaleID, ProductName, Quantity, Price)
VALUES
(2, 'Smartphone', 'xyz',
800.00); END TRY
BEGIN CATCH
PRINT 'Error: Invalid data. Please check data types.';
END CATCH;
ExNo:10
TRIGGERS
Date:

AIM:

ALGORITHM:
10. TRIGGERS

PROGRAM:

-- CREATE TABLE
SQL>CREATE TABLE PRODUCT ( ProductID INT PRIMARY KEY,
ProductName VARCHAR(50), StockQuantity INT, Price DECIMAL(8, 2) );

-- INSERT DATA
SQL>INSERT INTO PRODUCT (ProductID, ProductName, StockQuantity,
Price) VALUES (3, 'Desk Chair', 30, 150.00);

-- CREATE TRIGGER
SQL> CREATE TRIGGER trg_UpdateStock ON
PRODUCT AFTER UPDATE AS BEGIN
PRINT 'Stock quantity updated. Trigger executed.';
END;

-- UPDATE to activate the trigger

UPDATE PRODUCT
SET StockQuantity = 40
WHERE ProductID = 1;

-- Display data from the PRODUCT table

SELECT * FROM PRODUCT;

-- DROP TABLE and TRIGGER (Cleanup)

DROP TABLE PRODUCT;
DROP TRIGGER trg_UpdateStock;

************************** ALLTHEBEST****************************