Help ORACLE Queries

Question

Help ORACLE Queries

Navigation

#1 by (1 votes)

1

List the department, the total salary of the departments that the sum of their salary is greater than 20000.

SELECT D.DEPARTMENT_NAME,SUM(E.SALARY) FROM DEPARTMENTS DINNER JOIN EMPLOYEES E ON D.DEPARTMENT_ID=E.DEPARTMENT_ID GROUP BY D.DEPARTMENT_NAME;

The problem with the first query is how can I put the total salary of each department in the WHERE so that it is greater than 20000

Create a select that presents the id of the position, the description of the position, the number of employees for departments that have 4 employees.

SELECT J.JOB_ID,J.JOB_TITLE FROM JOBS J INNER JOIN EMPLOYEES E ON J.JOB_ID=E.JOB_ID;

The problem with the second query is how do I get the number of employees for the departments that have more than 4 employees

Display a select where I displayed the positions where the average salary is greater than 10000.

SELECT E.JOB_ID,AVG(E.SALARY) FROM EMPLOYEES E GROUP BY JOB_ID;

The problem with the third query is like the problem of the first query as I put the average of the wages in the WHERE and that this is greater than 10,000

In query 1 and 3 when I try to do MAX and AVG in WHERE I get an error and try to do it in SELECT but% WHERE does not read ALIAS

sql oracle plsql

asked by JJsCR 20.06.2018 в 16:35

source

1 answer

How to pass data from a controller to a module included with @include Laravel Blade? Requests not to enter the @RequestMapping in Spring

score 1 · Accepted Answer

Right now I can not test it but I think it would be something like this:

You must add a superior query to be able to discriminate by the sum of salaries. I also take the opportunity to correct the DINNER that I guess you got: P:

SELECT * 
FROM (
    SELECT 
        D.DEPARTMENT_NAME,
        SUM(E.SALARY) AS SALARY 
    FROM DEPARTMENTS D 
        INNER JOIN EMPLOYEES E ON D.DEPARTMENT_ID=E.DEPARTMENT_ID 
    GROUP BY D.DEPARTMENT_NAME) 
WHERE SALARY > 20000

This case will be similar to the previous one, but we must add another subquery to calculate the total number of employees by department.

SELECT *
FROM (
    SELECT 
        J.JOB_ID,
        J.JOB_TITLE,
        (SELECT COUNT(1)
         FROM EMPLOYEES E2
         WHERE E2.DEPARTMENT_ID = E.DEPARTMENT_ID) AS EMPLOYEES_BY_DEP
    FROM JOBS J 
        INNER JOIN EMPLOYEES E ON J.JOB_ID=E.JOB_ID)
WHERE EMPLOYEES_BY_DEP > 4

More of the same, as in case 1.