Certificate

This is to certify that Mr./Ms. ……………………………………………………… has

performed and completed the below mentioned experiments/presentation/assignments
satisfactorily as part of the term - work, during the academic year 2023-24.

Program:…………………………………………….. SAP ID:………………………………

Course: Database Management System Laboratory Course code: DJS22EL406

Class:……………………. Division:……………… Semester:……………..

INDEX

Sr. Title of the Experiment Page Date Remarks Signature of

No /presentation/ Assignment No. the Teacher
.
Design of ER diagram
1.
2 Design of relational schema

3. Data Definition Language

Statements and constraints
4. Data Manipulation Language
Statements
5. Perform SELECT statement for
retrieval of data from Database
and aggregate functions.
6. Perform various JOIN
operations on Tables
7. Create views and access data
from it using SQL statements.
8 Perform Nested queries

9 Implement Triggers

10 Mini Project

Signature of the Teacher Head of the Department

(Prof.AmitA.Deshmukh)
Name of the Teacher
Prof. Revathi A S Date
 Continuous Assessment for Laboratory
Academic Year 2023 - 2024
Name : SAP ID:
Course: Database Management System Laboratory Course Code: DJS22EL406
Year: SE Sem: IV Batch:
Department: Electronics and telecommunication Engineering

Performance Indicators 1 2 3 4 5 6 7 8 9
(Maximum 5 marks per indicator)
Course Outcome
1. Knowledge
(Factual/Conceptual/Procedural/Metacognitive)
2. Describe
(Factual/Conceptual/Procedural/Metacognitive)
3. Demonstration
(Factual/Conceptual/Procedural/Metacognitive)
4. Strategy (Analyse & / or Evaluate)
(Factual/Conceptual/Procedural/Metacognitive)
5. Interpret/ Develop
(Factual/Conceptual/Procedural/Metacognitive)
Total (out of 25)

Signature of the faculty member

Outstanding (5), Excellent (4), Good (3), Fair (2), Needs Improvement (1)

Signature of the Faculty member:

Name of the Faculty member: Date:

 Bloom’s (Revised) Taxonomy

Source: *Anderson, L.W. (Ed.), Krathwohl, D.R. (Ed.), Airasian, P.W., Cruikshank, K.A., Mayer, R.E., Pintrich, P.R.,
Raths, J., & Wittrock, M.C. (2001). A taxonomy for learning, teaching, and assessing: A revision of Bloom’s
Taxonomy of Educational Objectives (Complete edition). New York: Longman.

Course: Internet Engineering & Network Security Laboratory

DJS22EL406 On completion of the course, learner will be able to: Blooms Level
CO1 Analyze a case study and create ER diagram of the scenario and able to Analyse
create Database schema from this using SQL.
CO2 Write basic SQL queries to apply constraints, insert rows, do basic Create
operations like alter, update and delete, to use basic aggregate functions and
retrieve information from databases.
CO3 Write SQL queries to make joins and views on table. Create

CO4 Perform nested queries and triggers. Create

 Name: Anees Alwani
SapID: 60002220242
Batch:E1 -1

Experiment No-1
DESIGN OF ER DIAGRAM
Aim: Design of ER Diagram

Learning Outcomes: At the end of this experiment students will be able to

1.understand requirements of organizations for designing database

2.design ER diagram based on these requirements

Theory:

Entity Relationship Diagrams are a major data modelling tool and will help organize the
data in your project into entities and define the relationships between the entities. This
process has proved to enable the analyst to produce a good database structure so that the
data can be stored and retrieved in a most efficient manner.

By using a graphical format, it may help communication about the design between the
designer and the user and the designer and the people who will implement it.
Components of an ERD
An ERD typically consists of four different graphical components:
1. Entity.
A data entity is anything real or abstract about which we want to store data. Entity
types fall into five classes: roles, events, locations, tangible things or concepts. E.g.
employee, payment, campus, book. Specific examples of an entity are called
instances. E.g. the employee John Jones, Mary Smith's payment, etc.
2. Relationship.
A data relationship is a natural association that exists between one or more
entities. E.g. Employees process payments.
3. Cardinality.
Defines the number of occurrences of one entity for a single occurrence of the
related entity. E.g. an employee may process many payments but might not
process any payments depending on the nature of her job.
4. Attribute.
A data attribute is a characteristic common to all or most instances of a particular
entity. Synonyms include property, data element, and field. E.g. Name, address,
Employee Number, pay rate are all attributes of the entity employee. An attribute or
combination of attributes that uniquely identifies one and only one instance of an

1
 Name: Anees Alwani
SapID: 60002220242
Batch:E1 -1

entity is called a primary key or identifier. E.g. Employee Number is a primary key
for Employee.

One Methodology for Developing an ERD

Typically, you will start with a case study or perhaps a logical model of the system to be
developed. This document will demonstrate how to use the following process to convert that
information into an ERD.
The process has ten steps:
1. Identify Entities
Identify the roles, events, locations, tangible things or concepts about which the
end-users want to store data.
2. Find Relationships
Find the natural associations between pairs of entities using a relationship matrix.
3. Draw Rough ERD
Put entities in rectangles and relationships on line segments connecting the
entities.
4. Fill in Cardinality
Determine the number of occurrences of one entity for a single occurrence of the
related entity.
5. Define Primary Keys
Identify the data attribute(s) that uniquely identify one and only one occurrence of
each entity.
6. Draw Key-Based ERD
Eliminate Many-to-Many relationships and include primary and foreign keys in
each entity.
7. Identify Attributes
Name the information details (fields) which are essential to the system under
development.
8. Map Attributes
For each attribute, match it with exactly one entity that it describes.
9. Draw fully attributed ERD
Adjust the ERD from step 6 to account for entities or relationships discovered in
step 8.
10. Check Results
Does the final Entity Relationship Diagram accurately depict the system data?

2
 Name: Anees Alwani
SapID: 60002220242
Batch:E1 -1

Problem Statement

1. Design ER diagram for hospital management system

2. Consider a MOVIE database in which data is recorded about the movie industry.
The data requirements are summarized as follows:
■ Each movie is identified by title and year of release. Each movie has a length in
minutes. Each has a production company, and each is classified under one or more
genres (such as horror, action, drama, and so forth). Each movie has one or more
directors and one or more actors appear in it. Each movie also has a plot outline.
Finally, each movie has zero or more quotable quotes, each of which is spoken by a
particular actor appearing in the movie.
■ Actors are identified by name and date of birth and appear in one or more movies.
Each actor has a role in the movie.
■ Directors are also identified by name and date of birth and direct one or more
movies. It is possible for a director to act in a movie (including one that he or she may
also direct).
■ Production companies are identified by name and each has an address. A
production company produces one or more movies

Conclusion

We were able to visualize the ER diagram for a hospital management system and for a movie.

3
 Experiment No-2
Relational Schema

Aim: Design of Relational Schema.

Learning Outcomes: At the end of this experiment students will be able to

1.understand rules required to convert ERD to schema

2.design relational schema based on these rules.

Theory:

1. Entities and Simple Attributes:

An entity type within ER diagram is turned into a table. You may preferably keep the same name for
the entity or give it a sensible name but avoid DBMS reserved words as well as avoid the use of
special characters.
Each attribute turns into a column (attribute) in the table. The key attribute of the entity is the primary
key of the table which is usually underlined. It can be composite if required but can never be null.

A multi-valued attribute is usually represented with a double-line oval.

If you have a multi-valued attribute, take the attribute and turn it into a new entity or table of its own.
Then make a 1:N relationship between the new entity and the existing one. In simple words. 1. Create
a table for the attribute. 2. Add the primary (id) column of the parent entity as a foreign key within the
new table as shown below:

How to Convert ER Diagram to Relational Database

Posted by Imed Bouchrika+on March 10, 2014in Databases9 Comments

The ER Model is intended as a description of real-world entities. Although it is constructed in such a

way as to allow easy translation to the relational schema model, this is not an entirely trivial process.
The ER diagram represents the conceptual level of database design meanwhile the relational schema
is the logical level for the database design. We will be following the simple rules:

1. Entities and Simple Attributes:

An entity type within ER diagram is turned into a table. You may preferably keep the same name for
the entity or give it a sensible name but avoid DBMS reserved words as well as avoid the use of
special characters.

1
Each attribute turns into a column (attribute) in the table. The key attribute of the entity is the primary
key of the table which is usually underlined. It can be composite if required but can never be null.

It is highly recommended that every table should start with its primary key attribute conventionally
named as TablenameID.

Taking the following simple ER diagram:

The initial relational schema is expressed in the following format writing the table names with the
attributes list inside a parentheses as shown below for

Persons( personid , name, lastname, email )

Persons and Phones are Tables. name, lastname, are Table Columns (Attributes).

personid is the primary key for the table : Person

2. Multi-Valued Attributes

A multi-valued attribute is usually represented with a double-line oval.

If you have a multi-valued attribute, take the attribute and turn it into a new entity or table of its own.
Then make a 1:N relationship between the new entity and the existing one. In simple words. 1. Create
a table for the attribute. 2. Add the primary (id) column of the parent entity as a foreign key within the
new table as shown below:

Persons( personid , name, lastname, email )

Phones ( phoneid , personid, phone )

personid within the table Phones is a foreign key referring to the personid of Persons

2
3. 1:1 Relationships

To keep it simple and even for better performances at data retrieval, I would personally recommend
using attributes to represent such relationship. For instance, let us consider the case where the Person
has or optionally has one wife.

4. 1:N Relationships

This is the tricky part ! For simplicity, use attributes in the same way as 1:1 relationship but we have
only one choice as opposed to two choices. For instance, the Person can have a House from zero to
many , but a House can have only one Person. To represent such relationship the personid as the
Parent node must be placed within the Child table as a foreign key but not the other way around as
shown next:

How to Convert ER Diagram to Relational Database

Posted by Imed Bouchrika+on March 10, 2014in Databases9 Comments

The ER Model is intended as a description of real-world entities. Although it is constructed in such a

way as to allow easy translation to the relational schema model, this is not an entirely trivial process.
The ER diagram represents the conceptual level of database design meanwhile the relational schema
is the logical level for the database design. We will be following the simple rules:

1. Entities and Simple Attributes:

An entity type within ER diagram is turned into a table. You may preferably keep the same name for
the entity or give it a sensible name but avoid DBMS reserved words as well as avoid the use of
special characters.

3
Each attribute turns into a column (attribute) in the table. The key attribute of the entity is the primary
key of the table which is usually underlined. It can be composite if required but can never be null.

It is highly recommended that every table should start with its primary key attribute conventionally
named as TablenameID.

Taking the following simple ER diagram:

The initial relational schema is expressed in the following format writing the table names with the
attributes list inside a parentheses as shown below for

Persons( personid , name, lastname, email )

Persons and Phones are Tables. name, lastname, are Table Columns (Attributes).

personid is the primary key for the table : Person

2. Multi-Valued Attributes

A multi-valued attribute is usually represented with a double-line oval.

If you have a multi-valued attribute, take the attribute and turn it into a new entity or table of its own.
Then make a 1:N relationship between the new entity and the existing one. In simple words. 1. Create
a table for the attribute. 2. Add the primary (id) column of the parent entity as a foreign key within the
new table as shown below:

Persons( personid , name, lastname, email )

Phones ( phoneid , personid, phone )

personid within the table Phones is a foreign key referring to the personid of Persons

4
3. 1:1 Relationships

To keep it simple and even for better performances at data retrieval, I would personally recommend
using attributes to represent such relationship. For instance, let us consider the case where the Person
has or optionally has one wife. You can place the primary key of the wife within the table of the
Persons which we call in this case Foreign key as shown below.

Persons( personid , name, lastname, email , wifeid )

Wife ( wifeid , name )

Or vice versa to put the personid as a foreign key within the Wife table as shown below:
Persons( personid , name, lastname, email )
Wife ( wifeid , name , personid)

For cases when the Person is not married i.e. has no wifeID, the attribute can set to NULL

4. 1:N Relationships

This is the tricky part ! For simplicity, use attributes in the same way as 1:1 relationship but we have
only one choice as opposed to two choices. For instance, the Person can have a House from zero to
many , but a House can have only one Person. To represent such relationship the personid as the
Parent node must be placed within the Child table as a foreign key but not the other way around as
shown next:

It should convert to :
Persons( personid , name, lastname, email )
House ( houseid , num , address, personid)

5
5. N:N Relationships

We normally use tables to express such type of relationship. This is the same for N − ary relationship
of ER diagrams. For instance, The Person can live or work in many countries. Also, a country can
have many people. To express this relationship within a relational schema we use a separate table as
shown below:

Persons( personid ,name,lastname,email)

Countries( countryid ,name,code)
HasRelat ( hasrelatid , personid , countryid)

Problem statement

Convert ER diagrams designed in first experiment to relational schema.

Results and Conclusion

We were able to visualize a relation schema for the ER diagrams of hospital Management
system and movie database.

6
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-3
Data Definition Language

Aim: Implement DDL commands and constraints.

Software used: MySQL

Learning Outcomes: At the end of this experiment students will be able to

1.create database and tables in MySQL using DDL commands.

2.use ALTER, RENAME,DROP and DESC commands

3.understand different constraints and use them on tables.

Theory:

DDL- Data Definition Language (DDL) statements are used to define the database structure or
schema. Data Definition Language understanding with database schemas and describes how
the data should consist in the database, therefore language statements like CREATE TABLE
or ALTER TABLE belongs to the DDL. DDL is about "metadata".

DDL includes commands such as CREATE, ALTER and DROP statements. DDL is used to
CREATE, ALTER OR DROP the database objects (Table, Views, Users).

Data Definition Language (DDL) are used different statements :

1. CREATE - to create objects in the database

2. ALTER - alters the structure of the database
3. DROP - delete objects from the database
4. TRUNCATE - remove all records from a table, including all spaces allocated for the
records are removed
5. RENAME - rename an object

1) CREATE
a) To create a database

CREATE DATABASE dbname;

Eg-

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

CREATE DATABASE college;

b) To select existing database
Use dbname;
Eg- USE college;
c) To create a Table

The create table command defines each column of the table uniquely. Each column has minimum of three
attributes.

• Name
• Data type
• Size(column width).

Each table column definition is a single clause in the create table syntax. Each table column definition is
separated from the other by a comma. Finally, the SQL statement is terminated with a semicolon.

Syntax: Create table table name( fieldname1 datatype(),fieldname2 datatype()...);

The Structure of Create Table Command

Table name is Student

Column name Data type Size
Reg_no Varchar 10
Name Char 30
DOB Date
Address varchar 50

1) CREATE TABLE Student

(Reg_no varchar(10)primary key,
Name char(30)NOT NULL,
DOB date,
Address varchar(50));

2) CREATE TABLE Teacher

(
teacher_id int,
name varchar(10) NOT NULL,
address varchar(50),
primary key(teacher_id),
registration_no int,
foreign key (registration_no)
references student(reg_no)
on delete cascade);

2
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

2. ALTER

By The use of ALTER TABLE Command we can modify our exiting table.

Adding New Columns

Syntax:
ALTER TABLE <table_name>
ADD (<NewColumnName> <Data_Type>(<size>),......n)

Example:
ALTER TABLE Student
ADD (Age int(2), Marks int(3));

Dropping a Column from the Table

Syntax:

ALTER TABLE <table_name> DROP COLUMN <column_name>

Example:

ALTER TABLE Student DROP COLUMN Age;

This command will drop particular column

Modifying Existing Table

Syntax:
ALTER TABLE <table_name> MODIFY <column_name> <NewDataType>(<NewSize>)

Example:

ALTER TABLE Student MODIFY name Varchar(40);

Renaming Existing Table

Syntax:

ALTER TABLE <table_name> RENAME <new_table_name>

Example:

ALTER TABLE student RENAME new_student;

ALTER TABLE new_student RENAME student;

3
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

3. RENAME

Syntax:
RENAME TABLE <OldTableName> TO <NewTableName>

Example:
RENAME table Student TO Stud;

4. DROP

Syntax:
DROP TABLE <table_name>

Example:
DROP TABLE Student;

5. TRUNCATE

Syntax:
TRUNCATE TABLE <Table_name>

Example:
TRUNCATE TABLE Student;

6-SHOW
To check available databases and tables

Syntax

SHOW DATABASES;
SHOW TABLES;

7- DESCRIBE

To obtain information about table structure or query execution plans.

DESCRIBE < table_name>

DESC < table_name>

Example-

DESC stud;

CONSTRAINTS

Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole
purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.

4
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Types of constraints

• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• Key Constraints – PRIMARY KEY, FOREIGN KEY

NOT NULL:

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL
values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update
a record without adding a value to this field.

PRIMARY KEY:

Primary key uniquely identifies each record in a table. It must have unique values and cannot contain nulls. In
the below example the ROLL_NO field is marked as primary key, that means the ROLL_NO field cannot have
duplicate and null values.

FOREIGN KEY:

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-
reference between tables.

PROBLEM STATEMENT:

Create a database and tables inside it.

Perform all DDL commands and use constraints on tables.

SOLUTION:

1) CREATE
a) To create a database

b) To select existing database

5
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

c) To create a Table

6
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

7
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

8
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

9
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

10
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

2) ALTER

11
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

3) RENAME

4) DROP

5) TRUNCATE

12
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

6) SHOW

13
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

7) DESCRIBE

14
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

CONCLUSION:

DDL commands like CREATE, ALTER, RENAME, DROP, and DESC empower efficient database
management. Constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL ensure
data integrity. Mastery of these commands is vital for effective schema design, maintaining data
consistency, and enhancing proficiency in MySQL administration through regular practice.

15
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-4
Data Manipulation Language
Aim: Implement DML commands.
A data manipulation language (DML) is a family of computer languages including commands
permitting users to manipulate data in a database. This manipulation involves inserting data
into database tables, retrieving existing data, deleting data from existing tables and modifying
existing data. DML is mostly incorporated in SQL databases.

Software used: MySQL

Learning Outcomes: At the end of this experiment students will be able to

1.populate database and tables in MySQL using DML command INSERT.

2.Use other DML commands like SELECT,DELETE AND UPDATE

1) INSERT.

This command adds one or more records to a database table.

Syntax

INSERT INTO "table_name" ("column1", "column2", ...)

VALUES ("value1", "value2", ...);

Example

1) insert into stud (reg_no, name, dob, address, marks)

values('1','ruchita','2016-02-02', 'abcdef', '80');
2) insert into stud (reg_no, name, dob, address, marks)
values('2','abc','2016-02-02', 'abcdef', '85');

3) insert into stud values ('3','abc','2016-02-02', 'abcdef', '85');

2) SELECT

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

The SELECT statement is used to select data from a database.

Syntax

SELECT * FROM table_name;

Example-

Select * from stud;

3) UPDATE
The UPDATE statement is used to update existing records in a table.

Syntax

UPDATE table_name
SET column1=value1, column2=value2,...
WHERE some_column=some_value;

Example-

1) update stud

set Marks='75'

where reg_no=1;

2) update stud

set name='xyz', marks=90

where reg_no=2;

4) DELETE

This command removes one or more records from a table according to specified conditions.

Syntax

2
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

DELETE FROM table_name

WHERE some_column=some_value;

Example-

1) delete from stud

where reg_no=2;

2) delete from stud;

PROBLEM STATEMENT AND SOLUTIONS:

1) INSERT

3
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

2) SELECT

3) UPDATE

4
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

4) DELETE

CONCLUSION:

The study of DML commands in SQL found that INSERT is good for adding data, UPDATE
can change it, DELETE should be used carefully because it permanently removes data, and
SELECT helps in finding and displaying data. Managing transactions is important for data
safety, and how well commands perform depends on database setup.

5
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-5
SELECT COMMANDS AND AGGREGATE FUNCTIONS
Aim: Implement all select commands.

The most commonly used SQL command is SELECT statement. SQL SELECT statement is
used to query or retrieve data from a table in the database. A query may retrieve information
from specified columns or from all of the columns in the table. To create a simple SQL
SELECT Statement, you must specify the column(s) name and the table name. The whole
query is called SQL SELECT Statement.

Software used: MySQL

Learning Outcomes: At the end of this experiment students will be able to

1.query database using Select statement with where clause

2.use aggregate functions

3.use group by, order by and having.

Theory

Syntax-

SELECT expressions

FROM tables

[WHERE conditions];

Examples-

1. Retrieve all attribute values from employee.

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

select * from employee;

2. Retrieve the cross product of employee and department.

select *
from employee, department;

3. Retrieve employee id of all employees who work for department no.3

select id
from employee
where dno=3;

4. Retrieve employee name and phone numbers of all employees residing at vile
parle.
select ename, phone_no
from employee, department
where location='vile parle' and dnumber=dno;

2
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

5. Retrieve employee name and phone numbers of all employees residing at vile
parle.
select e.ename, e.phone_no
from employee as e, department as d
where d.location='vile parle' and d.dnumber=e.dno;

6. Retrieve distinct addresses from employee.

select distinct address from employee;

3
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

7. Retrieve all addresses from employee.

select all address from employee;

