Introduction to Database
UCT – Mogadishu, Somalia
Introduction to SQL
SQL Functions
Introduction to SQL
Aggregate Functions
Scalar Functions
Group By
Having
Introduction to SQL
Aggregate Functions
The AVG() function returns the average
value of a numeric column.
SELECT AVG(PPRICE) AS CELCELIS FROM PLOT;
Introduction to SQL
Aggregate Functions
The COUNT() function returns the number of rows that
matches a specified criteria.
SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values
(NULL values will not be counted) of the specified column
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table
Introduction to SQL
Aggregate Functions
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the
number of distinct values of the specified column:
Introduction to SQL
Aggregate Functions
SQL FIRST() Syntax
SELECT TOP 1 * FROM MPS;
SQL LAST() Syntax
SELECT TOP 1 * FROM MPS ORDER BY MPID DESC;
SQL Max() Syntax
SELECT Max(Pprice) FROM Plot;
SQL Min() Syntax
SELECT Min(Pprice) FROM Plot;
SQL Sum() Syntax
SELECT Min(Pprice) FROM Plot;
Introduction to SQL
Aggregate Functions
SQL Sum() Syntax
The SUM() function returns the total sum of
a numeric column.
Select Sum(Pprice) from Plot
Introduction to SQL
Scalar Functions
UPPER Function
The UPPER function converts the value of a
field to uppercase.
Select Upper(MpName) from Mps;
LOWER Function
The LOWER function converts the value of a
field to lowercase.
Select Lower(MpName) from Mps;
Introduction to SQL
Scalar Functions
SubString Function
he SubString function is used to extract
characters from a text field
SELECT SUBSTRING(column_name,start,length)
AS some_name FROM table_name;
SELECT SUBSTRING(CLANNAME,0,5) FROM CLAN
Introduction to SQL
Scalar Functions
LEN Function
The LEN() function returns the length of the
value in a text field
SELECT LEN(column_name) AS some_name FROM
table_name;
SELECT LEN(CLANNAME) FROM CLAN
Introduction to SQL
Scalar Functions
Round Function
The Round() function is used to round a
numeric field to the number of decimals
specified.
SELECT ROUND(column_name,decimals) FROM
table_name;
SELECT Round(Pprice,2) FROM PLOT;
Introduction to SQL
SQL GROUP BY Statement
Aggregate functions often need an added GROUP BY
statement.
The GROUP BY statement is used in conjunction with
the aggregate functions to group the result-set by one
or more columns.
SELECT COUNT(*), PLOCATION FROM PLOT GROUP BY PLOCATION;
Introduction to SQL
SQL HAVING BY Statement
The HAVING clause was added to SQL because the WHERE
keyword could not be used with aggregate functions..
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SELECT PLOCATION FROM PLOT GROUP BY PLOCATION HAVING PPRICE
> 30000;
