MADHAV INSTITUTE OF TECHNOLOGY & SCIENCE GWALIOR

Deemed University
(Declared under Distinct Category by Ministry of Education, Government of India)
NAAC Accredited with A++ Grade

LAB Report
on
Database Managment System (3240424)

SUBMITTED BY

Armaan Saxena
0901AI231014
4th Semester
Artificial Intelligence and Robotics

SUBMITTED TO
Professor Shubha Mishra

CENTRE FOR ARTIFICIAL INTELLIGENCE

Madhav Institute of Technology & Science Gwalior
- 474005 (MP) est. 1957
Index

S No. Experiment Date

1. Implementation of DDL Commands

of SQL with suitable examples.

2. Implementation of DML Commands

of SQL with suitable examples.

3. Implementation of different types of

function with suitable example.

4. Implementation of different types of SQL

operators.

5. Implementation of types of Joins.

6. Study and Implementation of

o Group by & having clause

o Order By clause
o Indexing

7. Study and Implementation of

o Sub queries
o Views
 Experiment No.1

Aim: Implementation of DDL Commands of SQL with suitable examples.

1.​ Create Table

2.​ Alter Table
3.​ Drop Table

Software Required: MySQL

Data Definition Language(DDL):

The DDL Commands in SQL are used to create and modify the database structures
such as tables, indexes, and schemas.

1.​ CREATE: CREATE is a DDL command used to create databases, tables and other
database objects.

Syntax:

CREATE TABLE table_name(

​Column_name1 datatype(size),

​Column_name2 datatype(size)..

​Column_nameN datatype(size)

);

Example:
Output:-

2. ALTER: ALTER is a DDL Command which adds, deletes or modifies the existing structure of
the database.

Syntax:

ALTER TABLE table_name ADD column_name datatype;

Example:

Output:-
3. TRUNCATE: TRUNCATE Command is used to delete or remove all the records from the
table. But this command will not delete the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

Output:-
4. DROP: DROP command is used to delete the table and all its values altogether.

Syntax:

DROP TABLE table_name;

Example:

Output:-
5. RENAME: RENAME command is used to rename the table name. This command is
used with the ALTER command to change the column name and Table Name.
Syntax:
ALTER TABLE table_name RENAME TO new_table_name;

For Column:
ALTER TABLE table_name RENAME COLUMN old_column_name TO
new_column_name;
Example:-

Output:-

6. COMMENT: Comments are important for clarity and context. It makes our queries
easier to understand by the user.
Single-Line Comments:
Statements starting with -- are treated as Single-Line Comments.
Multi-Line Comments:
Statements enclosed in // are treated as Multi-Line Comments.

Example:-
 Experiment No. 2

Aim: Implementation of DML Commands of SQL with suitable examples.

1.​ Insert
2.​ Update
3.​ Delete

Software Required: MySQL

Data Manipulation Language(DML): The DML Commands in Structured Query

Language change the data present in the SQL Database.

1.​ INSERT : The INSERT Command is an important DML Command which is used
to add new rows of data to a table of a database.

Syntax:

INSERT INTO table_name VALUES (value1,value2,.....valueN); OR

INSERT INTO table_name (column1, column2,....., column_N) VALUES

(value1,value2,.....valueN);

Example:-

Output:-
 2.​ SELECT: The SELECT Command in SQL is used to fetch and retrieve data from
a database. It also allows users to retrieve the specific data based on the
specific conditions.

Syntax:

SELECT column_1,column_2,...,column_N FROM table_name;

Example:-

Output:-
 3.​ UPDATE: The UPDATE Command in SQL is used to update or modify the
existing data in database tables.

Syntax:

UPDATE table_name SET column_name=value WHERE condition;

Example:-

Output:-

4.​ DELETE: The DELETE Command is used to remove single or multiple existing
records from the database tables.

Syntax:

DELETE FROM table_name WHERE condition;

Example:-

Output:-
 Experiment No. 3

Aim:- Implementation of different type of function with suitable example.

Software Required: MySQL

SQL Number Function: MySQL Numeric functions are used primarily for numeric
manipulation and/or mathematical calculations.

SQL Aggregate Function: SQL Aggregate Functions operates on a data group and
returns a singular output. They are mostly used with the GROUP BY clause of the
SELECT statement. Following are some of the aggregate functions:

1. AVG(): This function is used to calculate the average value.

Syntax:

SELECT AVG(column_name) FROM table_name;

Example:

Output:

2. COUNT(): This function is used to count the number of rows.

Syntax:

SELECT COUNT(column_name) FROM table_name

Example:

Output:
3. MAX(): This function returns the maximum value from the column.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example:

