MYSQL Practical Tutorial.pdf

MySQL – KEYWORDS, SYNTAX & EXAMPLES
Page 1 of 103
rmmakaha@gmail.com
Practice File............................................................................................................................................................3
SELECT Syntax .....................................................................................................................................................4
SELECT DISTINCT Syntax ..................................................................................................................................4
WHERE Syntax......................................................................................................................................................5
UPDATE Syntax ....................................................................................................................................................6
DELETE Syntax.....................................................................................................................................................7
DELETE a Table ....................................................................................................................................................8
AND Syntax ...........................................................................................................................................................9
OR Syntax ..............................................................................................................................................................9
NOT Syntax............................................................................................................................................................9
ORDER BY Syntax..............................................................................................................................................11
INSERT INTO Syntax..........................................................................................................................................13
IS NULL Syntax...................................................................................................................................................14
IS NOT NULL Syntax..........................................................................................................................................15
LIMIT Syntax.......................................................................................................................................................16
MIN () Syntax.......................................................................................................................................................17
MAX () Syntax.....................................................................................................................................................17
COUNT() Syntax..................................................................................................................................................18
AVG() Syntax.......................................................................................................................................................18
SUM() Syntax.......................................................................................................................................................18
LIKE Syntax.........................................................................................................................................................20
Wildcard Characters .............................................................................................................................................23
IN Syntax..............................................................................................................................................................25
BETWEEN Syntax...............................................................................................................................................26
Alias Column Syntax............................................................................................................................................28
Alias Table Syntax................................................................................................................................................28
INNER JOIN Syntax ............................................................................................................................................32
How to join three tables in SQL query – MySQL Example .................................................................................34
LEFT JOIN Syntax...............................................................................................................................................38
RIGHT JOIN Syntax ............................................................................................................................................39
CROSS JOIN Syntax............................................................................................................................................40
Self Join Syntax....................................................................................................................................................41
SUBQUERIES Syntax: ........................................................................................................................................42
UNION Syntax .....................................................................................................................................................48
UNION ALL Syntax ............................................................................................................................................48
GROUP BY Syntax..............................................................................................................................................51
HAVING Syntax ..................................................................................................................................................52
EXISTS Syntax ....................................................................................................................................................54
ANY Syntax .........................................................................................................................................................55
ALL Syntax With SELECT..................................................................................................................................56

Page 2 of 103
rmmakaha@gmail.com
SELECT INTO Syntax.........................................................................................................................................59
INSERT INTO SELECT Syntax..........................................................................................................................61
IF() Syntax............................................................................................................................................................63
CASE Syntax........................................................................................................................................................65
MySQL IFNULL() Function ................................................................................................................................66
MySQL COALESCE() Function..........................................................................................................................67
Single Line Comments .........................................................................................................................................67
MySQL Operators ................................................................................................................................................69
CREATE DATABASE Syntax ............................................................................................................................71
DROP DATABASE Syntax .................................................................................................................................71
CREATE TABLE Syntax.....................................................................................................................................72
Create Table Using Another Table Syntax ...........................................................................................................73
DROP TABLE Syntax..........................................................................................................................................73
TRUNCATE TABLE Syntax...............................................................................................................................74
ALTER TABLE - ADD Column syntax ..............................................................................................................74
ALTER TABLE - DROP COLUMN syntax........................................................................................................75
ALTER TABLE - RENAME COLUMN syntax..................................................................................................75
ALTER TABLE - MODIFY COLUMN syntax...................................................................................................75
CONSTRAINTS syntax .......................................................................................................................................78
NOT NULL Constraint.........................................................................................................................................79
UNIQUE Constraint .............................................................................................................................................80
PRIMARY KEY Constraint .................................................................................................................................82
FOREIGN KEY Constraint ..................................................................................................................................84
CHECK Constraint...............................................................................................................................................87
DEFAULT Constraint ..........................................................................................................................................89
CREATE INDEX Statement ................................................................................................................................90
CREATE INDEX Syntax .....................................................................................................................................91
DROP INDEX Syntax..........................................................................................................................................92
AUTO_INCREMENT Keyword..........................................................................................................................92
MySQL Dates.......................................................................................................................................................93
MySQL Views......................................................................................................................................................95
CREATE VIEW Syntax .......................................................................................................................................95
CREATE OR REPLACE VIEW Syntax..............................................................................................................96
DROP VIEW Syntax............................................................................................................................................97
MySQL String Functions......................................................................................................................................98
MySQL Numeric Functions .................................................................................................................................99
MySQL Date Functions......................................................................................................................................101
MySQL Advanced Functions .............................................................................................................................103

Page 3 of 103
rmmakaha@gmail.com
Practice File
Download the file using the link below: Open and Run it, using any of your database tools,
such as:
1. MySQL Workbench
2. HeidiSQL
Then, start practicing.
Link:
https://www.mediafire.com/file/bbppzu7d03nx3h4/MySQLSchool.sql/file

Page 4 of 103
rmmakaha@gmail.com
SELECT Syntax
SELECT column1, column2,...
FROM table_name;
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
Here, column1, column2, ... are the field names of the table you want to select data from.
If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;
Example
SELECT CustomerName, City, Country FROM Customers;
SELECT * FROM Customers;
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2,...
FROM table_name;
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to
list the different (distinct) values.
SELECT DISTINCT Country FROM Customers;
The above SQL statement selects only the DISTINCT values from the "Country" column in the
"Customers" table:

Page 5 of 103
rmmakaha@gmail.com
The following SQL statement counts and returns the number of different (distinct) countries in
the "Customers" table:
Example
SELECT COUNT(DISTINCT Country) FROM Customers;
WHERE Syntax
SELECT column1, column2,... FROM table_name
WHERE condition;
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition
Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE,
DELETE, etc.!
The following SQL statement selects all the customers from "Mexico":
Example:
SELECT * FROM Customers
WHERE Country = 'Mexico';
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double
quotes.
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM Customers WHERE CustomerID = 1;

Page 6 of 103
rmmakaha@gmail.com
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2,...
WHERE condition;
The UPDATE statement is used to modify the existing records in a table.
Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE
statement. The WHERE clause specifies which record(s) that should be updated. If you omit the
WHERE clause, all records in the table will be updated!
The following SQL statement updates the first customer (CustomerID = 1) with a new contact
person and a new city.
Example:
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

Page 7 of 103
rmmakaha@gmail.com
UPDATE Multiple Records
It is the WHERE clause that determines how many records will be updated.
The following SQL statement will update the PostalCode to 00000 for all records where country
is "Mexico":
Example
UPDATE Customers
SET PostalCode = 00000
WHERE Country = 'Mexico';
Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
Example
UPDATE Customers SET PostalCode = 00000;
DELETE Syntax
DELETE FROM table_name
WHERE condition;
The DELETE statement is used to delete existing records in a table.
Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE
statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE
clause, all records in the table will be deleted!

Page 8 of 103
rmmakaha@gmail.com
The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers"
table:
Example
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact:
DELETE FROM table_name;
The following SQL statement deletes all rows in the "Customers" table, without deleting the
table:
Example
DELETE FROM Customers;
The MySQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
DELETE a Table
To delete the table completely, use the DROP TABLE statement:
Example
Remove the Customers table:
DROP TABLE Customers;

Page 9 of 103
rmmakaha@gmail.com
The MySQL AND Operator
AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3
...;
The MySQL OR Operator
OR Syntax
FROM table_name
WHERE condition1 OR condition2 OR condition3
...;
The MySQL NOT Operator
NOT Syntax
FROM table_name
WHERE NOT condition;

Page 10 of 103
rmmakaha@gmail.com
AND Example
The following SQL statement selects all fields from "Customers" where country is "Germany"
AND city is "Berlin":
Example
WHERE Country = 'Germany' AND City = 'Berlin';
OR Example
The following SQL statement selects all fields from "Customers" where city is "Berlin" OR
"Stuttgart":
Example
WHERE City = 'Berlin' OR City = 'Stuttgart';
Example
OR "Spain":
WHERE Country = 'Germany'
OR Country = 'Spain';
NOT Example
The following SQL statement selects all fields from "Customers" where country is NOT
"Germany":
Example
WHERE NOT Country = 'Germany';

Page 11 of 103
rmmakaha@gmail.com
Combining AND, OR and NOT
You can also combine the AND, OR and NOT operators.
AND city must be "Berlin" OR "Stuttgart" (use parenthesis to form complex expressions):
Example
WHERE Country = 'Germany'
AND (City = 'Berlin'
OR City = 'Stuttgart');
The following SQL statement selects all fields from "Customers" where country is NOT
"Germany" and NOT "USA":
Example
WHERE NOT Country = 'Germany'
AND NOT Country = 'USA';
ORDER BY Syntax
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.

Page 12 of 103
rmmakaha@gmail.com
ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" column:
Example
SELECT * FROM Customers ORDER BY Country;
ORDER BY DESC Example
The following SQL statement selects all customers from the "Customers" table, sorted
DESCENDING by the "Country" column:
Example
SELECT * FROM Customers ORDER BY Country DESC;
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" and the "CustomerName" column. This means that it orders by Country, but if some
rows have the same Country, it orders them by CustomerName:
Example
ORDER BY Country, CustomerName;
ORDER BY Several Columns Example 2
The following SQL statement selects all customers from the "Customers" table, sorted ascending
by the "Country" and descending by the "CustomerName" column:
Example
ORDER BY Country ASC, CustomerName DESC;

Page 13 of 103
rmmakaha@gmail.com
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3, ...)
VALUES
(value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the
column names in the SQL query. However, make sure the order of the values is in the same
order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES
INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:
Example:
INSERT INTO Customers
(CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
Did you notice that we did not insert any number into the CustomerID field? The CustomerID column is an
auto-increment field and will be generated automatically when a new record is inserted into the table.

Page 14 of 103
rmmakaha@gmail.com
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the
"CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):
Example
INSERT INTO Customers
(CustomerName, City, Country)
VALUES
('Cardinal', 'Stavanger', 'Norway');
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names FROM table_name
WHERE column_name IS NULL;

Page 15 of 103
rmmakaha@gmail.com
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
The IS NULL Operator
The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.
The IS NOT NULL Operator
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Page 16 of 103
rmmakaha@gmail.com
The MySQL LIMIT Clause
The LIMIT clause is used to specify the number of records to return.
The LIMIT clause is useful on large tables with thousands of records. Returning a large number
of records can impact performance.
LIMIT Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
MySQL LIMIT Examples
The following SQL statement selects the first three records from the "Customers" table:
Example
LIMIT 3;
ADD a WHERE CLAUSE
The following SQL statement selects the first three records from the "Customers" table, where the
country is "Germany":
Example
WHERE Country='Germany'
LIMIT 3;

Page 17 of 103
rmmakaha@gmail.com
MySQL MIN () and MAX () Functions
The MIN () function returns the smallest value of the selected column.
The MAX () function returns the largest value of the selected column.
MIN () Syntax
SELECT MIN (column_name)
FROM table_name
WHERE condition;
MAX () Syntax
SELECT MAX (column_name)
FROM table_name
WHERE condition;
MIN () Example
The following SQL statement finds the price of the cheapest product:
Example:
SELECT MIN(Price)
AS SmallestPrice
FROM Products;

Page 18 of 103
rmmakaha@gmail.com
MAX () Example
The following SQL statement finds the price of the most expensive product:
Example
SELECT MAX(Price)
AS LargestPrice
FROM Products;
MySQL COUNT(), AVG() and SUM() Functions
COUNT() Syntax
The COUNT () function returns the number of rows that matches a specified criterion.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG() Syntax
The AVG () function returns the average value of a numeric column.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SUM() Syntax
The SUM () function returns the total sum of a numeric column.
SELECT SUM(column_name)
FROM table_name
WHERE condition

Page 19 of 103
rmmakaha@gmail.com
COUNT() Example
The following SQL statement finds the number of products:
Example
SELECT COUNT(ProductID)
FROM Products;
Note: NULL values are not counted.
AVG() Example
The following SQL statement finds the average price of all products:
Example
SELECT AVG(Price)
FROM Products;
Note: NULL values are ignored.
SUM() Example
The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:
Example
SELECT SUM(Quantity)
FROM Order_Details;
Note: NULL values are ignored.

Page 20 of 103
rmmakaha@gmail.com
The MySQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
The percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
FROM table_name WHERE column
LIKE pattern;
Tip: You can also combine any number of conditions using AND or OR operators.
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator Description
WHERE CustomerName
LIKE 'a%'
Finds any values that start with "a"
WHERE CustomerName
LIKE '%a'
Finds any values that end with "a"
WHERE CustomerName
LIKE '%or%'
Finds any values that have "or" in any position
WHERE CustomerName
LIKE '_r%'
Finds any values that have "r" in the second position
WHERE CustomerName
LIKE 'a_%'
Finds any values that start with "a" and are at least 2
characters in length
WHERE CustomerName
LIKE 'a__%'
Finds any values that start with "a" and are at least 3
WHERE ContactName
LIKE 'a%o'
Finds any values that start with "a" and ends with "o"

Page 21 of 103
rmmakaha@gmail.com
SQL LIKE Examples
The following SQL statement selects all customers with a CustomerName starting with "a":
Example:
WHERE CustomerName
LIKE 'a%';
The following SQL statement selects all customers with a CustomerName ending with "a":
Example:
WHERE CustomerName
LIKE '%a';
The following SQL statement selects all customers with a CustomerName that have "or" in any
position:
Example:
WHERE CustomerName
LIKE '%or%';
The following SQL statement selects all customers with a CustomerName that have "r" in the
second position:
Example :
WHERE CustomerName
LIKE '_r%';
The following SQL statement selects all customers with a CustomerName that starts with "a" and
are at least 3 characters in length:

Page 22 of 103
rmmakaha@gmail.com
Example:
WHERE CustomerName
LIKE 'a__%'
The following SQL statement selects all customers with a ContactName that starts with "a" and
ends with "o":
Example:
WHERE ContactName
LIKE 'a%o';
The following SQL statement selects all customers with a CustomerName that does NOT start
with "a":
Example:
WHERE CustomerName
NOT LIKE 'a%';

Page 23 of 103
rmmakaha@gmail.com
Wildcard Characters
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE
clause to search for a specified pattern in a column.
Wildcard Characters in MySQL
Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
The wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator Description
WHERE CustomerName
LIKE 'a%'
Finds any values that starts with "a"
WHERE CustomerName
LIKE '%a'
Finds any values that ends with "a"
WHERE CustomerName
LIKE '%or%'
Finds any values that have "or" in any position
WHERE CustomerName
LIKE '_r%'
Finds any values that have "r" in the second position
WHERE CustomerName
LIKE 'a_%_%'
Finds any values that starts with "a" and are at least 3
WHERE ContactName
LIKE 'a%o'
Finds any values that starts with "a" and ends with "o"
Using the % Wildcard
The following SQL statement selects all customers with a City starting with "ber":
Example:
WHERE City
LIKE 'ber%';

Page 24 of 103
rmmakaha@gmail.com
The following SQL statement selects all customers with a City containing the pattern "es":
Example
WHERE City
LIKE '%es%';
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character,
followed by "ondon":
Example
WHERE City
LIKE '_ondon';
The following SQL statement selects all customers with a City starting with "L", followed by any
character, followed by "n", followed by any character, followed by "on":
Example
WHERE City
LIKE 'L_n_on';

Page 25 of 103
rmmakaha@gmail.com
The MySQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
FROM table_name
WHERE column_name
IN (value1, value2, ...);
Or:
FROM table_name
WHERE column_name
IN (SELECT STATEMENT);
IN Operator Examples
Example:
The following SQL statement selects all customers that are located in "Germany", "France" or
"UK":
WHERE Country
IN ('Germany', 'France', 'UK');

Page 26 of 103
rmmakaha@gmail.com
Example
The following SQL statement selects all customers that are NOT located in "Germany", "France"
or "UK":
WHERE Country
NOT IN ('Germany', 'France', 'UK');
Example
The following SQL statement selects all customers that are from the same countries as the
suppliers:
WHERE Country
IN (SELECT Country FROM Suppliers);
The MySQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or
dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
FROM table_name
WHERE column_name
BETWEEN value1 AND value2;
BETWEEN Example
Example:
The following SQL statement selects all products with a price between 10 and 20:
SELECT * FROM Products
WHERE Price
BETWEEN 10 AND 20;

Page 27 of 103
rmmakaha@gmail.com
NOT BETWEEN Example
Example
To display the products outside the range of the previous example, use NOT BETWEEN:
WHERE Price
NOT BETWEEN 10 AND 20;
BETWEEN with IN Example
Example
The following SQL statement selects all products with a price between 10 and 20. In addition; do
not show products with a CategoryID of 1,2, or 3:
WHERE Price
BETWEEN 10 AND 20 AND CategoryID NOT IN
(1,2,3);
BETWEEN Text Values Example
Example
The following SQL statement selects all products with a ProductName between "Carnarvon
Tigers" and "Mozzarella di Giovanni":
WHERE ProductName
BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni'
ORDER BY ProductName;
Example
The following SQL statement selects all products with a ProductName between "Carnarvon
Tigers" and "Chef Anton's Cajun Seasoning":
WHERE ProductName
BETWEEN "Carnarvon Tigers" AND "Chef Anton's
Cajun Seasoning"

Page 28 of 103
rmmakaha@gmail.com
NOT BETWEEN Text Values Example
Example
The following SQL statement selects all products with a ProductName not between "Carnarvon
Tigers" and "Mozzarella di Giovanni":
WHERE ProductName
NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni'
BETWEEN Dates Example
Example
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and
'31-July-1996':
SELECT * FROM Orders
WHERE OrderDate
BETWEEN '1996-07-01' AND '1996-07-31';
MySQL Aliases
Aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax
SELECT column_name
AS alias_name
FROM table_name;
Alias Table Syntax
FROM table_name
AS alias_name

Page 29 of 103
rmmakaha@gmail.com
Alias for Columns Examples
Example
The following SQL statement creates two aliases, one for the CustomerID column and one for the
CustomerName column:
SELECT CustomerID
AS ID, CustomerName
AS Customer
FROM Customers;
Example
The following SQL statement creates two aliases, one for the CustomerName column and one for
the ContactName column. Note: Single or double quotation marks are required if the alias name
contains spaces:
SELECT CustomerName
AS Customer, ContactName
AS "Contact Person"
FROM Customers;
Example
The following SQL statement creates an alias named "Address" that combine four columns
(Address, PostalCode, City and Country):
SELECT CustomerName, CONCAT_WS (', ', Address, PostalCode, City, Country)
AS Address
FROM Customers;
---------------------------------
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country)
AS Address
FROM Customers;
Alias for Tables Example
Example
The following SQL statement selects all the orders from the customer with CustomerID=4
(Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases
of "c" and "o" respectively (Here we use aliases to make the SQL shorter):

Page 30 of 103
rmmakaha@gmail.com
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn'
AND c.CustomerID=o.CustomerID;
Example
The following SQL statement is the same as above, but without aliases:
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn'
AND Customers.CustomerID=Orders.CustomerID;
Aliases can be useful when:
There are more than one table involved in a query
Functions are used in the query
Column names are big or not very readable
Two or more columns are combined together

Page 31 of 103
rmmakaha@gmail.com
MySQL Joins
MySQL Joining Tables
A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.
Let's look at a selection from the "Orders" table:
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the
"Customers" table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects
records that have matching values in both tables:
Example:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
and it will produce something like this:

Page 32 of 103
rmmakaha@gmail.com
Supported Types of Joins in MySQL
INNER JOIN: Returns records that have matching values in both tables
LEFT JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT JOIN: Returns all records from the right table, and the matched records from the left
table
CROSS JOIN: Returns all records from both tables
INNER JOIN Syntax
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Page 33 of 103
rmmakaha@gmail.com
MySQL INNER JOIN Example
The following SQL statement selects all orders with customer information:
Example:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match
between the columns. If there are records in the "Orders" table that do not have matches in
"Customers", these orders will not be shown!
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM (
(Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
);
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers
ON Orders.ShipperID = Shippers.ShipperID;

Page 34 of 103
rmmakaha@gmail.com
How to join three tables in SQL query –
MySQL Example
Three tables JOIN Example SQL
If you need data from multiple tables in one SELECT query you need to use either subquery or JOIN.
SELECT table1.column(s),
table2.column(s),
table3.column(s)
FROM table1
JOIN table2 ON table1.primarykey = table2.foreignkey
Most of the time we only join two tables like Employee and Department but sometimes you
may require joining more than two tables and a popular case is joining three tables in SQL.
In the case of joining three tables table, 1 relates to table 2 and then table 2 relates to table 3. If you
look it closely you find that table 2 is a joining table that contains the primary key from both table 1
and table 2. As I said it can be extremely confusing to understand the join of three or more tables.
I have found that understanding table relationships as the primary key and foreign key helps to
alleviate confusion than the classical matching row paradigm.
SQL Join is also a very popular topic in SQL interviews and there are always been some questions
from Joins, like the difference between INNER and OUTER JOIN, SQL query with JOIN
like Employee Department relationship and Difference between LEFT and RIGHT OUTER JOIN,
etc. In short, this is one of the most important topics in SQL both from experience and interview
points of view.
Three table JOIN syntax in SQL
Here is a general SQL query syntax to join three or more tables. This SQL query should work in all
major relational databases like MySQL, Oracle, Microsoft SQLServer, Sybase, and PostgreSQL:
SELECT t1.col, t2.col t3.col
FROM table1

Page 35 of 103
rmmakaha@gmail.com
We first join table 1 and table 2 which produce a temporary table with combined data from
table1 and table2, which is then joined to table3. This formula can be extended to more than 3
tables to N tables, You just need to make sure that the SQL query should have N-1 join statement in
order to join N tables. for joining two tables, we require 1 join statement and for joining 3 tables we
need 2 join statements.
Here is a nice diagram that also shows how does different types of JOINs e.g. inner, left outer, right
outer and cross joins works in SQL:
SQL Query to JOIN three tables in MySQL
In order to better understand the joining of 3 tables in the SQL query let's see an example. Consider
the popular example of Employee and Department schema. In our case, we have used a link
table called Register which links or relates both Employee to Department.
The primary key of the Employee table (emp_id) is a foreign key in Register and similarly,
the primary key of the Department table (dept_id) is a foreign key in Register table.

Page 36 of 103
rmmakaha@gmail.com
Btw, the only way to master SQL join is doing as much exercise as possible. If you could solve most of
SQL puzzles from Joe Celko's classic book, SQL Puzzles, and Answers, 2nd edition, you will more
confident about dealing with SQL joins, whether it could be two, three or four tables.
In order to write an SQL query to print employee name and department name alongside we need to
join 3 tables. First JOIN statement will join Employee and Register and create a temporary
table which will have dept_id as another column. Now second JOIN statement will join this temp
table with Department table on dept_id to get the desired result.
Here is the complete SELECT SQL query example to join 3 tables and it can be extended to join more
than 3 or N tables.
mysql> SELECT * FROM Employee;
+--------+----------+--------+
| emp_id | emp_name | salary |
+--------+----------+--------+
| 1 | James | 2000 |
| 2 | Jack | 4000 |
| 3 | Henry | 6000 |
| 4 | Tom | 8000 |
+--------+----------+--------+
4 rows IN SET (0.00 sec)
mysql> SELECT * FROM Department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 101 | Sales |
| 102 | Marketing |
| 103 | Finance |
+---------+-----------+

Page 37 of 103
rmmakaha@gmail.com
mysql> SELECT * FROM Register;
+--------+---------+
| emp_id | dept_id |
+--------+---------+
| 1 | 101 |
| 2 | 102 |
| 3 | 103 |
| 4 | 102 |
+--------+---------+
mysql> SELECT emp_name, dept_name
FROM Employee e
JOIN Register r ON e.emp_id=r.emp_id
JOIN Department d ON r.dept_id=d.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| James | Sales |
| Jack | Marketing |
| Henry | Finance |
| Tom | Marketing |
+----------+-----------+
If you want to understand it even better then try joining tables step by step. So instead of joining 3
tables in one go, first join 2 tables and see how the result table will look like. That’s all on How to join
three tables in one SQL query in the relational database.
By the way, in this SQL JOIN Example, we have used ANSI SQL and it will work in another relational
database as well like Oracle, SQL Server, Sybase, PostgreSQL, etc. Let us know if you face any issues
while running this 3 table JOIN query in any other database.
MySQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matching
records (if any) from the right table (table2).

Page 38 of 103
rmmakaha@gmail.com
LEFT JOIN Syntax
FROM table1
LEFT JOIN table2
MySQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are
no matches in the right table (Orders).
MySQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching
records (if any) from the left table (table1).

Page 39 of 103
rmmakaha@gmail.com
RIGHT JOIN Syntax
FROM table1
RIGHT JOIN table2
MySQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have placed:
Example:
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if
there are no matches in the left table (Orders).
SQL CROSS JOIN Keyword
The CROSS JOIN keyword returns all records from both tables (table1 and table2).

Page 40 of 103
rmmakaha@gmail.com
CROSS JOIN Syntax
FROM table1 CROSS JOIN table2;
Note: CROSS JOIN can potentially return very large result-sets!
MySQL CROSS JOIN Example
The following SQL statement selects all customers, and all orders:
Example:
FROM Customers
CROSS JOIN Orders;
Note: The CROSS JOIN keyword returns all matching records from both tables whether the other
table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders",
or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed
as well.
If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will
produce the same result as the INNER JOIN clause:
Example
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;

Page 41 of 103
rmmakaha@gmail.com
MySQL Self Join
A self join is a regular join, but the table is joined with itself.
Self Join Syntax
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.
MySQL Self Join Example
The following SQL statement matches customers that are from the same city:
Example
SELECT A.CustomerName
AS CustomerName1, B.CustomerName
AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID AND A.City = B.City
ORDER BY A.City;

Page 42 of 103
rmmakaha@gmail.com
SUBQUIRIES IN MySQL
What are Subqueries?
Subqueries, also known as nested queries or inner queries, are queries that are embedded
within another query. They allow you to retrieve data from one table or query and use it as a
condition or comparison within another query. Subqueries can be used in various parts of a
SQL statement, such as the SELECT, FROM, WHERE, or HAVING clauses.
Benefits of Subqueries:
 Simplicity: Subqueries can help simplify complex queries by breaking them down into
smaller, more manageable parts.
 Flexibility: Subqueries allow you to perform calculations, filtering, and aggregation on
intermediate results before using them in the main query.
 Readability: By separating the logic into smaller steps, subqueries can enhance the
readability and understandability of your SQL code.
SUBQUERIES Syntax:
The basic syntax of a subquery consists of enclosing the inner query within parentheses and
using it as a value or condition in the outer query. Here's a general example:
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table);
Types of Subqueries:
1. Single-row Subquery: A subquery that returns only a single row as a result.
2. Multi-row Subquery: A subquery that returns multiple rows as a result.
3. Correlated Subquery: A subquery that refers to a column from the outer query, allowing for
more dynamic and related results.
4. Scalar Subquery: A subquery that returns a single value, which can be used in expressions or
comparisons.
Usage of Subqueries:
Subqueries can be used for various purposes, including:
 Filtering: Using a subquery in the WHERE clause to filter the results based on a condition.
 Joining: Using a subquery in the FROM clause to join tables or derived tables.
 Aggregation: Using a subquery in combination with aggregate functions to calculate
summary values.

Page 43 of 103
rmmakaha@gmail.com
 Inserting/Updating/Deleting: Using subqueries in INSERT, UPDATE, or DELETE statements to
modify data based on certain conditions.
Performance Considerations:
While subqueries provide flexibility, it's important to consider their impact on performance.
Subqueries can sometimes be inefficient, especially if they are used incorrectly or
unnecessarily. It's recommended to optimize subqueries by ensuring proper indexing, using
appropriate join conditions, and considering alternatives like JOINs or temporary tables when
applicable.
SELECT Statement:
A subquery can be used in the SELECT statement to retrieve data based on a condition or
calculation.
Example:
SELECT column1, column2, (SELECT MAX(column3) FROM table2) AS max_value
FROM table1;
In this example, the subquery (SELECT MAX(column3) FROM table2) is used to find the
maximum value of column3 from table2. The result of the subquery is then used as a
calculated column max_value in the main query.
FROM Statement:
A subquery can be used in the FROM statement to create a derived table, which can be joined
with other tables.
Example:
SELECT t1.column1, t2.column2
FROM (SELECT column1 FROM table1 WHERE condition) AS t1
JOIN table2 AS t2 ON t1.column1 = t2.column1;
In this example, the subquery (SELECT column1 FROM table1 WHERE condition) is used
to create a derived table t1. The derived table t1 is then joined with table2 on the condition
t1.column1 = t2.column1.
WHERE Statement:
A subquery can be used in the WHERE statement to filter the results based on a condition.
Example:
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

Page 44 of 103
rmmakaha@gmail.com
to retrieve a list of values from table2. The main query then selects rows from table1
where column1 matches any of the values returned by the subquery.
DELETE Statement:
A subquery can be used in the DELETE statement to delete rows based on a condition.
Example:
DELETE FROM table1
to retrieve a list of values from table2. The main query then deletes rows from table1
where column1 matches any of the values returned by the subquery.
UPDATE Statement:
A subquery can be used in the UPDATE statement to update rows based on a condition.
Example:
UPDATE table1
SET column1 = value
to retrieve a list of values from table2. The main query then updates rows in table1 where
column2 matches any of the values returned by the subquery.
IMPLEMENTATION
1. Subquery in the SELECT Statement:
Suppose you want to retrieve a list of customers along with the total number of orders they
have placed. You can use a subquery in the SELECT statement for this:
SELECT CustomerName,
(SELECT COUNT(OrderID)FROM orders
WHERE orders.CustomerID = customers.CustomerID)
AS TotalOrders
FROM customers;

Page 45 of 103
rmmakaha@gmail.com
In this query, the subquery calculates the total number of orders for each customer, and the
result is displayed as the "TotalOrders" column in the output.
2. How to Do it with PARTITION BY:
If you want to achieve a similar result without using subqueries, you can use the PARTITION
BY clause in conjunction with the SUM() window function:
SELECT
CustomerName,
COUNT(OrderID) OVER (PARTITION BY CustomerID) AS TotalOrders
FROM customers
LEFT JOIN orders ON customers.CustomerID = orders.CustomerID;
This query uses a window function to count the number of orders for each customer. The
PARTITION BY clause ensures that the count is calculated separately for each customer.
3. Subquery in the FROM Statement:
Suppose you want to find the customers who have placed orders in the last 30 days and their
total order count. You can use a subquery in the FROM statement to create a temporary table:
In this query, the subquery retrieves the total order count for customers who placed orders in
the last 30 days. The outer query joins this temporary result with the "customers" table to
fetch customer names.
SELECT c.CustomerName, o.TotalOrders
FROM customers c
INNER JOIN (
SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM orders
WHERE OrderDate >= CURDATE() - INTERVAL 30 DAY
GROUP BY CustomerID
) o ON c.CustomerID = o.CustomerID;
4. Subquery in the WHERE Statement:
Suppose you want to find all customers who have not placed any orders. You can use a
subquery in the WHERE statement to filter the results:
In this query, the subquery retrieves the distinct CustomerIDs from the "orders" table, and the
outer query selects customer names that are not found in the subquery result, effectively
finding customers with no orders.
These examples demonstrate how you can use subqueries and alternative techniques to
achieve various tasks in SQL with the "customers" and "orders" tables provided.

Page 46 of 103
rmmakaha@gmail.com
SELECT CustomerName
FROM customers
WHERE CustomerID NOT IN (
SELECT DISTINCT CustomerID
FROM orders
);
SUBQUERY IN SELECT, FROM & WHERE STATEMENT:
Subquery in the SELECT Statement:
You can use a subquery in the SELECT statement to retrieve a single value that will be
displayed as a column alongside other columns in the result set. Here's an example:
Suppose you have a database with a products table and a reviews table. You want to find
the average rating for each product and display it in the result:
SELECT product_name, (
SELECT AVG(rating)
FROM reviews
WHERE reviews.product_id = products.product_id
) AS avg_rating
FROM products;
In this query, the subquery calculates the average rating for each product in the outer query's
result set and aliases it as avg_rating.

Page 47 of 103
rmmakaha@gmail.com
Subquery in the FROM Statement:
You can use a subquery in the FROM statement to create a temporary table or result set that
you can then query further. Here's an example:
Suppose you want to find the highest-paid employee in each department. You can create a
subquery to get the maximum salary for each department and then join it with the employees
table:
SELECT d.department_name, e.employee_name, e.salary
FROM employees e
INNER JOIN (
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
) AS max_salaries
ON e.department_id = max_salaries.department_id
AND e.salary = max_salaries.max_salary;
In this query, the subquery calculates the maximum salary for each department, and the outer
query joins it with the employees table to find the highest-paid employee in each department.
Subquery in the WHERE Statement:
You can use a subquery in the WHERE statement to filter rows based on a condition derived
from another query. Here's an example:
Suppose you have a database with a students table and a grades table. You want to find all
students who have received an "A" grade in at least one course:
SELECT student_name
FROM students
WHERE student_id IN (
SELECT student_id
FROM grades
WHERE grade = 'A'
);
In this query, the subquery retrieves the student_id values of students who received an "A"
grade, and the outer query selects the student_name of those students.
These examples illustrate how you can use subqueries in different parts of your SQL
statements (SELECT, FROM, and WHERE) to perform various tasks like calculations,
temporary result sets, and filtering based on conditions. Subqueries provide flexibility and
power when working with complex data retrieval and analysis requirements in MySQL.

Page 48 of 103
rmmakaha@gmail.com
MySQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order
UNION Syntax
FROM table1
UNION SELECT column_name(s)
FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION
ALL:
FROM table1
UNION ALL SELECT column_name(s)
FROM table2;
Note: The column names in the result-set are usually equal to the column names in the first
SELECT statement.

Page 49 of 103
rmmakaha@gmail.com
SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both the "Customers"
and the "Suppliers" table:
Example
SELECT City FROM Customers
UNION SELECT City
FROM Suppliers
ORDER BY City;
Note: If some customers or suppliers have the same city, each city will only be listed once,
because UNION selects only distinct values. Use UNION ALL to also select duplicate values!
SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both the "Customers"
and the "Suppliers" table:
Example
SELECT City FROM Customers
UNION ALL SELECT City
FROM Suppliers
ORDER BY City;
SQL UNION With WHERE
The following SQL statement returns the German cities (only distinct values) from both the
"Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers
UNION SELECT City, Country
FROM Suppliers
ORDER BY City;

Page 50 of 103
rmmakaha@gmail.com
SQL UNION ALL With WHERE
The following SQL statement returns the German cities (duplicate values also) from both the
"Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers
UNION ALL SELECT City, Country
FROM Suppliers
ORDER BY City;
Another UNION Example
The following SQL statement lists all customers and suppliers:
Example
SELECT 'Customer'
AS Type, ContactName, City, Country
FROM Customers
UNION SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
 Notice the "AS Type" above - it is an alias.
 SQL Aliases are used to give a table or a column a temporary name.
 An alias only exists for the duration of the query.
 So, here we have created a temporary column named "Type", that list whether the contact
person is a "Customer" or a "Supplier".
The MySQL GROUP BY Statement

Page 51 of 103
rmmakaha@gmail.com
 The GROUP BY statement groups rows that have the same values into summary rows, like
"find the number of customers in each country".
 The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(),
MIN(), SUM(), AVG()) to group the result-set by one or more columns.
GROUP BY Syntax
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
MySQL GROUP BY Examples
Example
The following SQL statement lists the number of customers in each country:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
Example
The following SQL statement lists the number of customers in each country, sorted high to low:
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Page 52 of 103
rmmakaha@gmail.com
GROUP BY With JOIN Example
The following SQL statement lists the number of orders sent by each shipper:
Example
SELECT Shippers.ShipperName,
COUNT(Orders.OrderID)
AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
The MySQL HAVING Clause
 The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.
HAVING Syntax
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Page 53 of 103
rmmakaha@gmail.com
MySQL HAVING Examples
Example:
The following SQL statement lists the number of customers in each country. Only include
countries with more than 5 customers:
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
Example
The following SQL statement lists the number of customers in each country, sorted high to low
(Only include countries with more than 5 customers):
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
More HAVING Examples
The following SQL statement lists the employees that have registered more than 10 orders:
Example
SELECT Employees.LastName,
COUNT(Orders.OrderID)
AS NumberOfOrders
FROM
(Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Page 54 of 103
rmmakaha@gmail.com
Example
The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more
than 25 orders:
SELECT Employees.LastName,COUNT(Orders.OrderID)
AS NumberOfOrders
FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
The MySQL EXISTS Operator
 The EXISTS operator is used to test for the existence of any record in a subquery.
 The EXISTS operator returns TRUE if the subquery returns one or more records.
EXISTS Syntax
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Page 55 of 103
rmmakaha@gmail.com
MySQL EXISTS Examples
Example
The following SQL statement returns TRUE and lists the suppliers with a product price less than
20:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID =
Suppliers.supplierID AND Price < 20);
Example
The following SQL statement returns TRUE and lists the suppliers with a product price equal to
22:
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID =
Suppliers.supplierID AND Price = 22);
The MySQL ANY and ALL Operators
The ANY and ALL operators allow you to perform a comparison between a single column value
and a range of other values.
The ANY Operator
The ANY operator:
 returns a Boolean value as a result
 returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any
