使用设置代数的SELECT语句 [英] SELECT statement using set Algebra

问题描述

具有以下两个表:

部门

DNAME
-----------
RESEARCH
   IT
 SCIENCE

DEPTLOC

DNAME       LOCATION
----------------------
RESEARCH      BOSTON
   IT         LONDON
RESEARCH      DALLAS
 SCIENCE      BOSTON

我的问题是要找到仅位于波士顿或伦敦却不在两个城市都位于的DNAME.

my question is to find the DNAME where only located either in BOSTON or LONDON but not in both cities.

我正在使用以下查询:

SELECT D.DNAME 
FROM DEPARTMENT D 
INNER JOIN DEPTLOC L ON L.DNAME = D.DNAME 
WHERE L.CITY='BOSTON' OR L.CITY='DALLAS'
GROUP BY D.DNAME
HAVING COUNT(1) = 1;

它有效，但我认为问题不正确:

It works but I don't think it is correct as my question stated:

"Implementing set algebra SELECT statements"

那么，为了使用正确的语句获得正确的结果，我应该改变什么? 输出应为:

So what should I change in order to get the correct result using the right statement? Output should be like:

   DNAME
-----------
    IT
  SCIENCE

推荐答案

如果我在哪里猜，您会被要求提供使用诸如UNION，INTERSECT和DIFFERENCE (MINUS or EXCEPT)之类的集合操作的解决方案.如果A是位于波士顿的部门，而B是位于伦敦的部门，那么(A-B)U(B-A)是一种解决方案

If I where to guess you are asked to provide a solution using set operations like UNION, INTERSECT and DIFFERENCE (MINUS or EXCEPT). If A is departments located in Boston and B is departments located in London then (A - B) U (B - A) is one solution

这篇关于使用设置代数的SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！