Output:

4. MIN(): This function returns the minimum value from the column.

Syntax:

SELECT MIN(column_name) FROM table_name;

Example:

Output:

5. Sum(): Calculates the total sum of values in a numeric column.

Syntax:

SELECT SUM(column_name) FROM table_name;

Example:
Output:

SQL Character Function: SQL string functions are used to manipulate the string
values or character values. Following are some of the Character functions:

1. UCASE(): It converts a string to uppercase.

Syntax:

SELECT UCASE(column_name) FROM table_name;

Example:

Output:

2. LCASE(): It converts a string to lowercase.

Syntax:

SELECT LCASE(column_name) FROM table_name;

Example:
Output:

3. MID(): It returns a substring starting from the specified position.

Syntax:

SELECT MID (column_name, start, length) FROM (table_name);

Example:

Output:
4. LENGTH(): It returns the length of a string in bytes.

Syntax:

SELECT LENGTH(column_name) FROM table_name;

Example:

Output:

SQL Data Function: Date functions are used to perform operations on DATE, TIME,
and DATETIME data types.

1. NOW(): It is used to fetch the system’s current date and time.

Syntax:

SELECT NOW();

Example:

Output:
 Experiment No. 4

Aim:- Implementation of different types of SQL Operators with suitable example.

Software Required: MySQL

Types of Operators:-

Arithmetic Operators: These operators are used to perform operations such as

addition, multiplication, subtraction etc.

1. Addition : Add values on either side of the operator

Syntax:

SELECT num1 + num2;

Example:

Output:

2. Subtraction: Used to subtract the right hand side value from the left hand side
value

Syntax:

SELECT num1 - num2;

Example:

Output:
3. Multiplication: Multiples the values present on each side of the operator

Syntax:

SELECT num1 * num2;

Example:

Output:

4. Division: Divides the left hand side value by the right hand side value

Syntax:

SELECT num1 / num2;

Example:

Output:

5. Modulus: Divides the left hand side value by the right hand side value; and
returns the remainder

Syntax:

SELECT num1 % num2;

Example:
Output:

Comparison Operators: These operators are used to perform operations such

as equal to, greater than, less than etc.

Syntax:

SELECT *FROM (table_name) where (attribute) (operator) (value);

1. Equal to: Used to check if the values of both operands are equal or not. If they
are equal, then it returns TRUE.

Example:

Output:

2. Greater than: Returns TRUE if the value of left operand is greater than the right
operand.

Example:
Output:

3. Less than: Checks whether the value of left operand is less than the right
operand, if yes returns TRUE.

Example:

Output:

4. Greater than or equal to : Used to check if the left operand is greater than or
equal to the right operand, and returns TRUE, if the condition is true.

Example:

Output:
5. Less than or equal to: Returns TRUE if the left operand is less than or equal to
the right operand.

Example:

Output:

6. Not equal to: Used to check if values of operands are equal or not. If they are
not equal then, it returns TRUE.

Example:

Output:
Logical Operators: The logical operators are used to perform operations such
as And, Or, NOT, etc.

Syntax:

SELECT *FROM (table_name) WHERE (attributes) operator (attribute).

1. AND: Allows the user to mention multiple conditions in a WHERE clause.

Example:

Output:

2. OR: Combines multiple conditions in a WHERE clause.

Example:

Output:
3. NOT: A negate operators, used to reverse the output of the logical operator.

Example:

Output:
 Experiment No. 5

Aim:- Implementation of type of joins. a. Inner Join b. Outer Join c. Natural Join
etc.

Software Required: MySQL

Theory: Joins are used in SQL to combine data from multiple tables based on a
related column.

Types of Joins:

1. Inner Join → Returns only matching records from both tables.

2. ​ Left Join → Returns all records from the left table and matching records from
the
right table. If no match, NULL is returned.

3. ​ Right Join → Returns all records from the right table and matching records
from the
left table. If no match, NULL is returned.

4. Full Join → Returns all records when there is a match in either the left or
right table.
If no match, NULL is returned.

5. ​ Natural Join → Automatically joins tables based on columns with the same
name

and datatype, eliminating duplicate columns.

Output:-
 Experiment No.6

Aim: Study and Implementation of

o Group by & having clause

o Order By clause
o Indexing

Software Used: MySQL Workbench

Theory:

These are SQL clauses used in Database Management Systems (DBMS) for
efficient data retrieval and organization.

1. GROUP BY & HAVING → Used to group data and filter aggregated results.
2. ORDER BY → Sorts query results in ascending or descending order.
3. Indexing → Enhances search speed by creating a structured way to access
data quickly.

Code:
 Output:-