Oracle- SQL interview Question Answer Part -4 - ETL- iNFORMATICA DEVELOPER

Friday, July 5, 2019

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.

         ddth Month  year')   from EMP;

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


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);

No comments:

Post a Comment