Top SQL Interview Examples for Experienced Developer

1. How do you use a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SQL statement. It simplifies complex queries by breaking them into readable parts.


WITH EmployeeCTE AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT * FROM EmployeeCTE;

2. How do you use analytical functions like RANK()?

Analytical functions like RANK() allow you to perform calculations across a set of rows related to the current row, without collapsing the result set.


SELECT name,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

3. How do you create and use a sequence for auto-incrementing IDs?

A sequence is a database object that generates unique numeric values, commonly used for auto-incrementing primary keys.


CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

INSERT INTO employees (id, name)
VALUES (emp_seq.NEXTVAL, 'Jane Doe');

4. How do you use the MERGE statement for upsert operations?

The MERGE statement combines INSERT and UPDATE logic, allowing you to synchronize two tables efficiently.


MERGE INTO employees e
USING (SELECT 101 AS id, 'John Doe' AS name FROM dual) src
ON (e.id = src.id)
WHEN MATCHED THEN
    UPDATE SET e.name = src.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (src.id, src.name);

5. How do you use hierarchical queries with CONNECT BY?

Hierarchical queries retrieve data organized in parent-child relationships, useful for organizational charts or category trees.


SELECT employee_id,
       manager_id,
       LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

6. How do you use the CASE statement for conditional logic?

The CASE statement enables conditional logic within SQL queries, similar to if-else statements in programming.


SELECT name,
       CASE WHEN salary > 50000 THEN 'High'
            ELSE 'Low'
       END AS salary_category
FROM employees;

7. How do you use the NVL() function to handle NULL values?

NVL() replaces NULL values with a specified default, ensuring consistent output and avoiding null-related errors.


SELECT name,
       NVL(salary, 0) AS salary
FROM employees;

8. How do you use the LISTAGG() function for string aggregation?

LISTAGG() aggregates string values from multiple rows into a single, comma-separated string, useful for reporting and summaries.


SELECT department_id,
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS employees
FROM employees
GROUP BY department_id;

9. How do you use the REGEXP_LIKE() function for pattern matching?

REGEXP_LIKE() allows advanced pattern matching using regular expressions, ideal for filtering data based on string patterns.


SELECT name
FROM employees
WHERE REGEXP_LIKE(name, '^J.');

10. How do you use the DBMS_SCHEDULER package to schedule jobs?

DBMS_SCHEDULER is an Oracle package that lets you automate tasks like backups, reports, or batch processing by scheduling jobs.


BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'daily_backup',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN backup_procedure(); END;',
        start_date      => SYSDATE,
        repeat_interval => 'FREQ=DAILY; INTERVAL=1',
        enabled         => TRUE
    );
END;

Search

Categories

Recent Posts

Recent Tags