Use HR;

CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
					 employee_id INTEGER FOREIGN KEY REFERENCES employees (employee_id),
					 order_details VARCHAR(300));

ALTER TABLE orders ADD order_date datetime;

select * from employees;

drop table orders;

UPDATE employees SET salary = 1000 WHERE salary < 900;

SELECT COUNT(*) FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 60);

WITH tab (count1, employee_id) AS (SELECT COUNT(*), employee_id FROM job_history GROUP BY employee_id HAVING COUNT(*) >= 2) SELECT e.last_name FROM employees e, tab t WHERE t.employee_id = e.employee_id;

SELECT COUNT(*) FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

SELECT COUNT(*), department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 20;

SELECT e.first_name, e.last_name, j.job_title, d.department_name FROM employees e LEFT OUTER JOIN jobs AS j ON e.job_id = j.job_id LEFT OUTER JOIN departments AS d ON e.department_id  = d.department_id WHERE (e.department_id IS NULL OR e.manager_id IS NULL);



WITH  tab1 (department_id, count1, job_id) AS (SELECT department_id, COUNT(*), job_id FROM employees GROUP BY department_id, job_id), tab2 (last_name, rank1) AS (SELECT last_name, rank() over(PARTITION BY department_id, job_id ORDER BY hire_date, salary) FROM employees WHERE department_id IN (SELECT department_id FROM tab1 WHERE count1 > 1 AND department_id IS NOT NULL) AND job_id IN (SELECT job_id FROM tab1 WHERE count1 > 1 AND department_id IS NOT NULL)) SELECT last_name FROM tab2 WHERE rank1 = 1;