Practice Questions

SELECT first_name || ' ' || last_name AS full_name,
       job_id,
       salary
FROM   employees
WHERE  salary BETWEEN 5000 AND 12000    -- inclusive range filter
ORDER BY salary DESC,                   -- primary sort: highest salary first
         last_name ASC;                 -- secondary sort: alphabetical for same salary

Explanation: The || operator concatenates strings in Oracle. BETWEEN is inclusive (equivalent to >= 5000 AND <= 12000). Multiple ORDER BY columns are listed with commas; each can independently be ASC or DESC.

Expected rows: Around 38 employees (depending on HR data version).

SELECT employee_id,
       first_name || ' ' || last_name AS full_name,
       department_id,
       hire_date,
       commission_pct
FROM   employees
WHERE  department_id IN (50, 80)                -- department filter
AND    hire_date > DATE '1997-01-01'            -- date filter
AND    (commission_pct IS NULL                  -- no commission
        OR commission_pct > 0.2)               -- OR high commission
ORDER BY department_id,
         hire_date;

Explanation: IN (50, 80) is shorthand for department_id = 50 OR department_id = 80. The parentheses around the last OR condition are important — without them, operator precedence would misparse the logic (AND binds tighter than OR). DATE '1997-01-01' is the Oracle date literal syntax.

SELECT first_name || ' ' || last_name AS full_name,
       email,
       job_id,
       salary,
       CASE
           WHEN salary > 10000  THEN 'High'
           WHEN salary >= 5000  THEN 'Medium'
           ELSE                      'Low'
       END AS salary_grade
FROM   employees
WHERE  (UPPER(last_name) LIKE 'S%'           -- starts with S (case-insensitive)
        OR UPPER(last_name) LIKE '%SON')      -- ends with SON
AND    department_id IS NOT NULL              -- exclude NULL department
ORDER BY last_name;

Explanation: UPPER() makes the LIKE comparison case-insensitive. The % wildcard matches any sequence of characters. The CASE expression evaluates conditions in order; the first matching WHEN wins. IS NOT NULL is the correct way to test for NULL (never use != NULL).

SELECT department_id,
       COUNT(*)              AS num_employees,
       ROUND(AVG(salary), 2) AS avg_salary,
       MIN(salary)           AS min_salary,
       MAX(salary)           AS max_salary,
       SUM(salary)           AS total_payroll
FROM   employees
WHERE  department_id IS NOT NULL          -- exclude employees with no department
GROUP BY department_id
HAVING COUNT(*) > 5                       -- filter: more than 5 employees
AND    AVG(salary) > 6000                 -- filter: average salary above 6000
ORDER BY avg_salary DESC;

Explanation: WHERE filters individual rows before grouping; HAVING filters groups after aggregation. You cannot use aggregate functions in WHERE — that's what HAVING is for. COUNT(*) counts all rows in the group; COUNT(department_id) would skip NULLs.

SELECT e.job_id,
       j.job_title,
       COUNT(*)                                               AS total_employees,
       COUNT(CASE WHEN e.salary > j.max_salary THEN 1 END)   AS over_grade,
       COUNT(CASE WHEN e.salary < j.min_salary THEN 1 END)   AS under_grade,
       ROUND(AVG(e.salary), 2)                               AS avg_salary,
       j.min_salary,
       j.max_salary
FROM   employees e
JOIN   jobs j ON e.job_id = j.job_id
GROUP BY e.job_id, j.job_title, j.min_salary, j.max_salary
HAVING COUNT(CASE WHEN e.salary > j.max_salary THEN 1 END) > 0
OR     COUNT(CASE WHEN e.salary < j.min_salary THEN 1 END) > 0
ORDER BY (COUNT(CASE WHEN e.salary > j.max_salary THEN 1 END) +
          COUNT(CASE WHEN e.salary < j.min_salary THEN 1 END)) DESC;

Explanation: COUNT(CASE WHEN condition THEN 1 END) is a powerful pattern for conditional counting. The CASE returns 1 when the condition is true and NULL otherwise — COUNT ignores NULLs, so it effectively counts only the matching rows. This avoids multiple subqueries.

-- Main answer: hiring by year
SELECT EXTRACT(YEAR FROM hire_date)     AS hire_year,
       COUNT(*)                          AS employees_hired,
       ROUND(AVG(salary))                AS avg_starting_salary
FROM   employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;

-- Bonus: most popular hire month per year (using subquery + window function)
WITH monthly_hires AS (
    SELECT EXTRACT(YEAR  FROM hire_date) AS hire_year,
           EXTRACT(MONTH FROM hire_date) AS hire_month,
           TO_CHAR(hire_date, 'Month')   AS month_name,
           COUNT(*)                      AS cnt,
           RANK() OVER (
               PARTITION BY EXTRACT(YEAR FROM hire_date)
               ORDER BY COUNT(*) DESC
           ) AS rnk
    FROM   employees
    GROUP BY EXTRACT(YEAR FROM hire_date),
             EXTRACT(MONTH FROM hire_date),
             TO_CHAR(hire_date, 'Month')
)
SELECT hire_year, month_name, cnt AS peak_month_hires
FROM   monthly_hires
WHERE  rnk = 1
ORDER BY hire_year;

Explanation: EXTRACT(YEAR FROM hire_date) pulls the year component from the date. When grouping by a date expression, you must include the same expression in GROUP BY (not the alias). The bonus query uses a window function inside a CTE to find the peak month per year without a complex self-join.

SELECT e.first_name || ' ' || e.last_name AS full_name,
       j.job_title,
       d.department_name,
       l.city
FROM   employees   e
JOIN   jobs        j ON e.job_id        = j.job_id          -- every employee has a job
LEFT JOIN departments d ON e.department_id = d.department_id -- some employees have no dept
LEFT JOIN locations   l ON d.location_id   = l.location_id  -- location via department
ORDER BY d.department_name NULLS LAST,                      -- NULLs at the end
         e.last_name;

Explanation: The first JOIN is an INNER JOIN — every employee must have a valid job. The subsequent JOINs are LEFT JOINs because employees may lack a department, and departments chain to locations. NULLS LAST puts rows with no department at the bottom of the sort.

Key insight: When you LEFT JOIN through a chain (employee → department → location), once a NULL appears (no department), all subsequent JOINs in the chain also produce NULL (no location). The chain of NULLs propagates automatically.

SELECT d.department_id,
       d.department_name,
       l.city
FROM   departments d
LEFT JOIN employees  e ON d.department_id = e.department_id   -- include depts with no employees
LEFT JOIN locations  l ON d.location_id   = l.location_id
WHERE  e.employee_id IS NULL                                   -- filter: no employee matched
ORDER BY d.department_name;

Explanation: This is the classic "anti-join" pattern using LEFT JOIN + WHERE NULL. The LEFT JOIN keeps all departments. The WHERE e.employee_id IS NULL filter then keeps only those departments where no employee row matched — i.e., empty departments.

An alternative using NOT EXISTS:

SELECT d.department_id, d.department_name
FROM   departments d
WHERE  NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

Both approaches return the same result; the NOT EXISTS version is often clearer to read.

SELECT c.country_name,
       r.region_name,
       COUNT(DISTINCT l.city)    AS distinct_cities,
       COUNT(e.employee_id)      AS total_employees,
       ROUND(AVG(e.salary), 2)   AS avg_salary
FROM   countries   c
JOIN   regions     r ON c.region_id     = r.region_id
JOIN   locations   l ON l.country_id    = c.country_id
JOIN   departments d ON d.location_id   = l.location_id
JOIN   employees   e ON e.department_id = d.department_id
GROUP BY c.country_name, r.region_name
HAVING COUNT(e.employee_id) > 0        -- only countries with employees
ORDER BY total_employees DESC;

Explanation: The join chain flows: countries → regions (for region name) → locations (cities in each country) → departments (departments in each city) → employees (people in each department). COUNT(DISTINCT l.city) counts unique cities, not total location records. All INNER JOINs here are appropriate because we only want countries with all these relationships.

SELECT e.first_name || ' ' || e.last_name AS full_name,
       e.salary,
       e.department_id,
       ROUND(
           (SELECT AVG(salary)
            FROM   employees
            WHERE  department_id = e.department_id),   -- correlated: uses outer e.department_id
           2
       ) AS dept_avg_salary
FROM   employees e
WHERE  e.salary > (
    SELECT AVG(salary)                                 -- correlated subquery in WHERE
    FROM   employees
    WHERE  department_id = e.department_id             -- references outer query's row
)
ORDER BY e.department_id,
         e.salary DESC;

Alternative using window function (often more efficient):

SELECT full_name, salary, department_id, dept_avg_salary
FROM (
    SELECT first_name || ' ' || last_name              AS full_name,
           salary,
           department_id,
           ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg_salary
    FROM   employees
)
WHERE salary > dept_avg_salary
ORDER BY department_id, salary DESC;

Explanation: The correlated subquery re-executes for each employee, using that employee's department_id to compute the average for their department. The window function approach computes all department averages in one pass — generally faster for large tables.

SELECT e.employee_id,
       e.first_name || ' ' || e.last_name AS full_name,
       j.job_title                        AS current_job,
       e.salary                           AS current_salary,
       1 + (                              -- 1 (current job) + count of historical jobs
           SELECT COUNT(*)
           FROM   job_history jh
           WHERE  jh.employee_id = e.employee_id
       ) AS total_jobs_held
FROM   employees e
JOIN   jobs      j ON e.job_id = j.job_id
WHERE  EXISTS (
    SELECT 1
    FROM   job_history jh
    WHERE  jh.employee_id = e.employee_id   -- only employees with history records
)
ORDER BY total_jobs_held DESC,
         e.last_name;

Explanation: EXISTS is used to filter to only employees who appear in JOB_HISTORY. It's more efficient than IN for large subqueries because it short-circuits at the first match. The 1 + (subquery) computes total jobs including the current one: their job_history count plus their current role.

SELECT department_name,
       full_name,
       salary,
       salary_rank
FROM (
    SELECT d.department_name,
           e.first_name || ' ' || e.last_name        AS full_name,
           e.salary,
           RANK() OVER (
               PARTITION BY e.department_id
               ORDER BY e.salary DESC
           ) AS salary_rank
    FROM   employees   e
    JOIN   departments d ON e.department_id = d.department_id
)
WHERE  salary_rank <= 2                  -- top 2 per department (ties both included)
ORDER BY department_name,
         salary_rank;

Explanation: RANK() gives tied rows the same rank and then skips the next number (1, 2, 2, 4). This means WHERE salary_rank <= 2 correctly includes all employees tied for 2nd place. Using ROW_NUMBER() instead would arbitrarily exclude one tied employee. Using DENSE_RANK() would also work but might include 3 employees when two are tied for 2nd.

The window function runs in the inner query; the filter WHERE salary_rank <= 2 must be in an outer query (window function results are not visible in the same WHERE clause).

SELECT hire_date,
       first_name || ' ' || last_name              AS full_name,
       salary,
       SUM(salary) OVER (
           ORDER BY hire_date, employee_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )                                           AS running_total_payroll,
       ROUND(
           100.0 * salary / SUM(salary) OVER (),   -- OVER() with no partition = grand total
           2
       )                                           AS pct_of_total_payroll
FROM   employees
ORDER BY hire_date, employee_id;

-- Sample of expected output:
-- HIRE_DATE  | FULL_NAME        | SALARY | RUNNING_TOTAL | PCT_OF_TOTAL
-- -----------|------------------|--------|---------------|-------------
-- 1987-09-17 | Jennifer Whalen  | 4400   | 4400          | 0.46
-- 1989-11-17 | Michael Hartstein| 13000  | 17400         | 1.35
-- 1990-01-03 | Pat Fay          | 6000   | 23400         | 0.62

Explanation: SUM(salary) OVER (ORDER BY hire_date, employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) creates a cumulative sum. ROWS BETWEEN is used (rather than RANGE BETWEEN) to handle ties in hire_date cleanly — ROWS counts physical rows while RANGE would add all tied dates at once. SUM(salary) OVER () with an empty OVER() computes the grand total once and applies it to every row.

WITH emp_hierarchy (
    employee_id, full_name, manager_id, depth, manager_name, hierarchy_path
) AS (
    -- Anchor: start with the CEO (no manager)
    SELECT employee_id,
           first_name || ' ' || last_name AS full_name,
           manager_id,
           0                              AS depth,
           'No Manager'                   AS manager_name,
           first_name || ' ' || last_name AS hierarchy_path
    FROM   employees
    WHERE  manager_id IS NULL             -- top of the org chart

    UNION ALL

    -- Recursive: find each employee who reports to someone in the previous level
    SELECT e.employee_id,
           e.first_name || ' ' || e.last_name,
           e.manager_id,
           h.depth + 1,
           h.full_name,                  -- parent's name becomes manager_name
           h.hierarchy_path || ' > ' || e.first_name || ' ' || e.last_name
    FROM   employees e
    JOIN   emp_hierarchy h ON e.manager_id = h.employee_id  -- join to parent
)
SELECT LPAD(' ', depth * 3) || full_name AS employee_tree,
       depth                             AS org_level,
       manager_name,
       hierarchy_path
FROM   emp_hierarchy
ORDER BY hierarchy_path;

Explanation: The recursive CTE has two parts separated by UNION ALL. The anchor selects the root of the tree (the CEO with no manager). The recursive member joins each employee to their parent in the CTE result, increasing depth by 1 and extending the path string. The recursion terminates automatically when no more employees can be joined (i.e., when all leaves of the tree are reached). LPAD(' ', depth * 3) indents each level for visual hierarchy.

SELECT d.department_name,
       COUNT(e.employee_id)                                         AS total_employees,
       ROUND(AVG(e.salary), 2)                                      AS avg_salary,
       COUNT(CASE WHEN e.salary < 5000                THEN 1 END)   AS band_1_under_5k,
       COUNT(CASE WHEN e.salary BETWEEN 5000 AND 9999 THEN 1 END)   AS band_2_5k_10k,
       COUNT(CASE WHEN e.salary BETWEEN 10000 AND 14999 THEN 1 END) AS band_3_10k_15k,
       COUNT(CASE WHEN e.salary >= 15000               THEN 1 END)  AS band_4_15k_plus
FROM   departments d
JOIN   employees   e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) >= 1
ORDER BY avg_salary DESC;

-- Sample output:
-- DEPARTMENT_NAME    | TOTAL | AVG_SALARY | BAND_1 | BAND_2 | BAND_3 | BAND_4
-- -------------------|-------|------------|--------|--------|--------|-------
-- Executive          | 3     | 19333.33   | 0      | 0      | 0      | 3
-- Sales              | 34    | 8955.88    | 0      | 30     | 2      | 2
-- Finance            | 6     | 8600.00    | 0      | 5      | 1      | 0
-- IT                 | 5     | 5760.00    | 0      | 5      | 0      | 0
-- Shipping           | 45    | 3475.56    | 38     | 7      | 0      | 0

Explanation: This is pivot-style aggregation — turning row values into columns using COUNT(CASE WHEN ...). Each band column counts employees whose salary falls in that range. The CASE expression returns 1 when the condition is true and NULL otherwise; COUNT skips NULLs, so it counts only the matching rows. This is far simpler than Oracle's PIVOT clause for a fixed set of bands.