How to use Built Function in SQL

create table emp11(id int,name char(10),surname char(10),salary int,jdate date,rdate date,dept varchar(20),location varchar(20),dob date)

insert into emp11 values(1,’vipul’,’sane’,20000,’12/23/2015′,’12/7/2019′,’sql’,’kolhapur’,’3/8/1994′)

insert into emp11 values(2,’kushal’,’ghadge’,25000,’1/24/2016′,’8/12/2018′,’servicing’,’satara’,’12/24/1989′)

insert into emp11 values(3,’abhi’,’patil’,90000,’10/13/2015′,’4/30/2018′,’sap’,’pune’,’6/23/1993′)

insert into emp11 values(4,’tej’,’ghatage’,30000,’12/26/2015′,’7/3/2017′,’ccna’,’mumbai’,’7/3/1992′)

insert into emp11 values(5,’prafull’,’sutar’,35000,’8/17/2016′,’11/27/2018′,’automation’,’sagali’,’9/5/1995′)

select * from emp11

• Concate

select concat(name,surname) from emp11

Built in Functions

create table emp11(id int,name char(10),surname char(10),salary int,jdate date,rdate date,dept varchar(20),location varchar(20),dob date)

insert into emp11 values(1,’vipul’,’sane’,20000,’12/23/2015′,’12/7/2019′,’sql’,’kolhapur’,’3/8/1994′)

insert into emp11 values(2,’kushal’,’ghadge’,25000,’1/24/2016′,’8/12/2018′,’servicing’,’satara’,’12/24/1989′)

insert into emp11 values(3,’abhi’,’patil’,90000,’10/13/2015′,’4/30/2018′,’sap’,’pune’,’6/23/1993′)

insert into emp11 values(4,’tej’,’ghatage’,30000,’12/26/2015′,’7/3/2017′,’ccna’,’mumbai’,’7/3/1992′)

insert into emp11 values(5,’prafull’,’sutar’,35000,’8/17/2016′,’11/27/2018′,’automation’,’sagali’,’9/5/1995′)

select * from emp11

• Concate

select concat(name,surname) from emp11

• substr

select substr(name,2) from emp11

• instr

select instr(name,’i’) from emp11

• length

select length(location) from emp11

• upper

select upper(name)from emp11

• lower

select lower(name)from emp11

• reverse

select reverse(name)from emp11

• initcap

select initcap(name)from emp11

• mod

select mod(id,2)from emp11

select * from emp11 where mod(id,2)=1

•  greatest

create table tab7(a int,b int,c int)

insert into tab7 values(10,20,3)

insert into tab7 values(30,4,60)

insert into tab7 values(90,6,12)

select * from tab7

select greatest(a,b,c) from tab7

• Least

select least(a,b,c) from tab7

•  Sqrt

select sqrt(salary) from emp11

•  Round

select round(sqrt(salary)) from emp11

•  Trim

select trim(name) from emp11

•  Ltrim

select ltrim(name) from emp11

•  Rtrim

select rtrim(name) from emp11

•  Lpad

select lpad(name,7,’er.’) from emp11 where id=1

•  Rpad

select lpad(name,8,’er.’) from emp4 where id=1

•  Replace

select replace(name,’vip’,’abc’) from emp11 where id=1

•  Translate

select translate(name,’vpl’,’kop’)from emp11 where id=1

•  Next_day

select next_day(jdate,’sun’) from emp11

• Last_day

select last_day(rdate) from emp11

•  Months_between

select round(months_between(rdate,jdate)) from emp11

• Add_months

select add_months(rdate,10) from emp11

• Nvl

select nvl(salary,0) from emp11

select nvl2(12,45,68) from dual

Aggrigate Functions

• Max

select max(salary) from emp11

• Min

select min(salary) from emp11

• Avg

select avg(salary) from emp11

• Sum

select sum(salary) from emp11

• Count

select count(salary) from emp11

alter table emp11 rename column salary to total

alter table emp11 add(salary int,hra int,acc int,tra int,pf int)

update emp11 set hra=total*8/100,acc=total*7/100,tra=total*6/100,pf=total*5/100,salary=total-(hra+acc+tra+pf)

select * from emp11