SQL Tutorial

Posted on Edited on In , , ,

SQL Introduction

Reference

SQL Tutorial

Demo Database

Data source: northwindmysql.zip

Under Administration tab, click on Data Import/Restore
Under Import from Disk tab, select Import from Self-Contained File. Use the .. (browse button) to select the northwindmysql.sql file you extracted from the download in the first step.

Select the Default Target Schema:. The northwindmysql.sql file does not have a target schema. Use the New button to create a new target schema named northwind.

Click the Start Import button to import the Northwind database into the selected target schema. The Import Progress tab will display the progress of the import.

Under Schemas tab, you should now see a list of tables in the northwind schema. The Northwind database is now imported into MySQL.

MySQL
1
2
USE northwind;
SELECT * FROM Customers LIMIT 10;

Rename column from CompanyName to CustomerName in table customers.

MySQL
1
2
ALTER TABLE customers
RENAME COLUMN CompanyName TO CustomerName;

Rename column from UnitPrice to Price in table products.

MySQL
1
2
ALTER TABLE customers
RENAME COLUMN CompanyName TO CustomerName;

Rename table from order details to orderdetails.

MySQL
1
RENAME TABLE `order details` TO orderdetails;

SQL 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).

The following example uses a single-line comment as an explanation:

Example

MySQL
1
2
--Select all:
SELECT * FROM Customers;

The following example uses a single-line comment to ignore the end of a line:

Example

MySQL
1
2
SELECT * FROM Customers -- WHERE City='Berlin';
The following example uses a single-line comment to ignore a statement:

Example

MySQL
1
2
--SELECT * FROM Customers;
SELECT * FROM Products;

Multi-line Comments

Multi-line comments start with /* and end with */.

Any text between /* and */ will be ignored.

The following example uses a multi-line comment as an explanation:

Example

MySQL
1
2
3
4
5
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
The following example uses a multi-line comment to ignore many statements:

Example

MySQL
1
2
3
4
5
/*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.

The following example uses a comment to ignore part of a line:

Example

MySQL
1
2
SELECT CustomerName, /*City,*/ Country FROM Customers;
The following example uses a comment to ignore part of a statement:

Example

MySQL
1
2
3
4
5
SELECT * FROM Customers 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;

SQL Statements

SQL Tutorial - w3schools.com

SQL SELECT Statements

SQL SELECT Statement

https://www.w3schools.com/sql/sql_select.asp

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

MySQL
1
2
SELECT column1, column2, ...
FROM table_name;

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:

1
SELECT * FROM table_name;

Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4

 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

SELECT Column Example
The following SQL statement selects the CustomerName and City columns from the Customers table:

Example

MySQL
1
SELECT CustomerName, City FROM Customers;

SQL SELECT DISTINCT Statement

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 Syntax

MySQL
1
2
SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT Example Without DISTINCT
The following SQL statement selects ALL (including the duplicates) values from the Country column in the Customers table:

Example

MySQL
1
SELECT Country FROM Customers;

Now, let us use the DISTINCT keyword with the above SELECT statement and see the result.

SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the Country column in the Customers table:

Example

MySQL
1
SELECT DISTINCT Country FROM Customers;

The following SQL statement lists the number of different (distinct) customer countries:

MySQL
1
SELECT COUNT(DISTINCT Country) FROM Customers;

SQL WHERE Clause

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

WHERE Syntax

MySQL
1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!

WHERE Clause Example
The following SQL statement selects all the customers from the country Mexico, in the Customers table:

Example

MySQL
1
2
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:

MySQL
1
2
SELECT * FROM Customers
WHERE PostalCOde=05021;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column

The SQL 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.

AND Syntax

MySQL
1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

MySQL
1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax

MySQL
1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

AND Example
The following SQL statement selects all fields from Customers where country is Germany AND city is Berlin:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE Country="Germany" AND City="Berlin";

OR Example
The following SQL statement selects all fields from Customers where city is Berlin OR München:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City='Berlin' OR City='München';

The following SQL statement selects all fields from “Customers” where country is “Germany” OR “Spain”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE Country='Germany' OR Country='Spain';

NOT Example

The following SQL statement selects all fields from “Customers” where country is NOT “Germany”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE NOT Country='Germany';

Combining AND, OR and NOT
You can also combine the AND, OR and NOT operators.

The following SQL statement selects all fields from “Customers” where country is “Germany” AND city must be “Berlin” OR “München” (use parenthesis to form complex expressions):

MySQL
1
2
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

The following SQL statement selects all fields from “Customers” where country is NOT “Germany” and NOT “USA”:

MySQL
1
2
SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';

SQL ORDER BY Keyword

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.

ORDER BY Syntax

MySQL
1
2
3
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

ORDER BY Example
The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” column:

MySQL
1
2
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:

MySQL
1
2
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

MySQL
1
2
SELECT * FROM Customers
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

MySQL
1
2
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

SQL TOP, LIMIT or ROWNUM Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

SQL Server / MS Access Syntax:

MySQL Server
1
2
3
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL Syntax:

MySQL
1
2
3
4
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT {[offset,] row_count | row_count OFFSET offset};

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

  • Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
  • Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

Oracle Syntax:

Oracle
1
2
3
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

SQL LIMIT Example
The following SQL statement selects the first three records from the “Customers” table (MySQL):

MySQL
1
2
SELECT * FROM Customers
LIMIT 3;

ADD a WHERE CLAUSE
The following SQL statement selects the first three records from the “Customers” table, where the country is “Germany” (for MySQL):

MySQL
1
2
3
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

SQL Aggregate Functions

SQL Basic Aggregate Functions

The COUNT() function returns the number of rows that matches a specified criterion.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

COUNT() Syntax

MySQL
1
2
3
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax

MySQL
1
2
3
SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax

MySQL
1
2
3
SELECT SUM(column_name)
FROM table_name
WHERE condition;

MIN() Syntax

MySQL
1
2
3
SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

MySQL
1
2
3
SELECT MAX(column_name)
FROM table_name
WHERE condition;
COUNT() Example

The following SQL statement finds the number of products:

Example

MySQL
1
2
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

MySQL
1
2
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

MySQL
1
2
SELECT SUM(Quantity)
FROM OrderDetails;

Note: NULL values are ignored.

MIN() Example

The following SQL statement finds the price of the cheapest product:

Example

MySQL
1
2
SELECT MIN(Price) AS SmallestPrice
FROM Products;

MAX() Example

The following SQL statement finds the price of the most expensive product:

Example

MySQL
1
2
SELECT MAX(Price) AS LargestPrice
FROM Products;

SQL HAVING Clause

The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

HAVING Syntax

MySQL
1
2
3
4
5
6
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

SQL HAVING Examples
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

Example

MySQL
1
2
3
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

MySQL
1
2
3
4
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

Example

MySQL
1
2
3
4
5
SELECT COUNT(CustomerID), Country
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

SQL Server
1
2
3
4
5
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;
MySQL
1
2
3
4
5
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrder
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName
HAVING NumberOfOrder > 10;

The following SQL statement lists if the employees “Davolio” or “Fuller” have registered more than 25 orders:

Example

MySQL
1
2
3
4
5
6
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrder
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE LastName="Davolio" OR LastName="Fuller"
GROUP BY LastName
HAVING NumberOfOrder > 25;

SQL 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 represents a single character

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

The percent sign and the underscore can also be used in combinations!

LIKE Syntax

MySQL
1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE columnN 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 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"

SQL LIKE Examples
The following SQL statement selects all customers with a CustomerName starting with “a”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

The following SQL statement selects all customers with a CustomerName ending with “a”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

The following SQL statement selects all customers with a CustomerName that have “or” in any position:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

The following SQL statement selects all customers with a CustomerName that have “r” in the second position:

Example

MySQL
1
2
SELECT * FROM Customers
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:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

The following SQL statement selects all customers with a ContactName that starts with “a” and ends with “o”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';

The following SQL statement selects all customers with a CustomerName that does NOT start with “a”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

SQL Wildcards

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters in MS Access

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
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
! Represents any character not in the brackets h[!oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt
# Represents any single numeric character 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard Characters in SQL Server

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
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat
- Represents a range of characters c[a-b]t finds cat and cbt

Examples

All 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 characters in length
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

MySQL
1
2
SELECT * FROM Customers
WHERE City LIKE 'ber%';

The following SQL statement selects all customers with a City containing the pattern “es”:

Example

MySQL
1
2
SELECT * FROM Customers
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

MySQL
1
2
SELECT * FROM Customers
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

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

Using the [charlist] Wildcard

The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
Using the [!charlist] Wildcard

The two following SQL statements select all customers with a City NOT starting with “b”, “s”, or “p”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

Or:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

MySQL Regular Expressions

Regular Expressions
Python Tutorial - RegEx

Name Description
NOT REGEXP Negation of REGEXP
REGEXP Whether string matches regular expression
REGEXP_INSTR() Starting index of substring matching regular expression
REGEXP_LIKE() Whether string matches regular expression
REGEXP_REPLACE() Replace substrings matching regular expression
REGEXP_SUBSTR() Return substring matching regular expression
RLIKE Whether string matches regular expression

Syntax

Python Tutorial - RegEx

Metacharacters

Metacharacters are characters with a special meaning:

Character Description Example
[] A set of characters; Or a set for punctuations. "[a-e]"
\ Signals a special sequence(can also be used to escape special characters) "\d"
. Any character (except newline character) "Pa..y"
^ Starts with "^The"
$ Ends with "2020[.]$"
* Zero or more occurrences "isx*"
+ One or more occurrences "ine+"
? Zero or one occurrence "ab?"
{} Exactly the specified number of occurrences "om{2} -> omm"; "os{m, n}"
| Either or "Chi|Com"
() Capture and group "(ine)(se)*";"(ab|bc)"
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Regular Expression
SELECT * FROM Customers
WHERE Country REGEXP 'USA';

-- [] A set of characters; Or a set for punctuations.
SELECT * FROM Customers
WHERE Country REGEXP '[USA]';

-- \ Signals a special sequence(can also be used to escape special characters)	
SELECT * FROM Customers
WHERE CustomerName REGEXP 'Franchi S\.p\.A\.';

-- . Any character (except newline character)	
SELECT * FROM Customers
WHERE Country REGEXP 'U..';

-- ^ Starts with
SELECT * FROM Customers
WHERE Country REGEXP '^U.';

-- $ End with
SELECT * FROM Customers
WHERE Country REGEXP '^U.A$';

-- * Zero or more occurrences
SELECT * FROM Customers
WHERE Country REGEXP '^US*';

-- + One or more occurrences
SELECT * FROM Customers
WHERE Country REGEXP '^US+';

-- ? Zero or one occurrence
SELECT * FROM Customers
WHERE Country REGEXP '^US?';

-- {} Exactly the specified number of occurrences
SELECT * FROM Customers
WHERE Country REGEXP '^US{1}';

-- | Either or
SELECT * FROM Customers
WHERE Country REGEXP '^US|K';

-- () 	Capture and group
SELECT * FROM Customers
WHERE Country REGEXP '^U(S|K)';
Sets []

A set is a set of characters inside a pair of square brackets [] with a special meaning:

Set Description
[arn] Returns a match where one of the specified characters (a, r, or n) are present
[a-n] Returns a match for any lower case character, alphabetically between a and n
[^arn] Returns a match for any character EXCEPT a, r, and n
[0123] Returns a match where any of the specified digits (0, 1, 2, or 3) are present
[0-9] Returns a match for any digit between 0 and 9
[0-5][0-9] Returns a match for any two-digit numbers from 00 and 59
[a-zA-Z] Returns a match for any character alphabetically between a and z, lower case OR upper case
[+] In sets, +, *, ., ｜, (), $,{} has no special meaning, so [+] means: return a match for any + character in the string
character_class

[:character_class:]

Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in the ctype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

Character Class Name Meaning
alnum Alphanumeric characters
alpha Alphabetic characters
blank Whitespace characters
cntrl Control characters
digit Digit characters
graph Graphic characters
lower Lowercase alphabetic characters
print Graphic or space characters
punct Punctuation characters
space Space, tab, newline, and carriage return
upper Uppercase alphabetic characters
xdigit Hexadecimal digit characters
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- Regular Expression
SELECT * FROM Customers
WHERE Country REGEXP 'USA';

-- alnum Alphanumeric characters
SELECT * FROM Customers
WHERE PostalCode REGEXP '^[:alnum:]{5}$';

-- alpha Alphabetic characters
SELECT * FROM Customers
WHERE Country REGEXP '^[:alpha:]{3}$';

-- blank Whitespace characters
SELECT * FROM Customers
WHERE City REGEXP '^[:alpha:]+[:blank:]{1}[:alpha:]+';

SELECT * FROM Customers
WHERE Address REGEXP '^[:alpha:]+[:blank:]{1}[:alpha:]+$';

-- + cntrl Control characters
SELECT * FROM Customers
WHERE Address REGEXP '[:cntrl:]';

-- digit Digit characters
SELECT * FROM Customers
WHERE PostalCode REGEXP '^[:digit:]{5}$';

-- graph Graphic characters
SELECT * FROM Customers
WHERE Country REGEXP '[:graph:]';

-- lower Lowercase alphabetic characters. P.S: Currently, REGEXP is not case sensitive

-- print Graphic or space characters
SELECT * FROM Customers
WHERE Address REGEXP '^[:alpha:]+[:print:]{1}[:alpha:]+$';

-- punct Punctuation characters
SELECT * FROM Customers
WHERE Address REGEXP '^[:alpha:]+[:punct:]{1}.*+$';

-- space Space, tab, newline, and carriage return
SELECT * FROM Customers
WHERE Address REGEXP '^[:alpha:]+[:space:]{1}.*+$';

-- upper Uppercase alphabetic characters. P.S: Currently, REGEXP is not case sensitive

-- xdigit Hexadecimal digit characters
SELECT * FROM Customers
WHERE Phone REGEXP '^[:xdigit:]{1}.*+$';

Examples

Starting with ^

LIKE: The following SQL statement selects all customers with a City starting with “ber”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City LIKE 'ber%';

REGEXP: The following SQL statement selects all customers with a City starting with “ber”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City REGEXP '^ber';

Contains ()

LIKE: The following SQL statement selects all customers with a City containing the pattern “es”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City LIKE '%es%';

REGEX: The following SQL statement selects all customers with a City containing the pattern “es”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City REGEXP '.*es.*';

OR

MySQL
1
2
SELECT * FROM Customers
WHERE City REGEXP '.*(es).*';

LIKE: 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

MySQL
1
2
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

REGEXP: 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

MySQL
1
2
SELECT * FROM Customers
WHERE City REGEXP 'L.{1}n.{1}on';

End with $

LIKE: The following SQL statement selects all customers with a City starting with any character, followed by “ondon”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City LIKE '_ondon';

REGEXP: The following SQL statement selects all customers with a City starting with any character, followed by “ondon”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City REGEXP '.{1}ondon';

Match one of the symbol: REGEXP []

LIKE: The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

REGEXP: The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City REGEXP '^[bsp].*';

LIKE: The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

REGEXP: The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

Inverse Match: NOT REGEXP

The two following SQL statements select all customers with a City NOT starting with “b”, “s”, or “p”:

Example

SQL Server
1
2
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

Example

MySQL
1
2
SELECT * FROM Customers
WHERE City NOT REGEXP '^[bsp].*';

SQL 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

MySQL
1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or:

MySQL
1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

IN Operator Examples
The following SQL statement selects all customers that are located in “Germany”, “France” or “UK”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

or

MySQL
1
2
SELECT * FROM Customers
WHERE Country REGEXP '(Germany)|(France)|(UK)';

The following SQL statement selects all customers that are NOT located in “Germany”, “France” or “UK”:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

or

MySQL
1
2
SELECT * FROM Customers
WHERE Country NOT REGEXP '(Germany)|(France)|(UK)';

The following SQL statement selects all customers that are from the same countries as the suppliers:

Example

MySQL
1
2
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

SQL 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

MySQL
1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

MySQL
1
2
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:

Example

MySQL
1
2
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with IN 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:

Example

MySQL
1
2
3
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Mozzarella di Giovanni:

Example

MySQL
1
2
3
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

The following SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Chef Anton’s Cajun Seasoning:

Example

MySQL
1
2
3
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName NOT BETWEEN Carnarvon Tigers and Mozzarella di Giovanni:

Example

MySQL
1
2
3
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

SQL Alias

SQL 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 the query.

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

Alias Column Syntax

MySQL
1
2
SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

MySQL
1
2
SELECT column_name(s)
FROM table_name AS alias_name;

Alias for Columns Examples

The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

Example

MySQL
1
2
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column.

  • For SQL Server: It requires double quotation marks or square brackets if the alias name contains spaces.
  • For MySQL: It requires double quotation marks or backtick if the alias name contains spaces.

Example

SQL Server
1
2
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
MySQL
1
2
3
4
5
SELECT CustomerName AS Customer, ContactName AS `Contact Person`
FROM Customers;

SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;

The following SQL statement creates an alias named “Address” that combine four columns (Address, PostalCode, City and Country):

Example

SQL Server
1
2
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

Note: To get the SQL statement above to work in MySQL use the following:

MySQL
1
2
SELECT CustomerName, CONCAT(Address, ', ', PostalCode, ', ', City, ', ', Country) AS Address
FROM Customers;

Alias for Tables 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):

Example

MySQL
1
2
3
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;

The following SQL statement is the same as above, but without aliases:

MySQL
1
2
3
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

SQL Joins

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:

MySQL
1
SELECT * FROM Orders;

Orders

Then, look at a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

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:

MySQL
1
2
3
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

INNER JOIN

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

INNER JOIN Syntax

MySQL
1
2
3
4
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

INNER JOIN

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

MySQL
1
SELECT * FROM Orders;

Orders

And a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

SQL INNER JOIN Example
The following SQL statement selects all orders with customer information:

Example

MySQL
1
2
3
4
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:

MySQL
1
2
3
4
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperID, Shippers.CompanyName AS 'Courier Company'
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID;

LEFT JOIN

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

LEFT JOIN Syntax

MySQL
1
2
3
4
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

LEFT JOIN

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

MySQL
1
SELECT * FROM Orders;

Orders

And a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

SQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:

Example

MySQL
1
2
3
4
SELECT Customers.CustomerName, Orders.*
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).

RIGHT JOIN

SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

LEFT JOIN Syntax

MySQL
1
2
3
4
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

RIGHT JOIN

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

MySQL
1
SELECT * FROM Orders;

Orders

And a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have placed:

Example

MySQL
1
2
3
4
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 FULL OUTER JOIN Keyword

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

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

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SQL Server
1
2
3
4
5
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

FULL JOIN

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

MySQL
1
SELECT * FROM Orders;

Orders

And a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

SQL FULL OUTER JOIN Example
The following SQL statement selects all customers, and all orders:

SQL Server
1
2
3
4
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

MySQL does not support FULL OUTER JOIN.

Note: The FULL OUTER 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.

SQL Self JOIN

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

Self JOIN Syntax

MySQL
1
2
3
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

T1 and T2 are different table aliases for the same table.

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

SQL Self JOIN Example
The following SQL statement matches customers that are from the same city:

Example

MySQL
1
2
3
4
5
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;

SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order

UNION Syntax

MySQL
1
2
3
SELECT column_name(s) 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:

MySQL
1
2
3
SELECT column_name(s) 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 in the UNION.

Difference between a JOIN and a UNION

What Is the Difference Between a Join and a UNION?

Joins Combine Columns

Unions Combine Rows

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

And a selection from the “Suppliers” table:

MySQL
1
SELECT * FROM Suppliers;

Suppliers

SQL UNION Example

