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****************************
