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
