Select ename,sal,deptno from emp e where deptno=10 and sal>any(select sal from emp where e.deptno!=deptno); 73.Display the names of employees in upper case.
Select upper(ename) from emp; 74.Display the names of employees in lower case. Select lower(ename) from emp; 75.Display the names of employees in proper case. Select initcap(ename) from emp; 76.Find out the length of your name using appropriate function. Select length(‘india’) from dual; 77.Display the length of all employees names. Select sum(length(ename)) from emp; 78.Display the name of the employee concatenate with empno. 79.Select ename||empno from emp; 80. (or) Select concat(ename,empno) from emp; 81.Use appropriate function and extract 3 characters starting from 2 characters from the following string ‘oracle’ i.e. the output should be ‘rac’. Select substr(‘oracle’,2,3) from dual;
82.Find the first occurrence of character a from the following string ‘computer maintenance corporation’. Select instr(‘computer maintenance corporation’,’a’,1,1) from dual; 83.Replace every occurrence of alphabet Awith B in the string allen’s (user translate function). Select replace(‘allens’,’A’,’b’) from dual; 84.Display the information from emp table wherever job ‘manager’ is found it should be displayed as boss(replace function). Select empno,ename,replace(job,’MANAGER’,’Boss’) job from emp; 85.Display empno,ename,deptno from emp table instead of display department numbers display the related department name (use decode function). Select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; 86.Display your age in days. Select round(sysdate-to_date(’15-aug-1947’)) from dual; 87.Display your age in months. Slect floor(months_between(sysdare,’15-aug-1947’)) “age in months” from dual; 88.Display current date as 15th august Friday nineteen forty seven. Select to_char(sysdate,’ddth month day year’) from dual;
89.Display the following output for each row from emp table as ‘scott has joined the company onWednesday 13th august nineteen ninety’. Select ename||’has joined the company on ‘|| to_char (hiredate,’day ddth month year’) from emp; 90.Find the date of nearest saturdau after current day. Select next_day(sysdate,’SATURDAY’) from dual; 91.Display current time. Select to_char(sysdate,’hh:mi:ss’) time from dual; 92.Display the date three months before the current date. Select add_months(sysdate,-3) from dual; 93.Display the common jobs from department number 10 and 20 . 94.Select job from emp where deptno=10 and fob in(select job from emp where deptno=20); (or) Select job from emp where deptno=10 intersect select job from emp where deptno=20; 95.Display the jobs found in department numer 10 and 20 eliminate duplicate jobs. 96.Select distinct(job) from emp where deptno=10 and job in(select job from emp where deptno=20);
(or) Select job from emp where deptno=10 intersect job from emp where deptno=20; 97.Display the jobs which are unique to deptno 10.