Interview Questions On Sql And Plsql

The tech industry's reliance on data has made SQL and PL/SQL proficiency a non-negotiable skill for database professionals, developers, and data analysts alike. Mastery of these topics demonstrates a candidate's readiness to handle real-world challenges such as optimizing slow queries, designing strong stored procedures, and implementing business rules through triggers. Because of that, when recruiters compile a list of SQL and PL/SQL interview questions, they aim to assess not just rote memorization, but the ability to think logically about data manipulation, transaction control, and procedural logic within Oracle environments. This article dives deep into the most frequently asked and highly relevant SQL and PL/SQL interview questions, offering structured insights that help both interviewers and interviewees deal with the conversation with confidence and clarity.

Foundational SQL Concepts Often Tested

A strong candidate should feel comfortable discussing the core building blocks of SQL before moving into Oracle-specific extensions. Questions typically start with data retrieval, filtering, and joining tables, as these form the basis of any database interaction Worth keeping that in mind. Which is the point..

• What is the difference between INNER JOIN and OUTER JOIN? An INNER JOIN returns only the rows that have matching values in both tables, effectively filtering out non-matching records. An OUTER JOIN, by contrast, preserves unmatched rows from one or both tables. A LEFT OUTER JOIN keeps all records from the left table and matched rows from the right, while a RIGHT OUTER JOIN does the opposite. A FULL OUTER JOIN combines both behaviors, returning all records when there is a match in either table, with NULLs filling gaps where no match exists.

• How does the WHERE clause differ from the HAVING clause? The WHERE clause filters rows before any grouping occurs, operating on individual row-level data. The HAVING clause is used with the GROUP BY function to filter groups after aggregation has taken place. Using WHERE before grouping can improve performance by reducing the row set early, whereas HAVING operates on the result set of aggregate functions like SUM, COUNT, or AVG.

• Explain the purpose of indexing and its impact on query performance. Indexes are database objects that improve the speed of data retrieval operations on a table at the cost of additional storage and slightly slower INSERT, UPDATE, and DELETE operations. A well-designed index allows the database engine to find data without scanning every row, much like an index in a book. On the flip side, over-indexing can lead to maintenance overhead and may even degrade performance during write-heavy operations And it works..

• What are window functions, and how do they differ from aggregate functions? Window functions perform calculations across a set of table rows related to the current row, without collapsing them into a single output row. Unlike aggregate functions, which return one result per group and lose detail about individual rows, window functions retain the original row structure while adding computed columns. Common examples include ROW_NUMBER(), RANK(), SUM() OVER(), and LEAD()/LAG() for accessing subsequent or preceding rows Easy to understand, harder to ignore..

PL/SQL Deep Dive: Procedures, Functions, Triggers, and Cursors

Moving beyond standard SQL, PL/SQL adds procedural capabilities to Oracle databases, enabling developers to write logical blocks of code, handle exceptions, and automate database actions Not complicated — just consistent..

• What is the difference between a stored procedure and a function? A stored procedure is a reusable block of code that performs a specific task and can return zero or multiple values, often used for actions like INSERT, UPDATE, or DELETE operations. It does not necessarily return a value and can be called independently. A function, however, must return a single value and is typically used for calculations or transformations. Functions can be called from SQL queries, whereas procedures cannot be directly invoked within a SELECT statement.

• How do you handle exceptions in PL/SQL? Exception handling in PL

How do you handle exceptions in PL/SQL?
PL/SQL provides a solid exception‑handling framework that lets you gracefully manage runtime errors without aborting an entire batch of work.

• Predefined exceptions – Oracle supplies a set of built‑in exceptions (e.g., NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_NUMBER). They are automatically raised by the database engine when specific conditions occur, so you can catch them without writing custom logic.

• User‑defined exceptions – You can declare your own exceptions using the DECLARE section of a PL/SQL block:

  my_app_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(my_app_exception, -20001);  -- associate with an error code
  

  This lets you raise a custom error with a clear message using RAISE my_app_exception; Simple, but easy to overlook..

• Exception block syntax – The classic EXCEPTION block follows the BEGIN … END structure:

  BEGIN
      -- business logic that may raise errors
      INSERT INTO accounts (id, balance) VALUES (101, 500);
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('Customer not found – defaulting balance to 0.- **Propagation** – If you want an error to bubble up to the caller (e.g.Because of that, - **SQL%ROWCOUNT** – Inside an exception handler you can check `SQL%ROWCOUNT` to see whether any rows were affected before the error occurred, which is useful for logging or rollback decisions. Practically speaking, pUT_LINE('Unexpected error: '||SQLERRM);
          RAISE;   -- re‑raise the error if you want it to propagate
  END;
  

• PRAGMA EXCEPTION_INIT – This directive links a user‑defined exception to a specific Oracle error number, giving you fine‑grained control over when to raise it. , an application tier), simply re‑raise it with RAISE; or RAISE my_app_exception;. In real terms, '); WHEN OTHERS THEN DBMS_OUTPUT. This preserves the original error stack for debugging.

Triggers: Automating Actions in Response to Data Changes

A trigger is a PL/SQL block that automatically executes when a specified data‑ manipulation event occurs on a table or view. Triggers are invaluable for enforcing business rules, maintaining audit trails, and synchronizing related data.

Example – Auditing changes

CREATE OR REPLACE TRIGGER audit_emp_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO emp_audit (emp_id, action, timestamp, user_name)
    VALUES (
        NVL(:NEW.employee_id, :OLD.employee_id),
        CASE
            WHEN INSERTING THEN 'INSERT'
            WHEN UPDATING THEN 'UPDATE'
            WHEN DELETING THEN 'DELETE'
        END,
        SYSTIMESTAMP,
        SYS_CONTEXT('USERENV','SESSION_USER')
    );
END;
/

The trigger fires for every row modified, capturing the action, timestamp, and the user who performed the change. This provides a simple yet effective audit trail without application code.

Cursors: Navigating Result Sets

Cursors are database handles that allow you to iterate over a set of rows returned by a query. PL/SQL distinguishes between implicit cursors (the default for single‑row SELECT statements) and explicit cursors (which you declare, open, fetch, and close manually).

Implicit Cursors

• Automatically created for SELECT … INTO, UPDATE … WHERE CURRENT OF, and DELETE … WHERE.
• Attributes: SQL%ROWCOUNT, SQL%NOTFOUND, SQL%FOUND, SQL%ISOPEN.
• Ideal for simple, one‑off operations.

Explicit Cursors

DECLARE
    CURSOR emp_cur IS
        SELECT employee_id, last_name, salary
        FROM employees
        WHERE department

The trigger fires for every row modified, capturing the action, timestamp, and the user who performed the change. This provides a simple yet effective audit trail without application code.

---

### Cursors: Navigating Result Sets  

PL/SQL gives developers two main ways to work with result sets: implicit cursors, which appear automatically for single‑row queries, and explicit cursors, which must be declared, opened, fetched, and finally closed. Understanding how they differ—and how to use them efficiently—can make a substantial difference in query performance and maintainability.

#### Implicit Cursors  
- Created behind the scenes for statements such as `SELECT … INTO`, `UPDATE … WHERE CURRENT OF`, and `DELETE … WHERE`.  
- Their state is exposed through attributes like `SQL%ROWCOUNT`, `SQL%NOTFOUND`, `SQL%FOUND`, and `SQL%ISOPEN`.  
- Because they are managed by the engine, implicit cursors are lightweight and ideal for short‑lived operations where you only need the first match.