of the values in the range.
ANY Syntax
SELECT column_name(s) FROM table_name
WHERE column_name operator
ANY
(SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Page 56 of 103
rmmakaha@gmail.com
The ALL Operator
The ALL operator:
 returns a Boolean value as a result
 returns TRUE if ALL of the subquery values meet the condition
 is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for
all values in the range.
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
FROM table_name
WHERE column_name operator
ALL (SELECT column_name
FROM table_name
WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
SQL ANY Examples

Page 57 of 103
rmmakaha@gmail.com
Example:
The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails
table has Quantity equal to 10 (this will return TRUE because the Quantity column has some
values of 10):
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM
OrderDetails WHERE Quantity = 10);
Example:
table has Quantity larger than 99 (this will return TRUE because the Quantity column has some
values larger than 99):
SELECT ProductName
FROM Products
OrderDetails WHERE Quantity > 99);
Example:
table has Quantity larger than 1000 (this will return FALSE because the Quantity column has no
values larger than 1000):
SELECT ProductName
FROM Products
OrderDetails WHERE Quantity > 1000);

Page 58 of 103
rmmakaha@gmail.com
SQL ALL Examples
The following SQL statement lists ALL the product names:
Example
SELECT ALL ProductName
FROM Products
WHERE TRUE;
Example
The following SQL statement lists the ProductName if ALL the records in the OrderDetails table
has Quantity equal to 10. This will of course return FALSE because the Quantity column has
many different values (not only the value of 10):
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM
OrderDetails WHERE Quantity = 10);

