SBOA School Senior Secondary, Madurai – 19
Class : 12 Computer Science
MySQL Functions
Functions are commands written with required values as parameters
to fulfill various tasks related to calculations or logical comparison or
displaying statistics from the database. Consider the following
database table for all functions:
S
QL Table
Math Functions
Math functions allows to do mathematical computations in MySQL.
The functions given in your syllabus are as following:
1. Power: It is used to compute power of given value as
parameter.
Syntax: power(value or column)
Example:
1. Value: Select power(3,3), the output will be
2. Column: select power(rollno, 2) from students;
2. Round: It is used to display the number to nearest number
with rounding up if the next digit is more than 5.
Syntax: round(value or column, digits)
3. Mod: This function returns the remainder after division of the
number with divisor.
Syntax: select mod(value or column, divisor)
Example: select mod(53,3)m
More functions not given in your syllabus
4. SQRT: This function returns square root of given number.
Syntax: select sqrt(value or column)
5. ABS: This function returns the number into positive number as
an absolute value.
Syntax: select abs(value or column)
6. Truncate: This function returns a number after removing
specified digits as parameter.
Syntax: select truncate(value or column, digits)
7. Sign: It will return 1 if the number is positive and returns -1 if
the number is negative.
Syntax: select sign(value or number)
Text Functions
The text functions are used to manipulate the text based data used
in database and display the results.
1. UCASE/UPPER: This function is used to convert the text into
upper case i.e. into capital.
Syntax: select ucase(‘text’ or column) or select upper(‘text’ or
column)
2. Lcase/lower: This function is used to convert the enclosed
text into lower case i.e. small letters.
Syntax: select lcase(‘text’ or column) or lower(‘text’ or column)
3. Mid: This function returns the text starting from a specified
number of letters to a specified letter from the enclosed text or
column value.
Syntax: select mid(‘Text’ or column, start_pos, end_pos)
Substring and substr is also used for the same.
4. Length: This function returns the number of letters from the
text including white space.
Syntax: select substr(‘text’ or column)
5. Left: This function is used to return specified left side letters
from the enclosed text or column values.
Syntax: select left(‘text’ or column, no_of_characters)
6. Right: It is exactly reverse than left, display the result from
right side of selected text.
Syntax: right(‘text’ or column, no_of_characters)
7. Instr(): It will check the specified text from the enclosed text
and return a number from where the specified text is starting.
Syntax: select instr(‘text’ or column,’text_to_search’)
MySQL Date Functions – Database Query using SQL
1. now/sysdate
2. curdate/current_date
3. date
4. month
5. monthname
6. year
7. day
8. dayname
9. dayofmonth
10. dayofweek
11. dayofyear
now/sysdate
This function returns the current date and time in the output in
‘YYYY-MM-DD HH:MM:SS’ or ‘YYYYMMDDHHMMSS.uuuuuu’.
There is slight difference between these two functions that now()
returns a constant time after execution. Sysdate() returns the exact
time when the command is executed including seconds.
select now(), sleep(3), now();
Output:
2020-11-02 07:30:15 0 2020-11-02 07:30:15
select sysdate(), sleep(3), sysdate();
Output:
2020-11-02 07:30:15 0 2020-11-02 07:30:18
As you can observe the difference in the output of above
statements. The output of now() functions returns the similar date
and time after executing sleep where as sysdate() function changes
the output and returns 3 seconds more in the time.
curdate()/current_date()/current_date
This function is used to display date value in ‘YYYY-MM-DD’ or
‘YYYYMMDD’ format.
select curdate();
Output:
2020-11-02
select current_date();
Output:
2020-11-02
select current_date;
Output:
2020-11-02
You can use different operators with these functions to manipulate
data.
date()
It will display the date from the selected dates. The format will be
‘YYYY-MM-DD’ or ‘YYYY-MM-DD’.
select date(now());
Output:
2020-11-02
month()
It will display the month number from the specified date.
select month(now());
Output:
11
select month('2018-05-20')
Output:
5
monthname()
This function will return the name of month from the specified date.
select monthname(curdate());
Output:
November
select monthname('2018-05-20')
Output:
May
year()
This function returns year from the specified date.
select year(now());
Output:
2020
day()
This function will display day from the specified date. Supposed the
date is ‘2020-08-17’ then the output will be 17. Observe the
following command:
select day(curdate());
Output:
2
dayname()
This function returns the name of the day of the week of specified
date.
select dayname(now());
Output:
Monday
dayofmonth()
This function returns the day number from the specified date.
select dayofmonth(current_date());
Output:
2
dayofweek()
It returns the day number of the week from the specified date. It
starts with Sunday=1.
select dayofweek(now());
Output:
2
dayofyear()
It returns the day number from the year.
select dayofyear('2020-02-02');
Output:
33
Aggregate functions
Aggregate functions that return one value from the set of specified
rows.
There are two keywords used to group data into columns:
1. Distinct
2. All
Distinct keyword
It avoids the duplicates data. Observe the following table named
Departments:
Department HO no_of_emp
Accounts Ahmedabad 300
Sales Baroda 250
IT Ahmedabad 350
HRM Anand 200
departments
select distinct HO from departments;
Output:
Ahmedabad
Baroda
Anand
So in the above command Ahmedabad is present two time in the HO
column. When distinct keyword is used, it will consider the first
value and ignore rest all similar values. As in above example we
have Ahmedabad.
All Keyword
It will consider all the values including duplicates. It is by default
option for all the queries if not specified the distinct key word.
We will cover the following aggregate functions in Database Query
using SQL Class 12 as per your syllabus.
1. avg
2. count
3. max
4. min
5. sum
avg()
It compute average of given values as parameters.
select avg(no_of_emp) from departments;
Output
275.0
count()
This function count the total no. of values from the given set of rows.
select count(*) from departments;
Output will be - 4
select count(distinct HO) from departments;
Output will be - 3
In above example, two variations of count() is used. When it is used
with distinct keyword it eliminates the duplicate value in counting.
The count(*) will count all the values including null and where as
count(column_name) will ignore null values.
max()
This function is used to return maximum value from the given set of
values.
select max(no_of_emp) from departments;
Output will be - 350
min()
It will return the minimum value from given set of values. Just
consider above given example and replace max with min.
sum()
It will return the addition of specified values. It is also similar like
max and min. Write example yourself and see the results. This
function can work with column as well with specific where condition.
The order by clause – MySQL Queries IP Class12
As you know when we are using SQL queries, the results are not
available with proper order. It is displayed as it is inserted in the
table by default.
To get the results in proper order, order by clause is useful. The
order by clause will be the last part of the select query in MySQL
probably. Observe the following query:
select * from employees order by empname;
This command display the result in ascending order. You can use
order by clause with where condition as well.
select empname,salary from employees where salary>=2500 order
by salary;
You can use any of the column with order by clause. You can use
order by clause with multiple columns in following manner:
select empname, salary from employees where salary>=2500 order
by salary, order by ename ;
For accurate results, use only one order by clause in the query. As it
will sort the columns as specified order by clause in the query.
You can sort the records in descending order by using DESC key
word. Observe the following code:
select empname, salary from employees order by salary desc;
To sort the records in ascending order ASC keyword is used. By
default, the results are available in ascending order by itself.
You can also sort the results produced with expression in select
query. As it is written with select clause.
select empname, salary * 12 from employees order by salary*12
asc;
Group By, having and Order by MySQL Queries
1. Group By – This clause is used to group the query results. It
will display the results in a group of particular values from the
columns specified with group by clause in MySQL query.
2. Having – Having clause applies conditions on the group by
clause. The conditions can be written as we write where clause
in MySQL Commands.
3. Order By – The Order by clause is used to sort the query
results either in Ascending order or Descending order.
In the next section of MySQL Queries IP Class12 we will discuss the
group by clause.
The Group By Clause
The group by clause display the query results in specific group of
values on the specified fields.
For example, If a table has a field named city. Now if you want to
display the query results according to cities, you can you group by
clause. It divide the table in the particular group of values.
As it is used with column name, in addition to this the group by
clause can be also used with aggregate functions to display the
value for each group.
For example, if you want to count the frequencies of customers from
a particular city. You can use group by with count function.
Now observe this query:
select designation, count(*) from employees group by designation;
It shows the number of managers, engineers, and other designations
available in the employees table.
Now observer this query and understand the output:
select department, count(*), sum(salary) from employees group by
department;
Points to be remembered:
1. The group by clause always written after from clause.
2. The column which is to be grouped will be written in the select
clause and group by clause.
The having clause
The having clause is used to apply a condition in the group by query
results. It is working like where clause but there is one difference
between them i.e. the WHERE clause can’t include aggregate
functions whereas the HAVING clause can include them.
Now let’s we write the queries we discussed in the previous section
with having clause.
select designation, count(*) from employees group by designation
having designation in ('engineer','manager');
This query returns number of engineers and managers available in
the employees table.
The having clause can be used in this manner also:
select department, sum(salary) from employees group by
designation having sum(salary)>=5000;
The above query filters only those records which consist of the sum
of salary more than 5000 in the department column of the
employees table.
You can use all of the relational operators, membership operators,
conditional operators etc.
You can also display non-group expression with group by in the
following manner:
select empname, sum(salary) from employees group by
department;
But when you are going to use such queries, they didn’t show the
accurate the results.
