midterm solution around query in SQL

问题:

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:

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