Question

I have these following tables:

�Company(compid, compname, comptype)

�Job(jobid, jobtitle, salarylow, salaryhigh, location, compid*)

�Skills(skillid, skillname) �Jobskills(jobid*, skillid*, expertiseneeded)

�Applicant(appid, name, age, highdegree, expected_salary)

�AppSkills(appid*, skillid*, expertise)

�Applies(jobid*, appid*, appdate, decisiondate, outcome)

The queries are:

1. Find the outcome of all job applications of Monica (once again, note that there may be multiple Monica's in the database).

2. Find the applicants who have applied to more than 2 different companies.

3. Find the jobs that require no skills (well, as far as the data is concerned), i.e., jobs for which there is no corresponding skill listed in the database. Do this query without using a subquery.

4. Find the jobs located in Dayton that require both database and analysis skills.

5. Find the company offering the maximum salary in Chicago.

Answer 1

Short Summary:

• Within the alloted time, was able to create the tables and shown you queries
• Could not get time to insert records and shown you the sample outupt.

**************Please upvote the answer and appreciate our time.************

CREATE DATABASE JobSearch
GO

USE JobSearch
GO

CREATE TABLE Company(
   compid INT PRIMARY KEY IDENTITY(1,1)
   , compname VARCHAR(50)
   , comptype VARCHAR(50))

CREATE TABLE Job(
   jobid INT PRIMARY KEY IDENTITY(1,1)
   , jobtitle VARCHAR(50)
   , salarylow DECIMAL(18, 2)
   , salaryhigh DECIMAL(18, 2)
   , [location] VARCHAR(50)
   , compid INT
   , FOREIGN KEY(compid) REFERENCES Company(compid))

CREATE TABLE Skills(
   skillid INT PRIMARY KEY IDENTITY(1,1)
   , skillname VARCHAR(50))

CREATE TABLE Jobskills(
   jobid INT
   , skillid INT
   , expertiseneeded INT
   , PRIMARY KEY(jobid, skillid)
   , FOREIGN KEY(jobid) REFERENCES Job(jobid)
   , FOREIGN KEY(skillid) REFERENCES Skills(skillid))

CREATE TABLE Applicant(
   appid INT PRIMARY KEY IDENTITY(1,1)
   , [name] VARCHAR(50)
   , age INT
   , highdegree VARCHAR(50)
   , expected_salary DECIMAL(18, 2))

CREATE TABLE AppSkills(
   appid INT
   , skillid INT
   , expertise INT
   , PRIMARY KEY(appid, skillid)
   , FOREIGN KEY(appid) REFERENCES Applicant(appid)
   , FOREIGN KEY(skillid) REFERENCES Skills(skillid))

CREATE TABLE Applies(
   jobid INT
   , appid INT
   , appdate DATETIME
   , decisiondate DATETIME
   , outcome VARCHAR(50)
   , PRIMARY KEY(jobId, appid)
   , FOREIGN KEY(appid) REFERENCES Applicant(appid)
   , FOREIGN KEY(jobid) REFERENCES Job(jobid))

--1. Find the outcome of all job applications of Monica (once again, note that there may be multiple Monica's in the database).
SELECT jobid, outcome
FROM Applies
INNER JOIN Applicant ON Applicant.appid = Applies.appid
WHERE Applicant.[name] = 'Monica'

--2. Find the applicants who have applied to more than 2 different companies.
SELECT [name]
FROM Applicant
INNER JOIN Applies ON Applicant.appid = Applies.appid
INNER JOIN Job ON Job.jobid = Applies.jobid
GROUP BY Applicant.appid, Applicant.name
HAVING COUNT(DISTINCT Job.compid) >= 2

--3. Find the jobs that require no skills (well, as far as the data is concerned),
--i.e., jobs for which there is no corresponding skill listed in the database. Do this query without using a subquery.

SELECT jobid
FROM Job
EXCEPT
SELECT DISTINCT jobid
FROM Jobskills

--4. Find the jobs located in Dayton that require both database and analysis skills.
SELECT *
FROM Job
INNER JOIN Jobskills ON Jobskills.jobid = Job.jobid
INNER JOIN Skills ON Skills.skillid = Jobskills.skillid
WHERE Job.[location] = 'Dayton' AND Skills.skillname = 'database' AND Skills.skillname = 'analysis'

--5. Find the company offering the maximum salary in Chicago.
SELECT *
FROM Company
INNER JOIN Job On Job.compid = Company.compid
WHERE Job.[location] = 'Chicago' AND Job.salaryhigh = (SELECT MAX(Job.salaryhigh) FROM Job WHERE Job.[location] = 'Chicago')

**************************************************************************************

Feel free to rate the answer and comment your questions, if you have any.

Please upvote the answer and appreciate our time.

Happy Studying!!!

**************************************************************************************