Interview: Oracle Hackerrank

SELECT employee_id, salary, department_id FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id ); Task: Write a procedure to increase salary by 10% for employees hired before 2015.

SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept FROM employees; Task: Find employees earning more than their managers. Oracle Hackerrank Interview

Here’s a helpful guide for an (typically for database developer, PL/SQL developer, or data engineer roles). It focuses on SQL and PL/SQL topics Oracle often tests. 🔹 1. Know the Typical Oracle HackerRank Question Types | Category | Common Tasks | |----------|----------------| | Basic SQL | SELECT , WHERE , ORDER BY , GROUP BY , HAVING | | Joins | INNER , LEFT , RIGHT , FULL , SELF JOIN , CROSS JOIN | | Subqueries | Correlated vs non-correlated, EXISTS , IN , ANY , ALL | | Set Operators | UNION , UNION ALL , INTERSECT , MINUS | | Aggregation | COUNT , SUM , AVG , MIN , MAX , RANK , DENSE_RANK , ROW_NUMBER | | String Functions | SUBSTR , INSTR , REPLACE , TRIM , UPPER , LOWER | | Date Functions | SYSDATE , TO_DATE , EXTRACT , ADD_MONTHS , MONTHS_BETWEEN | | Conditional Logic | CASE , DECODE , NVL , COALESCE , NULLIF | | PL/SQL | Blocks, loops, cursors, exception handling, procedures, functions | | Performance | Indexes, EXPLAIN PLAN , avoiding SELECT * | 🔹 2. Sample Oracle HackerRank-Style Questions ✅ Easy – Basic Filtering & Sorting Task: List employees with salary > 5000, sorted by hire date (newest first). It focuses on SQL and PL/SQL topics Oracle often tests

SELECT employee_id, first_name, salary, hire_date FROM employees WHERE salary > 5000 ORDER BY hire_date DESC; Task: Rank employees by salary within each department. Sample Oracle HackerRank-Style Questions ✅ Easy – Basic

SELECT e.employee_id, e.salary, m.salary AS manager_salary FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary; Task: Find employees with salary above department average.