The following SQL statement returns the cities (only distinct values) from both the “Customers” and the “Suppliers” table:

Example

MySQL
1
2
3
4
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:

MySQL
1
2
3
4
5
6
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement returns the German cities (duplicate values also) from both the “Customers” and the “Suppliers” table:

MySQL
1
2
3
4
5
6
7
Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Another UNION Example

The following SQL statement lists all customers and suppliers:

Example

MySQL
1
2
3
4
5
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”.

SQL GROUP BY Statement

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

MySQL
1
2
3
4
5
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Examples

The following SQL statement lists the number of customers in each country:

MySQL
1
2
3
4
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY Country;

The following SQL statement lists the number of customers in each country, sorted high to low:

Example

MySQL
1
2
3
4
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:

MySQL
1
SELECT * FROM Orders;

Orders

And a selection from the “Shippers” table:

MySQL
1
SELECT * FROM Shippers;

Shippers

GROUP BY With JOIN Example
The following SQL statement lists the number of orders sent by each shipper:

Example

First we get the Shippers.CompanyName for each OrderID Orders.OrderID.

MySQL
1
2
3
SELECT Shippers.CompanyName, Orders.OrderID
FROM Orders
LEFT JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID;

Then we can count Orders.OrderID.

MySQL
1
2
3
4
SELECT Shippers.CompanyName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID
GROUP BY CompanyName;

SQL Subquery

SQL EXISTS Operator

The SQL 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

MySQL
1
2
3
4
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Products” table:

MySQL
1
SELECT * FROM Products;

Products

And a selection from the “Suppliers” table:

MySQL
1
SELECT * FROM Suppliers;

Suppliers

SQL EXISTS Examples
The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

Example

MySQL
1
2
3
SELECT CompanyName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.SupplierID AND Price < 20);

OR you can explore more details, such as which product has price less then 20.

MySQL
1
2
3
4
SELECT Suppliers.CompanyName, Products.*
FROM Suppliers
LEFT JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE Price < 20;

The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:

Example

MySQl
1
2
3
SELECT CompanyName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.SupplierID AND Price = 22);

SQL ANY and ALL Operators

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

ANY Syntax

MySQL
1
2
3
4
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

ALL Syntax

MySQL
1
2
3
4
SELECT column_name(s)
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 <=).

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Products” table:

MySQL
1
SELECT * FROM Products;

Products

And a selection from the “OrderDetails” table:

MySQL
1
SELECT * FROM OrderDetails;

OrderDetails

SQL ANY Examples

The ANY operator returns TRUE if any of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity = 10:

Example

MySQL
1
2
3
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

The following SQL statement returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity > 99:

Example

MySQL
1
2
3
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);

SQL ALL Example

The ALL operator returns TRUE if all of the subquery values meet the condition.

The following SQL statement returns TRUE and lists the product names if ALL the records in the OrderDetails table has quantity = 10 (so, this example will return FALSE, because not ALL records in the OrderDetails table has quantity = 10):

Example

MySQL
1
2
3
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

SQL NULL

SQL NULL Values

What is a NULL Value?
A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

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

MySQL
1
2
3
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

MySQL
1
2
3
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

MySQL
1
2
3
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:

MySQL
1
2
3
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

SQL NULL Functions

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

UPDATE Products.UnitsInStock to NULL WHERE ProductName=’Ikura’

MySQL
1
2
3
4
-- UPDATE Products.UnitsInStock to NULL WHERE ProductName='Ikura'
UPDATE Products
SET UnitsInStock=NULL
WHERE ProductName='Ikura';

Look at the following “Products” table:

MySQL
1
2
SELECT ProductName, Price * (UnitsInStock + UnitsOnOrder) AS Cost
FROM Products;

In the example above, if any of the “UnitsOnOrder” values are NULL, the result will be NULL

Solution

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:

MySQL
1
2
SELECT ProductName, Price * (UnitsOnOrder + IFNULL(UnitsInStock, 0))
FROM Products;

or we can use the COALESCE() function, like this:

MySQL
1
2
SELECT ProductName, Price * (UnitsOnOrder + COALESCE(UnitsInStock, 0))
FROM Products;

The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:

SQL Server
1
2
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;

The MS Access IsNull() function returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):

MS Access
1
2
SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;

The Oracle NVL() function achieves the same result:

Oracle
1
2
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;

SQL Stored Procedures for SQL Server

What is a Stored Procedure?
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

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

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

SQL Server
1
2
3
4
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

SQL Server
1
EXEC procedure_name;

Stored Procedure Example

The following SQL statement creates a stored procedure named “SelectAllCustomers” that selects all records from the “Customers” table:

Example

SQL Server
1
2
3
4
5
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
Execute the stored procedure above as follows:

Example

SQL Server
1
EXEC SelectAllCustomers;

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the “Customers” table:

Example

SQL Server
1
2
3
4
5
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
Execute the stored procedure above as follows:

Example

SQL Server
1
EXEC SelectAllCustomers @City = 'London';

Stored Procedure With Multiple Parameters

Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the “Customers” table:

Example

SQL Server
1
2
3
4
5
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
Execute the stored procedure above as follows:

Example

SQL Server
1
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

SQL Operators

SQL Arithmetic Operators

Operator Description Example
+ Add Try it
- Subtract Try it
* Multiply Try it
/ Divide Try it
% Modulo Try it

SQL Bitwise Operators

Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR

SQL Comparison Operators

Operator Description Example
= Equal to Try it
> Greater than Try it
< Less than Try it
>= Greater than or equal to Try it
<= Less than or equal to Try it
<> Not equal to Try it

SQL Compound Operators

Operator Description
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
|*= Bitwise OR equals

SQL Logical Operators

Operator Description Example
ALL TRUE if all of the subquery values meet the condition Try it
AND TRUE if all the conditions separated by AND is TRUE Try it
ANY TRUE if any of the subquery values meet the condition Try it
BETWEEN TRUE if the operand is within the range of comparisons Try it
EXISTS TRUE if the subquery returns one or more records Try it
IN TRUE if the operand is equal to one of a list of expressions Try it
LIKE TRUE if the operand matches a pattern Try it
NOT Displays a record if the condition(s) is NOT TRUE Try it
OR TRUE if any of the conditions separated by OR is TRUE Try it
SOME TRUE if any of the subquery values meet the condition Try it

SQL 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

MySQL
1
2
3
4
5
6
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “OrderDetails” table:

MySQL
1
SELECT * FROM OrderDetails;

OrderDetails

SQL CASE Examples
The following SQL goes through conditions and returns a value when the first condition is met:

Example

MySQL
1
2
3
4
5
6
7
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;

The following SQL will order the customers by City. However, if City is NULL, then order by Country:

Example

MySQL
1
2
3
4
5
6
7
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

SQL INSERT Statements

SQL INSERT INTO Statement

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

INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

MySQL
1
2
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

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. The INSERT INTO syntax would be as follows:

MySQL
1
2
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

INSERT INTO Example
The following SQL statement inserts a new record in the “Customers” table:

Example

MySQL
1
2
INSERT INTO Customers (CustomerID, CustomerName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, Image, ImageThumbnail)
VALUES ('YOSHI', 'Yoshinoya', 'Eikichi Matsuda', 'Owner', '405 N Vineyard Ave Unit C', 'Ontario', 'CA', '91764', 'USA', '(909) 937-6832', '(909) 937-6832', Null, Null)

The selection from the “Customers” table will now look like this:

MySQL
1
2
SELECT * FROM Customers
WHERE ContactName='Eikichi Matsuda';

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 “CustomerID”, “CustomerName”, “ContactName”, “Address”, “City”, “Country”, and “Country” columns.

Example

