SQL打卡4.10

今天的题比较简单

[181. 超过经理收入的员工]

方法一：子查询

# Write your MySQL query statement below
select
    a.name Employee 
from 
    Employee a
where 
    a.salary > (
    select 
        b.salary 
    from 
        Employee b
    where 
        a.managerId = b.id);

方法二：

select
    a.Name as 'Employee'
from
    Employee as a,
    Employee as b
where
    a.ManagerId = b.Id
        and a.Salary > b.Salary

[182. 查找重复的电子邮箱]

# Write your MySQL query statement below
select 
    email 
from 
    Person
group by 
    email
having
    count(email) > 1;

记得顺序：where>group by>having>order by 

[183. 从不订购的客户]

select c.name Customers 
from Customers c 
where c.id 
not in (select customerid from orders);

[184. 部门工资最高的员工]

# Write your MySQL query statement below
SELECT
	d.NAME AS Department,
	e.NAME AS Employee,
	Salary 
FROM
	Employee e,
	Department d 
WHERE
	e.DepartmentId = d.Id 
	AND ( e.DepartmentId, Salary ) 
    IN (SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId )

[196. 删除重复的电子邮箱]

# Write your MySQL query statement below
DELETE p1 
FROM Person p1, Person p2
WHERE p1.Email = p2.Email 
AND p1.Id > p2.Id