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;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
CustomerID CustomerNam CustomerName Address City PostalCod Country
e e
1 Alfreds Juan Obere Str. 57 Berlin 12209 Germany
Futterkiste
2 Ana Trujillo Juan Avda. de la México 05021 Mexico
Emparedados y Constitución D.F.
helados 2222
3 Antonio Moreno Juan Mataderos México 05023 Mexico
Taquería 2312 D.F.
4 Around the Juan 120 Hanover London WA1 1DP UK
Horn Sq.
5 Berglunds Juan Berguvsväge Luleå S-958 22 Sweden
snabbköp n8
SQL TOP, LIMIT and FETCH FIRST Examples
The following SQL statement selects the first three records from the "Customers" table (for SQL
Server/MS Access):
Example
SELECT TOP 3 * FROM Customers;
The following SQL statement shows the equivalent example for MySQL:
Example
SELECT * FROM Customers
LIMIT 3;
The following SQL statement shows the equivalent example for Oracle:
Example
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;
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';
The following SQL statement shows the equivalent example for MySQL:
Example
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;
The following SQL statement shows the equivalent example for Oracle:
Example
SELECT * FROM Customers
WHERE Country='Germany'
FETCH FIRST 3 ROWS ONLY;
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() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID Product Supplier CategoryI Unit Price
D
Name ID
1 Chais 1 1 18
10 boxes x 20 bags
2 Chang 1 1 19
24 - 12 oz bottles
3 1 2 10
Aniseed Syrup 12 - 550 ml bottles
4 2 2 22
Chef Anton's Cajun 48 - 6 oz jars
Seasoning
5 2 2 21.35
Chef Anton's Gumbo 36 boxes
Mix
MIN() Example
The following SQL statement finds the price of the cheapest product:
Example
SELECT MIN(Price) AS SmallestPrice
FROM Products;
MAX() Example
The following SQL statement finds the price of the most expensive product:
Example
SELECT MAX(Price) AS LargestPrice
FROM Products;
SQL COUNT(), AVG() and SUM() Functions
The SQL COUNT(), AVG() and SUM() Functions
The COUNT() function returns the number of rows that matches a specified criterion.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
The AVG() function returns the average value of a numeric column.
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
The SUM() function returns the total sum of a numeric column.
SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
Product Product Supplier Category Unit Price
ID
Name ID ID
1 Chais 1 1 18
10 boxes x 20 bags
2 Chang 1 1 19
24 - 12 oz bottles
3 1 2 10
Aniseed Syrup 12 - 550 ml bottles
4 2 2 22
Chef Anton's Cajun 48 - 6 oz jars
Seasoning
5 2 2 21.35
Chef Anton's 36 boxes
Gumbo Mix
COUNT() Example
The following SQL statement finds the number of products:
Example
SELECT COUNT(ProductID)
FROM Products;
Note: NULL values are not counted.
AVG() Example
The following SQL statement finds the average price of all products:
Example
SELECT AVG(Price)
FROM Products;
Note: NULL values are ignored.
Demo Database
Below is a selection from the "OrderDetails" table in the Northwind sample database:
OrderDetailID OrderID ProductID Quantity
1 10248 11 12
2 10248 42 10
3 10248 72 5
4 10249 14 9
5 10249 51 40
SUM() Example
The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:
Example
SELECT SUM(Quantity)
FROM OrderDetails;
Note: NULL values are ignored.
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
Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of
the underscore (_).
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Tip: You can also combine any number of conditions using AND or OR operators.
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second
position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least
2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least
3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with
"o"
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
Customer Customer Customer Address City Postal Country
ID Name Name Code
1 Alfreds Maria Obere Str. 57 Berlin 12209 Germany
Futterkiste Anders
2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico
Emparedados Constitución D.F.
y helados 2222
3 Antonio Antonio Mataderos México 05023 Mexico
Moreno Moreno 2312 D.F.
Taquería
4 Around the Thomas 120 Hanover London WA1 UK
Horn Hardy Sq. 1DP
5 Berglunds Christina Berguvsvägen Luleå S-958 Sweden
snabbköp Berglund 8 22
6 Blauer See Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
Delikatessen
7 Blondel père Frédérique 24, place Strasbourg 67000 France
et fils Citeaux Kléber
8 Bólido Martín C/ Araquil, Madrid 28023 Spain
Comidas Sommer 67
preparadas
9 Bon app' Laurence 12, rue des Marseille 13008 France
Lebihans Bouchers
SQL LIKE Examples
The following SQL statement selects all customers with a CustomerName starting with "a":
Example
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
The following SQL statement selects all customers with a CustomerName ending with "a":
Example
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
The following SQL statement selects all customers with a CustomerName that have "or" in any position:
Example
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
The following SQL statement selects all customers with a CustomerName that have "r" in the second
position:
Example
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
The following SQL statement selects all customers with a CustomerName that starts with "a" and are at
least 3 characters in length:
Example
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
The following SQL statement selects all customers with a ContactName that starts with "a" and ends with
"o":
Example
SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';
The following SQL statement selects all customers with a CustomerName that does NOT start with "a":
Example
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';
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.
Wildcard Characters in MS Access
Symbol Description Example
* Represents zero or more characters bl* finds bl, black, blue, and blob
? Represents a single character h?t finds hot, hat, and hit
[] Represents any single character within the h[oa]t finds hot and hat, but not hit
brackets
! Represents any character not in the brackets h[!oa]t finds hit, but not hot and hat
- Represents any single character within the c[a-b]t finds cat and cbt
specified range
# Represents any single numeric character 2#5 finds 205, 215, 225, 235, 245, 255,
265, 275, 285, and 295
Wildcard Characters in SQL Server
All the wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with '%' and '_' wildcards: