Function In SQL
For doing operations on data SQL has many built-in Function In SQL, they are categorized in two categories and further sub-categorized in different seven functions under each category.
- Functions are pre define implementation provided by SQL.
- There are different categories of function
- Aggregate Function
- String Function
- Date Function
Aggregate Function
In this type values are group together and the single value as an output
Count()
mysql> select count(id) from employee;
+———–+
| count(id) |
+———–+
| 7 |
+———–+
1 row in set (0.01 sec)
mysql> select count(id) from employee where isActive = 1;
+———–+
| count(id) |
+———–+
| 5 |
+———–+
1 row in set (0.01 sec)
mysql> select count(id) as totalEmp from employee where isActive = 1;
+———-+
| totalEmp |
+———-+
| 5 |
+———-+
1 row in set (0.00 sec)
Sum()
mysql> select sum(salary) from employee;
+————–+
| sum(salary) |
+————–+
| 14488915.111 |
+————–+
1 row in set (0.00 sec)
mysql> select sum(salary) as totalSalary from employee;
+————–+
| totalSalary |
+————–+
| 14488915.111 |
+————–+
1 row in set (0.00 sec)
mysql> select sum(salary) as totalSalary from employee where isActive=0;
+————-+
| totalSalary |
+————-+
| 4783975.746 |
+————-+
1 row in set (0.00 sec)
Avg()
mysql> select avg(salary) as averageSalary from employee where isActive=1;
+——————–+
| averageSalary |
+——————–+
| 1940987.8730000001 |
+——————–+
1 row in set (0.00 sec)
mysql> select avg(salary) as averageSalary from employee;
+——————–+
| averageSalary |
+——————–+
| 2069845.0158571429 |
+——————–+
1 row in set (0.00 sec)
Min()
mysql> select min(salary) as minimumSalary from employee;
+—————+
| minimumSalary |
+—————+
| 1236987.873 |
+—————+
1 row in set (0.01 sec)
mysql> select min(salary) as minimumSalary from employee where isActive=1;
+—————+
| minimumSalary |
+—————+
| 1236987.873 |
+—————+
1 row in set (0.00 sec)
Max()
mysql> select max(salary) as maximumSalary from employee where isActive=1;
mysql> select max(salary) as maximumSalary from employee;
String function
Upper()
Is use to convert string value in to Capital case
mysql> select id, upper(name) as name, contact from employee;
Lower()
Is use to convert string into small case.
mysql> select id, lower(name) as name, contact from employee;
Concat()
Is use to combine the multiple column into a single column
select id, concat(name,’-‘ ,contact) from employee;
length()
Is use to get the total count of character in the string
mysql> select id, contact, length(contact) from employee;
trim()
Is use to remove the spaces from both the side (left and right) of the string. It will not remove the space which is in between the word.
select id,trim(name) from employee;
ltrim() and rtrim()
is use to remove the spaces from left side (ltrim) and right side (rtrim) of the string
mysql> select id,rtrim(name) from employee;
mysql> select id,ltrim(name) from employee;
Date Function
now()
To get the system data and time
mysql> select now();
+———————+
| now() |
+———————+
| 2022-04-07 08:02:07 |
+———————+
1 row in set (0.01 sec)
curdate()
to get the current system date
mysql> select curdate();
+————+
| curdate() |
+————+
| 2022-04-07 |
+————+
1 row in set (0.00 sec)
curtime()
can get the current system time
mysql> select curtime();
+———–+
| curtime() |
+———–+
| 08:02:56 |
+———–+
1 row in set (0.00 sec)
year()
Is use to get the year from the given date
mysql> select id,name,year(doj) from employee;
month()
Is use to get the month from the given date
mysql> select id,name,month(doj) from employee;
monthname()
Is use to get the month in word from the given date
mysql> select id,name,monthname(doj) from employee;
day()
is use to print the day in the given date
mysql> select id,name,day(doj) from employee;
datediff()
to Get the difference in two dates in day.
mysql> select id,name,doj, datediff(now(), doj) from employee;
date_format()
is use to fomrat the date
mysql> select id,name,date_format(doj,’%d %M %Y’) from employee;
Join Telegram : Click Here
All Full Stack Java Study Material
Job’s For Fresher