Query Question SQL Statement 1. List all students who graduate in May 2002 and a
ID: 3743483 • Letter: Q
Question
Query Question
SQL Statement
1. List all students who graduate in May 2002 and are Accounting majors.
2. List all students who accepted an internship, including the job title, and the name of the company.
3. List the average salary offer by major.
4. List the interviews scheduled for December 1, 2001 by company.
5. List the average bonus offer by company.
6. List the jobs that are currently available.
7. List all students who received more than one offer.
8. List the students still searching for jobs.
9. List the jobs that require leadership and communication skills.
Query Question
SQL Statement
1. List all students who graduate in May 2002 and are Accounting majors.
2. List all students who accepted an internship, including the job title, and the name of the company.
3. List the average salary offer by major.
4. List the interviews scheduled for December 1, 2001 by company.
5. List the average bonus offer by company.
6. List the jobs that are currently available.
7. List all students who received more than one offer.
8. List the students still searching for jobs.
9. List the jobs that require leadership and communication skills.
ame Offer ame er IfRep_GPA Recruiter dentStatus FirstName LastName Interview bID terview_Date dentSkills SID rview_Time JobSkills ID ID ID Skill bStatus JobDescription RID killName tionExplanation / Answer
1.List all students who graduate in May 2002 and are Accounting majors.
SELECT * FROM student where major = "accounting" AND graddate = "may 2002"
===========================================================================
2.List all students who accepted an internship, including the job title,
and the name of the company.
SELECT S.*,J.JOB_TITLE,C.C_Name FROM student as S
INNER JOIN interview as I ON I.SID = S.SID
INNER JOIN job as J ON J.JOB_ID = I.JOB_ID
INNER JOIN recruiter as R ON R.RID = J.RID
INNER JOIN company as C ON C.CID = R.CID
============================================================================
3.List the average salary offer by major.
SELECT OF.SALARYOFFER FROM student as S
INNER JOIN offer as OF ON OF.SID = S.SID ORDER BY OF.SALARYOFFER DESC
============================================================================
4.List the interviews scheduled for December 1, 2001 by company.
SELECT C.*,I.* FROM company as C
INNER JOIN recruiter as R ON R.CID = C.CID
INNER JOIN job as J ON J.RID = R.RID
INNER JOIN interview as I ON I.JOBID = J.JOBID where I.interview_date = "2001-12-01"
============================================================================
5.List the average bonus offer by company.
SELECT AVG(OF.bonus_offer) as bonus FROM company as C
INNER JOIN recruiter as R ON R.CID = C.CID
INNER JOIN job as J ON J.RID = R.RID
INNER JOIN offer as OF ON OF.JOBID = J.JOBID
============================================================================
6.List the jobs that are currently available.
SELECT * FROM job where job_status = "available"
============================================================================
7.List all students who received more than one offer.
SELECT S.*,COUNT(OF.SID) as countOffer from offer as OF
INNER JOIN student as S ON S.SID = OF.SID
GROUP by OF.SID HAVING COUNT(OF.SID) > 1
============================================================================
8.List the students still searching for jobs.
SELECT S.* FROM student as S
INNER JOIN interview as I ON I.SID = S.SID
INNER JOIN job as J ON J.JOBID = I.JOBID
where job_title LIKE "%search job%"'
============================================================================
9.List the jobs that require leadership and communication skills.
SELECT JS.*,S.* FROM job_skills as JS
INNER JOIN skill as S ON S.SKID = JS.SKID
===========================================================================