Difference between File System and DBMS

A file system and a DBMS are two kinds of data management systems that
are used in different capacities and possess different characteristics. A File
System is a way of organizing files into groups and folders and then storing
them in a storage device. It provides the media that stores data as well as
enables users to perform procedures such as reading, writing, and even
erasure.
On the other hand, DBMS is a more elaborate software application that is
solely charged with the responsibility of managing large amounts of
structured data. It provides functionalities such as query, index, transaction,
as well as data integrity. Although the file system serves well for the purpose
of data storage for applications where data is to be stored simply and does
not require any great organization, DBMS is more appropriate for
applications where data needs to be stored and optimized for organizational
and structural needs, security, etc.
File System
The file system is basically a way of arranging the files in a storage medium
like a hard disk. The file system organizes the files and helps in the retrieval
of files when they are required. File systems consist of different files which
are grouped into directories. The directories further contain other folders
and files. The file system performs basic operations like management, file
naming, giving access rules, etc.
Example: NTFS(New Technology File System) , EXT(Extended File
System).
DBMS ( Database Management System)
Database Management System is basically software that manages the
collection of related data. It is used for storing data and retrieving the data
effectively when it is needed. It also provides proper security measures for
protecting the data from unauthorized access. In Database Management
System the data can be fetched by SQL queries and relational algebra. It
also provides mechanisms for data recovery and data backup.
Example:
Oracle, MySQL, MS SQL server.
Difference Between File System and DBMS

Basics File System DBMS

The file system is a way

of arranging the files in a DBMS is software for
storage medium within a managing the database.
Structure computer.

Data Redundant data can be In DBMS there is no

Redundancy present in a file system. redundant data.

It doesn't provide Inbuilt

It provides in house tools for
mechanism for backup
backup and recovery of data
Backup and and recovery of data if it
even if it is lost.
Recovery is lost.

There is no efficient
Efficient query processing is
Query query processing in the
there in DBMS.
processing file system.

There is less data There is more data

consistency in the file consistency because of the
Consistency system. process of normalization .

It has more complexity in

It is less complex as
handling as compared to the
compared to DBMS.
Complexity file system.

File systems provide less DBMS has more security

Security security in comparison mechanisms as compared to
Constraints to DBMS. file systems.
Basics File System DBMS

It has a comparatively
It is less expensive than
higher cost than a file
DBMS.
Cost system.

In DBMS data
independence exists, mainly
of two types:
There is no data
1) Logical Data
independence.
Independence .
Data 2)Physical Data
Independence Independence.

Only one user can access Multiple users can access

User Access data at a time. data at a time.

The users are not The user has to write

required to write procedures for managing
Meaning procedures. databases

Data is distributed in
Due to centralized nature
many files. So, it is not
data sharing is easy
Sharing easy to share data.

It give details of storage

It hides the internal details
Data and representation of
of Database
Abstraction data

Integrity Integrity Constraints are Integrity constraints are easy

Constraints difficult to implement to implement
Basics File System DBMS

To access data in a file ,

user requires attributes No such attributes are
such as file name, file required.
Attribute s location.

Example Cobol , C++ Oracle , SQL Server

