Oracle Practice SQL Queries 01:

Oracle Practice SQL Queries 01:


1) Display the name of employees along with their annual salary (sal*12) the name of the employee earning highest annual salary should appear first?

                    SELECT   ename, sal, sal * 12 "Annual Salary"
                        FROM emp
                    ORDER BY "Annual Salary" DESC;

2) Display name, salary, Hra, pf, da, TotalSalary for each employee. The out put should be in the order of total salary, hra 15% of salary, DA 10% of salary .pf 5% salary Total Salary will be (salary+hra+da)-pf?

                    SELECT   ename, sal sa, sal * 0.15 hra, sal * 0.10 da, sal * 5 / 100 pf,
                             sal + (sal * 0.15) + (sal * 0.10) - (sal * .05) totalsalary
                        FROM emp
                    ORDER BY totalsalary DESC;

3) Display Department numbers and total number of employees working in each Department?

                    SELECT   deptno, COUNT (*)
                        FROM emp
                    GROUP BY deptno;

4) Display the various jobs and total number of employees working in each job group?

                    SELECT   job, COUNT (*)
                        FROM emp
                    GROUP BY job;

5) Display department numbers and Total Salary for each Department?

                SELECT   deptno, SUM (sal)
                    FROM emp
                GROUP BY deptno;

6) Display department numbers and Maximum Salary from each Department?

            SELECT   deptno, MAX (sal)
                FROM emp
            GROUP BY deptno;

7) Display various jobs and Total Salary for each job?
   
                SELECT   job, SUM (sal)
                    FROM emp
                GROUP BY job;

8)Display each job along with min of salary being paid in each job group?

                SELECT   job, MIN (sal)
                    FROM emp
                GROUP BY job;
               
9) Display the department Number with more than three employees in each department?

                SELECT   deptno, COUNT (*)
                    FROM emp
                GROUP BY deptno
                  HAVING COUNT (*) > 3;

10) Display various jobs along with total salary for each of the job where total salary is greater than 40000?
                
                SELECT   job, SUM (sal)
                    FROM emp
                GROUP BY job
                  HAVING SUM (sal) > 40000;

For more Please Check  Oracle Practice SQL Queries 02: