SQL
FUNCTIONS
SQL
has a lot of built-in functions for counting
and calculations.
Function
Syntax
The syntax for built-in SQL functions is:
|
SELECT
function(column) FROM table |
Original
Table (used in the examples)
|
Name
|
Age
|
|
Hansen,
Ola
|
34
|
|
Svendson,
Tove
|
45
|
|
Pettersen,
Kari
|
19
|
Function
AVG(column)
The
AVG function returns the average value of a
column in a selection. NULL values are not
included in the calculation.
Example
This
example returns the average age of the persons
in the "Persons" table:
|
SELECT
AVG(Age) FROM Persons |
Result
Example
This
example returns the average age for persons
that are older than 20 years:
|
SELECT
AVG(Age) FROM Persons where Age>20 |
Result
Function
MAX(column)
The
MAX function returns the highest value in a
column. NULL values are not included in the
calculation.
Example
|
SELECT
MAX(Age) FROM Persons |
Result:
Function
MIN(column)
The
MIN function returns the lowest value in a
column. NULL values are not included in
the calculation.
Example
|
SELECT
MIN(Age) FROM Persons |
Result:
Note:
The MIN and MAX functions can also be used on
text columns, to find the highest or lowest
value in alphabetical order.
Function
SUM(column)
The
SUM function returns the total sum of a column
in a given selection. NULL values are not
included in the calculation.
Example
This
example returns the sum of all ages in the
"person" table:
|
SELECT
SUM(Age) FROM Persons |
Result:
Example
This
example returns the sum of ages for persons
that are more than 20 years old:
|
SELECT
SUM(Age) FROM Persons where Age>20 |
Result:
The
GROUP BY Keywords
Aggregate
functions (like SUM) often need an added GROUP
BY functionality.
The GROUP BY
keywords have been added to SQL because
aggregate functions (like SUM) return the
aggregate of all column values every time they
are called.
Without the
GROUP BY functionality, finding the sum for
each individual group of column values was not
possible.
The syntax
for the GROUP BY function is:
SELECT column,SUM(column) FROM table GROUP BY column
|
GROUP
BY Example
This
"Sales" Table:
| Company |
Amount |
| Schools |
5500 |
| IBM |
4500 |
| Schools |
7100 |
And This SQL:
| SELECT
Company, SUM(Amount) FROM Sales |
Returns this
result:
| Company |
SUM(Amount) |
| Schools |
17100 |
| IBM |
17100 |
| Schools |
17100 |
The above
code is invalid because the column returned is
not part of an aggregate. A GROUP BY clause
will correct, as in this SQL:
SELECT
Company,SUM(Amount) FROM Sales
GROUP BY
Company |
Returns this
result:
| Company |
SUM(Amount) |
| Schools |
12600 |
| IBM |
4500 |
SQL
JOINS
Joins
and Keys
Sometimes we
have to select data from two tables to make our
result complete. We have to perform a join.
Tables in a
database can be related to each other with keys.
A primary key is a column with a unique value
for each row. The purpose is to bind data
together, across tables, without repeating all
of the data in every table.
In the
"Employees" table below, the
"ID" column is the primary key,
meaning that no two rows can have the same ID.
The ID distinguishes two persons even if they
have the same name.
When you look
on the example tables below, notice that:
- The
"ID" column is the primary key of
the "Employees" table
- The
"ID" column in the
"Orders" table is used to refer to
the persons in the "Employees"
table without using their names
Employees:
| ID |
Name |
| 01 |
Hansen,
Ola |
| 02 |
Svendson,
Tove |
| 03 |
Svendson,
Stephen |
| 04 |
Pettersen,
Kari |
Orders:
| ID |
Product |
| 01 |
Printer |
| 03 |
Table |
| 03 |
Chair |
Referring
to Two Tables
We can select
data from two tables by referring to two tables,
like this:
Example
Who has ordered
a product, and what did they order?
SELECT
Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.ID = Orders.ID |
Result
| Name |
Product |
| Hansen,
Ola |
Printer |
| Svendson,
Stephen |
Table |
| Svendson,
Stephen |
Chair |
Using
Joins
OR, we can
select data from two tables with the JOIN
keyword, like this (this is the prefered way to
do it):
Example
INNER JOIN
Syntax
SELECT
field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield =
second_table.foreign_keyfield |
Who has ordered
a product, and what did they order?
SELECT
Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.ID = Orders.ID |
The INNER JOIN
returns all rows from both tables where there is
a match. If there are rows in Employees that do
not have matches in Orders, those rows will not
be listed.
Result
| Name |
Product |
| Hansen,
Ola |
Printer |
| Svendson,
Stephen |
Table |
| Svendson,
Stephen |
Chair |
Example
LEFT JOIN
Syntax
SELECT
field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield =
second_table.foreign_keyfield |
List all
employees, and their orders - if any.
SELECT
Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.ID = Orders.ID |
The LEFT JOIN
returns all the rows from the first table
(Employees), even if there are no matches in the
second table (Orders). If there are rows in
Employees that do not have matches in Orders,
those rows also will be listed.
Result
| Name |
Product |
| Hansen,
Ola |
Printer |
| Svendson,
Tove |
|
| Svendson,
Stephen |
Table |
| Svendson,
Stephen |
Chair |
| Pettersen,
Kari |
|
Example
RIGHT JOIN
Syntax
SELECT
field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield =
second_table.foreign_keyfield |
List all
orders, and who has ordered - if any.
SELECT
Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.ID = Orders.ID |
The RIGHT JOIN
returns all the rows from the second table
(Orders), even if there are no matches in the
first table (Employee). If there had been any
rows in Orders that did not have matches in
Employees, those rows also would have been
listed.
Result
| Name |
Product |
| Hansen,
Ola |
Printer |
| Svendson,
Stephen |
Table |
| Svendson,
Stephen |
Chair |
|
|
