SQL

Some of The Most Important SQL Commands

 SELECT - extracts data from a database
 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 DROP TABLE - deletes a table
 CREATE INDEX - creates an index (search key)
 DROP INDEX - deletes an index

The SQL SELECT Statement

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

Example:
Return data from the Customers table:
SELECT CustomerName, City FROM Customers;

SELECT Syntax
SELECT column1, column2, ...
FROM table_name;

Select ALL Columns

To select ALL columns, without specifying every column name, use the SELECT * syntax:
Example
Select ALL columns from the "Customers" table:
SELECT * FROM Customers;
 SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (unique) values.

Example:
Select all the distinct (unique) countries from the "Customers" table:
SELECT DISTINCT Country FROM Customers;

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT Example Without DISTINCT

If you omit the DISTINCT keyword, the SQL statement returns the "Country" value from all the records
of the "Customers" table:
Example
SELECT Country FROM Customers;

Count Distinct Values

By using the COUNT() function with the DISTINCT keyword, we can count the number of unique
countries.
Example
SELECT COUNT(DISTINCT Country) FROM Customers;

Note: The COUNT(DISTINCT column_name) is not supported in Microsoft Access databases.

Here is a workaround for MS Access:
Example
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
 The SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specific condition.

Example
Here we select all customers from Mexico:
SELECT * FROM Customers
WHERE Country = 'Mexico';

WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE,
etc.

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM Customers
WHERE CustomerID = 1;

Operators in The WHERE Clause

You can use other operators than the = operator to filter the search.
Example
Select all customers with a CustomerID greater than 80:
SELECT * FROM Customers
WHERE CustomerID > 80;
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 ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the result-set in ascending order (ASC) by default.

Example
Sort the products from lowest to highest price:
SELECT * FROM Products
ORDER BY Price;

ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
ORDER BY DESC
To sort the records in descending order, use the DESC keyword.
Example
Sort the products from highest to lowest price:
SELECT * FROM Products
ORDER BY Price DESC;

Order Alphabetically
For string values, the ORDER BY keyword will sort the values in the column alphabetically:
Example
Sort the ProductName column in alphabetically order:
SELECT * FROM Products
ORDER BY ProductName;

Alphabetically DESC
To sort the text values in a column in a descending order, use the DESC keyword:
Example
Sort the ProductName column in descending alphabetically order:
SELECT * FROM Products
ORDER BY ProductName DESC;

ORDER BY Several Columns

The following SQL statement selects all customers from the "Customers" table - and sorts it by the
"Country" and the "CustomerName" column.
This means that it sorts it first by Country, and if some records have the same Country, it sorts them by
CustomerName:
Example
SELECT * FROM Customers
ORDER BY Country, CustomerName;
Combine ASC and DESC
The following SQL statement selects all customers from the "Customers" table, and sorts it ASCENDING
by the "Country" and DESCENDING by the "CustomerName" column:
Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

The SQL AND Operator

The WHERE clause can contain one or many AND operators.
The AND operator is used to filter records based on more than one condition.
Note: The AND operator displays a record if all the conditions are TRUE.
The following SQL selects all customers from Spain that starts with the letter 'G':

Example
Select all customers where Country is "Spain" AND CustomerName starts with the letter 'G':
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';

AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

All Conditions Must Be True

The following SQL selects all customers where Country is "Brazil" AND City is "Rio de Janeiro" AND
CustomerID is higher than 50:
Example
SELECT * FROM Customers
WHERE Country = 'Brazil'
AND City = 'Rio de Janeiro'
AND CustomerID > 50;

The SQL OR Operator

The WHERE clause can contain one or more OR operators.
The OR operator is used to filter records based on more than one condition.
Note: The OR operator displays a record if any of the conditions are TRUE.
The following SQL selects all customers from Germany OR Spain:

Example
Select all customers where Country is "Germany" OR "Spain":
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

At Least One Condition Must Be True

The following SQL selects all customers where City is "Berlin", OR CustomerName starts with the letter
"G", OR Country is "Norway":
Example
SELECT * FROM Customers
WHERE City = 'Berlin' OR CustomerName LIKE 'G%' OR Country = 'Norway';

AND vs. OR
The AND operator displays a record if all the conditions are TRUE.
The OR operator displays a record if any of the conditions are TRUE.
Combining AND and OR
You can also combine AND and OR operators.
The following SQL selects all customers from Spain that starts with a "G" or an "R" (make sure to use
parenthesis to get the correct result):
Example
Select all Spanish customers that starts with either "G" or "R":
SELECT * FROM Customers
WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
Without parenthesis, the SQL above will return all customers from Spain that starts with a "G", plus all
customers that starts with an "R", regardless of the country value:
Example
SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';

The NOT Operator

The NOT operator is used in the WHERE clause to return all records that DO NOT match the specified
criteria. It reverses the result of a condition from true to false and vice-versa.
The following SQL selects all customers that are NOT from Spain:

Example
Select only the customers that are NOT from Spain:
SELECT * FROM Customers
WHERE NOT Country = 'Spain';

In the example above, the NOT operator is used in combination with the = operator.
The NOT operator is also used in combination with other operators to exclude data, such as:
 NOT LIKE
 NOT BETWEEN
 NOT IN
 IS NOT NULL
 NOT EXISTS
NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

The NOT LIKE Operator

The NOT LIKE operator is used in the WHERE clause to exclude rows that match a specified character
pattern.
There are two wildcards often used in conjunction with the NOT LIKE operator:
 A percent sign % - represents zero, one, or multiple characters
 A underscore sign _ - represents a single character
The following SQL selects all customers that do NOT start with the letter "A":
Example
Select customers that does not start with the letter 'A':
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

The NOT BETWEEN Operator

The NOT BETWEEN operator is used in the WHERE clause to select rows where a value falls outside a
specified inclusive range.
The NOT BETWEEN operator can be used with numeric, text, or date values.
The following SQL selects all customers with a CustomerID NOT between 10 and 60:
Example
Select customers with a customerID not between 10 and 60:
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;
The NOT IN Operator
The NOT IN operator is used in the WHERE clause to exclude rows that match any value in a specified
list or a subquery result set.
The following SQL selects all customers with City NOT IN "Paris" or "London":
Example
Select customers that are not from Paris or London:
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');

NOT Greater Than

In SQL, the "NOT Greater Than" condition is most often expressed with the standard greater than or
equal to (>=) operator.
The following SQL selects all customers with a CustomerID not greater than 50:
Example
Select customers with a CustomerId not greater than 50:
SELECT * FROM Customers
WHERE NOT CustomerID > 50;

NOT Less Than

In SQL, the "NOT Less Than" condition is most often expressed with the standard less than or equal to
(<=) operator.
The following SQL selects all customers with a CustomerID not less than 50:
Example
Select customers with a CustomerID not less than 50:
SELECT * FROM Customers
WHERE NOT CustomerId < 50;
 The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
