Select enamesaldeptno from emp e where deptno10 and salanyselect sal from emp

Select enamesaldeptno from emp e where deptno10 and

This preview shows page 36 - 41 out of 59 pages.

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.
Image of page 36
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;
Image of page 37
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;
Image of page 38
89. Display the following output for each row from emp table as ‘scott has joined the company on Wednesday 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);
Image of page 39
(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.
Image of page 40
Image of page 41

You've reached the end of your free preview.

Want to read all 59 pages?

  • Winter '15
  • emp

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture