SQL TRAINING

DIFFERENT DB

Oracle – TOAD, SQL Navigator etc

My SQL

MS SQL Server – Microsoft – Used in our course - SSMS

DB2 – IBM

Sybase

MS Access

SQL COMMANDS
SELECT COMMAND

Select – DML Command - Retrieve data from database table

Or – Is used when you want to apply the filter on One Column

And – Is used when you want to apply filter for two different Columns.

Select * from actor

Select firstName, lastName from actor

Select * from Customers where Country = 'Germany'

Select * from Customers where City = 'London'

Select * from Customers where ContactTitle = 'Sales Representative'

Select * from Customers where City = 'London' or City ='Berlin'

Select * from Customers where Country = 'France' and City = 'Paris'

ORDER BY

Select * from Customers order by Country

Select * from Customers order by ContactTitle

Select * from Customers order by Country Desc

Select * from Customers where Country = 'USA' order by ContactName

TOP COMMAND

select * from customer limit 10

select * from customer order by first_name limit 10

Select * from Customers where Country = 'USA' limit 5

select * from customer where store_id = 1 order by first_name limit 10

IN CLAUSE

Select * From Customers

Where City in ('London','Paris','New York')

Select * From Customers

Where City Not in ('London','Paris','New York')

Select * from Customers where Country='USA' or Country ='Germany' or Country =

'Mexico' order by Country

Select * from Customers where Country In ('USA','Germany','Mexico') order by

Country

