SQL : How do i i get a department name and avg salary of departments (Oracle server) using PL/SQL bu-CodePudding

So basically i have an assignment. and in the assignment i have to print out the name of the department and its avg salary. now the twist is, i have to use PL/SQL and i have to use a single select. so a subquery essentially. but i got stuck on how should i do it and if anyone could help me that would be awesome !

here is the code :

DECLARE
    v_dep_name varchar2 (30);
    v_salay number (11,2);
BEGIN
    SELECT department_name,AVG(salary)
    INTO v_dep_name,v_salay
    FROM  departments NATURAL JOIN employees 
    WHERE department_id=(
        SELECT department_id
        FROM departments
        WHERE department_id=50)
    GROUP BY salary;
    DBMS_OUTPUT.PUT_LINE ('The average salary of the department '||v_dep_name || ' is : ' || v_salay);
    END;
/

CodePudding user response：

As per your code, You don't need a subquery. The correct code should look alike -

DECLARE
    v_dep_name varchar2 (30);
    v_salay number (11,2);
BEGIN
    SELECT department_name,AVG(salary)
      INTO v_dep_name,v_salay
      FROM departments 
   NATURAL JOIN employees 
     WHERE department_id=50
     GROUP BY department_name;       -- This should be department_name instead of salary.

    DBMS_OUTPUT.PUT_LINE ('The average salary of the department '||v_dep_name || ' is : ' || v_salay);
END;
/