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.
Selects all products with a price between 10 and 20:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
The IN operator allows you to specify multiple values in a
WHERE
The IN operator is a shorthand for multiple
OR
Return all customers from ‘Germany’, ‘France’, or ‘UK’
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the
LIKE operator is used in a
WHERE
Return all customers that starts with the letter ‘a’:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
The LIKE operator is used in a
WHERE
There are two wildcards often used in conjunction with the
LIKE
% represents zero, one, or multiple characters_ represents one, single characterSelect all customers that starts with the letter “a”:
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
The AVG() function returns the average value of a numeric column.
Find the average price of all products:
SELECT AVG(Price)
FROM Products;
Note: NULL values are ignored.
The SUM() function returns the total sum of a numeric column.
Return the sum of all Quantity fields in the OrderDetails table:
SELECT SUM(Quantity)
FROM OrderDetails;
The COUNT() function returns the number of rows that matches a specified criterion.
Find the total number of rows in the Products table:
SELECT COUNT(*)
FROM Products;
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
Find the lowest price in the Price column:
SELECT MIN(Price)
FROM Products;
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 clause of the
BYSELECT 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 columnMAX() – returns the largest value within the selected columnCOUNT() – returns the number of rows in a setSUM() – returns the total sum of a numerical columnAVG() – returns the average value of a numerical columnThe 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.
Select only the first 3 records of the Customers table:
SELECT TOP 3 * FROM Customers;
Note: Not all database systems support the
SELECT TOPLIMIT clause to select a limited number of records, while Oracle uses
FETCH FIRST ROWS ONLY and ROWNUM.
Continue reading SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause