Information System Management (ISM) Lab Project

Submitted in partial fulfillment of requirement of Bachelor of

Commerce (Hons.) (B.COM(H))

B.COM(H) 5thSemester (Morning Shift)

Batch 2020-2023

Submitted to: Submitted by:

Dr. Ruchi Singhal Dhananjay Sharma

(Assistant Professor) (01114188820)

JAGANNATH INTERNATIONAL MANAGEMENT SCHOOL, KALKAJI

MOR, Pocket 105, Kalkaji, New Delhi - 110 019
Ph.: +91 11 4061 9200 (100 lines)

1
Fax: +91 11 2622 0998
E-mail: [email protected]
Website: www.ipu.jimskalkaji.com

ACKNOWLEDGEMENT

It gives me immense pleasure to express my deepest sense of gratitude

and sincere thanks to Ruchi mam for her constant support and valuable
guidance, encouragement and help. Her useful suggestions for this
whole project and cooperation are sincerely acknowledged.

At the end I would like to express my sincere thanks to all my friends,

colleagues and others who helped me directly or indirectly during this
project.

Dhananjay Sharma
(01114188820)

2
Content
S NO. TOPICS Pg.no.
1 What is DBMS? Explain its features 5

2 Define various keys with examples 7

3 What is SQL? Discuss its advantages 13

4 What is MYSQL? Discuss the types of commands in 16
MYSQL
5 Show the steps to connect to the MYSQL sever through 24
XAMPP
6 Create table EMP in SQL with 5 columns 26

7 Write a query selecting 3 columns of employee 26-27

8 Write a query selecting E_name in descending order 27

9 Insert records in table EMP 28

10 Change the name of the employee with E_ID=5asParth 28

11 Add a column “Salary” after DOJ and assign salary to 28-29

each employee.
12 Write a query to find the maximum salary 29
13 Write a query to find the name of the employee having 30
maximum salary

3
14 Write a query to display the dept_no in descending 30-31
order
15 Write a query to count the number of employees 32
belonging to dept_id 12 the table EMP using a single
query
16 Delete the record with E_id=6,7,8 using a single query. 33

17 Delete the column DOJ from the table EMP 33-34

18 Delete all the records from the table EMP using a single 34-35
query
19 Delete the table EMP 35

20 Delete the database Detail 35-36

21 Create a table for Bank detail Bank Customers with 36

Primary Key as their account number.
22 Insert the database for 10 customers in bank_detail 37

23 Show the entry for Null Entry for Customer account 37-38
number in table bank_detail
24 Define the functions with examples 38-53

4
DBMS
Database Management System (DBMS) is software for storing and retrieving users’ data while
considering appropriate security measures. It consists of a group of programs that manipulate
the database. The DBMS accepts the request for data from an application and instructs the
operating system to provide the specific data. In large systems, a DBMS helps users and other
third-party software store and retrieve data.

DBMS allows users to create their own databases as per their requirements. The term “DBMS”
includes the user of the database and other application programs. It provides an interface
between the data and the software application.

Let us see a simple example of a university database. This database is maintaining information
concerning students, courses, and grades in a university environment. The database is
organized as five files:

 The STUDENT file stores the data of each student

 The COURSE file stores contain data on each course.
 The SECTION stores information about sections in a particular course.
 The GRADE file stores the grades which students receive in the various sections
 The TUTOR file contains information about each professor.

To define DBMS:

We need to specify the structure of the records of each file by defining the different types of
data elements to be stored in each record.

We can also use a coding scheme to represent the values of a data item.

5
Basically, your Database will have 5 tables with a foreign key defined amongst the various
tables.

Popular DBMS Software

Here is the list of some popular DBMS systems:

 MySQL
 Microsoft Access
 Oracle
 PostgreSQL
 dBase
 FoxPro
 SQLite
 IBM DB2
 LibreOffice Base
 MariaDB
 Microsoft SQL Server

Characteristics of DBMS

Here are the characteristics and properties of a Database Management System:

 Provides security and removes redundancy

 Self-describing nature of a database system
 Insulation between programs and data abstraction
 Support of multiple views of the data
 Sharing of data and multiuser transaction processing
 Database Management Software allows entities and relations among them to form
tables.
 It follows the ACID concept (Atomicity, Consistency, Isolation, and Durability).
 DBMS supports a multi-user environment that allows users to access and manipulate
data in parallel.

6
Keys

Keys play an important role in the relational database.

It is used to uniquely identify any record or row of data from the table. It is also used to
establish and identify relationships between tables.

For example, ID is used as a key in the student table because it is unique for each student. In
the PERSON table, passport number, license number, SSN are keys since they are unique for
each person.

Types of keys:

1. Primary key

It is the first key used to identify one and only one instance of an entity uniquely. An entity can
contain multiple keys, as we saw in the PERSON table. The key which is most suitable from
those lists becomes a primary key.

In the EMPLOYEE table, ID can be the primary key since it is unique for each employee. In the
EMPLOYEE table, we can even select License Number and Passport Number as primary keys
since they are also unique.

7
For each entity, the primary key selection is based on requirements and developers.

2. Candidate key

A candidate key is an attribute or set of attributes that can uniquely identify a tuple.

Except for the primary key, the remaining attributes are considered a candidate key. The
candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the
attributes, like SSN, passport Number, license Number, etc., are considered a candidate key.

3. Super Key

8
Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a
candidate key.

For example: In the above EMPLOYEE table, for (EMPLOEE_ID, EMPLOYEE_NAME),

the name of two employees can be the same, but their EMPLYEE_ID can't be the
same. Hence, this combination can also be a key.

The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

4. Foreign key

Foreign keys are the column of the table used to point to the primary key of another
table.

Every employee works in a specific department in a company, and employee and

department are two different entities. So, we can't store the department's information in
the employee table. That's why we link these two tables through the primary key of one
table.

We add the primary key of the DEPARTMENT table, Department_Id, as a new attribute
in the EMPLOYEE table.

9
In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.

5. Alternate key

There may be one or more attributes or a combination of attributes that uniquely identify
each tuple in a relation. These attributes or combinations of the attributes are called the
candidate keys. One key is chosen as the primary key from these candidate keys, and
the remaining candidate key, if it exists, is termed the alternate key. In other words, the
total number of the alternate keys is the total number of candidate keys minus the
primary key. The alternate key may or may not exist. If there is only one candidate key
in a relation, it does not have an alternate key.

For example, employee relation has two attributes, Employee_Id and PAN_No, that act
as candidate keys. In this relation, Employee_Id is chosen as the primary key, so the
other candidate key, PAN_No, acts as the Alternate key.

10
6. Composite key

Whenever a primary key consists of more than one attribute, it is known as a composite
key. This key is also known as Concatenated Key.

For example, in employee relations, we assume that an employee may be assigned

multiple roles, and an employee may work on multiple projects simultaneously. So, the
primary key will be composed of all three attributes, namely Emp_ID, Emp_role, and
Proj_ID in combination. So, these attributes act as a composite key since the primary
key comprises more than one attribute.

11
7. Artificial key

The key created using arbitrarily assigned data are known as artificial keys. These keys are
created when a primary key is large and complex and has no relationship with many other
relations. The data values of the artificial keys are usually numbered in a serial order.

For example, the primary key, which is composed of Emp_ID, Emp_role, and Proj_ID, is large
in employee relations. So, it would be better to add a new virtual attribute to identify each tuple
in the relation uniquely.

12
SQL
SQL is the standard language for dealing with Relational Databases. SQL can be used to
insert, search, update, and delete database records. SQL can do lots of other operations,
including optimizing and maintenance of databases.

What is SQL used for?

Here are important reasons for using SQL

 It helps users to access data in the RDBMS system.

 It helps you to describe the data.
 It allows you to define the data in a database and manipulate that specific data.
 With the help of SQL, you can create and drop databases and tables.
 SQL offers you to use the function in a database, create a view, and stored procedure.
 You can set permissions on tables, procedures, and views.

What are the Advantages of SQL?

SQL (Structured Query Language) is a data-driven programming language that is primarily

used to query and manipulate data in relational databases. It is a high-level domain language
because it is hardware independent and is therefore used by many businesses to handle their
databases. The advantages of knowing SQL is expanding and significant. SQL has seen a
significant increase in usage over the last few years.

The following are some of the advantages of utilizing SQL:

1. Faster and Efficient Query Processing

13
SQL works with an efficient speed. This high speed can boost the amount of data retrieval. It
can give users a rapid and effective means to obtain, alter, or store data.

SQL can quickly and efficiently retrieve a large volume of data records from a database.
Compared to an unstructured database such as MongoDB, it is a relational database that can
characterize the data in a structured way. Operations like insertion, deletion, querying,
manipulation, and calculations on data via analytical queries in a relational database can be
accomplished in a matter of seconds.

2. No Need for Coding Skills

There is no need for large and complex code lines for data extraction. One reason for using
SQL is that it does not require extensive coding and program writing knowledge. It is easy to
maintain database systems without having to write a good amount of code. There is no need
for large complex lines of code for data extraction.

SQL is mostly made up of English statements, making it simple to learn and write SQL queries.
All primary keywords, like SELECT, INSERT INTO, DELETE, UPDATE, etc., can be used to
carry out operations. Also, the syntactical rules are simple and easy to understand, making it a
user-friendly language.

3. Portable

Portability is an extremely desirable feature in any program that stores data in a database. A
software developer would like to use a small in-memory database, such as Apache Derby,
while creating an application.

SQL is highly portable because it is employed in programs on PCs, servers, tablets, and
independent laptops running operating systems such as Windows, Linux, Mac, and even some
mobile phones. It can also be embedded with other programs based on the requirements.

It can be used on local systems (internet and the intranet). SQL databases can be easily
transferred from one device to another.

4. Standardized Language

14
SQL is a relational database query language that has been accepted by ISO and ANSI. It
gives all users a consistent platform worldwide due to proper documentation and years of
establishment.

In 19861986 and 19871987, ANSI (American National Standards Institute) and ISO
(International Standards Organization) standardized the language. The most recent standard
part was produced in 20112011.

5. Interactive Language

SQL is a domain language that is simple to learn and easy to understand. It can also be used
to interface with databases and receive responses to complicated queries in seconds.

It becomes an interactive language for its users because it offers easy commands for all
purposes. SQL commands are also understandable to non-programmers.

SQL is used to build and administer large databases, including data sharing, updating, and
retrieval from numerous tables. This is the primary reason organizations employ SQL in
various applications such as web development, data analysis, IT support, etc.

6. Multiple Data Views

The SQL language gives each user a unique view of the data. A view is a SQL statement that
includes specified SQL queries that are stored in the database. It can be built from one or
more tables based on the queries written or the aim that the view satisfies.

SQL can provide distinct views of the database's structure and content to different users.

Check out this article to learn more about Views in SQL.

7. Internet Usage

Three-tier Internet architectures are compatible with SQL architecture. Client, application
server, and database components make up the architecture.

8. Large User Community

15
The SQL programming language is nearly 50 years old and is incredibly mature and still
extensively used. It boasts a vibrant network of specialists eager to provide advice and well-
established best practices.

There are several opportunities to improve one's skills and collaborate. Consultants and SQL
suppliers can provide extra assistance if needed. Your developers will be able to find the
answers they require using SQL.

