Unit 2: Database Query Using SQL Syllabus: Single Row Functions

Unit 2:
Database Query using SQL

SYLLABUS
● Math functions: POWER (), ROUND (), MOD ().
● Text functions: UCASE ()/UPPER (), LCASE ()/LOWER (), MID ()/SUBSTRING ()/SUBSTR(),
LENGTH (), LEFT (), RIGHT (), INSTR (), LTRIM (), RTRIM (), TRIM ().
● Date Functions: NOW (), DATE (), MONTH (), MONTHNAME (), YEAR (), DAY (),
DAYNAME ().
● Aggregate Functions: MAX (), MIN (), AVG (), SUM (), COUNT (); using
COUNT (*).
● Querying and manipulating data using Group by, Having, Order by.
Single Row Functions

These are also known as Scalar functions. Single rowfunctions are applied on a single value
and return a single value.
Math functions: POWER (), ROUND (), MOD ()

Text functions:
Date Functions:
Aggregate functions – MIN,MAX,AVG,COUNT,SUM
An aggregate function performs a calculation on multiple values and returns a

single value.
1) COUNT2) SUM3) AVG4) MIN5) MAX
The following illustrates the syntax of an aggregate function:
function_name(DISTINCT | ALL expression)

mysql> SELECT * FROM empl;
| id | name | salary | city | dpt_id |

| 101 | abc | 15000 | chennai | 101 |
| 102 | def | 12000 | chennai | 101 |
| 103 | ghi | 13000 | vellore | 101 |
| 104 | jkl | 10000 | vellore | 103 |
| 105 | abi | 8000 | dindigal | 103 |
| 106 | abg | 9000 | dindigal | 103 |
COUNT Function
The COUNT function returns the total number of values in the specified field. It
works on both numeric and non-numeric data types. All aggregate functions by
default exclude nulls values before working on the data.
COUNT (*) is a special implementation of the COUNT function that returns the
count of all the rows in a specified table. COUNT (*) also considers Nulls and
duplicates.
mysql> select count(city) from empl;
| count(city) |
---------------
6
Using DISTINCT
Distinct includes only distinct values in the column
mysql> select count(distinct city) from empl;
| count(distinct city) |
--------------------------
3
------------------------
mysql> select count(*) from empl;
count(*)
--------------------------
6
2) SUM:-returns the sum of all the values in the specified column. SUM works on
numeric fields only. Null values are excluded from the result returned.
mysql> select sum(salary ) from empl;
sum(salary)
67000
3) AVG:-returns the average of the non NULL values in a specified
column.It works only on numeric data types.
mysql> select avg(salary) from empl;
avg(salary)
-----------------------
11166.6667
---------------
4) MIN:-Return the lowest value (minimum) in a set of non-NULL values.
mysql> select min(salary) from empl;
---------------
min(salary)
---------------
8000
5) MAX :Return the highest value (maximum) in a set of non-NULL values.

mysql> select max(salary) from empl;
---------------
max(salary)
---------------
15000
GROUP BY
The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT, MAX,
MIN, SUM, AVG) to group the result-set by one or more columns.
ORDER BY Clause
ORDER BY clause is used to display data in an ordered (arranged) form with respect to a
specified column. By default, ORDER BY displays records in ascending order of the specified
column’s values. To display the records in descending order, the DESC (means descending)
keyword needs to be written with that column.
SELECT *FROM empl WHERE salary BETWEEN 8000 and 12000 order by salary;
SELECT *FROM empl WHERE salary BETWEEN 8000 and 12000 order by salary ASC;
SELECT *FROM empl WHERE salary BETWEEN 8000 and 12000 order by salary DESC;
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
mysql> SELECT CITY,SUM(SALARY) FROM EMPL GROUP BY CITY

ORDER BY SALARY;
--------------------------
| CITY | SUM(SALARY) |
--------------------------
| dindigal | 17000 |
| vellore| 23000 |
| chennai | 27000 |
--------------------------
HAVING
The HAVING clause was added to SQL because the WHERE keyword could not
be used with aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
mysql> SELECT CITY,SUM(SALARY)
FROM EMPL
GROUP BY CITY
HAVING SUM(SALARY) > 20000
ORDER BY SALARY;
-------------------------
| CITY | SUM(SALARY) |
-------------------------
| vellore | 23000 |
| chennai | 27000 |
-------------------------

Unit 2: Database Query Using SQL Syllabus: Single Row Functions

Uploaded by

Copyright:

Available Formats

Unit 2: Database Query Using SQL Syllabus: Single Row Functions

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Unit 2: Database Query Using SQL Syllabus: Single Row Functions

Uploaded by

Copyright:

Available Formats

Unit 2:

Database Query using SQL

● Math functions: POWER (), ROUND (), MOD ().

Single Row Functions

Math functions: POWER (), ROUND (), MOD ()

An aggregate function performs a calculation on multiple values and returns a

1) COUNT2) SUM3) AVG4) MIN5) MAX

The following illustrates the syntax of an aggregate function:

function_name(DISTINCT | ALL expression)

| id | name | salary | city | dpt_id |

5) MAX :Return the highest value (maximum) in a set of non-NULL values.

mysql> SELECT CITY,SUM(SALARY) FROM EMPL GROUP BY CITY

You might also like