Tricky SQL Queries For Interview
Below is the list of Tricky SQL Queries For Interview asked frequently in the database testing interviews.
1) Display the details of all employees.
SQL> select * from emp;2) Display the depart information from the department table.
SQL>select * from dept;3) Display the name and job of all the employees.
SQL>select ename, job from emp;4) Display the name and salary of all the employees.
SQL>select ename, sal from emp;5) Display the employee no and the total salary for all the employees.
SQL>select empno, sal+comm as total from emp group by empno;6) Display the employee name and annual salary for all employees.
SQL>select ename, sal * 12 as annual salary from emp;7) Display the names of all the employees who are working in depart number 10.
SQL>select emame from emp where deptno=10;8) Display the names of all the employees who are working as clerks and drawing a salary more than 3000.
SQL>select ename from emp where job='CLERKS ‘and sal>3000;9) Display the employee number and name who are earning comm.
SQL>select empno, ename from emp where comm is not null;10) Display the employee number and name who do not earn any comm.
SQL>select empno, ename from emp where comm is null;11) Display the names of employees who are working as clerks, salesman or analyst and drawing a salary more than 3000.
SQL>select ename from emp where job='CLERK' OR JOB='SALESMAN' OR JOB='ANALYST' AND SAL>3000;12) Display the names of the employees who are working in the company for the past 5 years.
SQL>select ename from emp where to_char (sysdate,'YYYY') -to_char (hiredate,'YYYY') >=5;13) Display the list of employees who have joined the company before30-JUN-90 or after 31-DEC-90.
SQL>select ename from emp where hiredate < '30- JUN-1990' or hiredate >'31-DEC-90';14) Display current Date.
SQL>select sysdate from dual;15) Display the list of all users in your database (use catalog table).
SQL>select username from all users;16) Give the names of all tables from the current user.
SQL>select name from tab;17) Give the name of the current user.
SQL>show user18) Display the names of employees working in depart number 10 or 20 or 40 or employees working as CLERKS, SALESMAN or ANALYST.
SQL> Select ename from emp where deptno in (10, 20, 40) or job in ('CLERKS','SALESMAN','ANALYST');19) Display the names of employees whose name starts with the alphabet S.
SQL>select ename from emp where ename like 'S%';20) Display the Employee names for employees whose name ends with Alphabet S.
SQL> Select ename from emp where ename like'%S';21) Display the names of employees whose names have second alphabet A in their names.
SQL> Select ename from EMP where ename like '_A%';22) Select the names of the employee whose names are exactly five characters in length.
SQL> select ename from emp wherelength (ename) =5;23) Display the names of the employee who are not working as MANAGERS.
SQL> Select ename from emp where job not in ('MANAGER');24) Give the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.
SQL>select ename from emp where job notin ('SALESMAN','CLERK','ANALYST');25) Display all rows from EMP table. The system should wait after every screen full of information.
SQL> Set pause on26) Display the total number of employees working in the company.
SQL> Select count (*) from EMP;27) Display the total salary begging paid to all employees.
SQL>select sum (sal) from emp;28) Display the maximum salary from emp table.
SQL>select max (sal) from emp;29) Display the minimum salary from emp table.
SQL>select min (sal) from emp;30) Display the average salary from emp table.
SQL>select avg (sal) from emp;31) Display the maximum salary being paid to the CLERK.
SQL>select max (sal) from emp where job='CLERK';32) Display the maximum salary being paid to depart number 20.
SQL>select max (sal) from emp where deptno=20;33) Display the minimum salary being paid to any SALESMAN.
SQL>select min (sal) from emp where job='SALESMAN';34) Display the average salary drawn by MANAGERS.
SQL>select avg (sal) from emp where job='MANAGER';35) Display the total salary drawn by ANALYST working in depart number40.
SQL>select sum (sal) from emp where job='ANALYST' and deptno=40;36) Display the names of the employee in order of salary i.e. the name of the employee earning the lowest salary should salary appear first.
SQL>select ename from emp order by sal;37) Display the names of the employee in descending order of salary.
SQL>select ename from emp order by sal desc;38) Display the names of the employee in order of employee name.
SQL>select ename from emp order by name;39) Display empno, ename, deptno, sal sort the output first base on name and within name by deptno and with in deptno by sal.
SQL>select empno, ename, deptno, sal from emp order by ename, deptno, sal;40) Display the name of the employee along with their annual salary (sal*12) . The name of the employee earning the highest annual salary should appear first.
SQL>select ename, sal*12 from emp order by sal desc;41) Display name, salary, hra, pf, da, total salary for each employee. The output should be in the order of total salary; hra 15% of salary, da 10%of salary, pf 5%salary, and total salary will be (salary+hra+da-pf).
SQL>select ename, sal, sal/100*15 as hra, sal/100*5 aspf, sal/100*10 asda, sal+sal/100*15+sal/100*10-sal/100*5 as total from emp;42) Display depart numbers and the total number of employees working in each department.
SQL>select deptno, count (deptno) from emp group by deptno;43) Display the various jobs and the total number of employees within each job group.
SQL>select job, count (job) from emp group by job;44) Display the depart numbers and total salary for each department.
SQL>select deptno, sum (sal) from emp group by deptno;45) Display the depart numbers and max salary for each department.
SQL>select deptno, max (sal) from emp group by deptno;46) Display the various jobs and total salary for each job.
SQL>select job, sum (sal) from emp group by job;47) Display the various jobs and total salary for each job.
SQL>select job, min (sal) from EMP group by job;48) Display the depart numbers with more than three employees in each dept.
SQL>select deptno, count (deptno) from emp group by deptno having count (*) >3;49) Display the various jobs along with the total salary for each of the jobs where the total salary is greater than 40000.
SQL>select job, sum (sal) from emp group by job having sum (sal) >40000;50) Display the various jobs along with the total number of employees in each job. The output should contain only those jobs with more than three employees.
SQL>select job, count (empno) from emp group by job having count (job) >3
Above is the list of Tricky SQL Queries For Interview.