Queries

8) alter table abhi_emp1 add column address varchar (20) ; 9) select distinct job from abhi_emp1; 10) select concat(concat((fname),' - '),(lname)) from abhi_emp1; 11) select fname,lname job,hiredate from abhi_emp1 where hiredate between '17/12/1980' and '23/01/1999' 12) select fname,lname,hiredate from abhi_emp1 where char(year(hiredate)) in (select (char(year(hiredate))) from abhi_emp1); select year(hiredate) from abhi_emp1; 13) select fname from abhi_emp1 where fname ='--a%'; 14) select lname from abhi_emp1 where fname like '%a%e%'; 15) select * from abhi_emp1 where job in ('manager','slaesman') and salary not in(1200,1500,2000); 16) select * from abhi_emp1 where comm=(0.20*sal); 17) select current date from sysibm.sysdummy1; 18) select fname,lname,month(hiredate) from abhi_emp1; 19) select fname,lname,year(hiredate) from abhi_emp1; 20)

select fname,lname, case (dept) when 1 then 'manager' when 2 then 'salesman' when 3 then 'clerk' else 'others' end as work from abhi_emp1;

21) select * from abhi_emp1 order by dept desc; 22) select empno,fname,lname,hiredate from abhi_emp1 order by hiredate; 23) select dept as department,max(sal) as max_sal,min(sal) as min_sal,avg(sal) as avg_sal from abhi_emp1 group by dept ; 24)

select job, max(sal) as max_sal, min(sal) as min_sal from abhi_emp1 group by job having max(sal) > 10000;

25) select concat(concat(concat(concat(concat(concat(lname,','),fname),' '),'is'),' '),'m') from abhi_emp1; 26 select day(hiredate) as day, dayname(hiredate) as day_name, month(hiredate) as hire_date, year(hiredate) as hire_year from abhi_emp1; select day(hiredate) as day, dayofweek(hiredate) as day_name, month(hiredate) as hire_date, dayofyear(hiredate) as hire_year from abhi_emp1;

27)

skipped.....

28) select round(sal,-2) from abhi_emp1 where sal = (select max(sal) from abhi_emp1); 29) select (((year('12/31/1997')-year(hiredate))*365) + ((month('12/31/1997')month(hiredate))*12) + ((day('12/31/1997')-day(hiredate))*30)) from abhi_emp1; 30) select year(hiredate),avg(sal) from abhi_emp1 group by year(hiredate) order by year(hiredate); 31)

select fname,lname,dept from abhi_emp1;

32) select distinct empno,fname,lname,job,mgr,hiredate,sal,comm,dept from abhi_emp1; 33)