CODEWITHSUNDEEP
mysqlsqlrelational-algebra
Mo T
Mo T

Reputation: 450

relational algebra to SQL

Having this schema :

Doctor(license_no, doctor_name, specialty)

Patient(pat_id, pat_name, pat_address, pat_phone, date_of_birth)

Visit(license_no, pat_id, date_of_visit, type, diagnosis, charge)

I have to translate Patient (join sign) pat_id(date>=1-1-2005^ date<1-1-2006(Visit)) and I thought it means to select patients who visited a doctor between 1/1/2005 and 1/1/2006 so I made this query:

SELECT p.pat_ID FROM Patient p INNER JOIN Visit v 
ON (p.pat_ID = v.pat_ID)
WHERE date_of_visit <= '01-01-2005' AND date_of_visit < '01-01-2006'

Does the relational algebra statement mean what I thought and the SQL query returns the right result?

Upvotes: 0

Views: 3084

Answers (1)

JYelton
JYelton

Reputation: 36512

When specifying a date range for a WHERE condition, you have the right syntax, but your first operator looks incorrect. (>= not <=).

If you need to select records between two dates including the start date but excluding the end date (known as closed-open representation), the syntax should be as follows:

...
WHERE date_of_visit >= date_start AND date_of_visit < date_end

If you need to select records between two dates excluding those dates, the syntax should be as follows:

...
WHERE date_of_visit > date_start AND date_of_visit < date_end

If you want to select records between two dates including those dates, you should use:

...
WHERE date_of_visit >= date_start AND date_of_visit <= date_end

The greater-than and equals operators allow you to specify whether you want the date range to be inclusive of the boundary dates or not.

Upvotes: 1

Related Questions