Top SQL Interview Examples for Experienced Developer

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

CTEs simplify complex queries by creating temporary result sets that can be referenced within the main query.

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 provide rankings or calculations over a partitioned dataset.

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?

Sequences generate unique values, often used for 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 for efficient data manipulation.

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 with parent-child relationships.

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 allows conditional expressions within SQL queries.

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 value.

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

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

LISTAGG() concatenates values from multiple rows into a single string.

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() performs advanced pattern matching using regular expressions.

_
SELECT name
FROM employees
WHERE REGEXP_LIKE(name, ‘^J.’);_*

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

The DBMS_SCHEDULER package allows you to create and manage scheduled 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;