user3186023 user3186023 - 1 year ago 56
SQL Question

SQL Query outputting "yes"

I'm studying for my Database System exam tomorrow, and I'm working on an SQL question. This question is the only one from the paper that doesn't have an answer, but here's the question:

Question 4 was fine, but for question 5, I amn't sure my approach is right, since we don't exactly want to output something from the table. Note that I think enrollment corresponds to when the instructor started teaching the course (which isn't the usual definition AFAIK).

My approach is as follows:

WITH dbsProfs AS (
FROM Professor P, Enroll E, Course C
WHERE = E.inst_name AND C.cno = E.cno AND C.title = "Database Systems"
AND E.enrollment BETWEEN 2000 and 2009
FROM dbsProfs
WHERE MAX(dbsProfs.age) - MIN(dbsProfs.age) <= 5

I'm fairly confident with my temporary table. I'm doing a join on all 3 tables and filtering out to include only the ones relevant to my query. It's the other half I'm unsure about.

Any insight on whether this is correct/how to correct this would be much appreciated. I amn't convinced
WHERE MAX(dbsProfs.age) - MIN(dbsProfs.age) <= 5
is valid SQL

Answer Source

With aggregate function you shoud use having

 FROM dbsProfs
 HAVING MAX(dbsProfs.age) - MIN(dbsProfs.age) <= 5
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download