Select * from Customers where ContactTitle in ('Owner','Sales

Representative','Marketing Manager') and Country = 'USA'

COMMENTS IN SQL

/*Below query will filter all cities = paris */

select * from Customers where City = ‘Paris’

BETWEEN COMMAND

select * from Products

select * from Products order by UnitPrice desc

select * from Products where UnitPrice between 50 and 150

--Not Between

Select * from Products where UnitPrice Not Between 50 and 150 order by
UnitPrice

LIKE COMMAND FOR PATTER MATCHING

Wild Card Characters

% - Match any number of characters

Select * from Customers where City like 'Bue%'

Select * from Customers where City like 'B%' and Country ='Sweden'

select * from Customers where City like '%Land%'

select * from Customers where City like '%Land'

select * from Customers where City Not like '%land%'

_ - Match single Character

select * from Customers where City like '_ondon'

--Not Matching

Select * from Customers where City Not Like 'b%' order by City

NULL Values

Select ContactName, Region from Customers

Where Region is NULL

Select ContactName, Region from Customers

Where Region is NOT NULL
DISTINCT

Select Distinct Country from Customers

ALIAS – AS

Useful when:

1. There are more than one table involved in a Query - Joins

2. Column Names are lengthy or not Meaningful

3. Two or more Columns are Combined together

4. SQL Functions are used

Renaming Column Names

select ContactName as Customer, ContactTitle as Designation from Customers

select * from Customers

Combining more than one Columns

select concat(first_name,' ',last_name) as FullName from customer

Renaming Tables

Select Column_Name from table_Name as Alias_Name

select [Link], [Link] from Customers as c, Orders as o where

[Link] = [Link]

INSERT COMMAND

A DML command - Used to insert data into an existing table

1. With all the values

Insert into Customers

 values ('AAA','Apple','Steve Jobs','CEO','Street
456','NewYork','NULL','12345','USA','0223444','04456788')

Insert into city

values

('601','new city',60,now(3))

2. With selected values

Insert into Customers (CustomerID,CompanyName,ContactName,City,Country)

values ('M23','Microsoft','Bill Gates','New Jersey','USA')

UPDATE

A DML command - Used to update existing data into an existing table. Update
command can have ‘Where’ or not have ‘Where’

UPDATE Customers
Set ContactTitle = 'CEO'
where CustomerID = 'M23'

Update Customers
set PostalCode = '12345'
where CompanyName = 'Microsoft'

Update Customers
Set ContactTitle='Owner',City='London'
Where CustomerID = 'AAA2'

DELETE

A DML command - Used to delete existing data from an existing table; ‘

Delete from Customers

where CustomerID ='M23'

select * from Customers where CustomerID = 'M23'

Begin/Rollback the Transactions

start transaction;
Update Customers2
Set Country = 'USA'

RollBack;

DELETE AND DROP

Delete from EmployeesBackUp – Delete rows/Data from the Table (DML- Data
Manipulation Language)
DELETE can be rolled back

Drop table EmployeesBackUp – Delete the entire Table from the Database.(DDL-
Data Definition Language)
Can be ROLL BACK in few databases but can’t be ROLL BACK in few
databases

Creating DataBase - DDL

CREATE Database Southwind

Drop Database
Drop database Southwind

CREATING TABLES - DDL

1. Using GUI/Design Editor – Not Recommended

2. Code (SQL) – Recommended

Unicode – English Character + Non-English Characters

UNICODE is a uniform character encoding standard. Unicode defines
encoding for characters in many languages.
Unicode:
A Unicode character takes more bytes to store the data in the
database. To support business to the customers worldwide by
 supporting different languages like Chinese, Japanese, Korean and
Arabic.
Non-Unicode – English Character – 1-byte size

Varchar – English Characters

nvarChar – English + Non English Char

char nchar varchar nvarchar

Unicode fixed- Unicode variable

length can store length can store
both non-Unicode both non-Unicode
Character Non-Unicode Non-Unicode
and Unicode and Unicode
Data Type fixed-length variable length
characters (i.e. characters (i.e.
Japanese, Korean Japanese, Korean
etc.) etc.)

Maximum up to 8,000 up to 4,000 up to 8,000 up to 4,000

Length characters characters characters characters

takes up 1 takes up 2 bytes takes up 2 bytes

Character takes up 1 byte
byte per per Unicode/Non- per Unicode/Non-
Size per character
character Unicode character Unicode character

Storage Actual Length 2 times Actual

n bytes 2 times n bytes
Size (in bytes) Length (in bytes)

use only if you used when data use only if you

use when need Unicode length is need Unicode
data length support such as variable or support such as
is constant the Japanese variable length the Japanese
Usage
or fixed Kanji or Korean columns and if Kanji or Korean
length Hangul characters actual data is Hangul characters
columns due to storage always way less due to storage
overhead than capacity overhead

N stands for National Language Character Set

DATA TYPES IN SQL

char Fixed length

varchar Variable length
tinytext Holds a string with a maximum length of 255 characters
Text Holds a string with a maximum length of 65,535 bytes
Mediumtext Holds a string with a maximum length of 16,777,215
characters
Longtext Holds a string with a maximum length of 4,294,967,295
characters
Bit A bit type value from 1 to 64
tinyint A very small integer. Signed ranging from -128 to 127
and unsigned ranging from 0 to 255
Smallint A small integer. Signed ranging from -32768 to 32767
and unsigned ranging from 0 to 65535
Mediumint A medium integer. Signed ranging from -8388608 to
8388607. Unsigned ranging from 0 to 16777215
bigint A large integer. Signed ranging from -
9223372036854775808 to 9223372036854775807.
Unsigned range is from 0 to 18446744073709551615
int A medium integer. Signed range is from -2147483648 to
2147483647. Unsigned range is from 0 to 4294967295
bool Zero is considered as false. Non-zero values are
considered as true
Float A floating point number
double A normal size floating point number

Create Table using SQL - Columns, Datatype, Constraints - 'Create Table'

Create Table Persons(

FirstName nvarchar(50),
LastName nvarchar(50),
Age int,
Country nvarchar(75)
)

--Inserting values in the table

 Insert into Persons values ('Ram','Prasad',30,'India')

UNION

SQL Union Operation combines the result from 2 or more Sql STATEMENTS

Rules:

1. Each Select statement should have same number of Columns

2. Columns should have same Data Type and they should be in the same order

Select City From Customers

UNION
Select City From Suppliers
Order by City

--Union ALL-

Select City From Customers

UNION All
Select City From Suppliers
Order by City

Select City, Country from Customers

Where Country = 'Germany'
UNION ALL
Select City, Country from Suppliers
Where Country = 'Germany'
Order by City
The result set of UNION does not contain duplicate rows, while the result set
of UNION ALL returns all the rows from both tables.

INTERSECT

It is used to combine two SELECT statements. The Intersect operation returns the common
rows from both the SELECT statements.
SELECT EmployeeName FROM employees
INTERSECT
SELECT EmployeeName FROM newemployees;
TRUNCATE

Difference Between Delete, Truncate and Drop

Drop – DDL Command - It will completely delete the table from the Database.

Drop table Customers

Delete – DML Command – Delete data from existing database table

Delete from Customers

Where Country = ‘Germany’

Truncate – DDL Command - Deletes all data from existing database table
Truncate table Customers

Delete - DML Truncate - DDL

Removes rows from the table with or Removes ALL rows in the table
without condition

Can have WHERE condition based on No WHERE condition possible

which DELETE can be performed

Can be ROLL BACK Can be ROLL BACK in few databases but

can’t be ROLL BACK in few databases

Identity Column retain the identity Identity column is reset to seed

value- will restart

DML Command DDL Command

CAST FUNCTION

The MySQL CAST() function is used for converting a value from one datatype to another specific datatype.

Below data types can be used

Date, DateTime, Time, Char, Signed, Unsigned and Binary

insert into city

values
(601,'city new',60, cast('2024-08-01' as Datetime))
Select * from city

Examples:
SELECT CAST(121 AS CHAR);
SELECT CAST(2-4 AS SIGNED);

KEYS IN MYSQL

 Primary Key - A table can have only one primary key. Primary key must contain UNIQUE
values, and it doesn’t allows NULL value.

 Foreign Key - The foreign key allows us to ensure referential integrity by placing
constraints on data in the related table. It is the column of a table i.e. used to point to
the primary key of another table.

 Unique Key - A group of one or more columns of a table that can uniquely identify a
tuple/record is known as a unique key. It prevents from storing duplicate value in two
records in a column. A unique key can have NULL value.

 Candidate Key - Candidate key is a column or set column that can uniquely identify a
record in a table. A table can contain multiple keys that can uniquely identify the record
so except primary key remaining key are considered as candidate key.

For example, In the EMPLOYEE table, employee_id, employee_license are the keys
which are unique for each Employee. Here Employee_ID is the best suitable for the
Primary key so the employee_license is considered as the candidate key.

 Super Key - Super key is set of fields/columns that can uniquely identify every row in a
table.

For Example, Employee_ID , (Employee_ID , Employee_Name ), Employee_License,

(Employee_ID , Employee_Department) all keys can be super key.
DIFFERENT TYPES OF CONSTRAINTS

NOT NULL CONSTRAINT

A column cannot have Null values if not null constrain is given to that
column. There can be one or more columns with ‘Not Null’ Constraint

Create Table Employee2(

EmployeeID int NOT NULL,
EmployeeName nvarchar(50) NOT NULL,
DateOfBirth date,
Country nvarchar(30)
)

--Add Not null to existing table

Alter Table Employee

Modify Country nvarchar(40) Not Null;

--Delete Not Null

Alter Table Student
Modify Regno int

PRIMARY KEY CONSTRAINT

Does not allow Duplicate and Null values in a column

select * from Customers

Insert into Customers (CustomerID,CompanyName,City) values

('AAA','Apple','Tokoyo') – Throws error as duplicate values are not
allowed on Customer ID which is a PK

Creating a table with Primary Key and Not Null Constraint

Create Table Cars(

ModelNumber int Not NULL Primary Key,
ModelName nvarchar(20) Not NULL,
EngineCapacity nvarchar(20),
Color nvarchar(20)
)
 Adding Primary Key constraint to existing column/attribute
Alter Table Cars
Add Primary Key(ModelNumber)

Deleting Primary Key Constraint

ALTER TABLE Employee DROP PRIMARY KEY;

UNIQUE KEY CONSTRAINT

Primary Key Unique Key

Do not allow Duplicate Values Do not allow Duplicate Values

It can be declared only for one PK in Can be declared for multiple Columns
a table

Used to establish relation between Cannot used to establish relation

tables between tables

Does not allow Null values Allows Null value

Used for Joins Cannot be Joins

Creating Table with Unique Key Constraint

Create Table Laptops(

ProductID int Not null Unique,
ProductName nvarchar(50) Not Null,
Price int Unique
)
select * from Laptops

Insert into Laptops

Values ('1','Lenevo',NULL)

Insert into Laptops

Values ('1','Apple',70000)

ERROR

Add Unique Key to existing table

ALTER TABLE Persons

ADD UNIQUE (ID);

--Delete Unique
Alter Table Bikes
Drop Constraint columnName

FOREIGN KEY CONSTRAINT

A foreign key is a column (or columns) that references a column (most often
the primary key) of another table. The purpose of the foreign key is to ensure
referential integrity of the data. In other words, only values that are
supposed to appear in the database are permitted.

Creating Table with Foreign Key

Create Table Orders(

OrderID int NOT NULL Primary Key,
CustomerName nvarchar(50) NOT NULL,
ProductID int,
Foreign Key(ProductID) References products(ProductID)
)

select * from Orders

Insert into Orders Values
('111','45','5')

select * from Mobiles2

Adding Foreign Key to existing Table

ALTER TABLE orders2

ADD CONSTRAINT fk_pro

FOREIGN KEY (productID)

REFERENCES products(productID);

Referential integrity is a relational database concept, which states that

table relationships must always be consistent. In other words, any foreign key
field must agree with the primary key that is referenced by the foreign key.

--Delete FK from a table

ALTER TABLE Orders2

DROP FOREIGN KEY fk_pro;

--------------------On Delete Cascade-------------

CREATE TABLE Payment (

payment_id int(10) PRIMARY KEY NOT NULL,

emp_id int(10) NOT NULL,

amount float NOT NULL,

payment_date date NOT NULL,

FOREIGN KEY (emp_id) REFERENCES Employee (emp_id) ON DELETE CASCADE

);

-If the record is deleted from the employees table, the record is
automatically deleted from payment table

Default

The DEFAULT constraint is used to provide a default value for a column. The
default value will be added to all new records IF no other value is specified.

CREATE TABLE Customers (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
 Age int,
City varchar(255) DEFAULT 'Sandnes'
);

Insert into ProductOrders

(OrderID,OrderNumber, ProductID) values
('1','3456','55')

-----Add default value to existing table--------

ALTER TABLE employee

ALTER country SET DEFAULT 'USA';

Drop Constraint

To Remove Default Constraint from a Column

Sytax:

ALTER TABLE employee

ALTER country DROP DEFAULT;

----------------------------------------Check Constraint-----------------------------------------
It is used to limit the values that can be entered in a column.
Example:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City navrchar(30),
CHECK (Age>=18)
);
Or
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=new York)

Alter table for check constraint

ALTER TABLE Persons
ADD CHECK (Age>=18);
ALTER TABLE

It is DDL - To alter table Structure -columns/attributes /constraints

1. Add Primary Key constraint

2. Add Not null constraint
3. Add Unique Key constraint
4. Add Default Constraint
5. Modify datatype and datasize
6. Add column
7. Remove Column
8. Remove Constraint - Primary Key, Not Null, Unique, Foreign Key

Adding New column/Attribute

Alter Table Cars
ADD DateOfManufacturing date

Dropping existing column/attribute

ALTER TABLE Customers
DROP COLUMN Fax;

DESCRIBE TABLE

DESCRIBE means to show the information in detail. Since we have tables in MySQL, so we will
use the DESCRIBE command to show the structure of our table, such as column names,
constraints on column names, etc. The DESC command is a short form of the DESCRIBE
command.

Syntax:

Desc <TableName>

AUTO INCREMENT

Auto increment can be used for generating key values. The identity property on a column
guarantees the following:
Creating table with auto increment
Create Table Mobiles2(
ProductID int AUTO_INCREMENT Primary Key,
MobileName nvarchar(50) Not NULL,
OperatingSystem nvarchar(50),
Price int
)

Delete from Mobiles2

Insert into Mobiles2

