DAV International School, Amritsar

Subject: Informatics Practices (065)

Resource Material- Querying and SQL Functions
Functions in SQL

We know that function is used to do specific task and returns some value. Functions are very important
part of SQL statements. In SQL functions can be applied on single or multiple records of a table. SQL
functions can be categorised as Single row functions and Aggregate functions.
Single Row Functions
Single Row Functions are also known as Scalar functions. These are applied on a single value and return
a single value. There are three types of Single Row Functions:
1. Math or Numeric Functions
2. String Functions
3. Date Functions

Math Functions
 POW( ) or POWER( )
POWER(A, B) or POW(A, B) returns the number A raised to the power of another number B. Here the
number A is the base and the number B is the exponent. Needs two numbers as parameters.
SYNTAX:
SELECT POW(A, B);

Examples:
1) mysql> select power(2,3);
power(2,3)
8
1 row in set (0.05 sec)
2) mysql>select pow(2,3);
pow(2,3)
8
1 row in set (0.00 sec)
3) mysql>select pow(2.0 , 3.0);
pow(2.0,3.0)
8
1 row in set (0.00 sec)

 ROUND( )
This function is used to round the number to the specified number of decimal places. Parameters required:
the number to be rounded and the number of decimal places required. If the number of decimal places
required is not mentioned, then the result will not have decimal places
SYNTAX:
SELECT ROUND(NUMBER, NUMBER OF DECIMAL PLACES);
Examples :
1) mysql>select round(2.25);
round(2.25)
2
1 row in set (0.01 sec)
2) mysql>select round(2.25, 1);
round(2.25, 1)
2.3
1 row in set (0.00 sec)
3) mysql>select round(2.25, 2);
round(2.25, 2)
2.25
1 row in set (0.00 sec)
4) mysql>select round (2.26, 0);
round(2.26, 0)
2
1 row in set (0.00 sec)
5) mysql>select round(135.43, 0);
round(135.43, 0)
135
1 row in set (0.00 sec)
6) mysql>select round(135.53, 0);
round(135.53, 0)
136
1 row in set (0.00 sec)
7) mysql>select round(135.55, 1);
round(135.55, 1)
135.6
1 row in set (0.00 sec)
8) mysql>select round(135.55, -1);
round(135.55, -1)
140
1 row in set (0.00 sec)
9) mysql>select round(134.45, -1);
round(134.45, -1)
130
1 row in set (0.00 sec)
10) mysql>select round(134.45, -2);
round(134.45, -2)
100
1 row in set (0.00 sec)
11) mysql>select round(154.45, -2);
round(154.45, -2)
200
1 row in set (0.00 sec)
 12) mysql>select round(1454.45, -2);
round(1454.45, -2)
1500
1 row in set (0.00 sec)
13) mysql>select round(1444.45, -2);
round(1444.45, -2)
1400
1 row in set (0.00 sec)
14) mysql>select round(1444.45, -3);
round(1444.45, -3)
1000
1 row in set (0.00 sec)
15) mysql>select round(1544.45, -3);
round(1544.45, -3)
2000
1 row in set (0.00 sec)

 MOD( )
This function can be used to find modulus (remainder) when one number is divided by another.
SYNTAX:
SELECT MOD(A, B);
Examples:
1) mysql>select mod(5,3)
mod(5,3)
2
1 row in set (0.00 sec)
2) mysql> select mod(5,4);
mod(5,4)
1
1 row in set (0.00 sec)
3) mysql>select mod(4,2);
mod(4,2)
0
1 row in set (0.00 sec)

Text/String/Character Functions:
 UCASE( ) / UPPER( )
Used to convert a character or text to uppercase. Examples:
1) mysql>SELECT UCASE('hello');
UCASE('hello')
HELLO
1 row in set (0.00 sec)
2) mysql>SELECT Upper('hello');
Upper('hello')
HELLO
1 row in set (0.00 sec)
 LCASE( ) / LOWER( ) :
Used to convert a character or text to lowercase. Examples:
1) mysql>select lcase('HELLO');
lcase('HELLO')
hello
1 row in set (0.00 sec)
2) mysql>select LOWER('HELLO');
LOWER('HELLO')
Hello
1 row in set (0.00 sec)

 MID( ) :
