SQL Queries ask in Interviews Part 5

SQL Queries ask in Interviews, www.askhareesh.com
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 Lowercase.

SQL>select lower(ename)from emp;

64) Display the names of the employees in Proper Case.

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 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 occurrence of alphabet A with B in the string Allens(use translate function)

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

71) Display the information 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 August Friday Nineteen Ninety Seven.

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