Database and SQL

Introduction and the SQL Language

SQL stands for Structural Query Language, and SQL is used for storing, manipulation, and retrieving data
from the database.

A database is a well-ordered collection of data. A database is an electronic system that permits data to
be easily manipulated, accessed, and updated, or an organization uses a database as a method of
managing, storing, and retrieving information. Modern databases are handled using a database
management system (DBMS).

Relational Databases are used to store data in tables (rows and columns). Some common relational
database management systems that use SQL are Oracle, Sybase, Microsoft SQL Server, Access,
Ingres, etc.

Row

Column

A Relational Database contains tables that store the data that is related in some way. SQL is the query
language that allows retrieval and manipulation of table data in the relational database. The
database below has two tables: one with data on Users and another with data on Products

To understand SQL, it is important to understand its keywords, data types and list of operators

An SQL data type refers to the type of data which can be stored in a column of a database table. For
example integer data, character data, monetary data, date and time data, binary strings, and so
on.

While creating a database table in a database, we need to specify following two attributes to define a table
column:

• Name of the column

 • Data type of the column

A database table's column defines the data, whereas database table rows populate data into the table.

For example, if you want to store student name in a column then you should give column name something
like student_name and it's data type will be char(50) which means it can store a string of characters up to 50
characters.

The data type provide guidelines for SQL to understand what type of data is expected inside each column, and
hence, prevents the user from entering any unexpected or invalid data in a column. For example, if we
want a column to store only integer values, we can specify its data types as INT. SQL will show an error if any
other value apart from an integer is inserted into that particular column. Different RDBMS supports different
type of data types to define their tables.

There are three main types of SQL data types available in any RDBMS. They are listed below −

• String
• Numeric
• Date and Time

Defining a Data Type

SQL Data types are defined during the creation of a table in a database. While creating a table, it is required
to specify its respective data type and size along with the name of the column.

Following is the syntax to specify a data type in MySQL −

CREATE TABLE table_name(column1 datatype, column2 datatype....)

Let us look at an example query below to understand better.

CREATE TABLE Customers (Name VARCHAR (25), Age INT);

In the above SQL query, we are creating a table Customers. And since the Name column only stores string
values, we are specifying its data type as "VARCHAR". The VARCHAR data type represents string values in
SQL. Similarly, we define the Age column with the integer data type, "INT".

When we assign the appropriate data type to a column, we can make efficient use of memory by
allocating only the amount of system memory required for the data in the relevant column.

MySQL Data Types

There are three main data types available in MySQL Database: string, numeric, and date and time.
Following section lists down all the data types available in MySQL Version 8.0

MySQL - String Data Types

Data type Description

CHAR(size) A fixed length string which can have letters, numbers, and special characters.
The size parameter specifies the column length in characters which can vary from
from 0 to 255. Default size is 1
VARCHAR(size) A variable length string which can contain letters, numbers, and special characters.
The size parameter specifies the maximum string length in characters which can
vary from 0 to 65535.
BINARY(size) This is equal to CHAR(), but stores binary byte strings. The size parameter
specifies the column length in bytes. Default size is 1
VARBINARY(size) This is equal to VARCHAR(), but stores binary byte strings. The size parameter
specifies the maximum column length in bytes.
TINYTEXT This holds a string with a maximum length of 255 characters
 TEXT(size) This holds a string with a maximum length of 65,535 bytes
LONGTEXT This holds a string with a maximum length of 4,294,967,295 characters
TINYBLOB This represents a small BLOBs (Binary Large Objects). Max length is 255 bytes
BLOB(size) This represents a BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data
MEDIUMTEXT This holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB This represents a medium BLOBs (Binary Large Objects). Holds up to 16,777,215
bytes of data
LONGBLOB This represents a large BLOBs (Binary Large Objects). Holds up to 4,294,967,295
bytes of data
ENUM(val1, val2, A string object that can contain only one value, chosen from a list of possible
val3, ...) values. You can list up to 65535 values in an ENUM list. If a value is inserted that
is not in the list, a blank value will be inserted. The values are sorted in the order
you enter them
SET(val1, val2, val3, A string object that can have 0 or more values, chosen from a list of possible
...) values. You can list up to 64 values in a SET list
MySQL - Numeric Data Types

Data type Description

INT A normal-sized integer that can be signed or unsigned. If signed, the allowable range
is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to
4294967295. You can specify a width of up to 11 digits.
TINYINT A very small integer that can be signed or unsigned. If signed, the allowable range is
from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a
width of up to 4 digits.
SMALLINT A small integer that can be signed or unsigned. If signed, the allowable range is from
-32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can
specify a width of up to 5 digits.
MEDIUMINT A medium-sized integer that can be signed or unsigned. If signed, the allowable range
is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215.
You can specify a width of up to 9 digits.
BIGINT A large integer that can be signed or unsigned. If signed, the allowable range is from -
9223372036854775808 to 9223372036854775807. If unsigned, the allowable range
is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.
FLOAT(M,D) A floating-point number that cannot be unsigned. You can define the display length
(M) and the number of decimals (D). This is not required and will default to 10,2,
where 2 is the number of decimals and 10 is the total number of digits (including
decimals). Decimal precision can go to 24 places for a FLOAT.
DOUBLE(M,D) A double precision floating-point number that cannot be unsigned. You can define the
display length (M) and the number of decimals (D). This is not required and will
default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53
places for a DOUBLE. REAL is a synonym for DOUBLE.
DECIMAL(M,D) An unpacked floating-point number that cannot be unsigned. In the unpacked
decimals, each decimal corresponds to one byte. Defining the display length (M) and
the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.
MySQL - Date and Time Data Types

Data type Description

DATE A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For
example, December 30th, 1973 would be stored as 1973-12-30.
DATETIME A date and time combination in YYYY-MM-DD HH:MM:SS format, between
1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the
afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00.
TIMESTAMP A timestamp between midnight, January 1st, 1970 and sometime in 2037. This
looks like the previous DATETIME format, only without the hyphens between
numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as
19731230153000 ( YYYYMMDDHHMMSS ).
 TIME Stores the time in a HH:MM:SS format.
YEAR(M) Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for
example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the
length is specified as 4, then YEAR can be 1901 to 2155. The default length is
4.

What is an Operator in SQL?

An SQL operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to
perform operation(s), such as comparisons and arithmetic operations. These Operators are used to
specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

An SQL operator can be either a unary or binary operator. A unary operator (example unary + or unary - )
uses only one operand to perform the unary operation, whereas the binary operator (example + or - etc) uses
two operands to perform the binary operation.

SQL supports following types of operators:

• Arithmetic operators

• Comparison operators

• Logical operators

• Operators used to negate conditions

SQL Arithmetic Operators

SQL Arithmetic Operators are used to perform mathematical operations on the numerical values. SQL
provides following operators to perform mathematical operations.

Operator Description Example

+ Addition 10 + 20 = 30

- Subtraction 20 - 30 = -10
* Multiplication 10 * 20 = 200
/ Division 20 / 10 = 2
% Modulus 5%2=1
SQL Comparison Operators

SQL Comparison Operators test whether two given expressions are the same or not. These operators are used
in SQL conditional statements while comparing one expression with another and they return a Boolean value
which can be either TRUE or FALSE. The result of an SQL comparison operation can be UNKNOWN when one
or another operand has it's value as NULL.

Operator Description Example

= Equal to 5 = 5 returns TRUE

!= Not equal 5 != 6 returns TRUE

<> Not equal 5 <> 4 returns TRUE
> Greater than 4 > 5 returns FALSE
< Less than 4 < 5 returns TRUE
>= Greater than or equal to 4 >= 5 returns FALSE
<= Less than or equal to 4 <= 5 returns TRUE
!< Not less than 4 !< 5 returns FALSE
 !> Not greater than 4 !> 5 returns TRUE
SQL Logical Operators

SQL Logical Operators are very similar to comparison operators and they test for the truth of some given
condition. These operators return a Boolean value which can be either a TRUE or FALSE. The result of an
SQL logical operation can be UNKNOWN when one or another operand has its value as NULL.

Operator Description

ALL TRUE if all of a set of comparisons are TRUE.

AND TRUE if all the conditions separated by AND are TRUE.

ANY TRUE if any one of a set of comparisons are TRUE.
BETWEEN TRUE if the operand lies within the range of comparisons.
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of expressions.
LIKE TRUE if the operand matches a pattern specially with wildcard.
NOT Reverses the value of any other Boolean operator.
OR TRUE if any of the conditions separated by OR is TRUE
IS NULL TRUE if the expression value is NULL.
SOME TRUE if some of a set of comparisons are TRUE.
UNIQUE The UNIQUE operator searches every row of a specified table for
uniqueness (no duplicates).
SQL Operator Precedence

The operator precedence in SQL is the sequence in which the SQL evaluates the different operators in a
given expression. The operators with higher precedence get evaluated first.

Following table lists all SQL operators as per their precedence. The operators with the highest precedence are
at the top and the operators with the lowest precedence are at the bottom.

Operator Operation

+, - identity, negation
*, / multiplication, division
+, - addition, subtraction
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, Comparison
IN
NOT logical negation
AND conjunction
OR inclusion
Example

Consider the following SQL statement:

SELECT 20 - 3 * 5;

This will result in the following. Here multiplication operator gets evaluated first and then subtraction happens.

SQL Keywords

SQL keywords are reserved words that have special meanings in the SQL language. These keywords are used
to perform various operations on a database, such as creating, modifying, and querying data

Keyword Description
ADD Adds a column in an existing table

ADD CONSTRAINT Adds a constraint after a table is already created

ALL Returns true if all of the subquery values meet the condition

ALTER Adds, deletes, or modifies columns in a table, or changes the data type of
a column in a table

ALTER COLUMN Changes the data type of a column in a table

ALTER TABLE Adds, deletes, or modifies columns in a table

AND Only includes rows where both conditions is true

ANY Returns true if any of the subquery values meet the condition

AS Renames a column or table with an alias

ASC Sorts the result set in ascending order

BACKUP DATABASE Creates a back up of an existing database

BETWEEN Selects values within a given range

CASE Creates different outputs based on conditions

CHECK A constraint that limits the value that can be placed in a column

COLUMN Changes the data type of a column or deletes a column in a table

CONSTRAINT Adds or deletes a constraint

CREATE Creates a database, index, view, table, or procedure

CREATE DATABASE Creates a new SQL database

CREATE INDEX Creates an index on a table (allows duplicate values)

CREATE OR REPLACE VIEW Updates a view

CREATE TABLE Creates a new table in the database

CREATE PROCEDURE Creates a stored procedure

CREATE UNIQUE INDEX Creates a unique index on a table (no duplicate values)

CREATE VIEW Creates a view based on the result set of a SELECT statement

DATABASE Creates or deletes an SQL database

DEFAULT A constraint that provides a default value for a column

DELETE Deletes rows from a table

DESC Sorts the result set in descending order

DISTINCT Selects only distinct (different) values

DROP Deletes a column, constraint, database, index, table, or view

DROP COLUMN Deletes a column in a table

DROP CONSTRAINT Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint

DROP DATABASE Deletes an existing SQL database

DROP DEFAULT Deletes a DEFAULT constraint

DROP INDEX Deletes an index in a table

DROP TABLE Deletes an existing table in the database

DROP VIEW Deletes a view

EXEC Executes a stored procedure

EXISTS Tests for the existence of any record in a subquery

FOREIGN KEY A constraint that is a key used to link two tables together

FROM Specifies which table to select or delete data from

FULL OUTER JOIN Returns all rows when there is a match in either left table or right table

GROUP BY Groups the result set (used with aggregate functions: COUNT, MAX, MIN,
SUM, AVG)

HAVING Used instead of WHERE with aggregate functions

IN Allows you to specify multiple values in a WHERE clause

INDEX Creates or deletes an index in a table

INNER JOIN Returns rows that have matching values in both tables

INSERT INTO Inserts new rows in a table

INSERT INTO SELECT Copies data from one table into another table

IS NULL Tests for empty values

IS NOT NULL Tests for non-empty values

JOIN Joins tables

LEFT JOIN Returns all rows from the left table, and the matching rows from the right
table

LIKE Searches for a specified pattern in a column

LIMIT Specifies the number of records to return in the result set

NOT Only includes rows where a condition is not true

NOT NULL A constraint that enforces a column to not accept NULL values

OR Includes rows where either condition is true

ORDER BY Sorts the result set in ascending or descending order

OUTER JOIN Returns all rows when there is a match in either left table or right table

PRIMARY KEY A constraint that uniquely identifies each record in a database table
 PROCEDURE A stored procedure

RIGHT JOIN Returns all rows from the right table, and the matching rows from the left
table

ROWNUM Specifies the number of records to return in the result set

SELECT Selects data from a database

SELECT DISTINCT Selects only distinct (different) values

SELECT INTO Copies data from one table into a new table

SELECT TOP Specifies the number of records to return in the result set

SET Specifies which columns and values that should be updated in a table

TABLE Creates a table, or adds, deletes, or modifies columns in a table, or deletes

a table or data inside a table

TOP Specifies the number of records to return in the result set

TRUNCATE TABLE Deletes the data inside a table, but not the table itself

UNION Combines the result set of two or more SELECT statements (only distinct
values)

UNION ALL Combines the result set of two or more SELECT statements (allows
duplicate values)

UNIQUE A constraint that ensures that all values in a column are unique

UPDATE Updates existing rows in a table

VALUES Specifies the values of an INSERT INTO statement

VIEW Creates, updates, or deletes a view

WHERE Filters a result set to include only records that fulfill a specified condition

2. How to run SQL Query on the local system

To run the SQL query on the local system, we need to install the MYSQL community server on the
system. We have given step by step installation process below.

Open Google and type Download MySQL and Click on MySQL Community Downloads

Click on MySQL Community Server

Click on window Button and search for Open MySQL Command.

Open MySQL Command-line Client and enter the password.

After entering the password, your MySQL client will get connected with MySQL.

There are many in-build Databases in MySQL; we can type show database.

we can use any of the above databases by just typing use database_name
3. SQL QUERY

A database most often contains tables. Some name identifies each table. The table includes
records(rows) with Data. To access those records, we need SQL Syntax. Most of the action you need to
perform Database by using the SQL Statement.

Note: SQL keywords are not case-sensitive (e.g., select as SELECT) o The
syntax of the language describes the language element. o SQL syntax is
somewhat like simple English sentences. o Keywords include SELECT,
UPDATE, WHERE, ORDER BY ETC.
Four fundamental operations that can apply to any databases are:

1. Read the Data -- SELECT

2. Insert the new Data -- INSERT
3. Update existing Data -- UPDATE
4. Remove Data –DELETE
These operations are referred to as the CRUD (Create, Read, Update, Delete).

The SQL SELECT QUERY

The SELECT statement permits you to read data from one or more tables.

The general syntax is:

SELECT first_name, last_name

FROM customer;

Example: Read the first_name and last_name from table customer.

To select all columns, use *

SELECT *

FROM customer;

The SQL SELECT DISTINCT

The SELECT DISTINCT statement is to return the different values.

SELECT DISTINCT first_name

FROM customer;

The SQL WHERE CLAUSE

The WHERE clause allows the user to filter the data from the table. The WHERE clause allows the user to
extract only those records that satisfy a specified condition.

When we access, the Text value

SQL requires single quotes around text values (many database systems will also use double quotes). And
numeric fields should not be enclosed in quotes.

SELECT first_name FROM customer

WHERE last_name = ‘perry’;

When we access the Numeric field

SELECT first_name , last_name FROM customer WHERE active = 0;

The SQL WHERE CLAUSE WITH AND, OR & NOT

A WHERE clause with AND:

SELECT first_name, email, address_id

FROM customer

WHERE fisrt_name = ‘IAN’ AND last_name = ‘STILL’

A WHERE clause with OR:

UPDATE customer

SET first_name = ‘jingle’

WHERE last_name = ‘ GREY’;

A WHERE clause with NOT:
Select store_id, first_name,last_name, email, address_id FROM customer

WHERE NOT store_id = 2;

The SQL ORDER BY

Order by is used to print the values from the table in order(ascending or descending)

Order By in Descending order

SELECT first_name, last_name,email

FROM customer

ORDER BY first_name DESC;

Order By in Ascending order

SELECT first_name, last_name,email

FROM customer
 ORDER BY first_name ASC;

The SQL SELECT TOP CLAUSE

The SELECT TOP is used to specify the number of records from the to return. The SELECT TOP is useful on
large tables with millions of records.

MySQL Syntax:
SELECT first_name, last_name,email

FROM customer WHERE first_name = ‘AUSTIN’

LIMIT 20;

The SQL MIN() AND MAX() FUNCTION

The MIN() function in SQL returns the smallest value of the selected column from the table. The MAX()
function in SQL returns the largest value of the selected column from the table.

MIN() Syntax

SELECT MIN(address_id)

FROM customer;

MAX() Syntax

SELECT MAX(address_id)

FROM customer;

The SQL COUNT(), AVG() AND SUM() FUNCTION

The COUNT() function gives the number of rows that matches specified conditions. And the AVG()
function in SQL returns the average value of a numeric column. The SUM() function in SQL returns the
total sum of a numeric column.
COUNT() Syntax

SELECT COUNT(email)

FROM customer;

AVG() Syntax

SELECT AVG(active)

FROM customer;

SUM() Syntax

SELECT SUM(active)

FROM customer

The SQL LIKE-OPERATOR

The LIKE operator is used with the WHERE clause to find for a specified pattern in an attribute. The two
wildcards are used in conjunction with the LIKE operator:

% - it represents zero, one, or multiple characters

 _ - it represents a single character

Note: MS Access uses an asterisk (*) in place of the percent sign (%) and a question mark (?) in place of the
underscore (_).

The ‘%’ and the ‘_’ can also be used in combinations.

LIKE Syntax

SELECT column1, column2, ... FROM table_name

WHERE column LIKE pattern;

Selects all columns of the customer with a first_name starting with "D".

SELECT * FROM customer

WHERE first_name LIKE 'D%';

Selects all columns of the customer with a first_name Ending with "E":

SELECT * FROM customer

WHERE first_name LIKE '%E';

Selects all columns of the customer with a first_name that have "or" in any position.
SELECT * FROM customer

WHERE first_name LIKE '%or%';

Selects all columns of the customer with a first_name that starts with "a" and ends with "o":
SELECT * FROM customer

WHERE first_name LIKE 'a%o';

The SQL IN AND NOT IN OPERATORS

The IN operator allows users to specify multiple values in a WHERE clause. The IN operator is a
shorthand for various OR conditions.

IN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, ...); OR:

SELECT column_name(s)

FROM table_name

WHERE column_name IN (SELECT STATEMENT);

Selects all the columns of customer whose customer_id in (1,2,3):

SELECT * FROM customer

WHERE cutomer_id IN (1,2,3);

Selects all the columns of customer whose customer_id NOT in (1,2,3):

 SELECT * FROM customer

WHERE cutomer_id NOT IN (1,2,3);

The SQL BETWEEN OPERATOR

The BETWEEN operator retrieves values within the given range. The values can be texts, numbers, or
dates. The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

Select all the columns from the customer with customer_id between 1 to 20.

SELECT * FROM customer WHERE customer_id BETWEEN 1 AND 20;

Select all the columns from the customer with customer_id, not between 1 to 570.

SELECT * FROM customer WHERE customer_id NOT BETWEEN 1 AND 570;

The SQL ALIAS

Aliases are used to give a nickname to a column in a table, a temporary name. Aliases are used to make
column names more readable to the user.

Alias Column Syntax

SELECT first_name AS first, last_name AS last FROM

customer;
Creates two aliases, one for the first_name column and one for the last_name column.
Alias Table Syntax

SELECT c.first_name, c.last_name

FROM customer AS c

Create an alias for the customer table

The SQL GROUP BY STATEMENT

The GROUP BY used to group rows from the table. And it has the same values as summary rows. For
example, find the number of customers in each country, The GROUP BY is often used with aggregate
functions like (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

GROUP BY Syntax

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

ORDER BY column_name(s);
Count the number of active and non-active customers

SELECT COUNT(customer_id) FROM customer GROUP BY active;

The SQL HAVING CLAUSE

The HAVING clause is added to SQL because the WHERE keyword can not be used with aggregate functions.

HAVING Syntax

SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

HAVING condition

ORDER BY column_name(s);

List the number of continents which has a region more than 6.

SELECT * from country group by(continent) having count(region) >6;

The SQL UNION

The UNION operator allows the user to combine the result-set of two or more SELECT statements in SQL.
Each SELECT statement within UNION should have the same number of columns. The columns in each SELECT
statement should also be in the same order. The columns should also have similar data types.

The SQL UNION

Select column_name(s) from table1

UNION

Select column_name(s) from table2;

UNION ALL Query

The UNION operator selects only different values by default. To allow duplicate values, the user can use
UNION ALL operator.

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

Note: The column names in the output are usually equal to the column names in the first SELECT
statement in the UNION.

4. SQL JOIN

The SQL Join help in retrieving data from two or more database tables. The tables are mutually related
using primary keys and foreign keys.

Type of Joins

INNER JOIN
The INNER JOIN is used to print rows from both tables that satisfy the given condition. For example,
the user wants to get a list of users who have rented movies together with titles of movies rented by
them. Users can use an INNER JOIN for that, which returns rows from both tables that satisfy with given
conditions.

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

INNER JOIN Syntax

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

SELECT city.city_id, country.country, city.last_update, country.last_update FROM city

INNER JOIN country ON city.country_id = country.country_id;

LEFT JOIN

The LEFT JOIN returns all the records from the table1 (left table) and the matched records from the
table2 (right table). The output is NULL from the right side if there is no match.

LEFT JOIN

LEFT JOIN Syntax

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

SELECT city.city_id, country.country, city.last_update, country.last_update FROM city

LEFT JOIN country ON city.country_id = country.country_id

RIGHT JOIN

The RIGHT JOIN is the opposite of LEFT JOIN. The RIGHT JOIN prints all the columns from the
table2(right table) even if there no matching rows have been found in the table1 (left table). If there no
matches have been found in the table (left table), NULL is returned.

RIGHT JOIN

RIGHT JOIN Syntax

SELECT column_name(s)

FROM table1

RIGHT JOIN table2 ON table1.column_name = table2.column_name;

SELECT city.city_id, country.country, city.last_update, country.last_update FROM city

RIGHT JOIN country ON city.country_id = country.country_id;

Full OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there are a match in left (table1) or right
(table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name WHERE condition;

Note: MySQL does not support the Full Join, so we can perform left join and right join separately then
take the union of them.

SELECT * FROM t1

LEFT JOIN t2 ON t1.id = t2.id

UNION

SELECT * FROM t1

RIGHT JOIN t2 ON t1.id = t2.id

SELF-JOIN

A self-JOIN is a regular join, but the table is joined with itself.

Self -JOIN Syntax

SELECT column_name(s)

FROM table1 T1, table1 T2

WHERE condition;

5. SQL DATABASE

The SQL CREATE DATABASE STATEMENT

The CREATE DATABASE statement in SQL is used to create a new SQL database.

Syntax

CREATE DATABASE database_name;

Let’s create a database and give name as testdb CREATE database testdb;

Now, let’s check the databases in MySQL by using show databases query.

Show databases;

The SQL DROP DATABASE STATEMENT

The DROP DATABASE statement in SQL is used to drop an existing SQL database.

Syntax

DROP DATABASE database_name;

Let’s drop the created database by using drop database testdb. DROP database testdb;

Now, let’s check the databases in MySQL by using show databases query after dropping the testdb.

SHOW databases;

The created database(testdb) has been dropped.

The SQL CREATE TABLE

The CREATE TABLE statement in SQL is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 data_type, column2 data_type, column3 data_type,

....

);
The column1, column2, ….., specify the names of the columns of the table. The datatype parameter
specifies the type of data the column can hold (e.g., varchar, integer, date, etc.)

Let’s create a customer table

CREATE TABLE cutomer(id integer, first_name varchar(10), last_name varchar(10), city varchar(10),
country varchar(15), phone varchar(15));

To check the schema of the table, use desc table_name.

DESC customer;

The SQL DROP TABLE STATEMENT

The DROP TABLE statement in SQL is used to drop an existing table in a database. DROP TABLE
customer;

The table has dropped after running the query drop table table_name. As we can see, the table does not
exist after dropped.

Now we are going to create the same table again to insert the values in that table.

The SQL INSERT INTO STATEMENT

The INSERT INTO statement in SQL is used to insert new records in a table.

INSERT INTO query

We can write the INSERT INTO statement in two ways. The first way is to specify both the column names
and the values to be inserted:

INSERT INTO customer(id , first_name, last_name ,city ,country,phone)VALUES (2, ‘Ana’, ‘Trujillo’,
‘Mexico’, ‘Mexico’, (5) 555-4729);

If users are adding values for all the columns of the table, you don’t need to specify the particular column
names in the SQL query. However, ensure the order of the values is in the same order as the columns in
the table.

The INSERT INTO query would be as follows:

INSERT INTO customer

VALUES (3, ‘Antonio, ‘Moreno, ‘Mexico’, ‘Mexico’, (5) 555-3932);

We have inserted two rows yet. Similarly, we can insert many rows in the table. Finally, we have added
ten rows as we can see in the picture below.

SELECT * FROM customer;

The SQL NULL VALUES

What is a NULL Value?

The field with a NULL value is a field with no value. If the field in a table is optional, to insert new data or
update data without adding a value to this field and Then, the field will be saved as a NULL value.

Note: A NULL value is not the same as a zero value, or we can say a field that holds spaces. The field
with a NULL value is one that has been left blank during record creation!

Insert the NULL values in tables

INSERT INTO customer VALUES(11, ‘Victoria’, ‘Ashworth’, ‘London’, NULL, ‘(171) 555-1212’)

As we can able to see, the last row contains one NULL value.

How to check for NULL Values?

To test for NULL values in the table has to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT *

FROM customer WHERE country IS NULL;

IS NOT NULL Syntax

SELECT * FROM customer

WHERE country IS NOT NULL;

It will return those countries which have some values(expect Null values).

The SQL UPDATE STATEMENT

The UPDATE statement in SQL is used to modify the existing records in a table.

UPDATE Syntax

UPDATE customer

SET country = ‘Mexico’ WHERE id = 11;

We have updated the null value of the country with Mexico.

The SQL DELETE STATEMENT

The DELETE statement in SQL is used to delete existing records in a table.

DELETE Syntax

DELETE FROM customer WHERE id = 11;

We have deleted one row, which contains id = 11.

The SQL ALTER TABLE STATEMENT

The ALTER TABLE statement in SQL is used to add, modify, or delete columns in an existing table. And it
also used to add and drop various constraints on a current table.

5.1.1. ALTER TABLE - ADD COLUMN IN EXISTING TABLE

To add a new column in a table, use the SQL query

ALTER TABLE customer

ADD email varchar(25);

5.1.2. ALTER TABLE – MODIFY/ALTER COLUMN

To change the data type of column values in a table, use the following syntax: ALTER TABLE customer
ADD COLUMN dob date;

We have assigned the dob with the datatype date. But now we want to change the datatype from date to
year.
 ALTER TABLE customer MODIFY dob year;

5.1.3. ALTER TABLE - DROP COLUMN

To delete a specific column in a table, use the following syntax (notice that some database systems don't
allow deleting a column):

Syntax:

ALTER TABLE customer

DROP COLUMN email;

5. SQL DATABASE

The SQL CREATE DATABASE STATEMENT

The CREATE DATABASE statement in SQL is used to create a new SQL database.

Syntax

CREATE DATABASE database_name;

Let’s create a database and give name as testdb CREATE database testdb;

Now, let’s check the databases in MySQL by using show databases query.

Show databases;

The SQL DROP DATABASE STATEMENT

The DROP DATABASE statement in SQL is used to drop an existing SQL database.

Syntax

DROP DATABASE database_name;

Let’s drop the created database by using drop database testdb. DROP database testdb;
Now, let’s check the databases in MySQL by using show databases query after dropping the testdb.

SHOW databases;

The created database(testdb) has been dropped.

The SQL CREATE TABLE

The CREATE TABLE statement in SQL is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 data_type, column2 data_type, column3 data_type,

....

);

The column1, column2, ….., specify the names of the columns of the table. The datatype parameter
specifies the type of data the column can hold (e.g., varchar, integer, date, etc.)

Let’s create a customer table

CREATE TABLE cutomer(id integer, first_name varchar(10), last_name varchar(10), city varchar(10),
country varchar(15), phone varchar(15));

To check the schema of the table, use desc table_name.

DESC customer;

The SQL DROP TABLE STATEMENT

The DROP TABLE statement in SQL is used to drop an existing table in a database. DROP TABLE
customer;

The table has dropped after running the query drop table table_name. As we can see, the table does not
exist after dropped.

Now we are going to create the same table again to insert the values in that table.

The SQL INSERT INTO STATEMENT

The INSERT INTO statement in SQL is used to insert new records in a table.

INSERT INTO query

We can write the INSERT INTO statement in two ways. The first way is to specify both the column names
and the values to be inserted:

INSERT INTO customer(id , first_name, last_name ,city ,country,phone)VALUES (2, ‘Ana’, ‘Trujillo’,
‘Mexico’, ‘Mexico’, (5) 555-4729);

If users are adding values for all the columns of the table, you don’t need to specify the particular column
names in the SQL query. However, ensure the order of the values is in the same order as the columns in
the table.

The INSERT INTO query would be as follows:

INSERT INTO customer

VALUES (3, ‘Antonio, ‘Moreno, ‘Mexico’, ‘Mexico’, (5) 555-3932);

We have inserted two rows yet. Similarly, we can insert many rows in the table. Finally, we have added
ten rows as we can see in the picture below.

SELECT * FROM customer;

The SQL NULL VALUES

What is a NULL Value?

The field with a NULL value is a field with no value. If the field in a table is optional, to insert new data or
update data without adding a value to this field and Then, the field will be saved as a NULL value.

Note: A NULL value is not the same as a zero value, or we can say a field that holds spaces. The field
with a NULL value is one that has been left blank during record creation!

Insert the NULL values in tables

INSERT INTO customer VALUES(11, ‘Victoria’, ‘Ashworth’, ‘London’, NULL, ‘(171) 555-1212’)

As we can able to see, the last row contains one NULL value.

How to check for NULL Values?

To test for NULL values in the table has to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT *

FROM customer WHERE country IS NULL;

IS NOT NULL Syntax

SELECT * FROM customer

WHERE country IS NOT NULL;

It will return those countries which have some values(expect Null values).

The SQL UPDATE STATEMENT

The UPDATE statement in SQL is used to modify the existing records in a table.

UPDATE Syntax

UPDATE customer

SET country = ‘Mexico’ WHERE id = 11;

We have updated the null value of the country with Mexico.

The SQL DELETE STATEMENT

The DELETE statement in SQL is used to delete existing records in a table.

DELETE Syntax
DELETE FROM customer WHERE id = 11;

We have deleted one row, which contains id = 11.

The SQL ALTER TABLE STATEMENT

The ALTER TABLE statement in SQL is used to add, modify, or delete columns in an existing table. And it
also used to add and drop various constraints on a current table.

5.1.1. ALTER TABLE - ADD COLUMN IN EXISTING TABLE

To add a new column in a table, use the SQL query

ALTER TABLE customer

ADD email varchar(25);

5.1.2. ALTER TABLE – MODIFY/ALTER COLUMN

To change the data type of column values in a table, use the following syntax: ALTER TABLE customer
ADD COLUMN dob date;

We have assigned the dob with the datatype date. But now we want to change the datatype from date to
year.
 ALTER TABLE customer MODIFY dob year;

5.1.3. ALTER TABLE - DROP COLUMN

To delete a specific column in a table, use the following syntax (notice that some database systems don't
allow deleting a column):

Syntax:

ALTER TABLE customer

DROP COLUMN email;

6. The SQL CONSTRAINTS

The Constraints in SQL can be specified when the table is created with the CREATE TABLE statement, or
after the table is altered with the ALTER TABLE statement.

Syntax:

CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint,

column3 datatype constraint, ....

);

SQL Constraints

SQL constraints are used to specify any rules for the records in a table. Constraints can be used to limit
the type of data that can go into a table. It ensures the accuracy and reliability of the records in the
table, and if there is any violation between the constraint and the record action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and tablelevel
constraints apply to the whole table.

The constraints are commonly used in SQL

CONSTRAINTS DESCRIPTION

Not Null It Ensures that a column cannot have a NULL value.

Unique It Ensures that all the values in a column are unique.

Primary Key It is a combination of a NOT NULL and UNIQUE. Uniquely

identifies each row in a table.

Foreign Key Uniquely identifies a record /row in another table

Check It checks that all values in a column satisfy a specific condition

Default It gives a default value for a column when no value is specified

Index It is Used to create and retrieve data from the database quickly.

NOT NULL CONSTRAINTS

The NOT NULL constraint enforces a column NOT to accept NULL values. This imposes a field always to
contain a value, which means that the user cannot insert a new record in a table or update a record
without adding a value to this field.

NOTE: By default, a column can hold NULL values.

Create a table using SQL not null constraints

The following SQL ensures that the "id", "First_name" and "Last_name" columns will NOT accept NULL
values when the "student" table is created:

Example

CREATE TABLE student( id int NOT NULL, first_name varchar(25) NOT NULL, last_name
varchar(25) NOT NULL, age int

);

In the above table, it has specified the id, first_name, and last_name as not null and age as null.

SQL NOT NULL on ALTER table Statement

To make a NOT NULL constraint on the "age" column when the "student" table is already created, use
the following SQL:

Example:

ALTER TABLE student

MODIFY age int NOT NULL;

In the above table, it has specified the id, first_name,last_name, and age as not null.

SQL UNIQUE CONSTRAINT

The UNIQUE constraint in SQL ensures that all values in a column are distinct. UNIQUE and PRIMARY
KEY constraints both provides a guarantee for uniqueness for a column or group of columns. A
PRIMARY KEY constraint, by default, has a UNIQUE constraint. However, the user can have many
UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Creates UNIQUE constraint on the "id" column when the "person" table is created

CREATE TABLE person ( id int NOT NULL, last_name varchar(255) NOT NULL, first_name
varchar(255), age int,

UNIQUE (ID)

);

We have applied unique constraints on id, and as we can see, it is showing as the primary key.

