Intro to MySQL

What is Data:
Data is information such as facts and numbers used to analyze something or make decisions. Data can be
represented in form of graphs, tables, text.

What is Dataset:
A dataset is collection of data organized or structured in tabular format, where every column represents
features or characteristics of an object, item or person in consideration and the row represents record
pertaining to the features or characteristics present in columns. A column is called as ‘Field’ and Row is
called as ‘Tuple’.

What is Database:
Database is a collection of Dataset where each Dataset or Table is inter-linked, connected or is in a
relationship with other tables which are part of the Database, organized in such a manner which represents
a cohesive system. A database is also called as Schema.

What is RDBMS(Relational Database Management System)

RDBMS is a Database Management System/Software which helps user to read, edit, manipulate and scale
data with primary objective to build correlation, power analysis and support Data-Driven Workflows. It also
helps data validation, access management and security.
Why Relational Database
• Imagine we maintain a single data set or table for all the information,
what are the advantages/disadvantages.
• Advantage will be all information will be at single location.
• Disadvantages would be redundancy and inconsistent dependency.
We will face greater challenge in INSERTING, UPDATING AND
DELETING the records in a single table.
How?
What is normalization?
• Normalization is the process of eliminating redundancy and
inconsistent dependency, by dividing the data into several tables
establishing relationship among these tables, it helps to make database
more flexible in terms of INSERTING, UPDATING and DELETING the
records in the tables.
• Now the data is divided into different tables, each table holds data of a
particular entity i.e. any item, person or place. This separate tables of
each entity is called as Dimension Table.
• and when these dimension tables gets into or participates into business
there participation forms another table called as Fact Table or
Transaction Table.
• Hence whenever we make any updates, inserts we do not make it directly
on the Fact Table or Transaction Table, so it does not impact the
performance and data integrity, and if we delete data from Fact
Table or Transaction table it does not impact the dimension table.
• Such interaction between different tables is called as Relationship.
Relational Model
• Such Relationship among the table forms the business transaction is
called as Relational Model.
• This Relation is called as Parent-Child Relationship.
• And the system in which such Relational Model exist among the
databases or datasets is called as Relational Database Management
System i.e. RDBMS.
• How this Relation among different table is maintained?
• It is maintained with the help of common columns among this tables,
they are Primary Key and Foreign Key.
 Common Columns

order id customer_id status created at

customer_id name email dob pincode
101 1 Delivered 11-06-2023
1 tom tom@gmail.com 15-04-2004 4875613 102 1 Not Delivered 12-09-2023

Customer Table Orders Table

Primary Key
Foreign Key
Primary Key
• What is Primary Key?
• The primary key in SQL is a single, or a group of fields or columns that can
uniquely identify a row in a table.
• It Primary Key constraint enforces uniqueness by not accepting any duplicate
values.
• It is a default index key.
• A table can only take one primary key.
• A primary key column cannot accept null values.
• A single-column primary key is a simple one. The one consisting of multiple
columns is called a composite primary key.
• It can be created at a column or table level, using CREATE TABLE or ALTER TABLE
statements.
Foreign Key
• What is Foreign Key
• A foreign key is a column or group of columns in a relational database table
that provides a link between data in two tables.
• A foreign key constraint is used to prevent actions that would destroy links
between tables.
• A foreign key can contain duplicate records
• A foreign key can be NULL.
Types of Relational Models
• one-to-one
• one-to-many
• many-to-many.
• Self-referencing.
CREATE Command
• The CREATE TABLE statement is used to create a new table in a
database.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
 Data Types
• String Data Type
• CHAR(size) : A FIXED length string, can contain letters, numbers, and special characters. The size
parameter specifies the column length in characters - can be from 0 to 255.
• VARCHAR(size) : A VARIABLE length string (can contain letters, numbers, and special characters). The
size parameter specifies the maximum column length in characters - can be from 0 to 65535.
• Number Data Type
• INT(size) : A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is
from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
• DECIMAL(size, d) : An exact fixed-point number. The total number of digits is specified in size. The
number of digits after the decimal point is specified in the d parameter. The maximum number for
size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d
is 0.
• Date Data Type
• A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31’.
• DATETIME() : A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is
from '1000-01-01 00:00:00' to '9999-12-31 23:59:59’.
• TIMESTAMP(fsp) : A timestamp. TIMESTAMP values are stored as the number of seconds since the
Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss.
DROP Command
• The DROP TABLE statement is used to drop an existing table in a
database.

