Lab Manual of Database Management System

[CSIT-405]

B. Tech. IV Semester

Jan - June 2025

Department of Computer Science and Information

Technology

Submitted to Submitted By
Mr. Satyam Shrivastava Surabhi Solanki
Assistant Professor, CSIT Dept. 0827CI231135
 LIST OF EXPERIMENT

S.No. Name of Experiment Page no Remark

1 Study of SQL
2 Implementation of DDL commands of SQL with suitable examples
● Create table
● Alter table
● Drop Table
● Rename
● Truncate

3 Implementation of DML commands of SQL with suitable examples

● Insert
● Update
● Delete
● Select

4 Implementation of DQL commands of SQL with suitable examples

● Select

5 Implementation of DCL commands of SQL with suitable examples

● Grant
● Revoke

6 Implementation of TCL commands of SQL with suitable examples

● Rollback
● Commit
● Savepoint

7 Implementation of different types of function with suitable examples

● Aggregate Function
● Character Function
● Conversion Function
● Date Function

8 Implementation of different types of operators in SQL

● Arithmetic Operators
● Logical Operators
● Comparison Operator
● Special Operator
● Set Operation

9 Implementation of different types of Joins

● Inner Join
● Outer Join
● Natural Join etc
● Self Join
10 Study and Implementation of
● Group By & having clause
● Order by clause

11 Study & Implementation of

● Sub queries
● Views

12 Study & Implementation of different types of constraints.

● Unique
● Not null
● Primary key
● Foreign Key
● Check
● Default
Study of SQL

Structured Query Language (SQL) is a powerful and standardized language used for managing and
manipulating relational databases. It allows users to interact with the database by performing
operations such as creating tables, inserting data, updating records, retrieving data, and managing
user access. SQL is essential for database administrators, developers, and analysts as it forms the
backbone of most modern data-driven applications.

SQL is divided into several categories of commands: Data Definition Language (DDL), Data
Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language
(TCL). DDL commands like CREATE, ALTER, DROP, and TRUNCATE help define and modify
the structure of the database. DML commands such as SELECT, INSERT, UPDATE, and DELETE
allow users to manipulate the actual data stored in the database. Understanding these commands is
crucial for efficient database design and data handling.

Through practical lab exercises, students gain hands-on experience in writing SQL queries, designing
tables, and managing data. This foundational knowledge not only strengthens database skills but also
prepares students for real-world scenarios in software development, data analysis, and information
systems management.
Implementation of DDL commands of SQL with suitable examples

Data Definition Language (DDL) is a subset of SQL that is used to define and manage the structure
of database objects such as tables, indexes, views, and schemas. Unlike DML, which manipulates the
data within tables, DDL is concerned with the schema and structure of the database itself. DDL
commands are essential during the initial setup and design of a database and whenever modifications
to its structure are needed.

The most common DDL commands include CREATE, ALTER, DROP, and TRUNCATE. The
CREATE command is used to create new database objects like tables or views. The ALTER
command modifies the structure of an existing object, such as adding a column to a table. The DROP
command permanently removes an object from the database, and TRUNCATE deletes all records
from a table without logging individual row deletions, which makes it faster than DELETE but
irreversible.

DDL commands are auto-committed, meaning changes made using DDL are immediately and
permanently saved to the database without requiring an explicit COMMIT. This makes DDL
powerful but potentially dangerous if not used carefully, as dropped or altered objects may not be
recoverable without backups. Overall, DDL plays a foundational role in setting up the structure upon
which all data operations (DML) depend.

The main DDL commands are:

• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE

1. CREATE TABLE The

CREATE TABLE command is used to define a new table in the database. You specify the table name
and define its columns with data types.

Syntax : CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

Example:

CREATE TABLE surabhi_0827CI231135 ( student_id NUMBER(5), name VARCHAR2(50), age

NUMBER(3));

This creates a table named surabhi_0827CI231135 with three columns: student_id, name, and age.

2.ALTER-TABLE
The ALTER TABLE command is used to modify the structure of an existing table. You can add,
modify, or delete columns.

Examples:

● Add_a_column:

Syntax: ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE surabhi_0827CI231135 ADD (email VARCHAR2(100));

Adds a new column named email.

 ● Modify_a_column:

Syntax: ALTER TABLE table_name MODIFY column_name new_datatype;

ALTER TABLE surabhi_0827CI231135 MODIFY (name VARCHAR2(20));

Increases the size of the name column.

● Drop_a_column:

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE surabhi_0827CI231135 DROP COLUMN age;

Removes the age column from the table.

3.DROP-TABLE
The DROP TABLE command permanently deletes a table and all of its data from the database. This
action cannot be rolled back.

Syntax : DROP TABLE table_name;

Example:

DROP TABLE surabhi_0827CI231135;

Completely removes the table and its structure from the database.

4.RENAME
The RENAME command changes the name of an existing table. It is useful when you want to update
naming conventions or clarify the table's purpose.

Syntax : RENAME old_table_name TO new_table_name;

Example:

RENAME surabhi_0827CI231135 TO surabhi_ci2_0827;

Renames the table surabhi_0827CI231135 to surabhi_ci2_0827.

5.TRUNCATE-TABLE
The TRUNCATE TABLE command deletes all records from a table quickly and efficiently, but the
table structure remains for future use. Unlike DELETE, it cannot be rolled back.

Syntax : TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE surabhi_ci2_0827;

Removes all rows from the table but keeps the table structure intact.
Implementation of DML commands of SQL with suitable examples

Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) that deals with
the insertion, retrieval, modification, and deletion of data in a relational database. DML commands
are used to interact with the data stored in tables without affecting their structure. These operations
are typically performed after a table has been created using DDL (Data Definition Language).

The core DML commands are INSERT, UPDATE, DELETE, and SELECT. INSERT is used to add
new records to a table, UPDATE modifies existing data, and DELETE removes data based on
conditions. The SELECT statement is used to retrieve data from one or more tables and is often
considered a read-only DML operation. These commands are essential for the daily use of databases
and are frequently used in applications, reports, and user interactions with systems. DML operations
can be committed (saved permanently) or rolled back (undone) depending on whether the user wants
to retain or discard the changes made during a session.

In multi-user environments, DML commands work with transactions, ensuring data consistency and
integrity. A transaction is a logical unit of work made up of one or more DML operations, and it can
be finalized with COMMIT or undone with ROLLBACK. This transactional control helps maintain
the accuracy and reliability of data even when multiple users are accessing or modifying the database
concurrently.

The main DML commands are:

1.INSERT
The INSERT command is used to add new records (rows) into an existing table. You can insert
values for all columns or just specific ones.

Syntax : INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

INSERT INTO surabhi_0827CI231135 (student_id, name, age) VALUES (10007, 'Priya Nair', 19);
2.UPDATE
The UPDATE command is used to modify existing data in a table. You can update one or more
columns for one or more rows based on a condition.

Syntax : UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Example:

UPDATE surabhi_0827CI231135 SET age = 20 WHERE name = 'Priya Nair';

3.DELETE
The DELETE command removes one or more rows from a table based on a condition. Be careful: if
no WHERE clause is provided, all rows will be deleted.

Syntax : DELETE FROM table_name WHERE condition;

Example:

DELETE FROM surabhi_0827CI231135 WHERE student_id = 10007;

4. SELECT

The SELECT command is used to retrieve data from one or more tables. You can retrieve all columns
or only selected ones, and apply filtering, sorting, and more.

Syntax : SELECT column1, column2 FROM table_name WHERE condition

Example:

SELECT name, age FROM surabhi_0827CI231135 WHERE age > 20;

Implementation of DQL commands of SQL with suitable examples

Data Query Language (DQL) is a subset of SQL (Structured Query Language) that is used to retrieve
data from a database. It focuses solely on querying and reading data without modifying it. DQL
enables users to extract meaningful information from the database using various conditions, filters,
and expressions, making it essential for reporting, analysis, and decision-making processes.

The primary and only command in DQL is the SELECT statement. This command allows users to
retrieve one or more rows from one or more tables, based on specific criteria. It supports a wide range
of operations like filtering using WHERE, sorting with ORDER BY, grouping with GROUP BY, and
even joining multiple tables. The flexibility of the SELECT command makes DQL extremely
powerful for accessing structured data.

Unlike DML (Data Manipulation Language), DQL does not alter the data; it only reads and presents
it. Therefore, DQL operations do not require transaction control like COMMIT or ROLLBACK. DQL
plays a central role in database interactions, especially in applications involving data analysis, reports,
dashboards, and user interfaces where data needs to be displayed or processed based on specific
queries.

1. SELECT

The SELECT command is used to fetch specific data from one or more tables.

Syntax : SELECT column1, column2 FROM table_name WHERE condition;

Example:

SELECT name, age FROM surabhi_0827CI231135 WHERE age > 20;

This query retrieves the names and ages of students whose age is greater than 20 from the table
surabhi_0827CI231135.
Implementation of DCL commands of SQL with suitable examples

Data Control Language (DCL) is a subset of SQL used to control access to data stored in a database.
It allows database administrators and privileged users to manage permissions and security by defining
who can perform certain actions on specific database objects like tables, views, procedures, etc.

The main purpose of DCL is to ensure data security and controlled access. It is typically used to grant
or revoke privileges to users or roles, ensuring that only authorized users can perform operations such
as reading, inserting, updating, or deleting data. DCL commands are especially critical in multi-user
environments where access to sensitive or critical data must be regulated.

The two primary DCL commands are GRANT and REVOKE. These commands help implement role-
based access control by assigning or removing privileges as needed. These changes may affect how
different users interact with the database and help in maintaining compliance and security within a
database system.

1.GRANT
The GRANT command is used to provide specific privileges to a user or role on a database object.

Syntax : GRANT privileges ON object TO user;

Example:

GRANT SELECT, INSERT ON surabhi_0827CI231135 TO sys;

This allows sys to read from and insert into the table surabhi_0827CI231135.
2. REVOKE

The REVOKE command is used to take back previously granted privileges from a user or role.

Syntax : REVOKE privileges ON object FROM user;

Example :

REVOKE INSERT ON surabhi_0827CI231135 FROM user1;

This removes the INSERT privilege from sys, but they can still use SELECT (if it was not revoked).
Implementation of TCL commands of SQL with suitable examples

Transaction Control Language (TCL) is a subset of SQL used to manage transactions in a database.
A transaction is a group of one or more SQL statements that are executed as a single unit of work.
TCL commands allow you to save, undo, or organize changes made during a transaction to ensure
data accuracy and integrity.

TCL is especially useful in multi-user and critical systems where multiple operations need to succeed
or fail together. If something goes wrong during a transaction, TCL provides a way to undo all or part
of the changes using commands like ROLLBACK, or to save changes permanently using COMMIT.
This ensures that the database remains in a consistent state, even if errors occur during data
manipulation.

1.COMMIT
Permanently saves all changes made during the current transaction.

Syntax : COMMIT;

Example:

INSERT INTO surabhi_0827CI231135 (student_id, name, age) VALUES (10008, 'Rohan Das', 21);

COMMIT;

2.ROLLBACK

Undoes all changes made during the current transaction since the last COMMIT.

Syntax : ROLLBACK;
Example:

DELETE FROM surabhi_0827CI231135 WHERE student_id = 10008;

ROLLBACK;

This cancels the delete operation and restores the deleted row.

3.SAVEPOINT
Creates a point within a transaction to which you can later roll back.

Syntax : SAVEPOINT savepoint_name;

ROLLBACK TO savepoint_name;
Example:

SAVEPOINT sp1;

UPDATE surabhi_0827CI231135 SET age = 25 WHERE student_id = 10001;

ROLLBACK TO sp1;

This reverts the update but keeps earlier changes made before sp1.
Implementation of different types of function with suitable
examples

