midterm解决方案在SQL中的查询 [英] midterm solution around query in SQL

问题描述

这是数据库：

  EMPLOYEE（fmane，minit，lname，ssn，birthdate，address，sex，salary， superssn，dno）KEY：ssn 
 DEPARTMENT（dname，dnumber，mgrssn，mgrstartdate）KEY：dnumber。 
 PROJECT（pname，pnumber，plocation，dnum）KEY：pnumber。 
 WORKS_ON（essn，pno，hours）KEY：（essn，pno）
 DEPENDENT（essn，dependent-name，sex，bdate，relationship）KEY：（essn，dependent-name）
  

问题是...

提供在两个
或更多项目上工作的未婚雇员的姓氏和SSN。

  SELECT e.Lname，e.ssn 
 FROM Employee AS e 
 WHERE e.ssn IN（
 SELECT w.essn 
 FROM works_on w 
 GROUP BY w.essn 
 HAVING count（*）> = 2 
）
 AND e.ssn NOT IN（
 SELECT essn 
 FROM dependent 
 WHERE relationship ='Spouse'
）; 
  

我的回答是错误的，因为我没有使用 e.ssn NOT IN 子句。

我的问题是：

code> group by 用于？ $ AS 有是否包括？

是否可以用其他命令替换？

解决方案

1）是的。小组出现在子选择

中

 （
 select w.essn 
 from works_on w 
 group by w.essn 
 have count（*）> = 2 
）
  

$ b

2）as创建别名，在这种情况下为employee的e。这让你避免编写员工无处不在的乏味。一些SQL数据库不需要as，但它通常是一个好主意，因为它使你的意图清楚。

3）有就像一个它对group by子句的每个成员操作。 https://en.wikipedia.org/wiki/Having_%28SQL%29

This is the database:

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)     KEY: ssn
DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)               KEY: dnumber.
PROJECT  (pname, pnumber, plocation, dnum)                          KEY: pnumber.
WORKS_ON (essn, pno, hours)                                         KEY: (essn, pno)
DEPENDENT  (essn, dependent-name, sex, bdate, relationship)             KEY: (essn, dependent-name)

The question asked is...

Give the last name and SSN of the unmarried employees who work on two or more projects.

SELECT e.Lname, e.ssn
FROM Employee AS e
WHERE e.ssn IN (
        SELECT w.essn
        FROM works_on w
        GROUP BY w.essn
        HAVING count(*) >= 2
        )
    AND e.ssn NOT IN (
        SELECT essn
        FROM dependent
        WHERE relationship = 'Spouse'
        );

My answer was wrong because I didn't use the e.ssn NOT IN clause.

My questions are:

1) What is the group by used for? strictly for works on only?

2) Regarding FROM Employee AS e, is AS a typo or a command?

3) Does having mean including? Can it be replaced by some other command?

解决方案

1) Yes. The group by appears in the sub-select

(
    select  w.essn
    from   works_on w
    group by w.essn
    having count(*) >= 2
)

and is only effective there.

2) "as" creates an alias, in this case "e" for "employee". This lets you avoid the tediousness of writing employee everywhere. Some SQL databases don't require the "as" but it's generally a good idea since it makes your intention clear.

3) "Having" is like a "Where" that operates on each member of a group by clause. https://en.wikipedia.org/wiki/Having_%28SQL%29

这篇关于midterm解决方案在SQL中的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！