• DROP TABLE table_name;

ALTER Command
• The ALTER TABLE statement is used to add, delete, or modify columns in
an existing table.
• The ALTER TABLE statement is also used to add and drop various
constraints on existing table.
• ALTER TABLE SYNTAX
• ALTER TABLE table_name
ADD column_name datatype;
• ALTER TABLE table_name
DROP COLUMN column_name;
• ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Truncate Command
• The SQL TRUNCATE TABLE command is used to delete all the records
from an existing table

• TRUNCATE TABLE table_name;

Rename Command
• Rename command is use to rename table name
• Syntax
• RENAME TABLE old_table_name TO new_table_name;
DDL Commands
1.CREATE Command
2.DROP Command
3.ALTER Command
4.TRUNCATE Command
5.RENAME Command
Constraints on Table
• 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 action, the action is
aborted.
• Types of Constraints.
• NOT NULL - Ensures that a column cannot have a NULL value.
• CHECK - Ensures that the values in a column satisfies a specific condition.
• DEFAULT - Sets a default value for a column if no value is specified.
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies
each row in a table.
• UNIQUE - Ensures that all values in a column are different, NULL value is
allowed.
• FOREIGN KEY - Prevents actions that would destroy links between tables.
Parent Table & Child Table
• How to identify which is the Parent table and which is the child table?

• If a table contains a Primary Key then it is a Parent Table

• If a table contains a Foreign Key which refers the primary key of

parent table then it is a Child Table.
CASCADE
• The purpose of CASCADE is to simultaneously delete or update an
entry from both the child and parent table.
• CASCADE helps to maintain consistent data across the relational data
model.
• ON DELETE CASCADE & ON UPDATE CASCADE.
Select
• ‘SELECT’ is used to retrieve data from the table, it is also called as DQL
Language Command, Whether it’s a entire table or particular
column/columns it can be retrieved from the table with the help of
‘SELECT’ command
• SELECT * FROM TABLE_NAME
• SELECT Column_1, Column_2, Column_3 FROM TABLE
LIMIT
• The LIMIT clause is used to specify the number of records to return.
• The LIMIT clause is useful on large tables with thousands of records.
Returning a large number of records can impact performance.
• E.g.
• SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Joins
• A JOIN clause is used to combine rows from two or more tables, based
on a related column between them.
• Inner Join
• Left Join
• Right Join
• Full Outer Join
• Cross Join
• Self Join
DISTINCT
• The SELECT DISTINCT statement is used to return only distinct
(different) values.

• Inside a table, a column often contains many duplicate values; and

sometimes you only want to list the different (distinct) values.
• SELECT DISTINCT column1, column2, ...
FROM table_name;
AND
• The AND operator displays a record if all the conditions separated by
AND are TRUE.
• SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3
...;
• At a given moment AND operator cannot be used
on same column more than once.
OR
• The OR operator displays a record if any of the conditions separated
by OR is TRUE.
• SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ..
.;
• OR operator can be used more than once on same
column.
IN
• The IN operator allows you to specify multiple values in a WHERE
clause.
• The IN operator is a shorthand for multiple OR conditions.
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
NOT
• The NOT operator displays a record if the condition(s) is NOT TRUE.
• SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
BETWEEN
• The BETWEEN operator selects values within a given range. The
values can be numbers, text, or dates.
• The BETWEEN operator is inclusive: begin and end values are
included.
• SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Stages of SQL Execution
1.FROM/JOIN
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.ORDER BY
7.LIMIT/OFFSET
Exists
• The EXISTS operator is used to test for the existence of any record in a
subquery.
• The EXISTS operator returns TRUE if the subquery returns one or
more records.
• SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condi
tion);
What is a NULL value?
• A field with a NULL value is a field with no value.
• If a field in a table is optional, it is possible to insert a new record or
update a record without adding a value to this field. Then, the field
will be saved with a NULL value.
• Note: A NULL value is different from a zero value or a field that
contains spaces. A field with a NULL value is one that has been left
blank during record creation!
IS NULL
• How to Test for NULL Values?
• It is not possible to test for NULL values with comparison operators,
such as =, <, or <>.
• We will have to use the IS NULL and IS NOT NULL operators instead.
• SELECT column_names
FROM table_name
WHERE column_name IS NULL;
Order By
• The ORDER BY keyword is used to sort the result-set in ascending or
descending order.
• The ORDER BY keyword sorts the records in ascending order by
default. To sort the records in descending order, use the DESC
keyword.
• SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
IF NULL
• The IFNULL() function returns a specified value if the expression is
NULL.
• If the expression is NOT NULL, this function returns the expression.
• SELECT IFNULL(NULL, "Welcome");
NULL IF
• The NULLIF() function compares two expressions and returns NULL if
they are equal. Otherwise, the first expression is returned.

• Syntax
• NULLIF(expr1, expr2)
Coalesce Function
• The COALESCE() function in MySQL is used to return the first non-null
value in a specified series of expressions. If this function evaluates all
values of the list are null, or it does not find any non-null value, then it
returns NULL.
• The following are the syntax of using COALESCE() function in MySQL:
• COALESCE(value1, value2, value3....., valueN);
Case Statement
• The CASE expression goes through conditions and returns a value
when the first condition is met (like an if-then-else statement). So,
once a condition is true, it will stop reading and return the result. If no
conditions are true, it returns the value in the ELSE clause.
• CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
Group By
• The GROUP BY statement groups rows that have the same values into
summary rows, like "find the number of customers in each country".
• The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one
or more columns.
• SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Having Clause
• The HAVING clause is used to filter aggregate function values.
• SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Date Functions
• adddate(date,INTERVAL)
• curdate()
• datediff(curdate(),ord_date) as DATE_DIFF
• extract(month/year/day from date())
• date_format(date(),’%W/%M/%Y’)
• Year()
• Quarter()
• Month()
• DATE_SUB(DATE, INTERVAL value interval)
• PERIOD_DIFF()
DML Statement
• INSERT
• UPDATE
• DELETE
Types of Sub-Query
• Single Row Sub-Query
• Multiple Row Sub-Query
• Correlated Sub-Query
User Defined Function
• DELIMITER $$
• CREATE FUNCTION FUNCTION_NAME(VAR DATA_TYPE) RETURNS INT
• DETERMINISTIC/NON DETERMINISTIC
• BEGIN
• DECLARE VAR INT;
• SET VAR
• RETURN DECLARED_VAR;
• END
Stored Procedure
• What is a Stored Procedure?
• A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again.

• So if you have an SQL query that you write over and over again, save it
as a stored procedure, and then just call it to execute it.

• You can also pass parameters to a stored procedure, so that the stored
procedure can act based on the parameter value(s) that is passed.
 Stored Procedure Syntax
• CREATE PROCEDURE procedure_name (IN|OUT parameter DATA_TYPE)
BEGIN
DECLARATION_SECTION
EXECUTABLE_SECTION
END
Window Functions
• ROW_NUMBER
• RANK
• DENSE_RANK
• LEAD
• LAG
Union & Union All
• Union & Union All combines records from two select statement
• Union maintains distinct records
• Union All allows duplicate values.
Index
• Indexes are used to retrieve data from the database more quickly
than otherwise. The users cannot see the indexes, they are just used
to speed up searches/queries.
• Note: Updating a table with indexes takes more time than updating a
table without (because the indexes also need an update). So, only
create indexes on columns that will be frequently searched against.
Intersect
• The INTERSECT operator returns the distinct (common) elements in
two sets or common records from two or more tables.
• SELECT column_lists FROM table_name WHERE condition
• INTERSECT
• SELECT column_lists FROM table_name WHERE condition;
Views
• In SQL, a view is a virtual table based on the result-set of an SQL
statement.
• A view contains rows and columns, just like a real table. The fields in a
view are fields from one or more real tables in the database.
• You can add SQL statements and functions to a view and present the
data as if the data were coming from one single table.
• A view is created with the CREATE VIEW statement.

• CREATE VIEW view_name AS

• SELECT column1, column2, ...
• FROM table_name
• WHERE condition;
Triggers
• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete
Exception Handling
• When an error occurs inside a stored procedure, it is
important to handle it appropriately, such as continuing
or exiting the current code block’s execution, and
issuing a meaningful error message.
• Let’s take an Example…..
CREATE TABLE….
• CREATE TABLE SupplierProducts (
• supplierId INT,
• productId INT,
• PRIMARY KEY (supplierId , productId)
• );
CREATE PROCEDURE…
• CREATE PROCEDURE InsertSupplierProduct(
• IN inSupplierId INT,
• IN inProductId INT
•)
Exception Condition
BEGIN
DECLARE EXIT HANDLER FOR 1062
BEGIN
SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
END;

INSERT INTO SupplierProducts(supplierId,productId)

VALUES(inSupplierId,inProductId);

SELECT COUNT(*)
FROM SupplierProducts
WHERE supplierId = inSupplierId;

END$$
Data Partitioning
• Partitioning in MySQL is used to split or partition the rows of a table
into separate tables in different locations, but still, it is treated as a
single table.
1.CREATE TABLE Sales ( cust_id INT NOT NULL, name VARC
HAR(40),
2.store_id VARCHAR(20) NOT NULL, bill_no INT NOT NULL,
3.bill_date DATE PRIMARY KEY NOT NULL, amount DECIMAL(
8,2) NOT NULL)
4.PARTITION BY RANGE (year(bill_date))(
5.PARTITION p0 VALUES LESS THAN (2016),
6.PARTITION p1 VALUES LESS THAN (2017),
7.PARTITION p2 VALUES LESS THAN (2018),
8.PARTITION p3 VALUES LESS THAN (2020));
1.INSERT INTO Sales VALUES
2.(1, 'Mike', 'S001', 101, '2015-01-02', 125.56),
3.(2, 'Robert', 'S003', 103, '2015-01-25', 476.50),
4.(3, 'Peter', 'S012', 122, '2016-02-15', 335.00),
5.(4, 'Joseph', 'S345', 121, '2016-03-26', 787.00),
6.(5, 'Harry', 'S234', 132, '2017-04-19', 678.00),
7.(6, 'Stephen', 'S743', 111, '2017-05-31', 864.00),
8.(7, 'Jacson', 'S234', 115, '2018-06-11', 762.00),
9.(8, 'Smith', 'S012', 125, '2019-07-24', 300.00),
10.(9, 'Adam', 'S456', 119, '2019-08-02', 492.20);
1.SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS
2.FROM INFORMATION_SCHEMA.PARTITIONS
3.WHERE TABLE_SCHEMA = 'myemployeedb' AND TABLE_N
AME = 'Sales';
Transaction Control Language(TCL)
• A single unit of work in a database is formed after the consecutive
execution of commands is known as a transaction.
• There are certain commands present in SQL known as TCL commands
that help the user manage the transactions that take place in a
database.
• COMMIT. ROLLBACK and SAVEPOINT are the most commonly used
TCL commands in SQL.
CREATE TABLE
• SET AUTOCOMMIT = 0;
• CREATE TABLE t_school(
• ID INT
• , School_Name VARCHAR(40)
• , Number_Of_Students INT
• , Number_Of_Teachers INT
• , Number_Of_Classrooms INT
• , EmailID VARCHAR(40));
INSERT INTO t_school(ID, School_Name, Number_Of_Students,
Number_Of_Teachers, Number_Of_Classrooms, EmailID)
VALUES(1, "Boys Town Public School", 1000, 80, 12, "btps15@g
mail.com"),
(2, "Guru Govind Singh Public School", 800, 35, 15, "ggps25@g
mail.com"),
(3, "Delhi Public School", 1200, 30, 10, "dps101@gmail.com"),
(4, "Ashoka Universal School", 1110, 40, 40, "aus17@gmail.com
"),
(5, "Calibers English Medium School", 9000, 31, 50, "cems@gm
ail.com");
• START TRANSACTION;
• SAVEPOINT Insertion;
• UPDATE t_school SET Number_Of_Students = 9050 W
HERE ID = 5;
• SELECT *FROM t_school;
• SAVEPOINT Updation;
• ROLLBACK TO Insertion;