How to use Built Function in SQL

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

Lotus it hub

 

 

  • 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

Louts it hub

  • lower

 

select lower(name)from emp11

Louts it hub

  • reverse

 

select reverse(name)from emp11

 

  • initcap

 

select initcap(name)from emp11

Louts it hub

  • 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

Louts it hub

  • Least

 

select least(a,b,c) from tab7

 

  •  Sqrt

 

select sqrt(salary) from emp11

Lotus it hub

 

  •  Round

 

select round(sqrt(salary)) from emp11

Lotus it hub

 

  •  Trim

 

select trim(name) from emp11

Lotus it hub

 

  •  Ltrim

 

select ltrim(name) from emp11

Louts it hub

  •  Rtrim

 

select rtrim(name) from emp11

Louts it hub

  •  Lpad

 

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

Louts it hub

  •  Rpad

 

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

Louts it hub

  •  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

Lotus it Hub

  • Last_day

 

select last_day(rdate) from emp11

Lotus it Hub

  •  Months_between

 

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

 

  • Add_months

 

select add_months(rdate,10) from emp11

Lotus it hub

  • Nvl

 

select nvl(salary,0) from emp11

Lotus it hub

select nvl2(12,45,68) from dual

Aggrigate Functions

Lotus it hub

  • Max

 

select max(salary) from emp11

Louts it hub

  • Min

 

select min(salary) from emp11

Louts it hub

  • Avg

 

select avg(salary) from emp11

Louts it hub

  • 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