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;
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
The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;
Note: Be careful when deleting records in a table! Notice the
WHEREDELETE 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!