bd lab 25.11.2018

--lab 6. fara divison nu ne trbuie
--ex3
SELECT emp.last_name, emp.employee_id, COUNT(start_date)
FROM employees emp
LEFT JOIN job_history jh ON emp.employee_id = jh.employee_id
GROUP BY emp.employee_id, emp.last_name
HAVING COUNT(start_date) > 1;

--ex4
SELECT country_name, COUNT(employee_id)
FROM employees
JOIN departments USING (department_id)
JOIN locations  USING (location_id)
RIGHT JOIN countries  USING  (country_id)
GROUP BY country_name;

--ex5
SELECT emp.employee_id, emp.last_name, COUNT(prj.project_id)
FROM employees emp
JOIN works_on wk ON emp.employee_id = wk.employee_id
JOIN project prj ON wk.project_id = prj.project_id
WHERE delivery_date > deadline
GROUP BY emp.employee_id, last_name
HAVING COUNT(prj.project_id) > 1;

--ex6
SELECT employee_id, project_id
FROM project JOIN works_on
USING (project_id)
RIGHT JOIN employees USING (employee_id)
ORDER BY employee_id;

--ex7
SELECT employee_id, last_name
FROM employees
WHERE department_id  IN ( --punem not in aici pt ex8
      SELECT department_id FROM employees
      WHERE employee_id IN (
            SELECT project_manager FROM project
            )
        );

--ex9
SELECT department_id, avg(salary)
FROM employees
GROUP BY department_id
HAVING avg(salary) > &medie1;

--ex16
SELECT department_id, last_name, salary * 12
FROM employees
WHERE LOWER(job_id) = '&job';

--ex17
SELECT last_name, department_id, salary * 12, hire_date
FROM employees
WHERE hire_date > to_date('&data','dd-mm-yyyy');

--ex18
SELECT &coloana
FROM &tabel
WHERE &conditie
ORDER BY &coloana;

--lab7
--1
INSERT INTO jobs
VALUES ('job_id', 'job_title', 1000, 7000);
--rolback (ca sa inchid sesiunea)
ROLLBACK;