HR SCHEMA ORACLE

Question

HR SCHEMA ORACLE

Create Function “GET_EMPLOYEE_SUMMARY” that receives an Employee ID and that returns, through an output parameter, a data structure with the following information: - Employee ID - First Name - Last Name - Manager Name - Department Name

So, the manager_name is baseade on employee_id..

Query:

select  

      e.first_name as first_name,
      e.last_name,
      e.employee_id,
      e.manager_id as Name,
      d.department_name

      from employees e 
      Cross join departments d

but i have some mistakes... idk

You need to JOIN employees one more time to get the manager name. — jarlh, Feb 17 '20 at 15:08
but when i do a join, it's not complete. because in table employees, i have employee id = 100 and manager id = 101 ex.., and the first_name of 101 is alex and the 100 is joao, when i return a 100 employee query the name of manager didn't come.. is a number.. i have to relacionate it to a name — Josè Oliviera, Feb 17 '20 at 15:09
@JosèOliviera: to understand the different join types I suggest you read [this question and its answers](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Bob Jarvis - Слава Україні, Feb 17 '20 at 15:25

score 0 · Answer 1 · answered Feb 17 '20 at 15:19

You just need to do a self join with the EMPLOYEE table again and join with the DEPARTMENT table should be inner join.

Something like following is needed:

SELECT
    E.FIRST_NAME   AS FIRST_NAME,
    E.LAST_NAME,
    E.EMPLOYEE_ID,
    M.FIRST_NAME || M.LAST_NAME   AS MANAGER_NAME, -- Change it according to your requirement
    D.DEPARTMENT_NAME
FROM
    EMPLOYEES E
    JOIN EMPLOYEES M ON E.MANAGER_ID = M.EMPLOYEE_ID
    JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE
    E.EMPLOYEE_ID = 100;

Cheers!!

score 0 · Answer 2 · answered Feb 17 '20 at 15:31

Since this looks very much like homework, here is a rough guide and you'll need to fill in the blanks:

The first part of your question is to:

create Function GET_EMPLOYEE_SUMMARY

so you should not be starting by writing a SQL query but should be writing a PL/SQL statement:

CREATE FUNCTION GET_EMPLOYEE_SUMMARY ...

You can look up the complete syntax in the Oracle documentation.

that receives an Employee ID

So one of the arguments to the function should be something like:

i_employee_id IN EMPLOYEE.EMPLOYEE_ID%TYPE

and that returns, through an output parameter, a data structure with the following information ...

So your return type should be a custom type created with a statement like:

CREATE TYPE <your_type_name> AS OBJECT(
  <list_of_properties _and_data_types>
)

and then you can use that return type in your function declaration.

When it comes to write the PL/SQL block for your function then you can include your query which should look something like:

CREATE FUNCTION <your_function_signature>
IS
  <declare your local variables>
BEGIN
  SELECT E.FIRST_NAME,
         E.LAST_NAME,
         M.FIRST_NAME || ' ' || M.LAST_NAME
         D.DEPARTMENT_NAME
  INTO   <your_first_name_variable>,
         <your_last_name_variable>,
         <your_manager_name_variable>,
         <your_department_name_variable>
  FROM   EMPLOYEES E
         LEFT OUTER JOIN EMPLOYEES M
         ON ( E.MANAGER_ID = M.EMPLOYEE_ID )
         INNER JOIN DEPARTMENTS D
         ON ( D.DEPARTMENT_ID = E.DEPARTMENT_ID )
  WHERE  E.EMPLOYEE_ID = i_employee_id;

  <do stuff>

  RETURN <your_custom_type>
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    <handle the exception>
END;
/

thx bro, in sql (querys) i understand a little, but pl-sql i have very dificults. — Josè Oliviera, Feb 18 '20 at 09:46
it's not my homework.. it's just a tutorial to see if i go with this view ( developer) or not in company... i like development, but pl-sql it's so dificult — Josè Oliviera, Feb 18 '20 at 09:47

HR SCHEMA ORACLE

2 Answers2