Practice SQL Queries with Solutions For Employee Table – Part 1
In this blogpost we would be providing Sample Practice SQL Queries with Solutions For Employee Table. If you are an Database Administrator or a Database Analyst oor someone who wants to know how to manipulate data from employee tables in your organization, you can use make use of this SQL solutions. You can use this SQL Queries with Solutions For Employee Table for most common problems related to retrieving data from SQL Database.
We have divided the Practice SQL Queries with Solutions For Employee Table in four different parts, as we do not want to make each post lengthy and boring. Each post will contain 50 Practice SQL Queries with Solutions For Employee Table. Use the following table to browse across different parts.
Here is the List of 50 Practice SQL Queries with Solutions For Employee Table – Part 1
| Q. No | Practice SQL Queries with Solutions For Employee Table – Part 1 |
| 1. | Display all the information of the emp table. |
| SQL> | Select * from emp ; |
| |
|
| 2. | Display unique jobs from EMP table. |
| SQL> | SELECT DISTINCT Job FROM EMP GROUP BY Job ; |
| Also Read: Sample C# Interview Questions and Answers | |
| 3. | List the details of the emps in asc order of their salaries. |
| SQL> | SELECT * FROM EMP
ORDER BY Sal ASC ; |
| Also Read: Best PL/SQL and SQL Tips To Read Before Interview | |
| 4. | List the details of the emps in asc order of the Deptnos and desc of Jobs. |
| SQL> | SELECT * FROM EMP
ORDER BY Deptno ASC, Job DESC ; |
| Also Read: Sample Hadoop HBase Interview Questions and Answers | |
| 5. | Display all the unique job groups in the descending order |
| SQL> | select unique job from emp order by job desc ; |
| |
|
| 6. | Display all the details of all ‘Mgrs’ |
| SQL> | select * from emp where job = ‘MANAGER’ ; |
| Also Read: Sample Hadoop HDFS Interview Questions and Answers | |
| 7. | List the emps who joined before 1981. |
| SQL> | select * from emp where hiredate < ’01-Jan-1981′ ; |
| 8. | List the Empno, Ename, Sal, Daily Sal of all Employees in the ASC order of AnnSal. |
| SQL> | SELECT Empno, Ename, sal, Sal/30 DailySal
FROM Emp ORDER BY Sal*12 ; |
| |
|
| 9. | Display the empno , ename, job, hiredate, exp of all Mgrs |
| SQL> | select empno, ename, sal,
months_between(sysdate,hiredate)/12 Exp from emp where job = ‘MANAGER’ ; |
| 10. | List the empno, ename, sal, exp of all emps working for Mgr 7839. |
| SQL> | select empno, ename, sal,
months_between(sysdate,hiredate)/12 Exp from emp B where Mgr = 7839 ; |
| |
|
| 11. | Display the details of the emps whose Comm. Is more than their sal. |
| SQL> | select * from emp where comm > sal ; |
| 12. | List the emps in the asc order of Designations |
| SQL> | select * from emp order by job ; |
| 13. | List the emps along with their exp and daily sal is more than Rs.100 |
| SQL> | Select emp.*, months_between(sysdate,hiredate)/12 Exp
from emp where sal/30 > 100 ; |
| |
|
| 14. | List the emps who are either ‘CLERK’ or ‘ANALYST’ in the desc order |
| SQL> | Select * from emp where job in (‘CLERK’,‘ANALYST’) order by job desc ; |
| 15. | List the emps who joined on 1May81,31Dec81, 17Dec81, 19Jan80 in asc order of seniority. |
| SQL> | |
| Select ename,hiredate,months_between(sysdate,hiredate)/12 EXP from emp
where hiredate like ’01-MAY-81′ or hiredate like ’31-DEC-81′ or hiredate like ’17-DEC-81′ or hiredate like ’19-JAN-80′ order by hiredate desc ; |
|
| 16. | List the emps who are working for the deptno 10 or 20 |
| SQL> | Select * from emp where deptno in (10,20) ; |
| |
|
| 17. | List the emps who are joined in the year 1981 |
| SQL> | Select * from emp where hiredate like ‘%81’ ; |
| 18. | List the emps who are joined in the month of Aug 1980 |
| SQL> | Select * from emp where hiredate like %AUG-80’ ; |
| 019. | List the emps whose annul sal ranging from 22000 and 45000 |
| SQL> | Select * from emp where sal*12 between 22000 and 45000 ; |
| 20. | List the emps those are having five characters in their names. |
| SQL> | Select * from emp where ename like ‘_____’ ; |
| 21. | List the enames those are starting with ‘s’ and with fire characters |
| SQL> | Select * from emp where ename like ‘S____’ ; |
| 22. | List the emps those are having four chars and third char must be ‘r’ |
| SQL> | Select * from emp where ename like ‘__R_’ ; |
| 23. | List the 5 character names starting with ‘s’ and ending with ‘h’ |
| SQL> | Select * from emp where ename like ‘S___H’ ; |
| |
|
| 24. | List the emps who joined in January |
| SQL> | Select * from emp where hiredate like ‘%JAN%’ ; |
| 25. | List the emps who joined in the month of which second character is ‘a’ |
| SQL> | Select * from emp where hiredate like ‘__-_A%’ ; |
| 26. | List the emps whose sal is 4 digit number ending with zero |
| SQL> | Select * from emp where sal like ‘___0’ ; |
| 27. | List the emps whose names having a character set ‘ll’ together |
| SQL> | Select * from emp where ename like ‘%LL%’ ; |
| 28. | List the emps those who joined in 80’s |
| SQL> | Select * from emp where hiredate like ‘%80’ ; |
| 29. | List the emps who does not belong to deptno 20 |
| SQL> | Select * from emp where deptno <> 20 ; |
| 30. | List all the emps except ‘president’ & ‘Mgr’ in asc order of salaries |
| SQL> | Select * from emp where job not in (‘PRESIDENT’,’MANAGER’)
order by sal ; |
| |
|
| 31. | List the emps who joined in before or after 1981 |
| SQL> | Select * from emp where hiredate not like ‘%81’ ; |
| 32. | List the emps whose empno not starting with digit 78 |
| SQL> | Select * from emp where empno not like ‘78%’ ; |
| 33. | List the emps who are working under ‘Mgr’ |
| SQL> | Select * from emp where mgr in
(select empno from emp where job = ‘MANAGER’) ; |
| 034. | List the emps who joined in any year but not belongs to the month of March |
| SQL> | Select * from emp where hiredate not like ‘%MAR%’ ; |
| 35. | List all the clerks of deptno 20. |
| SQL> | Select * from emp where deptno=20 and job =’CLERK’ ; |
| 36. | List the emps of deptno 30 or10 joined in the year 1981 |
| SQL> | Select * from emp where deptno in(10,30) and hiredate like ’%81’ ; |
| |
|
| 37. | Display the details of ‘Smith’ |
| SQL> | Select * from emp where ename = ‘SMITH’ ; |
| 38. | Display the location of ‘Smith’ |
| SQL> | Select loc from emp where ename = ‘SMITH’ ; |
| |
|
| 39. | List the total information of emp table along with dname and loc of all the emps working under ‘Accounting’ & ‘Research’ in the asc deptno |
| SQL> | SELECT EMP.*,DNAME,LOC FROM Emp, Dept
WHERE Dname IN (‘ACCOUNTING’,’RESEARCH’) AND EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMP.DEPTNO |
| |
|
| 40. | List the empno, ename, sal, dname of all the ‘Mgrs’ and ‘Analyst’ working in NEWYORK, DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc. |
| SQL> | SELECT EMPNO, ENAME,SAL, dname FROM EMP, DEPT
WHERE LOC IN (‘NEW YORK’,’DALLAS’) AND JOB IN (‘MANAGER’,’ANALYST’) AND MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 > 7 AND COMM IS NULL AND EMP.DEPTNO = DEPT.DEPTNO ORDER BY LOC ; |
| 41. | List the empno, ename, sal, dname, loc, deptno, job of all emps working at CHICAGO or working for ACCOUNTING dept wit ann sal > 28000, but the sal should not be = 3000 or 2800 who doesn’t belongs to the Mgr and whose no is having a digit ‘7’ or ‘8’ in 3rd position in the asc order of deptno and desc order of job. |
| SQL> | SELECT EMPNO, ENAME,JOB,SAL,EMP.DEPTNO, dname, loc FROM EMP, DEPT
WHERE (LOC = ‘CHICAGO’ OR DNAME = ‘ACCOUNTING’) AND SAL*12 > 28000 AND SAL NOT IN(3000,2800) AND MGR IS NULL AND EMPNO LIKE ‘__7_’ OR EMPNO LIKE ‘__8_’ AND EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMP.DEPTNO ASC, JOB DESC ; |
| |
|
| 42. | Display the total information of the emps along with grades in the asc order |
| SQL> | Select emp.*, grade from emp,salgrade where sal between losal and hisal ; |
| 43. | List all the grade2 and grade 3 emps |
| SQL> | Select emp.*, grade from emp,salgrade where grade in(2,3) and
sal between losal and hisal |
| |
|
| 44. | Display all grade 4,5 Analyst and Mgr |
| SQL> | Select emp.*,grade from emp,salgrade where grade in(4,5) and
Job in (‘ANALYST’,’MANAGER’) and sal between losal and hisal ; |
| 45. | List the empno, ename, sal, dname, grade, exp, ann sal of emps working for dept 20 or 10. |
| SQL> | Select empno, ename, sal, sal*12 AnnSal, dname, grade,
months_between(sysdate,hiredate)/12 Exp from emp, dept, salgrade where emp.deptno in(10,20) and emp.deptno=dept.deptno and sal between losal and hisal |
| |
|
| 46. | List all the information of emps with loc and the grade of all the emps belong to the grade ranges from 2 to 4 working at the dept those are not starting with char set ‘OP’ and not ending with ‘S’ with the design having a char ‘a’ any where joined in the year 81 but not in the month of Mar or Sep and sal not end with ‘00’ in the asc order of grades. |
| SQL> | Select emp.*, loc, grade from emp, dept, salgrade where
Grade between 2 and 4 and dname not like ‘OP%’ and dname not like ‘%S’ and Job like ‘%A%’ and (hiredate like ‘%81’ and hiredate not like ‘%MAR%’ and hiredate not like ‘%SEP%’) and sal not like %00 and emp.deptno=dept.deptno and sal between losal and hisal ; |
| |
|
| 47. | List the details of the depts along with empno, ename or without the emps |
| SQL> | Select empno, ename, dept.* from emp,dept where emp.deptno=dept.deptno ; |
| 48. | List the details of the emps whose salaries more than the employee BLAKE |
| SQL> | Using self-join:
select A.* from emp A, emp B where A.sal > B.sal and B.ename = ‘BLAKE’
Using sub-query: select * from emp where sal > (select sal from emp where ename = ‘BLAKE’) |
| 49. | List the details of the emps whose job is same as ALLEN. |
| SQL> | select * from emp where job = (select job from emp where ename = ‘ALLEN’) |
| |
|
| 50. | List the emps who are senior to King |
| SQL> | select * from emp where hiredate < (select hiredate from emp where ename = ‘KING’) |
