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

1. Relational Databases :
o Relational databases store data in tables (also called relations), with rows representing
records and columns representing attributes.
o The data is structured and typically follows a schema with defined relationships between
tables.
o Relational databases use Structured Query Language (SQL) for managing and querying
the data.
o Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

Key Features :

o Structured Data: Data is stored in tables with predefined schemas.

o ACID Properties: Ensures reliable transactions (Atomicity, Consistency, Isolation,
Durability).
o Normalization: Reduces data redundancy and ensures data integrity.
o Foreign Keys & Relationships: Allows relationships between different tables using
foreign keys.

2. Non-Relational Databases (NoSQL):

o Non-relational databases are more flexible and can store unstructured or semi-structured
data.
o They can use different data models like key-value pairs, document-based storage, graph
databases, and column-family stores.
o NoSQL databases can scale horizontally, making them well-suited for handling large
volumes of data, distributed environments, and high-velocity operations.
 o Examples: MongoDB, Cassandra, Couchbase, Redis, Elasticsearch.

Key Features of NoSQL Databases:

o Flexible Schema: No predefined schema, allowing more fluid data structures.

o Scalability: Easily scalable and can handle large amounts of data.
o Variety of Data Models: Can store data in key-value, document, column-family, or
graph formats.
o Eventual Consistency: Some NoSQL databases relax consistency in favor of availability
and partition tolerance (CAP theorem).

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 a server-based, multi-user, multi-threaded RDBMS that uses SQL to manage and
manipulate relational data. It was initially developed by MySQL AB (now owned by Oracle
Corporation) and has become an integral part of the LAMP stack (Linux, Apache, MySQL,
PHP/Perl/Python), which is a popular open-source platform for web development.

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

1. ACID Compliance:
o MySQL supports ACID (Atomicity, Consistency, Isolation, Durability) properties,
ensuring that transactions are processed reliably.

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

3. Scalability:
o MySQL can handle large databases and supports horizontal scaling (replication) for
distributing data across multiple servers.
 4. Replication:
o MySQL supports master-slave replication, allowing data to be copied across multiple
servers for load balancing and fault tolerance.

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

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

7. Foreign Key Constraints:

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

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

9. Cross-Platform:
o 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:

1. 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.
2. 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.
3. Comments: SQL allows for comments to be included in queries:
o Single-line comment: -- This is a comment --#
 o Multi-line comment: /* This is a multi-line comment */
4. 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:

1. CHAR: A fixed-length string (e.g., CHAR(10) will always store 10 characters, padding
with spaces if necessary).
o Example: CHAR(20) stores 20 characters.

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

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

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

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

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

o Example: DATE stores values like 2025-01-11.

6. DATETIME: Stores both date and time values(yyyy-mm-dd hh:mm:ss)

o Example: DATETIME stores values like 2025-01-11 14:30:00.
7. YEAR : used for storing year (yyyy)
Example : year stores value like 2025.
8. ENUM : ENUM is a string data type in MySQL that allows you to define a column with a fixed
set of allowed values.
 Example :

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

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

11. BLOB: Used for storing binary large objects, such as images, videos, or files.
o 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:

1. Always use a WHERE clause unless you intend to update every row in the table.
2. 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

Deletes specific rows Deletes the entire Deletes all rows but
Purpose
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

Data Removes selected Removes table Removes all rows

Parameter DELETE DROP TRUNCATE

and data
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:

1. Faster than DELETE: Since TRUNCATE does not log individual row deletions, it is
generally faster than DELETE when clearing large tables.
2. 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).
3. 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.
4. 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.
5. 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.
6. 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

1. DROP TABLE: Removes a table and all its data, indexes, and associated constraints.
2. DROP DATABASE: Removes a database and all the tables contained in it.
3. DROP INDEX: Removes an index from a table.
4. 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.

3. 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:

1. Irreversible: The DROP command permanently deletes the object and its associated data.
Once executed, the data is gone unless you have a backup.
2. 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.
3. 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.
4. DROP DATABASE Removes Everything: When you drop a database, all tables, views,
indexes, and stored procedures in that database are removed as well.
 5. 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

1. Add a New Column

2. Modify an Existing Column
3. Rename a Column or Table
4. Drop a Column
5. Add/Remove Constraints (like PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.)
6. Change the Data Type of a Column
7. 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:
 o Names starting with a certain letter.
o Words containing a specific substring.
o Data that adheres to a particular structure.

Wildcards:

The LIKE operator works in conjunction with wildcard characters:

 % (Percent sign):
o Represents zero or more characters.
o Example: LIKE 'a%' matches any string that starts with "a". LIKE '%xyz%'
matches any string that contains "xyz".
 _ (Underscore):
o Represents a single character.
o 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:

1. Search for names starting with "J":

SELECT * FROM employees

WHERE name LIKE 'J%';

2. 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.

3. 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.

4. 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.

5. Search for names with "a" in the second position:

SELECT * FROM employees

WHERE name LIKE '_a%';

1. 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 10:00:00

 CURDATE(): Returns the current date.

