Project brief: Comparing the job market from 2014 to 2024 for a Recruitment Agency

A recruitment agency has hired a data analyst to compare the rejection/acceptance rate of job applications within a 10-year period to determine if the job market has become fairer, more difficult, or remained the same. The data analyst will look at 10 candidates from 2014 and 10 candidates from 2024, with similar backgrounds, as well as 12 similar job positions for each year, and analyse 34 applications across these 10 applicants. Based on this information, the data analyst will relay the results to the recruitment agency to advertise to the consultant’s current candidates what to improve on their CV and applications.

Core requirements checklist:

• [x] Create a relational DB of your choice with minimum 5 tables
• [x] Set Primary and Foreign Key constraints to create relations between the tables
• [x] Using any type of joins, create a view that combines multiple tables in a logical way
• [x] In your DB, create a stored function that can be applied to a query in your DB
• [x] Prepare an example query with a subquery to demonstrate how to extract data from your DB for analysis
• [x] Create a DB diagram where all table relations are shown

Advanced requirements checklist:

• [x] Create a view that uses 3-4 base tables to prepare and demonstrate a query that uses the view
• [x] Prepare an example query with ‘group by’ and ‘having’ to demonstrate how to extract data from your DB for analysis
• [x] Create a stored procedure and demonstrate how it works

Relational DB with minimum 5 tables with PK/FK

• Candidate information

create table candidate_cv_2024
(candidate_id_24 int not null,
candidate_year varchar(20) not null,
first_name varchar(100) not null,
last_name varchar(100) not null,
candidate_education varchar(200) not null,
candidate_degree varchar(250),
candidate_grade varchar(100) not null,
primary key (candidate_id_24));

alter table candidate_cv_2024
add desired_job_type varchar(150);
alter table candidate_cv_2024
add desired_salary int;

• Candidate information

create table candidate_cv_2014
(candidate_id_14 int not null,
candidate_year varchar(20) not null,
first_name varchar(100) not null,
last_name varchar(100) not null,
candidate_education varchar(200) not null,
candidate_degree varchar(250),
candidate_grade varchar(100) not null,
primary key (candidate_id_14));

alter table candidate_cv_2014
add desired_job_type varchar(150);
alter table candidate_cv_2014
add desired_salary int;