SQL Explorer - 莫莉

莫莉

no such table: employees

Title

Connection

Description

SQL

-- 1.基础排序
-- 查询员工表（employees）中所有薪资（salary）大于10000且部门编号（dept_id）为5的员工姓名和入职日期
select name,date
from employees
where salary >10000 and dept_id=5;

-- 2.聚合与分组过滤
-- 统计每个部门的平均薪资，并仅显示平均薪资高于15000的部门
select dept_id,AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING avg_salary>150000;

-- 3.多表链接
-- 查询员工姓名及其所属的部门名称。已知员工表（employees）有部门ID（dept_id），部门信息在部门表（departments）中
SELECT e.name,d.dept_name
from employees e
join departments on e.dept_id=d.id;

-- 4.多表连接但以左表为基准
-- 查询没有订单的客户信息。客户表（customers），订单表（orders）
select c.id,c.name
from customers c
left join orders o on c.id=o.customer_id
where o.customer_id is null;

-- 5.窗口函数排名
-- 取出每个部门排名前三的员工姓名和薪资（因为where在select之前执行，所以要借助with..as来定义一个临时表
with ranked_employees as (
  select 
    name,salary,dept_id,
    dense_rank() OVER (PARTITION BY dept_id order by salary desc) as rank_num
  from employees
)
select name,salary,dept_id
from ranked_employees
where rank_num <=3;

-- 6.窗口函数排序和平均值
-- 计算每位员工的部门内工资排名和部门平均工资 
select 
  employee_id,
  name，
  department,
  salary,
  row_number ()over (partition by department oder by salary desc)as dept_salary_rank,
  avg(salary)over (partition by deparyment) as dept_avg_salary
from
 employees
order by 
 department,dept_salary_rank；

Assistant Example

SQL Assistant