MySQL

Introduction to Databases and MySQL

What is a Database?

A database is an organized collection of data that is stored and managed in a structured way to
allow efficient access, manipulation, and management of that data.
It typically stores data in tables, rows, and columns, making it easier to retrieve, modify, and
analyze. Databases are used in various applications, ranging from business operations to
scientific research, web development, and more.
Database Management Systems (DBMS)
A Database Management System (DBMS) is software that provides an interface to interact
with the database. It handles the storage, retrieval, and updating of data, ensuring that it is done
efficiently and securely. DBMSs can be used for relational or non-relational databases and
provide features such as:
• Data Integrity: Ensures accuracy and consistency.

• Security: Provides access controls and data protection.

• Backup and Recovery: Ensures data recovery in case of failure.

• Query Processing: Optimizes SQL or other queries to access data efficiently.

• Data Modeling: Helps define the structure of the data.

RDBMS
Relational Database Management Systems (RDBMS) are a subset of DBMS designed to
handle structured data that is organized into tables
Types of Databases: Relational vs. Non-Relational
• Relational Databases :
• Relational databases store data in tables (also called relations), with rows representing
records and columns representing attributes.

• The data is structured and typically follows a schema with defined relationships between
tables.

• Relational databases use Structured Query Language (SQL) for managing and querying
the data.

• Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

 • Non-Relational Databases (NoSQL):
• Non-relational databases are more flexible and can store unstructured or semi-structured
data.

• They can use different data models like key-value pairs, document-based storage, graph
databases, and column-family stores.

• NoSQL databases can scale horizontally, making them well-suited for handling large
volumes of data, distributed environments, and high-velocity operations.

• Examples: MongoDB, Cassandra, Couchbase, Elasticsearch.

Data

Any sort of information that is stored is called data.

Examples:

• Messages & multimedia on WhatsApp

• Products and orders on Amazon

• Contact details in telephone directory, etc.

Introduction to SQL:
• SQL (Structured Query Language) is a standardized programming language used to
manage and manipulate relational databases.
• It allows users to create, read, update, and delete data (often referred to as CRUD
operations) within databases. SQL is widely used in various database management
systems (DBMS) like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.
What is MySQL?
MySQL is an open-source, relational database management system (RDBMS) that uses
Structured Query Language (SQL) to manage and manipulate data. It is one of the most popular
database systems used in web applications, known for its speed, reliability, and ease of use.
MySQL is commonly used in conjunction with programming languages such as PHP, Java, and
Python to build dynamic websites and applications.
Key characteristics of MySQL:
• Relational Database: Data is stored in tables with predefined relationships between them.

• SQL-Based: Uses SQL for querying and managing the data.

• Open-Source: It is free to use and has an active community contributing to its development.

• Cross-Platform: MySQL can be installed on various operating systems, including Linux,

Windows, macOS, and others.
Features of MySQL
• ACID Compliance:
• MySQL supports ACID (Atomicity, Consistency, Isolation, Durability) properties,
ensuring that transactions are processed reliably.

• High Performance:
• MySQL is optimized for high-speed data access and is suitable for applications that
require fast query execution.

• Scalability:
• MySQL can handle large databases and supports horizontal scaling (replication) for
distributing data across multiple servers.

• Security:
• MySQL provides strong security features, including user authentication, encryption, and
data access controls.

• Indexing:
• It supports various types of indexes to optimize query performance.

• Foreign Key Constraints:

• MySQL supports foreign keys, allowing relationships between tables and ensuring data
integrity.

• Multi-Threaded:
• MySQL uses multi-threading for handling multiple queries simultaneously, enhancing
performance in multi-user environments.

• Cross-Platform:
• It runs on different platforms such as Linux, Windows, macOS, and others.

Basic SQL Commands

SQL commands are categorized into different types:
 • DDL (Data Definition Language): Commands used to define the structure of a database/table.
Example: CREATE, ALTER, DROP.

• DML (Data Manipulation Language): Commands used to manipulate data in tables. Example:
SELECT, INSERT, UPDATE, DELETE.

• DCL (Data Control Language): Commands for controlling access to data. Example: GRANT,
REVOKE.

• TCL (Transaction Control Language): Commands used to manage transactions in the database.
Example: COMMIT, ROLLBACK.
SQL Syntax and Conventions
SQL syntax follows specific rules and conventions:
• SQL Keywords: Keywords (such as SELECT, INSERT, UPDATE) are generally written in
uppercase to make the commands more readable. However, SQL is case-insensitive, so SELECT,
select, or SeLeCt all work the same.

• Semicolon (;): The semicolon is used to terminate a SQL statement. While some databases might
automatically detect the end of a statement, it's good practice to use it explicitly.

• Comments: SQL allows for comments to be included in queries:

• Single-line comment: -- This is a comment --#

• Multi-line comment: /* This is a multi-line comment */

• Strings: Text values (strings) should be enclosed in single quotes ('), e.g., 'Hello'.
Creating and Managing Databases
In SQL, you can create, modify, and delete databases. Below are the key SQL commands used
for managing databases:
CREATE DATABASE
The CREATE DATABASE command is used to create a new database.
Syntax:
CREATE DATABASE database_name;
Example:

CREATE DATABASE company;

SHOW DATABASES
The SHOW DATABASES command displays all the databases available in the MySQL server.
Syntax:
SHOW DATABASES;
USE Database Command
The USE command is used to select a specific database to work with. After selecting a database,
all subsequent SQL commands (like SELECT, INSERT, etc.) will be applied to that database.
Syntax:
USE database_name;
Example:

USE company;
This will set the company database as the active database, so any queries or operations you perform will be
executed within the company database.
Additional SQL Commands for Managing Databases
DROP DATABASE
The DROP DATABASE command deletes a database and all of its data (tables, views, etc.).
Syntax:
DROP DATABASE database_name;
Example:

DROP DATABASE company;

SQL Data Types :

SQL supports various data types for different kinds of data. Some of the common SQL data types
include:
• CHAR: A fixed-length string (e.g., CHAR(10) will always store 10 characters, padding
with spaces if necessary).
• Example: CHAR(20) stores 20 characters.

• VARCHAR: A variable-length string (more efficient than CHAR when data length varies).
• Example: VARCHAR(50) stores up to 50 characters, but only uses the required space.

• INT: Integer data type for whole numbers. We can also use
(Tinyint,smallint,mediumint,bigint).
• Example: INT can store values like -2147483648 to 2147483647.

• FLOAT/DOUBLE/DECIMAL: Used for storing decimal numbers.

• Example: FLOAT can store numbers like 3.14 or -99.99.

• DATE: Used for storing date values ((yyyy-mm-dd).

• Example: DATE stores values like 2025-01-11.
 • DATETIME: Stores both date and time values(yyyy-mm-dd hh:mm:ss)
• Example: DATETIME stores values like 2025-01-11 [Link].

• YEAR : used for storing year (yyyy)

Example : year stores value like 2025.

• ENUM : ENUM is a string data type in MySQL that allows you to define a column with a fixed
set of allowed values.

Example :

• BOOLEAN: Used for storing true or false values (some databases may use TINYINT for
this).
• Example: BOOLEAN can store TRUE or FALSE.

• TEXT: Used for large amounts of text data (larger than VARCHAR). We can also use
(Tinytext , mediumtext,longtext).
• Example: TEXT can store long descriptions or paragraphs.

• BLOB: Used for storing binary large objects, such as images, videos, or files.
• Example: BLOB can store raw binary data.

Creating and Managing Tables in SQL

Tables are the fundamental objects within a relational database. A table consists of rows (records)
and columns (attributes). SQL provides various commands for creating, modifying, and deleting
tables. Below are the commands used to manage tables:
1. CREATE TABLE
The CREATE TABLE command is used to define a new table in a database. You specify the table's
name, followed by the columns that make up the table and their data types.

Syntax:
CREATE TABLE table_name (
column_name1 data_type [constraints],
column_name2 data_type [constraints],
...
);

Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10, 2)
);

This creates a table named employees with five columns:

• employee_id: An integer that serves as the primary key.
• first_name: A variable-length string (up to 50 characters) for the employee's first name.
• last_name: A variable-length string (up to 50 characters) for the employee's last name.
• hire_date: A date column to store the employee's hiring date.
• salary: A decimal
column to store the salary, with up to 10 digits, 2 of which can be after
the decimal point.

2. DESCRIBE Table
The DESCRIBE (or EXPLAIN) command provides information about the structure of a table, such
as the column names, data types, and constraints.
Syntax:
DESCRIBE table_name;

Example:
DESCRIBE employees;

This command will return the table structure for the employees table, showing the column names, data
types, and any constraints (such as PRIMARY KEY).

Basic Data Manipulation in SQL

SQL provides commands for manipulating data within the tables. These commands allow you to
perform operations such as inserting, retrieving, updating, and deleting data. Below is an
overview of the basic data manipulation commands in SQL.
1. Inserting Data (INSERT INTO)
The INSERT INTO statement is used to add new records (rows) to a table.
Syntax:

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

VALUES (value1, value2, ...);

Example:
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', '2025-01-01', 55000.00);

This inserts a new row into the employees table with values for the columns employee_id, first_name,
last_name, hire_date, and salary.

Alternatively, if you're inserting into all columns (in the same order as defined), you can omit the
column names:
INSERT INTO employees
VALUES (2, 'Jane', 'Smith', '2025-02-01', 60000.00);

2. Retrieving Data (SELECT)

The SELECT statement is used to retrieve data from one or more tables. You can specify which
columns to retrieve and filter the results using conditions.
Basic Syntax:
SELECT column1, column2, ... FROM table_name;

If you want to retrieve all columns, you can use *:

SELECT * FROM table_name;

SELECT first_name, last_name, salary FROM employees;

This will retrieve the first_name, last_name, and salary columns from the employees table.
Possible Mistakes

Mistake 1

The number of values that we're inserting must match with the number of column names that are
specified in the query.

INSERT INTO player(name, age, score) VALUES ("Virat", 31) ;

Error: 2valuesfor3columns

Mistake 2

We have to specify only the existing tables in the database.

INSERT INTO players_information(name, age, score)

VALUES ("Virat", 31, 30);

SQL
1
Error: nosuchtable: players_information

Mistake 3

While inserting data, be careful with the datatypes of the input values. Input value datatype
should be same as the column datatype.

INSERT INTO player(name, age, score)

VALUES ("Virat", 31, "Hundred");
SQL
Warning
If the datatype of the input value doesn't match with the datatype of column, SQLite doesn't raise
an error.
WHERE Clause:
The WHERE clause in SQL is used to filter records and specify conditions that must be met for
the records to be selected, updated, or deleted. It helps to retrieve specific data based on the
condition provided.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• column1, column2, ...: The columns you want to retrieve.

• table_name: The name of the table you want to query.

• condition: The condition that the data must meet to be included in the result.
Example 1: Select records based on a condition

SELECT *
FROM employees
WHERE department = 'Sales';

Filtering Data with WHERE Clause:

To filter the results based on a condition, you use the WHERE clause.

SELECT first_name, last_name, salary

FROM employees
WHERE salary > 55000;

This will return only the rows where the salary is greater than 55000.

Update Clause:
The UPDATE clause in SQL is used to modify existing records in a table. You can use it to update
one or more columns in the records that meet a specific condition defined by the WHERE clause.
If you don't use a WHERE clause, all the records in the table will be updated.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: The name of the table where you want to update data.

• column1, column2, ...: The columns you want to update.

• value1, value2, ...: The new values for the respective columns.

• WHERE condition: Specifies the condition that must be met for the rows to be updated. Without
the WHERE clause, all rows in the table will be updated.
Example 1: Simple Update

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

This query updates the salary of the employee with employee_id 101 to 60,000.
Example 2: Updating Multiple Columns

UPDATE employees
SET salary = 70000, department = 'Marketing'
WHERE employee_id = 102;

This query updates both the salary and department of the employee with employee_id 102.
Example 3: Update Based on Multiple Conditions

UPDATE employees
SET salary = 80000
WHERE department = 'Sales' AND hire_date > '2020-01-01';

This query updates the salary of employees in the "Sales" department who were hired after
January 1, 2020.

Example 4: Updating All Records (No WHERE clause)

UPDATE employees
SET department = 'HR';

This query will set the department to 'HR' for every record in the employees table (since no WHERE
condition is provided).

Using Subquery with UPDATE

You can use a subquery to update data based on values from another table.
UPDATE employees
SET department = (SELECT department FROM departments WHERE department_id = 1)
WHERE employee_id = 101;

This query updates the department of the employee with employee_id 101 to the value retrieved
from the departments table based on the department_id of 1.
Example 6: Update with Arithmetic Operations
You can perform arithmetic operations in the SET clause to update values.
UPDATE employees
SET salary = salary + 5000
WHERE department = 'Sales';

This query increases the salary by 5,000 for all employees in the "Sales" department.
Important Notes:
• Always use a WHERE clause unless you intend to update every row in the table.
• If you forget the WHERE clause, all rows will be updated, which can cause data loss or
unintended changes.

Comparison Between DELETE, DROP, and TRUNCATE

The following table lists all the major differences between DELETE, DROP,
and TRUNCATE:
Parameter DELETE DROP TRUNCATE
Type DML DDL DDL
Purpose Deletes specific rows Deletes the entire Deletes all rows but
 based on condition table or database retains table structure
DELETE FROM
DROP TABLE TRUNCATE TABLE
Syntax table_name WHERE
table_name; table_name;
condition;

Rollback Cannot be
Can be Rollback Cannot be Rollback
Support Rollback
Removes table
Data Removes selected
and data Removes all rows
Removal rows
completely
Slower, as each row is Instant removal, Faster than DELETE
Efficiency
processed individually affecting schema but slower than DROP
Does not fire
Triggers Fires triggers Does not fire triggers
triggers

Delete Clause:
The DELETE clause in SQL is used to remove records from a table. You can delete specific rows
that meet certain conditions using the WHERE clause, or delete all rows from the table if no
WHERE condition is provided.
Syntax:

DELETE FROM table_name

WHERE condition;

• table_name: The name of the table from which you want to delete records.

• condition: A condition that specifies which records to delete. If you omit the WHERE clause, all
records in the table will be deleted.
Important Notes:
• WHERE Clause: If you use the WHERE clause, only the rows that meet the specified condition
will be deleted.

• Without WHERE Clause: If you omit the WHERE clause, all rows in the table will be deleted.
However, the table structure remains intact (i.e., the table itself is not deleted).

• Cannot Undo: The DELETE operation is permanent, so be careful when executing it. Once the
data is deleted, it's gone unless you have a backup or transaction to roll back.
Example 1: Delete Specific Record
DELETE FROM employees
WHERE employee_id = 101;
This query deletes the record of the employee with employee_id 101 from the employees table.
Example 2: Delete Multiple Records

DELETE FROM employees

WHERE department = 'Sales';

This query deletes all records of employees who belong to the "Sales" department.
Example 3: Delete All Records (Without WHERE Clause)

DELETE FROM employees;

This query deletes all records from the employees table, but the table structure (columns, indexes,
etc.) remains intact. Be cautious, as this action is irreversible unless you have a backup.
Example 4: Delete Using a Subquery
You can use a subquery to delete records based on data from another table.
DELETE FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name =
'Marketing');
This query deletes all employees who are part of the "Marketing" department by using a subquery to find
the matching department_id.

DELETE vs. TRUNCATE:

• DELETE removes data row by row, and it can be controlled with conditions (WHERE).
• TRUNCATE (a different command) is faster for removing all rows in a table and resets any
auto-increment counters, but it cannot be used with a WHERE clause and is less flexible.

Truncate Command

the TRUNCATE command is used to delete all rows from a table quickly. Unlike the DELETE command,
TRUNCATE does not generate individual row deletions; instead, it removes all data in the table by
deallocating the data pages associated with the table. It is a fast and efficient way to clear out all data
from a table, but with some important differences compared to DELETE.
Syntax:
TRUNCATE TABLE table_name;
 • table_name: The name of the table from which you want to remove all rows.
Key Characteristics of TRUNCATE:
• Faster than DELETE: Since TRUNCATE does not log individual row deletions, it is
generally faster than DELETE when clearing large tables.
• Resets AUTO_INCREMENT: In MySQL, TRUNCATE resets the AUTO_INCREMENT
counter for the table, meaning that any new rows inserted will start with the first auto-
increment value (e.g., 1).
• Cannot Be Rolled Back (in some cases): In MySQL, TRUNCATE is treated like a DDL
(Data Definition Language) operation, not a DML (Data Manipulation Language)
operation. This means that in certain scenarios (like when not wrapped in a transaction),
you cannot roll back the action. However, if you use TRUNCATE inside a transaction in
InnoDB (and the transaction hasn't been committed), it can be rolled back.
• Does Not Fire Triggers: Unlike DELETE, which might trigger BEFORE DELETE and
AFTER DELETE triggers (if they are defined), TRUNCATE does not fire triggers in MySQL.

• Table Structure Remains: Unlike DROP, which removes the table entirely, TRUNCATE
only removes the data. The structure of the table, including columns, constraints, and
indexes, remains unchanged.
• No WHERE Clause: TRUNCATE removes all rows from the table. You cannot specify a
condition or filter like you can with DELETE. If you need to delete specific rows, use
DELETE with a WHERE clause.
Example 1: Basic TRUNCATE Usage
TRUNCATE TABLE employees;
This query will remove all rows from the employees table, but the table structure will remain
intact, and any auto-increment counter will be reset.

DROP COMMAND
he DROP command is used to remove database objects like tables, databases, or indexes
permanently. When you use the DROP command, the object (such as a table) and all its data are
completely removed, and this action cannot be undone unless you have a backup.
Common Uses of the DROP Command
• DROP TABLE: Removes a table and all its data, indexes, and associated constraints.

• DROP DATABASE: Removes a database and all the tables contained in it.

• DROP INDEX: Removes an index from a table.

• DROP VIEW: Removes a view from the database.

Syntax for DROP Commands
1. DROP TABLE
DROP TABLE table_name;
• table_name: The name of the table you want to drop.
Example:

DROP TABLE employees;

This query will completely remove the employees table from the database, including all rows,
structure (columns), constraints, and indexes.
• DROP DATABASE

DROP DATABASE database_name;

This query will completely remove the company database, including all tables and data inside it.

Key Characteristics of DROP:

• Irreversible: The DROP command permanently deletes the object and its associated data.
Once executed, the data is gone unless you have a backup.
• Cannot Be Rolled Back (in most cases): Unlike DELETE or UPDATE, which are DML
operations and can be rolled back in a transaction, DROP is a DDL operation, and in most
cases, it cannot be rolled back.
• No Data Left Behind: When dropping a table, all data stored within that table is deleted.
The structure of the table (like columns, constraints, and indexes) is also completely
removed.
• DROP DATABASE Removes Everything: When you drop a database, all tables, views,
indexes, and stored procedures in that database are removed as well.
• Dropping Foreign Key Constraints: If there are foreign key constraints referencing the
table being dropped, you may get an error unless the foreign key constraint is explicitly
dropped first. You can either drop the constraints or set them to cascade.

ALTER STATEMENT:
the ALTER statement is used to modify the structure of an existing database object, such as a
table. It allows you to add, delete, or modify columns, change a table’s name, or even rename or
modify constraints like indexes or primary keys.
Syntax:

ALTER TABLE table_name

[operation];
 • table_name: The name of the table you want to modify.

• operation: The type of modification you want to make (e.g., adding, deleting, or modifying
columns).
Common Operations with ALTER TABLE
• Add a New Column

• Modify an Existing Column

• Rename a Column or Table

• Drop a Column

• Add/Remove Constraints (like PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.)

• Change the Data Type of a Column

• Add or Drop an Index
1. Add a New Column
You can use the ADD COLUMN clause to add a new column to an existing table.
Syntax:
ALTER TABLE table_name
ADD COLUMN column_name datatype;
Example:
ALTER TABLE employees
ADD COLUMN email VARCHAR(255);

This query adds a new column called email of type VARCHAR(255) to the employees table.
2. Modify an Existing Column
You can change the properties of an existing column, such as its data type or default value, using
the MODIFY COLUMN clause.
Syntax:

ALTER TABLE table_name

MODIFY COLUMN column_name new_datatype;

Example:
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10, 2);
3. Rename a Column or Table
You can rename a column or a table using the RENAME COLUMN or RENAME TO clause.
Rename a Column:

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;
Example:
ALTER TABLE employees
RENAME COLUMN email TO email_address;

This query renames the email column to email_address in the employees table.
Rename a Table:

ALTER TABLE old_table_name

RENAME TO new_table_name;
Example:
ALTER TABLE employees
RENAME TO staff;

This query renames the employees table to staff.

4. Drop a Column
You can remove a column from a table using the DROP COLUMN clause.
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;

Example:
ALTER TABLE employees
DROP COLUMN email_address;
This query deletes the email_address column from the employees table.
Like operators
The LIKE operator in MySQL is a powerful tool used in WHERE clauses to search
for patterns within strings. Here's a breakdown of its key features:
Purpose:
• It enables you to perform pattern matching, allowing you to find data that
resembles a specific format rather than requiring an exact match.
• This is particularly useful when you need to search for:
• Names starting with a certain letter.
• Words containing a specific substring.
• Data that adheres to a particular structure.
Wildcards:
The LIKE operator works in conjunction with wildcard characters:
• % (Percent sign):
• Represents zero or more characters.
• Example: LIKE 'a%' matches any string that starts with "a". LIKE '%xyz%'
matches any string that contains "xyz".
• _ (Underscore):
• Represents a single character.
• Example: LIKE 'a_c' matches strings like "abc", "adc", etc.
Syntax:
The basic syntax is:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Examples:
• Search for names starting with "J":
SELECT * FROM employees
WHERE name LIKE 'J%';

• Search for names ending with "n":

SELECT * FROM employees
WHERE name LIKE '%n';

SELECT * FROM custom WHERE name LIKE "a%"

OR name LIKE "s%";

 This will return all records where the name ends with "n", like "John", "Brian", etc.
• Search for names containing "an":
SELECT * FROM employees
WHERE name LIKE '%an%';
 This will return all records where the name contains "an", like "James", "Amanda", etc.
• Search for names with exactly 4 characters and starting with "J":
SELECT * FROM employees
WHERE name LIKE 'J___';

 This will return names that start with "J" and have exactly 3 more characters, like "John",
"Jack", etc.
• Search for names with "a" in the second position:
SELECT * FROM employees
WHERE name LIKE '_a%';

• This will return records where the second letter of the name is "a", like "James", "Carlos",
etc.

Combining % and _:
You can combine both wildcards to create more complex patterns.
• Match names starting with "J" and having exactly 4 characters:
SELECT * FROM employees
WHERE name LIKE 'J__%';
This will match names like "John", "Jack", "Joe", etc., starting with "J" and having at
least 3 characters.
Examples of wildcard combinations:
• Match names with exactly 3 characters, with "a" as the second character:
SELECT * FROM employees
WHERE name LIKE '_a_';
This will match names like "Jay", "Max", etc., where the second character is "a" and the
name is 3 characters long.
Important Notes:
• By default, the LIKE operator is case-insensitive in MySQL, but this
behavior may vary depending on the collation of the column or database. To
perform case-sensitive searches, you can use the BINARY keyword:
SELECT * FROM employees
WHERE BINARY name LIKE 'J%';
This will match names starting with a capital "J" but not lowercase "j".
• Wildcards only work with the LIKE operator. If you need more advanced
pattern matching (like regular expressions), you can use the REGEXP operator
in MySQL.
Summary:
• % matches zero or more characters.

• _ matches exactly one character.

These wildcards, combined with the LIKE operator, provide powerful pattern
matching capabilities in MySQL queries.

Constraints in SQL.
SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. If there is any violation between the constraint and the data entered ,
then the data is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table
level

constraints apply to the whole table.

Below are different types of constraints available in SQL,

NOT NULL CONSTRAINTS:

By default, a column can hold NULL values, but the NOT NULL constraint enforces a column to NOT
accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or
update a record without adding a value to this field.
CREATE TABLE users (

id INT NOT NULL,

name VARCHAR(100) NOT NULL

);

UNIQUE CONSTRAINTS:
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and
PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE [Link], you can have many
UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

CREATE TABLE users (

email VARCHAR(100) UNIQUE

);

PRIMARY KEY CONSTRAINTS:

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key.

CREATE TABLE users (

id INT PRIMARY KEY,

name VARCHAR(100)

);

FOREIGN KEY CONSTRAINTS:

A FOREIGN KEY is a field in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the
referenced or parent table.

CREATE TABLE orders (

order_id INT PRIMARY KEY,

user_id INT,
 FOREIGN KEY (user_id) REFERENCES users(id)

);

CHECK CONSTARINTS:
The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a column it will allow only certain values for this column which
will satisfy a specified condition.

CREATE TABLE products (

id INT PRIMARY KEY,

price DECIMAL(10, 2),

CHECK (price > 0)

);

DEFAULT CONSTARINTS:
The DEFAULT constraint is used to set a default value for a column.

The default value will be added to all new records, if no other value is specified.

CREATE TABLE employees (

id INT,

status VARCHAR(20) DEFAULT 'Active'

);

AUTO INCREMENT CONSTARINTS:

Auto-increment allows a unique number to be generated automatically when a new record is inserted into
a table. Often this is the primary key field that we would like to be created automatically every time a new
record is inserted

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
Pagination
Pagination in MySQL is typically done using the LIMIT and OFFSET clauses to fetch a subset of
rows from a larger dataset. It allows you to display results in pages rather than retrieving the
entire dataset at once.
Basic Syntax:
SELECT * FROM table_name
LIMIT [offset], [limit];

• offset: The starting point (0-based index) of the results.

• limit: The maximum number of records to retrieve.

Example:
Suppose you have a table called employees and you want to retrieve records in pages of 10 rows
each.
• Page 1: To get the first 10 rows:
SELECT * FROM employees
LIMIT 0, 10;

Using OFFSET Syntax (Alternative):

The LIMIT and OFFSET can also be written as:
SELECT * FROM employees
LIMIT 10 OFFSET 10; -- This is the same as LIMIT 10, 10

Example with a real use case (Page 1, 10 records per page):

SELECT * FROM employees

LIMIT 0, 10; -- First 10 records

SELECT * FROM employees

LIMIT 10, 10; -- Next 10 records (Page 2)

Here’s a detailed overview of String functions, Date and Time functions, Numeric functions,
Control flow functions, Mathematical operators, and Regular expressions in MySQL
queries:
1. String Functions in MySQL
• CONCAT(): Concatenates two or more strings.
SELECT CONCAT('Hello', ' ', 'World');
-- Output: Hello World
• LENGTH(): Returns the length of a string (in bytes).
SELECT LENGTH('Hello');
-- Output: 5
 • SUBSTRING(): Extracts a portion of a string.
SELECT SUBSTRING('Hello World', 7, 5);
-- Output: World
• UPPER(): Converts a string to uppercase.
UPDATE custom
SET name = UPPER(name);
SELECT UPPER('hello');
-- Output: HELLO
• LOWER(): Converts a string to lowercase.
SELECT LOWER('HELLO');
-- Output: hello
• TRIM(): Removes leading and trailing spaces from a string.
SELECT TRIM(' Hello ');
-- Output: Hello
• REPLACE(): Replaces all occurrences of a substring within a string.
SELECT REPLACE('Hello World', 'World', 'Everyone');
-- Output: Hello Everyone

• LEFT(): Returns the left part of a string with a specified number of characters.
SELECT LEFT('Hello', 3);
-- Output: Hel
• RIGHT(): Returns the right part of a string with a specified number of characters.
SELECT RIGHT('Hello', 3);
-- Output: llo
2. Date and Time Functions in MySQL
• NOW(): Returns the current date and time.
SELECT NOW();
-- Output: 2025-03-05 [Link]
• CURDATE(): Returns the current date.
SELECT CURDATE();
-- Output: 2025-03-05
• CURTIME(): Returns the current time.
SELECT CURTIME();
-- Output: [Link]
• DATE_FORMAT(): Formats a date value according to a specified format.
SELECT DATE_FORMAT('2025-03-05', '%M %d, %Y');
-- Output: March 05, 2025
• DATE_ADD(): Adds a time interval to a date.
SELECT DATE_ADD('2025-03-05', INTERVAL 7 DAY);
-- Output: 2025-03-12
• DATE_SUB(): Subtracts a time interval from a date.
 SELECT DATE_SUB('2025-03-05', INTERVAL 7 DAY);
-- Output: 2025-02-26
• YEAR(), MONTH(), DAY(): Extracts the year, month, or day from a date.
SELECT YEAR('2025-03-05');
-- Output: 2025
• DATEDIFF(): Returns the difference in days between two dates.
SELECT DATEDIFF('2025-03-12', '2025-03-05');
-- Output: 7
3. Numeric Functions in MySQL
• ROUND(): Rounds a number to a specified number of decimal places.
SELECT ROUND(123.4567, 2);
-- Output: 123.46
• FLOOR(): Returns the largest integer value less than or equal to a number.
SELECT FLOOR(123.456);
-- Output: 123
• CEIL() or CEILING(): Returns the smallest integer value greater than or equal to a
number.
SELECT CEIL(123.456);
-- Output: 124
• ABS(): Returns the absolute value of a number.
SELECT ABS(-123.45);
-- Output: 123.45
• MOD(): Returns the remainder of a number divided by another number.
SELECT MOD(10, 3);
-- Output: 1
5. Mathematical Operators and Expressions
• +, -, *, /: Standard mathematical operators for addition, subtraction, multiplication, and
division.
SELECT 5 + 3, 10 - 2, 4 * 2, 8 / 2;
-- Output: 8, 8, 8, 4
• MOD(): Returns the remainder of a division (modulo operator).
SELECT MOD(10, 3);
-- Output: 1
• POWER(): Returns the value of a number raised to a power.
SELECT POWER(2, 3);
-- Output: 8
• SQRT(): Returns the square root of a number.
SELECT SQRT(9);
-- Output: 3
• LOG(): Returns the natural logarithm of a number.
SELECT LOG(100);
-- Output: 4.605170
 AGGREGATION FUNCTIONS
In MySQL, aggregation refers to the process of combining multiple rows of data into a single
result based on some criteria. This is typically done using aggregate functions in combination
with the GROUP BY clause. Common aggregate functions include:
• COUNT(): Counts the number of rows or non-NULL values.
• SUM(): Sums the values of a column.
• AVG(): Calculates the average of a numeric column.
• MIN(): Returns the minimum value from a column.
• MAX(): Returns the maximum value from a column.
Basic Syntax of Aggregation
Here’s a basic structure of using aggregate functions:
SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

Example Queries:
1. COUNT()
To count the number of rows in a table:
SELECT COUNT(*) FROM employees;
To count the number of employees in each department:
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

2. SUM()
To calculate the total salary of all employees:
SELECT SUM(salary) FROM employees;
To calculate the total salary for each department:
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

3. AVG()
To calculate the average salary of employees:
SELECT AVG(salary) FROM employees;
To calculate the average salary per department:
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

4. MIN() and MAX()

To find the lowest and highest salary in the table:
SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM employees;
To find the minimum and maximum salary in each department:
SELECT department_id, MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

5. HAVING Clause
You can also use the HAVING clause to filter results after aggregation. The WHERE clause
filters rows before aggregation, while HAVING filters after aggregation.
For example, to find departments with a total salary greater than $100,000:
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;
Combining Aggregate Functions
You can combine multiple aggregate functions in a single query:
SELECT department_id,
COUNT(*) AS num_employees,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

GROUP BY and HAVING and ORDER BY

In SQL, GROUP BY and HAVING are often used together to aggregate data and filter it based
on certain conditions. Let me break down each of them for you:
GROUP BY
• Purpose: The GROUP BY clause is used to group rows that have the same values in
specified columns into summary rows, like "total" or "average". It is often used in
combination with aggregate functions (e.g., COUNT(), SUM(), AVG(), MAX(), MIN()).
 • How it works: When you use GROUP BY, SQL will organize the data into groups based
on the column(s) you specify. Each group can then be summarized using aggregate
functions.
Example of GROUP BY:
SELECT dept, SUM(salary)
FROM employees
GROUP BY dept;
This will give you the total salary for each department.

HAVING
• Purpose: The HAVING clause is used to filter groups that are created by the GROUP BY
clause. It is similar to the WHERE clause, but WHERE filters rows before grouping,
while HAVING filters groups after aggregation.
• How it works: You can use HAVING to apply conditions to aggregated data (like the
result of SUM, AVG, etc.).
Example of HAVING:
SELECT dept, SUM(salary)
FROM employees
GROUP BY dept
HAVING SUM(salary) > 30000;
This will give you the total salary for each department, but only for departments where the total
salary is greater than 30,000.
Key Points to Remember:
• GROUP BY is used to create groups of rows based on one or more columns.
• HAVING is used to filter those groups based on aggregate functions (e.g., SUM(),
AVG()), whereas WHERE is used to filter rows before grouping.
Full Example with Both GROUP BY and HAVING:
SELECT dept, COUNT(*), SUM(salary)
FROM employees
GROUP BY dept
HAVING SUM(salary) > 50000;
This will count the number of employees and calculate the total salary for each department. It
will only return departments where the total salary exceeds 50,000.

Example 1: Finding departments with more than 5 employees

Suppose you want to find departments where there are more than 5 employees.
SELECT dept, COUNT(*) AS employee_count
FROM employees
GROUP BY dept
HAVING COUNT(*) > 5;
• Explanation: This query groups the employees by department (dept), counts the number
of employees in each department (COUNT(*)), and then filters those departments to
include only those with more than 5 employees using the HAVING clause.
Example 2: Finding departments with total salary less than 100,000
Suppose you want to find departments where the total salary of employees is less than 100,000.
SELECT dept, SUM(salary) AS total_salary
FROM employees
GROUP BY dept
HAVING SUM(salary) < 100000;
• Explanation: This query groups employees by department and calculates the total salary
for each department (SUM(salary)). Then it filters the departments with a total salary of
less than 100,000 using the HAVING clause.
Example 3: Departments with average salary greater than 50,000
If you're interested in departments where the average salary is greater than 50,000, you can use
the AVG() function.
SELECT dept, AVG(salary) AS average_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 50000;
• Explanation: This query groups employees by department and calculates the average
salary (AVG(salary)) for each department. It then filters the departments where the
average salary is greater than 50,000 using the HAVING clause.
Order by:-
The ORDER BY clause in MySQL is used to sort the result set of a SELECT statement. You can
sort the data in ascending (A-Z, 0-9) or descending (Z-A, 9-0) order based on one or more
columns.
Here's the basic syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort [ASC | DESC];
Example:-
SELECT customer_id, first_name, last_name
FROM customers
ORDER BY first_name;
Example 4: Finding the department with the highest total salary
If you want to find the department that has the highest total salary, you can use the ORDER BY
clause along with GROUP BY.
SELECT dept, SUM(salary) AS total_salary
FROM employees
GROUP BY dept
ORDER BY total_salary DESC
LIMIT 1;
• Explanation: This query calculates the total salary per department, sorts the result in
descending order (ORDER BY total_salary DESC), and limits the result to just the top
department (LIMIT 1), which will be the department with the highest total salary.
Example 5: Finding departments with more than one employee with a salary above 100,000
If you want to find departments where there are more than one employee earning a salary greater
than 100,000:
SELECT dept, COUNT(*) AS high_salary_count
FROM employees
WHERE salary > 100000
GROUP BY dept
HAVING COUNT(*) > 1;

• Explanation: This query first filters employees with a salary greater than 100,000 using
the WHERE clause. Then it groups those filtered employees by department (GROUP BY
dept) and counts how many employees in each department have a salary greater than
100,000. It then filters the results to only include departments with more than one such
employee using the HAVING clause.
Example 6: Finding the department with the lowest average salary
To find the department with the lowest average salary:
SELECT dept, AVG(salary) AS average_salary
FROM employees
GROUP BY dept
ORDER BY average_salary ASC
LIMIT 1;
• Explanation: This query calculates the average salary for each department, sorts the
departments by average salary in ascending order (ORDER BY average_salary ASC),
and limits the result to the department with the lowest average salary using LIMIT 1.
Example 7: Departments with a salary greater than the total salary of all departments
combined
This query finds departments where the total salary is greater than half of the total salary of all
departments combined.
SELECT dept, SUM(salary) AS total_salary
FROM employees
GROUP BY dept
HAVING SUM(salary) > (SELECT SUM(salary) / 2 FROM employees);

SELECT dept, MIN(salary) AS min_salary, MAX(salary) AS max_salary

FROM employees
GROUP BY dept
HAVING MIN(salary) > 30000 AND MAX(salary) > 30000;
• Explanation: The HAVING clause filters departments where the total salary is greater
than half of the total salary of all employees. The subquery (SELECT SUM(salary)
FROM employees) calculates the total salary of all employees.
Example 8: Find departments with a salary range (difference between max and min salary)
greater than 50,000
If you want to identify departments where the difference between the highest and lowest salaries
is greater than 50,000:
SELECT dept, MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY dept
HAVING (MAX(salary) - MIN(salary)) > 50000;
• Explanation: This query calculates the salary range (difference between the max and min
salary) for each department using MAX(salary) - MIN(salary). The HAVING clause
filters the departments where this range is greater than 50,000.
Practice Questions
1. Find departments with the maximum salary greater than 50,000

2. Count departments with fewer than 3 employees

3. Find departments with both the highest and lowest salaries greater than 30,000

4. Departments where the total salary is between 50,000 and 100,000

JOINS

In MySQL, joins are used to combine rows from two or more tables based on a related column
between them. Here’s a detailed overview of the different types of joins you can use in MySQL:

1. INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.

Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example:

SELECT [Link], departments.department_name

FROM employees
INNER JOIN departments ON employees.department_id = [Link];

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table and the matched records from the
right table. If there is no match, NULL values are returned for columns from the right table.

Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

SELECT [Link], departments.department_name

FROM employees
LEFT JOIN departments ON employees.department_id = [Link];

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table and the matched records from
the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

SELECT [Link], departments.department_name

FROM employees
RIGHT JOIN departments ON employees.department_id = [Link];

. FULL JOIN (or FULL OUTER JOIN)

MySQL does not support FULL OUTER JOIN directly, but you can achieve the same result using
a combination of LEFT JOIN and RIGHT JOIN with a UNION.
Example:
SELECT [Link], departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = [Link]
UNION
SELECT [Link], departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = [Link];

INDEXES
Think of a table in MySQL like a book and its data as the content. Without an index, if you want
to find specific information, you have to read through the entire book (scan the whole table). An
index is like the index at the back of a book – it provides a shortcut, allowing MySQL to quickly
locate the rows that match your search criteria without scanning the entire table.
Why Use Indexes?
* Speed up SELECT queries: This is the primary reason. Indexes drastically reduce the time it
takes to retrieve data.
* Improve WHERE clause performance: When you filter data using WHERE clauses, indexes
help MySQL quickly identify the relevant rows.
* Optimize JOIN operations: Indexes on the join columns can significantly speed up how
MySQL combines data from multiple tables.
* Enforce UNIQUE constraints: Unique indexes ensure that all values in a column are distinct.
* Speed up ORDER BY and GROUP BY in some cases: If the index matches the sorting or
grouping order, MySQL can use the index to avoid extra sorting steps.

How Indexes Work (Simplified):

When you create an index on a column (or a set of columns), MySQL creates a separate data
structure that contains a copy of the indexed data along with pointers to the actual rows in the
table. This index is sorted, allowing MySQL to use efficient search algorithms (like binary search
in B-trees) to quickly locate the desired data.
Creating Indexes:
You can create indexes using the CREATE INDEX or ALTER TABLE statements:
-- Using CREATE INDEX
CREATE INDEX idx_lastname ON customers (last_name);

-- Creating a composite index (on multiple columns)

CREATE INDEX idx_name_email ON customers (first_name, last_name, email);

-- Adding a unique index using ALTER TABLE

ALTER TABLE products ADD UNIQUE INDEX idx_product_code (product_code);
note :-
indexes:-
create :-
CREATE INDEX index_name
ON table_name (column_name);
display:-
SHOW INDEX FROM tb_name;
drop:-
DROP INDEX ind_name ON tb_name;

VIEWS
In MySQL, a view is a virtual table that is based on the result of a SELECT query. It
doesn't store data physically, but rather, it provides a way to encapsulate a query
and present it as a table. You can use views to simplify complex queries, reuse SQL
code, and encapsulate logic.
Basic Syntax to Create a View:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE status = 'active';

n this example, active_employees is a view that displays the employee_id, first_name, last_name, and
hire_date of all employees who have a status of 'active'.
Querying a View:
Once the view is created, you can query it just like a regular table:
SELECT * FROM active_employees;

Altering a View:
You can modify the structure of an existing view with the CREATE OR REPLACE VIEW statement:
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, hire_date, department
FROM employees
WHERE status = 'active';

Dropping a View:
If you no longer need a view, you can remove it using the DROP VIEW statement:
DROP VIEW active_employees;
note :-
views:-
create :-
CREATE VIEW view_name AS
SELECT column1, column2, ...
 FROM table_name
WHERE condition;
display:-
SHOW FULL TABLES WHERE
Table_type = 'VIEW';
drop :-
DROP VIEW view_name;

SET OPERATIONS

In MySQL, set operators are used to combine the results of multiple queries. The most common
set operators are:
• UNION

• UNION ALL

• INTERSECT (not directly supported in MySQL, but can be simulated)

• EXCEPT (also not directly supported in MySQL, but can be simulated)

Each set operator works with the result sets of multiple SELECT queries. Let’s go through them in
detail with examples:

1. UNION
The UNION operator combines the results of two or more SELECT statements into a
single result set. It removes duplicate rows by default.
Syntax:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Example: Assuming we have two tables, employees_2022 and employees_2023, with the
same structure:

Table: employees_2022
id name
1 Alice
2 Bob
Table: employees_2023
id name
2 Bob
3 Charlie
Query:
SELECT name FROM employees_2022
UNION
SELECT name FROM employees_2023;

Result:
name
Alice
Bob
Charlie

2. UNION ALL
The UNION ALL operator also combines the results of two or more SELECT statements
but includes all duplicates.
Syntax:
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
Example: Using the same tables as above:
Query:
SELECT name FROM employees_2022
UNION ALL
SELECT name FROM employees_2023;

Result:
name
Alice
Bob
Bob
Charlie

3. INTERSECT
MySQL does not support the INTERSECT operator directly. However, you can achieve
the same result using a subquery.
Example: To find names that exist in both employees_2022 and employees_2023:
 Query:
SELECT name FROM employees_2022
WHERE name IN (SELECT name FROM employees_2023);

Result:
name
Bob

4. EXCEPT
MySQL does not support the EXCEPT operator directly either. You can achieve similar
functionality using a LEFT JOIN or NOT IN.
Example: To find names in employees_2022 that are not in employees_2023:
Query:
SELECT name FROM employees_2022
WHERE name NOT IN (SELECT name FROM employees_2023);

Result:
name
Alice
Summary of Set Operations
• UNION: Combines results from multiple queries and removes duplicates.
• UNION ALL: Combines results from multiple queries and includes duplicates.
• INTERSECT: Not directly supported, but can be simulated using INNER
JOIN or EXISTS.

• EXCEPT: Not directly supported, but can be simulated using LEFT JOIN or NOT IN.

SUBQUERIES

Subqueries in MySQL
• A SQL subquery is a query inside another query.
• The outer query in which the inner query is inserted is the main query.
• We can include a subquery into the SELECT, FROM, JOIN, WHERE or HAVING
clause.
• We can also use subqueries when updating the database (i.e. in INSERT, UPDATE, and
DELETE statements).
• ORDER BY command cannot be used in a subquery.
Types of Subquery
• Scalar subqueries (Single-row)
• Multiple row subqueries (Multi-row)

Scalar Subquery
• A scalar subquery is a subquery that returns a single value.
• Mostly Aggregate Functions (COUNT, MIN, MAX, SUM, AVG) used in scalar subquery.
• Scalar subquery can also be a query with expression which returns single value as
[Link] is typically used in a context where a single value is expected, such as in the
SELECT clause or a comparison operation (WHERE or HAVING Clause).
Syntax :-
Select column
from table
where column operator ( sub query);
example :-
SELECT name
FROM employee
WHERE salary <(SELECT AVG(salary) FROM employee);

Multi-row Subquery
• It is a type of subquery in SQL that returns multiple rows of data.
• Multi-row subqueries can return multiple rows that can be used as a set of values for
comparison, filtering, or other operations in the outer query.
Syntax:-
Select columns
from table
where column in/not in /any/all ( sub query);
example :-
SELECT name, department
FROM employee
WHERE department IN (SELECT department FROM employee GROUP BY
department HAVING COUNT(*) > 2 );

ANY Operator
• The ANY operator is used in combination with comparison operators to compare a value
with a set of values returned by a subquery.(Multiple-row subquery)
• It returns true if the comparison holds true for at least one value in the set.
Syntax :-
Select columns
from table
where column any ( select column from table);
example :-
SELECT *
FROM employee
WHERE salary = ANY ( SELECT salary FROM employee WHERE department = 'HR');

ALL Operator
• The ALL operator is used in combination with comparison operators to compare a value
with a set of values returned by a subquery.(Multiple-row subquery)
• It returns true if the comparison holds true for all values in the set.
SYNTAX:
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (Subquery);
Example :-
SELECT *
FROM employee
WHERE salary > ALL ( SELECT salary FROM employee WHERE department =
'Accounts ' );

Transaction Management
 • Transaction Management in MySQL refers to controlling a set of SQL operations so that they
either complete entirely (commit) or not at all (rollback), ensuring data integrity and
consistency.

• It’s crucial for applications where multiple operations need to be treated as a single unit of work
—like bank transfers, bookings, etc.

What is Transaction?
• All modification is successful when the transaction is committed.
• All modifications are undone when the transaction is rollback.

ACID properties :-

Atomicity
• Atomicity ensures that the entire transaction is treated as a single unit of work.
• Atomicity simply means that all queries in a transaction must succeed for the transaction
to succeed.
• If one query fails, the entire transaction fails.

Consistency
• Consistency ensures that the database remains in a valid state before and after the
transaction.

Isolation
• Isolation ensures that the execution of a transaction is independent of other transactions
running concurrently.

Durability
• Durability ensures that once a transaction is committed, its effects are permanent and
survive system failures.

TRANSCATION CAN BE ACHIEVED BY FOLLOWING COMMANDS .

Auto Commit Mode
 • By default, many databases operate in auto commit mode, where each individual SQL
statement is automatically committed.
• In auto commit mode each SQL statement is transaction.
• To execute multiple statements as single transaction, we must explicitly start the
transaction.
START TRANSACTION;

START TRANSACTION
• This command marks the beginning of a transaction.
• All the subsequent SQL statements/queries within the same session will be considered
part of the same transaction until it is either committed or rolled back.
SYNTAX:
START TRANSACTION;
or
BEGIN TRANSACTION;

COMMIT
• This command is used to permanently save the changes made during the current
transaction.
• Once a transaction is committed, its changes are made permanent and cannot be rolled
back
SYNTAX:-
COMMIT
ROLLBACK
• This command is used to undo the changes made during the current transaction.
• It rolls back the transaction to its starting point, discarding any modifications made within
the transaction.
SYNTAX:-
ROLLBACK
SAVEPOINT
• This command allows you to set a point within a transaction to which you can later roll
back.
• It is useful when you want to undo part of a transaction without rolling back the entire
 transaction.
SYNTAX:
SAVEPOINT savepoint_name ;

ROLLBACK TO
• After setting a savepoint, you can roll back to it using
SYNTAX:
ROLLBACK TO savepoint_name ;
Example :-
START TRANSACTION;
INSERT INTO emp (id, name, salary, department, dob) VALUES (111, 'Joe', 50000, 'HR', '1990-
01-01');
SAVEPOINT sp_after_insert;
UPDATE emp SET salary = 60000 WHERE id = 106;
SAVEPOINT sp_after_update;
DELETE FROM emp WHERE id = 102;
ROLLBACK TO sp_after_update;
--check emp table
ROLLBACK TO sp_after_insert;
--check emp table

IMAGE

• In MySQL, you can insert images (or any binary files) into a table using a BLOB
(Binary Large Object) data type. To do this, you can use the LOAD_FILE() function to
read a file (like an image) from the file system and store its binary content into the
database.

2. Create Table to Store Image

create table image(image_name varchar(20),file longblob);
Use BLOB, MEDIUMBLOB, or LONGBLOB based on image size.
🔹 3. Insert Image Using LOAD_FILE()

insert into image values("img",load_file("C:\\ProgramData\\MySQL\\MySQL

Server 8.0\\Uploads\\[Link]"));
select * from image;

NORMALIZATION

Normalization:-
Normalization is a process used in database design to minimize redundancy and dependency

by organizing fields and tables of a database. The goal is to ensure that data is logically stored

and reduces the chances of data anomalies (update, delete, and insert anomalies). Let's break

down the different normal forms:

a. First Normal Form (1NF)

A table is in 1NF if:

• All the columns contain atomic values (indivisible values).

• There are no repeating groups

b. Second Normal Form (2NF)

A table is in 2NF if:

• It is in 1NF.

• All non-key attributes are fully functionally dependent on the primary key (no partial

dependencies).
c. Third Normal Form (3NF)
A table is in 3NF if:

• It is in 2NF.

• There is no transitive dependency (non-prime attributes are not dependent on other

non-prime attributes).

Denormalization
Denormalization is the process of combining tables to optimize read performance at the cost

of redundancy and update anomalies. While normalization improves data integrity,

denormalization improves query performance by reducing the need for complex joins.