1. SQL Tutorial (4) LIMIT, Math functions, LIKE

LIMIT Clause

LIMIT: to specify the number of records to return

• useful on large tables w/ thousands of records
• returning a large number of records can impact performance

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Example:

SELECT * FROM Customers
LIMIT 3;

SELECT * FROM Customers
WHERE Country = 'Germany'
LIMIT 3;

---

MIN() & MAX() Functions

MIN(): returns the smallest value of the selected column

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX(): returns the largest value of the selected column

SELECT MAX(column_name)
FROM table_name
WHERE condition;

---

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

COUNT(): returns the number of rows that matches a specified criterion

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG(): returns the average value of a numeric column

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM(): returns the total sum of a numeric column

SELECT SUM(column_name)
FROM table_name
WHERE condition;

---

LIKE Operator

: used in a WHERE clause to search for a specified pattern in a column

• used in conjunction w/ the LIKE operator:
  • percent sign (%): represents zero, one, or multiple characters
  • underscore sign: represents one, single character

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern; 

Examples:

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

---

This post is based on W3School's articles about SQL.