SCHOOL OF COMPUTING

Department of Computer Science and Engineering

Database Management System

Lab Record (212CSE2305)

Student Name : ………………………………………………….….

Register
Number: …………………………………………………. . ..
..
Section :…………………………………………………….
.

1
 BONAFIDE CERTIFICATE

Bonafide record of work done by of

Second year in the Department of Computer Science and Engineering during Even semester in

academic year 2025-2026.

Staff In-charge

------------------------------------------------------------------------------------------------

Submitted to the practical Examination held at Kalasalingam Academy of

Research and Education, Anand Nagar, Krishnankoil on

REGISTER NUMBER

Internal Examiner External Examiner

2
 LIST OF EXPERIMENTS

Course Title: Database Management System Laboratory

[Link] Name of the Experiment Page No

1. Implementation of DDL commands of SQL with suitable examples

 Create table
 Alter table
 Drop Table

2. Implementation of DML commands of SQL with suitable examples

 Insert
 Update
 Delete

3. Implementation of Student Management System

 Student Table Creation
 Inserting Student Details
 Updating Student Records
 Retrieving Student Data

4. Implementation of Library Management System

 Book Table Creation

 Member Table Creation
 Issue and Return of Books
 Record Maintenance

5.

Implementation of Aggregate Functions (Any 6)

 COUNT()
 SUM()
 AVG()
 MAX()
 MIN()
 DISTINCT

3
6. Implementation of String Functions (Any 6)

 UPPER()
 LOWER()
 LENGTH()
 CONCAT()
 SUBSTRING()
 TRIM()

7. Implementation of Numeric Functions (Any 6)

 ROUND()
 ABS()
 CEIL()
 FLOOR()
 POWER()
 MOD()

Implementation of Joins
8.
 Inner Join
 Left Join
 Right Join
 Full Join

9 Implementation of Banking System

 Customer Table Creation

 Account Table Creation
 Deposit and Withdrawal
 Balance Checking
 Transaction Records

10. Implementation of Clause Operations

10 WHERE Clause
 GROUP BY Clause
 HAVING Clause
 ORDER BY Clause

4
EXP NO:1
Date:

Title: Implementation of DDL commands of SQL with suitable examples

• Create table
• Alter table
• Drop Table

Objective:
To understand the different issues involved in the design and implementation of a
database system
DATA DEFINITION LANGUAGE (DDL):
The Data Definition Language (DDL) is used to create and destroy databases and database
objects. These commands will primarily be used by database administrators during the setup and
removal phases of a database project.
Let's take a look at the structure and usage of four basic DDL commands:
1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:
(a)CREATE TABLE: This is used to create a new relation (table)

Syntax: CREATE TABLE <relation_name/table_name >

(field_1 data_type(size),field_2 data_type(size), .. . );

Example:
SQL> CREATE TABLE Student (sno NUMBER (3), sname CHAR (10), class CHAR (5));
SQL> DESCRIBE Student;

2. ALTER:
5
 (a) ALTER TABLE ...ADD...: This is used to add some extra fields into existing
relation.

Syntax: ALTER TABLE relation_name ADD (new field_1 data_type(size), new field_2
data_type(size),..);
Example: SQL>ALTER TABLE student ADD (Address CHAR(10));

(b) ALTER TABLE...MODIFY...: This is used to change the width as well as data type
of fields of existing relations.

Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size), field_2

newdata_type(Size), ... field_newdata_type(Size));

Example: SQL>ALTER TABLE student MODIFY (sname VARCHAR(10), class

VARCHAR(5));

ALTER TABLE..DROP .... This is used to remove any field of existing relations.

Syntax: ALTER TABLE relation_name DROP COLUMN (field_name);

Example:SQL>ALTER TABLE student DROP column (sname);

6
 (c) ALTER TABLE..RENAME...: This is used to change the name of fields in existing
relations.

Syntax: ALTER TABLE relation_name RENAME COLUMN (OLD field_name) to (NEW

field_name);

Example: SQL>ALTER TABLE student RENAME COLUMN sname to stu_name;

3. DROP TABLE: This is used to delete the structure of a relation. It permanently deletes the
records in the table.
Syntax: DROP TABLE relation_name; Example:
SQL>DROP TABLE student;

Result:
Thus, the implementation helped in understanding how database structures are defined,
modified, and removed using DDL commands. The objective of learning the design and
implementation aspects of database systems was successfully achieved.
Exp No:2
7
Date:

Title : Implementation of DML commands of SQL with suitable examples

• Insert table
• Update table
• Delete Table

Objective :
To understand the different issues involved in the design and implementation of a database
system
To understand and use data manipulation language to query, update, and manage a

database

Theory :

DATA MANIPULATION LANGUAGE (DML): The Data Manipulation Language

(DML) is used to retrieve, insert and modify database information. These commands will be used
by all database users during the routine operation of the database. Let's take a brief look at the
basic DML commands:
1. INSERT 2. UPDATE 3. DELETE
1. INSERT INTO: This is used to add records into a relation. These are three type of INSERT
INTO queries which are as a) Inserting a single record
Syntax: INSERT INTO < relation/table name> (field_1,field_2……field_n)VALUES
(data_1,data_2, ...... data_n);
Example: SQL> INSERT INTO student(sno,sname,class) VALUES (1,'Ravi','CSE');
Inserting a single record
Syntax: INSERT INTO < relation/table name>VALUES (data_1,data_2, ....... data_n);
Example: SQL>INSERT INTO student VALUES (1,’Ravi’,’[Link]’,’Palakol’);

b) Inserting all records from another relation

8
Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n
FROM relation_name_2 WHERE field_x=data;
Example: SQL>INSERT INTO std SELECT sno,sname FROM student
WHERE name = ‘Ramu‘;

c) Inserting multiple records

Syntax: INSERT INTO relation_name field_1,field_2, .... field_n) VALUES
(&data_1,&data_2, ....... &data_n);
Example: SQL>INSERT INTO student (sno, sname, class,address)
VALUES (&sno,’&sname’,’&class’,’&address’);

2. UPDATE-SET-WHERE: This is used to update the content of a record in a relation.

Syntax: SQL>UPDATE relation name SET Field_name1=data,field_name2=data, WHERE
field_name=data;
Example: SQL>UPDATE student SET sname = ‘kumar’ WHERE sno=1;

9
 3. DELETE-FROM: This is used to delete all the records of a relation but it will retain the
structure of that relation.
a) DELETE-FROM: This is used to delete all the records of relation.
Syntax: SQL>DELETE FROM relation_name;
Example: SQL>DELETE FROM std;
b) DELETE -FROM-WHERE: This is used to delete a selected record from a relation. Syntax:
SQL>DELETE FROM relation_name WHERE condition;
Example: SQL>DELETE FROM student WHERE sno = 2;

4. TRUNCATE: This command will remove the data permanently. But structure will not be
removed.

Difference between Truncate & Delete:-

 By using truncate command data will be removed permanently & will not get back where
as by using delete command data will be removed temporally & get back by using roll
back command.
 By using delete command data will be removed based on the condition where as by using
truncate command there is no condition.
 Truncate is a DDL command & delete is a DML command.

Syntax: TRUNCATE TABLE <Table name>

Example TRUNCATE TABLE student;

 To Retrieve data from one or more tables.

10
 1. SELECT FROM: To display all fields for all records.
Syntax : SELECT * FROM relation_name;
Example : SQL> select * from dept;
DEPTNO DNAME LOC
-------- ----------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

2. SELECT FROM: To display a set of fields for all records of relation.

Syntax: SELECT a set of fields FROM relation_name;
Example: SQL> select deptno, dname from dept;
DEPTNO DNAME