Create a UNIQUE constraint on the "first_name" column when the "persons" table already
exists.

ALTER TABLE persons

ADD UNIQUE (first_name);

Now we have two unique constraints(id and first_name) in the person table.

To name the UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the
following SQL syntax:

ALTER TABLE person

ADD CONSTRAINT UC_person UNIQUE (age, last_name);

Here the age and last_name are converted as unique constraints.

DROP A UNIQUE CONSTRAINT

To drop a UNIQUE constraint, use the SQL query

ALTER TABLE person

DROP INDEX UC_Person;

As we can see in the person table The unique constraint(UC_Persons) has been dropped.
SQL PRIMARY KEY CONSTRAINTS

The PRIMARY KEY constraint uniquely identifies each of the records in a table. Only ONE primary key can
have in a table. And also, in the table, this primary key can consist of single or multiple columns (fields).
Primary keys should contain UNIQUE values, and cannot contain NULL values.

CREATE TABLE person(ID int NOT NULL, last_name varchar(255) NOT NULL, first_name varchar(255),
age int, PRIMARY KEY(ID));

To allow the naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on
multiple columns, use the SQL syntax.

CREATE TABLE person ( id int NOT NULL, last_name varchar(255) NOT NULL, first_name
varchar(255), age int,

CONSTRAINT PK_person PRIMARY KEY (id,last_name)

);

Note: In this example, there is only ONE PRIMARY KEY as PK_Person. And the VALUE of the primary key
is made up of two columns (id+ last_name).

SQL PRIMARY KEY on ALTER TABLE

Create a PRIMARY KEY constraint on the column_name "id" when the table_name(student) is already
created, use the following SQL:

ALTER TABLE student

ADD PRIMARY KEY (id);

Here we have assigned the primary key as “id” on the student table.

Allow the naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple
columns, use the SQL query:

ALTER TABLE student

ADD CONSTRAINT PK_student PRIMARY KEY (id,first_name);

DROP PRIMARY KEY CONSTRAINTS

To drop the PRIMARY KEY constraint from the table, use the SQL Query:

ALTER TABLE student

DROP PRIMARY KEY;

As we can see from the student table, the primary key has been dropped from the table.

SQL FOREIGN KEY CONSTRAINT

A FOREIGN KEY is used to link two tables together. It is sometimes also called a referencing key. Foreign
Key is a combination of columns (can be single column) whose value matches a Primary Key in the
different tables. The relationship between two tables matches the Primary Key in one of the tables with a
Foreign Key in the second table. If the table contains a primary key defined on any field, then the user
should not have two records having the equal value of that field.
Let’s create two tables using the foreign key.

CUSTOMER table

CREATE TABLE customer(

Id int NOT NULL,

Name varchar(20) NOT NULL,

Age int NOT NULL,

Address varchar(25) ,

Salary decimal (18, 2),

PRIMARY KEY (id)

);

Order Table with Foreign key

CREATE TABLE Orders (OrderID int NOT NULL, OrderNumber int NOT NULL, Id int,

PRIMARY KEY(OrderID), CONSTRAINT FK_customerOrder FOREIGN KEY(Id));

Here the Id is the primary key for the customer table and foreign key for orders table.

FOREIGN KEY on ALTER TABLE

To create the FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already
created, use the SQL query:

ALTER TABLE Orders

ADD FOREIGN KEY (ID) REFERENCES customer(id);

DROP A FOREIGN KEY CONSTRAINT

To drop a FOREIGN KEY constraint from the table, use the SQL query:

ALTER TABLE Orders

DROP FOREIGN KEY FK_PersonOrder;

SQL CHECK CONSTRAINTS

The CHECK CONSTRAINTS is used to limit the range of value that can be placed in a column if the user
defines a CHECK constraint on a single column, it allows only specific values for the column. If the user
defines a CHECK constraint on a table, it can limit the values in particular columns based on values in
another column in the row.

SQL CHECK on CREATE TABLE

SQL Query to creates a CHECK constraint on the column "Age" when the table "Persons" is created. The
CHECK constraint makes sure that the user can not have any person below 18 years: CREATE TABLE
Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CHECK (Age>=18)

);
Here we have created the Persons table and given a check constraint on the Age column. If the Age<18,
then it will throw an error, as shown below.

INSERT INTO Persons VALUES(1, ‘abc’, ‘aaa’, 17);

For creating a CHECK constraint on multiple columns in the table, use the SQL syntax:

CHECK on ALTER TABLE

Create a CHECK constraint on the column "Age" when the table is already created, use the following SQL:

ALTER TABLE Persons

ADD CHECK (Age >= 18)

Defining CHECK constraint on multiple columns of a table, use the SQL query:

ALTER TABLE Persons

ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP A CHECK CONSTRAINT

To drop a CHECK constraint from the table, use the following SQL:

ALTER TABLE Persons

DROP CHECK CHK_PersonAge;

Here we have dropped the CHK_PersonAge constraints by using the drop statement.

SQL DEFAULT CONSTRAINT

The DEFAULT constraint in SQL is used to provide a default value for a column of the table. The default
value will be added to every new record if no other value is mentioned.

SQL DEFAULT on CREATE TABLE

The SQL query to sets a DEFAULT value for the "City" column when the "Persons" table is created
CREATE TABLE Persons (
 ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

City varchar(255) DEFAULT 'Sandnes'

);

As we can see in the Persons table, the city name is written as Sandnes by Default.

SQL DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the column "City" when the table is already created, use the following
SQL:

ALTER TABLE Persons

ALTER Age SET DEFAULT 20;

DROP A DEFAULT CONSTRAINT

To drop a DEFAULT constraint from the table, use the SQL query:

ALTER TABLE Persons

ALTER City DROP DEFAULT;

As we can see in the Persons table, the default value of the city has been removed.

7. SQL CREATE INDEX STATEMENT

CREATE INDEX statement in SQL is used to create indexes in tables. The indexes are used to retrieve
data from the database more quickly than others. The user can not see the indexes, and they are just
used to speed up queries /searches.

Note: Updating the table with indexes takes a lot of time than updating a table without indexes. It is
because the indexes also need an update. So, only create indexes on those columns that will be
frequently searched against.

CREATE INDEX Syntax

It creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);

Example:

Creates an index named "idex_lastname" on the "LastName" column in the "Persons" table: CREATE
INDEX idex_lastname on Persons (LastName)

If a user wants to create an index on a combination of columns, you can list the column names within the
parentheses, separated by commas:

CREATE INDEX idex_pname

ON Persons (LastName, FirstName);

CREATE UNIQUE INDEX

It creates a unique index on a table and Duplicate values are not allowed.

Syntax:

Create UNIQUE INDEX index_name

on table_name (column1, column2, ...);

Note: The query for creating indexes varies among different databases. Therefore, Check the query for
creating indexes in your database.

DROP INDEX STATEMENT

The DROP INDEX statement in SQL is used to delete an index in a table.

ALTER TABLE table_name

DROP INDEX index_name;

8. SQL VIEWS STATEMENT

In SQL, the view is a virtual table based on the result-set of an SQL statement. A view holds rows and
columns, similar to a real table. The fields in a view are fields from one or more real tables in the
database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if
the data were coming from one single table.

CREATE VIEW Syntax

CREATE VIEW view_name AS SELECT column1, column2, ...

FROM table_name

WHERE condition;

Note: A view always shows up-to-date data! The database engine recreates the data, using the view's
SQL statement, every time a user queries a view.

Create a table customer

Create a view on the table customers. Here, the view would be used to have a customer name and age
from the customers table.

CREATE VIEW CUSTOMERS_VIEW AS

SELECT name, age

FROM customers;

The WITH CHECK OPTION

The WITH CHECK OPTION in SQL is a CREATE VIEW statement option. The objective of the WITH
CHECK OPTION is to make sure that all UPDATE and INSERTs satisfy the condition(s) in the view
definition.

If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

The following code block has an example of creating the same view CUSTOMERS_VIEW with the WITH
CHECK OPTION.

CREATE VIEW CUSTOMER_VIEW AS

SELECT name, age

FROM customers

WHERE age IS NOT NULL

WITH CHECK OPTION;

Here we have created a view(CUSTOMER_VIEW) with the check option.

DELETING ROWS INTO A VIEW

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT
commands apply to the DELETE command.

Example Delete a record having AGE = 25.

DELETE FROM CUSTOMER_VIEW

WHERE age = 25;

Here we have deleted the row, which contains the age = 25.

DROPPING VIEWS

Where the user has a view, you need a method to drop the view if it is no longer needed. The query is
straightforward and is given below:

DROP VIEW view_name;

It’s similar to the other dropping option, as we have done yet for tables. As we can see, the view is not
available in the database after dropping the view.

9. STORED PROCEDURE AND FUNCTIONS

Advance MySQL provides better understanding for Stored Procedure, View, Triggers, Events and Indexes.
In this chapter, we are going to understand all of the above terminology one by one in details with the
help of MySQL workbench.

9.1. MySQL Stored Procedure

What is a SQL Stored Procedure?

The stored procedure is a prepared SQL query that you can save so that the query can be reused over
and over again. So, if the user has an SQL query that you write over and over again, keep it as a stored
procedure and execute it. Users can also pass parameters to a stored procedure so that the stored
procedure can act based on the parameter value that is given.

9.1.1. Creating the Stored Procedure

Syntax for creating a Stored Procedure

DELIMITER $$

CREATE PROCEDURE PROCEDURE_NAME() BEGIN

 SELECT Column_name1, Column_name2,………..

FROM Table_name

END$$

DELIMITER

Here, the DELIMITER is not the part of Query, the first Delimiter is change the default delimiter to // and
the second delimiter is change the delimiter to the default. The Stored procedure is saved automatically
in the database while creation.

To execute the query in MySQL, use the MySQL workbench for better user-interface, and use inbuilt
databases to perform the advance MySQL queries.

Here we have create a procedure called Customer, and we have mentioned few column names in it. And
in last we have closed the procdure. If we want to know the output of the above query, then need to run
the procedure by clicking on the execution button on workbench display.

9.1.2. EXECUTION OF STORE PROCEDURE

Execution of the Stored Procedure is very simple by using the CALL procedure_name, Execute the
below query to get the result of the defined stored procedure.

After calling the procedure, we are able to see the selected columns which are mentioned in the
procedure. The output is as follow:

Stored Procedue can have parameters,so while execution we can pass the argument and get the result.
We can use control flow (like: IF, LOOP, CASE, etc.)in the stored procedure to make dynamic queries and
also we can pass one stored procedure inside the other which will help to modulize the queries.
9.1.3. DROP THE STORED PROCEDURE

Drop procedure use to delete the stored procedure from the databases. The following query used to
delete the stored procedure for the Database:

DROP Stored_procedure_name

The below syntax used for conditionally drop of stored_procedure and first it check the procedure_name
& if it exist then drop the stored procedure from the database.

DROP PROCEDURE [IF EXIST] Stored_procedure_name;

If the stored procedure is not available then it throw an error like mentioned below.

9.1.4. STORED PROCEDURE PARAMETERS

We can create a stored procedure with parameters. In Stored procedure the parameters are like IN, OUT
and INPUT. The parameters make the Stored Procedure more flexible and useful.

DEFINING A PARAMETERS

To define the parameter inside the stored procedure, run the below query:

[ IN | OUT | INPUT ] PARAMETER_NAME datatype[(length)]

IN Parameter

It is the default parameter in Stored Procedure and the calling program should pass an argument to
stored Procedure. The value of IN is protected that means even the IN value is changed inside the
stored procedure the original value will retained after end of the Stored Procedure.

