Practice set of question on SQL.

1.   

Find the details of highest paid employee?

select ename, job, sal, deptno from emp where sal = (select max(sal) from emp);

2.  List the employees  whose job is same as ‘ALLEN’,Or,‘SMITH’?

SELECT * FROM EMPLOYEE WHERE JOB = (SELECT JOB FROM EMPLOYEE WHERE ENAME =  'ALLEN') OR JOB =(SELECT JOB FROM EMPLOYEE WHERE ENAME = 'SMITH');

Q3 get the employees with the top salary with department = 20 and job = clerk.

SELECT * FROM EMPLOYEE  WHERE DEPTNO = '20' AND JOB = 'CLERK' ORDER BY SAL DESC;

Q4. List the employees who are senior to most recently hired employee and is working under manager,‘KING’.

select * from employee where hiredate < (select max(hiredate) from employee where mgr in (select empno from employee where ename = 'KING')) ;

Q5. Show all the different department id along with number of employee work in that department. Display only department in which more than four employee are present.

select deptno, count(empno) from employee group by deptno having count(*) >= 4;

  

Q6. List,job,`title,and,number,of,employee,who,are,doing,that,job.,

select  deptno ,job ,count(*)  from emp group by  deptno,job;

Q7. Determine,the,highest,paid,employee,under,each,job,`tle.,

select * from emp where sal in (select max(sal) from emp group by job) order by sal desc;

Q8. Display the ename,job,sal for the employees whose sal is greater than the maximum,avg(sal) by deptno.

SELECT ENAME,JOB,SAL FROM EMPLOYEE WHERE SAL>(SELECT MAX(SAL)FROM EMPLOYEE);

9. Find,the,employee,whose,salary,is,greater,than,average,salary.

SELECT ENAME FROM EMPLOYEE WHERE SAL> (SELECT AVG(SAL)FROM EMPLOYEE;