SELECT CURDATE();
-- Output: 2025-03-05

 CURTIME(): Returns the current time.

SELECT CURTIME();
-- Output: 10:00:00

 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:

1. COUNT(): Counts the number of rows or non-NULL values.

2. SUM(): Sums the values of a column.
3. AVG(): Calculates the average of a numeric column.
4. MIN(): Returns the minimum value from a column.
5. 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];

SET OPERATIONS

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

1. UNION
2. UNION ALL
3. INTERSECT (not directly supported in MySQL, but can be simulated)
4. 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.

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);

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;

 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

1. Scalar subqueries (Single-row)

2. 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 );

Correlated Subquery

 A correlated subquery refers to one or more column from the outer query within its own
query block.
 It is evaluated for each row of the outer query and can be used to filter or retrieve data
based on the values of the outer query.
 It can be slower compared to other types of subqueries.

Syntax :-

SELECT column1, column2

FROM outer_table AS o

WHERE column1 OPERATOR (

 SELECT column

FROM inner_table AS i

WHERE [Link] = [Link]

);

Example :-

SELECT name, salary, dept_id

FROM employees e

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE dept_id = e.dept_id

);

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.
 The ANY operator is typically used with the WHERE or HAVING clause to filter rows
based on specific conditions.

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.
 The ALL operator is often used with the WHERE or HAVING clause.

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 ' );

EXISTS Operator

 The EXISTS operator is used to test for the existence of any record in a subquery.
 It returns TRUE if the subquery returns one or more records.
 It is useful when you want to conditionally retrieve data based on the existence of related
records in same or another table.

Syntax:-

SELECT column_name(s)

FROM table_name

WHERE EXISTS (Subquery);

Example :-

SELECT name
FROM customers

WHERE EXISTS ( SELECT * FROM orders WHERE orders. customer_id =

[Link]);

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

 FOREIGN KEY
A foreign key in MySQL is a field (or collection of fields) in one table that uniquely identifies a
row of another table. It is used to establish a relationship between the two tables. The foreign key
in a child table points to the primary key in the parent table.

Foreign Key Constraints in MySQL:

 Referential Integrity: Ensures that the foreign key values in the child table always
match the primary key values in the parent table, or are null if allowed.
 Cascading Actions: MySQL allows you to specify actions to take when the referenced
primary key is updated or deleted (e.g., ON DELETE CASCADE, ON UPDATE CASCADE).

Steps to Create a Foreign Key in MySQL:

1. Create the Parent Table (Table with the Primary Key): The parent table contains the
primary key that will be referenced by the foreign key.
2. Create the Child Table (Table with the Foreign Key): The child table contains the
foreign key that references the primary key in the parent table.

Syntax:
CREATE TABLE child_table (

column1 data_type,

column2 data_type,

...

FOREIGN KEY (foreign_key_column) REFERENCES parent_table

(primary_key_column)

);

Example:

Let’s take an example where we have two tables: departments and employees. The employees table
will reference the departments table through the department_id foreign key.
Step 1: Create the Parent Table (departments)
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary Key
department_name VARCHAR(100),
location VARCHAR(100)
);

 The department_id is the primary key of the departments table.

 This table holds data about the departments, such as department name and location.

Step 2: Create the Child Table (employees) with a Foreign Key

CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary Key
name VARCHAR(100),
salary INT,
department_id INT, -- This will be the foreign key
FOREIGN KEY (department_id) REFERENCES departments(department_id)

ON DELETE CASCADE -- This action ensures that when a department is deleted, all related employees are also
deleted.

 department_id in the employees table is the foreign key that references the department_id in
the departments table.
 ON DELETE CASCADE means that if a department is deleted, all employees in that
department will be deleted automatically.

 Yes, you can add a foreign key to an existing table in MySQL after the table has already
been created. This is done using the ALTER TABLE statement.

 Syntax to Add a Foreign Key:

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (foreign_key_column)
REFERENCES parent_table (primary_key_column)
ON DELETE action

Example:

Let’s assume you already have two tables, customers and orders, and you want to add a foreign key
to the orders table that references the customers table.

1. Existing Tables:
o customers table:

CREATE TABLE customers (

customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);

orders table (without a foreign key initially):

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
customer_id INT
);

Add Foreign Key to the orders Table:

ALTER TABLE orders

ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE CASCADE

Drop the Foreign Key: Once you have the name of the foreign key constraint, you can remove
it using the ALTER TABLE statement.
Syntax:

ALTER TABLE table_name

DROP FOREIGN KEY foreign_key_name;

Example (dropping the fk_customer_id foreign key from the orders table):

ALTER TABLE orders

DROP FOREIGN KEY fk_customer_id;

inserting image :-
• to insert images into table use load_file() command .

syntax:-
insert into table_name values ( load_file());

create table image(image_name varchar(20),file longblob);

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.

stored procedure
  A MySQL stored procedure is a set of SQL statements that are compiled and stored in the
database server. Once created, you can execute it by simply calling its name, often with
parameters. It's like writing a function or a subroutine in a programming language, but for
your database.

Why Use Stored Procedures?

 Performance: Stored procedures are pre-compiled. This means the database server doesn't need
to parse and optimize the SQL statements every time they are executed, leading to faster
execution.
 Reduced Network Traffic: Instead of sending multiple individual SQL statements from the
client to the server, you send a single CALL statement to execute the entire procedure. This
reduces the number of round trips between the client and the server.
 Reusability: Common business logic can be centralized in stored procedures. Once created, they
can be called by multiple applications, users, or even other stored procedures, reducing code
duplication.

Syntax:-
DELIMITER // -- Temporarily change the delimiter
CREATE PROCEDURE procedure_name ( [IN|OUT] parameter_name parameter_datatype
more parameters if needed )
BEGIN
-- SQL statements and logic go here
-- Each statement ends with a semicolon (;)
END //
DELIMITER ;-- Revert the delimiter back to semicolon
Example :-
DELIMITER //
CREATE PROCEDURE GetCustomerDetails ( )
BEGIN
SELECT customer_id, first_name, last_name, email
FROM Customers
WHERE customer_id = 101;
END //
DELIMITER ;
-- Calling the stored procedure
CALL GetCustomerDetails(2);

IN Parameters (Input Parameters)

 Purpose: IN parameters are used to pass values into the stored procedure. The procedure uses
these values for its operations.

Syntax :-

CREATE PROCEDURE name ( IN var_name datatype )

Example :-

-- Stored Procedure with an IN parameter

DELIMITER //

CREATE PROCEDURE GetEmpDetails ( IN emp_id INT )

BEGIN

SELECT id, first_name, last_name, email

FROM employee

WHERE id = emp_id;

END //

DELIMITER ;

-- Calling the stored procedure

CALL GetEmpDetails(2);

OUT Parameters (Output Parameters)

 Purpose: OUT parameters are used to pass values out of the stored procedure back to the caller.
The procedure calculates or retrieves a value and assigns it to an OUT parameter.
 Example :-

-- Stored Procedure with an OUT parameter

DELIMITER //

CREATE PROCEDURE CountCustomers (

OUT total_customers INT -- total_customers is an OUT parameter

BEGIN

SELECT COUNT(*) INTO total_customers FROM Customers;

END //

DELIMITER ;

-- Calling the stored procedure and retrieving the OUT parameter value

CALL CountCustomers(@customer_count); -- Pass a user-defined variable to hold the output

SELECT @customer_count; -- Retrieve the value from the user-defined variable

Dropping a Stored Procedure (DROP PROCEDURE)

To remove a stored procedure from your MySQL database, you use the DROP PROCEDURE
statement.

Syntax:

DROP PROCEDURE [IF EXISTS] procedure_name;

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 images (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
data LONGBLOB
);

Use BLOB, MEDIUMBLOB, or LONGBLOB based on image size.

🔹 3. Insert Image Using LOAD_FILE()

INSERT INTO images (name, data)
VALUES ('[Link]', LOAD_FILE('/var/lib/mysql-files/[Link]'));

Control Flow Functions in MySQL

In MySQL, control flow functions are used to control the execution of SQL statements based
on certain conditions. They help make SQL queries more dynamic and intelligent. Here's a
breakdown of the key control flow functions: IF, IFNULL, NULLIF, and CASE.

🔹 1. IF(condition, true_value, false_value)

 Works like an if-else statement.

 If the condition is true, it returns the true_value; otherwise, it returns the false_value.

Syntax:

IF(condition, value_if_true, value_if_false)

Example:

SELECT IF(10 > 5, 'Yes', 'No') AS Result;

-- Output: Yes

🔹 2. IFNULL(expression, alt_value)

 Returns alt_value if the expression is NULL, otherwise returns the expression itself.
 Useful for handling NULL values gracefully.

Syntax:

IFNULL(expression, replacement_value)

Example:

SELECT IFNULL(NULL, 'Default');

-- Output: Default
SELECT IFNULL('Data', 'Default');
-- Output: Data

🔹 3. NULLIF(expr1, expr2)

 Returns NULL if both expressions are equal, otherwise returns expr1.

 Commonly used to avoid division by zero or for conditional NULL logic.

Syntax:

NULLIF(expr1, expr2)

Example:

SELECT NULLIF(100, 100); -- Output: NULL

SELECT NULLIF(100, 200); -- Output: 100

🔹 4. CASE (like switch-case or multiple if-else)

Used for multiple conditional branches. Can be written in two forms:

Compares an expression with different values.

Syntax:

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END

Example:

SELECT
CASE 'B'
WHEN 'A' THEN 'Apple'
WHEN 'B' THEN 'Ball'
ELSE 'Unknown'
END AS Result;
-- Output: Ball

Checks multiple conditions directly.

Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END

Example:

SELECT

CASE
WHEN marks >= 90 THEN 'A+'
WHEN marks >= 75 THEN 'A'
WHEN marks >= 60 THEN 'B'
ELSE 'Fail'
END AS Grade
FROM students;