Example for IN: Create a Stored Procedure that find all the active customers by the input parameter
as Active.
Output:

Out Parameter

The value of the Output Parameter can be changed inside the Store Procedure and pass the new value
while calling the Stored Procedure.

Example for OUT: write a stored procedure to print the square root of a number.

Output:

The my_sqrt stored procedure has two parameters.

input_number : it takes input from user in interger format. out_number : it store the
output of the function.
To print the output, we have call the my_sqrt() and pass the arguments, the first argument is user input
and second is output, and to show the output use select @out_number.

INOUT

It is the combination of the IN and OUT Parameters.

Example for INOUT : Here we are just counting the numbers between a region using INOUT stored
procedure.

Output:

It will print the query in sequence so if we call the stored procedure for many time,so it will count all the
sum and in last it will print the counter values.

9.1.5. STORED PROCEDURE VARIABLES

In this unit we will learn about the variables, and also how to declare variables? How to use the
variables?. Basically a variable is a called as data object whose value can be change while execution of
Stored Procedure.

DECLARING THE VARIABLE

To declare the variable inside a stored procedure, use the below query:

DECLARE Variable_name datatype(size) [DEFAULT Default_value];

Here,

DECLARE – It is a keyword and it is use to declare the variable. First write DECLARE keyword and then
variable_name.
Datatype(size) – it us use to define the variable datatype (like: IN, Varchar, or char)and size use to define
the length of the variable.

Default – it assign variable with default value option. If we declare the variable without specifying any
default values, then it’s values will be NULL.

Using MySQL Stored Procedure, we can declare more than one variable.

ASSIGNING VARIABLE

Once we declare the variable, now it is ready to use. To assign a value to the variable use SET
statement:

10. TRIGGERS

Trigger is a stored program that invoked automatically in response to an event such as insert, delete or
update that occurs in the table. Suppose, you defined a trigger and you insert a row inside the table, then
it will automatically invoked before or after the insertion of row.

There are two types of the TRIGGERS:

1. Row-level-Triggers: it is activated for each row that is inserted, deleted or updated.

2. Statement-level-Triggers: it is executed for each transaction.

Note: It supports only Row-level-Triggers.

Advantage of Triggers

• It provides a way to check the integrity in data.

• It can be useful for auditing the data changes in tables

 • It handles the errors from the database layer.

10.1. CREATING TRIGGERS

CREATE TRIGGER statement is used to create the triggers. The syntax is following:

CREATE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | DELETE | UPDATE } ON table_name FOR EACH ROW

trigger_body;

Here,

• The first line is for creating the trigger with trigger_name.

• It will make the condition that the trigger invokes before or after any modification in
row.

• The operation we can choose as INSERT, DELETE OR UPDATE on the table_name

at any row.

Let’s understand the triggers by using an example. Now, we are going to create a table names as

EmployeeDetail. And defined a primary key as id;

Create another table and insert some rows into that table, now we are going to create one more as
“employee”. On the “employee” table, we are going to perform all the trigger operation on it and the
operations log will be stored in “EmployeeDetail”.
Let’s create a trigger using the before update operation on employee table. As we can see, the trigger is
created as name “before_on_employee_update”.

Now, let’s use the trigger and update the row values of employee,

Let’s check the table EmployeeDetail and check the action on it.

As we can see, the trigger is automatically invoked and inserted a new row inside the EmployeeDetail
table and the row is updated.

10.2. DROP TRIGGER

To delete the TRIGGER we use the DROP TRIGGER statement, and it will delete the trigger from the
database. The syntax is as follow:

DROP TRIGGER [IF EXISTS] trigger_name;

Here,

• Firstly, it will check the triggers_name and if it exist then delete that particular trigger.

• To delete any trigger, the trigger_name should be written after the DROP TRIGGER.

OR

DROP TRIGGER trigger_name;

It will delete the trigger without checking their existence in the database.
The trigger before_on_employee_update has been deleted from the database.

10.3. BEFORE INSERT TRIGGER

The before insert trigger are automatically fired before an insert occurs on the table. The syntax for
before insert trigger as follow:

CREATE TRIGGER trigger_name

BEFORE INSERT

ON table_name FOR EACH ROW

trigger_body;

let’s understand through an example. We are creating a table as totalamount;

Now, let’s create another table as totalamountstatus to store the summary of the triggers.

Lets create a before insert trigger to get the totalamount in the totalamountstatus table before a new
work center is inserted into the totalamount table.
The trigger is created successfully for updating before insert into the totalamount table. Let’s test the
trigger by inserting the value in it.

We have successfully insderted the value in the totalamount table. But the value is invoked in the
totalamountstatus table. Let’s call the totalamountstatus table to check the total amount.

The trigger is invoked and inserted a new row into the totalamountstatus. If we insert another value that
will automatically added into the present amount and return the totalamount.

10.4. AFTER INSERT TRIGGER

The after insert trigger are automatically fired after an insert occurs on the table. The syntax for after
insert trigger as follow:

CREATE TRIGGER trigger_name

AFTER INSERT

ON table_name FOR EACH ROW trigger_body

Let’s understand the after insert trigger using an example; Create a table named as members.
Create another table as remembers.

Now, create a after insert trigger as after_members_insert and that trigger insert into reminders table
if the birth_date of any person is null.

Let’s test the alter insert trigger.

We have inserted the two rows inside the members table and the members table is shown below;

As we can see here, the two rows are inserted but the birthdate of Hemant is null and as we mentioned
the condition in trigger, it will invoke a message if birth date is as null. Let’s check the reminders table.
As we have made the condition inside the trigger, it has invoked automatically when the birth day found
as null. And the message showing as Hello Hemant, update your date_of_birth.

10.5. BEFORE UPDATE TRIGGER

The BEFORE UPDATE TRIGGER is invoked automatically before an update event occurs on the table
which associated with the trigger.

CREATE TRIGGER trigger_name

BEFORE UPDATE

ON table_name FOR EACH ROW

trigger_body

Let’s understand through an example;

Create a table as sales;

Insert few rows into the sales table;

Creating the BEFORE UPDATE TRIGGER, and assigning the error message as the new quantity cannot be
greater than 3-times of previous.
The trigger will automatically invoke and fire before updating any values in any row.

Let’s update the values in row of sales table;

We have updated a value of quantity where the id = 1 but it will not satisfied the condition so it will not
give the error message, see the table;

Let’s update the quantity as some other value which are 3-times greater than the quantity150.

As we have increased the quantity as 3-times higher than previous, it’s showing message as “the new
quantity cannot be 3times greater than the current quantity”.

10.6. AFTER UPDATE TRIGGER

The ALTER UPDATE TRIGGER invoke automatically after updating the events in the associated table. The
syntax for AFTER update triggers as follow:
 CREATE TRIGGER trigger_name

AFTER UPDATE

ON table_name FOR EACH ROW

trigger_body

Let’s understand the after update trigger with an example; we are going to use the first table as sales
table and the second table as sales_changes. So let’s create the second table sales_changes.

Now, let’s create the AFTER UPDATE TRIGGER;

The after_update_sales trigger automatically invoked after updating any row of the sales table. Updating
the quantity column in sales table, where id = 1

Let’s check the sales_changes table;

As we can see the value is updated automatically in the sales_changes.

10.7. BEFORE DELETE TRIGGER

The BEFORE DELETE TRIGGER are fired automatically before a delete event occurs in table. The syntax
for before delete trigger as follow: TRIGGER trigger_name

BEFORE DELETE

ON table_name FOR EACH ROW

trigger_body
Let’s create a table as salary;

Insert few rows into salary table;

Create another table as deleted_salary to store the deleted salaries;

Now let’s create a stored procedure, which contains the before delete triggers. Before delete trigger store
the deleted value into the deleted_salary table.

Let’s delete a row from the salary table;

Now, check the deleted_Salary table to check whether the data is stored or not.

As we can see here, the BEFORE DELETE TRIGGER is automatically invoked the row before event occurs
on the salary table.

10.8. AFTER DELETE TRIGGER

AFTER DELETE TRIGGERS are invoke automatically after deleting the event occurs on the table. The
syntax for AFTER DELETE TRIGGERS as follow:

CREATE TRIGGER trigger_name

AFTER DELETE

ON table_name FOR EACH ROW

trigger_body;

Create a table salary and insert few rows into the table;

Create another table to store the deleted row into that,we are creating another table as deleted_salary;

Now,let’s store the value of total into the deleted_salary table by using the below command. Here,we are
using the SUM() function to add the salaries from the salary table and store it into the deleted_salary as
total.

So the total amount is 180000.

Now, Let’s create AFTER DELETE TRIGGER;

We are creating a trigger which update the total salary into the deleted_Salary table after deleting from
the salary table.

Let’s delete a row where the employee_no = 1022 inside the salary table;
Check the deleted_Salary;

As we can the value of total is decresed by 50000, because it is substracted from the total amount.
Week 2: Relational Database Conception Principles
a. Functional Dependence
i. Definition of Functional Dependence

In the context of database theory, specifically in the normalization process, functional dependence is a
R and attributes A
constraint between two sets of attributes in a relation from a database. Given a relation
and B, we say that A functionally determines B, represented as A→B, if for each pair of tuples in R that
have the same value for A, they also have the same value for B.

In simpler terms, functional dependence means that the value of one attribute (or set of attributes) in a
relation uniquely determines the value of another attribute (or set of attributes).

Examples:

In a student database with attributes StudentID, FirstName, LastName, GPA:

StudentID → FirstName because a given student ID uniquely determines the first name.
In a product inventory database with attributes ProductID, ProductName, Price:

ProductID → Price because a specific product ID will have a unique price.

In MySQL, you can define constraints that enforce functional dependencies when you create or alter tables.

Here's an example of how you could define a table in MySQL where StudentID functionally determines
FirstName, LastName, and GPA.

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

GPA FLOAT

);

In this example, StudentID is the primary key, which means it uniquely identifies each record in the table.
Therefore, it functionally determines all other attributes (FirstName, LastName, GPA) in the table.

ii. Types of Functional Dependencies (Full, Partial, Transitive)

Functional dependencies can be categorized into different types based on their characteristics. To enforce
functional dependencies in MySQL, you can use constraints like PRIMARY KEY, UNIQUE, and FOREIGN
KEY. The primary types of functional dependencies are:

Full Functional Dependency:

A functional dependency A→B is a full functional dependency if removal of any attribute A′ from A means that
the dependency does not hold anymore. In simpler terms, B is fully functionally dependent on A and not on
any subset of A.

Example: In a table with attributes {StudentID,CourseID,Grade}, {StudentID,CourseID}→Grade is a full

functional dependency.

CREATE TABLE Grades (

 StudentID INT,

CourseID INT,

Grade CHAR(1),

PRIMARY KEY (StudentID, CourseID)

);

Here, Grade is fully functionally dependent on the composite key {StudentID, CourseID}.

Partial Functional Dependency:

A functional dependency A→B is a partial functional dependency if some attribute B can be functionally
determined by a proper subset of A.

Example:In a table with attributes {StudentID,CourseID,Grade,StudentName}, if

{StudentID,CourseID}→StudentName, then it's a partial functional dependency because StudentName can be
determined solely by StudentID.

To avoid partial functional dependencies, you can decompose the table into two separate tables like below:

-- Student Information

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

StudentName VARCHAR(50)

);

-- Course Grades

CREATE TABLE Grades (

StudentID INT,

CourseID INT,

Grade CHAR(1),

PRIMARY KEY (StudentID, CourseID),

FOREIGN KEY (StudentID) REFERENCES Students(StudentID)

);

Transitive Functional Dependency:

A functional dependency A→B is a transitive functional dependency if there exists an attribute set C where
A→C and C→B, but C is not a subset of A and B is not functionally dependent on A directly.
Example:In a table with attributes {EmployeeID,Department,DepartmentHead}, if EmployeeID→Department
and Department→DepartmentHead, then EmployeeID→DepartmentHead is a transitive dependency.

To resolve transitive dependencies, you can also break the table into smaller tables.

-- Department Information
 CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,

DepartmentHead VARCHAR(50)

);

-- Employee Information

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

DepartmentID INT,

FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

);
iii. Closure of Attributes

In the context of database theory and functional dependency, the closure of a set of attributes A, denoted as
A+, is the set of attributes that can be functionally determined by A using a given set of functional
dependencies.

The closure helps to infer all the attributes that can be determined by a given set of attributes, and it is crucial
for various processes like database normalization, finding candidate keys, and more.

Example: Attribute Closure in MySQL

Let's say you have a table with attributes StudentID, CourseID, and Grade. To see if StudentID and
CourseID together can functionally determine Grade, you could use:

SELECT StudentID, CourseID, COUNT(DISTINCT Grade)

FROM Grades

GROUP BY StudentID, CourseID

HAVING COUNT(DISTINCT Grade) > 1;

If the query returns no rows, it indicates that the set {StudentID, CourseID} functionally determines
Grade, confirming that the closure {StudentID,CourseID}+ includes Grade
iv. Functional Dependency Diagrams

Consider a university database schema that has a table named Enrollments containing the following
attributes:

StudentID: The unique identifier for each student.

CourseID: The unique identifier for each course.

Semester: The semester during which a course is taken.

Grade: The grade received by the student in a course.

ProfessorID: The unique identifier for each professor.

Let's assume we have the following functional dependencies:

 {StudentID,CourseID,Semester}→Grade

The combination of a student, a course, and a semester uniquely determines a grade.

{CourseID}→ProfessorID

Each course is taught by one professor.

{StudentID,Semester}→CourseID

A student's ID and the semester uniquely determine the courses they are taking.

The diagram might look something like:

• Nodes represent attributes (StudentID, CourseID, Semester, Grade, ProfessorID).

• Arrows represent functional dependencies.

i. {StudentID,CourseID,Semester}→Grade:

a. StudentID, CourseID, and Semester together functionally determine Grade.

Hence, arrows go from these attributes to Grade.

ii. {CourseID}→ProfessorID:

a. CourseID alone determines the ProfessorID. So, there's a straight arrow from
CourseID to ProfessorID.

iii. {StudentID,Semester}→CourseID:

a. StudentID and Semester together determine the CourseID, which is why there's
an arrow from these attributes to CourseID.

By understanding the functional dependencies and visualizing them through the diagram, you can get insights
into:

• Keys and candidate keys for tables.

• How to optimize the database schema.
• How to proceed with database normalization.
b. Algorithms and Normalization
i. Introduction to Database Normalization

Database Normalization is a technique used to design a relational database schema in such a way that it
reduces redundancy and improves data integrity. The process involves dividing larger tables into smaller, more
manageable tables and linking them with relationships. Each step of normalization is called a "Normal Form,"
and there are several Normal Forms, each with a specific set of rules.
ii. Purpose of Normalization

Elimination of Redundant Data: In an unnormalized database, the same information could be duplicated in
multiple places. Normalization helps in reducing this redundancy by ensuring that each piece of information is
stored only once. This not only saves storage space but also makes the database more efficient.

Example

Consider a table Orders:

OrderID CustomerName ProductName Price Address

1 Alice Laptop 1000 123 Main St

2 Bob Phone 500 456 Elm St

3 Alice Mouse 20 123 Main St

Here, the CustomerName and Address for Alice is repeated. If Alice were to change her address, we would
have to update multiple rows, which is inefficient and error-prone.

Data Integrity: Normalization constraints, like primary and foreign keys, help in maintaining data integrity.
These constraints ensure that there can be no duplicate records, and that all references from one table to
another are valid.

Simplify Queries: Normalized databases are generally easier to query. Since each piece of information is
stored only once, there is less confusion and fewer errors when querying data.

Facilitate Updates: In a normalized database, updating a piece of information doesn't require changes to be
made in multiple places. This makes updates easier to implement and less error-prone.

Increase Scalability: Normalized databases are more scalable. As the amount of data increases, a well-
normalized database can adapt more easily without requiring major changes in the logical structure.

Optimize Indexes: Normalization often results in smaller table sizes, which allows for more efficient use of
indexes and faster query performance.

Improved Security: In a well-normalized database, it may be easier to implement security features. For
example, you could grant permissions on a table-by-table basis, thus providing more granular control over who
can access or modify what data.

Simplify Maintenance: Maintaining a well-structured, normalized database is generally easier. Changes

made to one table can be more readily understood and won't have unpredictable ripple effects throughout the
database.
iii. Functional Dependency and Normalization

In the context of database normalization, a functional dependency is a constraint between two sets of
attributes in a relation (table) from a database. Given a relation R, a set of attributes X in R is said to
functionally determine another set of attributes Y, written as X→Y, if and only if each X value is associated
with precisely one Y value.

For example, in a table of students with columns StudentID and StudentName, the StudentID functionally
determines the StudentName because each StudentID will have exactly one StudentName associated
with it. In mathematical terms, this is expressed as StudentID→StudentName.
Functional dependency plays a critical role in the process of normalization. The different normal forms (1NF,
2NF, 3NF, etc.) are defined based on satisfying certain functional dependency conditions.

Let's consider a simple example to illustrate these concepts. Suppose we have a table named
StudentCourses that contains the following columns:

StudentID, StudentName, CourseID, CourseName

Here's how the table might look:

CREATE TABLE StudentCourses (

StudentID INT,

StudentName VARCHAR(50),

CourseID INT,

CourseName VARCHAR(50),

PRIMARY KEY (StudentID, CourseID)

);

INSERT INTO StudentCourses VALUES (1, 'Alice', 101, 'Math');

INSERT INTO StudentCourses VALUES (1, 'Alice', 102, 'Physics');

INSERT INTO StudentCourses VALUES (2, 'Bob', 101, 'Math');

INSERT INTO StudentCourses VALUES (2, 'Bob', 103, 'Chemistry');

Issues:

• Redundancy: The StudentName and CourseName are repeated for each combination of
StudentID and CourseID.

• Update Anomalies: If Alice changes her name, we have to update multiple rows.

Normalization:

1NF: The table is already in 1NF as it has a primary key (StudentID, CourseID) and atomic attributes.

2NF: To bring it to 2NF, we'll break it into two tables:

• Students: StudentID, StudentName

• Courses: CourseID, CourseName

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

StudentName VARCHAR(50)

);

INSERT INTO Students VALUES (1, 'Alice');

INSERT INTO Students VALUES (2, 'Bob');

 CREATE TABLE Courses (

CourseID INT PRIMARY KEY,

CourseName VARCHAR(50)

);

INSERT INTO Courses VALUES (101, 'Math');

INSERT INTO Courses VALUES (102, 'Physics');

INSERT INTO Courses VALUES (103, 'Chemistry');

3NF: Now, let's create a table for the relationship between students and courses.

CREATE TABLE StudentEnrollments (

StudentID INT,

CourseID INT,

PRIMARY KEY (StudentID, CourseID),

FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

INSERT INTO StudentEnrollments VALUES (1, 101);

INSERT INTO StudentEnrollments VALUES (1, 102);

INSERT INTO StudentEnrollments VALUES (2, 101);

INSERT INTO StudentEnrollments VALUES (2, 103);

The database is now in 3NF. We've removed redundancy and minimized update anomalies, all thanks to
functional dependencies guiding our normalization process.
iv. Anomalies in Unnormalized Data

Anomalies in an unnormalized MySQL database occur due to poor schema design. These anomalies can result
in incorrect data, inconsistencies, and inefficiencies. Here are the main types of anomalies:

Insertion Anomaly

Problems occur when you try to insert data into the database.

Example

Suppose you have a StudentsCourses table like below:

StudentID StudentName CourseID CourseName

1 Alice C1 Math
 1 Alice C2 Science

2 Bob C1 Math

Inserting a new course for a new student would require knowledge of both the student and the course. If the
course is not yet known, the record can't be inserted.

Update Anomaly

Problems occur when you try to update existing data.

Example

Continuing with the StudentsCourses table, suppose you need to update Alice's name to "Alicia". You'll have
to update multiple rows, which is inefficient and error-prone.

StudentID StudentName CourseID CourseName

1 Alicia C1 Math

1 Alicia C2 Science

2 Bob C1 Math

Deletion Anomaly

Problems occur when you try to delete data.

Example

If Alice drops all her courses and you delete all rows concerning Alice, you lose all information about her, even
the fact that she was a student.

After deleting Alice's records:

StudentID StudentName CourseID CourseName

2 Bob C1 Math

Redundancy

Duplicate data could exist, causing inconsistencies.

Example

The StudentName and CourseName columns are redundant because the same names are repeated for
each new course or student.

Inconsistency

Data might become inconsistent if not updated in all places.

Example

If you need to rename a course from "Math" to "Mathematics", you'll have to update multiple rows. If you miss
any, the database will be inconsistent.
c. Normal Forms
Database normalization is a process used to organize a database into tables and columns. The main idea is
that a table should be about a specific topic and only supporting topics included. Let's explore each normal
form along with its requirements and examples:

First Normal Form (1NF):

Requirements:

i. Each table has a primary key: uniquely identifying each row.

ii. All attributes (columns) contain only atomic (indivisible) values.

iii. Each column contains values of a single data type.

iv. The order in which data is stored does not matter.

MySQL Example:

Consider a table which records the courses taken by students:

Student Courses
Alice Math, English
Bob Science, Math
This table is not in 1NF because the Courses column contains multiple values. To bring this to 1NF:

Student Course
Alice Math
Alice English
Bob Science
Bob Math

Second Normal Form (2NF):

Requirements:
i. Table is in 1NF.
ii. No partial dependencies of any column on the primary key.

MySQL Example:

Consider a table recording student scores in various courses:

StudentID, Course StudentName CourseName Score

1, Math Alice Math 90
1, English Alice English 85
2, Science Bob Science 80
Here, the primary key is a combination of StudentID and Course. The CourseName is dependent only on the
Course, not on the complete primary key. This is a partial dependency.

To bring this to 2NF, we split the table:

1. Student table:

StudentID StudentName
1 Alice
2 Bob
2. Scores table:
 StudentID Course Score
1 Math 90
1 English 85
2 Science 80

Third Normal Form (3NF):

Requirements:
i. Table is in 2NF.
ii. No transitive dependencies of any column on the primary key.

MySQL Example:

Consider a table:

StudentID Course Instructor InstructorOffice

1 Math Mr. Smith Room 101
2 Eng Ms. Doe Room 105
Here, InstructorOffice is transitive as it depends on the Instructor, not directly on StudentID.

To bring this to 3NF, we split the table:

1. Courses table:

Course Instructor InstructorOffice

Math Mr. Smith Room 101
Eng Ms. Doe Room 105
2. StudentCourses table:

StudentID Course
1 Math
2 Eng

Boyce-Codd Normal Form (BCNF):

Requirements:

1. Table is in 3NF.

2. For every non-trivial functional dependency X→Y, X is a super key.

MySQL Example:

Consider a table:

StudentID CourseID Instructor

1 101 Mr. Smith
2 102 Mr. Smith
Here, CourseID determines Instructor but CourseID isn't a super key.

To bring this to BCNF, we split the table:

1. Instructors table:

CourseID Instructor
 101 Mr. Smith
102 Mr. Smith
2. StudentCourses table:

StudentID CourseID
1 101
2 102

Fourth Normal Form (4NF):

Requirements:
i. Table is in BCNF.
ii. No multi-valued dependencies.

MySQL Example:

Consider a table:

Student Course Hobby

Alice Math Painting
Alice Math Reading
Bob Eng Swimming
Here, a student's hobbies are independent of courses. This is a multi-valued dependency.

To bring this to 4NF, we split the table:

1. StudentCourses:

Student Course
Alice Math
Bob Eng
2. StudentHobbies:

Student Hobby
Alice Painting
Alice Reading
Bob Swimming

Fifth Normal Form (5NF):

Deals with cases where information can be reconstructed from smaller pieces of information that can be
maintained with less redundancy. A table is said to be in 5NF if and only if every join dependency in it is
implied by the candidate keys.

Requirements for 5NF:

i. The table should be in 4NF.
ii. There should not be any join dependency, unless it is trivial, in which the table can be decomposed and then
recomposed using natural joins without loss of information.

Example:
For a practical example, consider a scenario where we have a table containing information about courses,
instructors, and books. An instructor can teach multiple courses, and for each course, they can choose
different books.

Initial Table (Not in 5NF):

Instructor Course Book

John Math Mathematics
John CS Programming
Jane Math Algebra
Jane CS Algorithms
Here, the information can be broken down into two separate relationships:

Decomposed Tables:

1. Instructor_Course:

Instructor Course
John Math
John CS
Jane Math
Jane CS
2. Course_Book:

Course Book
Math Mathematics
Math Algebra
CS Programming
CS Algorithms
Now, these two tables are in 5NF. The original table can be reconstructed by performing a natural join on
these two tables without loss of information.
d. Integrity Constraints

Integrity constraints are rules applied to database tables to ensure the reliability and accuracy of the data
stored within them. They enforce the correctness of the data and maintain the consistency of the database.

I. Static Integrity Constraints

Static integrity constraints are constraints that must be satisfied at all times, and they are checked whenever
any changes are made to the database.

a. Primary Key Constraints

• Definition: A primary key uniquely identifies each record in a table and must contain unique values. A
table can have only one primary key.

• Example: In a students table, student_id could be a primary key.

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);
b. Unique Constraints

• Definition: A unique constraint ensures that all values in a column are distinct.
 • Example: In a users table, email could have a unique constraint.
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE,
password VARCHAR(255)
);
c. Check Constraints

• Definition: A check constraint verifies that the values in a column satisfy a specific condition.

• Example: In an employees table, salary could have a check constraint to ensure it is always above
0.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2) CHECK (salary > 0)
);
II. Dynamic Integrity Constraints

Dynamic integrity constraints are constraints that are checked at the point of executing a statement, such as a
modification or a deletion.

a. Foreign Key Constraints

• Definition: A foreign key is a column that creates a relationship between two tables. It refers to the
primary key in another table and ensures referential integrity.

• Example: In an orders table, customer_id could be a foreign key referring to customers table.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
b. Triggers for Data Validation

• Definition: Triggers are stored procedures that are automatically executed or fired when a specific
event occurs.

• Example: A trigger can be created to validate data before inserting it into a products table.
CREATE TRIGGER validate_product_price
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
IF NEW.price < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative';
END IF;
END;
c. Assertions for Data Validation

• Definition: Assertions are conditions or Boolean expressions which the database always evaluates as
true for the transactions to be committed.

• Example: An assertion can be created to ensure that the total salary of employees does not exceed a
certain limit.
 CREATE ASSERTION salary_check

CHECK (SELECT SUM(salary) FROM employees <= 1000000);

d. Enforcing Integrity Constraints

• Definition: Enforcing integrity constraints is the process of ensuring that all the defined constraints
are adhered to.

• Example: A database management system (DBMS) automatically enforces integrity constraints like
primary key, unique, check, and foreign key constraints when data is inserted, updated, or deleted
from the database.
Week 3: Database administration
a. Physical implementation of the data

The physical implementation of data refers to how data is stored, managed, and retrieved from disk by the
MySQL Server. This involves understanding the storage engines, table structures, indexing, and partitioning.

Storage Engines

Storage engines are the underlying software components that MySQL uses to create, read, update, and delete
data. Different storage engines offer different features, performance characteristics, and capabilities.

• InnoDB is a transaction-safe storage engine and supports foreign keys, row-level locking, and is
generally used for complex, high-volume transactional applications.

• MyISAM is non-transactional and is generally used for read-heavy, simple applications due to its
speed.

Relationship with other Components :

• Table Structures: The storage engine chosen affects how table structures are stored and managed
on the disk.

• Indexing: Different storage engines implement indexing in various ways, affecting query
performance.
 • Partitioning: Some storage engines support table partitioning, allowing for more efficient data
management and retrieval.

Code example: specifying storage engine

CREATE TABLE example_table (

id INT AUTO_INCREMENT PRIMARY KEY,

data VARCHAR(255)

) ENGINE=InnoDB;

Table Structures

Table structures define how data is organized within tables. They are defined by the columns, data types, and
constraints applied to the tables.

• Indexing: The table structure determines which columns can be indexed and how effectively indexes
can be used.

• Partitioning: The design of table structures can impact the efficiency of partitioning strategies and
data retrieval.

Indexing

Indexes are data structures that improve the speed of data retrieval operations on a database table. They
work similarly to an index in a book.

• Primary Index: Unique index on the primary key.

• Secondary Index: Non-unique index that improves the performance of certain queries.

Code Example: Creating Indexes

-- Creating a secondary index

CREATE INDEX idx_department ON employee (department);

Partitioning

Partitioning is the database process where very large tables are divided into smaller, more manageable pieces,
yet still being treated as a single table. It can improve performance and assist in managing large tables.

• Range Partitioning: Partitioning by a range of values.

• List Partitioning: Partitioning by a list of values.

Code Example: Range Partitioning

CREATE TABLE sales (

sale_id INT AUTO_INCREMENT PRIMARY KEY,

sale_date DATE,

amount DECIMAL(10, 2)

PARTITION BY RANGE( YEAR(sale_date) ) (

PARTITION p0 VALUES LESS THAN (1991),

 PARTITION p1 VALUES LESS THAN (1992),

PARTITION p2 VALUES LESS THAN (1993)

);

Consider the large table storing sales data, structured with columns for date, sales_id, and amount. If you
choose InnoDB as the storage engine, you can leverage its support for partitioning to divide the table by date
ranges, enhancing data retrieval performance for range-based queries. Creating appropriate indexes on
sales_id and date will further optimize query performance, allowing rapid lookups within specific partitions.

b. Structure of the file and index

MySQL organizes its data into different files and directories, each serving a specific purpose. Here’s a typical
organization of MySQL’s database files:

Data Directory

• Database Directories: Each database corresponds to a separate directory.

• Table Files: Every table is represented by one or more files within its database directory.

• .frm files store table definitions.

• .ibd files store table data and indexes for InnoDB tables.

• .MYD and .MYI files are for MyISAM tables: .MYD stores data and .MYI stores indexes.

Log Files

• Error Log: Contains information about errors that occur while the server is running.

• General Query Log: Provides a general record of what MySQL is doing, logging established client
connections and statements received from clients.

• Binary Log: Contains information about data modifications made by each transaction.

Temporary Files

MySQL may create temporary files when performing tasks like sorting large result sets

Socket File

Used for local client connections to the MySQL server

MySQL Indexes

Indexes are used to quickly locate a row in a table without scanning the entire table. The two main types of
indexes in MySQL are:

Primary Index

• It’s a unique index.

• InnoDB tables have a primary index on the primary key column.

Secondary Index

• It’s a non-unique index.

• Helps in improving the performance of read operations.

Full-text Index
 • Used for full-text searches in textual column types like CHAR, VARCHAR, or TEXT.

Example : creating indexes

-- Creating a Primary Index

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(50)

);

-- Creating a Secondary Index

CREATE INDEX idx_name ON employees(name);

-- Creating a Full-text Index

ALTER TABLE employees ADD FULLTEXT(name);

MySQL Database and Index Operations

Viewing Table Structure

To view the structure of a table, use the DESCRIBE statement:

DESCRIBE employees;

Creating a Database
CREATE DATABASE company;

Creating a Table
USE company;

CREATE TABLE department (

department_id INT PRIMARY KEY,

name VARCHAR(50)

);

Adding an Index
-- Adding a Secondary Index to an Existing Table

CREATE INDEX idx_department_name ON department(name);

Dropping an Index
-- Dropping an Index

DROP INDEX idx_department_name ON department;

Optimizing Indexes

Use the OPTIMIZE TABLE command to reclaim unused space and to defragment the data file:

OPTIMIZE TABLE department;

 c. Control of concurrent access

Controlling concurrent access is crucial in MySQL database administration to ensure the integrity and
consistency of data. This involves managing how multiple transactions are processed simultaneously to avoid
conflicts and data corruption.

Transactions

Transactions are sequences of one or more SQL operations executed as a single unit. They follow the ACID
properties:

• Atomicity: The transaction is treated as a single unit, either fully completed or not executed at all.

• Consistency: The database remains in a consistent state before and after the transaction.

• Isolation: Concurrent transactions are isolated from each other.

• Durability: Once committed, the effects of a transaction are permanent.

Example: Starting a Transaction

START TRANSACTION;

Example: Committing a Transaction

COMMIT;

Example: Rolling Back a Transaction

ROLLBACK;

Locks

Locks are mechanisms that prevent multiple transactions from accessing the same data simultaneously,
avoiding conflicts and ensuring data integrity

Shared and Exclusive Locks

• Shared Locks (READ LOCKS): Allow multiple transactions to read a resource but prevent any from
writing to it.

• Exclusive Locks (WRITE LOCKS): Prevent any other transaction from accessing the resource, either
for reading or writing.

Example: Acquiring a Shared Lock

SELECT * FROM employees WHERE employee_id = 1 FOR SHARE;

Example: Acquiring an Exclusive Lock

SELECT * FROM employees WHERE employee_id = 1 FOR UPDATE;

Deadlocks

A deadlock occurs when two or more transactions are waiting for each other to release a lock, causing the
system to stall.

Handling Deadlocks: MySQL automatically detects and resolves deadlocks by choosing one transaction as
the 'victim' and rolling back its operations.

Example: Deadlock Scenario

− Transaction A acquires a lock on row 1.

 − Transaction B acquires a lock on row 2.
− Transaction A tries to acquire a lock on row 2 but is blocked by Transaction B.
− Transaction B tries to acquire a lock on row 1 but is blocked by Transaction A.
Isolation Levels

Isolation levels define the degree to which the operations in one transaction are isolated from those in other
concurrent transactions.

• READ UNCOMMITTED: Allows reading uncommitted changes made by other transactions. It has the
lowest level of isolation.

• READ COMMITTED: Only allows reading committed changes, but the data can change during the
transaction.

• REPEATABLE READ: Ensures that if a value is read, the same value will be seen throughout the
transaction.

• SERIALIZABLE: Guarantees complete isolation between transactions.

Example: Setting Isolation Level

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Optimistic and Pessimistic Concurrency Control

• Optimistic Concurrency Control: Assumes conflicts are rare. Transactions are allowed to proceed
without locking, but conflicts are checked at commit time.

• Pessimistic Concurrency Control: Assumes conflicts are likely. Resources are locked as soon as
they are accessed.

Practical Example: Controlling Concurrent Access

Let's consider a scenario where we have a table named accounts with columns account_id and
balance. Two users, A and B, try to update the balance of the same account concurrently.

Pessimistic Concurrency Control