Page 59 of 103
rmmakaha@gmail.com
SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
SELECT INTO Syntax
Copy all columns into a new table:
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
The new table will be created with the column-names and types as defined in the old table.
You can create new column names using the AS clause.
SQL SELECT INTO Examples
The following SQL statement creates a backup copy of Customers:
SELECT * INTO CustomersBackup2017
FROM Customers;
The following SQL statement uses the IN clause to copy the table into a new table in another
database:
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
The following SQL statement copies only a few columns into a new table:
SELECT CustomerName, ContactName INTO
CustomersBackup2017
FROM Customers;
The following SQL statement copies only the German customers into a new table:

Page 60 of 103
rmmakaha@gmail.com
SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';
The following SQL statement copies data from more than one table into a new table:
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID;
Tip: SELECT INTO can also be used to create a new, empty table using the schema of another.
Just add a WHERE clause that causes the query to return no data:
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
The MySQL INSERT INTO SELECT Statement

Page 61 of 103
rmmakaha@gmail.com
 The INSERT INTO SELECT statement copies data from one table and inserts it into
another table.
 The INSERT INTO SELECT statement requires that the data types in source and target
tables matches.
Note: The existing records in the target table are unaffected.
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
MySQL INSERT INTO SELECT Examples
Example:
The following SQL statement copies "Suppliers" into "Customers" (the columns that are not
filled with data, will contain NULL):
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
Example
The following SQL statement copies "Suppliers" into "Customers" (fill all columns):
INSERT INTO Customers (CustomerName, ContactName,
Address, City, PostalCode, Country) SELECT