MySQL
1
2
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('CARDI', 'Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

The selection from the “Customers” table will now look like this:

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerID='CARDI';

SQL SELECT INTO Statement

The SELECT INTO statement copies data from one table into a new table.
MySQL Server doesn’t support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing.

See Section INSERT INTO … SELECT Statement.

SELECT INTO Syntax

Copy all columns into a new table:

SQL Server
1
2
3
4
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Copy only some columns into a new table:

SQL Server
1
2
3
4
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:

SQL Server
1
2
SELECT * INTO CustomersBackup2017
FROM Customers;

The following SQL statement uses the IN clause to copy the table into a new table in another database:

SQL Server
1
2
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

The following SQL statement copies only a few columns into a new table:

SQL Server
1
2
SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

The following SQL statement copies only the German customers into a new table:

SQL Server
1
2
3
SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';

The following SQL statement copies data from more than one table into a new table:

SQL Server
1
2
3
4
SELECT Customers.CustomerName, Orders.OrderID
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:

SQL Server
1
2
3
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

MySQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

  • INSERT INTO SELECT requires that data types in source and target tables match
  • The existing records in the target table are unaffected

INSERT INTO SELECT Syntax

Copy all columns from one table to another table:

MySQL
1
2
3
INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Copy only some columns from one table into another table:

MySQL
1
2
3
4
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table:

MySQL
1
SELECT * FROM Customers;

Customers

And a selection from the “Suppliers” table:

MySQL
1
SELECT * FROM Suppliers;

Suppliers

SQL INSERT INTO SELECT Examples
The following SQL statement copies “Suppliers” into “Customers” (the columns that are not filled with data, will contain NULL):

MySQL
1
2
INSERT INTO Customers (CustomerName, City, Country)
SELECT CompanyName, City, Country FROM Suppliers;

The following SQL statement copies “Suppliers” into “Customers” (fill all columns):

MySQL
1
2
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

The following SQL statement copies only the German suppliers into “Customers”:

MySQL
1
2
3
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

SQL UPDATE Statement

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

UPDATE Syntax

MySQL
1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

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!

UPDATE Table
The following SQL statement updates the first customer (CustomerID=ALFKI) with a new contact person and a new city.

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerID='ALFKI';

Example

MySQL
1
2
3
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID='ALFKI';

The selection from the “Customers” table will now look like this:

MySQL
1
2
SELECT * FROM Customers
WHERE CustomerID='ALFKI';

UPDATE Multiple Records
It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the contactname to “Juan” for all records where country is “Mexico”:

MySQL
1
2
3
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

The selection from the “Customers” table will now look like this:

Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

SQL DELETE Statement

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

DELETE Syntax

MySQL
1
DELETE FROM table_name WHERE condition;

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!

SQL DELETE Example
The following SQL statement deletes the customer “Alfreds Futterkiste” from the “Customers” table:

MySQL
1
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:

MySQL
1
DELETE FROM table_name;

The following SQL statement deletes all rows in the “Customers” table, without deleting the table:

Example

MySQL
1
DELETE FROM Customers;

Data Definition Statements

Data Definition Statements

CREATE FUNCTION Statement

CREATE FUNCTION Statement

CREATE FUNCTION Statement for User-Defined Functions

CREATE FUNCTION Statement for User-Defined Functions
MySQL | Creating stored function

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

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

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

The CREATE FUNCTION statement require CREATE ROUTINE database privilege.

Syntax:
The syntax for CREATE FUNCTION statement in Mysql is:

MySQL
1
2
3
CREATE FUNCTION function_name(func_parameter1, func_parameter2, ..)
          RETURN datatype [characteristics]
          func_body

Parameters used:

  1. function_name:
    It is the name by which stored function is called. The name should not be same as native(built_in) function. In order to associate routine explicitly with a specific database function name should be given as database_name.func_name.
  2. func_parameter:
    It is the argument whose value is used by the function inside its body. You can’t specify to these parameters IN, OUT, INOUT. The parameter declaration inside parenthesis is provided as func_parameter type. Here, type represents a valid Mysql datatype.
  3. datatype:
    It is datatype of value returned by function.
  4. characteristics:
    The CREATE FUNCTION statement is accepted only if at least one of the characterisitics { DETERMINISTIC, NO SQL, or READS SQL DATA } is specified in its declaration.

func_body is the set of Mysql statements that perform operation. It’s structure is as follows:

MySQL
1
2
3
4
5
6
BEGIN

        Mysql Statements

        RETURN expression;
END

The function body must contain one RETURN statement.

To DROP a function

MySQL
1
DROP FUNCTION [IF EXISTS] function_name;

To show a function details in MySQL Workbench:

Example: Return the shipped days from the table orders

MySQL
1
SELECT * FROM orders;

The ShippedDate shows the date of shippment.

Define the function ShippedDays() (shipped days of the orders). It is equal to the current days - ShippedDate.

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Return the shipped days of an order
DELIMITER //

CREATE FUNCTION ShippedDays(date1 DATE) RETURNS int DETERMINISTIC
BEGIN
 DECLARE date2 DATE;
  SELECT current_date() INTO date2;
  RETURN date(date2) - date(date1);
END 

//

DELIMITER ;

Call function ShippedDays and pass the argument ShippedDate.

MySQL
1
2
3
4
5
6
7
8
SELECT
	OrderID,
    CustomerID,
    DATE(ShippedDate) AS ShippedDate,
    current_date() AS CurrentDate,
    shippedDays(ShippedDate) AS ShippedDays
FROM
	Orders;

Example: Return the Nth highest price from the table products

For instance, we want to see the 5th highest price with scale 2.

MySQL
1
2
3
4
5
6
SELECT 
	*
FROM
	products
ORDER BY
	Price DESC;

Define the function getNthHighest()

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Return the Nth highest price from the table products
DELIMITER //

CREATE FUNCTION getNthHighest(N INT) RETURNS DECIMAL(10, 2) DETERMINISTIC
BEGIN
 DECLARE M INT;
 DECLARE NthHighestPrice DECIMAL(10, 2);
 SET M = N - 1;
	SELECT
		(SELECT
			DISTINCT Price
		FROM
			products
		ORDER BY
			Price DESC
		LIMIT
			1
		OFFSET
			M) INTO NthHighestPrice;
	RETURN NthHighestPrice;
END 

//

DELIMITER ;

Call function getNthHighest().

MySQL
1
SELECT getNthHighest(5);

Data Manipulation Statements

Data Manipulation Statements

WITH (Common Table Expressions)

WITH (Common Table Expressions)
MySQL | Recursive CTE (Common Table Expressions)

What is a CTE?
In MySQL every query generates a temporary result or relation. In order to give a name to those temporary result set, CTE is used.

  • A CTE is defined using WITH clause.
  • Using WITH clause we can define more than one CTEs in a single statement.
  • A CTE can be referenced in the other CTEs that are part of same WITH clause but those CTEs should be defined earlier.
  • The scope of every CTE exist within the statement in which it is defined.

A recursive CTE is a subquery which refer to itself using its own name.

  • The recursive CTEs are defined using WITH RECURSIVE clause.
  • There should be a terminating condition to recursive CTE.
  • The recursive CTEs are used for series generation and traversal of hierarchical or tree-structured data.
MySQL
1
2
3
4
WITH RECURSIVE 
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;
  • cte_name: Name given to recursive subquery written in subquery block.
  • col1, col2, …colN: The name given to columns generated by subquery.
  • subquery: A MySql query that refer to itself using cte_name as its own name.

Here, the column names given in SELECT statement should match with the names provided in list followed by cte_name.

The recursive CTE structure provided in the subquery block:

MySQL
1
2
3
4
Select col1, col2, ... coln from table_name        
UNION [ALL, DISTINCT]
Select col1, col2, ...coln from cte_name          
WHERE clause

The recursive CTE consist of a non-recursive subquery followed by a recursive subquery-

  • The first select statement is a non-recursive statement, which provides initial rows for result set.
  • UNION [ALL, DISTINCT] is use to add additional rows to previous result set. Use of ALL and DISTINCT keyword are used to include or eliminate duplicate rows in the last result set.
  • The second select statement is a recursive statement which produces result set iteratively until the condition provided in WHERE clause is true.
  • The result set produced at each iteration take result set produced at previous iteration as the base table.
  • The recursion ends when the recursive select statement doesn’t produce any additional rows.

Example 1

Consider, following query which genearte a series of first 5 odd numbers

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH RECURSIVE 
odd_no (sr_no, n) AS -- recursive name is odd_no
(
SELECT -- non-recursive: 1st query
	1, 1 
UNION ALL
SELECT -- recursive query: the rest queries
	sr_no+1, n+2
FROM 
	odd_no 
WHERE 
	sr_no < 5 
)
SELECT * FROM odd_no; -- select specific rows from odd_no

Explanation:

The above query consists of two parts- non-recursive and recursive.

Non-Recursive part- It will produce initial rows which consist of two columns named as “sr_no” and “n” and single row.

Example 2

Consider the following “bst” table.

MySQL
1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS bst (node INT, parent INT);
TRUNCATE bst;
INSERT bst VALUE (1, NULL);
INSERT bst VALUE (2, 1);
INSERT bst VALUE (3, 1);
INSERT bst VALUE (4, 2);
INSERT bst VALUE (5, 2);
INSERT bst VALUE (6, 3);
INSERT bst VALUE (7, 3);

The above table “bst” consist of two columns “node” and “parent” which gives the value of a node in the binary search tree and their respective parent value.

Problem description: We have to find paths of all the node in the given “bst”.

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH RECURSIVE
cte (node, path) AS -- recursive name is cte
( 
SELECT -- non-recursive: 1st query
	node, 
    CAST(1 AS CHAR(30))
FROM 
	bst 
WHERE 
	parent IS NULL
UNION ALL
SELECT -- recursive query: the rest queries
	bst.node,
    CONCAT(cte.path, '-->', bst.node) 
FROM 
	cte 
JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node; -- select specific rows from cte

Explanation:
Here, non-recursive part in above CTE will give only a single row that consists of a root node along with its path which is set as 1.

MySQL
1
2
3
4
5
6
7
SELECT 
    node, 
    CAST(1 as char(30) )  
FROM 
    bst 
WHERE 
    parent IS NULL

Recursive part-

MySQL
1
2
3
4
5
SELECT 
    bst.node,  
    CONCAT(cte.path, '-->', bst.node ) 
FROM 
    cte JOIN bst ON cte.node = bst.parent

The recursive SELECT statement will find all those nodes in bst whose parent is the node produced in the previous iteration.

Such iteration ends when node i.e(leaf node) produced in the previous iteration doesn’t consist of any child node in bst .

Exmaple 3

All People Report to the Given Manager

Schema

MySQL
1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS Employees;
Create table If Not Exists Employees (employee_id int, employee_name varchar(30), manager_id int);
Truncate table Employees;
insert into Employees (employee_id, employee_name, manager_id) values ('1', 'Boss', '1');
insert into Employees (employee_id, employee_name, manager_id) values ('3', 'Alice', '3');
insert into Employees (employee_id, employee_name, manager_id) values ('2', 'Bob', '1');
insert into Employees (employee_id, employee_name, manager_id) values ('4', 'Daniel', '2');
insert into Employees (employee_id, employee_name, manager_id) values ('7', 'Luis', '4');
insert into Employees (employee_id, employee_name, manager_id) values ('8', 'John', '3');
insert into Employees (employee_id, employee_name, manager_id) values ('9', 'Angela', '8');
insert into Employees (employee_id, employee_name, manager_id) values ('77', 'Robert', '1');

Table Employees

MySQL
1
2
3
4
5
6
7
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
+---------------+---------+
  • employee_id is the primary key for this table.
  • Each row of this table indicates that the employee with ID employee_id and name employee_name reports his work to his/her direct manager with manager_id
  • The head of the company is the employee with employee_id = 1.

Write an SQL query to find employee_id of all employees that directly or indirectly report their work to the head of the company.

The indirect relation between managers will not exceed 3 managers as the company is small.

Return result table in any order without duplicates.

The query result format is in the following example:

Employees table:

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1           | Boss          | 1          |
| 3           | Alice         | 3          |
| 2           | Bob           | 1          |
| 4           | Daniel        | 2          |
| 7           | Luis          | 4          |
| 8           | Jhon          | 3          |
| 9           | Angela        | 8          |
| 77          | Robert        | 1          |
+-------------+---------------+------------+

Result table:

MySQL
1
2
3
4
5
6
7
8
+-------------+
| employee_id |
+-------------+
| 2           |
| 77          |
| 4           |
| 7           |
+-------------+

The head of the company is the employee with employee_id 1.
The employees with employee_id 2 and 77 report their work directly to the head of the company.
The employee with employee_id 4 report his work indirectly to the head of the company 4 –> 2 –> 1.
The employee with employee_id 7 report his work indirectly to the head of the company 7 –> 4 –> 2 –> 1.
The employees with employee_id 3, 8 and 9 don’t report their work to head of company directly or indirectly.

Solution 1: With head and initial node

The curical concetp of MySQL Recursive CTE is the first non-recursive query and the rest recursive queries.

MySQL | Recursive CTE (Common Table Expressions) By Tanvi_Garg

Solution 1 non-recursive query: using the head as root node.

  • employee_id: 1
  • manager_id: 1

Solution 1 recursive query: the partent node from every pervious iteration.

Root Node

MySQL
1
2
3
4
5
6
7
8
-- Select the head as root node
SELECT 
	employee_id, 
    manager_id
FROM 
	Employees
WHERE 
	employee_id = 1;
employee_id manager_id
1 1

Solution 1: Path

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Solution 1: Path
WITH RECURSIVE
CTE (employee_id, manager_id) AS -- recursive name is CTE
(
SELECT -- non-recursive: 1st query, the head
	employee_id,
    CAST(manager_id AS CHAR(30))
FROM 
	Employees
WHERE
	employee_id = 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the head
	e.employee_id,
    CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
	CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
HAVING 
	e.employee_id != 1
)
SELECT -- select specific rows from cte
	*
FROM 
	CTE
ORDER BY 
	employee_id;
employee_id manager_id
1 1
2 2-->1
4 4-->2-->1
7 7-->4-->2-->1
77 77-->1

Solution 1: Answer

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Solution 1: Answer
WITH RECURSIVE
CTE (employee_id
	-- , manager_id
) AS -- recursive name is CTE
(
SELECT -- non-recursive: 1st query, the head
	employee_id
    -- , CAST(manager_id AS CHAR(30))
FROM 
	Employees
WHERE
	employee_id = 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the head
	e.employee_id
    -- , CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
	CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
HAVING 
	e.employee_id != 1
)
SELECT -- select specific rows from cte
	*
FROM 
	CTE
WHERE employee_id != 1 -- Filter the root node that is the head in solution 1
ORDER BY 
	employee_id;
employee_id
2
4
7
77
Solution 2: Without head and initial node

Solution 2 non-recursive query: the two second level nodes as root nodes.

  • employee_id: 2 & 77 (employee_id != 1)
  • manager_id: 1 (manager_id = 1)

Solution 2 recursive query: the partent node from every pervious iteration.

Root Node

MySQL
1
2
3
4
5
6
7
8
9
10
-- Select the two second level nodes as root nodes
SELECT 
	employee_id, 
    manager_id
FROM 
	Employees
WHERE 
	manager_id = 1 
	AND 
	employee_id != 1;
employee_id manager_id
2 1
77 1

Solution 2: Path

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Solution 2: Path
WITH RECURSIVE
CTE (employee_id, manager_id) AS -- recursive name is CTE
(
SELECT -- non-recursive query: the two second level nodes as root nodes.
	employee_id,
    CAST(manager_id AS CHAR(30))
FROM 
	Employees
WHERE 
	manager_id = 1 
	AND 
	employee_id != 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the two second level nodes
	e.employee_id,
    CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
	CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
)
SELECT 
	*
FROM 
	CTE
ORDER BY 
	employee_id;
employee_id manager_id
2 1
4 4-->1
7 7-->4-->1
77 1

Solution 2: Answer

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Solution 2: Answer
WITH RECURSIVE -- recursive name is CTE
CTE (employee_id
	-- , manager_id
    ) AS 
(
SELECT -- non-recursive query: the two second level nodes as root nodes.
	employee_id
    -- , CAST(manager_id AS CHAR(30))
FROM 
	Employees
WHERE 
	manager_id = 1 
	AND 
	employee_id != 1
UNION ALL
SELECT -- recursive query: the rest queries and the path to the two second level nodes
	e.employee_id
    -- , CONCAT(e.employee_id, '-->', c.manager_id)
FROM 
	CTE c
INNER JOIN Employees e ON c.employee_id = e.manager_id
)
SELECT
	*
FROM 
	CTE
ORDER BY 
	employee_id;
employee_id
2
4
7
77

SQL Database

SQL Data Types

SQL Data Types

The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

Note: Data types might have different names in different database. And even if the name is the same, the size and other details may be different! Always check the documentation!

MySQL Data Types (Version 8.0)

In MySQL there are three main data types: string, numeric, and date and time.

String Data Types

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

Numeric Data Types

Data type Description
BIT(size) A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.
TINYINT(size) A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255)
BOOL Zero is considered as false, nonzero values are considered as true.
BOOLEAN Equal to BOOL
SMALLINT(size) A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255)
MEDIUMINT(size) A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255)
INT(size) A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
INTEGER(size) Equal to INT(size)
BIGINT(size) A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255)
FLOAT(size, d) A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions
FLOAT(p) A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()
DOUBLE(size, d) A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter
DOUBLE PRECISION(size, d)  
DECIMAL(size, d) An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.
DEC(size, d) Equal to DECIMAL(size,d)

Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.