8. Retrieve all employee names whose address is in vile parle.

select ename
from employee
where address like '%vile%';

9. Retrieve all employee names whose phone number starts with 99.
select ename
from employee
where phone_no like '99________';

4
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

10. Retrieve all employee names as employee_name for all employees working for
department no.3
select ename as employee_name
from employee
where dno=3;

11. Retrieve all employees in department no.1 whose salary is between 30000 and
60000
select *
from employee
where (salary between 30000 and 60000) and dno=1;

12. Retrieve employee id, name, phone number and department name of all
employees who work for computer department ordered alphabetically in
ascending order by employee name and descending order by department name.
select id, ename, phone_no,dname
from employee,department

5
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

where dname='comp' and dnumber=dno

order by dname desc, ename asc;

13. Retrieve names of all employees who do not get salary.

select ename
from employee
where salary is null;

14. Retrieve maximum salary offered to employees

select max(salary)
from employee;

15. Retrieve minimum salary offered to employees

select min(salary)
from employee;

6
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

16. Retrieve department wise average salary of employee.

select avg(salary)
from employee
group by dno;

17. Retrieve total number of employees.

select count(*)
from employee;

15. Retrieve department name, department number and average salary for all
departments having average salary greater than 40000

select dname, dnumber, avg(salary)

from employee, department
where dnumber=dno
group by dno

7
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

having avg(salary)>40000;

18. Retrieve total salary given to employees.

select sum(salary)
from employee;

Results:
In conclusion, through the exploration of SQL SELECT statements in MySQL, this
experiment has empowered us with fundamental querying skills. By mastering the
usage of WHERE clauses, aggregate functions, GROUP BY, ORDER BY, and
HAVING, we are now equipped to proficiently extract and manipulate data from
databases, fostering a deeper understanding of database management.

8
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-6
SQL JOINS
Aim: To implement joins in database

Learning Outcomes: At the end of this experiment students will be able to

1.understand the significance of joins and types of it

2.understand sql queries to implement joins

JOINS

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match
between the columns in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows
in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching
rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL RIGHT JOIN Syntax

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Examples-

1) Retrieve customer names having loan or account or both by eliminating duplicates

select cust_name
from borrower
union
select cust_name
from depositor;
2) Retrieve customer names having loan or account or both without eliminating
duplicates
select cust_name
from borrower
union all
select cust_name
from depositor;

3) List customers who have placed order.

select cname, order_number
from customer join order1 on order1.id=customer.customer_id;

4) List all customers whether they placed order or not.

select cname, order_number
from customer left outer join order1 on order1.id=customer.customer_id;

5) List all order numbers whether they are ordered by customers or not.
select cname, order_number
from customer right outer join order1 on order1.id=customer.customer_id;

2
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Codes and Outputs:

JOIN/NATURAL JOIN:

3
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

LEFT JOIN:

4
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

RIGHT JOIN

5
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

INNER JOIN

6
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

EQUI JOIN

Results & Conclusion:

In conclusion, this experiment has provided us with a comprehensive understanding of joins
in database management. By exploring INNER JOIN, LEFT JOIN, and RIGHT JOIN
operations, we have gained the skills to combine data from multiple tables effectively,
enhancing their ability to retrieve and analyse interconnected data sets using SQL queries.

7
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

8
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-7
Views
Aim: To implement Views in database

Learning Outcomes: At the end of this experiment students will be able to

1.understand the use of views in database.

2.understand sql queries to implement various views in SQL,

Theory

"views" refer to virtual tables that are derived from the result of a predefined SQL query.
These views do not contain any data themselves; instead, they are dynamic representations of
data from one or more tables in a database. Views are useful for simplifying complex queries,
providing a layer of abstraction, and controlling access to data.

When you create a view in SQL, you define a query that selects data from one or more tables,
possibly applying filters, joins, or calculations. This query is stored as metadata in the
database, and when you query the view, the database engine dynamically executes the
underlying query and returns the result set as if it were a table.

Views offer several benefits:

Simplicity: Views can simplify complex queries by encapsulating logic into a single, easy-to-
use object. This can improve readability and maintainability of your SQL code.

Abstraction: Views provide a layer of abstraction over the underlying tables, allowing you to
hide the complexity of the database schema from end users or applications. Changes to the
underlying schema can be managed within the view definition without affecting applications
that use the view.

Security: Views can be used to control access to sensitive data by restricting the columns or
rows that users are allowed to see. By granting permissions to views instead of tables, you
can enforce fine-grained access control.

Performance: In some cases, views can improve performance by precomputing complex joins
or aggregations and storing the results, reducing the overhead of executing the same query
multiple times.

Overall, views are a powerful feature of SQL that enable developers and database
administrators to simplify complex queries, manage data access, and improve performance.

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Codes and Outputs:

2
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

3
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

4
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

5
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

6
Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

7
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Results & Conclusion:

In conclusion, the implementation of views in databases has provided students with
invaluable insights into enhancing database management. Through understanding the concept
and practical application of views, participants can simplify queries, ensure data security, and
optimize performance. This experiment equips them with essential skills to navigate and
manipulate data effectively in SQL environments.

8
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-8
Nested Queries
Aim: To use nested queries

Learning Outcomes: At the end of this experiment students will be able to

1.understand the use and types of nested queries

2.understand sql syntax for nested queries.

Theory

A Subquery or Inner query or Nested query is a query within another SQL query and
embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further
restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements
along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

1) Find the customers who are borrowers from the bank and who appear in the list of
account holders.
SELECT cust_name
from borrower
where cust_name in ( select cust_name
from depositor);

2) Find the customers who are borrowers from the bank but do not hold an account.
SELECT cust_name
from borrower
where cust_name not in ( select cust_name

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

from depositor);

3) Find all customers who have balance greater than at least one customer located at
branch Mumbai.
select cust_name
from depositor
where balance> some ( select balance
from depositor
where branch='mumbai');

4) Find customers who have balance greater than or equal to that of each customer
located at Mumbai branch.
select cust_name
from depositor
where salary>=all ( select salary
from depositor
where branch='mumbai');

5) Find the customers who are borrowers from the bank and who appear in the list of
account holders using EXISTS.

SELECT cust_name
from borrower
where exists ( select *
from depositor
where borrower.cust_name=depositor.cust_name);

6) Find the customers who are borrowers from the bank but do not hold an account
using NOT EXISTS.
SELECT cust_name
from borrower
where not exists ( select *
from depositor
where borrower.cust_name=depositor.cust_name);

7) create a backup for borrower table by copying all records of borrower to a new table
borrower_bkp.
insert into borrower_bkp
select * from borrower;

2
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Codes and Outputs:

Results & Conclusion:

Through the exploration of nested queries in SQL, this experiment has provided us with a
deeper understanding of query composition and data manipulation. By comprehending the
syntax and applications of subqueries, we are now adept at leveraging nested queries to refine
and optimize their database interactions effectively.

3
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Experiment No-8
Triggers
Aim: To use triggers

Learning Outcomes: At the end of this experiment students will be able to

1.understand the use of triggers

2.learn syntax of triggers

Theory

A trigger is a set of actions that are run automatically when a specified change operation
(SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers
are useful for tasks such as enforcing business rules, validating input data, and keeping an
audit trail.

A trigger is a named database object that is associated with a table, and it activates when a
particular event (e.g. an insert, update or delete) occurs for the table. The statement CREATE
TRIGGER creates a new trigger in MySQL.

Syntax

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

Example

1) delimiter //
create trigger depocheck
before insert on depositor

1
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

FOR EACH ROW

IF NEW.salary is null
THEN
SET NEW.salary = 5000;
END IF;
//
2) delimiter //
create trigger feed_depositor_bkp
after insert on depositor
FOR EACH ROW
insert into depositor_bkp(cust_name,salary,branch) values
(NEW.cust_name, new.salary,new.branch);
//
3) delimiter //
create trigger employeetrig
before update on employee
for each row
if new.dno is null then
set new.dno= 01;
end if;
//
4) delimiter //
create trigger total_sal1
after insert on employee
for each row
if new.dno is not null then
update department
set total_sal=total_sal+new.salary
where dnumber=new.dno;
end if;
//

5) drop trigger feed_depositor_bkp;

Codes and Outputs:

2
 Name: Anees Alwani
SapID: 60002220242
Batch: E1-1

Results & Conclusion:

Through this experiment, we have gained a comprehensive understanding of triggers in

MySQL. By delving into their syntax and practical applications, we now possess the skills to
implement triggers effectively, automating actions based on specified events and enhancing
database integrity and functionality.