Question 1: What is Monica's level of expertise in SAP?

Question 2:

Formulate a SQL query to find the name(s) of the applicant(s) who applied to more than 2 different companies.

HINTS Question 1:

(1) Look for the values in the question. Monica is a NAME in the APPLICANT table and SAP is a SKILLNAME in the SKILLS table, so we know that we need to trace the path between these tables. (2) Look for other column names in the question. EXPERTISE is a column in APPSKILLS, so we know that we need to SELECT from this table. (3) List the criteria that join these tables in the schema by looking for the foreign key references between tables mentioned in the schema: APPSKILLS.APPID references APPLICANT.APPID and APPSKILLS.SKILLID references SKILLS.SKILLID (4) You should now have all the tables required for the query, and the relationships between them. (5) Now create a query of the form:

select

from

where

and ;

HINTS Question 2:

(1) Look for the nouns in the question. NAME is in APPLICANT table and companies are in COMPANY table, so we know that we need to trace the path between these tables. (2) Think of a sentence to describe the application process, to get a feeling for the relationship between tables. Look for tables matching the nouns and verbs in your sentence. Example: an APPLICANT APPLIES for a JOB offered by a COMPANY. (3) List the criteria that join these tables in the schema: - Look for the foreign key references between tables mentioned in the schema: APPLIES.JOBID references JOB.COMPID and JOB.COMPID references COMPANY.COMPID - Look for any primary key columns which are used elsewhere, even if a reference is not formally defined. Both APPLICANT and APPLIES have a column called APPID which is the ID of the APPLICANT. (4) You should now have all the tables required for the query, and an idea of the relationships between them. (5) Now create a query of the form: select from where ;

(6) We should now have a table with a list of names and companies. However, some candidates have applied to the same company more than once, and the question asks for the number of applications to *different* companies. We need to eliminate the duplicates. To do this, add the keyword DISTINCT to your query.

(7) We can count the names to get the answer: only one applicant has applied for more than 2 companies. However, counting would be difficult for a bigger data set. We need an aggregate function to sum the number of companies for each applicant. Use COUNT with GROUP BY to list the names and count the different companies each has applied for. This time, we only need to apply DISTINCT to COMPI, the company ID, since GROUP BY name ensures that each name is on its own row.

(8) The results now clearly show the applicant who has applied for more than 2 jobs, and gives us enough information to answer the question. However, it also gives some additional information, when we were only required to provide a list of names (in this case, the list has only one entry). In real-world applications, superfluous information must often be excluded for a number of reasons, such as privacy, or to reduce network traffic. For example, the job agency might require a list of people who are repeatedly rejected for jobs, in order to monitor equal opportunities. In that case, private information about the actual number of applications, and the jobs and companies the candidates applied for, would be excluded. This is where the HAVING clause can be used, along with one or more criteria. It is has a similar function to WHERE, but is applied to aggregated (grouped) data.

drop table Jobskills; drop table Appskills; drop table Applies; drop table Applicant; drop table Skills; drop table Job; drop table Company; create table Company(compid char(5) primary key, compname varchar(20), comptype varchar(15)); create table Job(jobid char(5) primary key, jobtitle varchar(20), salarylow int, salaryhigh int, location char(10), compid references Company(compid) on delete cascade); create table Skills(skillid char(5) primary key, skillname varchar(15)); create table Jobskills(jobid references Job(jobid) on delete cascade, skillid references Skills(skillid), expertiseneeded int, primary key(jobid,skillid)); create table Applicant(appid char(5) primary key, name varchar(15), age int, highdegree char(5), expected_salary int) ; create table AppSkills(appid references Applicant(appid) on delete cascade, skillid references Skills(skillid), expertise int, primary key(appid, skillid)); create table Applies(jobid references Job(jobid), appid references Applicant(appid) on delete cascade, appdate date, decisiondate date, outcome char(10), primary key(jobid, appid)); rem Initial Company data insert into Company values('PWC', 'Price Waterhouse', 'consulting'); insert into Company values('MSFT', 'Microsoft', 'software'); insert into Company values('INTL', 'Intel', 'electronics'); insert into Company values('NCR', 'NCR Corp', 'server'); insert into Company values('WPAF', 'WP Air Force', 'defense'); insert into Company values('DLT', 'Deloitte', 'consulting'); rem Initial Job data insert into Job values('101', 'Programmer', 55000, 60000, 'Redmond', 'MSFT'); insert into Job values('j02', 'Designer', 42000, 45000, 'Redmond', 'MSFT'); insert into Job values('j03', 'SAP impl', 30000, 40000, 'Chicago', 'PWC'); insert into Job values('j04', 'Proj mgmt', 35000, 55000, 'Chicago', 'PWC'); insert into Job values('j05', 'SOX', 60000, 65000, 'Detroit', 'PWC'); insert into Job values('j06', 'db admin', 45000, 50000, 'Dayton', 'NCR'); insert into Job values('j07', 'db designer', 35000, 40000, 'Dayton', 'NCR'); insert into Job values('j08', 'intern', 25000, 28000, 'Dayton', 'NCR'); insert into Job values('j09', 'engineer', 52000, 55000, 'Dayton','WPAF'); insert into Job values('j10', 'dba', 62000, 65000, 'Dayton','WPAF'); insert into Job values('j11', 'hardware dev', 50000, 65000, 'NYC','INTL'); insert into Job values('j12', 'pcb designer', 55000, 68000,'NYC','INTL');