Date and Time Data Types

Data type Description
DATE A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
DATETIME(fsp) A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time
TIMESTAMP(fsp) A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition
TIME(fsp) A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
YEAR A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
MySQL 8.0 does not support year in two-digit format.

Database Operations

Show database info

Show all databses’ name

MySQL
1
SHOW DATABASES;

Show which database is using

MySQL
1
SELECT DATABASE();

SQL CREATE DATABASE Statement

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

Syntax

MySQL
1
CREATE DATABASE databasename;

SQL DROP DATABASE Statement

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

Syntax

MySQL
1
2
DROP DATABASE databasename;

Note: Be careful before dropping a database. Deleting a database will result in loss of complete information stored in the database!

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;

SQL BACKUP DATABASE for SQL Server

The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database.

Syntax

SQL Server
1
2
BACKUP DATABASE databasename
TO DISK = 'filepath';

The SQL BACKUP WITH DIFFERENTIAL Statement

A differential back up only backs up the parts of the database that have changed since the last full database backup.

Syntax

SQL Server
1
2
3
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

BACKUP DATABASE Example

The following SQL statement creates a full back up of the existing database “testDB” to the D disk:

Example

SQL Server
1
2
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak';

Tip: Always back up the database to a different drive than the actual database. Then, if you get a disk crash, you will not lose your backup file along with the database.

BACKUP WITH DIFFERENTIAL Example

The following SQL statement creates a differential back up of the database “testDB”:

Example

SQL Server
1
2
3
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak'
WITH DIFFERENTIAL;

Tip: A differential back up reduces the back up time (since only the changes are backed up).

Table Operations

Show table infor

Basic info

MySQL
1
SHOW TABLES;

Basic info include table type

MySQL
1
SHOW FULL TABLES;

Show detailed info for a specifc table

MySQL
1
DESCRIBE [table_name];

Show detailed info for all tables

MySQL
1
SHOW TABLE STATUS;

SQL CREATE TABLE Statement

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

Syntax

MySQL
1
2
3
4
5
6
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 Data Types Reference.

SQL CREATE TABLE Example

The following example creates a table called “Persons” that contains five columns: PersonID, LastName, FirstName, Address, and City:

Example

MySQL
1
2
3
4
5
6
7
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.

Tip: The empty “Persons” table can now be filled with data with the MySQL INSERT INTO SELECT statement.

Example

MySQL
1
2
3
4
CREATE TABLE Person (
	Id INT,
    Email VARCHAR(30)
    );

MySQL
1
2
3
4
5
6
Truncate table Person;
insert into Person (Id, Email) values ('1', 'john@example.com');
insert into Person (Id, Email) values ('2', 'bob@example.com');
insert into Person (Id, Email) values ('3', 'john@example.com');

SELECT * FROM Person;

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.

Syntax

MySQL
1
2
3
4
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

MySQL
1
2
3
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

SQL Primary Key

SQL Primary Key
SQL by Design: Choosing a Primary Key

  • Accessibility: Each database table needs a primary key because it ensures row-level accessibility.
  • Only one Primary key: Each table has one and only one primary key, which can consist of one or many columns.
  • Never Null: No primary key value can be null, nor can you do anything to render the primary key null.
  • Brevity: Because the SQL Server query processor uses the primary key index for lookups and comparisons, choose a brief primary key—one column, if possible.
  • Simplicity: When choosing a primary key, look for candidates with no embedded spaces, special characters, or differential capitalization.
  • Data Type: Integer is the best.
  • Nonidentifying Value: Primary key should be meaningless.
  • Never Change: After you assign a primary key value, never change it.
  • Surrogate Keys: Here’s one method to ensure that the primary key value is never null, brief, a simple data type, and a nonidentifying value: Create the primary key column as a surrogate key. (unique key could be null)

SQL 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).

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:

MySQL
1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    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:

MySQL
1
2
3
4
5
6
7
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    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).

SQL 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:

MySQL
1
2
ALTER TABLE Persons
ADD 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:

1
2
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:

MySQL
1
2
ALTER TABLE Persons  
DROP PRIMARY KEY;

SQL 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](https://www.w3schools.com/sql/sql_primarykey.asp) 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:

Persons Table

PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20

Orders Table

OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1

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.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the “PersonID” column when the “Orders” table is created:

MySQL
1
2
3
4
5
6
7
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 multiple columns, use the following SQL syntax:

MySQL
1
2
3
4
5
6
7
8
CREATE TABLE Orders (  
    OrderID int NOT NULL,  
    OrderNumber int NOT NULL,  
    PersonID int,  
    PRIMARY KEY (OrderID),  
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)  
    REFERENCES Persons(PersonID)  
);

SQL 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:

MySQL
1
2
ALTER TABLE Orders  
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL
1
2
3
ALTER TABLE Orders  
ADD CONSTRAINT FK_PersonOrder  
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL
1
2
ALTER TABLE Orders  
DROP FOREIGN KEY FK_PersonOrder;

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create database school;
use school;

drop table if exists students;
drop table if exists enrollment;
drop table if exists courses;
create table if not exists students (id int, name varchar(30), primary key (id));
insert into students (id, name) values (1, 'zacks');
create table if not exists courses (id int, name varchar(30), primary key (id));
insert into courses (id, name) values (1, 'English');
insert into courses (id, name) values (2, 'Math');
create table if not exists enrollment (student_id int, course_id int, foreign key (course_id) references courses(id));
-- create table if not exists enrollment (student_id int, course_id int, constraint FK_courses foreign key (course_id) references courses(id));
insert into enrollment (student_id, course_id) values (1, 1);
insert into enrollment (student_id, course_id) values (1, 2);
-- couldn't work if using foreign key to refer other courses since there is no id = 3 in courese
-- insert into enrollment (student_id, course_id) values (1, 3);

describe enrollment;

select * from students;
select * from courses;
select * from enrollment;

-- couldn't work if constraint FK_courses foreign key (course_id) references courses(id)
-- could work if foreign key (course_id) references courses(id)
delete from courses where id = 2;
-- could work
delete from enrollment where course_id = 2;
select * from courses;
select * from enrollment;

Conclusion:

  1. with either foreign key (course_id) references courses(id) or constraint FK_courses foreign key (course_id) references courses(id), you cannot insert (1, 3) into enrollment since there is no id=3 in courses.
  2. with constraint FK_courses foreign key (course_id) references courses(id), you cannot delete from courses where id = 2; since 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.
  3. with foreign key (course_id) references courses(id), you can delete from courses where id = 2;.

SQL Keywords

This SQL keywords reference contains the reserved words in SQL.

SQL Keywords

Keyword Description
ADD Adds a column in an existing table
ADD CONSTRAINT Adds a constraint after a table is already created
ALTER Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table
ALTER COLUMN Changes the data type of a column in a table
ALTER TABLE Adds, deletes, or modifies columns in a table
ALL Returns true if all of the subquery values meet the condition
AND Only includes rows where both conditions is true
ANY Returns true if any of the subquery values meet the condition
AS Renames a column or table with an alias
ASC Sorts the result set in ascending order
BACKUP DATABASE Creates a back up of an existing database
BETWEEN Selects values within a given range
CASE Creates different outputs based on conditions
CHECK A constraint that limits the value that can be placed in a column
COLUMN Changes the data type of a column or deletes a column in a table
CONSTRAINT Adds or deletes a constraint
CREATE Creates a database, index, view, table, or procedure
CREATE DATABASE Creates a new SQL database
CREATE INDEX Creates an index on a table (allows duplicate values)
CREATE OR REPLACE VIEW Updates a view
CREATE TABLE Creates a new table in the database
CREATE PROCEDURE Creates a stored procedure
CREATE UNIQUE INDEX Creates a unique index on a table (no duplicate values)
CREATE VIEW Creates a view based on the result set of a SELECT statement
DATABASE Creates or deletes an SQL database
DEFAULT A constraint that provides a default value for a column
DELETE Deletes rows from a table
DESC Sorts the result set in descending order
DISTINCT Selects only distinct (different) values
DROP Deletes a column, constraint, database, index, table, or view
DROP COLUMN Deletes a column in a table
DROP CONSTRAINT Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint
DROP DATABASE Deletes an existing SQL database
DROP DEFAULT Deletes a DEFAULT constraint
DROP INDEX Deletes an index in a table
DROP TABLE Deletes an existing table in the database
DROP VIEW Deletes a view
EXEC Executes a stored procedure
EXISTS Tests for the existence of any record in a subquery
FOREIGN KEY A constraint that is a key used to link two tables together
FROM Specifies which table to select or delete data from
FULL OUTER JOIN Returns all rows when there is a match in either left table or right table
GROUP BY Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
HAVING Used instead of WHERE with aggregate functions
IN Allows you to specify multiple values in a WHERE clause
INDEX Creates or deletes an index in a table
INNER JOIN Returns rows that have matching values in both tables
INSERT INTO Inserts new rows in a table
INSERT INTO SELECT Copies data from one table into another table
IS NULL Tests for empty values
IS NOT NULL Tests for non-empty values
JOIN Joins tables
LEFT JOIN Returns all rows from the left table, and the matching rows from the right table
LIKE Searches for a specified pattern in a column
LIMIT Specifies the number of records to return in the result set
NOT Only includes rows where a condition is not true
NOT NULL A constraint that enforces a column to not accept NULL values
OR Includes rows where either condition is true
ORDER BY Sorts the result set in ascending or descending order
OUTER JOIN Returns all rows when there is a match in either left table or right table
PRIMARY KEY A constraint that uniquely identifies each record in a database table
PROCEDURE A stored procedure
RIGHT JOIN Returns all rows from the right table, and the matching rows from the left table
ROWNUM Specifies the number of records to return in the result set
SELECT Selects data from a database
SELECT DISTINCT Selects only distinct (different) values
SELECT INTO Copies data from one table into a new table
SELECT TOP Specifies the number of records to return in the result set
SET Specifies which columns and values that should be updated in a table
TABLE Creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table
TOP Specifies the number of records to return in the result set
TRUNCATE TABLE Deletes the data inside a table, but not the table itself
UNION Combines the result set of two or more SELECT statements (only distinct values)
UNION ALL Combines the result set of two or more SELECT statements (allows duplicate values)
UNIQUE A constraint that ensures that all values in a column are unique
UPDATE Updates existing rows in a table
VALUES Specifies the values of an INSERT INTO statement
VIEW Creates, updates, or deletes a view
WHERE Filters a result set to include only records that fulfill a specified condition

MySQL Functions

MySQL has many built-in functions.

This reference contains string, numeric, date, and some advanced functions in MySQL.

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)
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 CONCAT() Function

Definition and Usage
The CONCAT() function adds two or more expressions together.

Note: Also look at the CONCAT_WS() function.

Syntax

MySQL
1
CONCAT(expression1, expression2, expression3,...)

Parameter Values

Parameter Description
expression1,
expression2,
expression3,
etc.		 Required. The expressions to add together.

Note: If any of the expressions is a NULL value, it returns NULL

Example

MySQL
1
2
SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address
FROM Customers;

MySQL CONCAT_WS() Function

Definition and Usage
The CONCAT_WS() function adds two or more expressions together with a separator.

Note: Also look at the CONCAT() function.

Syntax

MySQL
1
CONCAT_WS(separator, expression1, expression2, expression3,...)

Parameter Values

Parameter Description
separator Required. The separator to add between each of the expressions. If separator is NULL, this function returns NULL
expression1,
expression2,
expression3,
etc.		 Required. The expressions to add together. An expression with a NULL value will be skipped

Example
Add three columns (and add a space between them) into one “Address” column:

MySQL
1
2
SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;

MySQL LOWER() Function

Definition and Usage
The LOWER() function converts a string to lower-case.

Note: The LCASE() function is equal to the LOWER() function.

Syntax

MySQL
1
LOWER(text)

Parameter Values

Parameter Description
text Required. The string to convert

Example

Convert the text in “CustomerName” to upper-case:

MySQL
1
2
SELECT LOWER(CustomerName) AS LowercaseCustomerName
FROM Customers;

MySQL LTRIM() Function

Definition and Usage
The LTRIM() function removes leading spaces from a string.

Syntax

MySQL
1
LTRIM(string)

Parameter Values

Parameter Description
string Required. The string to remove leading spaces from

MySQL RTRIM() Function

Definition and Usage
The RTRIM() function removes trailing spaces from a string.

Syntax

MySQL
1
RTRIM(string)

Parameter Values

Parameter Description
string Required. The string to remove trailing spaces from

MySQL SUBSTRING() Function

Definition and Usage
The SUBSTRING() function extracts a substring from a string (starting at any position).

Note: The SUBSTR() and MID() functions equals to the SUBSTRING() function.

Syntax

MySQL
1
SUBSTRING(string, start, length)

OR:

MySQL
1
SUBSTRING(string FROM start FOR length)

Parameter Values

Parameter Description
string Required. The string to extract from
start Required. The start position. Can be both a positive or negative number. If it is a positive number, this function extracts from the beginning of the string. If it is a negative number, this function extracts from the end of the string
length Optional. The number of characters to extract. If omitted, the whole string will be returned (from the start position)

Example
Extract a substring from the text in a column (start at position 2, extract 5 characters):

MySQL
1
2
SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString
FROM Customers;

Example
Extract a substring from a string (start from the end, at position -5, extract 5 characters):

MySQL
1
SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;

MySQL TRIM() Function

Definition and Usage
The TRIM() function removes leading and trailing spaces from a string.

Syntax

MySQL
1
TRIM(string)

Parameter Values

Parameter Description
string Required. The string to remove leading and trailing spaces from

MySQL UPPER() Function

Definition and Usage
The UPPER() function converts a string to upper-case.

Note: This function is equal to the UCASE() function.

Syntax

MySQL
1
UPPER(text)

Parameter Values

Parameter Description
text Required. The string to convert

Example

Convert the text in “CustomerName” to upper-case:

MySQL
1
2
SELECT UPPER(CustomerName) AS UppercaseCustomerName
FROM Customers;

MySQL Numeric Functions

Function Description
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
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

MySQL ROUND() Function

Definition and Usage

The ROUND() function rounds a number to a specified number of decimal places.

Note: See also the FLOOR(), CEIL(), CEILING(), and TRUNCATE() functions.

