SQL Queries ask in Interviews Part 6

76) Display the following output for each row from emp table. A 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);

81)Display the names of employees who earn a salary more than that of A or that of salary greater than   that of B.
SQL>select ename,sal from emp where sal> (select sal from emp where ename=’A')and sal> (select sal from emp where ename=’B');

81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.
SQL>select distinct(job) from emp where deptno=10 or deptno=20;

(or)

SQL>select distinct(job) from emp where deptno in(10,20);

82) Display the jobs which are unique to department 10.
SQL>select distinct(job) from emp where deptno=10;

83) Display the details of those who do not have any person working under them.
SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename having count(*)=1;

84) Display the details of those employees who are in sales department and grade is 3.
SQL>select * from emp where deptno=(select deptno from dept where dname=’SALES’)and sal between(select losal from salgrade where grade=3)and (select hisal from salgrade where grade=3);

85) Display those who are not managers and who are managers any one.
i)display the managers names
SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;
ii)display the who are not managers
SQL>select ename from emp where ename not in(select distinct(m.ename) from emp e,emp m where m.empno=e.mgr);

86) Display those employee whose name contains not less than 4 characters.
SQL>select ename from emp where length(ename)>4;

87) Display those department whose name start with “S” while the location name ends with “K”.
SQL>select dname from dept where dname like ‘S%’ and loc like ‘%K’;

88) Display those employees whose manager name is akash.
SQL>select p.ename from emp e,emp p where e.empno=p.mgr and e.ename=’Akash’;

89) Display those employees whose salary is more than 3000 after giving 20% increment.
SQL>select ename,sal from emp where (sal+sal*.2)>3000;

90) Display all employees while their dept names;
SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno;

<<<Previous      Continue>>>>