--exercitiul 11
SELECT Manager_Id
FROM Employees
GROUP BY Manager_Id
HAVING COUNT(employee_id)>=2;

--exercitiul 12
SELECT DISTINCT Location_Id
FROM departments;

--exercitiul 13
SELECT d.Department_Id
FROM Departments D
WHERE NOT EXISTS(
SELECT E.Employee_Id
FROM Employees E
WHERE E.Department_Id=D.Department_Id);

--exercitiul 14 sau cu join
SELECT e.Employee_Id, e.Last_Name, e.Hire_Date, e.Salary, e.Manager_id
FROM Employees E
WHERE E.Manager_Id=(
SELECT Ee.Employee_Id
FROM Employees Ee
WHERE LOWER(Ee.Last_Name) LIKE 'de haan');

SELECT E.Employee_Id, E.Last_Name, E.Hire_Date, E.Salary, E.Manager_Id
FROM Employees E
Join Employees Dehan
ON(e.manager_id=dehan.employee_id AND LOWER(dehan.Last_Name) LIKE 'de haan');

--b
SELECT E.Employee_Id, E.Last_Name, E.Hire_Date, E.Salary, E.Manager_Id, LEVEL
FROM Employees E
START WITH Employee_Id =(
SELECT Ee.Employee_Id
FROM Employees Ee
WHERE LOWER(ee.last_name) LIKE 'de haan'
)
CONNECT BY Manager_Id = PRIOR Employee_Id;

--exercitiul 15
SELECT E.Employee_Id, E.Last_Name, E.Hire_Date, E.Salary, E.Manager_Id, LEVEL
FROM Employees E
START WITH Employee_Id=114
CONNECT BY manager_id = PRIOR employee_id;

--exercitiul 16
SELECT E.Employee_Id, E.Manager_Id, E.Last_Name, LEVEL
FROM Employees E
WHERE LEVEL=3
START WITH Employee_Id =(
SELECT Ee.Employee_Id
FROM Employees Ee
WHERE LOWER(ee.last_name) LIKE 'de haan'
)
CONNECT BY Manager_Id = PRIOR Employee_Id;

--exercitiul 17
SELECT E.Employee_Id, E.Manager_Id, LEVEL, E.Last_Name
FROM Employees E
--Start With Employee_Id=E.Employee_Id --pot sa scot linia
CONNECT BY PRIOR Manager_Id =  Employee_Id;

--exercitiul 18
SELECT E.Employee_Id, E.Manager_Id, LEVEL, E.Last_Name
FROM Employees E
WHERE e.salary>5000
START WITH Employee_Id=(
SELECT Ee.Employee_Id
FROM Employees Ee
WHERE Ee.Salary =(
SELECT MAX(Eee.Salary)
FROM employees eee
)
)
CONNECT BY Manager_Id =  PRIOR Employee_Id;

--exercitiul 19
WITH Depsal AS(
SELECT SUM(Salary) suma, department_id idDep
FROM Employees
GROUP BY Department_Id),
Avgsalariu AS(
SELECT AVG(SUM(suma)) Avgsal
FROM Depsal
)
SELECT department_name, suma
FROM Departments
Join Depsal
USING(department_id)
WHERE suma>(
SELECT Avgsal
FROM Avgsalariu);

--exercitiul 20
WITH directi AS(
SELECT Employee_Id, hire_date
FROM Employees
WHERE Manager_Id=(
SELECT Employee_Id
FROM Employees
WHERE LOWER(first_name||last_name)='stevenking'
)
),
Direct_Vechi AS(
SELECT Employee_Id
FROM Directi
WHERE Hire_Date=(
SELECT MIN(Hire_Date)
FROM directi
)
)
SELECT Employee_Id, First_Name||' '||Last_Name, Job_Id, Hire_Date
FROM Employees
WHERE TO_CHAR(Hire_Date,'YYYY')!='1970'
START WITH Employee_Id IN (
SELECT Employee_Id
FROM Direct_Vechi
)
CONNECT BY PRIOR Employee_Id = Manager_Id;

--exercitiul 21
SELECT Last_Name
FROM (
SELECT * FROM employees ORDER BY salary DESC
)
WHERE Rownum<=10;

--exercitiul 22
WITH Medie AS(
SELECT AVG(Salary),job_id
FROM Employees
GROUP BY Job_Id
ORDER BY 1 ASC
)
SELECT *
FROM Medie
WHERE rownum<=3;