To extract a specified number of characters from the string. First parameter is the text/string. Second
parameter is the starting index and the third parameter is the number of characters required. (Note: index
starts with 1 and not 0.)
Examples:
1) mysql>SELECT MID('ABCDEFGHIJKLMNOP', 1,4);
MID('ABCDEFGHIJKLMNOP', 1,4)
ABCD
1 row in set (0.00 sec)
2) mysql>SELECT MID('ABCDEFGHIJKLMNOP', 1);
MID('ABCDEFGHIJKLMNOP', 1)
ABCDEFGHIJKLMNOP
1 row in set (0.00 sec)
3) mysql> SELECT MID('ABCDEFGHIJKLMNOP', -2,-1);
MID('ABCDEFGHIJKLMNOP', -2,-1)

1 row in set (0.00 sec)

4) mysql>SELECT MID('ABCDEFGHIJKLMNOP', 0,4);
MID('ABCDEFGHIJKLMNOP', 0,4)

1 row in set (0.00 sec)

(Please note the output of example 3, 4 )
5) mysql>SELECT MID('ABCDEFGHIJKLMNOP', 3,4);
MID('ABCDEFGHIJKLMNOP', 3,4)
CDEF
1 row in set (0.00 sec)
6) mysql>SELECT MID('ABCDEFGHIJKLMNOP', -4,2);
MID('ABCDEFGHIJKLMNOP', -4,2)
MN
1 row in set (0.00 sec)

 SUBSTRING( ) :
Same as MID( ) function To extract a specified number of characters from the string.
Examples:
1) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP', 3,4);
SUBSTRING('ABCDEFGHIJKLMNOP', 3,4)
CDEF
 1 row in set (0.00 sec)
2) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP', 0,4);
SUBSTRING('ABCDEFGHIJKLMNOP', 0,4)

1 row in set (0.00 sec)

3) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP', 1,4);
SUBSTRING('ABCDEFGHIJKLMNOP', 1,4)
ABCD
1 row in set (0.00 sec)
4) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP', 4,2);
SUBSTRING('ABCDEFGHIJKLMNOP', 4,2)
DE
1 row in set (0.00 sec)
5) mysql>SELECT SUBSTRING('ABCDEFGHIJKLMNOP', -4,2);
SUBSTRING('ABCDEFGHIJKLMNOP', -4,2)
MN
1 row in set (0.00 sec)

 SUBSTR( ) :
Same as that of MID( ) and SUBSTRING( )
Examples:
1) mysql>SELECT SUBSTR('ABCDEFGHIJKLMNOP', -4,3);
SUBSTR('ABCDEFGHIJKLMNOP', -4,3)
MNO
1 row in set (0.00 sec)
2) mysql>SELECT SUBSTR('ABCDEFGHIJKLMNOP', 1,3);
SUBSTR('ABCDEFGHIJKLMNOP', 1,3)
ABC
1 row in set (0.00 sec)
3) mysql>SELECT SUBSTR('ABCDEFGHIJKLMNOP', 4,3);
SUBSTR('ABCDEFGHIJKLMNOP', 4,3)
DEF
1 row in set (0.00 sec)
 LENGTH( ) :
This function returns the number of characters in the given text.
Examples:
1) mysql> SELECT LENGTH('HELLO WORLD');
LENGTH('HELLO WORLD')
11
1 row in set (0.00 sec)
2) mysql> SELECT LENGTH(' ');
LENGTH(' ')
3
1 row in set (0.00 sec)
3) mysql> SELECT LENGTH(' ');
LENGTH(' ')
1
  LEFT( ): Returns the specified number of characters including space starting from the left
most characters. Parameters required : text, number of characters to be extracted.
Examples:
1) mysql>SELECT LEFT('ABCDEFGHIJKLMNOP',1);
LEFT('ABCDEFGHIJKLMNOP',1)
A
1 row in set (0.00 sec)
2) mysql> SELECT LEFT('ABCDEFGHIJKLMNOP',2);
LEFT('ABCDEFGHIJKLMNOP',2)
AB
1 row in set (0.00 sec)
3) mysql> SELECT LEFT('ABCDEFGHIJKLMNOP',3);
LEFT('ABCDEFGHIJKLMNOP',3)
ABC
1 row in set (0.00 sec)
4) mysql> SELECT LEFT('ABCDEFGHIJKLMNOP',-1);
LEFT('ABCDEFGHIJKLMNOP',-1)

1 row in set (0.00 sec)

(Note : In the above example , the number of characters to be selected is -1 and hence
characters are not extracted)
 RIGHT( ): Returns the specified number of characters including space starting from the right of the
text. Parameters required : text, number of characters to be extracted.
Examples:
1) mysql> SELECT RIGHT('ABCDEFGHIJKLMNOP',1);
RIGHT('ABCDEFGHIJKLMNOP',1)
P
1 row in set (0.00 sec)
2) mysql> SELECT RIGHT('ABCDEFGHIJKLMNOP',2);
RIGHT('ABCDEFGHIJKLMNOP',2)
OP
1 row in set (0.00 sec)
3) mysql> SELECT RIGHT('ABCDEFGHIJKLMNOP',3);
RIGHT('ABCDEFGHIJKLMNOP',3)
NOP
1 row in set (0.00 sec)
4) mysql> SELECT RIGHT('ABCDEFGHIJKLMNOP',4);
RIGHT('ABCDEFGHIJKLMNOP',4)
MNOP
1 row in set (0.00 sec)
5) mysql> SELECT RIGHT('ABCDEFGHIJKLMNOP',-1);
RIGHT('ABCDEFGHIJKLMNOP',-1)

1 row in set (0.00 sec)

 INSTR( ) : Checks whether the second string/text is present in the first string. If present it returns the
starting index.Otherwise returns 0.
Examples:
1) mysql> SELECT INSTR('ABCDEFGHIJKLMNOP','ABC');
INSTR('ABCDEFGHIJKLMNOP','ABC')
1
1 row in set (0.00 sec)
2) mysql> SELECT INSTR('ABCDEFGHIJKLMNOP','BC');
INSTR('ABCDEFGHIJKLMNOP','BC')
2
1 row in set (0.00 sec)
3) mysql> SELECT INSTR('ABCDEFGHIJKLMNOP','EFG');
INSTR('ABCDEFGHIJKLMNOP','EFG')
5
1 row in set (0.00 sec)
4) mysql> SELECT INSTR('ABCDEFGHIJKLMNOP','QRST');
INSTR('ABCDEFGHIJKLMNOP','QRST')
0
1 row in set (0.00 sec)

 LTRIM( ) :To trim the spaces, if any, from the beginning of the text.
Examples:
1) mysql> SELECT LTRIM(' HELLO');
LTRIM(' HELLO')
HELLO

 RTRIM( ) : To trim the spaces, if any, from the end of the text.
Examples:
1) mysql> SELECT RTRIM('HELLO ');
RTRIM('HELLO ')
HELLO
1 row in set (0.00 sec)
3) mysql> SELECT CONCAT(RTRIM('HELLO'), 'WORLD');
CONCAT(RTRIM('HELLO '), 'WORLD')
HELLOWORLD
1 row in set (0.00 sec)
 TRIM( ): To trim the spaces, if any, from the beginning and end of the text.
Examples:
1) mysql> SELECT CONCAT(TRIM('HELLO '), 'WORLD');
CONCAT(TRIM('HELLO '), 'WORLD')
HELLOWORLD
1 row in set (0.00 sec)
Note: CONCAT( ) combines two strings/texts
2) mysql> SELECT TRIM(' HELLO ');
TRIM(' HELLO ')
HELLO
1 row in set (0.00 sec)
Date Functions
1) NOW():- Function that returns the current date and time.

2) DATE():- This function returns the date part from Date and time

3) MONTH() returns the month part of a date.

4) MONTHNAME() :-gives month name from a date.

5) YEAR():- Date function returns year part of a date.

6) DAY():- Day function provide the day part of Date.

7) DAYNAME():- Returns the weekday name of a date.

 Aggregate functions: -
It performs calculation on multiple values and return a single value. It is also known as Multi-row
functions
List of aggregate functions:
MAX(),MIN(),AVG(),SUM(),COUNT()
Note: Aggregate function will not consider NULL values for calculation.
Consider the following table named student:

1) MAX():- returns maximum value from a dataset.

2) MIN():- Returns the minimum value from a dataset.

3) AVG():- Returns the average value from a dataset.

4) SUM():- It returns the sum of values in a dataset.