10 ACCOUNTING
20 RESEARCH
30 SALES

3. SELECT - FROM -WHERE: This query is used to display a selected set of fields for a
selected set of records of a relation.

Syntax: SELECT a set of fields FROM relation_name WHERE condition;

Example: SQL> select * FROM dept WHERE deptno<=20;
DEPTNO DNAME LOC
------ ----------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS

Result
Thus, the DML commands such as INSERT, UPDATE, DELETE, and TRUNCATE are
successfully implemented and verified. The operations for inserting, updating, and deleting
records in a table are performed correctly, and the differences between DELETE and TRUNCATE
are understood.

Exp No: 3
Date:
11
 Title: Student Management System with suitable examples.

Objective:

To understand the different issues involved in the design and implementation of a Student
Management database system

DATA DEFINITION LANGUAGE (DDL):

The Data Definition Language (DDL) is used to create and destroy databases and database objects.
These commands will primarily be used by database administrators during the setup and removal
phases of a database project.
Let's take a look at the structure and usage of four basic DDL commands:

1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:
(a) CREATE TABLE:
This is used to create a new relation (table)
Syntax:
CREATE TABLE Student (id INT,name VARCHAR(20),course VARCHAR(20));
Example:
SQL> CREATE TABLE Student (id INT, name VARCHAR(20), course VARCHAR(20));

2 . INSERT:
This is used to insert values into the relation
Syntax:
INSERT INTO TABLE table_name VALUES (col_name1 datatype, col_name2
datatype);

Example:
12
 INSERT INTO students VALUES (1, 'Ravi', 20, 'BCA', 85), (2, 'Anita', 21, 'BCA',
90), (3, 'Kumar', 22, 'BBA', 75), (4, 'Priya', 20, 'BCom', 80);

3. ALTER:

(a) ALTER TABLE ...ADD...

This is used to add some extra fields into existing relation.
Syntax:
ALTER TABLE Student ADD (age INT);
Example:
SQL> ALTER TABLE Student ADD (age INT);

(b) ALTER TABLE..DROP....

This is used to remove any field of existing relations.
13
 Syntax:
ALTER TABLE Student DROP COLUMN age;
Example:
SQL> ALTER TABLE Student DROP COLUMN age;

Result
The Student Management System table was successfully created, and records were inserted
into it. The table structure was modified by adding and dropping columns using ALTER
commands. All operations were executed correctly and verified.
Exp No: 4
Date:

14
 Title:
Library Management System with suitable examples.

Objective:
To understand the different issues involved in the design and implementation of a Library
Management database system

DATA DEFINITION LANGUAGE (DDL):

The Data Definition Language (DDL) is used to create and destroy databases and database
objects. These commands will primarily be used by database administrators during the setup and
removal phases of a database project.

Let's take a look at the structure and usage of four basic DDL commands:
1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:
(a) CREATE TABLE:
This is used to create a new relation (table)

Syntax:
CREATE TABLE Library (book_id INT,title VARCHAR(20),author
VARCHAR(20));

Example:
SQL> CREATE TABLE Library (book_id INT, title VARCHAR(50), author
VARCHAR(50), quantity INT);

2. INSERT:
This is used to insert values into the relation

15
Syntax:
INSERT INTO TABLE table_name VALUES (col_name1 datatype, col_name2 datatype);
Example:
INSERT INTO library VALUES (1, 'DBMS', 'Korth', 5), (2, 'Python', 'Guido', 4);

3. ALTER:
(a) ALTER TABLE ...ADD...
This is used to add some extra fields into existing relation.
Syntax:
ALTER TABLE Library ADD (price INT);
Example:
SQL> ALTER TABLE Library ADD (price INT);

(b) ALTER TABLE..DROP....

This is used to remove any field of existing relations.
Syntax:

16
 ALTER TABLE Library DROP COLUMN price;
Example:
SQL> ALTER TABLE Library DROP COLUMN price;

Result
The Library table was successfully created, and records were inserted into it. The table
structure was modified by adding and dropping columns using ALTER commands. All the operations
were executed successfully and verified.

17
Exp No: 5
Date:

TITLE :

Implementation of Aggregate Functions

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()
• DISTINCT

OBJECTIVE :

To implement different types of aggregate functions in SQL and analyze summarized data
from a database.

THEORY :

Aggregate functions in SQL are used to perform calculations on a set of values and return a
single value. These functions are mainly used with the GROUP BY clause to group rows that have
the same values.

Aggregate functions ignore NULL values (except COUNT(*)) and help in generating
summarized reports such as totals, averages, and counts.

AGGREGATE FUNCTIONS :
1. COUNT() Function :
It returns the total number of rows.

18
Example:
SELECT COUNT(salary) FROM employee;

2. SUM() Function :
It returns the total sum of a numeric column.
Example:
SELECT SUM(salary) FROM employee;

3. AVG() Function :
It returns the average value of a numeric column.
Example:
SELECT AVG(salary) FROM employee;

4. MAX() Function :
It returns the maximum value in a column.
Example:
SELECT MAX(salary) FROM employee;
19
 5. MIN() Function :
It returns the minimum value in a column.
Example:
SELECT MIN(salary) FROM employee;

6. DISTINCT Keyword :
It is used to return only unique (different) values.
Example:
SELECT DISTINCT deptno FROM employee;

RESULT :
The aggregate functions such as COUNT(), SUM(), AVG(), MAX(), MIN(), and DISTINCT
were successfully implemented. The queries were executed correctly, and summarized results were
obtained from the database.
Exp No: 6

20
Date:

Title :
Implementation of String Functions
• UPPER()
• LOWER()
• LENGTH()
• CONCAT()
• SUBSTRING()
• TRIM()

OBJECTIVE :
To implement different string functions in SQL and perform operations on character data.

THEORY :
String functions in SQL are used to manipulate and process character or text data. These
functions help in converting text case, finding length, combining strings, extracting substrings, and
removing unwanted spaces.
They are widely used in database queries for formatting and data cleaning.

STRING FUNCTIONS :
1. UPPER() Function :
Converts all characters of a string into uppercase.
Example:
SELECT UPPER(name) FROM employee;

2. LOWER() Function :

21
 Converts all characters of a string into lowercase.
Example:
SELECT LOWER(name) FROM employee;

3. LENGTH() Function :
Returns the length of a string.
Example:
SELECT LENGTH(name) FROM employee;

4. CONCAT() Function :
Combines two or more strings into one.
Example:
SELECT CONCAT(name, ' works in dept ', deptno) FROM employee;

5. SUBSTRING() Function :
Extracts a part of a string.
Example:
SELECT SUBSTRING(name, 1, 3) FROM employee;

6. TRIM() Function :
Removes leading and trailing spaces from a string.
Example:
SELECT TRIM(name) FROM employee;

Result :
The string functions such as UPPER(), LOWER(), LENGTH(), CONCAT(), SUBSTRING(),
and TRIM() were successfully implemented. The queries were executed correctly, and string
manipulations were performed on the table data.

22
23
24
25
Exp No: 8
Date:

Title : Implementation of different types of Joins

• Inner Join
• Outer Join
• Natural Join..etc

Objective :
To implement different types of joins

Theory :
The SQL Joins clause is used to combine records from two or more tables in a
database. A JOIN is a means for combining fields from two tables by using values common to
[Link] join is actually performed by the ‘where’ clause which combines specified rows of
tables.
Syntax:
SELECT column 1, column 2, column 3...
FROM table_name1, table_name2
WHERE table_name1.column name = table_name2.columnname;

Types of Joins :
1. Simple Join
2. Self Join
3. Outer Join

Simple Join:
It is the most common type of join. It retrieves the rows from 2 tables having a common
column and is further classified into
Equi-join :
26
 A join, which is based on equalities, is called equi-join.
Example:
Select * from item, cust where [Link]=[Link];

In the above statement, item-id = cust-id performs the join statement. It retrieves rows from
both the tables provided they both have the same id as specified by the where clause. Since the
where clause uses the comparison operator (=) to perform a join, it is said to be equijoin. It
combines the matched rows of tables. It can be used as follows:

 To insert records in the target table.

 To create tables and insert records in this table.
 To update records in the target table.
 To create views.

Non Equi-join:
It specifies the relationship between columns belonging to different tables by
making use of relational operators other than’=’.

Example:
Select * from item, cust where [Link]<[Link];
Table Aliases
Table aliases are used to make multiple table queries shorted and more readable. We give an
alias name to the table in the ‘from’ clause and use it instead of the name throughout the query.

Self join:
Joining of a table to itself is known as self-join. It joins one row in a table to another. It can
compare each row of the table to itself and also with other rows of the same table.
27
Example:
select * from emp x ,emp y where [Link] >= (select avg(salary) from [Link] where x.
deptno =[Link]);

Outer Join:

It extends the result of a simple join. An outer join returns all the rows returned by simple join as
well as those rows from one table that do not match any row from the table. The symbol(+)
represents outer join.

– Left outer join

– Right outer join

– Full outer join

28
 Result
The different types of joins such as Inner Join (Equi-join and Non-Equi join), Self Join,
and Outer Join (Left, Right, Full) were successfully implemented. The queries were executed
correctly, and the combined data from multiple tables was retrieved and verified.
Exp No:10
Date:

Title: Implementation of Clause Operations

 WHERE Clause
 GROUP BY Clause
 HAVING Clause
 ORDER BY Clause

Objective:
 To learn how to filter records using conditions
 To group data based on specific columns
 To apply conditions on grouped data
 To sort the data in ascending or descending order

Theory:
SQL clauses are used to perform specific operations while retrieving data from a database.

1. WHERE Clause
The WHERE clause is used to filter records based on a condition. It extracts only
those records that satisfy the given condition.

2. GROUP BY Clause
The GROUP BY clause is used to arrange identical data into groups. It is mostly used
with aggregate functions like COUNT, SUM, AVG, MAX, and MIN.

3. HAVING Clause
The HAVING clause is used to apply conditions on groups created by the GROUP BY
clause.

4. ORDER BY Clause
The ORDER BY clause is used to sort the result in ascending (ASC) or descending
(DESC) order.

Table Used: STUDENT

Roll_No Name Dept Marks
1 Arun CSE 85
29
2 Bala ECE 78
3 Chitra CSE 92
4 Devi EEE 70
Procedure with Queries and Output

1. WHERE Clause
Query:
SELECT * FROM STUDENT
WHERE Marks > 80;

Output:
Roll_N Name Dept Marks
o
1 Arun CSE 85
3 Chitra CSE 92
5 Kiran ECE 88

2. GROUP BY Clause
Query:
SELECT Dept, COUNT(*) AS Total_Students
FROM STUDENT
GROUP BY Dept;

Output:
Dept Total_Students
CSE 2
ECE 2
EEE 1

3. HAVING Clause
Query:
SELECT Dept, AVG(Marks) AS Avg_Marks
FROM STUDENT
GROUP BY Dept
HAVING AVG(Marks) > 80;

Output:
Dep Avg_Marks
t
CSE 88.5
ECE 83

30
 4. ORDER BY Clause
Query:
SELECT * FROM STUDENT
ORDER BY Marks DESC;

Output:

Roll_N Name Dept Marks

o
3 Chitra CSE 92
5 Kiran ECE 88
1 Arun CSE 85
2 Bala ECE 78
4 Devi EEE 70

Result

Thus, the clause operations such as WHERE, GROUP BY, HAVING, and ORDER BY are
successfully implemented and verified.

31