I'm preparing for an exam in databases and SQL and I'm solving an exercise:
We have a database of 4 tables that represent a human resources company. The tables are:
applicant(a-id,a-name,a-city,years-of-study), job(job-name,job-id), qualified(a-id,job-id) wish(a-id,job-id).
the table applicant represents the table of applicants obviously. And jobs is the table of available jobs. the table qualified shows what jobs a person is qualified for, and the table wish shows what jobs a person is interested in.
The question was to write a query that displays for each job-id, the number of applicants that are both qualified and interested to work in.
Here is the solution the teacher wrote:
Select q1.job_id , count(q1.a_id) from qualified as q1 , wish as w1 Where q1.a_id = w1.a_id and q1.job_id = w1.job_id Group by job_id;
That's all well and good, I'm not sure why we needed that "as q1" and "as w1", but i can see why it works.
And here is the solution I wrote:
SELECT job-id,COUNT(a-id) FROM job,qualified,wish WHERE (qualified.a-id=wish.a-id) GROUP BY job-id
Why is my solution wrong? And also - From which table will it select the information? Suppose I write
SELECT job-id FROM job,qualified,wish. From which table will it take the information? because
job-id exists in all 3 of these tables.