Skip to content

Instantly share code, notes, and snippets.

@sleepypioneer
Last active March 28, 2018 12:58
Show Gist options
  • Save sleepypioneer/f749838e27de9c0b5857ef57655fae30 to your computer and use it in GitHub Desktop.
Save sleepypioneer/f749838e27de9c0b5857ef57655fae30 to your computer and use it in GitHub Desktop.
RA query to find all pizzerias that serve every pizza eaten by people over 30. https://stanford.io/2GkWfPx
/* Flawed as assumes 2 pizzas eaten by those over 30 should be free from this */
(\project_{pizzeria}(
\rename_{pizza}( // renames as Pizza so they can then be joined with Serves to collect the Pizzeria
\project_{pizza1}( // take only first pizza
\select_{pizza1 < pizza2}(( // gives one column for pizza 1 and a collumn for pizza 2
\rename_{pizza1}( // need to rename to be able to do cross product
\project_{pizza}((
\project_{pizza}(( // Select pizzas from table with pizzas eaten by people over 30
\select_{age > 30}Person)
\join Eats
)
)
\join(
\project_{pizza, pizzeria}Serves
)
)
)
)
\cross( // cross product to take only the pizzas eaten by those over 30
\rename_{pizza2}(
\project_{pizza}((
\project_{pizza}((
\select_{age > 30}Person)
\join Eats
)
)
\join(
\project_{pizza, pizzeria}Serves
)
)
)
))
)
)
\join
Serves
))
\intersect // find intersect of pizzerias serving pizza 1 and pizza 2
(\project_{pizzeria}(
\rename_{pizza}(
\project_{pizza2}( // take only second pizza
\select_{pizza1 < pizza2}((
\rename_{pizza1}(
\project_{pizza}((
\project_{pizza}((
\select_{age > 30}Person)
\join Eats
)
)
\join(
\project_{pizza, pizzeria}Serves)
)
)
)
\cross(\rename_{pizza2}(
\project_{pizza}((
\project_{pizza}((
\select_{age > 30}Person)
\join Eats
)
)
\join (
\project_{pizza, pizzeria}Serves)
)
))
)
)
)
\join Serves
))
/* version with spaces taken out to fit under 1000 characters */
(\project_{pizzeria}(\rename_{pizza}(\project_{pizza1}(\select_{pizza1 < pizza2}((\rename_{pizza1}(\project_{pizza}((\project_{pizza}((\select_{age > 30}Person)\join Eats))\join(\project_{pizza, pizzeria}Serves))))\cross(\rename_{pizza2}(\project_{pizza}((\project_{pizza}((\select_{age > 30}Person) \join Eats))\join(\project_{pizza, pizzeria}Serves)))))))\join Serves))
\intersect
(\project_{pizzeria}(\rename_{pizza}(\project_{pizza2}(\select_{pizza1 < pizza2}((\rename_{pizza1}(\project_{pizza}((\project_{pizza}((\select_{age > 30}Person)\join Eats))\join(\project_{pizza, pizzeria}Serves))))\cross(\rename_{pizza2}(\project_{pizza}((\project_{pizza}((\select_{age > 30}Person)\join Eats))\join (\project_{pizza, pizzeria}Serves)))))))\join Serves))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment