几道SQL标题

几道SQL题目
1.查出各部门员工工资最高的员工信息。

原始数据表：



结果SQL语句：
  两种解决方案
2-1 ：
  

SELECT 	 dep.name as Department , emp.name as Employee , emp.salary as Salary FROM 	tb_employee emp ,tb_employee emp1, tb_department depWHERE	emp.salary <=emp1.salary 	AND emp.departmentId = emp1.departmentId	AND emp.departmentId = dep.id GROUP BY 	dep.name,emp.name ,emp.salaryHAVING COUNT(*)<=1ORDER BY 	emp.departmentId , emp.salary desc;

2-2 :
 

select  	dep.name as Department , emp.name as Employee , emp.salary as Salary from 	tb_employee emp , tb_department dep where	emp.departmentId = dep.id and (emp.departmentId , emp.salary) in  (select departmentId , max(salary) from tb_employee group by  departmentId) ;

执行结果：



2.查找出没有购物的客户

原始数据表：



结果SQL：

SELECT 	id AS ID ,	name AS Name FROM 	tb_customers WHERE 	id NOT IN 	(	SELECT 		DISTINCT(customerId) 	FROM tb_orders	)

结果如下：



3.分数排名
原始数据表



查询SQL：

select 	s2.Score ,	(	select 		count(distinct(s1.Score)) 	from 		tb_score s1 	where 		s1.Score>=s2.Score	) as Rank from 	tb_score s2 order by 	s2.score DESC;

查询结果：