Introduction to Databases

A database is an organized collection of data that allows users to store,

retrieve, manage, and manipulate information efficiently. Databases are
fundamental to modern computing and play a crucial role in various fields,
including business, education, healthcare, finance, and government. They
enable organizations to store large volumes of structured or unstructured data
in a way that ensures accessibility, security, and efficiency.
In the digital era, the vast amount of data generated every second makes
databases indispensable. Whether it's a social media platform storing user
interactions, a bank managing transactions, or an e-commerce website tracking
orders, databases provide the backbone for these operations. By using a
Database Management System (DBMS), businesses and individuals can
efficiently handle and process data to make informed decisions, enhance
productivity, and maintain seamless operations.
Databases come in different forms, ranging from traditional relational
databases that store data in structured tables to modern NoSQL databases that
allow flexible and scalable data storage. The choice of database depends on
factors such as data complexity, scalability needs, and performance
requirements.
Basic Concepts of a Database
1. Data: Raw facts and figures.
2. Database Management System (DBMS): Software that manages
databases (e.g., MySQL, PostgreSQL, MongoDB).
3. Schema: The structure that defines the database (tables, columns,
relationships).
4. Tables: A collection of related data organized in rows and columns.
5. Keys: Unique identifiers for records (Primary Key, Foreign Key, etc.).
Key Features of Database Management Systems (DBMS)
1. Data Security: Provides authentication and access control to prevent
unauthorized access.
 2. Data Integrity: Ensures accuracy and consistency through constraints
and rules.
3. Concurrency Control: Supports multiple users accessing data
simultaneously without conflicts.
4. Backup and Recovery: Maintains data availability by providing
automated backups and recovery mechanisms.
5. Scalability: Allows databases to grow as the organization's needs
increase.
6. Data Independence: Separates data from application programs, making
modifications easier.
7. Query Processing: Supports powerful querying capabilities using SQL or
NoSQL languages.
8. Transaction Management: Ensures ACID (Atomicity, Consistency,
Isolation, Durability) compliance for reliable transactions.
9. Data Redundancy Control: Minimizes duplication of data to optimize
storage and efficiency.
10.Interoperability: Supports integration with other software and
databases.
Advantages of Using a Database
1. Data Organization: Stores data in a structured way, making retrieval
easy.
2. Data Integrity: Ensures accuracy and consistency.
3. Data Security: Controls access to sensitive information.
4. Concurrency Control: Allows multiple users to access data
simultaneously.
5. Backup and Recovery: Provides data recovery in case of failure.
6. Redundancy Reduction: Avoids duplicate data storage.
Types of Databases
1. Relational Database (RDBMS): Stores data in tables with rows and
columns, using SQL for querying.
 2. NoSQL Database: Non-tabular databases, designed for scalability and
flexibility.
3. Hierarchical Database: Data is organized in a tree-like structure.
4. Network Database: Uses a graph structure to represent relationships.
5. Object-Oriented Database (OODBMS): Stores data in objects, used in
OOP languages.
6. Cloud Database: Stored on cloud servers for easy access and
management.
7. Time-Series Database: Optimized for handling time-stamped data.
Database Languages
Database languages are used to create, manage, and manipulate databases.
The main types of database languages include:
1. Data Definition Language (DDL)
o Used to define and modify database structure.
o Commands: CREATE, ALTER, DROP, TRUNCATE.
2. Data Manipulation Language (DML)
o Used to manipulate and retrieve data.
o Commands: INSERT, UPDATE, DELETE, SELECT.
3. Data Control Language (DCL)
o Used to control access to data.
o Commands: GRANT, REVOKE.
4. Transaction Control Language (TCL)
o Manages database transactions to ensure consistency.
o Commands: COMMIT, ROLLBACK, SAVEPOINT.
Conclusion
Databases are essential for managing large amounts of data efficiently.
Understanding database types and languages helps in designing and managing
structured data effectively.
SQL Queries
Creating a table
syntax: given below in picture

 Added attributes on the table st11

Inserting data into table

 Above command select * from st11 will display the table containing the
data

 See the Results Displays all the data of table st11

The SELECT statement in SQL is used to retrieve data from a database. The
WHERE clause is used to filter records based on specified conditions.
 Creating a New table student with more attributes
The COUNT function in SQL is used to count the number of records in a group.
When used with the GROUP BY clause, it groups the rows based on a specific
column and applies aggregate functions like COUNT() to each group.

Explanation:
 COUNT(*): Counts the number of rows in each group.
 GROUP BY column_name(ex:section): Groups the records based on the
specified column.
The ORDER BY clause in SQL is used to sort the result set based on one or more
columns in ascending or descending order.

The SUM() function calculates the total sum of a numeric column. The GROUP
BY clause groups the records, and the HAVING clause filters the grouped results
based on conditions.
Explanation:
 SUM(fees): Calculates the total fees for each class.
 GROUP BY class: Groups the records by class.
 HAVING SUM(salary) <= 15000: Filters the results to show only class
where the total fees exceeds 15,000.
The HAVING clause is used instead of WHERE because aggregate functions
(SUM, AVG, etc.) cannot be used directly in WHERE.
Creating a new table named Sales where we do the group functions
  Explanation of Group Functions:
COUNT(): This function counts the number of rows in a column, typically to find
the number of occurrences of a certain value.

COUNT(OrderID) counts how many orders were placed for each product.
AVG(): The AVG() function calculates the average of a numeric column.

AVG(Quantity) computes the average quantity ordered per product.

SUM(): The SUM() function adds up the values in a numeric column.

SUM(Quantity) gives the total quantity of a product sold.

MIN(): The MIN() function finds the minimum value in a numeric column.

MIN(Quantity) finds the least quantity ordered in a single order for each
product.
MAX(): The MAX() function identifies the maximum value in a numeric column.
MAX(Quantity) gives the highest quantity ordered for each product.

 Common Single-Row Functions

UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.
CONCAT(): Concatenates two or more strings.
SUBSTR(): Extracts a substring from a string.
ROUND(): Rounds a numeric value to a specified number of decimal places.
LENGTH(): Returns the length of a string.

Explanation of the Functions:

UPPER(ProductName): This function converts the product name to uppercase.

For example, Apple becomes APPLE.

LOWER(ProductName): This function converts the product name to lowercase.
For example, Apple becomes apple.
CONCAT(ProductName, ' - ', Price): This function concatenates (joins) the
product name with the price, separated by a hyphen.

For example, Apple with a price of 5.00 becomes Apple - 5.00.

ROUND(Price, 2): This function rounds the price to 2 decimal places.

For example, a price of 5.00 remains 5.00, but if there were a price like 5.678,
it would be rounded to 5.68.

Joining Data from Multiple Tables Using SQL

To join data from multiple tables, you can use JOIN operations in SQL. There
are different types of joins, but we'll focus on the basic ones: INNER JOIN, LEFT
JOIN, RIGHT JOIN, and FULL JOIN.

Step 1: Create Sample Tables

Let's create two tables: Customers and Orders.

Step 3: Using JOINs to Combine Data

We will now perform different types of joins to display data from both the
Customers and Orders tables.

1. INNER JOIN
The INNER JOIN returns only the rows that have matching values in both tables.

Explanation:
The INNER JOIN will return only the customers who have placed orders, i.e.,
where the CustomerID is found in both the Customers and Orders tables.

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN returns all rows from the left table (Customers), and the
matched rows from the right table (Orders). If there is no match, the result is
NULL from the right table.
Explanation:
The LEFT JOIN will return all customers, whether they have placed an order or
not. If a customer has no order, the OrderID, OrderDate, and
TotalAmount will be NULL.
Lisa White does not have an order, so OrderID, OrderDate, and
TotalAmount are NULL

3. RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN is similar to the LEFT JOIN, but it returns all rows from the right
table (Orders), and the matching rows from the left table (Customers). If there
is no match, the result is NULL from the left table
Explanation:
The RIGHT JOIN will return all orders, even if they do not have a corresponding
customer (though in this case, every order has a corresponding customer).
Explanation:
The RIGHT JOIN will return all orders, even if they do not have a corresponding
customer (though in this case, every order has a corresponding customer).

Explanation:
The RIGHT JOIN will return all orders, even if they do not have a corresponding
customer (though in this case, every order has a corresponding customer).

4. FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN returns all rows from both tables. If there is no match, the result
will be NULL from the missing table.
Explanation:
The FULL JOIN will return all customers and all orders, even if there is no
matching row between them.
Here, Lisa White is shown because she has no matching order, and Orders with
no matching customer would also be shown (though there are none
in this example).

Conclusion
INNER JOIN: Returns only matching rows from both tables.
LEFT JOIN: Returns all rows from the left table and matching rows from the
right table. Non-matching rows from the right table will have NULL values.
RIGHT JOIN: Returns all rows from the right table and matching rows from the
left table. Non-matching rows from the left table will have NULL values.
FULL JOIN: Returns all rows from both tables. Non-matching rows will have
NULL values in the missing table's columns.
These joins are essential for combining related data from multiple tables based
on common keys or conditions.
 Subqueries in SQL
A subquery (also called an inner query or nested query) is a query that is
embedded inside another query. It is used to perform operations that require a
result from one query to be used in another query.

Subqueries can be used in several contexts, such as:

In the SELECT clause – to return values for each row.

In the WHERE clause – to filter data based on a result from another query.
In the FROM clause – to treat the result of a subquery as a temporary table.
In the HAVING clause – to filter data based on aggregate functions.
Types of Subqueries
Scalar Subqueries: A subquery that returns a single value.
Column Subqueries: A subquery that returns a list of values (single column).
Row Subqueries: A subquery that returns a single row of values (multiple
columns).
Table Subqueries: A subquery that returns a table (multiple rows and columns).
 Let's go through different examples of using subqueries in SQL:

Example Tables
We’ll use the same Customers and Orders tables from the previous examples
for these queries.
Customers Table:
CustomerID CustomerName Country
1 John Doe USA
2 Jane Smith UK
3 Sam Brown Canada
4 Lisa White Australia
Orders Table:
OrderID CustomerID OrderDate TotalAmount
101 1 2025-01-10 250.00
102 2 2025-01-15 300.00
103 1 2025-02-05 450.00
104 3 2025-02-20 100.00
105 2 2025-03-05 200.00
106 4 2025-03-10 150.00
1. Subquery in the SELECT Clause
A scalar subquery can be used in the SELECT clause to return a single value that
is calculated for each row of the main query.

Query:
Let's say we want to display the CustomerName along with the total number of
orders each customer has placed.
SELECT
CustomerName,
(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID =
Customers.CustomerID) AS TotalOrders
FROM Customers;

Explanation:
The subquery (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID =
Customers.CustomerID) counts the total orders for each customer.
The subquery uses the CustomerID from the outer Customers table to find the
count for each customer.
Result:
CustomerName TotalOrders
John Doe 2
Jane Smith 2
Sam Brown 1
Lisa White 1
2. Subquery in the WHERE Clause
A subquery in the WHERE clause is used to filter the results based on a
condition that involves another query.

Query:
Let’s say we want to find all customers who have placed more than 1 order.

SELECT CustomerName, Country

FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders GROUP BY
CustomerID HAVING COUNT(OrderID) > 1);
Explanation:
The subquery (SELECT CustomerID FROM Orders GROUP BY CustomerID
HAVING COUNT(OrderID) > 1) finds customers who have placed more than one
order.
The outer query then uses this result to filter customers who are in this list of
CustomerID.
Result:
CustomerName Country
John Doe USA
Jane Smith UK
3. Subquery in the FROM Clause
A subquery in the FROM clause allows you to treat the result of the subquery
as a derived table (temporary table). This is useful when you need to perform
calculations or aggregations before applying further filtering.

Query:
Let’s say we want to display the total order amount per customer along with
the customer name.
SELECT
Customers.CustomerName,
SubQuery.TotalAmount
FROM Customers
JOIN (SELECT CustomerID, SUM(TotalAmount) AS TotalAmount FROM Orders
GROUP BY CustomerID) AS SubQuery
ON Customers.CustomerID = SubQuery.CustomerID;
Explanation:
The subquery (SELECT CustomerID, SUM(TotalAmount) AS TotalAmount FROM
Orders GROUP BY CustomerID) calculates the total order amount per customer.
The outer query joins this subquery with the Customers table to display the
CustomerName and their total order amount.
Result:
CustomerName TotalAmount
John Doe 700.00
Jane Smith 500.00
Sam Brown 100.00
Lisa White 150.00
4. Subquery in the HAVING Clause
A subquery in the HAVING clause is used to filter results after the aggregation is
done. This is useful when you want to filter based on aggregate values.