#### Explicit Cursors  
```sql
DECLARE
    CURSOR emp_cur IS
        SELECT employee_id, last_name, salary
        FROM employees
        WHERE department = 'Sales'               -- filter to reduce rows
          AND salary > 50000                   -- additional predicate
        ORDER BY salary DESC;                  -- deterministic order
BEGIN
    OPEN emp_cur;                               -- obtain a handle
    LOOP
        FETCH emp_cur BULK COLLECT INTO tmp_emp; -- load into memory
        EXIT WHEN NOT FETCHES_EMPTY;             -- stop when empty
        FOR r IN tmp_emp LOOP                     -- process each record
            DBMS_OUTPUT.PUT_LINE(r.employee_id || ', ' || r.last_name);
        END LOOP;
    END LOOP;
    CLOSE emp_cur;                               -- release resources
END;
/

Key points illustrated above:

• Bulk collect (BULK COLLECT) pulls the entire result set into a temporary table variable, which dramatically reduces I/O overhead compared with fetching row‑by‑row.
• ORDER BY inside the cursor definition guarantees a consistent processing sequence, useful for reporting or pagination logic.
• EXIT WHEN NOT FETCHES_EMPTY (or checking SQL%ROWCOUNT) lets you break out cleanly once the loop has exhausted its source.

Explicit cursors also give you fine‑grained control over fetching strategies: you can fetch a single row at a time with FETCH NEXT ... if you need streaming behavior, or you can combine multiple cursors in parallel using PARALLEL FOR constructs for heavy‑weight transformations.

Performance Tips & Alternatives

1. Avoid Nested Loops – If your inner operation touches many rows, consider using hash joins or materialized views instead of looping over each outer row.
2. Use Refcursor with Subqueries – For rows that belong to sub‑sets defined by correlated predicates, a refcursor can replace a full scan of the parent table.
3. Batch Updates – When you must modify many rows based on trigger events, batch the updates in a single UPDATE statement rather than issuing individual UPDATE … RETURNING commands.
4. Parameterize Cursor Predicates – Pass filter expressions as bind variables; this allows the optimizer to reuse execution plans across runs.

Putting Triggers and Cursors Together

In practice, you’ll often combine these concepts. A common pattern is to fire a trigger after a bulk load, then run a cursor to populate summary statistics that were built during the load. To give you an idea, after inserting a large batch of orders, you might create a trigger that logs each insertion, followed by a cursor that aggregates total revenue per product category:

Counterintuitive, but true Less friction, more output..

CREATE OR REPLACE TRIGGER aggregate_revenue
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- Simple aggregation; could be moved to a separate function for clarity
    INSERT INTO revenue_summary (product_category, total_amount)
    VALUES (SUBSTR(:NEW.product_category,1,1000), SUM(SYS_NULLIF(amount,-0)))
    ON CONFLICT (product_category) DO UPDATE SET
        total_amount = EXCLUDED.total_amount + COALESCE(EXCLUDED.total_amount,0);
END;
/

A subsequent PL/SQL block can then read the populated revenue_summary table via an explicit cursor, applying any downstream business rules or generating reports And that's really what it comes down to..

Conclusion

Triggers and cursors are complementary tools in PL/SQL development. Triggers let you react instantly to data modifications, preserving the original error context while enabling auditing, validation, and side‑effects without cl

intervening in the main application flow. Cursors, whether implicit or explicit, provide structured access to result sets, allowing developers to process rows individually or in batches with precise control over memory usage and execution timing.

The key to mastering these constructs lies in understanding their trade-offs. In real terms, triggers offer convenience and consistency but can introduce hidden complexity and performance overhead if overused. Cursors provide flexibility and control but require careful management of resources and state. By following best practices—such as keeping triggers lightweight, using explicit cursors for complex logic, and leveraging bulk operations where appropriate—you can build dependable, maintainable database applications that scale effectively.

Short version: it depends. Long version — keep reading.

In the long run, the choice between triggers and cursors should be driven by your specific use case: use triggers for automatic, event-driven responses, and cursors for controlled, procedural data processing. When combined thoughtfully, they form a powerful foundation for sophisticated database programming.