Database Schema and Advanced SQL Query Solutions

Posted on Feb 4, 2026 in Graphic Arts

Relational Schema Functional Dependencies (FDs)

Person Entity

Schema: person (per_id, name, zip_code, email, gender, phone)

per_id → name, zip_code, gender
email, phone → per_id

Skill Entity

Schema: skill (sk_code, title, description, level_type)

sk_code → title, description, level_type
title → description

Company Entity

Schema: company (comp_id, address, zip_code, website, industry_group)

comp_id → address, zip_code, website, industry_group
website, industry_group → comp_id

Takes Entity (Course Enrollment)

Schema: takes (per_id, c_code, sec_no, complete_date)

sec_no → c_code, complete_date
per_id → c_code, sec_no, complete_date

Prereq Entity (Course Prerequisites)

Schema: prereq (c_code, required_code)

c_code → required_code

Store Entity

Schema: store (store_id, address, zip_code, phone)

store_id → address, zip_code, phone
address, zip_code, phone → store_id

Course Entity

Schema: course (c_code, title, level_type, description, status, retail_price)

c_code → title, level_type, description, status, retail_price
status, title, description → c_code
title → description

Advanced SQL Query Scenarios

1. Identifying Almost Qualified Personnel (Missing-K List)

When a company cannot find any fully qualified person for a job position, a secondary solution is to find individuals who are almost qualified. Create a “missing-k” list that identifies people who lack $k$ skills required for a specified pos_code, where $k < 4$. (Problem 22)

WITH missing AS (
    SELECT per_id, COUNT(sk_code) AS need_sk
    FROM (
        SELECT per_id, sk_code
        FROM person, requires
        WHERE pos_code = '9285616'
        MINUS
        SELECT per_id, sk_code
        FROM has_skill
    )
    GROUP BY per_id
)
SELECT per_id, fName, lName, need_sk
FROM missing NATURAL JOIN person
WHERE need_sk < 4;

2. Finding Courses Covering Skill Gaps

Given a person’s identifier (per_id) and a position code (pos_code), find the courses (course ID and title) where each course alone teaches all the skills missing for this person to be qualified for the specified position. This assumes the entire skill gap of the worker and the requirement of the position can be covered by one course.

WITH match AS(
    SELECT sk_code, c_code
    FROM teaches NATURAL JOIN (
        SELECT sk_code
        FROM requires
        WHERE pos_code = '9285619'
        MINUS
        SELECT sk_code
        FROM has_skill
        WHERE per_id = '109'
    ) miss
)
SELECT C.c_code, C.title
FROM course C
WHERE NOT EXISTS (
    SELECT DISTINCT sk_code
    FROM match
    MINUS
    SELECT sk_code
    FROM teaches T
    WHERE C.c_code = T.c_code
);

3. Determining Highest Paying Qualified Position

Given a person’s identifier (per_id), find the job position that offers the highest pay rate among all positions for which this person is fully qualified based on their current skill possession.

WITH qualified AS (
    SELECT DISTINCT pos_code, pay_range_high AS high
    FROM position
    WHERE NOT EXISTS (
        SELECT DISTINCT sk_code
        FROM requires
        WHERE pos_code = position.pos_code
        MINUS
        SELECT sk_code
        FROM has_skill
        WHERE per_id = '109'
    )
)
SELECT pos_code, title, high
FROM qualified NATURAL JOIN position
WHERE high = (
    SELECT MAX(high)
    FROM qualified
);

4. Tracking Worker Salary Increases by Industry

Calculate the number of workers whose earnings increased between their previous and current employment records, grouped by the industry_group (using the attribute in the Company table).

WITH current_s AS (
    SELECT per_id, industry_group, pay
    FROM works
    NATURAL JOIN job
    NATURAL JOIN company
    NATURAL JOIN (
        SELECT comp_id, job_code, (pay_rate) * 1920 AS pay
        FROM job
        WHERE pay_type = 'wage'
        UNION
        SELECT comp_id, job_code, pay_rate AS pay
        FROM job
        WHERE pay_type = 'salary'
    ) salary
    WHERE end_date = (SELECT MAX(end_date) FROM works)
),
prev_slry AS (
    SELECT per_id, industry_group, pay
    FROM works
    NATURAL JOIN job
    NATURAL JOIN company
    NATURAL JOIN (
        SELECT comp_id, job_code, (pay_rate) * 1920 AS pay
        FROM job
        WHERE pay_type = 'wage'
        UNION
        SELECT comp_id, job_code, pay_rate AS pay
        FROM job
        WHERE pay_type = 'salary'
    ) salary
    WHERE end_date IS NULL
),
compare_slry AS (
    SELECT DISTINCT Curr.per_id, Curr.industry_group, Curr.pay
    FROM prev_slry Prev, current_s Curr
    WHERE Curr.industry_group = Prev.industry_group
    AND Curr.pay > Prev.pay
)
SELECT industry_group, COUNT(per_id)
FROM compare_slry
GROUP BY industry_group;