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.
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. oracle 12c sql hands-on assignments solutions
Oracle 12c, SQL, Assignments, PL/SQL, Window Functions However, the core of database mastery still lies
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. Write a query to page through employee data,
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').
Write a query to page through employee data, showing rows 21 to 30 (Offset 20, Fetch 10).
Oracle 12c SQL: Step-by-Step Solutions to Hands-On Assignments (Employee & Sales Schema)