.

SQL is a standard language for accessing and manipulating

databases.

What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL became a standard of the American National
Standards Institute (ANSI) in 1986, and of the International
Organization for Standardization (ISO) in 1987

What Can SQL do?

 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI/ISO standard, there are different
versions of the SQL language.
However, to be compliant with the ANSI standard, they all
support at least the major commands (such
as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar
manner.

SQL Statements
Most of the actions you need to perform on a database are
done with SQL statements.
SQL statements consist of keywords that are easy to
understand.
The following SQL statement returns all records from a table
named "Customers":
Example
Select all records from the Customers table:
SELECT * FROM Customers;

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

SQL SELECT Statement

The SQL SELECT Statement

The SELECT statement is used to select data from a
database.
Return data from the Customers table:
SELECT CustomerName, City FROM Customers;

Syntax
SELECT column1, column2, ...
FROM table_name;
Here, column1, column2, ... are the field names of the
table you want to select data from.
The table_name represents the name of the table you
want to select data from.

SQL SELECT DISTINCT Statement

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only
distinct (different) values.
Example
Select all the different countries from the "Customers"
table:
SELECT DISTINCT Country FROM Customers;
Inside a table, a column often contains many duplicate
values; and sometimes you only want to list the different
(distinct) values.

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
By using the DISTINCT keyword in a function called COUNT,
we can return the number of different 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);

SQL WHERE Clause

The SQL WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a
specified condition.
Example
Select all customers from Mexico:
SELECT * FROM Customers
WHERE Country='Mexico';

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 SQL ORDER BY

The ORDER BY keyword is used to sort the result-set in
ascending or descending order.
Example
Sort the products by price:
SELECT * FROM Products
ORDER BY Price;

Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

DESC
The ORDER BY keyword sorts the records in ascending
order by default. 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 order
alphabetically:
Example
Sort the products alphabetically by ProductName:
SELECT * FROM Products
ORDER BY ProductName;

Alphabetically DESC
To sort the table reverse alphabetically, use
the DESC keyword:
Example
Sort the products by ProductName in reverse order:
SELECT * FROM Products
ORDER BY ProductName DESC;

ORDER BY Several Columns

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
SELECT * FROM Customers
ORDER BY Country, CustomerName;

Using Both ASC and DESC

The following SQL statement selects all customers from the
"Customers" table, sorted ascending by the "Country" and
descending by the "CustomerName" column:
Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

SQL AND Operator

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, like if you want to return all customers
from Spain that starts with the letter 'G':
Example
Select all customers from Spain that starts with the letter
'G':
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';

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

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.

All Conditions Must Be True

The following SQL statement selects all fields
from Customers where Country is "Germany" AND City is
"Berlin" AND PostalCode is higher than 12000:
Example
SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin'
AND PostalCode > 12000;

Combining AND and OR

You can combine the AND and OR operators.
The following SQL statement selects all customers from
Spain that starts with a "G" or an "R".
Make sure you 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 select statement 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 all customers that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G
%' OR CustomerName LIKE 'R%';
SQL OR Operator

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, like if you want to return all customers
from Germany but also those from Spain:
Example
Select all customers from Germany or Spain:
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

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

OR vs AND
The OR operator displays a record if any of the conditions
are TRUE.
The AND operator displays a record if all the conditions are
TRUE.
At Least One Condition Must Be True
The following SQL statement selects all fields from
Customers where either City is
"Berlin", CustomerName starts with the letter "G"
or Country is "Norway":
Example
SELECT * FROM Customers
WHERE City = 'Berlin' OR CustomerName LIKE 'G
%' OR Country = 'Norway';

Combining AND and OR

You can combine the AND and OR operators.
The following SQL statement selects all customers from
Spain that starts with a "G" or an "R".
Make sure you 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 select statement 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 all customers that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G
%' OR CustomerName LIKE 'R%';

SQL NOT Operator

The NOT Operator

The NOT operator is used in combination with other
operators to give the opposite result, also called the
negative result.
In the select statement below we want to return all
customers that are NOT from Spain:

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, but it can be used in
combination with other comparison and/or logical
operators. See examples below.

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

NOT LIKE
Example
Select customers that does not start with the letter 'A':
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

NOT BETWEEN
Example
Select customers with a customerID not between 10 and
60:
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;

NOT IN
Example
Select customers that are not from Paris or London:
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');

NOT Greater Than

Example
Select customers with a CustomerId not greater than 50:
SELECT * FROM Customers
WHERE NOT CustomerID > 50;
Note: There is a not-greater-than operator: !> that would
give you the same result.

NOT Less Than

Example
Select customers with a CustomerID not less than 50:
SELECT * FROM Customers
WHERE NOT CustomerId < 50;
Note: There is a not-less-than operator: !< that would give
you the same result.

