Feature $3$: search for job posts. The input includes a keyword, job type $($full time or part time$),$ and a state. The procedure does the following:

$1)$ It finds all job posts that are active and the job title or job description has a substring that matches the input keyword, and the job post's state and job type match the input state and job type. The procedure prints out the matching job posts' job post id$,$ associated company name, job title, description, min and max pay, city and state.

$2)$ In case there are no matching job posts, print out $\text{'}$No job posts found'.

Hint: to match a substring in the procedure, use

where column$\_$name like $\text{'}\%\text{'}||$ input$\_$keyword $||\text{'}\%\text{'}$

Feature $7$: Generate job alerts. The input is a job post ID and a timestamp. The procedure does the following:

$1)$ it first checks whether there is a job post with the input ID and is still active. If there is no such a job post, print a message 'Invalid job post ID$\text{'}$ and stop.

$2)$ it then finds all job seekers who satisfy ALL of the following conditions:

a$)$ the job seeker's highest degree meets the minimal degree requirement of the job post $($if you use $1$ for associate degree, $2$ for bachelor, $3$ for master's and $4$ for doctoral degree, then the highest degree $>=$ minimal degree of the job post$)$;

b$)$ the job seeker's number of years of work experience is greater or equal to the minimal experience of the job post;

c$)$ for each skill required by the job post, the job seeker has that skill and the skill level is greater or equal to required level.

$3)$ for each job seeker found in step $2),$ print out the name of the job seeker and insert a row into the message table with a newly generated message ID$,$ account ID as the job seeker's account ID$,$ message time as the input time, and body of the message is 'A job post X is available and you are qualified to apply' where X is the input job post ID$.$

Hint: to check condition c$)$ in step $2,$ you can use an explicit cursor to return job seeker who satisfies condition a$)$ and b$)$ and another explicit cursor to return the required skill ID and minimal level for the job post. A nested loop can then be used to check condition c using the following pseudo code:

$--$ c$1$ is the first cursor to check condition a and b$,$ c$2$ is the second cursor to get required skill IDs and minimal skill levels.

for r$1$ in c$1$ loop

flag:$=1$;

for r$2$ in c$2$ loop

$--$ use select count$(*)$ to check whether condition c is satisfied $($i$.$e$.,$ whether job seeker r$1$ has a skill that matches the skill returned in r$2$ and the skill level $>=$ r$2\text{'}$s minimal level.

if the count is zero $($not satisfied$),$ set flag to $0$ and exit the loop

end for;

if flag $=1$ then

$--$ do step $3.$

end if;

end for;