user3186023 user3186023 - 7 months ago 9
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:

https://gyazo.com/0049e5162c9e40418a81d5f784655926

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 (
SELECT P.age
FROM Professor P, Enroll E, Course C
WHERE P.name = E.inst_name AND C.cno = E.cno AND C.title = "Database Systems"
AND E.enrollment BETWEEN 2000 and 2009
)
SELECT "Yes"
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

With aggregate function you shoud use having

 SELECT "Yes"
 FROM dbsProfs
 HAVING MAX(dbsProfs.age) - MIN(dbsProfs.age) <= 5
Comments