Basic Data Types in DBMS

In a DBMS, data types define the kind of values that can be stored in a column of a table.
Choosing the correct data type ensures data integrity, saves storage space, and improves
performance. Basic data types are generally divided into the following categories:

1. Numeric Data Types

These are used to store numbers.

 INT or INTEGER stores whole numbers without any decimal part.

Example:

CREATE TABLE Student (RollNo INT);

INSERT INTO Student VALUES (101);

 DECIMAL(p,s) or NUMERIC(p,s) stores numbers with fixed precision (p) and

scale (s).
Example: DECIMAL(6,2) can store 1234.56 but not 12345.678.

CREATE TABLE Product (Price DECIMAL(6,2));

INSERT INTO Product VALUES (249.75);

 FLOAT or REAL stores numbers with decimal points but allows approximate
values, useful for scientific calculations.
Example: 3.14159

2. Character (String) Data Types

These are used to store text, letters, or symbols.

 CHAR(n) stores fixed-length strings. If the value is shorter than n, spaces are added
to fill the length.
Example:

CREATE TABLE City (Code CHAR(3));

INSERT INTO City VALUES ('PUN'); -- Fixed length

 VARCHAR(n) stores variable-length strings, saving space if the text is shorter than
n.
Example:

CREATE TABLE Customer (Name VARCHAR(50));

INSERT INTO Customer VALUES ('Rahul Sharma');

 TEXT stores large blocks of text such as descriptions or comments.

Example: "This is a long product description..."
3. Date and Time Data Types

These are used to store date, time, or both.

 DATE stores only the date in YYYY-MM-DD format.

Example:

CREATE TABLE Orders (OrderDate DATE);

INSERT INTO Orders VALUES ('2025-08-12');

 TIME stores only the time in HH:MM:SS format.

Example: 14:30:00
 DATETIME or TIMESTAMP stores both date and time.
Example:

CREATE TABLE Log (CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

4. Boolean Data Type

Used to store logical values: TRUE or FALSE. Some DBMSs store them as 1 (true) and 0
(false).
Example:

CREATE TABLE Users (IsActive BOOLEAN);

INSERT INTO Users VALUES (TRUE);

5. Binary Data Type

Used to store data in binary form, such as images, audio, or other files.

 BLOB (Binary Large Object) stores large binary data.

Example: Storing a .jpg image file in a database column.

-------------------------------------------------------------------------------------------------------------------------------------

Data Definition Language (DDL)

Definition:
Data Definition Language is a set of SQL commands used to define, create, modify, and
delete the structure of database objects such as tables, views, indexes, and schemas.
DDL commands deal with the structure of the database, not the actual data inside it.

Key Point:
When a DDL command is executed, changes are permanent and automatically committed
to the database.
Main DDL Commands

1. CREATE
Used to create new database objects such as tables, databases, or views.
Example:

sql
CopyEdit
CREATE TABLE Employee (
EmpID INT,
Name VARCHAR(50),
Salary DECIMAL(10,2)
);

This creates a table named Employee with three columns.

2. ALTER
Used to modify the structure of an existing table, such as adding, deleting, or changing
columns.
Example – Add a column:

sql
CopyEdit
ALTER TABLE Employee ADD Department VARCHAR(30);

Example – Modify a column:

sql
CopyEdit
ALTER TABLE Employee MODIFY Salary DECIMAL(12,2);

3. DROP
Used to permanently delete a database object. Once dropped, all data inside it is lost.
Example:

sql
CopyEdit
DROP TABLE Employee;

This deletes the Employee table completely.

4. TRUNCATE
Removes all rows from a table but keeps its structure for future use. It is faster than DELETE
because it does not log individual row deletions.
Example:

sql
CopyEdit
TRUNCATE TABLE Employee;

This clears the table but keeps its columns intact.

5. RENAME
Used to rename an existing database object.
Example:

sql
CopyEdit
RENAME TABLE Employee TO Staff;

This changes the table name from Employee to Staff.

--------------------------------------------------------------------------------------------------------------------------------------

Data Manipulation Language (DML)

Definition:
Data Manipulation Language is a set of SQL commands used to insert, modify, delete, and
retrieve the data stored in database tables.
Unlike DDL, which changes the structure, DML commands work with the data inside the
structure.

Key Point:

 DML changes are not permanent until you use COMMIT.

 You can undo changes using ROLLBACK (if supported by the DBMS).

Main DML Commands

1. INSERT

Used to add new rows of data into a table.

Example – Insert all column values:

INSERT INTO Employee (EmpID, Name, Salary)

VALUES (101, 'Amit Kumar', 45000.50);

Example – Insert only specific columns:

INSERT INTO Employee (EmpID, Name)

VALUES (102, 'Rahul Sharma');

2. UPDATE

Used to modify existing records in a table.

Example – Update salary of one employee:

UPDATE Employee
SET Salary = 50000
WHERE EmpID = 101;

Example – Update salary for all employees:

UPDATE Employee
SET Salary = Salary + 2000;

3. DELETE

Used to remove existing rows from a table.

Example – Delete one employee:

DELETE FROM Employee

WHERE EmpID = 101;

Example – Delete all rows:

DELETE FROM Employee;

4. SELECT

Used to retrieve data from one or more tables.

Example – Retrieve all columns:

SELECT * FROM Employee;

Example – Retrieve specific columns:

SELECT Name, Salary FROM Employee

WHERE Salary > 40000;
--------------------------------------------------------------------------------------------------------------------------------------

SQL Clauses

Clauses in SQL are special keywords used to define specific conditions or instructions within
an SQL statement.

They allow you to control how data is retrieved, grouped, or sorted from database tables.

Clauses are not independent statements; they work in combination with commands like
SELECT, UPDATE, or DELETE.
The most commonly used clauses are WHERE, GROUP BY, ORDER BY, and HAVING.
Each plays a different role in filtering, grouping, or ordering the data.

1. WHERE Clause

The WHERE clause is used to filter records based on a given condition.

Only those rows that satisfy the condition are included in the result set or affected by the
query.

Key Points:

 Used with SELECT, UPDATE, and DELETE statements.

 Conditions can use comparison operators (=, <, >, <=, >=, <>) and logical operators
(AND, OR, NOT).
 Can be combined with functions (e.g., UPPER(), LOWER(), LENGTH()) to make
filtering more specific.
 Filters data before any grouping or aggregation is done.

Example 1

SELECT Name, Salary

FROM Employee
WHERE Salary > 40000;

Explanation:
This query will display only those employees whose salary is greater than 40,000.

2. GROUP BY Clause

The GROUP BY clause groups rows that have the same value in specified columns into
summary rows.

It is most often used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), MIN() to
perform calculations on each group of data.