insert into Job values('j13', 'chip designer', 40000, 55000,'Chicago','INTL'); insert into Job values('j14', 'IT', 40000, 60000, 'Dayton', 'DLT'); insert into Job values('j15', 'IT', 50000, 70000, 'Chicago', 'DLT'); rem initial Skills data insert into Skills values('s1', 'database'); insert into Skills values('s2', 'programming'); insert into Skills values('s3', 'sox'); insert into Skills values('s4', 'project'); insert into Skills values('s5', 'hardware'); insert into Skills values('s6', 'sap'); insert into Skills values('s7', 'analysis'); rem Initial Jobskills data insert into Jobskills values('101', 's2', 5); insert into Jobskills values('101', 's7', 4); insert into Jobskills values('j02', 's2', 3); insert into Jobskills values('j02', 's7', 5); insert into Jobskills values('j03', 's6', 5); insert into Jobskills values('j04', 's7', 4); insert into Jobskills values('j04', 's4', 5); insert into Jobskills values('j04', 's2', 2); insert into Jobskills values('j05', 's3', 5); insert into Jobskills values('j06', 's1', 5); insert into Jobskills values('j06', 's2', 3); insert into Jobskills values('j07', 's1', 4); insert into Jobskills values('j07', 's7', 3); insert into Jobskills values('j08', 's1', 2); insert into Jobskills values('j09', 's2', 4); insert into Jobskills values('j09', 's4', 4); insert into Jobskills values('j10', 's4', 3); insert into Jobskills values('j10', 's1', 5); insert into Jobskills values('j11', 's5', 3); insert into Jobskills values('j11', 's4', 3); insert into Jobskills values('j12', 's5', 5); insert into Jobskills values('j13', 's1', 4); insert into Jobskills values('j13', 's2', 5); insert into Jobskills values('j14', 's7', 4); rem initial Applicants data insert into Applicant values('a1', 'Joe', 30, 'MS', 55000); insert into Applicant values('a2', 'Monica', 25, 'BS', 62000); insert into Applicant values('a3', 'Jim', 22, 'BS', 45000); insert into Applicant values('a4', 'Monica', 25, 'BS', 34000); rem initial Appskills data insert into Appskills values('a1', 's1', 3); insert into Appskills values('a1', 's2', 4); insert into Appskills values('a1', 's4', 4); insert into Appskills values('a1', 's6', 3); insert into Appskills values('a1', 's7', 4); insert into Appskills values('a2', 's2', 3); insert into Appskills values('a2', 's3', 5);

insert into Appskills values('a2', 's6', 4); insert into Appskills values('a3', 's4', 3); insert into Appskills values('a3', 's1', 3); insert into Appskills values('a3', 's2', 5); rem Applies insert into Applies values ('101', 'a1', '01-JAN-06', '08-JAN-06', 'hire'); insert into Applies values ('101', 'a2', '01-JAN-06', '08-JAN-06', 'hire'); insert into Applies values ('j02', 'a2', '01-JAN-06', '08-JAN-06', 'hire'); insert into Applies values ('j04', 'a2', '01-JAN-06', '08-JAN-06', 'hire'); insert into Applies values ('j02', 'a3', '01-JAN-06', '08-JAN-06', 'nohire'); insert into Applies values ('j04', 'a3', '01-JAN-06', '08-JAN-06', 'nohire'); insert into Applies values ('j06', 'a3', '01-JAN-06', '08-JAN-06', 'nohire');