SELECT department_id, last_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees WHERE department_id IS NOT NULL ORDER BY department_id, salary DESC; Find the salary difference between each employee and the next highest paid employee in the same department.
SELECT e.first_name, e.last_name, e.salary, e.department_id FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e.department_id) ORDER BY e.department_id, e.salary DESC; Problem 5: Fetch the top 5 highest paid employees, but show ties (i.e., if the 5th highest salary is shared by 3 people, show all of them).
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. oracle 12c sql hands-on assignments solutions
SELECT first_name, last_name, hire_date, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS years, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hire_date), 12)) AS months, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) || ' years, ' || TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, hire_date), 12)) || ' months' AS tenure FROM employees; Mask email addresses for a report (Show first 2 letters, then ' ** ', then the domain 'oracle.com').
12 minutes Introduction Oracle 12c introduced several game-changing features, such as Top-N Row limiting (the FETCH FIRST clause) and improved Visibility into Partitioned Tables . However, the core of database mastery still lies in solving real-world problems. Show rank, dense rank, and row number
CREATE TABLE emp_analytics AS SELECT department_id, last_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees; Oracle 12c SQL is robust, but the secret to passing hands-on assignments is understanding the subtle differences in windowing functions and the modern Top-N syntax . Always test your OFFSET/FETCH logic on a small subset first to verify sorting order.
SELECT product_id, product_name, list_price FROM oe.product_information WHERE list_price BETWEEN 50 AND 200 AND UPPER(product_name) NOT LIKE '%MONITOR%'; Problem 3: Show the department name, city, and the number of employees working in that department. Include departments with zero employees. However, the core of database mastery still lies
Oracle 12c, SQL, Assignments, PL/SQL, Window Functions