Key Points:

 Groups rows based on one or more columns.

 Comes after the WHERE clause (if WHERE is used) and before the HAVING
clause.
 Without an aggregate function, grouping has no effect.
 Helps in summarizing large sets of data.

Example
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employee
GROUP BY Department;

Explanation:
This query counts the number of employees in each department and displays them as separate
groups.

3. ORDER BY Clause

The ORDER BY clause is used to sort the result set based on one or more columns, either in
ascending (ASC) or descending (DESC) order.

Key Points:

 Default sorting is ascending if ASC or DESC is not specified.

 You can sort by columns not included in the SELECT list (though not recommended
for clarity).
 Multiple columns can be used for sorting; sorting is applied in the order columns are
listed.
 Works after data retrieval, so it’s usually the last clause in a query.

Example 1

SELECT Name, Salary

FROM Employee
ORDER BY Salary DESC;

Explanation:
Displays employees in order of highest salary first.

4. HAVING Clause

The HAVING clause is used to filter records after the GROUP BY operation has been performed.

It acts like a WHERE clause for groups rather than individual rows.

Key Points:

 Always used with GROUP BY (though some DBMS allow HAVING without GROUP
BY).
 Can use aggregate functions for filtering, unlike WHERE.
 Comes after GROUP BY and before ORDER BY in a query.

Example
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 3;

Explanation:
Groups employees by department and then displays only those departments that have more
than 3 employees.

----------------------------------------------------------------------------------------------------------------

Joins in SQL – Detailed Notes

A JOIN in SQL is used to combine data from two or more tables based on a related column
between them.

The related column is usually a primary key in one table and a foreign key in another, but it
can be any matching column.

Joins help in retrieving meaningful information spread across multiple tables.

Basic Syntax of a JOIN

sql
CopyEdit
SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

Types of Joins

1. INNER JOIN

Returns only the rows where there is a match in both tables based on the joining condition.

Key Points:

 If there is no match, that row is excluded from the result.

 Most commonly used type of join.

Example:
We have two tables:
Employees

EmpID Name DeptID

1 Amit 101
2 Rahul 102
3 Priya 103

Departments

DeptI DeptName
D
101 HR
102 IT
104 Finance

Query:

SELECT Employees.Name, Departments.DeptName

FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:
Only employees whose DeptID exists in both tables will be shown:

Amit | HR
Rahul | IT

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and the matching rows from the right table. If there is no
match in the right table, NULL values are returned.

Key Points:

 Preserves all rows from the left table.

 Missing matches on the right side are shown as NULL.

