Display students who enrolled in this date and students who graduated this year. Return by order with most recent enrollment date first

Question

Hello it's my first time using stackoverflow and I am trying to learn databases.

I need to create a report of data in the student table.

Display all students who enrolled on or after August 1, 2020.

Display all students who graduated in 2020.

Return the result in order of enrollment, with the most recent enrollment date first.

I have 4 choices of queries and I need an explanation on why is that the correct answer.

A. SELECT * FROM student WHERE enrollment_date >= '2020-08-01' OR academic_status='Graduated' OR graduation_date >= '2020-01-01' ORDER BY enrollment_date DESC

B. SELECT * FROM student WHERE enrollment_date >= '2020-08-01' OR academic_status='Graduated' AND graduation_date >= '2020-01-01' ORDER BY enrollment_date DESC

C. SELECT * FROM student WHERE enrollment_date >= '2020-08-01' AND academic_status='Graduated' AND graduation_date >= '2020-01-01' ORDER BY enrollment_date ASC

D. SELECT * FROM student WHERE enrollment_date >= '2020-08-01' AND academic_status='Graduated' OR graduation_date >= '2020-01-01' ORDER BY enrollment_date

If I have to guess, it should be in a DESC order for the most recent date. But i am still not sure on what the answer should be is.

Thank you!

Answer 1

You will likely need parenthesis around WHERE clauses. Based on the limited requirements you indicate, I would write

SELECT * FROM student
WHERE (enrollement_date >= '2020-08-01') 
  OR (academic_status = 'Graduated' 
    AND graduation_date BETWEEN '2020-01-01' AND '2020-12-31') 
ORDER BY enrollement_date DESC

• people who entrolled on or after 08/01/2020,
• plus people who graduated in the calendar year of 2020
• display them with the most recent enrolled persons first