艰难理解这个数据库查询

Question

艰难理解这个数据库查询

4

我是斯坦福大学关系代数课程的学习者，但我在理解其中一个问题时遇到了困难，尽管我已经得到了答案。

以下是模式：

/* Delete the tables if they already exist */
drop table if exists Person;
drop table if exists Frequents;
drop table if exists Eats;
drop table if exists Serves;

/* Create the schema for our tables */
create table Person(name text, age int, gender text);
create table Frequents(name text, pizzeria text);
create table Eats(name text, pizza text);
create table Serves(pizzeria text, pizza text, price decimal);

/* Populate the tables with our data */
insert into Person values('Amy', 16, 'female');
insert into Person values('Ben', 21, 'male');
insert into Person values('Cal', 33, 'male');
insert into Person values('Dan', 13, 'male');
insert into Person values('Eli', 45, 'male');
insert into Person values('Fay', 21, 'female');
insert into Person values('Gus', 24, 'male');
insert into Person values('Hil', 30, 'female');
insert into Person values('Ian', 18, 'male');

insert into Frequents values('Amy', 'Pizza Hut');
insert into Frequents values('Ben', 'Pizza Hut');
insert into Frequents values('Ben', 'Chicago Pizza');
insert into Frequents values('Cal', 'Straw Hat');
insert into Frequents values('Cal', 'New York Pizza');
insert into Frequents values('Dan', 'Straw Hat');
insert into Frequents values('Dan', 'New York Pizza');
insert into Frequents values('Eli', 'Straw Hat');
insert into Frequents values('Eli', 'Chicago Pizza');
insert into Frequents values('Fay', 'Dominos');
insert into Frequents values('Fay', 'Little Caesars');
insert into Frequents values('Gus', 'Chicago Pizza');
insert into Frequents values('Gus', 'Pizza Hut');
insert into Frequents values('Hil', 'Dominos');
insert into Frequents values('Hil', 'Straw Hat');
insert into Frequents values('Hil', 'Pizza Hut');
insert into Frequents values('Ian', 'New York Pizza');
insert into Frequents values('Ian', 'Straw Hat');
insert into Frequents values('Ian', 'Dominos');

insert into Eats values('Amy', 'pepperoni');
insert into Eats values('Amy', 'mushroom');
insert into Eats values('Ben', 'pepperoni');
insert into Eats values('Ben', 'cheese');
insert into Eats values('Cal', 'supreme');
insert into Eats values('Dan', 'pepperoni');
insert into Eats values('Dan', 'cheese');
insert into Eats values('Dan', 'sausage');
insert into Eats values('Dan', 'supreme');
insert into Eats values('Dan', 'mushroom');
insert into Eats values('Eli', 'supreme');
insert into Eats values('Eli', 'cheese');
insert into Eats values('Fay', 'mushroom');
insert into Eats values('Gus', 'mushroom');
insert into Eats values('Gus', 'supreme');
insert into Eats values('Gus', 'cheese');
insert into Eats values('Hil', 'supreme');
insert into Eats values('Hil', 'cheese');
insert into Eats values('Ian', 'supreme');
insert into Eats values('Ian', 'pepperoni');

insert into Serves values('Pizza Hut', 'pepperoni', 12);
insert into Serves values('Pizza Hut', 'sausage', 12);
insert into Serves values('Pizza Hut', 'cheese', 9);
insert into Serves values('Pizza Hut', 'supreme', 12);
insert into Serves values('Little Caesars', 'pepperoni', 9.75);
insert into Serves values('Little Caesars', 'sausage', 9.5);
insert into Serves values('Little Caesars', 'cheese', 7);
insert into Serves values('Little Caesars', 'mushroom', 9.25);
insert into Serves values('Dominos', 'cheese', 9.75);
insert into Serves values('Dominos', 'mushroom', 11);
insert into Serves values('Straw Hat', 'pepperoni', 8);
insert into Serves values('Straw Hat', 'cheese', 9.25);
insert into Serves values('Straw Hat', 'sausage', 9.75);
insert into Serves values('New York Pizza', 'pepperoni', 8);
insert into Serves values('New York Pizza', 'cheese', 7);
insert into Serves values('New York Pizza', 'supreme', 8.5);
insert into Serves values('Chicago Pizza', 'cheese', 7.75);
insert into Serves values('Chicago Pizza', 'supreme', 8.5);

我很难理解第6个问题。问题的内容是：“找到所有只被24岁以下人吃或在它们被提供的所有地方都少于10美元的披萨。”现在，我理解这个查询。它基本上分为两部分：

1.由24岁以下人吃的披萨 2.价格低于10美元的披萨

我甚至理解答案，即（所有提供的披萨与价格大于等于10美元的披萨之间的差异）和（所有人吃的披萨与年龄大于等于24岁的人所吃的披萨之间的差异）的联合。

(\project_{pizza} (Serves) \diff \project_{pizza} (\select_{price>='10'} Serves))
\union
(\project_{pizza} (Eats) \diff \project_{pizza} (\select_{age>='24'} Person \join Eats))

然而，我不明白为什么相反的查询没有给出相同的答案，即(pizzas价格低于10美元)和(被24岁以下人吃的披萨)的联合。

(\project_{pizza} (\select_{price<'10'} Serves))
\union
(\project_{pizza} (\select_{age<'24'} Person \join Eats))

为什么第二个查询的答案与第一个不同。在我看来，它们应该都给出相同的答案。

感谢所有回复。

- philosopher

将people表与pizza表进行LEFT JOIN，然后WHERE筛选年龄小于24岁且成本小于$10的记录。 - Matt The Ninja

嘿，忍者Matt，你能否详细说明一下？我对逻辑比查询本身更感兴趣。谢谢！ - philosopher

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Terje D. · Accepted Answer

您的第一个查询是查找价格不超过$10任何地方，或者没有被24岁或以上的人吃掉的披萨。

而您的最后一个查询是查找在某些地方以低于$10的价格出售，或者被一些年龄低于24岁的人吃掉的披萨。

这些查询将针对在某些地方以低于$10的价格销售，而在其他地方以超过$10的价格销售，或者被一些年轻人和一些老年人吃掉的披萨产生不同的结果。