Page 62 of 103
rmmakaha@gmail.com
SupplierName, ContactName, Address, City, PostalCode,
Country FROM Suppliers;
Example
The following SQL statement copies only the German suppliers into "Customers":
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

Page 63 of 103
rmmakaha@gmail.com
IF() Function
Return "YES" if the condition is TRUE, or "NO" if the condition is FALSE:
SELECT IF(500<1000, "YES", "NO");
The IF() function returns a value if a condition is TRUE, or another value if a condition is
FALSE.
IF() Syntax
IF(condition, value_if_true, value_if_false)
Parameter Description
condition Required. The value to test
value_if_true Required. The value to return if condition is TRUE
value_if_false Required. The value to return if condition is FALSE
Example
Return 5 if the condition is TRUE, or 10 if the condition is FALSE:
SELECT IF(500<1000, 5, 10);
Example
SELECT IF(STRCMP("hello","bye") = 0,
"YES", "NO");
Example
Return "MORE" if the condition is TRUE, or "LESS" if the condition is FALSE:
SELECT OrderID, Quantity,
IF(Quantity>10, "MORE", "LESS")
FROM OrderDetails;

Page 64 of 103
rmmakaha@gmail.com
Example
SELECT *,
IF( score >= 50, 'PASS', 'FAIL')
AS result
FROM students;
The MySQL code you provided is a SELECT statement that retrieves data from a table called
"students" and calculates a new column called "result" based on the "score" column. Here's a
breakdown of the code:
1. SELECT *, ...: This part of the statement selects all columns from the "students"
table, including the existing columns, and a new calculated column named "result."
2. SELECT score, ...: This part of the statement specifies that you want to retrieve
two columns from the "students" table. The columns are "score" and a calculated
column that will be named "result."
3. IF(score >= 50, 'PASS', 'FAIL') AS result: This is the interesting part
of the query. It uses the IF function to create a new column called "result" based
on the value in the "score" column. Here's how it works:
o The AS keyword is used to assign a name to the result of the IF function. In
this case, the result is assigned the name 'result'.
o score >= 50: This is a condition. It checks if the value in the "score"
column is greater than or equal to 50.
o
o 'PASS': If the condition is true (i.e., the score is greater than or equal to 50),
this part of the IF function assigns the value "PASS" to the "result" column for
that row.
o 'FAIL': If the condition is false (i.e., the score is less than 50), this part of the
IF function assigns the value "FAIL" to the "result" column for that row.
4. FROM students: This specifies that you want to perform this operation on the
"students" table.
In summary, this SQL code retrieves the "score" column from the "students" table and creates
a new "result" column. The "result" column will contain "PASS" if the "score" is greater than
or equal to 50, and "FAIL" if the "score" is less than 50. It essentially categorizes the students
as pass or fail based on their scores.