Values ('HTC','Android','35000')

Insert into Mobiles2

Values ('iphone5s','IOS','50000')

Truncate table Mobiles2

Insert into Mobiles2

Values ('Nokia','Windows','20000')
Insert into Mobiles2
Values ('BlackBerry','BB','40000')

Difference between Delete and Truncate

Delete – Retains the previous Identity column value

Truncate – Identity Starts from beginning (Seed value)

Setting the starting value

ALTER TABLE mobiles2 AUTO_INCREMENT=100;

IMPORT/EXPORT DATA

We can Import and export database using the SQL workbench. Follow the below steps to export a database

 Click on Server  Data Export

 Select the Database to export

 Select the tables

 Select the option ‘Export to self contained file’

 Click on Export progress tab and click on Start Export

Importing Database

 Click on Server  Data Import

 Select the option ‘Import from self contained file’

 Select target schema or create a new one

 Click in Import progress tab and click on Start Import

JOINS
1. Inner Join
2. Left Join
3. Right Join
4. Self Join
5. Full outer Join

INNER JOIN

Returns intersection of two tables i.e selects records that have matching
values in both tables.

Select [Link],[Link],[Link],[Link]
from Employee as e
Inner Join Department as d ON [Link] = [Link]

Select [Link],[Link],[Link],[Link],[Link]
from Employee as e
Inner Join Department as d ON [Link] = [Link]
LEFT JOIN

All the rows from the left table and the matched records from the right table.
The result is NULL from the right side, if there is no match

Select [Link], [Link], [Link], [Link]

From Employee as emp
Left Outer Join Department as dept
On [Link] = [Link]

RIGHT JOIN

All the rows from the right table and the matched records from the left table
(table1). The result is NULL from the left side, when there is no match.

Select [Link], [Link], [Link], [Link], [Link]

From Employee as e
Right Outer Join Department as dept
on [Link] = [Link]

FULL OUTER JOIN

All the rows when there is a match in either left (table1) or right (table2) table records. MySQL
does not have explicit full join command. It is done by combining left join, right join and union
operations.

SELECT * FROM Employees as e

LEFT JOIN Departments as d ON [Link] = [Link]

UNION

SELECT * FROM Employees as e

RIGHT JOIN Departments as d ON [Link] = [Link]

More Than two table

Select [Link],[Link],[Link],[Link]
From Customers as C
Inner Join Orders as o
on [Link] = [Link]
Inner Join Employees as e
on [Link] = [Link]

SELF JOIN
A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just
write an ordinary join where both tables involved in the join are the same table. One thing to
notice is that when you are self joining it is necessary to use an alias for the table otherwise the
table name would be ambiguous.
It is useful when you want to correlate pairs of rows from the same table, for example a parent
- child relationship.
select [Link],
[Link],
[Link],
[Link] as SupervisorFirstName
from EmployeeList e1
inner join EmployeeList e2 on [Link] = [Link]

EQUI AND NON EQUI JOINS

EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables. EQUI
JOIN also create JOIN by using JOIN with ON and then providing the names of the columns with
their relative tables to check equality using equal sign (=).

Equi join can be done using ‘On’ or ‘=’

Example:
Select [Link], [Link], [Link], [Link]

From Employees as emp, Departments as dept

where [Link] = [Link]

Non-Equi join
NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >, <,
>=, <= <>, between conditions.
SELECT *

FROM city

INNER JOIN country ON [Link] <> [Link]

SQL FUNCTIONS
SQL functions are simply sub-programs which are used for processing or
manipulating data.

SQL AGGREGATE FUNCTIONS

This returns a single values based on the calculation from a column

Sum()
Avg()
Max()
Min()
Count()