5) COUNT(*):-Will return the total number of rows present in the Table

 6) COUNT(Field Name) will count the number of values(excluding NULL) present in the dataset.

Here answer 4 is displyed even though 5 rows are present in the student table because one NULL
value is present in the column named mark.
Multiple Choice Questions
1) Prachi has given the following command to obtain the highest marks Select max(marks) from student
where group by class; but she is not getting the desired result. Help her by writing the correct command.
a. Select max(marks) from student where group by class;
b. Select class, max(marks) from student group by marks;
c. Select class, max(marks) group by class from student;
d. Select class, max(marks) from student group by class;
2) Help Ritesh to write the command to display the name of the youngest student?
a. select name,min(DOB) from student ;
b. select name,max(DOB) from student ;
c. select name,min(DOB) from student group by name ;
d. select name,maximum(DOB) from student;
3) All aggregate functions ignore NULLs except for the __________ function.
a. Distinct b. Count(*) c. Average() d. None of these
4) Which of the following are correct aggregate functions in SQL
a. AVERAGE() b. MAXIMUM() c. COUNT() d. TOTAL()
5) What will be returned by the given query ? SELECT INSTR(‘INDIA’, ‘DI’);
a. 2 b. 3 c. -2 d -3
6) What will be returned by the given query ? SELECT ROUND(153.669,2);
a. 153.6 b. 153.66 c. 153.67 d. 153.7
7) What will be returned by the given query? SELECT month(‘2020-05-11’);
a. 5 b. 11 c. May d. November
8) “COUNT” keyword belongs to which categories in Mysql?
a. Aggregate functions b. Operators c. Clauses d. All of the mentioned
9) What will be the outcome of the following query?
Select round(144.23,-1) from dual;
a. 140 b. 144 c. 150 d. 100
10) What will be the outcome of the following query?
Select substr(‘Informatics Practics’, 3,4);
a. from b. orma c. info d. matic
Answer:
1. d 2. d 3. a 4. c 5. B 6. C 7. A 8. A 9. a 10. A

Answer the following:

1. Select Round(546.345, -2); Ans: 500
2. Select Round(546.345, -3); Ans: 1000
3. Select MOD(34,5); Ans: 4
4. Select MOD(6,8); Ans: 6
5. Select MOD(12.6, 8); Ans: 4.6
6. Select Pow(2,4); Ans: 16
7. Select Substr('JS09876/XII-H/12',-8,7); Ans: XII-H/1
8. Select Trim(Leading 'Pp' from 'PppProgram Features'); Ans: pProgram Features
9. Select Instr('COORDINATION COMMITTEE ORDER','OR'); Ans: 3
10. Select left('COORDINATION COMMITTEE ORDER', length('committee')); Ans: COORDINAT
11. SELECT CONCAT(NAME,dept) AS 'NAME DEPT' FROM Employee;
12. SELECT LOWER(“SCHOOL”); Ans: school
13. Select substr('ABCDEFGH', 3,4); Ans: CDEF
14. Select substr('ABCDEFGH', -3,4); Ans: FGH
15. SELECT UCASE('pqrxyz'); Ans: PQRXYZ
16. SELECT RTRIM('abcdefgh '); Ans: abcdefgh
17. Select trim(' pqr Periodic Test 1 is over pqr '); Ans: pqr Periodic Test 1 is over pqr
18. Select instr(‘DAV International School, Amritsar','DAV'); Ans: 1
19. SELECT LENGTH(‘DAV International School, Amritsar’); Ans: 34
20. SELECT LENGTH(12345); Ans: 5
21. Select left('DAV12345/DAV/XII/A',7); Ans: DAV1234
22. Select substr('DAV12345/DAV/XII/A',9,4); Ans: /DAV
23. Select right('DAV12345/DAV/XII/A',5); Ans: XII/A
24. Select MOD(23,5); Ans: 3
25. select power(3,4); Ans: 81
26. select ROUND(20.392,1); Ans: 20.3
27. select pow(2,3), power(-2,3), pow(3,4); Ans: 8, -8, 81
28. select round(12345.789,2), round(1434.56,-1); Ans: 12345.79, 1430
29. select round(62.789),round(6.89,0); Ans: 63, 7
30. select mod(23,2), mod(78,4); Ans: 1, 2
31. select concat(“info”,”rmatics”); Ans: informatics
32. select concat(“ISM -”,concat(“xii”,”I”)); Ans: ISM -xiiI
33. select lower(“INFORM”),lcase(“Class XII”); Ans: inform, class xii
34. select upper(“Class xii”),ucase(“informatics”); Ans: CLASS XII, INFORMATICS
35. select substring(“India is the Best”,3,2),substr(“Indian”,-2,1); Ans: di, a
36. select length(trim(“ abcde defe “)); Ans: 10
37. select instr(“Informatics”,”r”); Ans: 5
38. select length(“ab cde fge”); Ans: 10
39. select left(“Informatics”,4) from dual; Ans: Info
40. select right(“Informatics”,6); Ans: matics
41. select mid(“Indian School Muscat”,8,6); Ans: School
42. Select curdate(), current_date(); Ans: 2023-10-15, 2023-10-15
43. Select date(now()); Ans: 2023-10-15
44. Select month(now()); Ans: 10
45. Select year(“2012-02-21”); Ans: 2012
46. Select dayname(now()); Ans: Sunday
47. Select dayofmonth(“2011-03-23”); Ans: 23
48. Select dayofweek(now()); Ans: 1
49. Select dayofyear(“2016-02-04”); Ans: 35
50. Select dayofyear(“2012-02-02”); Ans: 33
Short Questions
1. A relation ‘Vehicles’ is given below:

Write SQL commands to:

a. Display the average price of each type of vehicle having quantity more than 20.
b. Count the type of vehicles manufactured by each company.
c. Display the total price of all the types of vehicles.
Ans:
a. select Type, avg(Price) from Vehicle group by Type having Qty>20;
b. select Company, count(distinct Type) from Vehicle group by Company;
c. Select Type, sum(Price* Qty) from Vehicle group by Type;

2. Consider the table ‘FANS’ and answer the following.

Write MySQL queries for the following:

a. To display the details of fans in descending order of their DOB
b. To count the total number of fans of each fan mode.
c. To display the dob of the youngest fan
Ans:
a. Select * from fans order by fan_dob desc;
b. Select fan_mode, count(*) from fans group by fan_mode;
c. Select max(fan_dob) from fans;

3. Write commands in SQL for (i) and (ii) and output for (iii)

a. To display the details of the store in alphabetical order of name.

b. To display the City and the number of stores located in that City, only if the number of stores
is more than 2.
c. SELECT COUNT(STOREID), NOOFEMP FROM STORE GROUP BY NOOFEMP
HAVING MAX(SALESAMT)
 Ans:
a. SELECT * FROM STORE ORDER BY NAME;
b. SELECT CITY, COUNT(*) FROM STORE GROUP BY STORE HAVING COUNT(*)>2;
c. Count(StoreId) | NoOfEmp |
1 10
1 11
1 8
1 5

4. Based on the table: “Emp” given below:

Write the output of the following:

i. Select mod(Salary, 100) from emp;
ii. Select average(Salary) from emp;
iii. Select sum(Salary) from emp where empid > 3;
iv. Select max(Salary) from emp;
Ans:
i. mod(Salary, 100)
0
0
0
0
Null
ii. average(Salary)
47500
iii. sum(Salary)
40000
iv. max(Salary)
55000
5. Explain the functions with suitable example to do this:
i. To find the position of specific word or character in the given text
Ans: instr()
Example
Select instr(‘School’, ‘c’);
ii. Display the total number characters from the text
Ans: length()
Example
Select length(“hello”);
iii. To display remainder of given two numbers
Ans: mod()
Example
Selct mod(10,3);
6. Vats is working with functions of MySQL. Explain him the following with example:
i. To remove extra leading spaces from the text
Ans: trim()
Example
Select trin(‘ School ’);

ii. To return only day part from today’s date

Ans: day()
Example
Select day(curdate());

iii. To return average of particular column from the table

Ans:avg()
Example
Select avg(salary) from table;

7. Om has written following queries:

i. select count(*) from student;
ii. select count(std_no) from student;
He was surprised with the output as query (i) returns 5 rows whereas Query(ii) returns only 3 rows.
Explain why?
Ans:
In first query Om has used count(*) which will return the total number of rows present in the Table
and in second query he used count(std_no) mean column name in count function which will count the
number of values(excluding NULL) present in the dataset.