SQL BETWEEN Operator

Toggle navigation
TUTORIAL HOME
SQL BETWEEN Operator
❮ Previous Next ❯
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.

BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
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
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 1 2 48 – 6 oz jars 22
5 Chef Anton’s Gumbo Mix 1 2 36 boxes 21.35
BETWEEN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

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

Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

»
BETWEEN with IN Example
The following SQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

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

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

Example
SELECT * FROM Products
WHERE ProductName BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
ORDER BY ProductName;

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

Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
ORDER BY ProductName;

»
Sample Table
Below is a selection from the “Orders” table in the Northwind sample database:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2
BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate BETWEEN ’04-July-1996′ and ’09-July-1996′:

Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

❮ Previous Next ❯

SQL Wildcards

Toggle navigation
TUTORIAL HOME
SQL Wildcards
❮ Previous Next ❯
SQL Wildcard Characters
A wildcard character is used to substitute any other character(s) in a string.

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

There are two wildcards used in conjunction with the LIKE operator:

% – The percent sign represents zero, one, or multiple characters
_ – The underscore represents a single character
Note: MS Access uses a question mark (?) instead of the underscore (_).

In MS Access and SQL Server you can also use:

[charlist] – Defines sets and ranges of characters to match
[^charlist] or [!charlist] – Defines sets and ranges of characters NOT to match
The wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that starts with “a” and ends with “o”
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

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

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
Using the % Wildcard
The following SQL statement selects all customers with a City starting with “ber”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘ber%’;
»
The following SQL statement selects all customers with a City containing the pattern “es”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘%es%’;
»
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character, followed by “erlin”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘_erlin’;
»
The following SQL statement selects all customers with a City starting with “L”, followed by any character, followed by “n”, followed by any character, followed by “on”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘L_n_on’;
»
Using the [charlist] Wildcard
The following SQL statement selects all customers with a City starting with “b”, “s”, or “p”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘[bsp]%’;
»
The following SQL statement selects all customers with a City starting with “a”, “b”, or “c”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘[a-c]%’;
»
Using the [!charlist] Wildcard
The two following SQL statements selects all customers with a City NOT starting with “b”, “s”, or “p”:

Example
SELECT * FROM Customers
WHERE City LIKE ‘[!bsp]%’;
»
Or:

Example
SELECT * FROM Customers
WHERE City NOT LIKE ‘[bsp]%’;
»

❮ Previous Next ❯

SQL IN Operator

TUTORIAL HOME
SQL IN Operator
❮ Previous Next ❯
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.

IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

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
IN Operator Examples
The following SQL statement selects all customers that are located in “Germany”, “France” and “UK”:

Example
SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);
»
The following SQL statement selects all customers that are NOT located in “Germany”, “France” or “UK”:

Example
SELECT * FROM Customers
WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);
»
The following SQL statement selects all customers that are from the same countries as the suppliers:

Example
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
»

❮ Previous Next ❯

TUTORIAL HOME SQL LIKE Operator

TUTORIAL HOME
SQL LIKE Operator
❮ Previous Next ❯
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 used in conjunction with the LIKE operator:

% – The percent sign represents zero, one, or multiple characters
_ – The underscore represents a single character
Note: MS Access uses 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:

CustomerID CustomerName ContactName Address City PostalCode Country
1

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
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 CustomerName 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 NOT starts with “a”:

Example
SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘a%’;
»

❮ Previous Next ❯

SQL COUNT(), AVG() and SUM() Functions

Toggle navigation
TUTORIAL HOME
SQL COUNT(), AVG() and SUM() Functions
❮ Previous Next ❯
The SQL COUNT(), AVG() and SUM() Functions
The COUNT() function returns the number of rows that matches a specified criteria.

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

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

COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
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:

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 2 2 48 – 6 oz jars 21.35
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 25
COUNT() Example
The following SQL statement finds the number of products:

Example
SELECT COUNT(ProductID)
FROM Products;
»
AVG() Example
The following SQL statement finds the average price of all products:

Example
SELECT AVG(Price)
FROM Products;
»
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;
»

❮ Previous Next ❯

SQL MIN() and MAX() Functions

Toggle navigation
TUTORIAL HOME
SQL MIN() and MAX() Functions
❮ Previous Next ❯
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 ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 2 2 48 – 6 oz jars 21.35
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 25
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;
»

❮ Previous Next ❯

SQL DELETE Statement

Toggle navigation
TUTORIAL HOME
SQL DELETE Statement
❮ Previous Next ❯
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) that 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 in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

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
SQL DELETE Example
The following SQL statement deletes the customer “Alfreds Futterkiste” from the “Customers” table:

Example
DELETE FROM Customers
WHERE CustomerName=’Alfreds Futterkiste’;
»
The “Customers” table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
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;
or:

DELETE * FROM table_name;

❮ Previous Next ❯

SQL TOP, LIMIT or ROWNUM Clause

Toggle navigation
TUTORIAL HOME
SQL TOP, LIMIT or ROWNUM Clause
❮ Previous Next ❯
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 on performance.

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

SQL Server / MS Access Syntax:

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

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

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
SQL TOP, LIMIT and ROWNUM Examples
The following SQL statement selects the first three records from the “Customers” table:

Example
SELECT TOP 3 * FROM Customers;
»
The following SQL statement shows the equivalent example using the LIMIT clause:

Example
SELECT * FROM Customers
LIMIT 3;
»
The following SQL statement shows the equivalent example using ROWNUM:

Example
SELECT * FROM Customers
WHERE ROWNUM <= 3;
SQL TOP PERCENT Example
The following SQL statement selects the first 50% of the records from the “Customers” table:

Example
SELECT TOP 50 PERCENT * FROM Customers;
»
ADD a WHERE CLAUSE
The following SQL statement selects the first three records from the “Customers” table, where the country is “Germany”:

Example
SELECT TOP 3 * FROM Customers
WHERE Country=’Germany’;
»
The following SQL statement shows the equivalent example using the LIMIT clause:

Example
SELECT * FROM Customers
WHERE Country=’Germany’
LIMIT 3;
»
The following SQL statement shows the equivalent example using ROWNUM:

Example
SELECT * FROM Customers
WHERE Country=’Germany’ AND ROWNUM <= 3;

❮ Previous Next ❯

TUTORIAL HOME SQL UPDATE Statement

Toggle navigation
TUTORIAL HOME
SQL UPDATE Statement
❮ Previous Next ❯
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!

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

CustomerID CustomerName ContactName Address City PostalCode Country
1

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
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;
»
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 helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
UPDATE Multiple Records
It is the WHERE clause that determines how many records that 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’;
»
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 helados Juan Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Juan Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

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 Emparedados y helados Juan Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Juan Mataderos 2312 México D.F. 05023 Mexico
4

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

❮ Previous Next ❯

SQL INSERT INTO Statement

Toggle navigation
TUTORIAL HOME
SQL INSERT INTO Statement
❮ Previous Next ❯
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.

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

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, …);
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

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 Karttunen Keskuskatu 45 Helsinki 21240 Finland
91

Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
INSERT INTO Example
The following SQL statement inserts a new record in the “Customers” table:

Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);
»
The selection from the “Customers” table will now look like this:

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 Karttunen Keskuskatu 45 Helsinki 21240 Finland
91

Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway
Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.

Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the “CustomerName”, “City”, and “Country” columns (CustomerID will be updated automatically):

Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);
»
The selection from the “Customers” table will now look like this:

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 Karttunen Keskuskatu 45 Helsinki 21240 Finland
91

Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal null null Stavanger null Norway

❮ Previous Next ❯