Syntax 1
Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Syntax 2
If you insert values for ALL the columns of the table, you can omit the column names.
However, the order of the values must be in the same order as the columns in the table:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Demo Database
Below is a selection from the Customers table used in the examples:

CustomerID CustomerName ContactName Address City PostalCode Country

89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA

90 Wilman Kala Matti Keskuskatu 45 Helsinki 21240 Finland

Karttunen

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

INSERT INTO Example

Here we insert values for ALL the columns of the table, so we omit the column names.
The following SQL inserts a new record in the "Customers" table:

Example
INSERT INTO Customers
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The last record in the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

Insert Data Only in Specific Columns
Here we insert values only in some specific columns of the table.
The following SQL inserts a new record - but only inserts data in the "CustomerName", "City", and
"Country" columns (CustomerID will be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
The last record in the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

92 Cardinal null null Stavanger null Norway

Insert Multiple Rows

To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:
The following SQL inserts three new records in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
Note: Make sure you separate each set of values with a comma ,.
The last three records in the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

93 Greasy Burger Per Olsen Gateveien 15 Sandnes 4306 Norway

94 Tasty Tee Finn Egan Streetroad 19B Liverpool L1 0AA UK

 What is a NULL Value?
If a field in a table is optional, it is possible to insert or update a record without adding any value to this
field. This way, the field will be saved with a NULL value.
A NULL value represents an unknown, missing, or inapplicable data in a database field. It is not a value
itself, but a placeholder to indicate the absence of data.
Note: A NULL value is different from zero (0) or an empty string (''). A field with a NULL value is one
that has been left blank upon 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
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.

The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

The SQL UPDATE Statement

The UPDATE statement is used to update or modify one or more records in a table.

UPDATE Syntax
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!

Demo Database
Below is a selection from the Customers table used in the examples:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico

Emparedados y Constitución 2222 D.F.
helados

3 Antonio Moreno Antonio Mataderos 2312 México 05023 Mexico

Taquería Moreno D.F.

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22 Sweden

Berglund
UPDATE Table
The following SQL updates the record with CustomerID = 1, with a new contact person AND a new city.
Example:
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Alfred Schmidt Obere Str. 57 Frankfurt 12209 Germany

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la México 05021 Mexico

helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023 Mexico

Moreno D.F.

UPDATE Multiple Records

The WHERE clause determines which records that will be updated.
The following SQL will update the ContactName to "Juan" for ALL records where country is "Mexico":
Example
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Alfred Obere Str. 57 Frankfurt 12209 Germany

Schmidt

2 Ana Trujillo Emparedados y Juan Avda. de la México 05021 Mexico

helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Juan Mataderos 2312 México 05023 Mexico

D.F.
Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!
The following SQL will update the ContactName to "Juan" for ALL records:
Example
UPDATE Customers
SET ContactName='Juan';
The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Juan Obere Str. 57 Frankfurt 12209 Germany

2 Ana Trujillo Juan Avda. de la México 05021 Mexico

Emparedados y helados Constitución 2222 D.F.

3 Antonio Moreno Juan Mataderos 2312 México 05023 Mexico

Taquería D.F.

The SQL DELETE Statement

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

DELETE Syntax
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!

Demo Database
Below is a selection from the Customers table used in the examples:
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados Ana Trujillo Avda. de la México 05021 Mexico

y helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023 Mexico

Moreno D.F.
SQL DELETE Example
The following SQL deletes the customer "Alfreds Futterkiste" from the "Customers" table:
Example
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

2 Ana Trujillo Emparedados Ana Trujillo Avda. de la México 05021 Mexico

y helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México 05023 Mexico
D.F.

Delete All Records

It is possible to delete all records in a table, without deleting the table. This means that the table
structure, attributes, and indexes will be intact.
Syntax
DELETE FROM table_name;
The following SQL deletes ALL records in the "Customers" table, without deleting the table:
Example
DELETE FROM Customers;

Delete a Table
To delete the table completely, use the DROP TABLE statement:
Syntax
DROP TABLE table_name;
The following SQL drops the entire "Customers" table:
Example
Delete entire "Customers" table:
DROP TABLE Customers;
 The SQL SELECT TOP Clause
The SELECT TOP clause is used to limit 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.
The following SQL selects only the first 3 records of the "Customers" table:

Example
Select only the first 3 records of the Customers table:
SELECT TOP 3 * FROM Customers;
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 FETCH FIRST n ROWS ONLY.
Syntax for SQL Server / MS Access
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
Syntax for MySQL
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Syntax for Oracle 12+
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

MySQL - The LIMIT Clause

The following SQL shows the equivalent example for MySQL:
Example
Select the first 3 records of the Customers table:
SELECT * FROM Customers
LIMIT 3;
Oracle - The FETCH FIRST Clause
The following SQL shows the equivalent example for Oracle:
Example
Select the first 3 records of the Customers table:
SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;

SQL TOP PERCENT Example

Here we will use the SELECT TOP clause with the percent syntax.
The following SQL selects the first 50% of the records from the "Customers" table (for SQL Server/MS
Access):
Example
SELECT TOP 50 PERCENT * FROM Customers;
The following SQL shows the equivalent example for Oracle:
Example
SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;

SELECT TOP with WHERE

The following SQL selects the first three records from the "Customers" table, where Country is
"Germany" (for SQL Server/MS Access):
Example
SELECT TOP 3 * FROM Customers
WHERE Country = 'Germany';
The following SQL shows the equivalent example for MySQL:
Example
SELECT * FROM Customers
WHERE Country = 'Germany'
LIMIT 3;
The following SQL shows the equivalent example for Oracle:
Example
SELECT * FROM Customers
WHERE Country = 'Germany'
FETCH FIRST 3 ROWS ONLY;

SELECT TOP and ORDER BY

Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted
result.
For SQL Server and MS Access:
Example
Sort the result reverse alphabetically by CustomerName, and return the first 3 records:
SELECT TOP 3 * FROM Customers
ORDER BY CustomerName DESC;
The following SQL shows the equivalent example for MySQL:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;
The following SQL shows the equivalent example for Oracle:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC
FETCH FIRST 3 ROWS ONLY;

SQL Aggregate Functions

An aggregate function is a function that performs a calculation on a set of values, and returns a single
value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP
BY clause splits the result-set into groups of values and the aggregate function can be used to return a
single value for each group.
The most commonly used SQL aggregate functions are:
 MIN() - returns the smallest value of a column
 MAX() - returns the largest value of a column
 COUNT() - returns the number of rows in a set
 SUM() - returns the sum of a numerical column
 AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT(*)).
Demo Database
Below is a selection from the Products table used in the examples:

ProductID ProductName SupplierID CategoryID Unit Price

1 Chais 1 1 10 boxes x 20 bags 18.00

2 Chang 1 1 24 - 12 oz bottles 19.00

3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00

4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22.00

5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35

Demo Database (SUM)

Below is a selection from the OrderDetails table used in the examples:

OrderDetailID OrderID ProductID Quantity

1 10248 11 12

2 10248 42 10

3 10248 72 5

4 10249 14 9

5 10249 51 40

The SQL MIN() Function

The MIN() function returns the smallest value of the selected column.
The MIN() function works with numeric, string, and date data types.
MIN() Example
Return the lowest price in the Price column, in the "Products" table:
SELECT MIN(Price)
FROM Products;

MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;

Set Column Name (Alias)

When using MIN(), the returned column will not have a name.
Use the AS keyword to give the column a descriptive name:
Example
SELECT MIN(Price) AS SmallestPrice
FROM Products;

Use MIN() with Date Column

The following SQL returns the earliest BirthDate in the BirthDate column, in the Employees table:
Example
SELECT MIN(BirthDate) AS EarliestBirthdate
FROM Employees;

Use MIN() with GROUP BY

Here we use the MIN() function and the GROUP BY clause, to return the smallest price for each
category in the Products table:
Example
SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;
 The SQL MAX() Function
The MAX() function returns the largest value of the selected column.
The MAX() function works with numeric, string, and date data types.

MAX Example
Return the highest price in the Price column, in the "Products" table:
SELECT MAX(Price)
FROM Products;

MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;

Set Column Name (Alias)

When you use MAX(), the returned column will not have a name.
Use the AS keyword, to give the column a descriptive name:
Example
SELECT MAX(Price) AS HighestPrice
FROM Products;

Use MAX() with Date Column

The following SQL returns the latest BirthDate in the BirthDate column, in the Employees table:
Example
SELECT MAX(BirthDate) AS LatestBirthdate
FROM Employees;

Use MAX() with GROUP BY

Here we use the MAX() function and the GROUP BY clause, to return the highest price for each
category in the Products table:
Example
SELECT MAX(Price) AS HighestPrice, CategoryID
FROM Products
GROUP BY CategoryID;
 The SQL COUNT() Function
The COUNT() function returns the number of rows that matches a specified criterion.
COUNT() Syntax
SELECT COUNT([DISTINCT] column_name | *)
FROM table_name
WHERE condition;

The behavior of COUNT() depends on the argument used within the parentheses:
 COUNT(*) - Counts the total number of rows in a table (including NULL values).
 COUNT(columnname) - Counts all non-null values in the column.
 COUNT(DISTINCT columnname) - Counts only the unique, non-null values in the column.

Using COUNT(*)
The following SQL uses COUNT(*), and counts the total number of rows in the "Products" table (will
include NULL values):
Example
SELECT COUNT(*)
FROM Products;

Using COUNT(column_name)
The COUNT(column_name) counts all non-null values in the specified column.
The following SQL counts all non-null values of the "ProductName" column:
Example
SELECT COUNT(ProductName)
FROM Products;

Using COUNT(DISTINCT column_name)

You can ignore duplicates by using the DISTINCT keyword.
The COUNT(DISTINCT column_name) counts only the unique, non-null values in the column.
If DISTINCT is specified, rows with the same value for the specified column will be counted as one.
The following SQL counts the unique, non-null values of the "Price" column:
Example
How many different prices are there in the "Products" table:
SELECT COUNT(DISTINCT Price)
FROM Products;

Add a WHERE Clause

You can add a WHERE clause to specify conditions:
Example
Count the number of products where Price is higher than 20:
SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;

Use an Alias
When using COUNT(), the returned column will not have a name. Use the AS keyword to give the
column a descriptive name.
Example
Name the "count" column "Number of records":
SELECT COUNT(*) AS [Number of records]
FROM Products;

Use COUNT() with GROUP BY

Here we use the COUNT() function and the GROUP BY clause, to return the number of records for
EACH category in the "Products" table:
Example
SELECT COUNT(*) AS [Number of records], CategoryID
FROM Products
GROUP BY CategoryID;
 The SQL SUM() Function
The SUM() function is used to calculate the total sum of values within a numeric column.
The SUM() function ignores NULL values in the column.
The following SQL returns the sum of the Quantity field in the "OrderDetails" table:

Example
SELECT SUM(Quantity)
FROM OrderDetails;

SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

Add a WHERE Clause

You can add a WHERE clause to specify conditions.
The following SQL returns the sum of the Quantity field for the product with ProductID = 11, in the
"OrderDetails" table:
Example
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;

Use an Alias
Give the summarized column a name by using the AS keyword.
Example
Name the column "total":
SELECT SUM(Quantity) AS total
FROM OrderDetails;
Use SUM() with GROUP BY
Here we use the SUM() function and the GROUP BY clause, to return the Quantity for EACH OrderID
in the "OrderDetails" table:
Example
SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;

SUM() With an Expression

The parameter inside the SUM() function can also be an expression.
If we assume that each product in the "OrderDetails" table costs 10 dollars, we can find the total
earnings in dollars by multiply each quantity with 10:
Example
Use an expression inside the SUM() function:
SELECT SUM(Quantity * 10)
FROM OrderDetails;
We can also join the "OrderDetails" table with the "Products" table to find the actual price, instead of
assuming it is 10 dollars:
Example
Join OrderDetails with Products, and use SUM() to find the total amount:
SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON [Link] = [Link];

The SQL AVG() Function

The AVG() function returns the average value of a numeric column.
The AVG() function ignores NULL values in the column.

Example
Find the average price of all products:
SELECT AVG(Price)
FROM Products;
Note: NULL values are ignored.
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

Add a WHERE Clause

You can add a WHERE clause to specify conditions:
Example
Return the average price of products in category 1:
SELECT AVG(Price)
FROM Products
WHERE CategoryID = 1;

Use an Alias
Give the AVG column a name by using the AS keyword.
Example
Name the column "average price":
SELECT AVG(Price) AS [average price]
FROM Products;

Higher Than Average

To list all records with a higher price than average, we can use the AVG() function in a sub query:
Example
Return all products with a higher price than the average price:
SELECT * FROM Products
WHERE price > (SELECT AVG(price) FROM Products);

Use AVG() with GROUP BY

Here we use the AVG() function and the GROUP BY clause, to return the average price for EACH
category in the "Products" table:
Example
SELECT AVG(Price) AS AveragePrice, CategoryID
FROM Products
GROUP BY CategoryID;

The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern within a column's text
data.
There are two wildcards often used in conjunction with the LIKE operator:
 A percent sign % - represents zero, one, or multiple characters
 A underscore sign _ - represents a single character
The following SQL selects all customers that starts with the letter "a":
Example
Select all customers that starts with the letter "a":
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Demo Database (LIKE, Wildcard, In, Alias, Self Join, Union, Union All, Group By)
Below is a selection from the Customers table used in the examples:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México D.F. 05021 Mexico

Emparedados y Constitución 2222
helados

3 Antonio Moreno Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Taquería
 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

The % Wildcard
The % wildcard represents any number of characters, even zero characters.
Example
Return all customers from a City that contains the character sequence 'on':
SELECT * FROM Customers
WHERE city LIKE '%on%';

The _ Wildcard
The _ wildcard represents one single character.
It can be any character or number, but each _ represents one, and only one, character.
Example
Return all customers from a City that starts with 'l' followed by one wildcard character, then 'nd' and
then two wildcard characters:
SELECT * FROM Customers
WHERE city LIKE 'l_nd__';

Starts With
To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase.
Example
Return all customers that starts with 'La':
SELECT * FROM Customers
WHERE CustomerName LIKE 'La%';
Tip: You can also combine any number of conditions using AND or OR operators.
Example
Return all customers that starts with 'a' or starts with 'b':
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b%';
Ends With
To return records that ends with a specific letter or phrase, add the % at the beginning of the letter or
phrase.
Example
Return all customers that ends with 'a':
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
Tip: You can also combine "starts with" and "ends with":
Example
Return all customers that starts with "b" and ends with "s":
SELECT * FROM Customers
WHERE CustomerName LIKE 'b%s';

Contains
To return records that contains a specific letter or phrase, add the % both before and after the letter or
phrase.
Example
Return all customers that contains the phrase 'or'
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

Combine Wildcards
Any wildcard, like % and _ , can be used in combination with other wildcards.
Example
Return all customers that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
Example
Return all customers that have "r" in the second position:
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
Without Wildcards
If no wildcard is specified, the phrase has to have an exact match to return a result.
Example
Return all customers from Spain:
SELECT * FROM Customers
WHERE Country LIKE 'Spain';

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to
search for a specified pattern in a column.
Example
Return all customers that starts with the letter 'a':
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

Wildcard Characters

Symbol Description

% Represents zero or more characters

_ Represents a single character

[] Represents any single character within the brackets *

^ Represents any character not in the brackets *

- Represents any single character within the specified range *

{} Represents any escaped character **

* Not supported in PostgreSQL and MySQL databases.

** Supported only in Oracle databases.
Using the % Wildcard
The % wildcard represents any number of characters, even zero characters.
Example
Return all customers that ends with the pattern 'es':
SELECT * FROM Customers
WHERE CustomerName LIKE '%es';
Example
Return all customers that contains the pattern 'mer':
SELECT * FROM Customers
WHERE CustomerName LIKE '%mer%';

Using the _ Wildcard

The _ wildcard represents a single character.
It can be any character or number, but each _ represents one, and only one, character.
Example
Return all customers with a City starting with any character, followed by "ondon":
SELECT * FROM Customers
WHERE City LIKE '_ondon';
Example
Return all customers with a City starting with "L", followed by any 3 characters, ending with "on":
SELECT * FROM Customers
WHERE City LIKE 'L___on';

Using the [] Wildcard

The [] wildcard returns a result if any of the characters inside gets a match.
Example
Return all customers starting with either "b", "s", or "p":
SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';

Using the - Wildcard

The - wildcard allows you to specify a range of characters inside the [] wildcard.
Example
Return all customers starting with "a", "b", "c", "d", "e" or "f":
SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';

Combine Wildcards
Any wildcard, like % and _ , can be used in combination with other wildcards.
Example
Return all customers that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
Example
Return all customers that have "r" in the second position:
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

Without Wildcard
If no wildcard is specified, the phrase has to have an exact match to return a result.
Example
Return all customers from Spain:
SELECT * FROM Customers
WHERE Country LIKE 'Spain';

Microsoft Access Wildcards

The Microsoft Access Database has some other wildcards:

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 any single character within the specified range 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
 The SQL IN Operator
The IN operator is used in the WHERE clause to check if a specified column's value matches any value
within a provided list.
The IN operator functions as a shorthand for multiple OR conditions, making queries shorter and more
readable.
The following SQL uses the IN operator to select all customers from Germany, France, or UK:

Example
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
The following SQL uses multiple OR conditions to select all customers from Germany, France, or UK
(same result, but longer code):
Example
SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'France' OR Country = 'UK';

Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

The NOT IN Operator

By using the NOT IN operator, you return all records that are NOT any of the values in the list.
Example
Return all customers that are NOT from 'Germany', 'France', or 'UK':
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

IN (SELECT)
You can also use IN with a subquery in the WHERE clause.
With a subquery you can return all records from the main query that are present in the result of the
subquery.
The following SQL returns all customers who also have an order in the "Orders" table:
Example
SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

NOT IN (SELECT)
The result in the example above returned 74 records, that means that there are 17 customers that haven't
placed any orders.
Let us check if that is correct, by using the NOT IN operator.
The following SQL returns all customers who do NOT have any orders in the "Orders" table:
Example
SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);

The SQL BETWEEN Operator

The BETWEEN operator is used in the WHERE clause to select values within a specified range.
The range is inclusive - the beginning and end values of the range are included in the results.
The values can be numbers, text, or dates.
Example
Select all products with a price between 10 and 20:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

NOT BETWEEN
The NOT BETWEEN operator is used in the WHERE clause to select values outside a specified range.
The following SQL returns all products with a price NOT between 10 and 20:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with IN
The following SQL returns all products with a price between 10 and 20. In addition, the CategoryID
must be either 1, 2 or 3:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID IN (1,2,3);

BETWEEN Text Values

The following SQL selects all products with a ProductName alphabetically between 'Geitost' and
'Louisiana Hot Spiced Okra':
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Geitost' AND 'Louisiana Hot Spiced Okra'
ORDER BY ProductName;

NOT BETWEEN Text Values

The following SQL selects all products with a ProductName NOT between 'Geitost' and 'Louisiana Hot
Spiced Okra':
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Geitost' AND 'Louisiana Hot Spiced Okra'
ORDER BY ProductName;

BETWEEN Dates
The BETWEEN operator is useful for filtering records within a specific date or time period. Ensure the
date format matches the database (e.g. 'YYYY-MM-DD').
The following SQL selects all orders placed in July, 1996:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

SQL Aliases
An alias is created with the AS keyword, and is often used to make a column name more readable.
An alias only exists for the duration of that query.

Alias for Columns

The following SQL creates two aliases, one for the CustomerID column and one for the CustomerName
column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

Syntax
Alias for column:
SELECT column_name AS alias_name
FROM table_name;
Alias for table:
SELECT column_name(s)
FROM table_name AS alias_name;

Aliases with Spaces

If you want your alias to contain one or more spaces, like "My Great Products", surround the aliasname
with square brackets or double quotes:
Example
Using [square brackets] for aliases with space characters:
SELECT ProductName AS [My Great Products]
FROM Products;
OR:
Example
Using "double quotes" for aliases with space characters:
SELECT ProductName AS "My Great Products"
FROM Products;
Note: Some database systems allows both [] and "", and some only allows one of them.

Concatenate Columns
The following SQL creates an alias named "Address" that combine four columns (Address, PostalCode,
City and Country):
Example
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 Example
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
Note: To get the SQL statement above to work in Oracle use the following:
Oracle Example
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
FROM Customers;

Alias for Tables

The same rules applies when you want to use an alias for a table.
Example
Refer to the Customers table as Persons instead:
SELECT * FROM Customers AS Persons;
It might seem useless to use aliases on tables, but when you are joining tables, it makes sense.
In the following example, c is the alias for customers and o is for orders, making the query shorter and
easier to read:
Example
SELECT [Link], [Link]
FROM customers AS c
JOIN orders AS o ON [Link] = [Link];

Aliases are useful when:

 There are more than one table involved in a query
 Functions are used in the query
 Column names are long or not very readable
 Two or more columns are combined together

The SQL JOIN Clause

The JOIN clause is used to combine rows from two or more tables, based on a related column between
them.
Here are the different types of JOINs in SQL:
 (INNER) JOIN: Returns only rows that have matching values in both tables
 LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the
right table
 RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from
the left table
 FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table
Look at an order in "Orders" table:

OrderID CustomerID OrderDate

10308 2 1996-09-18

Then, look at a customer in the "Customers" table:

CustomerID CustomerName ContactName Country

2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico

Here we see that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the
"Customers" table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records
that have matching values in both tables:
Example
SELECT [Link], [Link], [Link]
FROM Orders
INNER JOIN Customers ON [Link]=[Link];
and it will produce something like this:

OrderID CustomerName OrderDate

10308 Ana Trujillo Emparedados y helados 1996-09-18

SQL INNER JOIN

The INNER JOIN returns only rows that have matching values in both tables.
Tip: You can use just JOIN instead of INNER JOIN, as INNER is the default join type.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Note: The syntax combines two tables based on a related column, and the ON keyword is used to specify
the matching condition.

INNER JOIN Example

Look at a product in the Products table:

ProductID ProductName CategoryID Price

3 Aniseed Syrup 2 10.00

And look at a row in the Categories table:

CategoryID CategoryName Description

2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings

Here we see that the related column between the two tables above, is the "CategoryID" column.
Now we create an INNER JOIN on the "Products" table and the "Categories" table, via the CategoryID
field:
Example
Join "Products" and "Categories" with the INNER JOIN keyword:
SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON [Link] = [Link];
Note: INNER JOIN returns only rows with a match in both tables. This means that if there is a product
with no CategoryID, or with a CategoryID not present in the Categories table, that row will not be
returned in the result.

Naming the Columns

It is a good practice to also include the table name when specifying columns in SQL joins:
Example
Add table name in front of column names:
SELECT [Link], [Link], [Link]
FROM Products
INNER JOIN Categories ON [Link] = [Link];
The example above works without specifying table names, because none of the specified column names
are present in both tables. However, if you add the CategoryID column in the SELECT statement, an
error occurs, if you do not specify the table name. This is because the CategoryID column is present in
both tables.

JOIN or INNER JOIN

JOIN and INNER JOIN will return the same result.
INNER is the default join type for JOIN, so when you write JOIN the parser actually writes INNER
JOIN.
Example
JOIN is the same as INNER JOIN:
SELECT [Link], [Link], [Link]
FROM Products
JOIN Categories ON [Link] = [Link];

JOIN Multiple Tables

You can join more than two tables by adding multiple INNER JOIN clauses in your query.
The following SQL selects all orders with customer and shipper information:
Example
SELECT [Link], [Link], [Link]
FROM Orders
INNER JOIN Customers ON [Link] = [Link]
INNER JOIN Shippers ON [Link] = [Link];
Here is the Shippers table:

ShipperID ShipperName Phone

1 Speedy Express (503) 555-9831

2 United Package (503) 555-3199

3 Federal Shipping (503) 555-9931

SQL LEFT JOIN

The LEFT JOIN returns all rows from the left table (table1), and only the matched rows from the right
table (table2).
If there is no match in the right table, the result for the columns from the right table will be NULL.
The LEFT JOIN and LEFT OUTER JOIN keywords are equal - the OUTER keyword is optional.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Note: The syntax combines two tables based on a related column, and the ON keyword is used to specify
the matching condition.

Demo Database
Below is a selection from the "Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México D.F. 05021 Mexico

Emparedados y Constitución
helados 2222

3 Antonio Moreno Antonio Moreno Mataderos México D.F. 05023 Mexico

Taquería 2312

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

Here we see that the related column between the two tables above, is the "CustomerID" column.

SQL LEFT JOIN Examples

The following SQL returns all customers and their orders, including customers who have not placed any
orders:
Example
SELECT [Link], [Link]
FROM Customers
LEFT JOIN Orders ON [Link] = [Link]
ORDER BY [Link];
Tip: To find only the customers who have not placed any order, add a WHERE clause to filter for NULL
values on the right table:
Example
SELECT [Link], [Link]
FROM Customers
LEFT JOIN Orders
ON [Link] = [Link]
WHERE [Link] IS NULL;

SQL RIGHT JOIN

The RIGHT JOIN returns all rows from the right table (table2), and only the matched rows from the left
table (table1).
If there is no match in the left table, the result for the columns from the left table will be NULL.
The RIGHT JOIN and RIGHT OUTER JOIN keywords are equal - the OUTER keyword is optional.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Note: The syntax combines two tables based on a related column, and the ON keyword is used to specify
the matching condition.

Demo Database
Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

 10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo

1 Davolio Nancy 1968-12-08 [Link]

2 Fuller Andrew 1952-02-19 [Link]

3 Leverling Janet 1963-08-30 [Link]

Here we see that the related column between the two tables above, is the "EmployeeID" column.

SQL RIGHT JOIN Example

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

Example
SELECT [Link], [Link], [Link]
FROM Orders
RIGHT JOIN Employees ON [Link] = [Link]
ORDER BY [Link];
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 JOIN

The FULL JOIN returns all rows when there is a match in either the left or right table.
If a row in the left table has no match in the right table, the result set includes the left row's data and
NULL values for all columns of the right table.
If a row in the right table has no match in the left table, the result set includes the right row's data and
NULL values for all columns of the left table.
The FULL JOIN and FULL OUTER JOIN keywords are equal - the OUTER keyword is optional.
Note: FULL JOIN can potentially return very large result-sets!

FULL JOIN Syntax

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Demo Database
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México D.F. 05021 Mexico

Emparedados y Constitución
helados 2222

3 Antonio Moreno Antonio Mataderos México D.F. 05023 Mexico

Taquería Moreno 2312

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

SQL FULL JOIN Example

The following SQL statement selects all customers, and all orders:
Example
SELECT [Link], [Link]
FROM Customers
FULL JOIN Orders
ON [Link] = [Link];

A selection from the result-set may look like this:

CustomerName OrderID

Null 10309

Null 10310

Alfreds Futterkiste Null

Ana Trujillo Emparedados y helados 10308

Antonio Moreno Taquería Null

Note: FULL JOIN 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
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.

SQL Self Join Example

The following SQL statement matches customers that are from the same city:
Example
SELECT [Link] AS CustomerName1, [Link] AS CustomerName2, [Link]
FROM Customers A, Customers B
WHERE [Link] <> [Link]
AND [Link] = [Link]
ORDER BY [Link];
 The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
The UNION operator automatically removes duplicate rows from the result set.
Requirements for UNION:
 Every SELECT statement within UNION must have the same number of columns
 The columns must also have similar data types
 The columns in every SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1
UNION
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.

Demo Database
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico

Emparedados y Constitución D.F.
helados 2222

3 Antonio Moreno Antonio Mataderos México 05023 Mexico

Taquería Moreno 2312 D.F.

And a selection from the "Suppliers" table:

SupplierID SupplierName ContactName Address City PostalCode Country

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. London EC1 4SD UK

2 New Orleans Shelley Burke P.O. Box 78934 New Orleans 70117 USA
Cajun Delights

3 Grandma Kelly's Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA
Homestead
SQL UNION Example
The following SQL returns the unique (distinct) countries from both the "Customers" and the "Suppliers"
table:
Example
SELECT Country FROM Customers
UNION
SELECT Country FROM Suppliers
ORDER BY Country;
Note: If some customers or suppliers have the same country, each country will only be listed once,
because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL UNION With WHERE

Here we add a WHERE clause to only return the unique German cities from both the "Customers" and
the "Suppliers" table:
Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Another UNION Example

The following SQL lists all customers and suppliers:
Example
SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
Notice the "AS Type" above - it is an alias. Aliases are used to give a column a temporary name. So, here
we have created a temporary column named "Type", that list whether the contact person is a "Customer"
or a "Supplier".
 The SQL UNION ALL Operator
The UNION ALL operator is used to combine the result-set of two or more SELECT statements.
The UNION ALL operator includes all rows from each statement, including any duplicates.
Requirements for UNION ALL:
 Every SELECT statement within UNION ALL must have the same number of columns
 The columns must also have similar data types
 The columns in every SELECT statement must also be in the same order
UNION ALL Syntax
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.

SQL UNION ALL Example

The following SQL returns all the countries (also duplicate values) from both the "Customers" and the
"Suppliers" table:
Example
SELECT Country FROM Customers
UNION ALL
SELECT Country FROM Suppliers
ORDER BY Country;

SQL UNION ALL With WHERE

Here we add a WHERE clause to return all the German cities from both the "Customers" and the
"Suppliers" table:
Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
 The SQL GROUP BY Statement
The GROUP BY statement is used to group rows that have the same values into summary rows, like
"Find the number of customers in each country".
The GROUP BY statement is almost always used in conjunction with aggregate functions,
like COUNT(), MAX(), MIN(), SUM(), AVG(), to perform calculations on each group.
GROUP BY Syntax
SELECT column1, aggregate_function(column2), column3, ...
FROM table_name
WHERE condition
GROUP BY column1, column3
ORDER BY column_name;

SQL GROUP BY Examples

The following SQL returns the number of customers in each country:
Example
SELECT Country, COUNT(CustomerID) AS [Number of Customers]
FROM Customers
GROUP BY Country;
The following SQL returns the number of customers in each country, sorted from high to low:
Example
SELECT Country, COUNT(CustomerID) AS [Number of Customers]
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:

OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90 5 1996-07-04 3

10249 81 6 1996-07-05 1

10250 34 4 1996-07-08 2
And a selection from the "Shippers" table:

ShipperID ShipperName

1 Speedy Express

2 United Package

3 Federal Shipping

GROUP BY With JOIN Example

The following SQL returns the number of orders sent by each shipper:
Example
SELECT [Link], COUNT([Link]) AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers
ON [Link] = [Link]
GROUP BY ShipperName;

The SQL HAVING Clause

The HAVING clause is used to filter the results of a GROUP BY query based on aggregate functions.
Unlike the WHERE clause, which filters individual rows before grouping, the HAVING clause filters
groups after the aggregation has been performed.
HAVING Syntax
SELECT column1, aggregate_function(column2), column3, ...
FROM table_name
WHERE condition
GROUP BY column1, column3
HAVING condition -- The condition on grouped data
ORDER BY column_name;

SQL HAVING Examples

The following SQL returns the number of customers in each country - but only include countries with
more than 5 customers:
Example
SELECT Country, COUNT(CustomerID) AS [Number of Customers]
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

The following SQL returns the number of customers in each country, sorted from high to low (and only
include countries with more than 5 customers):
Example
SELECT Country, COUNT(CustomerID) AS [Number of Customers]
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:

OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90 5 1996-07-04 3

10249 81 6 1996-07-05 1

10250 34 4 1996-07-08 2

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo Notes

1 Davolio Nancy 1968-12-08 [Link] Education includes a BA....

2 Fuller Andrew 1952-02-19 [Link] Andrew received his BTS....

3 Leverling Janet 1963-08-30 [Link] Janet has a BS degree....

More HAVING Examples

The following SQL returns the employees that have registered more than 10 orders:
Example
SELECT [Link], COUNT([Link]) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON [Link] = [Link]
GROUP BY LastName
HAVING COUNT([Link]) > 10;
The following SQL returns if the employees "Davolio" or "Fuller" have registered more than 25 orders:
Example
SELECT [Link], COUNT([Link]) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON [Link] = [Link]
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT([Link]) > 25;

The SQL EXISTS Operator

The EXISTS operator is used in a WHERE clause to check whether a subquery returns any rows.
The EXISTS operator evaluates to TRUE if the subquery returns at least one row, and FALSE otherwise.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

Demo Database
Below is a selection from the "Products" table in the Northwind sample database:

ProductID ProductName SupplierID CategoryID Unit Price

1 Chais 1 1 10 boxes x 20 bags 18.00

2 Chang 1 1 24 - 12 oz bottles 19.00

3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00

4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22.00

5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35

And a selection from the "Suppliers" table:

SupplierID SupplierName ContactName Address City PostalCode Country

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. London EC1 4SD UK

2 New Orleans Shelley Burke P.O. Box 78934 New Orleans 70117 USA
Cajun Delights

3 Grandma Kelly's Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA
Homestead

4 Tokyo Traders Yoshi Nagase 9-8 Sekimai Tokyo 100 Japan

Musashino-shi

SQL EXISTS Examples

The following SQL checks if any suppliers has a product with a price lower than 10. If the subquery
evaluates to TRUE; list the suppliers with a product price less than 10:
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
SELECT ProductName
FROM Products
WHERE [Link] = [Link] AND Price < 10
);
The following SQL checks if any suppliers has a product with a price equal to 22. If the subquery
evaluates to TRUE; list the suppliers with a product price equal to 22:
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
SELECT ProductName
FROM Products
WHERE [Link] = [Link] AND Price = 22
);
 The SQL ANY Operator