--SUM()
Select SUM(UnitPrice) as Total from Products

--Average
Select AVG(UnitPrice) as Average from Products

Select ProductName, UnitPrice from Products

Where UnitPrice > (select AVG(UnitPrice) from Products)

--Max()

Select MAX(UnitPrice) as MaximumPrice from Products

-- Min()
Select MIN(UnitPrice) as MinimumPrice from Products

Select UnitPrice from Products

where UnitPrice =(Select MAX(UnitPrice) from Products) or UnitPrice = (Select
MIN(UnitPrice) from Products)

--Number of Rows in a Table

Select COUNT(*) as NumberOFRows from Products

--Round()
select * from Products
Select ProductName, ROUND(UnitPrice,0) from Products

FirstParameter  Column Name

SecondParameter  Number of decimals to be returned.

SQL STRING FUNCTION

This returns value for each row based on the input values

Upper()
Lower()
Left()
Right()
Mid()
Length()

Select * from Customers

--UPPER()
Select UPPER(CompanyName) as CompanyName from Customers

--Lower()
Select LOWER(CompanyName) as CompanyName from Customers

-- LEFT()

Select LEFT(City,3) as smallCityName from Customers

-- Right()
 Select Right(City,3) as smallCityName from Customers

--Mid()
Select mid(name,2,2) from language

Where first 2 is the starting index

Second 2 is number of characters after the starting index

Length
Select ContactName, LENGTH(Address) as LengthOfAddress from Customers

Select ContactName, LENGTH(CustomerID) as LengthOfCI

From Customers
Where LENGTH(CustomerID) > 5

3. SQL Date Functions

1. CURDATE() – To get current System Date and time

--getDate()
Select ProductName,UnitPrice,GetDate() as DateAndTime from Products

2. Day, Month, Now, Year - Used to get Partial of Date

Select mid(name,2,2), Month(curdate()) from language

3. DateDiff() - This will calculate number of Days between any two Dates

DATEDIFF(DATE1, DATE2)

Select mid(name,2,2), DateDiff(curdate(), "2024-06-03") from language

GROUP BY
The GROUP BY Statement in SQL is used to arrange identical data into groups
with the help of some functions. i.e if a particular column has same values in
different rows then it will arrange these rows in a group.

The GROUP BY statement is often used with aggregate functions (COUNT, MAX,
MIN, SUM, AVG) to group the result-set by one or more columns.

Select * from Orders

Select * from Employees

Select [Link], COUNT([Link]) as NumberOFCities

From country as cou
inner join city as c
on [Link] = [Link]
Group by Name

Having Clause

Where clause cannot be used with aggregate functions. Whereas Having clause
can be used with aggregate functions

Select [Link], COUNT([Link]) as NumberOfORders

From Employees as E
Inner Join
Orders as O
On [Link] = [Link]
Group by [Link]
Having Count([Link]) > 50

--Having and Where Clause

Select [Link], COUNT([Link]) as NumberOFCities

From country as cou

inner join city as c

on [Link] = [Link]

where [Link] like 'A%'

Group by Name
having count([Link]) >2

SQL SUBQUERIES
Also called as inner Query, Nested Query or Query within a Query

Rules:
1. SubQuery is always written within ()

2. SubQuery returning only one value to the main query we should use ‘ = , > ,
<’

3. SubQueries returning more than one value should be used with ‘IN’ clause

4. Order by cannot be used with SubQueries

UNCORRELATED SUBQUERY

Select ProductName, UnitPrice

From Products
Where UnitPrice = (Select AVG(UnitPrice) from Products);

Select ProductName, UnitPrice

From Products
Where UnitPrice = (Select MAX(UnitPrice) from Products)
or
UnitPrice = (Select MIN(UnitPrice) from Products)

select name from city where countrycode in (select code from country where
name = 'Vietnam' or name = 'poland')

CORRELATED SUBQUERY

A correlated subquery is a subquery that refers to a column from the outer query. The subquery is executed
repeatedly for each row of the outer query, using the values from the current row to perform the subquery. The
result of the subquery is then used in the evaluation of the outer query.

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the
outer query.

Example:

Select productName, (select count(productID) from orders where productID =

[Link]) as numberOfOrders
from Products

SQL VIEWS

A View is virtual table which is formed from result set of a SQL statement. Fields in View are fields from one or
more Tables in DB

- Order by clause cannot used in Views

--Creating View
Create View Product_List as
Select ProductID , ProductName
From Products
Where Discontinued = 0;

Create View CustomerOrders As

Select [Link], [Link],[Link],[Link]
From Customers as C
Left Join
Orders as o
On [Link] = [Link];

--Open View
Select * from Product_List;

Create View Products_More_than_AvgPrice As

Select ProductName, UnitPrice
From Products
Where UnitPrice > (Select AVG(UnitPrice) from Products);

Select * from Products_More_than_AvgPrice

--Modify View

Create or replace View Product_list as

Select ProductID , ProductName, price
From Products
Where price >100;
Select * from Product_list

--Drop View from DB

Drop View Product_List

INDEXES

An index is a data structure that allows us to add indexes in the existing table. It enables you to
improve the faster retrieval of records on a database table. We use it to quickly find the record
without searching each row in a database table whenever the table is accessed.

Before creating Index:

Explain select * from country where Continent = 'Asia'

Creating Index:
CREATE INDEX continent ON country (Continent);

After creating Index:

Explain select * from country where Continent = 'Asia'

To see index of a table:

SHOW INDEXES FROM student;

Drop Index:
ALTER TABLE country

DROP INDEX continent;

STORED PROCEDURES

A stored procedure is a prepared SQL query that can be saved and executed when ever
required. If there are queries that are used very frequently, then those queries can be saved as
stored procedures. Procedures also allow to send parameters.

Advantages:

o Stored Procedure increases the performance of the applications. Once stored

procedures are created, they are compiled and stored in the database.
 o Stored procedure reduces the traffic between application and database server. Because
the application has to send only the stored procedure's name and parameters instead of
sending multiple SQL statements.

o Stored procedures are reusable and transparent to any applications.

CREATING A STORED PROCEDURE

DELIMITER // -- changing the default delimiter to // instead of ;

CREATE PROCEDURE GetAllProducts()

BEGIN

SELECT * FROM products;

END //

DELIMITER ;

CALLING A STORED PROCEDURE

call Getcountry()

CREATING STORED PROCEDURES WITH PARAMETERS

DELIMITER //

CREATE PROCEDURE GetSinglecountry(in countryName nvarchar(50))

BEGIN

SELECT * FROM country where name = countryName;

END //

DELIMITER ;

call GetSinglecountry('japan')

REMOVING PROCEDURE

Drop procedure <procedureName>

DIFFERENCE BETWEEN STORED PROCEDURES AND FUNCTIONS

Functions Stored Procedure

A function has a return type and returns a A procedure does not have a return type
value.
You cannot call stored procedures from a You can call a function from a stored
function procedure.
You can call a function using a select You cannot call a procedure using select
statement. statements.
You cannot use a function with Data You can use DML queries such
Manipulation queries. Only Select queries are as insert, update, select etc… with procedures.
allowed in functions.

FUNCTIONS

The CREATE FUNCTION statement is used for creating a stored function and user-defined
functions. A stored function is a set of SQL statements that perform some operation and return
a single value.

Just like Mysql in-built function, it can be called from within a Mysql statement.

By default, the stored function is associated with the default database.

Example:
DELIMITER //

CREATE FUNCTION no_of_years(date1 date)

RETURNS int READS SQL DATA

BEGIN

DECLARE date2 DATE;

Select current_date()into date2;

RETURN year(date2)-year(date1);

END

//

DELIMITER ;

select EmployeeName, no_of_years(DOJ) as numbOfYears from employees

READS SQL DATA

DETERMINISTIC

No SQL

---------------------------------------------------------------------END-----------------------------------------------------------