View difference between Paste ID: ujUgdYLH and Pr3JsQqJ

 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, jobs j, departments d WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND (e.department_id IS NULL OR e.manager_id IS NULL);
+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;