The ANY operator is used to compare a value to every value returned by a subquery.
The ANY operator evaluates to TRUE if at least one value in the subquery result-set meet the condition.
ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (subquery);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Demo Database
Below is a selection from the "Products" table in the Northwind sample database:

ProductID ProductName Price

1 Chais 18.00

2 Chang 19.00

3 Aniseed Syrup 10.00

4 Chef Anton's Cajun Seasoning 22.00

And a selection from the "OrderDetails" table:

OrderDetailID ProductID Quantity

1 11 12

2 42 10

3 72 5

4 14 9

SQL ANY Examples

The following SQL returns the ProductName if it finds ANY records in the "OrderDetails" table that has
Quantity equal to 10 (this will return TRUE because the Quantity column has some values of 10):
Example
SELECT ProductName FROM Products
WHERE ProductID = ANY (
SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10
);
The following SQL returns the ProductName if it finds ANY records in the "OrderDetails" table that has
Quantity larger than 99 (this will return TRUE because the Quantity column has some values larger than
99):
Example
SELECT ProductName FROM Products
WHERE ProductID = ANY (
SELECT ProductID
FROM OrderDetails
WHERE Quantity > 99
);
The following SQL returns the ProductName if it finds ANY records in the "OrderDetails" table that has
Quantity larger than 1000 (this will return FALSE because the Quantity column has no values larger
than 1000):
Example
SELECT ProductName FROM Products
WHERE ProductID = ANY (
SELECT ProductID
FROM OrderDetails
WHERE Quantity > 1000
);

The SQL ALL Operator

The ALL operator is used to compare a value to every value returned by a subquery.
The ALL operator evaluates to TRUE if every value in the subquery result-set meet the condition.
The ALL operator is typically used with WHERE and HAVING statements.
ALL Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (subquery);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Demo Database
Below is a selection from the "Products" table in the Northwind sample database:

ProductID ProductName Price

1 Chais 18.00

2 Chang 19.00

3 Aniseed Syrup 10.00

4 Chef Anton's Cajun Seasoning 22.00

And a selection from the "OrderDetails" table:

OrderDetailID ProductID Quantity

1 11 12

2 42 10

3 72 5

4 14 9

SQL ALL Example

The following SQL returns the ProductName if ALL the records in the "OrderDetails" table has Quantity
equal to 10. This will of course return FALSE because the Quantity column has many different values
(not only the value of 10):

Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL (
SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10
);
 The SQL SELECT INTO Statement
The SELECT INTO statement is used to create a new table and fill it with data from an existing table.
The SELECT INTO statement is useful for creating backups or for creating a temporary table for
analysis.
Note: The new table will be created with the same column names and data types as defined in the source
table. However, primary keys, indexes, or NOT NULL constraints are not automatically transferred.
SELECT INTO Syntax
Copy entire table into a new table:
SELECT * INTO newtable [IN external_db]
FROM sourcetable
WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, ...
INTO newtable [IN external_db]
FROM sourcetable
WHERE condition;

SQL SELECT INTO Examples

The following SQL creates a backup copy of the "Customers" table:
SELECT * INTO CustomersBackup2026
FROM Customers;
The following SQL creates a backup copy of the "Customers" table in another database ('[Link]'):
SELECT * INTO CustomersBackup2026 IN '[Link]'
FROM Customers;
The following SQL copies only a few columns from the "Customers" table into a new table:
SELECT CustomerName, ContactName INTO Customers2
FROM Customers;
The following SQL copies only the customers from USA in the "Customers" table, into a new table:
SELECT * INTO US_Customers
FROM Customers
WHERE Country = 'USA';
The following SQL copies data from more than one table into a new table:
SELECT [Link], [Link] INTO CustomersOrder
FROM Customers
LEFT JOIN Orders ON [Link] = [Link];
Tip: SELECT INTO can also be used to create a new, empty table using the schema of another. Just add
a WHERE clause that causes the query to return no data:
SELECT * INTO newtable
FROM sourcetable
WHERE 1 = 0;

The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement is used to copy data from an existing table and insert it into
another existing table.
The INSERT INTO SELECT statement requires that the data types in source and target tables match.
Note: The existing records in the target table are unaffected.
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTO target_table
SELECT * FROM source_table
WHERE condition;
Note: If you omit the column names, the number and order of columns in the source and target tables
must be exactly the same!
Copy only some columns from one table to another table:
INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
Demo Database
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México D.F. 05021 Mexico

Emparedados y Constitución
helados 2222

3 Antonio Moreno Antonio Mataderos México D.F. 05023 Mexico

Taquería Moreno 2312

And a selection from the "Suppliers" table:

SupplierID SupplierName ContactName Address City Postal Code Country

1 Exotic Liquid Charlotte 49 Gilbert St. Londona EC1 4SD UK

Cooper

2 New Orleans Shelley Burke P.O. Box 78934 New 70117 USA
Cajun Delights Orleans

3 Grandma Kelly's Regina Murphy 707 Oxford Rd. Ann 48104 USA
Homestead Arbor

SQL INSERT INTO SELECT Examples

Example
Copy "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
Example
Copy "Suppliers" into "Customers" (copy all columns):
INSERT INTO Customers
SELECT * FROM Suppliers;
Example
Copy only the German suppliers into "Customers":
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
 The SQL CASE Expression
The CASE expression is used to define different results based on specified conditions in an SQL
statement.
The CASE expression goes through the conditions and stops at the first match (like an if-then-else
statement). So, once a condition is true, it will stop processing and return the result. If no conditions are
true, it returns the value in the ELSE clause. If there is no ELSE clause and no conditions are true, it
returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE default_result
END;

SQL CASE Example

Here we use the CASE expression to categorize data (Price) and we create a new column
(PriceCategory) that shows in which price category each product is:
Example
SELECT ProductName, Price,
CASE
WHEN Price < 20 THEN 'Low Cost'
WHEN Price BETWEEN 20 AND 50 THEN 'Medium Cost'
ELSE 'High Cost'
END AS PriceCategory
FROM Products;

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

Operations involving NULL values can sometimes lead to unexpected results.
SQL has some built-in functions to handle NULL values, and the most common functions are:
 COALESCE() - The preferred standard. (Works in MySQL, SQL Server and Oracle)
 IFNULL() - (MySQL)
 ISNULL() - (SQL Server)
 NVL() - (Oracle)
 IsNull() - (MS Access)
Note: A NULL value represents an unknown or missing data in a database field. It is not a value itself,
but a placeholder to indicate the absence of data.

Demo Database
Assume we have the following "Products" table:

PId ProductName Price InStock InOrder

1 Jarlsberg 10.45 16 15

2 Mascarpone 32.56 23 null

3 Gorgonzola 15.67 9 20

The "InOrder" column is optional, and may contain NULL values.