Functions in SQL: In SQL, a function is a built-in operation that performs a calculation or

transformation on data and returns a single value. Functions help in data manipulation, formatting,
aggregation, and conversion and are commonly used in SELECT statements. SQL functions are
categorized based on the type of operation they perform, such as aggregate, character, date, and
conversion functions.

Functions can be applied to columns, literals, or expressions, and are essential for data analysis,
reporting, and transformation.

1.Aggregate_Function
Aggregate functions perform calculations on a group of rows and return a single result.

Common Functions: SUM, AVG, MAX, MIN, COUNT

Syntax : SELECT COUNT(*), SUM(column), AVG(column), MAX(column), MIN(column) FROM

table_name;

Examples:

SELECT COUNT(*) AS total_students, AVG(age) AS average_age, MAX(age) AS oldest,

MIN(age) AS youngest FROM surabhi_0827CI231135;

2.Character_Function
Character functions manipulate or analyze string data (text).

Common Functions: UPPER, LOWER, LENGTH, SUBSTR, INSTR

Syntax : SELECT UPPER(column), LOWER(column), LENGTH(column) FROM table_name;

Example:

SELECT name, UPPER(name) AS upper_name, LENGTH(name) AS name_length FROM

surabhi_0827CI231135;

3.Conversion_Function
Conversion functions convert one data type to another, such as from number to string or string to
date. Common Functions: TO_CHAR, TO_DATE, TO_NUMBER

Syntax : SELECT TO_CHAR(date_column, 'format') FROM table_name;

Example:

SELECT TO_CHAR(age) AS age_as_text FROM surabhi_0827CI231135;

4.Date_Function
Date functions manipulate and return values related to date and time.Common Functions:
SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, TO_DATE

Syntax : SELECT SYSDATE, ADD_MONTHS(SYSDATE, n), MONTHS_BETWEEN(date1,

date2) FROM DUAL;

Example:

SELECT SYSDATE AS today, ADD_MONTHS(SYSDATE, 3) AS after_3_months,

NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday FROM dual;

dual is a special one-row, one-column table in Oracle used for queries that don't need actual
table data.
Implementation of different types of operators in SQL

Operators:
In SQL, an operator is a symbol or keyword used to perform operations on data, such as calculations,
comparisons, logical tests, or combining results. Operators are commonly used in SELECT, WHERE,
HAVING, and other clauses to filter, compare, or compute values.

Operators are classified into various types based on the type of operation they perform.

1.Arithmetic_Operators
Used to perform mathematical operations on numeric data.

Operators: + (Addition), - (Subtraction), * (Multiplication), / (Division), % (Modulus)

Example:

SELECT student_id, age, age + 1 AS next_year_age FROM surabhi_0827CI231135;

2.Logical_Operators
Used to combine multiple conditions in a SQL query.

Operators: AND, OR, NOT

Example:

SELECT * FROM surabhi_0827CI231135 WHERE age > 20 AND name LIKE 'A%';
3.Comparison_Operators
Used to compare two values and return a boolean result (TRUE or FALSE).

Operators: =, != or <>, >, <, >=, <=, BETWEEN, LIKE, IN

Example:

SELECT * FROM surabhi_0827CI231135 WHERE age BETWEEN 20 AND 22;

4.Special_Operators
Used for more specific operations such as checking for NULL, pattern matching, and membership.

Operators: IS NULL, IS NOT NULL, LIKE, IN, EXISTS, BETWEEN

Examples:

● Check for NULL values

SELECT * FROM surabhi_0827CI231135 WHERE age IS NULL;

● Check if name is among specific values

SELECT * FROM surabhi_0827CI231135 WHERE name IN ('Amit Roy', 'Sanya Kapoor');

5.Set_Operators
Used to combine results from two or more SELECT queries.

Operators: UNION, UNION ALL, INTERSECT, MINUS

(Tables used must have the same number and type of columns.)

Example:
Assume another table student_backup with the same structure:

SELECT name FROM surabhi_0827CI231135 UNION SELECT name FROM student_backup;

UNION removes duplicates, while UNION ALL keeps them. INTERSECT returns common
rows, MINUS returns rows from the first query that are not in the second.
Implementation of different types of Joins
In SQL, a JOIN is used to combine rows from two or more tables based on a related column between
them. It allows you to retrieve data spread across multiple tables and present it in a single, meaningful
result set. Joins are essential when data is normalized across several tables.

Joins work by matching records using foreign keys, primary keys, or any common columns. SQL
supports several types of joins, each serving different purposes based on whether you want to include
unmatched rows or not.

1.INNER_JOIN
Returns only the matching rows from both tables based on a specified condition.

Syntax : SELECT * FROM table1 INNER JOIN table2 ON table1.col = table2.col;

Example:

SELECT a.student_id, a.name, b.age FROM surabhi_0827CI231135 a INNER JOIN student_backup

b ON a.student_id = b.student_id;

Returns students whose student_id exists in both tables.

2.LEFT_OUTER_JOIN
Returns all rows from the left table and matching rows from the right table. If there’s no match, NULL
is returned for columns from the right table.

Syntax : SELECT * FROM table1 INNER JOIN table2 ON table1.col = table2.col;

Example:

SELECT a.student_id, a.name, b.age FROM surabhi_0827CI231135 a LEFT OUTER JOIN

student_backup b ON a.student_id = b.student_id;

Returns all students from surabhi_0827CI231135, with age from student_backup if matched.
3.RIGHT_OUTER_JOIN
Returns all rows from the right table and matching rows from the left table. If there’s no match,
NULL is returned for left table columns.

Syntax : SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.col = table2.col;

Example:

SELECT a.student_id, a.name, b.age FROM surabhi_0827CI231135 a RIGHT OUTER JOIN

student_backup b ON a.student_id = b.student_id;

Returns all students from student_backup, and matches from surabhi_0827CI231135 if

available.

4.FULL_OUTER_JOIN
Returns all rows from both tables, with NULL where there’s no match.

Syntax : SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.col = table2.col;

Example:

SELECT a.student_id, a.name, b.age FROM surabhi_0827CI231135 a FULL OUTER JOIN

student_backup b ON a.student_id = b.student_id;

5.NATURAL_JOIN
Automatically joins tables using columns with the same names and compatible data types. You don’t
have to specify the join condition.

Syntax : SELECT * FROM table1 NATURAL JOIN table2;

Example:

SELECT * FROM surabhi_0827CI231135 NATURAL JOIN student_backup;

Automatically joins on student_id (or any matching column).

6.SELF_JOIN
A self join is used to join a table with itself. Useful when comparing rows within the same table.

Syntax : SELECT A.col, B.col FROM table A, table B WHERE A.col = B.col;

Example:

SELECT a.student_id AS ID1, a.name AS Name1, b.student_id AS ID2, b.name AS Name2 FROM
surabhi_0827CI231135 a, surabhi_0827CI231135 b WHERE a.age = b.age AND a.student_id <>
b.student_id;

Finds pairs of students with the same age but different IDs.
Study and Implementation of

● Group By & having clause

The GROUP BY clause is used to group rows that have the same values in specified columns into
summary rows (like total, average, count, etc.). It is typically used with aggregate functions like
COUNT(), SUM(), AVG(), MAX(), MIN().

The HAVING clause is used to filter grouped rows. Unlike the WHERE clause (which filters rows
before grouping), HAVING filters after the groups are created.

Syntax : SELECT column FROM table GROUP BY column HAVING condition;

Example: SELECT age, COUNT(*) AS student_count FROM surabhi_0827CI231135 GROUP BY

age HAVING COUNT(*) > 1;

This query groups students by age, counts how many students fall into each age group and
only shows age groups that have more than one student.

• Order By clause
The ORDER BY clause is used to sort the result set by one or more columns. Sorting can be done in
ascending (ASC) or descending (DESC) order. The default is ascending.

Syntax: SELECT * FROM table ORDER BY column ASC|DESC;

Example: SELECT student_id, name, age FROM surabhi_0827CI231135 ORDER BY age DESC,
name ASC;

This query sorts students by age in descending order (oldest first).

If two students have the same age, it then sorts them by name in ascending (A-Z) order.
Study & Implementation of

● Sub queries
A subquery (also called an inner query or nested query) is a query placed inside another SQL query.
Subqueries are used to return data that will be used by the main query, often in conditions like
WHERE, IN, EXISTS, or even as derived tables.

Subqueries help break complex problems into smaller parts and make your SQL more modular and
readable.

Syntax : SELECT column FROM table WHERE column = (SELECT column FROM table WHERE
condition);

Example:

SELECT name, age FROM surabhi_0827CI231135 WHERE age = (SELECT MAX(age) FROM
surabhi_0827CI231135);

The subquery finds the maximum age and the main query retrieves the student(s) with that age.

● Views
A view is a virtual table based on a SQL query. It does not store data itself, but shows data
dynamically from one or more tables when queried. Views are useful for:

● Simplifying complex queries

● Hiding sensitive columns
● Presenting specific data to users

Views can be queried just like regular tables, but you can’t insert, update, or delete from all views—
especially if they use joins or aggregate functions.

Syntax : CREATE VIEW view_name AS SELECT columns FROM table WHERE condition;
Example:

CREATE VIEW student_age_view AS SELECT student_id, name, age FROM

surabhi_0827CI231135 WHERE age >= 21;

To use the view:

SELECT * FROM student_age_view;

The view student_age_view contains students who are 21 or older.

The view is updated automatically if the underlying table data changes.
Study & Implementation of different types of constraints

Constraints in SQL: In SQL, constraints are rules applied to table columns to enforce data integrity,
accuracy, and consistency. Constraints prevent invalid data from being entered into tables and ensure
that the relationships between data remain correct. They are applied at the time of table creation or
can be added later using the ALTER TABLE command.

Constraints help define business rules directly in the database schema, ensuring that the data always
adheres to specified conditions without needing application-level validation. Oracle supports several
types of constraints, each designed for specific validations.

● Unique
Definition: Ensures that all values in a column are unique (no duplicates).
Use: For fields like email or roll number that should be different for each row.

Syntax : column datatype UNIQUE

Example:

ALTER TABLE surabhis_0827CI231135 ADD CONSTRAINT unique_name UNIQUE (name);

● Not null
Definition: Ensures that a column cannot contain NULL values.
Use: When a column must always have a value.

Syntax : column datatype NOT NULL

Example:
CREATE TABLE surabhis_0827CI231135 ( student_id NUMBER(5) NOT NULL, name
VARCHAR2(50) NOT NULL);
 ● Primary key
Definition: Uniquely identifies each row. It is a combination of NOT NULL and UNIQUE.
Use: Used to identify records in a table (e.g., student_id).

Syntax : PRIMARY KEY (column)

Example:

ALTER TABLE surabhis_0827CI231135ADD CONSTRAINT pk_student PRIMARY KEY

(student_id);

● Foreign Key
Definition: Enforces a link between two tables by referencing the PRIMARY KEY in another table.
Use: Maintains referential integrity across tables.

Syntax : FOREIGN KEY (column) REFERENCES other_table(column)

Example:

CREATE TABLE student_marks_1( mark_id NUMBER PRIMARY KEY, student_id NUMBER,

FOREIGN KEY (student_id) REFERENCES surabhis_0827CI231135(student_id));

● Check
Definition: Ensures that values in a column meet a specific condition.
Use: To limit values within a range or pattern.

Syntax : CHECK (condition)

Example:

ALTER TABLE surabhis_0827CI231135 ADD CONSTRAINT check_age CHECK (student_id >

0);

● Default
Definition: Assigns a default value to a column when no value is provided.
Use: For columns like status, created_date, or grade.

Syntax : column datatype DEFAULT value

Example:

ALTER TABLE surabhis_0827CI231135 ADD grade VARCHAR2(2) DEFAULT 'NA';