Page 65 of 103
rmmakaha@gmail.com
The MySQL CASE Statement
 The CASE statement goes through conditions and returns a value when the first condition
is met (like an if-then-else statement). So, once a condition is true, it will stop reading and
return the result. If no conditions are true, it returns the value in the ELSE clause.
 If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN conditionN THEN resultN
ELSE result
END;
CASE
WHEN conditionN THEN resultN
ELSE result END;
MySQL CASE Examples
Example:
The following SQL goes through conditions and returns a value when the first condition is met:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText FROM OrderDetails;

Page 66 of 103
rmmakaha@gmail.com
Example:
The following SQL will order the customers by City. However, if City is NULL, then order by
Country:
SELECT CustomerName, City, Country
FROM Customers
ORDER BY (
CASE
WHEN City IS NULL THEN Country
ELSE City END
);
MySQL IFNULL () and COALESCE () Functions
Look at the following "Products" table:
Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.
Look at the following SELECT statement:
SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;
In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.
MySQL IFNULL() Function
The MySQL IFNULL() function lets you return an alternative value if an expression is NULL.
The example below returns 0 if the value is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

Page 67 of 103
rmmakaha@gmail.com
MySQL COALESCE() Function
Or we can use the COALESCE() function, like this:
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
MySQL Comments
 Comments are used to explain sections of SQL statements, or to prevent execution of
SQL statements.
Single Line Comments
 Single line comments start with --.
 Any text between -- and the end of the line will be ignored (will not be executed).
Example
The following example uses a single-line comment as an explanation:
-- Select all: SELECT * FROM Customers;
Example
The following example uses a single-line comment to ignore the end of a line:
SELECT * FROM Customers -- WHERE City='Berlin';
Example
The following example uses a single-line comment to ignore a statement:
-- SELECT * FROM Customers; SELECT * FROM Products;
Multi-line Comments
 Multi-line comments start with /* and end with */.
 Any text between /* and */ will be ignored.
Example
The following example uses a multi-line comment as an explanation:

Page 68 of 103
rmmakaha@gmail.com
/*Select all the columns of all the records in the
Customers table:*/ SELECT * FROM Customers;
Example
The following example uses a multi-line comment to ignore many statements:
/*SELECT * FROM Customers; SELECT * FROM Products;
SELECT * FROM Orders; SELECT * FROM Categories;*/
SELECT * FROM Suppliers;
To ignore just a part of a statement, also use the /* */ comment.
Example
The following example uses a comment to ignore part of a line:
SELECT CustomerName, /*City,*/ Country
FROM Customers;
Example
The following example uses a comment to ignore part of a statement:
WHERE
(CustomerName LIKE 'L%' OR CustomerName LIKE 'R%'
/*OR CustomerName LIKE 'S%' OR CustomerName LIKE 'T%'*/
OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;

Page 69 of 103
rmmakaha@gmail.com
MySQL Operators
MySQL Arithmetic Operators
Operator Description Example
+ Add
- Subtract
* Multiply
/ Divide
% Modulo
MySQL Bitwise Operators
Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR
MySQL Comparison Operators
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
MySQL Compound Operators
Operator Description
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
|*= Bitwise OR equals

Page 70 of 103
rmmakaha@gmail.com
MySQL Logical Operators
ALL TRUE if all of the subquery values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY
TRUE if any of the subquery values meet the
condition
BETWEEN
TRUE if the operand is 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
NOT Displays a record if the condition(s) is NOT TRUE
OR
TRUE if any of the conditions separated by OR is
TRUE
SOME
TRUE if any of the subquery values meet the
condition

Page 71 of 103
rmmakaha@gmail.com
CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL database.
CREATE DATABASE Syntax
CREATE DATABASE databasename;
CREATE DATABASE Example
The following SQL statement creates a database called "testDB":
Example
CREATE DATABASE testDB;
Tip: Make sure you have admin privilege before creating any database. Once a database is created,
you can check it in the list of databases with the following SQL command: SHOW DATABASES;
DROP DATABASE Statement
The DROP DATABASE statement is used to drop an existing SQL database.
DROP DATABASE Syntax
DROP DATABASE databasename;
Note: Be careful before dropping a database. Deleting a database will result in loss of complete
information stored in the database!
DROP DATABASE Example
The following SQL statement drops the existing database "testDB":
Example:
DROP DATABASE testDB;
Tip: Make sure you have admin privilege before dropping any database. Once a database is dropped,
you can check it in the list of databases with the following SQL command: SHOW DATABASES;

Page 72 of 103
rmmakaha@gmail.com
CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype, ....
);
The column parameters 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.).
Tip: For an overview of the available data types, go to our complete Data Types Reference.
MySQL CREATE TABLE Example
The following example creates a table called "Persons" that contains five columns: PersonID,
LastName, FirstName, Address, and City:
Example:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
 The PersonID column is of type int and will hold an integer.
 The LastName, FirstName, Address, and City columns are of type varchar and will hold
characters, and the maximum length for these fields is 255 characters.
The empty "Persons" table will now look like this:
PersonID LastName FirstName Address City

Page 73 of 103
rmmakaha@gmail.com
Tip: The empty "Persons" table can now be filled with data with the SQL INSERT INTO
statement
Create Table Using Another Table
A copy of an existing table can also be created using CREATE TABLE.
The new table gets the same column definitions. All columns or specific columns can be selected.
If you create a new table using an existing table, the new table will be filled with the existing
values from the old table.
Create Table Using Another Table Syntax
CREATE TABLE new_table_name
AS SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
The following SQL creates a new table called "TestTables" (which is a copy of the "Customers"
table):
Example
CREATE TABLE TestTable
AS SELECT customername, contactname
FROM customers;
The MySQL DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.
DROP TABLE Syntax
DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a table will result in loss of complete
information stored in the table!

Page 74 of 103
rmmakaha@gmail.com
MySQL DROP TABLE Example
The following SQL statement drops the existing table "Shippers":
Example:
DROP TABLE Shippers;
MySQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
TRUNCATE TABLE Syntax
TRUNCATE TABLE table_name;
MySQL ALTER TABLE Statement
 The ALTER TABLE statement is used to add, delete, or modify columns in an existing
table.
 The ALTER TABLE statement is also used to add and drop various constraints on an
existing table.
ALTER TABLE - ADD Column syntax
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the "Customers" table:
Example:
ALTER TABLE Customers
ADD Email varchar(255);

Page 75 of 103
rmmakaha@gmail.com
ALTER TABLE - DROP COLUMN syntax
To delete a column in a table, use the following syntax (notice that some database systems don't
allow deleting a column):
DROP COLUMN column_name;
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - RENAME COLUMN syntax
To rename a column in a table, use the following syntax:
RENAME COLUMN old_name TO new_name;
ALTER TABLE - MODIFY COLUMN syntax
To change the data type of a column in a table, use the following syntax:
MODIFY COLUMN column_name datatype;