Example:

SELECT Employees.Name, Departments.DeptName

FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:

Amit | HR
Rahul | IT
Priya | NULL

Here, Priya has DeptID 103, which is not in Departments, so DeptName is NULL.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and the matching rows from the left table. If there is no
match in the left table, NULL values are returned.

Key Points:

 Preserves all rows from the right table.

 Missing matches on the left side are shown as NULL.

Example:

SELECT Employees.Name, Departments.DeptName

FROM Employees
RIGHT JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Result:

Amit | HR
Rahul | IT
NULL | Finance

Here, Finance department exists in Departments but has no employee in Employees, so

Name is NULL.

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows from both tables, with NULL values where there is no match in either table.

Key Points:

 Combines the results of LEFT JOIN and RIGHT JOIN.

 Not supported in some DBMS (like MySQL) directly; can be simulated using UNION.

Example :

SELECT Employees.Name, Departments.DeptName

FROM Employees
FULL JOIN Departments
ON Employees.DeptID = Departments.DeptID;
Result:

Amit | HR
Rahul | IT
Priya | NULL
NULL | Finance

----------------------------------------------------------------------------------------------------------------

Operators in SQL

In SQL, operators are special symbols or keywords used to perform operations on data.

They allow you to compare values, perform calculations, combine conditions, or work with
multiple result sets.

Operators in SQL can be classified into four main categories: Relational, Arithmetic,
Logical, and Set Operators.

1. Relational Operators

Relational operators are used to compare two values.

The result of such a comparison is either TRUE, FALSE, or UNKNOWN (when dealing
with NULL values).

Common Relational Operators:

 = : Equal to
 <> or != : Not equal to
 > : Greater than
 < : Less than
 >= : Greater than or equal to
 <= : Less than or equal to

Example

SELECT Name, Salary

FROM Employee
WHERE Salary > 50000;
2. Arithmetic Operators

Arithmetic operators are used to perform mathematical calculations on numeric data.

Common Arithmetic Operators:

 + : Addition
 - : Subtraction
 * : Multiplication
 / : Division
 % : Modulus (remainder after division; not supported in all DBMS)

Example

SELECT Name, Salary, Salary + 5000 AS NewSalary

FROM Employee;

Explanation:
Adds 5,000 to each employee’s salary and displays it as NewSalary.

SELECT Name, Salary * 0.10 AS Bonus

FROM Employee;

Explanation:
Calculates a 10% bonus for each employee.

3. Logical Operators

Logical operators are used to combine multiple conditions in SQL queries. They return
TRUE, FALSE, or UNKNOWN.

Common Logical Operators:

 AND – Returns TRUE if both conditions are true.

 OR – Returns TRUE if at least one condition is true.
 NOT – Reverses the result; returns TRUE if the condition is false.

Example

SELECT Name, Department, Salary

FROM Employee
WHERE Department = 'IT' AND Salary > 50000;

Explanation:
Displays IT department employees with salary above 50,000.
Example

SELECT Name
FROM Employee
WHERE Department = 'HR' OR Department = 'Finance';

Explanation:
Displays employees who work in either HR or Finance.

4. Set Operators

Set operators are used to combine the results of two or more SELECT statements. The number
and order of columns must match in all queries used with set operators.

Common Set Operators:

 UNION – Combines results from two queries, removing duplicates.

 UNION ALL – Combines results from two queries, including duplicates.
 INTERSECT – Returns only rows that appear in both result sets.
 EXCEPT / MINUS – Returns rows from the first query that are not in the second query.

Example – UNION:

SELECT Name FROM Employee

WHERE Department = 'HR'
UNION
SELECT Name FROM Employee
WHERE Department = 'IT';

Explanation:
Shows unique employee names from HR or IT departments.

Example – UNION ALL:

SELECT Name FROM Employee

WHERE Department = 'HR'
UNION ALL
SELECT Name FROM Employee
WHERE Department = 'IT';

Explanation:
Shows all employee names from HR or IT, including duplicates.

Example – INTERSECT:

SELECT Name FROM ProjectA

INTERSECT
SELECT Name FROM ProjectB;
Explanation:
Shows names of employees who worked on both ProjectA and ProjectB.

Example – EXCEPT (or MINUS in Oracle):

SELECT Name FROM ProjectA

EXCEPT
SELECT Name FROM ProjectB;

Explanation:
Shows names of employees who worked on ProjectA but not ProjectB.

----------------------------------------------------------------------------------------------------------------

Functions in SQL

Functions in SQL are predefined programs (built-in) that perform specific operations on
data and return a single value.

They help in calculations, data manipulation, and summarization without writing complex
code.

