SELECT d.department_name, l.city, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id LEFT JOIN locations l ON d.location_id = l.location_id GROUP BY d.department_name, l.city ORDER BY employee_count DESC; List employees who earn more than the average salary of their own department.
SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS WITH TIES; Note: Without WITH TIES , it returns exactly 5 rows. With WITH TIES , it respects the salary rank. oracle 12c sql hands-on assignments solutions
SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2012 ORDER BY hire_date ASC; You can also use the TO_CHAR method: SELECT d
SELECT email, SUBSTR(email, 1, 2) || '****@oracle.com' AS masked_email FROM employees; Problem 9: Rank employees within each department by salary. Show rank, dense rank, and row number. and row number. WHERE TO_CHAR(hire_date
WHERE TO_CHAR(hire_date, 'YYYY') = '2012'; Find all products in the oe.product_information table whose list price is between $50 and $200, but exclude products where the product name contains the word 'Monitor'.