Oracle- SQL interview Question Answer Part -4

61) Display the names of the employees from department number 10 with

     salary greater than that of all employee working in other departments.

SQL>select ename from emp where deptno=10 and sal>all(select sal from

            emp where deptno not in 10).

62) Display the names of the employees in Uppercase.

SQL>select upper(ename)from emp

63) Display the names of the employees in Lowecase.

SQL>select lower(ename)from emp

64) Display the names of the employees in Propercase.

SQL>select initcap(ename)from emp;

65) Display the length of Your name using appropriate function.

SQL>select length('name') from dual

66) Display the length of all the employee names.

SQL>select length(ename) from emp;

67) select name of the employee concatenate with employee number.

SQL>select ename||empno from emp;

68) User appropriate function and extract 3 characters starting from 2

      Administrator characters from the following  string 'Oracle'. i.e the

         out put should be 'ac'.

SQL>select substr('oracle',3,2) from dual

69) Find the First occurance of character 'a' from the following string i.e

       'Computer Maintenance Corporation'.

SQL>SELECT INSTR('Computer Maintenance Corporation','a',1) FROM DUAL

70) Replace every occurance of alphabhet A with B in the string Allens

        (use  translate function)

SQL>select translate('Allens','A','B') from dual

71) Display the informaction from emp table.Where job manager is found it

should be displayed as boos(Use replace function).

SQL>select replace(JOB,'MANAGER','BOSS') FROM EMP;

72) Display empno,ename,deptno from emp table.Instead of display department

numbers display the related department name(Use decode function).

SQL>select empno,ename,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPRATIONS') from emp;

73) Display your age in days.

SQL>select to_date(sysdate)-to_date('10-sep-77')from dual

74) Display your age in months.

SQL>select months_between(sysdate,'10-sep-77') from dual

75) Display the current date as 15th Augest Friday Nineteen Ninety Saven.

 SQL>select to_char(sysdate,'ddth Month day year') from dual

76) Display the following output for each row from emp table.

      scott has joined the company on wednesday 13th August ninten nintey.

SQL>select ENAME||' HAS JOINED THE COMPANY ON  '||to_char(HIREDATE,'day

         ddth Month  year')   from EMP;

77) Find the date for nearest saturday after current date.

SQL>SELECT NEXT_DAY(SYSDATE,'SATURDAY')FROM DUAL;

78) Display current time.

SQL>select to_char(sysdate,'hh:MM:ss') from dual.

79) Display the date three months Before the current date.

SQL>select add_months(sysdate,3) from dual;

80) Display the common jobs from department number 10 and 20.

SQL>select job from emp where deptno=10 and job in(select job from emp

        where deptno=20);