mysql - SQL 查询显示不正确的数据

Question

您好，我有以下数据库架构

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)

我需要执行以下操作，但根据我的培训师的说法，它们是错误的。

假设这些公司可能位于多个城市。查找所有公司位于 Small Bank Corporation 所在的每个城市。

SELECT company-name, city
FROM company
UNION ALL
SELECT company-name, city
FROM company
WHERE company-name = ‘Small Bank Corporation’

找到所有收入高于所有员工平均工资的员工他们公司的。

SELECT employee-name
FROM works
WHERE salary > ( SELECT AVG(w1.salary) 
FROM works w1
INNER JOIN works w2
ON w1.company-name = w2.company-name)

找到拥有最多员工的公司。

SELECT company-name
FROM works
GROUP BY company-name
HAVING count(*) = ( SELECT count(*)
FROM works
GROUP BY company-name
ORDER BY count(*) DESC
LIMIT 1)

找到工资单最少的公司。

SELECT company-name
FROM works
GROUP BY company-name
HAVING count(*) < ( SELECT count(*)
FROM works
GROUP BY company-name
ORDER BY count(*) DESC
LIMIT 1)

Answer 1

假设这些公司可能位于多个城市。查找位于 Small Bank Corporation 所在的每个城市的所有公司。

    SELECT company-name,city FROM company 
    WHERE city IN(SELECT city  FROM company 
WHERE company-name='Small Bank Corporation');