SQL INSERT INTO Statement

The 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:
1. Specify both the column names and the values to be
inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO Example

The following SQL statement inserts a new record in the
"Customers" table:

INSERT INTO Customers (CustomerName, ContactName,

Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway');

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
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;

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;

SQL UPDATE Statement

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing
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!

UPDATE Table
The following SQL statement updates the first customer
(CustomerID = 1) with a new contact person and a new
city.
Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

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":
Example
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

Update Warning!
Be careful when updating records. If you omit
the WHERE clause, ALL records will be updated!
Example
UPDATE Customers
SET ContactName='Juan';

SQL DELETE Statement

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!

Delete All Records

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

Delete a Table
To delete the table completely, use the DROP
TABLE statement:
Example
Remove the Customers table:
DROP TABLE Customers;

SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

The SQL SELECT TOP 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.
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 and ROWNUM.
SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Oracle 12 Syntax:
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
Older Oracle Syntax:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Older Oracle Syntax (with ORDER BY):

SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER
BY column_name(s))
WHERE ROWNUM <= number;

LIMIT
The following SQL statement shows the equivalent
example for MySQL:
Example
Select the first 3 records of the Customers table:
SELECT * FROM Customers
LIMIT 3;
Try it Yourself »

FETCH FIRST
The following SQL statement 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

The following SQL statement selects the first 50% of the
records from the "Customers" table (for SQL Server/MS
Access):
Example
SELECT TOP 50 PERCENT * FROM Customers;
Try it Yourself »
The following SQL statement shows the equivalent
example for Oracle:
Example
SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;

ADD a WHERE CLAUSE

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

ADD the ORDER BY Keyword

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 statement shows the equivalent

example for MySQL:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;
The following SQL statement shows the equivalent
example for Oracle:
Example
SELECT * FROM Customers
ORDER BY CustomerName DESC
FETCH FIRST 3 ROWS ONLY;

SQL Aggregate Functions

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 within the selected
column
 MAX() - returns the largest value within the selected
column
 COUNT() - returns the number of rows in a set
 SUM() - returns the total sum of a numerical column
 AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except
for COUNT()).
We will go through the aggregate functions above in the
next chapters.

SQL MIN() and MAX() Functions

The SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the
selected column.
The MAX() function returns the largest value of the
selected column.
MIN Example
Find the lowest price in the Price column:
SELECT MIN(Price)
FROM Products;

MAX Example
Find the highest price in the Price column:
SELECT MAX(Price)
FROM Products;

Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Set Column Name (Alias)

When you use MIN() or MAX(), the returned column will
not have a descriptive name. To give the column a
descriptive name, use the AS keyword:
Example
SELECT MIN(Price) AS SmallestPrice
FROM Products;

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;

SQL COUNT() Function

The SQL COUNT() Function

The COUNT() function returns the number of rows that
matches a specified criterion.
Example
Find the total number of rows in the Products table:
SELECT COUNT(*)
FROM Products;

Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Specify Column
You can specify a column name instead of the asterix
symbol (*).
If you specify a column name instead of (*), NULL values
will not be counted.
Example
Find the number of products where the ProductName is
not null:
SELECT COUNT(ProductName)
FROM Products;

Add a WHERE Clause

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

Ignore Duplicates
You can ignore duplicates by using the DISTINCT keyword
in the COUNT() function.
If DISTINCT is specified, rows with the same value for the
specified column will be counted as one.
Example
How many different prices are there in the Products table:
SELECT COUNT(DISTINCT Price)
FROM Products;

Use an Alias
Give the counted column a name by using the AS keyword.
Example
Name the 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 returns the total sum of a numeric
column.
Example
Return the sum of all Quantity fields in
the OrderDetails table:
SELECT SUM(Quantity)
FROM OrderDetails;

Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

Add a WHERE Clause

You can add a WHERE clause to specify conditions:
Example
Return the sum of the Quantity field for the product
with ProductID 11:
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;
You will learn more about the GROUP BY clause later in this
tutorial.

SUM() With an Expression

The parameter inside the SUM() function can also be an
expression.
If we assume that each product in the OrderDetails column
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 to
the Products table to find the actual amount, 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 OrderDetails.ProductID =
Products.ProductID;

Add a WHERE Clause

You can add a WHERE clause to specify conditions:
Example
Return the sum of the Quantity field for the product
with ProductID 11:
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 column
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 to

the Products table to find the actual amount, 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 OrderDetails.ProductID =
Products.ProductID;

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;

SQL LIKE Operator

The 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 sign _ represents one, single character
You will learn more about .
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;

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 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__';

The % Wildcard
The % wildcard represents any number of characters, even
zero characters.
Example
Return all customers from a city that contains the letter 'L':
SELECT * FROM Customers
WHERE city LIKE '%L%';
Try it Yourself »

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%';
Try it Yourself »
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%';
Try it Yourself »

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';
Try it Yourself »
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 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';

SQL Wildcards

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%';
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';

SQL IN Operator

The 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.
Example
Return all customers from 'Germany', 'France', or 'UK'
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

NOT IN
By using the NOT keyword in front of the 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');

SQL BETWEEN Operator

The 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.
Example
Selects 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
To display the products outside the range of the previous
example, use NOT BETWEEN:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Try it Yourself »

BETWEEN with IN
The following SQL statement selects 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);
Try it Yourself »

BETWEEN Text Values

The following SQL statement selects all products with a
ProductName alphabetically between Carnarvon Tigers
and Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon
Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
Try it Yourself »
The following SQL statement selects all products with a
ProductName between Carnarvon Tigers and Chef Anton's
Cajun Seasoning:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon
Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
Try it Yourself »
NOT BETWEEN Text Values
The following SQL statement selects all products with a
ProductName not between Carnarvon Tigers and
Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon
Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

BETWEEN Dates
The following SQL statement selects all orders with an
OrderDate between '01-July-1996' and '31-July-1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1
996#;
OR:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-
31';
NOT BETWEEN
To display the products outside the range of the previous
example, use NOT BETWEEN:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Try it Yourself »

BETWEEN with IN
The following SQL statement selects 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);
Try it Yourself »

BETWEEN Text Values

The following SQL statement selects all products with a
ProductName alphabetically between Carnarvon Tigers
and Mozzarella di Giovanni:
Example
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
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon
Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

NOT BETWEEN Text Values

The following SQL statement selects all products with a
ProductName not between Carnarvon Tigers and
Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon
Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
BETWEEN Dates
The following SQL statement selects all orders with an
OrderDate between '01-July-1996' and '31-July-1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1
996#;
OR:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-
31';

Alias for Columns

The following SQL statement creates two aliases, one for
the CustomerID column and one for the CustomerName
column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
Try it Yourself »
Using Aliases With a Space Character
If you want your alias to contain one or more spaces, like
"My Great Products", surround your alias with square
brackets or double quotes.
Example
Using [square brackets] for aliases with space characters:
SELECT ProductName AS [My Great Products]
FROM Products;
Try it Yourself »
Example
Using "double quotes" for aliases with space characters:
SELECT ProductName AS "My Great Products"
FROM Products;
Try it Yourself »
Note: Some database systems allows both [] and "", and
some only allows one of them.

Concatenate Columns
The following SQL statement 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;
Try it Yourself »
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;
Try it Yourself »
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 using more than one table in your queries, it can
make the SQL statements shorter.
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
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:
Example
SELECT Orders.OrderID, Orders.OrderDate,
Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the
Horn' AND Customers.CustomerID=Orders.CustomerID;
Aliases can be useful when:
 There are more than one table involved in a query
 Functions are used in the query
 Column names are big or not very readable
 Two or more columns are combined together

SQL Joins

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

SELECT Orders.OrderID, Customers.CustomerName,

Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.
CustomerID;
SQL INNER JOIN

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

SELECT ProductID, ProductName, CategoryName

FROM Products
INNER JOIN Categories ON Products.CategoryID =
Categories.CategoryID;

Note: The INNER JOIN keyword returns only rows with a

match in both tables. Which means that if you have a
product with no CategoryID, or with a CategoryID that is
not present in the Categories table, that record would not
be returned in the result.

Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

ADVERTISEMENT

Naming the Columns

It is a good practice to include the table name when
specifying columns in the SQL statement.
Example
Specify the table names:
SELECT Products.ProductID, Products.ProductName,
Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID =
Categories.CategoryID;
Try it Yourself »
The example above works without specifying table names,
because none of the specified column names are present
in both tables. If you try to include CategoryID in
the SELECT statement, you will get an error if you do not
specify the table name (because CategoryID 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 Products.ProductID, Products.ProductName,
Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID =
Categories.CategoryID;

SELECT Orders.OrderID, Customers.CustomerName,

Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID =
Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID =
Shippers.ShipperID);
SQL LEFT JOIN Keyword

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left
table (table1), and the matching records from the right
table (table2). The result is 0 records from the right side, if
there is no match.
LEFT JOIN Syntax
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.

SQL LEFT JOIN Example

The following SQL statement will select all customers, and
any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the
left table (Customers), even if there are no matches in the
right table (Orders).

SQL RIGHT JOIN Keyword

❮ PreviousNext ❯

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right
table (table2), and the matching records from the left table
(table1). The result is 0 records from the left side, if there
is no match.
RIGHT JOIN Syntax
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.

SQL RIGHT JOIN Example

The following SQL statement will return all employees, and
any orders they might have placed:
Example
SELECT Orders.OrderID, Employees.LastName,
Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID =
Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the
right table (Employees), even if there are no matches in
the left table (Orders).
SQL FULL OUTER JOIN Keyword

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.
Tip: FULL OUTER JOIN and FULL JOIN are the same.
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

SQL FULL OUTER JOIN Example

The following SQL statement selects all customers, and all
orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orde
rs.CustomerID
ORDER BY Customers.CustomerName;

SQL Self Join

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 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 SQL UNION Operator

The UNION operator is used to combine the result-set of
two or more SELECT statements.
 Every SELECT statement within UNION must have the same
number of columns
 The columns must also have similar data types
 The columns in every SELECT statement must also be in the
same order
UNION Syntax
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:
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 Example

The following SQL statement returns the cities (only
distinct values) from both the "Customers" and the
"Suppliers" table:
ExampleGet your own SQL Server
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
Note: If some customers or suppliers have the same city,
each city will only be listed once, because UNION selects
only distinct values. Use UNION ALL to also select duplicate
values!

SQL UNION ALL Example

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

SQL UNION With WHERE

The following SQL statement returns the German cities
(only distinct values) from both the "Customers" and the
"Suppliers" table:
Example
SELECT City, Country FROM Customers
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:
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
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 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
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SQL GROUP BY Examples

The following SQL statement lists the number of customers
in each country:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
The following SQL statement lists the number of customers
in each country, sorted high to low:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

GROUP BY With JOIN Example

The following SQL statement lists the number of orders
sent by each shipper:
Example
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) A
S NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID =
Shippers.ShipperID
GROUP BY ShipperName;

SQL HAVING Clause

The SQL HAVING Clause

The HAVING clause was added to SQL because
the WHERE keyword cannot be used with aggregate
functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

SQL HAVING Examples

The following SQL statement lists the number of customers
in each country. Only include countries with more than 5
customers:
Example
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
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
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;

The following SQL statement lists if the employees

"Davolio" or "Fuller" have registered more than 25 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS
NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID =
Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

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
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE conditi
on);

SQL EXISTS Examples

The following SQL statement returns TRUE and lists the
suppliers with a product price less than 20:
Example
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products W
HERE Products.SupplierID = Suppliers.supplierID AND Price
< 20);

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products W
HERE Products.SupplierID = Suppliers.supplierID AND Price
= 22);

SQL ANY and ALL Operators

The SQL ANY and ALL Operators

The ANY and ALL operators allow you to perform a
comparison between a single column value and a range of
other values.

The SQL ANY Operator

The ANY operator:
 returns a boolean value as a result
 returns TRUE if ANY of the subquery values meet the
condition
ANY means that the condition will be true if the operation
is true for any of the values in the range.
ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
Note: The operator must be a standard comparison
operator (=, <>, !=, >, >=, <, or <=).

The SQL ALL Operator

The ALL operator:
 returns a boolean value as a result
 returns TRUE if ALL of the subquery values meet the
condition
 is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the
operation is true for all values in the range.
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING
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 <=).
SQL ANY Examples
The following SQL statement lists the ProductName if it
finds ANY records in the OrderDetails table has Quantity
equal to 10 (this will return TRUE because the Quantity
column has some values of 10):
Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
The following SQL statement lists the ProductName if it
finds ANY records in the OrderDetails table 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 statement lists the ProductName if it

finds ANY records in the OrderDetails table 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);
SQL ALL Examples
The following SQL statement lists ALL the product names:
Example
SELECT ALL ProductName
FROM Products
WHERE TRUE;

The following SQL statement lists the ProductName if ALL

the records in the OrderDetails table has Quantity equal to
10. This will of course return FALSE because the Quantity
column has many different values (not only the value of
10):
Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);

SQL CASE Expression

The SQL CASE Expression

The CASE expression goes through conditions and returns
a value when the first condition is met (like an if-then-else
statement). So, once a condition is true, it will stop reading
and return the result. If no conditions are true, it returns
the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it
returns NULL.
CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

SQL CASE Examples

The following SQL goes through conditions and returns a
value when the first condition is met:
Example
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
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);

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
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
Execute a Stored Procedure
EXEC procedure_name;

SQL Views

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of
an SQL statement.
A view contains rows and columns, just like a real table.
The fields in a view are fields from one or more real tables
in the database.
You can add SQL statements and functions to a view and
present the data as if the data were coming from one
single table.
A view is created with the CREATE VIEW statement.
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: A view always shows up-to-date data! The database
engine recreates the view, every time a user queries it.

SQL CREATE VIEW Examples

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