Now look at the following SQL statement:
SELECT ProductName, Price * (InStock + InOrder)
FROM Products;
Note: In the SQL above, if any of the "InOrder" values are NULL, the result will be NULL!

The COALESCE() Function

The COALESCE() function is the preferred standard for handling potential NULL values.
The COALESCE() function returns the first non-NULL value in a list of values.
The COALESCE() function works in MySQL, SQL Server, and Oracle (not in MS Access).

Syntax
COALESCE(val1, val2, ...., val_n)
Here we use the COALESCE() function to replace NULL values with 0:
SELECT ProductName, Price * (InStock + COALESCE(InOrder, 0))
FROM Products;

The IFNULL() Function (MySQL)

The MySQL IFNULL() function replaces NULL with a specified value.
Syntax
IFNULL(expr, alt)
Here we replace NULL values with 0:
SELECT ProductName, Price * (InStock + IFNULL(InOrder, 0))
FROM Products;

The ISNULL() Function (SQL Server)

The SQL Server ISNULL() function replaces NULL with a specified value.

Syntax
ISNULL(expr, alt)
Here we replace NULL values with 0:
SELECT ProductName, Price * (InStock + ISNULL(InOrder, 0))
FROM Products;

The NVL() Function (Oracle)

The Oracle NVL() function replaces NULL with a specified value.

Syntax
NVL(expr, alt)
Here we replace NULL values with 0:
SELECT ProductName, Price * (InStock + NVL(InOrder, 0))
FROM Products;

The IsNull() Function (MS Access)

The MS Access IsNull() function returns TRUE if the expression is NULL, otherwise FALSE.
Syntax
IsNull(expr)
The MS Access IIf() function returns one of two parts, depending on the evaluation of the expression.
Syntax
IIf(expr, truepart, falsepart)
 expr - Required. The expression to evaluate
 truepart - Value to return if expr is True
 falsepart - Value to return if expr is False
Here we replace NULL values with 0:
SELECT ProductName, Price * (InStock + IIf(IsNull(InOrder), 0, InOrder))
FROM Products;

What is a Stored Procedure?

A stored procedure is a precompiled SQL code that can be saved and reused.
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.
A stored procedure can also have parameters, so it can act based on the parameter value(s) that is passed.

Key Benefits of Stored Procedures

Stored procedures are widely used in database management, and have the following benefits:
 Code Reusability - The same procedure can be called from various applications
 Improved Performance - Stored procedures are precompiled and runs faster
 Database Security - You can set users permission to run a specific procedure (limits direct access to tables)
 Easy Maintenance - When updating a procedure, it automatically updates all its use

Stored Procedure Syntax (SQL Server)

To create a stored procedure, use the CREATE PROCEDURE statement:
CREATE PROCEDURE procedure_name
@param1 datatype,
@param2 datatype
AS
BEGIN
-- SQL_statements to be executed
SELECT column1, column2
FROM table_name
WHERE columnN = @paramN;
END;
Execute a Stored Procedure
To run a stored procedure, use the EXEC statement:
EXEC procedure_name @param1 = 'value1', @param2 = 'value2';

Drop a Stored Procedure

To delete a stored procedure, use the DROP PROCEDURE statement:
DROP PROCEDURE procedure_name;
Tip: To ensure that DROP PROCEDURE does not return an error, if the procedure is missing, add
the IF EXISTS clause:
DROP PROCEDURE IF EXISTS procedure_name;

Stored Procedure Example

The following SQL creates a stored procedure named "GetCustomersByCity" that can be used to select
Customers from a particular City in the "Customers" table:
Example
CREATE PROCEDURE GetCustomersByCity
@City nvarchar(50)
AS
BEGIN
SELECT * FROM Customers
WHERE City = @City;
END;
Here we execute the stored procedure by passing a city ('London') as a parameter, and the stored
procedure returns the relevant details from the "Customers" table:
Example
EXEC GetCustomersByCity @City = 'London';

Stored Procedure With Multiple Parameters

Adding multiple parameters is easy. Just list each parameter and the data type separated by a comma as
shown below.
The following SQL creates a stored procedure that selects Customers from a particular City with a
particular PostalCode from the "Customers" table:
Example
CREATE PROCEDURE GetCustomersByCity
@City nvarchar(50),
@PostalCode nvarchar(10)
AS
BEGIN
SELECT * FROM Customers
WHERE City = @City AND PostalCode = @PostalCode;
END;
Execute the stored procedure above as follows:
Example
EXEC GetCustomersByCity @City = 'London', @PostalCode = 'WA1 1DP';

SQL Comments
Comments are used to explain SQL code, or to temporarily prevent execution of SQL code (for
debugging).
Comments are ignored by the database engine.
SQL supports single-line comments --, and multi-line comments /* */.
Note: Comments are not supported in Microsoft Access databases.

SQL Single-line Comments

Single-line comments start with -- and continue to the end of line.
Any text after -- and to the end of the line will be ignored.
The following example uses a single-line comment as an explanation:
Example
-- Selects all German customers
SELECT * FROM Customers
WHERE Country = 'Germany';
The following example uses a single-line comment to comment away the end of a line:
Example
SELECT * FROM Customers -- WHERE City='Berlin';
The following example uses a single-line comment to temporarily prevent execution of an SQL statement:
Example
-- SELECT * FROM Customers;
SELECT * FROM Products;

SQL 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
/* Selects all German customers
from Berlin */
SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';
The following example uses a multi-line comment to ignore many SQL statements:
Example
/* SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories; */
SELECT * FROM Suppliers;
To ignore just a part of an SQL code, you can also use multi-line comment:
Example
SELECT CustomerName, /*City,*/ Country FROM Customers;
The following example uses a multi-line comment to ignore part of an SQL statement:
Example
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 Operators
SQL operators are keywords and symbols used to perform operations with data values.
SQL operators are used in SQL statements like SELECT, WHERE, LIKE, etc.
SQL operators is categorized into the following types:
 Arithmetic operators
 Comparison operators
 Compound operators
 Bitwise operators
 Logical operators

SQL Arithmetic Operators

Operator Description

+ Addition

- Subtraction

* Multiplication

/ Division

% Modulus

SQL Comparison Operators

Operator Description

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

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 Bitwise Operators

Operator Description

& Bitwise AND

| Bitwise OR

^ Bitwise exclusive OR

~ Bitwise NOT

SQL Logical Operators

Operator Description

ALL TRUE if all of the subquery values meet the condition

AND TRUE if all the conditions separated by AND is TRUE

ANY TRUE if any of the subquery values meet the condition

BETWEEN TRUE if the operand is within the range of comparisons

EXISTS TRUE if the subquery returns one or more records

IN TRUE if the operand is equal to one of a list of expressions

LIKE TRUE if the operand matches a pattern

NOT Displays a record if the condition(s) is NOT TRUE

OR TRUE if any of the conditions separated by OR is TRUE

SOME TRUE if any of the subquery values meet the condition