Tables with attributes: Company(compid, compname, comptype) Job(jobid, jobtitle,
ID: 3652386 • Letter: T
Question
Tables with attributes: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)
Queries:
1. For each company in the database, show the total number of jobs listed by that company, and average minimum salary. Show the number of jobs as "numjobs" and average minimum salary as "avgmin".
2. Find the companies who have more than 2 jobs listed for the Dayton location.
3. Find the applicants who have applied to more than 2 different companies.
4. 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 withoug using a subquery.
Explanation / Answer
1) select count(jobid),avg(salarylow) from company c,job j where c.compid=j.compid; 2) select compname from company c,job j where c.compid=j.compid group by compid having (count(location)>2) where location='Dayton'; 3)select appid,count(appid) from applies group by appid having count(appid)>2; 4)select jobid from jobskills where skillsid is null;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.