Page 76 of 103
rmmakaha@gmail.com
MySQL ALTER TABLE Example
Look at the "Persons" table:
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
Example
ALTER TABLE Persons
ADD DateOfBirth date;
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data
type specifies what type of data the column can hold. For a complete reference of all the data
types available in MySQL, go to our complete Data Types reference.
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
Example
ALTER TABLE Persons
MODIFY COLUMN DateOfBirth year;
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or
four-digit format.

Page 77 of 103
rmmakaha@gmail.com
DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
Example
ALTER TABLE Persons
DROP COLUMN DateOfBirth;

Page 78 of 103
rmmakaha@gmail.com
MySQL Constraints
SQL constraints are used to specify rules for data in a table.
Create Constraints
Constraints can be specified when the table is created with the CREATE TABLE statement, or after
the table is created with the ALTER TABLE statement.
CONSTRAINTS syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint, ....
);
MySQL Constraints
 SQL constraints are used to specify rules for the data in a table.
 Constraints are used to limit the type of data that can go into a table. This ensures the
accuracy and reliability of the data in the table. If there is any violation between the
constraint and the data action, the action is aborted.
 Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
 NOT NULL - Ensures that a column cannot have a NULL value
 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each
row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT- Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very quickly

Page 79 of 103
rmmakaha@gmail.com
NOT NULL Constraint
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record,
or update a record without adding a value to this field.
NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT
accept NULL values when the "Persons" table is created:
Example:
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already
created, use the following SQL:
Example
ALTER TABLE Persons
MODIFY Age int NOT NULL;

Page 80 of 103
rmmakaha@gmail.com
UNIQUE Constraint
 The UNIQUE constraint ensures that all values in a column are different.
 Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a
column or set of columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint.
 However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "ID" column when the
"Persons" table is created:
ID int NOT NULL,
Age int,
UNIQUE (ID)
);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple
columns, use the following SQL syntax:
ID int NOT NULL,
Age int,
CONSTRAINT UC_Person
UNIQUE (ID,LastName)
);

Page 81 of 103
rmmakaha@gmail.com
UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the "ID" column when the table is already created, use the
following SQL:
ALTER TABLE Persons
ADD UNIQUE (ID);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the
following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person
UNIQUE (ID,LastName);
DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
ALTER TABLE Persons
DROP INDEX UC_Person;

Page 82 of 103
rmmakaha@gmail.com
PRIMARY KEY Constraint
 The PRIMARY KEY constraint uniquely identifies each record in a table.
 Primary keys must contain UNIQUE values, and cannot contain NULL values.
 A table can have only ONE primary key; and in the table, this primary key can consist of
single or multiple columns (fields).
PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is
created:
ID int NOT NULL,
Age int,
PRIMARY KEY (ID)
);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on
multiple columns, use the following SQL syntax:
ID int NOT NULL,
Age int, CONSTRAINT PK_Person PRIMARY KEY
(ID,LastName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the
VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use
the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);

Page 83 of 103
rmmakaha@gmail.com
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on
ALTER TABLE Persons ADD CONSTRAINT PK_Person
PRIMARY KEY (ID,LastName);
Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been
declared to not contain NULL values (when the table was first created).
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons DROP PRIMARY KEY;

Page 84 of 103
rmmakaha@gmail.com
FOREIGN KEY Constraint
 The FOREIGN KEY constraint is used to prevent actions that would destroy links between
tables.
 A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY
KEY in another table.
 The table with the foreign key is called the child table, and the table with the primary key is
called the referenced or parent table.
Look at the following two tables:
 Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in
the "Persons" table.
 The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
 The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
 The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key
column, because it has to be one of the values contained in the parent table.

Page 85 of 103
rmmakaha@gmail.com
FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table
is created:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on
OrderID int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder
REFERENCES Persons(PersonID) );

Page 86 of 103
rmmakaha@gmail.com
FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is
already created, use the following SQL:
ALTER TABLE Orders
ADD
REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

Page 87 of 103
rmmakaha@gmail.com
CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a column it will allow only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on
values in other columns in the row.
CHECK on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is
created. The CHECK constraint ensures that the age of a person must be 18, or older:
ID int NOT NULL,
Age int,
CHECK (Age>=18)
);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple
ID int NOT NULL,
Age int,
City varchar(255),
CONSTRAINT CHK_Person
CHECK (Age>=18 AND City='Sandnes')
);

Page 88 of 103
rmmakaha@gmail.com
CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already created, use the
following SQL:
ALTER TABLE Persons
ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge
CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Page 89 of 103
rmmakaha@gmail.com
DEFAULT Constraint
The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
DEFAULT on CREATE TABLE
The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is
created:
ID int NOT NULL,
Age int,
City varchar(255)
DEFAULT 'Sandnes'
);
The DEFAULT constraint can also be used to insert system values, by using functions like
CURRENT_DATE():
ID int NOT NULL,
OrderDate date
DEFAULT CURRENT_DATE()
);

Page 90 of 103
rmmakaha@gmail.com
DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the
following SQL:
ALTER TABLE Persons
ALTER City
SET DEFAULT 'Sandnes';
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Persons
ALTER City
DROP DEFAULT;
CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database more quickly than
otherwise. The users cannot see the indexes, they are just used to
speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a
table without (because the indexes also need an update). So, only
create indexes on columns that will be frequently searched against.

Page 91 of 103
rmmakaha@gmail.com
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
MySQL CREATE INDEX Example
The SQL statement below creates an index named "idx_lastname" on the "LastName" column in
the "Persons" table:
CREATE INDEX idx_lastname
ON Persons (LastName);
If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas:
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

Page 92 of 103
rmmakaha@gmail.com
DROP INDEX Syntax
The DROP INDEX statement is used to delete an index in a table.
DROP INDEX index_name;
What is an AUTO INCREMENT Field?
 Auto-increment allows a unique number to be generated automatically when a new record is
inserted into a table.
 Often this is the primary key field that we would like to be created automatically every time a
new record is inserted.
AUTO_INCREMENT Keyword
 MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
 By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each
new record.
 The following SQL statement defines the "Personid" column to be an auto-increment primary
key field in the "Persons" table:
Personid int NOT NULL AUTO_INCREMENT,
Age int,
PRIMARY KEY (Personid) );
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE Persons AUTO_INCREMENT=100;

Page 93 of 103
rmmakaha@gmail.com
When we insert a new record into the "Persons" table, we do NOT have to specify a value for the
"Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');
The SQL statement above would insert a new record into the "Persons" table. The "Personid"
column would be assigned a unique value automatically. The "FirstName" column would be set
to "Lars" and the "LastName" column would be set to "Monsen".
MySQL Dates
The most difficult part when working with dates is to be sure that the format of the date you are
trying to insert, matches the format of the date column in the database.
As long as your data contains only the date portion, your queries will work as expected. However,
if a time portion is involved, it gets more complicated.
MySQL Date Data Types
MySQL comes with the following data types for storing a date or a date/time value in the
database:
 DATE - format YYYY-MM-DD
 DATETIME - format: YYYY-MM-DD HH:MI:SS
 TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
 YEAR - format YYYY or YY
Note: The date data type are set for a column when you create a new table in your database!
Working with Dates
Look at the following table:

Page 94 of 103
rmmakaha@gmail.com
Now we want to select the records with an OrderDate of "2008-11-11" from the table above.
We use the following SELECT statement:
WHERE OrderDate='2008-11-11'
The result-set will look like this:
Note: Two dates can easily be compared if there is no time component involved!
Now, assume that the "Orders" table looks like this (notice the added time-component in the
"OrderDate" column):
If we use the same SELECT statement as above:
WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with no time portion.
Tip: To keep your queries simple and easy to maintain, do not use time-components in your
dates, unless you have to!

Page 95 of 103
rmmakaha@gmail.com
MySQL Views
MySQL CREATE VIEW Statement
 In SQL, a view is a virtual table based on the result-set of an SQL statement.
 A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.
 You can add SQL statements and functions to a view and present the data as if the data were
coming from one single table.
 A view is created with the CREATE VIEW statement.
CREATE VIEW 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 view, every time a
user queries it.
MySQL CREATE VIEW Examples
The following SQL creates a view that shows all customers from Brazil:
Example:
CREATE VIEW [Brazil Customers] AS SELECT
CustomerName, ContactName FROM Customers WHERE
Country = 'Brazil';
We can query the view above as follows:
Example
SELECT * FROM [Brazil Customers];

Page 96 of 103
rmmakaha@gmail.com
The following SQL creates a view that selects every product in the "Products" table with a price
higher than the average price:
Example
CREATE VIEW [Products Above Average Price]
AS SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price)
FROM Products);
We can query the view above as follows:
Example
SELECT * FROM [Products Above Average Price];
MySQL Updating a View
A view can be updated with the CREATE OR REPLACE VIEW statement.
CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name
AS SELECT column1, column2, ...
FROM table_name
WHERE condition;
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
CREATE OR REPLACE VIEW [Brazil Customers]
AS SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';

Page 97 of 103
rmmakaha@gmail.com
MySQL Dropping a View
A view is deleted with the DROP VIEW statement.
DROP VIEW Syntax
DROP VIEW view_name;
The following SQL drops the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];

Page 98 of 103
rmmakaha@gmail.com
MySQL String Functions
Function Description
ASCII Returns the ASCII value for the specific character
CHAR_LENGTH Returns the length of a string (in characters)
CHARACTER_LENGTH Returns the length of a string (in characters)
CONCAT Adds two or more expressions together
CONCAT_WS Adds two or more expressions together with a separator
FIELD Returns the index position of a value in a list of values
FIND_IN_SET Returns the position of a string within a list of strings
FORMAT
Formats a number to a format like "#,###,###.##", rounded to a specified
number of decimal places
INSERT
Inserts a string within a string at the specified position and for a certain
number of characters
INSTR Returns the position of the first occurrence of a string in another string
LCASE Converts a string to lower-case
LEFT Extracts a number of characters from a string (starting from left)
LENGTH Returns the length of a string (in bytes)
LOCATE Returns the position of the first occurrence of a substring in a string
LOWER Converts a string to lower-case
LPAD Left-pads a string with another string, to a certain length
LTRIM Removes leading spaces from a string
MID Extracts a substring from a string (starting at any position)
POSITION Returns the position of the first occurrence of a substring in a string
REPEAT Repeats a string as many times as specified
REPLACE
Replaces all occurrences of a substring within a string, with a new
substring
REVERSE Reverses a string and returns the result
RIGHT Extracts a number of characters from a string (starting from right)

Page 99 of 103
rmmakaha@gmail.com
RPAD Right-pads a string with another string, to a certain length
RTRIM Removes trailing spaces from a string
SPACE Returns a string of the specified number of space characters
STRCMP Compares two strings
SUBSTR Extracts a substring from a string (starting at any position)
SUBSTRING Extracts a substring from a string (starting at any position)
SUBSTRING_INDEX
Returns a substring of a string before a specified number of delimiter
occurs
TRIM Removes leading and trailing spaces from a string
UCASE Converts a string to upper-case
UPPER Converts a string to upper-case
MySQL Numeric Functions
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of one or two numbers
ATAN2 Returns the arc tangent of two numbers
AVG Returns the average value of an expression
CEIL Returns the smallest integer value that is >= to a number
CEILING Returns the smallest integer value that is >= to a number
COS Returns the cosine of a number
COT Returns the cotangent of a number
COUNT Returns the number of records returned by a select query
DEGREES Converts a value in radians to degrees

Page 100 of 103
rmmakaha@gmail.com
DIV Used for integer division
EXP Returns e raised to the power of a specified number
FLOOR Returns the largest integer value that is <= to a number
GREATEST Returns the greatest value of the list of arguments
LEAST Returns the smallest value of the list of arguments
LN Returns the natural logarithm of a number
LOG
Returns the natural logarithm of a number, or the logarithm of a number
to a specified base
LOG10 Returns the natural logarithm of a number to base 10
LOG2 Returns the natural logarithm of a number to base 2
MAX Returns the maximum value in a set of values
MIN Returns the minimum value in a set of values
MOD Returns the remainder of a number divided by another number
PI Returns the value of PI
POW Returns the value of a number raised to the power of another number
POWER Returns the value of a number raised to the power of another number
RADIANS Converts a degree value into radians
RAND Returns a random number
ROUND Rounds a number to a specified number of decimal places
SIGN Returns the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Calculates the sum of a set of values
TAN Returns the tangent of a number
TRUNCATE Truncates a number to the specified number of decimal places

Page 101 of 103
rmmakaha@gmail.com
MySQL Date Functions
ADDDATE Adds a time/date interval to a date and then returns the date
ADDTIME
Adds a time interval to a time/datetime and then returns the
time/datetime
CURDATE Returns the current date
CURRENT_DATE Returns the current date
CURRENT_TIME Returns the current time
CURRENT_TIMESTAMP Returns the current date and time
CURTIME Returns the current time
DATE Extracts the date part from a datetime expression
DATEDIFF Returns the number of days between two date values
DATE_ADD Adds a time/date interval to a date and then returns the date
DATE_FORMAT Formats a date
DATE_SUB Subtracts a time/date interval from a date and then returns the date
DAY Returns the day of the month for a given date
DAYNAME Returns the weekday name for a given date
DAYOFMONTH Returns the day of the month for a given date
DAYOFWEEK Returns the weekday index for a given date
DAYOFYEAR Returns the day of the year for a given date
EXTRACT Extracts a part from a given date
FROM_DAYS Returns a date from a numeric datevalue
HOUR Returns the hour part for a given date
LAST_DAY Extracts the last day of the month for a given date
LOCALTIME Returns the current date and time
LOCALTIMESTAMP Returns the current date and time
MAKEDATE Creates and returns a date based on a year and a number of days value

Page 102 of 103
rmmakaha@gmail.com
MAKETIME Creates and returns a time based on an hour, minute, and second value
MICROSECOND Returns the microsecond part of a time/datetime
MINUTE Returns the minute part of a time/datetime
MONTH Returns the month part for a given date
MONTHNAME Returns the name of the month for a given date
NOW Returns the current date and time
PERIOD_ADD Adds a specified number of months to a period
PERIOD_DIFF Returns the difference between two periods
QUARTER Returns the quarter of the year for a given date value
SECOND Returns the seconds part of a time/datetime
SEC_TO_TIME Returns a time value based on the specified seconds
STR_TO_DATE Returns a date based on a string and a format
SUBDATE Subtracts a time/date interval from a date and then returns the date
SUBTIME
Subtracts a time interval from a datetime and then returns the
time/datetime
SYSDATE Returns the current date and time
TIME Extracts the time part from a given time/datetime
TIME_FORMAT Formats a time by a specified format
TIME_TO_SEC Converts a time value into seconds
TIMEDIFF Returns the difference between two time/datetime expressions
TIMESTAMP Returns a datetime value based on a date or datetime value
TO_DAYS Returns the number of days between a date and date "0000-00-00"
WEEK Returns the week number for a given date
WEEKDAY Returns the weekday number for a given date
WEEKOFYEAR Returns the week number for a given date
YEAR Returns the year part for a given date
YEARWEEK Returns the year and week number for a given date

Page 103 of 103
rmmakaha@gmail.com
MySQL Advanced Functions
BIN Returns a binary representation of a number
BINARY Converts a value to a binary string
CASE
Goes through conditions and return a value when the first condition is
met
CAST Converts a value (of any type) into a specified datatype
COALESCE Returns the first non-null value in a list
CONNECTION_ID Returns the unique connection ID for the current connection
CONV Converts a number from one numeric base system to another
CONVERT Converts a value into the specified datatype or character set
CURRENT_USER
Returns the user name and host name for the MySQL account that the
server used to authenticate the current client
DATABASE Returns the name of the current database
IF
Returns a value if a condition is TRUE, or another value if a condition is
FALSE
IFNULL
Return a specified value if the expression is NULL, otherwise return the
expression
ISNULL Returns 1 or 0 depending on whether an expression is NULL
LAST_INSERT_ID
Returns the AUTO_INCREMENT id of the last row that has been inserted
or updated in a table
NULLIF
Compares two expressions and returns NULL if they are equal. Otherwise,
the first expression is returned
SESSION_USER Returns the current MySQL user name and host name
SYSTEM_USER Returns the current MySQL user name and host name
USER Returns the current MySQL user name and host name
VERSION Returns the current version of the MySQL database

MYSQL Practical Tutorial.pdf

In this document

More Related Content

What's hot

Similar to MYSQL Practical Tutorial.pdf

More from Makaha Rutendo

Recently uploaded

MYSQL Practical Tutorial.pdf