MySQL
MySQL is an open-source relational database management system. As with other relational
databases, MySQL stores data in tables made up of rows and columns. Users can define,
manipulate, control, and query data using Structured Query Language, more commonly known
as SQL. MySQL’s name is a combination of “My,” the name of MySQL creator Michael
Widenius’s daughter, and “SQL”.

A flexible and powerful program, MySQL is the most popular open-source database system in
the world. As part of the widely-used LAMP technology stack (which consists of a Linux-based
operating system, the Apache web server, a MySQL database, and PHP for processing), it’s
used to store and retrieve data in a wide variety of popular applications, websites, and
services.

SQL Commands

SQL commands are instructions. It is used to communicate with the database. It is also used
to perform specific tasks, functions, and queries of data.

SQL can perform various tasks like create a table, add data to tables, drop the table, modify
the table, set permission for users.

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

16
1. Data Definition Language (DDL)

DDL changes the structure of the table like creating a table, deleting a table, altering a table,
etc.

All the command of DDL is auto-committed that means it permanently save all the changes in
the database.

Here are some commands that come under DDL:

 CREATE
 ALTER
 DROP
 TRUNCATE

17
CREATE

It is used to create a new table in the database.

Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example: CREATE TABLE EMPLOYEE(Name Dhananjay(20), Email VARCHAR2(100), DOB

DATE);

DROP

It is used to delete both the structure and record stored in the table

Syntax;

DROP TABLE table_name;

Example;

DROP TABLE EMPLOYEE;

ALTER

It is used to alter the structure of the database. This change could be either to modify the
characteristics of an existing attribute or probably to add a new attribute.

Syntax; ALTER TABLE table_name ADD column_name COLUMN-definition;

To add a new column in the table

18
ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE table_name MODIFY(column_definitions....);

EXAMPLE

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

TRUNCATE

It is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

DML commands are used to modify the database. It is responsible for all form of changes in
the database.

The command of DML is not auto-committed that means it can't permanently save all the
changes in the database. They can be rollback.

Here are some commands that come under DML:

 INSERT
 UPDATE
 DELETE

INSERT

The INSERT statement is a SQL query. It is used to insert data into the row of a table.
19
Syntax:

INSERT INTO TABLE_NAME

(col1, col2, col3,.... col N)

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

Or

INSERT INTO TABLE_NAME

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

For example:

INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

UPDATE

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE

CONDITION]

For example:

UPDATE students

SET User_Name = 'Sonoo'

WHERE Student_Id = '3'

DELETE

It is used to remove one or more row from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

20
For example:

DELETE FROM javatpoint

WHERE Author="Sonoo";

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

 Grant
 Revoke

Grant

It is used to give user access privileges to a database.

Example

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

Revoke

It is used to take back permissions from the user.

Example

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

21
These operations are automatically committed in the database that's why they cannot be used
while creating tables or dropping them.

Here are some commands that come under TCL:

 COMMIT
 ROLLBACK
 SAVEPOINT

Commit

Commit command is used to save all the transactions to the database.

Syntax:

COMMIT;

Example:

DELETE FROM CUSTOMERS

WHERE AGE = 25;

COMMIT;

Rollback

Rollback command is used to undo transactions that have not already been saved to the
database.

Syntax:

ROLLBACK;

Example:

DELETE FROM CUSTOMERS

WHERE AGE = 25;

22
ROLLBACK;

SAVEPOINT

It is used to roll the transaction back to a certain point without rolling back the entire
transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

SELECT

This is the same as the projection operation of relational algebra. It is used to select the
attribute based on the condition described by WHERE clause.

Syntax:

SELECT expressions

FROM TABLES

WHERE conditions;

For example:

SELECT emp_name

FROM employee

WHERE age > 20;

23
MYSQL sever through XAMPP
The XAMPP Stack of Software is an Open-source Localhost-based server encompassing
software packages that offer several functionalities. You can use the XAMPP Control Panel to
start or deactivate all Software that comes with it.

