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 total salary for each of the jobs where 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 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;
51) Display the name of the employee who earns highest salary.
SQL>select ename from emp where sal=(select max(sal) from emp);
52) Display the employee number and name for employee working as clerk and earning highest salary among clerks.
SQL>select empno,ename from emp where where job=’CLERK’ and sal=(select max(sal) from emp where job=’CLERK’);
53) Display the names of salesman who earns a salary more than the highest salary of any clerk.
SQL>select ename,sal from emp where job=’SALESMAN’ and sal>(select max(sal) from emp where job=’CLERK’);
54) Display the names of clerks who earn a salary more than the lowest salary of any salesman.
SQL>select ename from emp where job=’CLERK’ and sal>(select min(sal) from emp where job=’SALESMAN’);
55) Display the names of the employees who earn highest salary in their respective departments.
SQL>select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);
56) Display the names of the employees who earn highest salaries in their respective job groups.
SQL>select ename,sal,job from emp where sal in(select max(sal) from emp group by job);
57) Display the employee names who are working in accounting department.
SQL>select ename from emp where deptno=(select deptno from dept where dname=’ACCOUNTING’);
58) Display the employee names who are working in Ahmedabad .
SQL>select ename from emp where deptno=(select deptno from dept where LOC=’Ahmedabad’);
59) Display the Job groups having total salary greater than the maximum salary for managers.
SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM EMP WHERE JOB=’MANAGER’);
60) Display the names of employees from department number 10 with salary greater than that of any employee working in other department.
SQL>select ename from emp where deptno=10 and sal>any(select sal from emp where deptno not in 10);
From my experience, learning SQL through practical interview queries is one of the most effective ways to build strong database skills. These examples help clarify important concepts like GROUP BY, HAVING, and subqueries, which are frequently used in real-world applications and technical interviews. I have found that regularly practicing such problems improves both accuracy and confidence when writing queries under pressure. At the same time, while managing technical learning and academic tasks, I often also think about structured writing requirements such as how many references should a dissertation have, since both demand clarity, organization, and proper support for better results.
ReplyDelete