Parameter Values

Parameter Description
number Required. The number to be rounded
decimals Optional. The number of decimal places to round number to. If omitted, it returns the integer (no decimals)

Example

MySQL
1
2
SELECT ROUND(345.156, 0);
-- 345

Example

Round the Price column (to 1 decimal) in the “Products” table:

MySQL
1
2
SELECT ProductName, Price, ROUND(Price, 1) AS RoundedPrice
FROM Products;

MySQL Date Functions

Function Description
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
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

MySQL DATE() Function

Definition and Usage
The DATE() function extracts the date part from a datetime expression.

Syntax

MySQL
1
DATE(expression)

Parameter Values

Parameter Description
expression Required.  A valid date/datetime value. Returns NULL if expression is not a date or a datetime

Example
Extract the date part:

MySQL
1
2
SELECT DATE("2017-06-15 09:34:21");
-- 2017-06-15

Example
Extract the date part (will return NULL):

MySQL
1
2
SELECT DATE("The date is 2017-06-15");
-- NULL

Example
Extract the date part:

MySQL
1
SELECT DATE(OrderDate) FROM Orders;

MySQL YEAR() Function

Definition and Usage
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).

Syntax

MySQL
1
YEAR(date)

Example

1
2
SELECT YEAR('2020-11-10');
# 2020

MySQL MONTH() Function

Definition and Usage
The MONTH() function returns the month part for a given date (a number from 1 to 12).

Syntax

MySQL
1
MONTH(date)

Parameter Values

Parameter Description
date Required. The date or datetime to extract the month from

Example
Return the month part of a date:

MySQL
1
2
SELECT MONTH("2017-06-15 09:34:21");
'6'

Example
Return the month part of the current system date:

MySQL
1
2
SELECT MONTH(CURDATE());
'3'

MySQL DAY() Function

Definition and Usage
The DAY() function returns the day of the month for a given date (a number from 1 to 31).

Syntax

MySQL
1
SELECT DAY(date)

Example

MySQL
1
2
SELECT DAY('2020-11-10');
# 10

MySQL Now() and CURDATE() Functions

1
2
SELECT NOW();
# '2021-09-30 17:36:24'
1
2
SELECT DATE(NOW());
# '2021-09-30'
1
2
SELECT CURDATE();
# '2021-09-30'

MySQL TIMESTAMP() Function

Definition and Usage
The TIMESTAMP() function returns a datetime value based on a date or datetime value.

Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.

Syntax

MySQL
1
TIMESTAMP(expression, time)

Parameter Values

Parameter Description
expression Required. A date or datetime value
time Optional. A time value to add to expression

Example

MySQL
1
2
3
4
SELECT TIMESTAMP(19991110);
# '1999-11-10 00:00:00'
SELECT TIMESTAMP('1999-11-10', '11:20:59');
# '1999-11-10 11:20:59'

MySQL DATEDIFF() Function

Definition and Usage
The DATEDIFF() function returns the number of days between two date values.

Syntax

MySQL
1
DATEDIFF(date1, date2)

Parameter Values

Parameter Description
date1, date2 Required. Two dates to calculate the number of days between. (date1 - date2)

Example

Return the number of days between two date values:

MySQL
1
2
SELECT DATEDIFF("2017-06-25 09:34:21", "2017-06-15 15:25:35");
-- 10

Example

Return the number of days between two date values:

MySQL
1
2
SELECT DATEDIFF("2017-01-01", "2016-12-24");
-- 8

Example

MySQL
1
2
3
4
5
SELECT DATEDIFF('2020-10-01', 20211001);
# -365

SELECT DATEDIFF(NOW(), 20201001); # 362
# 364

Example

Calculate age:

Not really good

MySQL
1
2
SELECT DATEDIFF(NOW(), 19940225) / 365 AS age;
# '21.9041'

Better solution

MySQL
1
2
3
4
5
SELECT TIMESTAMPDIFF(YEAR, 19991110, CURDATE()) AS age;
# 21

SELECT TIMESTAMPDIFF(YEAR, '1999-11-10', CURDATE()) AS age;
# 21

MySQL TIMESTAMPDIFF() Function

MySQL TIMESTAMPDIFF() function

Syntax

MySQL
1
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

Arguments

Name Description
datetime_expr1 A datetime expression.
datetime_expr1 A datetime expression.
unit An unit, as described in the description.

Example

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
SELECT TIMESTAMPDIFF(YEAR, '1999-11-10', CURDATE());
# 21
SELECT TIMESTAMPDIFF(MONTH, '1999-11-10', CURDATE());
# 262
SELECT TIMESTAMPDIFF(DAY, '1999-11-10', CURDATE());
# 7995
SELECT TIMESTAMPDIFF(HOUR, '1999-11-10', CURDATE());
# 191880
SELECT TIMESTAMPDIFF(MINUTE, '1999-11-10', CURDATE());
# 11512800
SELECT TIMESTAMPDIFF(SECOND, '1999-11-10', CURDATE());
# 690768000

MySQL DATE_FORMAT() Function

MySQL DATE_FORMAT() Function
datetime — Basic date and time types

Definition and Usage
The DATE_FORMAT() function formats a date as specified.

Syntax

MySQL
1
DATE_FORMAT(date, format)

Parameter Values

Parameter Description
date Required. The date to be formatted
format Required. The format to use. Can be one or a combination of the following values:
Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (0 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %x
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%X Year for the week where Sunday is the first day of the week. Used with %V
%x Year for the week where Monday is the first day of the week. Used with %v
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value

Example
Format a date:

MySQL
1
2
SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");
-- 'June 15 2017'

Example

Format a date:

MySQL
1
2
SELECT DATE_FORMAT("2017-06-15", "%W %M %e %Y");
-- 'Thursday June 15 2017'

Example
Format a date:

MySQL
1
SELECT DATE_FORMAT(BirthDate, "%W %M %e %Y") FROM Employees;

Example

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
# '2021-09-30 17:41:33'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
# '2021-09-30 17:41'

SELECT DATE_FORMAT(NOW(), '%y-%m-%D %H:%i');
# '21-09-30th 17:42'

SELECT DATE_FORMAT(NOW(), '%y-%m-%D %w');
# '2021-09-30 4'
SELECT DATE_FORMAT(NOW(), '%y-%m-%D %W');
# '2021-09-30 Thursday'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %a');
# '2021-09-30 Thu'

SELECT DATE_FORMAT(NOW(), '%m/%d/%y');
# '09/30/21'

MySQL STR_TO_DATE() Function

MySQL STR_TO_DATE() Function

Definition and Usage
The STR_TO_DATE() function returns a date based on a string and a format.

Syntax

MySQL
1
STR_TO_DATE(string, format)

Example

MySQL
1
2
3
4
5
6
7
8
SELECT STR_TO_DATE('10-01-2020', '%m-%d-%Y');
# '2020-10-01'

SELECT STR_TO_DATE('October 1 2020', '%M %d %Y');
# '2020-10-01'

SELECT STR_TO_DATE("Monday, August 14, 2017", "%W, %M %d, %Y");
# '2017-08-14'

MySQL ADDDATE() and SUBDATE() Functions

  • ADDDATE() is equal to DATE_ADD()
  • SUBDATE() is equal to DATE_SUB()

Definition and Usage
The DATE_ADD() function adds a time/date interval to a date and then returns the date.

Syntax

1
DATE_ADD(date, INTERVAL value addunit)

Parameter Values

Parameter Description
date Required. The date to be modified
value Required. The value of the time/date interval to add. Both positive and negative values are allowed
addunit Required. The type of interval to add. Can be one of the following values:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Example

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT ADDDATE('2020-10-01', INTERVAL 1 DAY);
# 2020-10-02

SELECT ADDDATE(20201001, INTERVAL 1 DAY);
# 2020-10-02

SELECT ADDDATE('10-01-2020', INTERVAL 1 DAY);
# NULL

SELECT ADDDATE(STR_TO_DATE('10-01-2020', '%m-%d-%Y'), INTERVAL 1 DAY);
# 2020-10-02

SELECT ADDDATE(NOW(), INTERVAL 1 DAY);
# '2021-10-01 17:55:31'

SELECT ADDDATE(CURDATE(), INTERVAL 1 DAY);
# '2021-10-01'

SELECT ADDDATE(CURDATE(), INTERVAL -30 DAY);
# '2021-08-31'

SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY);
# '2021-08-31'

Advanced Examples

MySQL
1
USE northwind;

Select records in a specific data range.

Notice BETWEEN is inclusive - inclusive

MySQL
1
2
3
SELECT *
FROM Orders
WHERE ShippedDate BETWEEN '1994-08-15' AND '1995-08-16';

Select orders that shipped within 10 days after order confirmed.

MySQL
1
2
3
4
5
6
7
SELECT
	OrderId, 
    DATE(OrderDate) AS OrderDate, 
    DATE(ShippedDate) AS ShippedDate,
    DATEDIFF(ShippedDate, OrderDate) AS days
FROM Orders
WHERE ShippedDate <= DATE_ADD(OrderDate, INTERVAL 10 DAY);

OR

MySQL
1
2
3
4
5
6
7
SELECT
	OrderId, 
    DATE(OrderDate) AS OrderDate, 
    DATE(ShippedDate) AS ShippedDate,
    DATEDIFF(ShippedDate, OrderDate) AS days
FROM Orders
WHERE DATEDIFF(ShippedDate, OrderDate) <= 10;

Count orders that didn’t shipped within 30 days:

MySQL
1
2
3
SELECT COUNT(OrderId)
FROM Orders
WHERE DATEDIFF(ShippedDate, OrderDate) > 30;

If orders must be shipped with 30 days, select the qualified orders.

  • COUNT(ShippedDate): because some orders have not shipped yet.
MySQL
1
2
3
SELECT COUNT(OrderId) / (SELECT COUNT(ShippedDate) FROM Orders)
FROM Orders
WHERE DATEDIFF(ShippedDate, OrderDate) <= 30;

MySQL Advanced Functions

Function Description
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 CAST() Function

Definition and Usage
The CAST() function converts a value (of any type) into the specified datatype.

Tip: See also the CONVERT() function.

Syntax

MySQL
1
CAST(value AS datatype)
Parameter Description
value Required. The value to convert
datatype Required. The datatype to convert to. Can be one of the following:
Value Description
DATE Converts value to DATE. Format: "YYYY-MM-DD"
DATETIME Converts value to DATETIME. Format: "YYYY-MM-DD HH:MM:SS"
DECIMAL Converts value to DECIMAL. Use the optional M and D parameters to specify the maximum number of digits (M) and the number of digits following the decimal point (D).
TIME Converts value to TIME. Format: "HH:MM:SS"
CHAR Converts value to CHAR (a fixed length string)
NCHAR Converts value to NCHAR (like CHAR, but produces a string with the national character set)
SIGNED Converts value to SIGNED (a signed 64-bit integer)
UNSIGNED Converts value to UNSIGNED (an unsigned 64-bit integer)
BINARY Converts value to BINARY (a binary string)

Example
Convert a value to a CHAR datatype:

MySQL
1
2
SELECT CAST(150 AS CHAR);
-- 150

Example
Convert a value to a TIME datatype:

MySQL
1
2
SELECT CAST("14:06:10" AS TIME);
-- '14:06:10'

Example
Convert a value to a SIGNED datatype:

MySQL
1
2
SELECT CAST(5-10 AS SIGNED);
'-5'

MySQL COALESCE() Function

Definition and Usage
The COALESCE() function returns the first non-null value in a list.

Syntax

MySQL
1
COALESCE(val1, val2, ...., val_n)

Parameter Values

Parameter Description
val1, val2, val_n Required. The values to test

MySQL CONVERT() Function

Definition and Usage
The CONVERT() function converts a value into the specified datatype or character set.

Tip: Also look at the CAST() function.

Syntax

MySQL
1
CONVERT(value, type)

OR:

MySQL
1
CONVERT(value USING charset)
Parameter Description
value Required. The value to convert
type Required. The datatype to convert to. Can be one of the following:
Value Description
DATE Converts value to DATE. Format: "YYYY-MM-DD"
DATETIME Converts value to DATETIME. Format: "YYYY-MM-DD HH:MM:SS"
DECIMAL Converts value to DECIMAL. Use the optional M and D parameters to specify the maximum number of digits (M) and the number of digits following the decimal point (D).
TIME Converts value to TIME. Format: "HH:MM:SS"
CHAR Converts value to CHAR (a fixed length string)
NCHAR Converts value to NCHAR (like CHAR, but produces a string with the national character set)
SIGNED Converts value to SIGNED (a signed 64-bit integer)
UNSIGNED Converts value to UNSIGNED (an unsigned 64-bit integer)
BINARY Converts value to BINARY (a binary string)
charset Required. The character set to convert to

Example
Convert a value to a CHAR datatype:

MySQL
1
2
SELECT CONVERT(150, CHAR);
-- 150

Example
Convert a value to a TIME datatype:

MySQL
1
2
SELECT CONVERT("14:06:10", TIME);
-- '14:06:10'

Example
Convert a value to a SIGNED datatype:

MySQL
1
2
SELECT CONVERT(5-10, SIGNED);
'-5'

Example
Convert a value to LATIN1 character set:

MySQL
1
2
SELECT CONVERT("zacks.one" USING latin1);
-- 'zacks.one'

MySQL IF() Function

Definition and Usage
The IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.

Syntax

MySQL
1
IF(condition, value_if_true, value_if_false)

Parameter Values

Parameter Description
condition Required. The value to test
value_if_true Optional. The value to return if condition is TRUE
value_if_false Optional. The value to return if condition is FALSE

Examples

Example

Return “YES” if the condition is TRUE, or “NO” if the condition is FALSE:

MySQL
1
SELECT IF(500<1000, "YES", "NO");

Return 5 if the condition is TRUE, or 10 if the condition is FALSE:

MySQL
1
SELECT IF(500<1000, 5, 10);

Test whether two strings are the same and return “YES” if they are, or “NO” if not:

MySQL
1
SELECT IF(STRCMP("hello","bye") = 0, "YES", "NO");

Example

Return “MORE” if the condition is TRUE, or “LESS” if the condition is FALSE:

MySQL
1
2
SELECT OrderID, Quantity, IF(Quantity>10, "MORE", "LESS")
FROM OrderDetails;

MySQL Aggregate Functions

Aggregate Functions
Aggregate Function Descriptions
GROUP BY Modifiers
MySQL Handling of GROUP BY
Detection of Functional Dependence

Name Description
AVG() Return the average value of the argument
BIT_AND() Return bitwise AND
BIT_OR() Return bitwise OR
BIT_XOR() Return bitwise XOR
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
GROUP_CONCAT() Return a concatenated string
JSON_ARRAYAGG() Return result set as a single JSON array
JSON_OBJECTAGG() Return result set as a single JSON object
MAX() Return the maximum value
MIN() Return the minimum value
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
SUM() Return the sum
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance

MySQL Window Functions

Window Functions
Window Function Concepts and Syntax
Window Function Frame Specification
Named Windows
Window Function Restrictions

This section describes nonaggregate window functions that, for each row from a query, perform a calculation using rows related to that row. Most aggregate functions also can be used as window functions;

Name Description
CUME_DIST() Cumulative distribution value
DENSE_RANK() Rank of current row within its partition, without gaps
FIRST_VALUE() Value of argument from first row of window frame
LAG() Value of argument from row lagging current row within partition
LAST_VALUE() Value of argument from last row of window frame
LEAD() Value of argument from row leading current row within partition
NTH_VALUE() Value of argument from N-th row of window frame
NTILE() Bucket number of current row within its partition.
PERCENT_RANK() Percentage rank value
RANK() Rank of current row within its partition, with gaps
ROW_NUMBER() Number of current row within its partition

Window Function Concepts and Syntax

This section describes how to use window functions.

A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row:

  • The row for which function evaluation occurs is called the current row.
  • The query rows related to the current row over which function evaluation occurs comprise the window for the current row.

Example

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Create table If Not Exists Employee (employee_id int, team_id int);
Truncate table Employee;
insert into Employee (employee_id, team_id) values ('1', '8');
insert into Employee (employee_id, team_id) values ('2', '8');
insert into Employee (employee_id, team_id) values ('3', '8');
insert into Employee (employee_id, team_id) values ('4', '7');
insert into Employee (employee_id, team_id) values ('5', '9');
insert into Employee (employee_id, team_id) values ('6', '9');

-- Aggregate Function: Count the team_size.
SELECT
	team_id,
    COUNT(employee_id) AS team_size
FROM
	Employee
GROUP BY
	team_id;

-- Window Function: Count the team_size
SELECT
	employee_id,
    COUNT(*) OVER(PARTITION BY team_id) AS team_size,
    COUNT(*) OVER() AS total_employees
FROM
	Employee
ORDER BY
	employee_id;

MySQL | Ranking Functions

MySQL | Ranking Functions

The ranking functions in MySql are used to rank each row of a partition. The ranking functions are also part of MySQL windows functions list.

  • These functions are always used with OVER() clause.
  • The ranking functions always assign rank on basis of ORDER BY clause.
  • The rank is assigned to rows in a sequential manner.
  • The assignment of rank to rows always start with 1 for every new partition.

There are 3 types of ranking functions supported in MySQL

  • dense_rank():
    This function will assign rank to each row within a partition without gaps. Basically, the ranks are assigned in a consecutive manner i.e if there is a tie between values then they will be assigned the same rank, and next rank value will be one greater then the previous rank assigned.
  • rank():
    This function will assign rank to each row within a partition with gaps. Here, ranks are assigned in a non-consecutive manner i.e if there is a tie between values then they will be assigned same rank, and next rank value will be previous rank + no of peers(duplicates).
  • percent_rank():
    It returns the percentile rank of a row within a partition that ranges from 0 to 1. It tells the percentage of partition values less than the value in the current row, excluding the highest value.

Example

Consider we have different products with different price from mutiple suppliers.
We want to see the products price by suppliers.

MySQL
1
2
3
4
5
6
7
8
SELECT
	SupplierID,
	ProductID,
	ProductName,
    Price,
    RANK() OVER(PARTITION BY SupplierID ORDER BY Price ASC) AS PriceRankBySuppliers
FROM
	products;

MySQL | LEAD() and LAG() Function

MySQL | LEAD() and LAG() Function

The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition. These functions are termed as nonaggregate Window functions.

The Window functions are those functions which perform operations for each row of the partition or window. These functions produce the result for each query row unlikely to the aggregate functions that group them and results in a single row.

  • The row on which operation occur is termed as current row.
  • The set of rows which are realted to current row or using which function opaerates on current row is termed as Window.

The LAG() function is used to get value from row that precedes the current row.
The LEAD() function is used to get value from row that succedes the current row.

Syntax:
For LEAD() function

MySQL
1
2
LEAD(expr, N, default) 
          OVER (Window_specification | Window_name)

For LAG() function

MySQL
1
2
LAG(expr, N, default) 
          OVER (Window_specification | Window_name)

The N and default argument in the function is optional.

Parameters used:

  • expr: It can be a column or any bulit-in function.
  • N: It is a positive value which determine number of rows preceding/succeeding the current row. If it is omitted in query then its default value is 1.
  • default: It is the default value return by function in-case no row precedes/succeedes the current row by N rows. If it is missing then it is by default NULL.
  • OVER(): It defines how rows are partitioned into groups. If OVER() is empty then function compute result using all rows.
  • Window_specification: It consist of query partition clause which determines how the query rows are partitioned and ordered.
  • Window_name: If window is specified elsewhere in the query then it is referenced using this Window_name.

User Operations

Show current user

1
SELECT USER();

SQL VIEW

SQL Views
MySQL Views