Functions can be applied on:

 Single values (Scalar Functions) → return one value for each input.
 Groups of values (Aggregate Functions) → return one value for a set of rows.

1. Numeric Functions

Used to perform mathematical operations on numeric data.

Common Numeric Functions:

 ABS(number)

Returns absolute (positive) value.

SELECT ABS(-15); -- Output: 15

 ROUND(number, decimals)

Rounds a number to a given decimal places.

SELECT ROUND(123.456, 2); -- Output: 123.46

 CEIL(number) / FLOOR(number)

CEIL rounds up, FLOOR rounds down.

 SELECT CEIL(4.2), FLOOR(4.8); -- Output: 5 , 4

 POWER(number, exponent)

Returns number raised to a power.

SELECT POWER(2, 3); -- Output: 8

 MOD(dividend, divisor)

Returns remainder after division.

SELECT MOD(17, 5); -- Output: 2

2. Date and Time Functions

Used to handle, format, and manipulate date/time values.

Common Date/Time Functions:

 CURRENT_DATE / CURRENT_TIME

Returns current system date/time.

SELECT CURRENT_DATE, CURRENT_TIME;

 NOW()

Returns current date and time.

SELECT NOW();

 DATE_ADD(date, INTERVAL value unit)

Adds time to a date.

SELECT DATE_ADD('2025-08-12', INTERVAL 10 DAY); -- Output: 2025-08-22

 DATEDIFF(date1, date2)

Difference in days between two dates.

SELECT DATEDIFF('2025-08-22', '2025-08-12'); -- Output: 10

 EXTRACT(unit FROM date)

Extracts parts of a date.

SELECT EXTRACT(YEAR FROM '2025-08-12'); -- Output: 2025

3. String Functions

Used for operations on text (character) data.

Common String Functions:

 LENGTH(string)

Returns number of characters.

SELECT LENGTH('SQL'); -- Output: 3

 UPPER(string) / LOWER(string)

Converts to upper/lower case.

SELECT UPPER('hello'), LOWER('WORLD'); -- Output: HELLO , world

 CONCAT(str1, str2, …)

Joins multiple strings.

SELECT CONCAT('Data', 'Base'); -- Output: Database

 SUBSTRING(string, start, length)

Extracts part of string.

SELECT SUBSTRING('Database', 5, 3); -- Output: bas

 TRIM(string)

Removes spaces from start and end.

SELECT TRIM(' SQL '); -- Output: SQL

4. Aggregate Functions

Used to perform calculations on a set of values and return a single result.

They are often used with GROUP BY clause.

Common Aggregate Functions:

 COUNT(column)

Counts number of rows.

SELECT COUNT(*) FROM Employee;

  SUM(column)

Adds values in a column.

SELECT SUM(Salary) FROM Employee;

 AVG(column) → Calculates average value.

SELECT AVG(Salary) FROM Employee;

 MIN(column) / MAX(column) → Returns smallest/largest value.

SELECT MIN(Salary), MAX(Salary) FROM Employee;

----------------------------------------------------------------------------------------------------------------

Views in DBMS

A View is a virtual table in SQL that is based on the result of a query.

It does not store data physically but displays data stored in underlying tables.

A view can contain data from one or multiple tables using joins and other SQL operations.

Key Points about Views

 It is like a saved SQL query.

 Data is not stored separately — it is fetched from the original tables whenever the
view is used.
 Views help in simplifying queries, enhancing security, and customizing data
presentation.

Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

If e a table Employee with columns (EmpID, Name, Department, Salary):

Create a view to show only employees from the “IT” department:

CREATE VIEW IT_Employees AS

SELECT Name, Salary
FROM Employee
WHERE Department = 'IT';
Using the view:

SELECT * FROM IT_Employees;

This will return the same result as the original query, but now it is simplified.

Indexes in DBMS

An Index is a database object that improves the speed of data retrieval from a table.
It is like an index in a book — it helps find data faster without scanning the whole table.

How it Works

 An index creates an internal data structure (like B-Tree or Hash Table) that stores
key values along with pointers to the actual table rows.
 When a query searches for a value in an indexed column, the DBMS uses the index to
find results quickly.

Syntax
sql
CopyEdit
CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example

If you frequently search employees by Name:

CREATE INDEX idx_name

ON Employee (Name);

Now, queries like:

SELECT * FROM Employee

WHERE Name = 'Amit';

will run faster because of the index.

Advantages of Indexes

 Faster query execution.

 Improved performance in searching and sorting.

Disadvantages

 Takes up extra storage space.

 Slows down INSERT, UPDATE, and DELETE operations because the index must be
updated to
----------------------------------------------------------------------------------------------------------------