Question

Using SQL statements in MySQL

(1)   Find the position number, title, employer and salary of positions that have been applied by the most amounts of applicants.

(2)   Find the applicant number, full name of applicants that possessed all skills needed by a position 00000005.
Note that an applicant possessed all skills needed by a position means all skills (sname) needed are possessed by the applicant,
and the skill levels (skilllevel) that needed less or equals to the correspondent skill levels the applicant possessed.

Default Value Nullat NO NO NO NO NO NO NO YES NO YES YES NO NO Table Applicant Applicant Applicant Applicant Applicant Applicant Applicant Applicant Applicant Applicant Applicant Applies Applies Applies LPTitle LSkill LState Position Position Position Position Position Position SNeeded SNeeded SNeeded SPossessed SPossessed SPossessed Total_Skills Total_skills Column aNumber fname Iname address city state phone Number faxNumber email acomment no_skills aNumber pNumber appdate title skill state pNumber pTitle employer salary extras specification pNumber sname skilllevel aNumber sname skilllevel aNumber skillNO Type decimal(6,0) varchar(20) varchar(30) varchar(50) varchar(30) varchar(20) decimal(10,0) decimal(10,0) varchar(50) varchar(1000) decimal(2,0) decimal(6,0) decimal(8,0) date varchar(30) varchar(30) varchar(3) decimal(8,0) varchar(50) varchar(100) decimal(10,2) varchar(50) varchar(500) decimal(8,0) varchar(30) decimal(2,0) decimal(6,0) varchar(30) decimal(2,0) decimal(6,0) bigint(21) NO NO NO NO NO YES NO YES YES YES NO NO YES NO NO YES NO NO 0

Answer 1

1.SELECT P.pnumber,P.ptitle,P.employer,P.salary

FROM Position P INNER JOIN applies A ON A.pnumber =P.pnumber

Group By P.pnumber

HAVING COUNT(A.anumber) =

(SELECT MAX(No_Of_application) FROM(

Select COUNT(anumber) as No_Of_application From Applies

Group By pnumber));

EXPLANATION:-

We use SELECT tofetch data from the table.

We are using INNER JOIN to combine the tables Position and applies, then we group the results by position number.

HAVING clause is used to check for count of applicants applied for position using COUNT() function.

Now, in sub query part we are getting maximum of the of the applicant applied for any position using GROUP BY clause from applies table and MAX() aggregate function.

Then we are comparing this result with the count .This is done to get the multiple position with maximum applicant.

2.SELECT A.anumber ,CONCAT(A.fname,' ',A.lname) AS Name FROM

Applicant A INNER JOIN SPossessed SP ON SP.anumber=A.anumber

INNER JOIN SNeeded SN ON SN.sname=SP.sname AND SP.slevel >=SN.slevel

WHERE SN.pnumber =00000005

Group BY anumber

HAVING COUNT(SP.sname) =(SELECT COUNT(sname) FROM SNeeded WHERE pnumber =00000005) ;

EXPLANATION:-

Here, we are using CONCAT() function to combine the first name and last name to get the full name.

Then we combine different tables using INNER JOIN.

While joining SNeeded table we checked for Sname as well as slevel possessed by applicant to be greater or equal to level needed for position number 00000005.

Where clause is used to filter the data on the basis pnumber.

Then GROUP BY is used for grouping by anumber.

Then we are checking count of all these skills using HAVING clause to check whether it is equal to the the number skills needed for pnumber 00000005.