With XAMPP in place, you can test projects/modifications before releasing them to the
Internet. The MySQL Database forms a vital component of the XAMPP Stack, being one of the
most significant features offered by it. With XAMPP, you can use phpMyAdmin to create & use
your XAMPP MySQL Database in minutes.

Step 1: Opening XAMPP

Go to your system’s XAMPP folder or simply click the XAMPP Icon to open it. The Control
Panel is now visible, and you may use it to start or stop any module.

Step 2: Starting XAMPP

Select the “Start” option for the Apache and MySQL modules, respectively. The user will see
the following screen once it has started working:

24
Step 3: Listing commands in Command prompt for establishing connection:

25
Create table EMP in SQL with 5 columns

Writing a query selecting 3 columns of

employee

26
E_name in descending order

Insert records in table EMP

27
Add a column “Salary” after DOJ and
assign salary to each employee

28
Write a query to find the maximum salary

29
Writing a query to find the name of the
employee having maximum salary

Writing a query to display the dept_no in

descending order

30
31
Write a query to count the number of
employees belonging to dept_id 12 the
table EMP using a single query

32
Deleting the record with Emp_id = 6, 7, 8
using a single query

Delete the column DOJ from the table

EMP
33
Delete all the records from the table EMP
using a single query

34
Delete the table EMP

Delete the database Detail

35
Create a table for Bank detail Bank
Customers with Primary Key as their
account number

36
Insert the database for 10 customers in
bank_detail

Show the entry for Null Entry for

Customer account number in table
bank_detail

37
Define the functions with examples
Structured Query Language aka SQL is used to handle data in databases. It provides various
in-built functions and commands to access and manage databases according to our
requirements.

SQL functions are categorized into the following two categories:

 Aggregate Functions
 Scalar Functions

Aggregate SQL Functions

The Aggregate Functions in SQL perform calculations on a group of values and then return a
single value. Following is few of the most commonly used Aggregate Functions:

38
Function Description

SUM () Used to return the sum of a group of values.

COUNT () Returns the number of rows either based on a

condition, or without a condition.
AVG () Used to calculate the average value of a numeric
column.
MIN () This function returns the minimum value of a
column.
MAX () Returns a maximum value of a column.
FIRST () Used to return the first value of the column.
LAST () This function returns the last value of the column.

Let us look into each one of the above functions in depth. For your better understanding, I will
be considering the following table to explain to you all the examples.

Student ID Student Name Marks

1 Sanjay 64
2 Varun 72
3 Akash 45
4 Rohit 86
5 Anjali 92

SUM()

Used to return a total sum of numeric column which you choose.

Syntax:

1. SELECT SUM(ColumnName)

39
2. FROM TableName;

Example:

Write a query to retrieve the sum of marks of all students from the students’ table.

1. SELECT SUM(Marks)
2. FROM Students;

Output:

 `359

COUNT()

Returns the number of rows present in the table either based on some condition or without any
condition.

Syntax:

40
1. SELECT COUNT(ColumnName)
2. FROM TableName
3. WHERE Condition;

Example:

Write a query to count the number of students scoring marks > 75 from the students’ table.

1. SELECT COUNT(StudentID)
2. FROM Students
3. WHERE Marks >75;

Output:

 2

AVG()

This function is used to return the average value of a numeric column.

Syntax:

1. SELECT AVG(ColumnName)
2. FROM TableName;

Example:

Write a query to calculate the average marks of all students from the Students table.

41
1. SELECT AVG(Marks)
2. FROM Students;

Output:

 71.8

MIN()

Used to return the minimum value of a numeric column.

Syntax:

1. SELECT MIN(ColumnName)
2. FROM TableName;

Example:

Write a query to retrieve the minimum marks out of all students from the Students table.

1. SELECT MIN(Marks)
2. FROM Students;

Output:

 45

42
MAX()

Returns the maximum value of a numeric column.

Syntax:

1. SELECT MAX(ColumnName)
2. FROM TableName;

Example:

Write a query to retrieve the maximum marks out of all students from the students’ table.

1. SELECT MAX(Marks)
2. FROM Students;

Output:

 92

43
FIRST()

This function returns the first value of the column which you choose.

Syntax:

1. SELECT FIRST(ColumnName)
2. FROM TableName;

Example:

Write a query to retrieve the marks of the first student.

1. SELECT FIRST(Marks)
2. FROM Students;
 Output: 64

LAST()

Used to return the last value of the column which you choose.

Syntax:

1. SELECT LAST(ColumnName)
2. FROM TableName;

Example:

Write a query to retrieve the marks of the last student.

1. SELECT LAST(Marks)
2. FROM Students;
 Output: 92

44
Scalar SQL Functions

The Scalar Functions in SQL are used to return a single value from the given input value.
Following is few of the most commonly used Aggregate Functions:

Let us look into each one of the above functions in depth.

Function Description
LCASE() Used to convert string column values to
lowercase
UCASE() This function is used to convert a string column
values to Uppercase.
LEN() Returns the length of the text values in the
column.
MID() Extracts substrings in SQL from column values
having String data type.
ROUND() Rounds off a numeric value to the nearest
integer.
NOW() This function is used to return the current system
date and time.
FORMAT() Used to format how a field must be displayed.

LCASE()

Used to convert values of a string column to lowercase characters.

Syntax:

1. SELECT LCASE(ColumnName)
2. FROM TableName;

45
Example:

Write a query to retrieve the names of all students in lowercase.

1. SELECT LCASE(StudentName)
2. FROM Students;

Output:

 Sanjay
 Varun
 Akash
 Rohit
 Anjali

UCASE()

Used to convert values of a string column to uppercase characters.

Syntax:

1. SELECT UCASE(ColumnName)
2. FROM TableName;

46
Example:

Write a query to retrieve the names of all students in lowercase.

1. SELECT UCASE(StudentName)
2. FROM Students;

Output:

 SANJAY
 VARUN
 AKASH
 ROHIT
 ANJALI

LEN()

Used to retrieve the length of the input string.

Syntax:

 SELECT LENGTH(String) AS SampleColumn;

Example:

47
Write a query to extract the length of the student name “Sanjay”.

 SELECT LENGTH(“Sanjay”) AS StudentNameLen;

Output:

 6

MID()

This function is used to extract substrings from columns having string data type.

Syntax:

1. SELECT MID(ColumnName, Start, Length)

2. FROM TableName;

48
Example:

Write a query to extract substrings from the StudentName column.

1. SELECT MID(StudentName, 2, 3)
2. FROM Students;

Output:

 anj
 aru
 kas
 ohi
 nja

ROUND()

This function is used to round off a numeric value to the nearest integer.

49
Syntax:

1. SELECT ROUND(ColumnName, Decimals)

2. FROM TableName;

Example:

For this example, let us consider the following Marks table in the Students table.

StudentID StudentName Marks

1 Sanjay 90.76
2 Varun 80.45
3 Akash 54.32
4 Rohit 72.89
5 Anjali 67.66

Write a query to round the marks to the integer value.

1. SELECT ROUND(Marks)
2. FROM Students;

Output:

 91
 80
 54
 73
 68

50
NOW()

Used to return the current date and time. The date and time are returned in the “YYYY-MM-DD
HH-MM-SS” format.

Syntax:

 SELECT NOW();

Example:

Write a query to retrieve the current date and time

 SELECT NOW();

Output:

NOW()

51
2022-12-25 11:20:38

FORMAT()

This function formats the way a field must be displayed.

Syntax:

 FORMAT(InputValue, Format)

Example:

Write a query to display the numbers “123456789” in the format “###-###-###”

 SELECT FORMAT(123456789, “###-###-###”);

Output:

52
 123-456-789

53