Here, User A will lock the row for update, preventing User B from modifying it until User A commits or rolls
back the transaction.
-- User A

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1 FOR UPDATE;

-- User B has to wait until User A commits or rolls back.

COMMIT;

Optimistic Concurrency Control

Here, both users can read the value and attempt to update it, but only one will succeed based on a
versioning mechanism.
-- User A

START TRANSACTION;

SELECT balance FROM accounts WHERE account_id = 1; -- Assume balance is 500

 -- User B can also read the value.

UPDATE accounts SET balance = 400 WHERE account_id = 1 AND balance = 500;

-- If User B has changed the balance in the meantime, the update will fail for User A.

COMMIT;

d. Breakdown resistance

Database administration is crucial for ensuring the availability, performance, and security of MySQL databases.
One essential aspect of database administration is developing strategies to make the database resistant to
breakdowns, which typically involve backup and recovery, optimizing performance, and managing user
privileges.

Backup and Recovery

MySQL Dump

MySQL Dump is a utility that performs logical backups by producing a set of SQL statements that can
recreate the original database.

Example:

mysqldump -u [username] -p[password] [database_name] > backup.sql

To restore a database from a dump file:

mysql -u [username] -p[password] [database_name] < backup.sql

Binary Backups

Binary backups consist of copying the actual database files. This method is usually faster than logical
backups.

Example :

cp /var/lib/mysql/[database_name] /path/to/backup/

Performance Optimization

Indexing

Indexes are used to quickly locate a row in a table without scanning the entire table.

Example:

CREATE INDEX idx_columnname ON tablename (columnname);

Query Optimization

Optimizing queries is crucial to improving database performance. Use the EXPLAIN statement to
understand how MySQL processes a query.

Example:

EXPLAIN SELECT * FROM tablename WHERE columnname = 'value';

User Privilege Management

Creating Users
 Creating users with the least required privileges is crucial for securing the database.

Example:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Granting Privileges

After creating a user, assign the necessary privileges using the GRANT statement.

Example:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

Monitoring and Logging

Error Log

MySQL error log contains information about errors that occur while the server is running. Regularly
monitoring the error log can help in identifying and resolving issues promptly.

Example: To view the error log, locate the log file and use a text viewer:

cat /var/log/mysql/error.log

General Query Log

The general query log provides information on client connections and SQL queries.

Example: To enable the general query log, add the following lines to the MySQL configuration file (my.cnf
or my.ini):

[mysqld]

general_log = 1

general_log_file = /var/log/mysql/mysql.log

Replication for High Availability

Master-Slave Replication

Master-slave replication allows the slave to replicate data changes made by the master, providing data
redundancy and read scalability.

Example: On the master, add the following to the configuration file:

[mysqld]

server-id=1

log-bin=mysql-bin

On the slave, add:

[mysqld]

server-id=2

Then, on the master, grant replication privileges to the slave:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_host' IDENTIFIED BY 'password';

Configuring the Slave

 On the slave, execute the following commands to start replication:
CHANGE MASTER TO

MASTER_HOST='master_host',

MASTER_USER='slave_user',

MASTER_PASSWORD='password',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=107;

START SLAVE;

e. Security and protection of data

Securing and protecting data is crucial. We will discuss various topics, including user privilege management,
secure connections, and data encryption.

User Privilege Management

User privilege management is essential for securing MySQL databases. MySQL employs a robust privilege
system that authenticates a user connecting from a host and associates the user with access privileges on a
database.

Create a user with the following SQL command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace username with the desired username and password with a secure password.

Grant necessary privileges to the user:

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

Replace database_name with the name of your database.

To revoke privileges from a user:

REVOKE SELECT, INSERT, UPDATE ON database_name.* FROM 'username'@'localhost';

Secure Connections

To secure connections to the MySQL server, use Secure Sockets Layer/Transport Layer Security (SSL/TLS)
encryption. MySQL supports encrypted connections based on the OpenSSL library.

To enable SSL on MySQL Server running on Windows, you need to specify the server certificates in the my.ini
configuration file:

[mysqld]

ssl-ca=ca-cert.pem

ssl-cert=server-cert.pem

ssl-key=server-key.pem

Data Encryption

Data encryption is a crucial part of data protection. MySQL provides multiple ways to encrypt data, such as
Transparent Data Encryption (TDE) and column-level encryption.
TDE encrypts MySQL tablespace data files. To enable TDE, use the ALTER TABLE statement:

ALTER TABLE table_name ENCRYPTION='Y';

To encrypt specific columns in a table, use the AES_ENCRYPT() function:

INSERT INTO table_name (column_name) VALUES (AES_ENCRYPT('data', 'encryption_key'));

Firewall and Network Security

On Windows, it’s essential to configure Windows Firewall to allow MySQL connections.

To add a rule in Windows Firewall allowing MySQL connections, use the following PowerShell command:

New-NetFirewallRule -DisplayName "MySQL" -Direction Inbound -Protocol TCP -LocalPort 3306 -Action
Allow

Backup and Recovery

Regular backups are essential for data protection. Use mysqldump to create backups of your databases.

To create a backup of your MySQL database on Windows, run the following command in Command Prompt:

mysqldump -u username -p database_name > backup.sql

To restore a backup:

mysql -u username -p database_name < backup.sql

f. Parameter setting, start, stop, save, restoration

Administering MySQL databases also involves managing database parameters, starting and stopping the
server, and performing database save and restoration operations.

Parameter Setting

MySQL parameters are typically set in the my.ini configuration file, located in the MySQL installation directory.
This file allows you to configure various settings related to the MySQL server, such as buffer sizes, log files,
and port numbers.

To change a parameter, open my.ini with a text editor and modify the desired setting. For example, to
change the maximum allowed packet size:

[mysqld]

max_allowed_packet=64M

After making changes, save the file and restart the MySQL server for the changes to take effect.

Starting and Stopping MySQL Server

To start the MySQL server on Windows, open Command Prompt and navigate to the MySQL bin directory, then
run:

mysqld

To stop the MySQL server, open another Command Prompt window and run:

mysqladmin -u root -p shutdown

Database Save and Restoration

To create a backup of a MySQL database on Windows, use the mysqldump command:

 mysqldump -u username -p database_name > backup.sql

Replace username with your MySQL username and database_name with the name of the database you
want to back up.

To restore a backup, use the mysql command:

mysql -u username -p database_name < backup.sql

Monitoring and Logging

Monitoring MySQL performance and logging are crucial for maintaining database health and troubleshooting
issues.

To enable the general log, add the following lines to my.ini:

[mysqld]

general_log=1

general_log_file="C:/path/to/logfile.log"

MySQL’s Performance Schema provides detailed information about server execution. Enable it by adding the
following line to my.ini:

performance_schema=ON

MySQL Service Management

In Windows, MySQL can run as a service, which can start automatically with Windows startup.

To install MySQL as a Windows service, open Command Prompt as an Administrator, navigate to the MySQL
bin directory, and run:

mysqld –install

To start the MySQL service, run:

net start MySQL

To stop the MySQL service, run:

net stop MySQL

g. Distributed database, distributed processing

Distributed databases and distributed processing in MySQL involve splitting data across multiple servers to
improve performance, availability, and fault tolerance.

MySQL NDB Cluster

MySQL NDB Cluster is a high-availability, high-redundancy version of MySQL, designed for distributed
computing environments. It combines the MySQL Server with the NDB storage engine and enables data to be
distributed across multiple nodes for higher availability.

Download the MySQL NDB Cluster package from the official MySQL website and follow the installation
instructions for Windows.

Create a configuration file, config.ini, for the management node:

[ndbd default]

NoOfReplicas=2
 [ndb_mgmd]

hostname=management_node_hostname

datadir=C:\path\to\management\node\data\directory

[ndbd]

hostname=data_node1_hostname

datadir=C:\path\to\data\node1\data\directory

[ndbd]

hostname=data_node2_hostname

datadir=C:\path\to\data\node2\data\directory

[mysqld]

hostname=sql_node_hostname

Start the management node:

ndb_mgmd --initial --config-file=C:\path\to\config.ini

Start the data nodes:

ndbd –initial

Start the MySQL Server (SQL Node):

Mysqld

MySQL Fabric

MySQL Fabric is a framework for managing a collection of MySQL servers. It provides mechanisms to execute
tasks on MySQL servers and organize them into groups, facilitating the distribution of databases.

To install MySQL Fabric, use the MySQL Installer for Windows and select the MySQL Utilities package during
installation.

Create a configuration file, fabric.cfg, for MySQL Fabric:

[fabric]

uuid = fabric_uuid

address = fabric_address:port

[storage]

user = fabric_user

database = fabric_database

Start MySQL Fabric with the command:

 mysqlfabric manage start

Create a new Fabric node:

mysqlfabric manage setup

Define a new shard (a partition of a database):

mysqlfabric sharding create_definition RANGE

Distributed Processing

Distributed processing involves splitting tasks across multiple servers to improve performance and reliability.
MySQL NDB Cluster and MySQL Fabric enable distributed processing by allowing read and write operations to
be distributed across multiple nodes.

When querying distributed databases in MySQL NDB Cluster or MySQL Fabric, the syntax is the same as
querying a regular MySQL database:

SELECT * FROM table_name WHERE condition;

The underlying distributed processing mechanisms ensure that the query is executed across all relevant nodes
and the results are aggregated and returned to the client.
h. Auditing, optimization

Auditing and optimization are integral parts of MySQL Database administration. Auditing helps in maintaining
the security and integrity of the database by tracking user activities and changes made to the database.
Optimization ensures that the database performs efficiently, minimizing the resource usage and response
times.

Auditing in MySQL

Auditing in MySQL can be achieved by using the MySQL Enterprise Audit plugin, which logs the activities
performed by users and the server.

To enable auditing, add the following lines to the my.ini file located in the MySQL installation directory:

[mysqld]

audit_log_file = C:/path/to/audit.log

audit_log_policy = ALL

After saving the changes, restart the MySQL server.

The audit log file specified in the my.ini file will contain all the audit entries. Open the audit log file with any
text editor to view the entries.

Optimization in MySQL

Optimization in MySQL involves optimizing the database schema, queries, and the MySQL server configuration.

Design the database schema efficiently, use appropriate data types, and index the columns that are frequently
searched or sorted. To add an index to a column:

ALTER TABLE table_name ADD INDEX (column_name);

Write efficient SQL queries and use the EXPLAIN statement to understand how MySQL executes a query:

EXPLAIN SELECT * FROM table_name WHERE condition;

Adjust the MySQL server configuration in the my.ini file to optimize the server’s performance. For example, to
set the buffer pool size for InnoDB:

[mysqld]

innodb_buffer_pool_size = 1G

Monitoring and Analyzing Performance

Use performance monitoring tools and analyze the performance data to identify bottlenecks and optimize the
database accordingly.

Enable the Performance Schema to monitor the performance of MySQL server by adding the following line to
the my.ini file:

performance_schema = ON

Enable the slow query log to identify slow-running queries:

[mysqld]
slow_query_log = 1
slow_query_log_file = C:/path/to/slow-query.log
long_query_time = 2
MySQL Workbench provides various tools for monitoring and optimizing MySQL server performance. Connect
to the server using MySQL Workbench and use the ‘Performance Reports’ and ‘Performance Schema Reports’
options to analyze the performance.

Maintenance and Repair

Regularly maintain and repair the databases to ensure optimal performance and reliability.

Optimize tables using the OPTIMIZE TABLE statement:

OPTIMIZE TABLE table_name;

Repair tables using the REPAIR TABLE statement:

REPAIR TABLE table_name;