I have designed a schema and generated a few SQL queries. I'm using PostgreSQL.
CREATE TABLE train ( train_code SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE journey ( journey_id SERIAL PRIMARY KEY, int INTEGER, train_code REFERENCES train(train_code) ); CREATE TABLE price ( journey_id REFERENCES journey(journey_id), price INTEGER );
Give me all train names that commence from train_code NYC or SFO that are priced $50 or more. (assume the price table is in $ already).
SELECT train.name JOIN train JOIN journey on train.train_code = journey.train_code JOIN price on price.journey_id = journey.journey_id WHERE price.price >= 50 AND journey.train_code IN ('NYC', 'SFO') AND journey.int = 1 -- (first train of the journey)
I am unsure how to convert this into a relational algebra and/or calculus query. I have looked at a few converters e.g. http://dbis-uibk.github.io/relax/calc.htm but in this calculator 'join' for example and 'in' is not allowed.