Introduction to

MySQL
What is a Database?
A database is a digital system designed for the storage and
arrangement of data. Databases facilitate the efficient management of
data, enabling the simple addition, modification, removal, and access of
information.
What is MySQL?
MySQL is an open-source relational database management system (RDBMS)
that uses Structured Query Language (SQL) to manage data. MySQL is a
Relational Database Management System (RDBMS) software that provides
many features, which are as follows:
• Data Storage: Efficiently stores large amounts of data.
• Data Retrieval: Allows quick and easy access to data.
• Data Manipulation: Supports operations like inserting, updating, and
deleting data.
• Data Security: Offers robust security features to protect data.
• Scalability: Can handle small to large applications with ease.
Types Of Databases
Data Types
Each column in a database table is required to have a name and a data type.
Types of Commands
 Data Definition Language (DDL)
DDL or Data Definition Language actually consists of the SQL commands that
can be used to defining, altering, and deleting database structures such as
tables, indexes, and schemas.
Create database or its objects (table,
CREATE TABLE table_name (column1
CREATE index, function, views, store
data_type, column2 data_type, ...);
procedure, and triggers)

DROP Delete objects from the database DROP TABLE table_name;

ALTER TABLE table_name ADD

ALTER Alter the structure of the database
COLUMN column_name data_type;

Remove all records from a table,

TRUNCATE including all spaces allocated for the TRUNCATE TABLE table_name;
records are removed

Rename an object existing in the RENAME TABLE old_table_name TO

RENAME database new_table_name;
Data Definition Language (DDL)
Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
 Data Manipulation Language (DML)
The SQL commands that deal with the manipulation of data present in the
database belong to DML or Data Manipulation Language and this includes most
of the SQL statements.

INSERT INTO table_name (column1,

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

UPDATE table_name SET column1 =

UPDATE Update existing data within a table value1, column2 = value2 WHERE
condition;

DELETE FROM table_name WHERE

DELETE Delete records from a database table
condition;
Data Manipulation Language (DML)
Example:

INSERT INTO employees (first_name, last_name, department)

VALUES ('Jane', 'Smith', 'HR');
 Data Query Language (DQL)
DQL statements are used for performing queries on the data within schema
objects. The purpose of the DQL Command is to get some schema relation based
on the query passed to it.

It is used to retrieve data from the SELECT column1, column2, ...FROM

SELECT database table_name WHERE condition;
 Data Query Language (DQL)
Example:

SELECT first_name, last_name, hire_date

FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
 Data Control Language (DCL)
DCL (Data Control Language) includes commands such as GRANT and REVOKE
which mainly deal with the rights, permissions, and other controls of the
database system.
Assigns new privileges to a user GRANT privilege_type [(column_list)]
GRANT account, allowing access to specific ON [object_type] object_name TO user
database objects, actions, or functions. [WITH GRANT OPTION];

Removes previously granted privileges REVOKE [GRANT OPTION FOR]

from a user account, taking away their privilege_type [(column_list)] ON
REVOKE access to certain database objects or [object_type] object_name FROM user
actions. [CASCADE];
 Data Control Language (DCL)
Example:

GRANT SELECT, UPDATE ON employees TO user_name;

 Transaction Control Language (TCL)
Transactions group a set of tasks into a single execution unit. Each transaction
begins with a specific task and ends when all the tasks in the group are
successfully completed.

Saves all changes made during the

COMMIT transaction
COMMIT;

Undoes all changes made during the

ROLLBACK transaction
ROLLBACK;

Creates a savepoint within the current

SAVEPOINT transaction
SAVEPOINT savepoint_name;
 SELECT Statements
The SELECT statement is used to fetch data from a database.

Example:
SELECT column1, column2 FROM table_name;

Example:
SELECT name, salary FROM employees;

Using DISTINCT:
SELECT DISTINCT department FROM employees;
 Filtering Data
Filtering involves selecting subsets of data from a database based on specified
conditions or criteria.
1. Using WHERE Clause
2. Using Comparison Operator
3. Using Logical Operators
4. Using ORDER BY Clause
5. Using GROUP BY Clause
 Filtering Data
Using WHERE Clause
The WHERE clause is the fundamental tool for filtering data in MySQL queries. It
allows you to specify conditions that the retrieved rows must meet.

Example:
SELECT * FROM students WHERE age > 20;
 Filtering Data
Using Comparison Operator
Comparison Operators in MySQL provides a range of comparison operators to
refine your WHERE clause conditions. These include "=", "<>", ">", "<", ">=",
"<=", etc.

Example:
SELECT * FROM students WHERE grade = 'B';
 Filtering Data
Using Logical Operators
Logical operators such as AND, OR, and NOT allow us to combine multiple
conditions in a WHERE clause.

Example:
SELECT * FROM students WHERE age < 20 OR grade = 'A';
 Filtering Data
Using ORDER BY CLAUSE
While not strictly a filtering technique, the ORDER BY clause allows us to sort
query results based on specified columns, making it easier to analyze the data.

Example:
SELECT * FROM students ORDER BY age DESC;
 Aggregate Functions
Aggregate functions in MySQL process a set of values and return a single value.
They are typically used with the SELECT statement and can be applied to various
columns within a table.
Common aggregate functions include
1. COUNT()
2. SUM()
3. AVG()
4. MAX()
5. MIN()
 Aggregate Functions
Aggregate functions in MySQL process a set of values and return a single value.
They are typically used with the SELECT statement and can be applied to various
columns within a table.
Common aggregate functions include
1. COUNT() Syntax:
2. SUM() SELECT
3. AVG() AGGREGATE_FUNCTION(column_name)
4. MAX() FROM table_name
5. MIN() WHERE condition;
 Aggregate Functions
COUNT()
The COUNT() function returns the number of rows that match a specified
condition. It can count all rows or only rows that meet certain criteria.

Example:
SELECT COUNT(*) AS total_employees FROM employees;
 Aggregate Functions
SUM()
The SUM() function returns the total sum of a numeric column.

Example:
SELECT SUM(salary) AS total_sales FROM employees;
 Aggregate Functions
AVG()
The AVG() function returns the average value of a numeric column.

Example:
SELECT AVG(salary) AS average_salary FROM employees;
 Aggregate Functions
MAX()
The MAX() function returns the maximum value in a set of values.

Example:
SELECT MAX(salary) AS highest_salary FROM employees;
 Aggregate Functions
MIN()
The MIN() function returns the minimum value in a set of values.

Example:
SELECT MIN(salary) AS lowest_salary FROM employees;
 Grouping Data
In MySQL, the GROUP BY clause is useful operator that is used to group rows that
have the same values, and can be used to push these values into summary
rows, like "find the number of customers in each city" or "calculate the total
number of sales per product category.“

Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
Grouping Data
Before we get into exploring the
GROUP BY Clause , we shall see the
details of the employees table that
we are going to use for our
understanding.
 Grouping Data
Example 1: Grouping by a Single SELECT department, AVG(salary)
Column AS average_salary
Lets say that you have a table named FROM employees
employees with column such as GROUP BY department;
customer_id, order_date, and
total_amount. We want to find the
average salary obtained by each
department( such as IT, HR, Marketing
etc).
 Grouping Data
SELECT department, name,
Example 2: Grouping by Multiple AVG(salary) AS average_salary
Columns FROM employees
Consider a same employees table ,where
GROUP BY department , name;
this time we will find the average_salary of
each employee and retrive the result along
with the department they work in.
The query finds the average_salary and
returns the tables with the department and
the name of the employee along with the
average salary.
 Grouping Data
Example 3: MySQL GROUP BY
Clause with COUNT Function

SELECT department, COUNT(*) AS

employee_count
FROM employees
GROUP BY department;
 Grouping Data
Example 4: MySQL GROUP BY
Clause with SUM Function

SELECT department, SUM(salary) AS

total_salary
FROM employees
GROUP BY department;
 Grouping Data
Example 5: MySQL GROUP BY
Clause with MIN Function

SELECT department, MIN(salary) AS

min_salary
FROM employees
GROUP BY department;
 Grouping Data
Example 6: MySQL GROUP BY
Clause with AVG Function

SELECT department, AVG(salary) AS

average_salary
FROM employees
GROUP BY department;
 Subquery
MYSQL Subquery can be used with an outer query which is used as input to the
outer query. It can be used with SELECT, FROM, and WHERE clauses. MYSQL
subquery is executed first before the execution of the outer query.

Employee Departments
Table Table
 Subquery with WHERE Clause
Let's select employees from department with the department id as 1.
SELECT *
FROM Employee
WHERE department=(SELECT department FROM Departments WHERE
deptid=1);
 Subquery with Comparison Operators
Let's select employees whose salary is less than average salary of all
employees.
SELECT *
FROM Employee
WHERE salary < (SELECT avg(salary) from Employee)
 Subquery with IN and NOT IN operators
IN operator
Let’s select all employees whose department is in departments table.

SELECT *
FROM Employee
WHERE department IN (SELECT department FROM Departments);
 Subquery with IN and NOT IN operators
NOT IN operator
Let’s select all employees whose department is not in department table.

SELECT *
FROM Employee
WHERE department NOT IN (SELECT department FROM Departments);
 Subquery with FROM clause
Let’s select all departments from employee table with nested query.

SELECT department
FROM (SELECT * from Employee) as A;
 Correlated Subquery
Correlated subquery is the one which uses columns from outer table for
execution.
Let’s select EmpId and Name of employee from Employee where salary is less
than average salary and deparment is same as outer table.

SELECT empid , name

FROM Employee AS A
WHERE salary < ( SELECT avg(salary) from Employee AS B WHERE
[Link] = [Link]);
 Subquery with EXISTS and NOT EXISTS
EXISTS

Let’s select employees for which there exists at least 1

department where department of employee is same as
department in departments.

SELECT empid , name

FROM Employee
WHERE EXISTS (SELECT 1 FROM Departments
WHERE [Link] =
[Link]);
 Subquery with EXISTS and NOT EXISTS
NOT EXISTS

Let's select employees for which there does not exist at

least 1 department where department of employee is
same as department in departments.

SELECT empid , name

FROM Employee
WHERE NOT EXISTS (SELECT 1 FROM
Departments WHERE [Link] =
[Link]);
 Joins
A MySQL JOIN is a method to combine data from two or more tables in a
database based on a related column between them.

department_i department_id department_name

employee_id name
d
Types of Joins: 1 Alice 1 1 HR

1. INNER JOIN 2 Bob 2 2 Engineering

3 Charlie 1 3 Marketing
2. LEFT OUTER JOIN
4 David 3 4 Finance

3. RIGHT OUTER JOIN 5 Eve NULL

Departments
4. FULL OUTER JOIN Table
Employees
5. CROSS JOIN
Table
6. SELF JOIN
INNER JOIN
It Returns records that have matching
values in both tables.

SELECT [Link], name department_name

departments.department_name Alice HR

FROM employees
Bob Engineering

INNER JOIN departments

Charlie HR
ON employees.department_id =
David Marketing
departments.department_id;
LEFT OUTER JOIN
It returns all records from the Left table and
matched records from the Right table. If there is
no match, then NULL values are returned for Right
table columns. name department_name

SELECT [Link], Alice HR

departments.department_name Bob Engineering

FROM employees Charlie HR

LEFT JOIN departments David Marketing

ON employees.department_id = Eve NULL

departments.department_id;
RIGHT OUTER JOIN
It Returns all the rows from the right table and the
matched rows from the left table. NULL values will
be returned for columns from the left table when
there are no matches. department_nam
name
e
SELECT [Link], Alice HR

departments.department_name Bob Engineering

FROM employees Charlie HR

RIGHT JOIN departments David Marketing

NULL Finance
ON employees.department_id =
departments.department_id;
FULL OUTER JOIN
It Returns all records when there is a match in
either the left or the right table. In case of no
match, NULL values are returned for columns that
have no match in either table. name department_name

SELECT [Link], Alice HR

departments.department_name
Bob Engineering
FROM employees
Charlie HR
LEFT JOIN departments
ON employees.department_id = David Marketing

departments.department_id Eve NULL

UNION
NULL Finance
SELECT [Link],
departments.department_name
FROM employees
RIGHT JOIN departments
CROSS JOIN
It Returns the Cartesian product of two tables. name department_name
Alice HR
Matches every row of one table with every row of Alice Engineering

Alice Marketing

another table. Alice Finance

Bob HR

Bob Engineering
SELECT [Link], Bob Marketing

Bob Finance

departments.department_name Charlie HR

Charlie Engineering

FROM employees Charlie Marketing

Charlie Finance

CROSS JOIN departments; David HR

David Engineering

David Marketing

David Finance

Eve HR

Eve Engineering

Eve Marketing

Eve Finance
SELF JOIN
It Returns the Cartesian product of two tables.
Matches every row of one table with every row of
another table.
employee manager

SELECT [Link] AS employee,

Alice Charlie

[Link] AS manager
Bob Charlie
FROM employees a, employees b
WHERE a.manager_id = Charlie David

b.employee_id;
 UNION Operator
The UNION operator in MySQL is used to combine the results of two or more
SELECT statements into a single result set.

Syntax:
SELECT column1, column2, ...
FROM table1
WHERE condition
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition;
UNION Operator
Example:
SELECT name, 'Student' AS type
FROM students
UNION
SELECT name, 'Teacher' AS type
FROM teachers;
Date Functions
Function Description Example
CURDATE()/
CURRENT_DATE()/ Returns the current date. SELECT CURDATE() ;
CURRENT_DATE
Extracts the date part of a SELECT DATE(‘2020-12-31
DATE()
date or date-time expression. 01:02:03’) ;
Returns the month from the SELECT MONTH(‘2020-12-31’)
MONTH()
date passed. ;
YEAR() Returns the year SELECT YEAR(‘2020-12-31’) ;
Returns the time at which the
NOW() SELECT NOW() ;
function executes.
SELECT NOW(), SLEEP(2),
Returns the current date and NOW() ;
SYSDATE()
time. or SELECT SYSDATE(),
SLEEP(2), SYSDATE() ;
Conditional Statements
IF( ) Function
The MySQL IF() function is a control flow function that returns different values
based on the result of a condition.

Syntax:
IF(condition, true_value, false_value)

Example:
SELECT OrderID, Quantity, IF(Quantity>10, "MORE", "LESS")
FROM OrderDetails;
Conditional Statements
CASE Function
The CASE Function in MySQL allows using conditional logic within the queries. It
evaluates the conditions and returns a value when a condition is met.

Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Conditional Statements
CASE Function Example:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END
FROM OrderDetails;
Window Functions
• Window functions in SQL perform calculations across a set of table rows
related to the current row.
• Unlike aggregate functions which return a single value for a group of rows,
window functions return a result for each row in the result set.

Syntax:
window_function_name([expression]) OVER (
[PARTITION BY expression]
[ORDER BY expression [ASC|DESC]]
[ROWS or RANGE frame_clause]
)
Window Functions
LEAD() and LAG()
LEAD() and LAG() functions allow you to access subsequent or previous
rows' data without the need for self-joins
Example: employee_id salary next_salary previous_salary
101 50000 60000 NULL
SELECT 102 60000 70000 50000

employee_id,
103 70000 80000 60000

104 80000 NULL 70000

salary,
LEAD(salary, 1) OVER (ORDER BY employee_id) AS next_salary,
LAG(salary, 1) OVER (ORDER BY employee_id) AS previous_salary
FROM employees;
Window Functions
ROW_NUMBER()
This function is used to assigns a unique sequential integer to rows
within a partition
department_i
employee_id salary row_num
d
Example:
101 1 90000 1

SELECT
102 1 85000 2
employee_id,
department_id, 103 2 95000 1

salary, 104 2 70000 2

ROW_NUMBER() OVER (PARTITION BY

department_id ORDER BY salary DESC) AS row_num
FROM employees;
Window Functions
RANK() and DENSE_RANK()
The use of this function id to leave gaps in the ranking when they are
ties and also assigns a ranking within a partition
Example: employee_id department_id salary rank dense_rank

101 1 90000 1 1
SELECT
102 1 85000 2 2
employee_id,
103 1 85000 2 2

department_id, 104 1 75000 4 3

salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS
dense_rank
FROM employees;
Thank you