Interview Questions
Oracle SQL Interview Questions and Answers
Oracle-specific interview questions usually center on PL/SQL and features that differ from standard/MySQL syntax -- sequences instead of auto-increment, analytic functions, and Oracle's particular NULL-handling quirks.
Example: A PL/SQL block using a cursor
PL/SQLDECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary FROM employees WHERE department_id = 10;
v_id employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ' || v_id || ': ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;Frequently Asked Questions
Oracle traditionally uses a SEQUENCE object (CREATE SEQUENCE emp_seq; then emp_seq.NEXTVAL to get the next value), independent of any table, rather than an auto-increment column attribute like MySQL's AUTO_INCREMENT. Since Oracle 12c, IDENTITY columns (similar to MySQL's approach) are also supported, but sequences remain very common, especially in older schemas.
Oracle's procedural extension to SQL -- it adds variables, loops, conditionals, and exception handling around SQL statements, letting you write stored procedures, functions, and triggers directly in the database. It's Oracle-specific, unlike standard SQL.
ROWID is a physical address uniquely identifying a row's exact storage location -- it's stable and can be used to fetch that exact row very quickly. ROWNUM is a pseudo-column representing a row's position in the result set as Oracle returns it -- it's assigned before ORDER BY is applied, which is a very common source of bugs when developers assume ROWNUM reflects final sort order.
Functions like RANK(), ROW_NUMBER(), and SUM() OVER (PARTITION BY ...) compute a value across a set of related rows without collapsing them into a single row the way GROUP BY does -- useful for things like "rank each employee's salary within their department" while still returning one row per employee.
Oracle treats an empty string ('') as NULL for VARCHAR2 columns -- unlike most other databases, where '' and NULL are distinct values. This is a well-known gotcha when porting SQL between Oracle and other database systems, since WHERE column = '' behaves very differently (it never matches, since NULL never equals anything with =).
A function must return a single value and can be used directly inside a SQL statement (e.g. SELECT my_function(col) FROM table). A procedure doesn't have to return a value, can return multiple values via OUT parameters, and is called as a standalone statement rather than embedded in a query.
It combines an INSERT and an UPDATE into a single statement -- commonly called an "upsert." MERGE INTO target USING source ON (match condition) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... -- useful for syncing a table from a staging table without writing separate insert/update logic.