Query:
Let’s say we want to find customers who have placed orders with a total order
amount greater than 400.
SELECT
CustomerID,
SUM(TotalAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(TotalAmount) > (SELECT AVG(TotalAmount) FROM Orders);
Explanation:
The subquery (SELECT AVG(TotalAmount) FROM Orders) calculates the average
order amount across all customers.
The outer query groups orders by CustomerID and filters the customers whose
total order amount is greater than the calculated average.
Result:
CustomerID TotalOrderAmount
1 700.00
2 500.00
5. Correlated Subqueries
A correlated subquery is a subquery that references a column from the outer
query. This type of subquery depends on the outer query for its value.

Query:
Let's say we want to find customers who have placed orders greater than the
average amount of their own orders.
SELECT CustomerName, TotalAmount
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.CustomerID = c.CustomerID
AND o.TotalAmount > (SELECT AVG(TotalAmount) FROM Orders WHERE
CustomerID = c.CustomerID)
);
Explanation:
The subquery SELECT AVG(TotalAmount) FROM Orders WHERE CustomerID =
c.CustomerID calculates the average order amount for each customer.
The correlated subquery checks if the order amount for a particular customer is
greater than their own average order amount.
Result:
CustomerName TotalAmount
John Doe 450.00
Jane Smith 300.00
Summary of Subqueries
Subquery in SELECT clause: Used to calculate or fetch values for each row.
Subquery in WHERE clause: Used to filter results based on a condition from
another query.
Subquery in FROM clause: Used to treat the result of a subquery as a
temporary table (derived table).
Subquery in HAVING clause: Used to filter aggregated data based on a result
from another query.
Correlated Subqueries: A subquery that depends on values from the outer
query.
Subqueries are a powerful tool for querying and manipulating data in SQL. They
allow you to perform complex operations that would otherwise require
multiple queries or additional steps.

Data Manipulation in DBMS (Database Management System):

Data manipulation in DBMS refers to the process of interacting with the data
stored in a database to retrieve, modify, or remove it. These operations are
generally performed using a language like SQL (Structured Query Language).
The main operations in data manipulation are retrieving data (SELECT),
inserting data (INSERT), updating data (UPDATE), and deleting data (DELETE).

Key Operations in Data Manipulation:

SELECT (Retrieving Data):

The SELECT statement is used to retrieve data from one or more tables in a
database.
Example:
SELECT * FROM Employees;
This query retrieves all columns and rows from the "Employees" table.
INSERT (Adding Data):

The INSERT statement is used to add new rows to a table.

Example:
INSERT INTO Employees (EmployeeID, Name, Age, Department)
VALUES (101, 'John Doe', 28, 'HR');
This query inserts a new employee into the "Employees" table.
UPDATE (Modifying Data):

The UPDATE statement is used to modify existing records in a table.

Example:
UPDATE Employees
SET Age = 29
WHERE EmployeeID = 101;
This query updates the age of the employee with ID 101.

DELETE (Removing Data):

The DELETE statement is used to remove rows from a table.
Example:
DELETE FROM Employees
WHERE EmployeeID = 101;
This query deletes the record of the employee with ID 101.
Additional Concepts:
Joins: To manipulate and retrieve data from multiple tables, we often use JOIN
operations like INNER JOIN, LEFT JOIN, etc.

WHERE Clause: It helps to filter data based on a condition.

Example:
SELECT * FROM Employees
WHERE Age > 30;
ORDER BY: To sort the results of a query.
Example:
SELECT * FROM Employees
ORDER BY Age DESC;
GROUP BY: To group data and perform aggregate functions like SUM, AVG,
COUNT.

Example:
SELECT Department, COUNT(*) FROM Employees
GROUP BY Department;
 Example Scenario:
Let's assume you have a database with the following table structure:

Table: Employees

EmployeeID Name Age Department

101 John Doe 28 HR
102 Jane Smith 34 Finance
103 Sam Green 40 IT
SELECT Example: To view all employees working in the "IT" department:

SELECT * FROM Employees

WHERE Department = 'IT';
Output:

EmployeeID Name Age Department

103 Sam Green 40 IT
INSERT Example: Adding a new employee to the "HR" department:

INSERT INTO Employees (EmployeeID, Name, Age, Department)

VALUES (104, 'Alex Brown', 25, 'HR');
UPDATE Example: If you want to increase the age of all employees in the
"Finance" department by 1 year:

UPDATE Employees
SET Age = Age + 1
WHERE Department = 'Finance';
DELETE Example: If you want to delete the record of the employee with ID 101:

DELETE FROM Employees

WHERE EmployeeID = 101;
Conclusion:
Data manipulation in DBMS is crucial for performing operations like adding,
updating, deleting, and retrieving data from a database. SQL provides a rich set
of commands and features to interact with data efficiently, allowing users to
manage and retrieve data in meaningful ways

Data Definition Language (DDL) commands are used to define and manage the
structure of a database, including creating, altering, or deleting tables and
other database objects. DDL commands do not deal with data manipulation
directly (like retrieving or modifying data), but rather focus on defining the
schema or structure.

Common DDL Commands:

CREATE: Used to create a new table, view, index, or other objects in the
database.
ALTER: Used to modify an existing database object, such as adding, deleting, or
modifying columns in a table.
DROP: Used to delete an existing database object like a table, view, or index.
TRUNCATE: Used to remove all records from a table, but unlike DELETE, it does
not log individual row deletions and is much faster.
RENAME: Used to rename a database object (like a table).
COMMENT: Used to add comments or descriptions to a table or column.

Let’s explore each DDL command with examples:

1. CREATE Table:
The CREATE command is used to create a new table in the database with
specific columns and their data types.

Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);
This command creates a new table called "Employees" with four columns:
EmployeeID, Name, Age, and Department.

2. ALTER Table:
The ALTER command is used to modify an existing table's structure, such as
adding or dropping columns, changing column types, or renaming the table.
Example 1: Add a column
ALTER TABLE Employees
ADD Salary DECIMAL(10, 2);
This command adds a new column Salary to the "Employees" table.

Example 2: Modify a column's data type

ALTER TABLE Employees
MODIFY Age INT(3);
This modifies the Age column in the "Employees" table, changing its size or
type.

Example 3: Drop a column

ALTER TABLE Employees
DROP COLUMN Department;
This command removes the Department column from the "Employees" table.

3. DROP Table:
The DROP command is used to remove a table from the database entirely,
including all of its data, structure, and indexes. Be careful when using this
command because it is irreversible.

Example:
DROP TABLE Employees;
This command deletes the "Employees" table from the database.

4. TRUNCATE Table:
The TRUNCATE command is used to remove all records from a table but does
not affect the table structure. It is faster than DELETE because it does not log
each row deletion individually and does not fire triggers. However, TRUNCATE
is also irreversible.

Example:
TRUNCATE TABLE Employees;
This command deletes all records from the "Employees" table, but the table
structure (columns, data types) remains unchanged.

 Key Difference between DELETE and TRUNCATE:

DELETE: Removes data row by row and logs each row for recovery. It is slower
and can be rolled back (if inside a transaction).
TRUNCATE: Removes all rows at once, does not log individual row deletions, is
faster, but cannot be rolled back unless inside a transaction.
5. RENAME Table:
The RENAME command is used to change the name of an existing table.

Example:
RENAME TABLE Employees TO Staff;
This renames the "Employees" table to "Staff".

6. COMMENT:
The COMMENT command is used to add a description or a comment to a table
or a column.

Example:
COMMENT ON COLUMN Employees.Age IS 'Age of the employee in years';
This adds a comment to the Age column of the "Employees" table, which can
help users or developers understand the purpose of that column.

Example Scenario:
Let’s assume you are working with the following table Employees:

EmployeeID Name Age Department

101 John Doe 28 HR
102 Jane Smith 34 Finance
Step 1: Create the Employees table:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);
Step 2: You realize you need to add a Salary column:

ALTER TABLE Employees

ADD Salary DECIMAL(10, 2);
Step 3: After some time, you decide to rename the Department column to
Dept:

ALTER TABLE Employees

RENAME COLUMN Department TO Dept;
Step 4: Now you want to delete all records (but keep the table structure for
future use):
TRUNCATE TABLE Employees;
Step 5: Finally, when the company no longer needs this table, you can drop it:

DROP TABLE Employees;

Conclusion:
DDL commands in DBMS are essential for defining and modifying the structure
of database objects like tables. Commands like CREATE, ALTER, DROP, and
TRUNCATE allow you to manage database schemas and efficiently handle
database objects. These commands are mostly used by database
administrators and developers for schema changes or maintenance tasks.

Created by : Nikhil Kumar Singh