practice quries

11)Find the day of the week on which Smith joined .
a)select ename,hiredate ,extract(day from hiredate) from emp where ename like'SMITH';


12)Find out the time of the day in(hh24:mi:ss) on which “Ford” joined the company.

a) select to_char(to_date(hiredate,'dd-mon-yy'),'hh24:mi:ss') from emp where ename like 'FORD';


13)Retrieve the analyst record with the hire date formatted as the 3rd,December,1994.
a)select job,hiredate , to_char(to_date(hiredate,'dd-mon-yy'),'DDTH,month,yyyy')from emp where job like 'ANALYST';

14)Find the day of week on which Smith joined .
a)select ename,hiredate ,extract(day from hiredate) from emp where ename like'SMITH';
select to_char(to_date(hiredate,'dd-mon-yy'),'DAY') from emp where ename like'SMITH';


15)How many months has the president worked for the company .round the nearest whole number of months.
a) select job,round(months_between(sysdate,hiredate)) from emp where job ='PRESIDENT';


16) Write a query to display rows from emp table , if empno contains digit 2.
a) select * from emp where empno like '%2%';


17) write a query to display greatest date 12-jun-99,17-mar-99,13-jun-99.
a)select greatest('12-jun-99','17-mar-99','13-jun-99') from dual;


18) Write a query to display next Monday date and find the size of the date
a)select next_day(sysdate,'monday') from dual;


19) Write a query for making the all initial letters of the give string
“yalamanchili school of learning located in the diamond park junction and beside the sai ram parlour”. In caps.;
a)select initcap('yalamanchili school of learning located in the diamond park junction and beside the sai ram parlour') from dual;

20) Raise the salary of emp Adams.
a)update emp set sal=sal+1000 from emp where ename='ADAMS';


21)Write a query to display the details of all employees who earn salary greatest than the average salary.
a)select sal from emp where sal>any(select avg(sal) from emp);

22)Determine the employee in Scott department and also have worked in sales.


23)Write a sub query to display max salary from emp table.
a) select * from( select ename,sal from emp order by sal desc) where rownum<2;


24) Write a query to display details of employees in dept no 20 , who have the same job some one in sales dept has as well.
a) select sal,ename from emp where deptno=20 and job='SALESMAN';



25)Product a list of jobs for departments 10,20,50 in that order . Display job id, and dept id using set operates.
a)

26)Display last name ,job and salary for all employees whose job is sales representatives or stock clerk and salary is not equal to 2500 ,3500 0r 7000.
a)select ename,job from emp where job in(sales,representative,stockclerk)and salary not in(2500,3500,7000);

27)WAQ to display employees getting first two least salaries.
a)select * from(select sal from emp order by sal desc) where rownum<2;

28) WAQ to display employees working in smith's department.
a)select * from emp where deptno=(select deptno from emp where ename like 'SMITH');