user3186023 - 11 months ago 31

SQL Question

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`

Answer

With aggregate function you shoud use having

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