Advantages of Database Management System
Database Management System (DBMS) is a collection of interrelated data
and a set of software tools/programs that access, process, and manipulate
data. It allows access, retrieval, and use of that data by considering
appropriate security measures and is really useful for better data integration
and security.
Advantages of DBMS
1. Data Security: DBMS enhances data security through access control
and encryption. It enforces privacy policies and prevents unauthorized
access. As user numbers grow, it mitigates associated risks of
breaches.
2. Data integration: DBMS unifies data from different sources into a
centralized system. This provides a coherent organizational view of
operations. It helps to keep track of how one segment of the company
affects another segment.
3. Data abstraction: Since many complex algorithms are used by the
developers to increase the efficiency of databases that are being
hidden by the users through various data abstraction levels to allow
users to easily interact with the system.
4. Reduction in data Redundancy: DBMS avoids data duplication by
enforcing unique constraints. It removes unnecessary repetitive
entries in databases. This ensures efficient use of storage and
improves consistency. for e.g. - If there are two same students in
different rows, then one of the duplicate data will be deleted.
5. Data sharing: A DBMS provides a platform for sharing data across
multiple applications and users, which can increase productivity and
collaboration.
6. Data consistency and accuracy: DBMS enforces integrity
constraints to maintain valid data. It minimizes discrepancies by
syncing updates across all views. This reduces errors and ensures
reliable & consistent data.
7. Data organization: A DBMS provides a systematic approach to
organizing data in a structured way, which makes it easier to retrieve
and manage data efficiently.
8. Efficient data access and retrieval: DBMS allows for efficient data
access and retrieval by providing indexing and query optimization
techniques which reduces time taken to retrieve large datasets. It
boosts system performance and user satisfaction.
9. Concurrency and maintained Atomicity: That means, if some
operation is performed on one particular table of the database, then
the change must be reflected for the entire database. The DBMS
allows concurrent access to multiple users by using the
synchronization technique.
10. Scalability and flexibility: DBMS is highly scalable and can
easily accommodate changes in data volumes and user requirements.
It allows flexible schema modifications and expansion. This makes it
ideal for dynamic organizational environments and can scale up or
down depending on the needs of the organization.
DBMS Architecture 1-level, 2-Level, 3-Level
A DBMS architecture defines how users interact with the database to read,
write, or update information. A well-designed architecture and schema (a
blueprint detailing tables, fields and relationships) ensure data consistency,
improve performance and keep data secure.
Types of DBMS Architecture
There are several types of DBMS Architecture that we use according to the
usage requirements.
 1-Tier Architecture
 2-Tier Architecture
 3-Tier Architecture
1-Tier Architecture
In 1-Tier Architecture, the user works directly with the database on the same
system. This means the client, server and database are all in one application.
The user can open the application, interact with the data and perform tasks
without needing a separate server or network connection.

A common example is Microsoft Excel. Everything from the user interface

to the logic and data storage happens on the same device. The user enters
data, performs calculations and saves files directly on their computer.
2-Tier Architecture
The 2-tier architecture is similar to a basic client-server model. The
application at the client end directly communicates with the database on the
server side. APIs like ODBC and JDBC are used for this interaction. The
server side is responsible for providing query processing and transaction
management functionalities.

On the client side, the user interfaces and application programs are run. The
application on the client side establishes a connection with the server side
to communicate with the DBMS. For Example: A Library Management
System used in schools or small organizations is a classic example of two-
tier architecture.
3-Tier Architecture
In 3-Tier Architecture, there is another layer between the client and the
server. The client does not directly communicate with the server. Instead, it
interacts with an application server which further communicates with the
database system and then the query processing and transaction management
takes place. This intermediate layer acts as a medium for the exchange of
partially processed data between the server and the client. This type of
architecture is used in the case of large web applications.
Example: E-commerce Store
 User: You visit an online store, search for a product and add it to your
cart.
 Processing: The system checks if the product is in stock, calculates
the total price and applies any discounts.
 Database: The product details, your cart and order history are stored
in the database for future reference.
SQL Commands | DDL, DQL, DML, DCL and TCL Commands
SQL commands are the fundamental building blocks for communicating
with a database management system (DBMS). It is used to interact with the
database with some operations. It is also used to perform specific tasks,
functions, and queries of data. SQL can perform various tasks like creating
a table, adding data to tables, dropping the table, modifying the table, set
permission for users.
SQL Commands are mainly categorized into five categories:
 DDL – Data Definition Language
 DQL – Data Query Language
 DML – Data Manipulation Language
 DCL – Data Control Language
 TCL - Transaction Control Language
1. DDL - Data Definition Language
DDL or Data Definition Language actually consists of the SQL commands
that can be used for defining, altering and deleting database structures such
as tables, indexes and schemas. It simply deals with descriptions of the
database schema and is used to create and modify the structure of database
objects in the database
Common DDL Commands

Command Description Syntax

Create database or its

CREATE TABLE
objects (table, index,
table_name (column1
CREATE function, views, store
data_type, column2
procedure, and
data_type, ...);
triggers)

Delete objects from the DROP TABLE

DROP
database table_name;

ALTER TABLE
Alter the structure of table_name ADD
ALTER
the database COLUMN column_name
data_type;

Remove all records

from a table, including
TRUNCATE TABLE
TRUNCATE all spaces allocated for
table_name;
the records are
removed

COMMENT
Add comments to the
COMMENT 'comment_text' ON
data dictionary
TABLE table_name;
Command Description Syntax

RENAME TABLE
Rename an object
RENAME old_table_name TO
existing in the database
new_table_name;

Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for
employee ID, first name, last name and hire date.
2. DQL - Data Query Language
DQL statements are used for performing queries on the data within schema
objects. The purpose of the DQL Command is to get some schema relation
based on the query passed to it. This command allows getting the data out
of the database to perform operations with it. When a SELECT is fired
against a table or tables the result is compiled into a further temporary table,
which is displayed or perhaps received by the program.
DQL Command

Command Description Syntax

SELECT column1, column2,

It is used to retrieve
SELECT ...FROM table_name WHERE
data from the database
condition;
Command Description Syntax

Indicates
SELECT column1
FROM the table(s) from
FROM table_name;
which to retrieve data.

Filters
SELECT column1
rows before any
WHERE FROM table_name
grouping or
WHERE condition;
aggregation

SELECT column1,
Groups rows that have
GROUP AGG_FUNCTION(column2)
the same values in
BY FROM table_name
specified columns.
GROUP BY column1;

SELECT column1,
AGG_FUNCTION(column2)
Filters the results
HAVING FROM table_name
of GROUP BY
GROUP BY column1
HAVING condition;

Removes duplicate SELECT DISTINCT column1,

DISTINCT rows from the result column2, ...
set FROM table_name;

SELECT column1
ORDER Sorts the result set by FROM table_name
BY one or more columns ORDER BY column1 [ASC |
DESC];
Command Description Syntax

By default, it sorts
in ascending SELECT * FROM table_name
LIMIT
order unless specified LIMIT number;
as DESC

Example:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees' first and last names, along with their hire
dates, from the employees table, specifically for those in the 'Sales'
department, sorted by hire date.
3. DML - Data Manipulation Language
The SQL commands that deal with the manipulation of data present in the
database belong to DML or Data Manipulation Language and this includes
most of the SQL statements. It is the component of the SQL statement that
controls access to data and to the database. Basically, DCL statements are
grouped with DML statements.
Common DML Commands

Command Description Syntax

INSERT INTO table_name (column1,

Insert data into a
INSERT column2, ...) VALUES (value1, value2,
table
...);

Update existing UPDATE table_name SET column1 =

UPDATE data within a value1, column2 = value2 WHERE
table condition;
Command Description Syntax

Delete records
DELETE FROM table_name WHERE
DELETE from a database
condition;
table

Table control LOCK TABLE table_name IN

LOCK
concurrency lock_mode;

Call a PL/SQL
CALL or JAVA CALL procedure_name(arguments);
subprogram

Describe the
EXPLAIN EXPLAIN PLAN FOR SELECT *
access path to
PLAN FROM table_name;
data

Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into the employees table with the first name
'Jane', last name 'Smith' and department 'HR'.
4. DCL - Data Control Language
DCL (Data Control Language) includes commands such as GRANT and
REVOKE which mainly deal with the rights, permissions and other controls
of the database system. These commands are used to control access to data
in the database by granting or revoking permissions.
Common DCL Commands
Command Description Syntax

Assigns new privileges GRANT privilege_type

to a user account, [(column_list)] ON
allowing access to [object_type]
GRANT
specific database object_name TO user
objects, actions, or [WITH GRANT
functions. OPTION];

REVOKE [GRANT
Removes previously
OPTION FOR]
granted privileges from
privilege_type
a user account, taking
REVOKE [(column_list)] ON
away their access to
[object_type]
certain database
object_name FROM user
objects or actions.
[CASCADE];

Example of DCL
GRANT SELECT, UPDATE ON employees TO user_name;
This command grants the user user_name the permissions to select and
update records in the employees table.
5. TCL - Transaction Control Language
Transactions group a set of tasks into a single execution unit. Each
transaction begins with a specific task and ends when all the tasks in the
group are successfully completed. If any of the tasks fail, the transaction
fails. Therefore, a transaction has only two results: success or failure.
Common TCL Commands

Command Description Syntax

BEGIN Starts a new BEGIN TRANSACTION

TRANSACTION transaction [transaction_name];
Command Description Syntax

Saves all changes

COMMIT made during the COMMIT;
transaction

Undoes all changes

ROLLBACK made during the ROLLBACK;
transaction

Creates a savepoint
SAVEPOINT
SAVEPOINT within the current
savepoint_name;
transaction

Example:
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department =
'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made, and a savepoint
is set. If needed, the transaction can be rolled back to the savepoint before
being committed.
SQL Aggregate functions
Last Updated : 02 Aug, 2025


SQL Aggregate Functions are used to perform calculations on a set of rows

and return a single value. These functions are particularly useful when we
need to summarize, analyze, or group large datasets in SQL databases.
They are often used with the GROUP BY clause in SQL to summarize data
for each group. Commonly used aggregate functions include COUNT(),
SUM(), AVG(), MIN() and MAX().
Key Features of SQL Aggregate Functions:
 Operate on groups of rows: They work on a set of rows and return a
single value.
 Ignore NULLs: Most aggregate functions ignore NULL values,
except for COUNT(*).
 Used with GROUP BY: To perform calculations on grouped data,
you often use aggregate functions with GROUP BY.
 Can be combined with other SQL clauses: Aggregate functions can
be used alongside HAVING, ORDER BY and other SQL clauses to
filter or sort results.
Commonly Used SQL Aggregate Functions
Below are the most frequently used aggregate functions in SQL.
1. Count()
The COUNT() function returns the number of rows that match a given
condition or are present in a column.
 COUNT(*): Counts all rows.
 COUNT(column_name): Counts non-NULL values in the specified
column.
  COUNT(DISTINCT column_name): Counts unique non-NULL
values in the column.
Examples:
-- Total number of records in the table
SELECT COUNT(*) AS TotalRecords FROM Employee;

-- Count of non-NULL salaries

SELECT COUNT(Salary) AS NonNullSalaries FROM Employee;

-- Count of unique non-NULL salaries

SELECT COUNT(DISTINCT Salary) AS UniqueSalaries FROM
Employee;
2. SUM()
The SUM() function calculates the total sum of a numeric column.
SUM(column_name): Returns the total sum of all non-NULL values in a
column.
Examples:
-- Calculate the total salary
SELECT SUM(Salary) AS TotalSalary FROM Employee;

-- Calculate the sum of unique salaries

SELECT SUM(DISTINCT Salary) AS DistinctSalarySum FROM
Employee;
3. AVG()
The AVG() function calculates the average of a numeric column. It divides
the sum of the column by the number of non-NULL rows.
AVG(column_name): Returns the average of the non-NULL values in the
column.
Examples:
-- Calculate the average salary
SELECT AVG(Salary) AS AverageSalary FROM Employee;
-- Average of distinct salaries
SELECT AVG(DISTINCT Salary) AS DistinctAvgSalary FROM Employee;
4. MIN() and MAX()
The MIN() and MAX() functions return the smallest and largest values,
respectively, from a column.
 MIN(column_name): Returns the minimum value.
 MAX(column_name): Returns the maximum value.
Examples:
-- Find the highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employee;

-- Find the lowest salary

SELECT MIN(Salary) AS LowestSalary FROM Employee;
Examples of SQL Aggregate Functions
Let's consider a demo Employee table to demonstrate SQL aggregate
functions . This table contains employee details such as their ID, Name,
and Salary.

Id Name Salary

1 A 802

2 B 403

3 C 604

4 D 705

5 E 606
Id Name Salary

6 F NULL

1. Count the Total Number of Employees

SELECT COUNT(*) AS TotalEmployees FROM Employee;
Output:

TotalEmployees

2. Calculate the Total Salary

SELECT SUM(Salary) AS TotalSalary FROM Employee;
Output:

TotalSalary

3120

3. Find the Average Salary:

SELECT AVG(Salary) AS AverageSalary FROM Employee;

Output:

AverageSalary

624

4. Find the Highest and Lowest Salary

SELECT MAX(Salary) AS HighestSalary FROM Employee;
Output:
HighestSalary

802

Using Aggregate Functions with GROUP BY

SQL GROUP BY allows us to group rows that have the same values in
specific columns. We can then apply aggregate functions to these groups,
which helps us summarize data for each group. This is commonly used
with the COUNT(), SUM(), AVG(), MIN(), and MAX() functions.
Example: Total Salary by Each Employee
SELECT Name, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Name;
Output:

Name TotalSalary

A 802

B 403

C 604

D 705

E 606

F -

Using HAVING with Aggregate Functions

The HAVING clause is used to filter results after applying aggregate
functions, unlike WHERE, which filters rows before aggregation.
HAVING is essential when we want to filter based on the result of an
aggregate function.
Example: Find Employees with Salary Greater Than 600
SELECT Name, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Name
HAVING SUM(Salary) > 600;
Output:

Name TotalSalary

A 802

C 604

D 705

E 606