--1.谁的工资比 Abel 高?select*from employees where salary>(select salary from employees where last_name='Abel');--2.返回job_id与141号员工相同,salary比143号员工多的员工的姓名,job_id和工资select last_name,job_id,salary from employees where job_id=(select job_id from employees where employee_id=141)and salary>(select salary from employees where employee_id=143);--3.返回公司工资最少的员工的last_name,job_id和salaryselect last_name,job_id,salary from employees where salary=(SELECTmin(salary)from employees );--4.查询最低工资大于50号部门最低工资的部门id和其最低工资select department_id,min(salary)from employees GROUPBY department_id HAVINGmin(salary)>(selectmin(salary)from employees where department_id=50);--5.返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salaryselect employee_id,last_name,job_id,salary from employees where salary<any(selectDISTINCT salary from employees where job_id='IT_PROG')and job_id!='IT_PROG';--6.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salaryselect employee_id,last_name,job_id,salary from employees where salary<all(selectDISTINCT salary from employees where job_id='IT_PROG')and job_id!='IT_PROG';--7.查询员工编号最小并且工资最高的员工信息select*from employees where employee_id=(selectmin(employee_id)from employees)and salary=(selectmax(salary)from employees);selectmin(employee_id),max(salary)from employees;select*from employees where(employee_id,salary)=(selectmin(employee_id),max(salary)from employees);--8.查询每个部门信息和员工个数select d.*,(selectcount(*)from employees e where e.department_id=d.department_id
)from departments d;select d.*,(selectcount(*)from employees )from departments d GROUPBY d.department_id;--9.查询每个部门的平均工资的工资等级#select salary,grade_level from employees e join job_grades on salary BETWEEN lowest_sal and highest_salselect grade_level,ag.department_id from(selectavg(salary) pjgz,department_id from employees GROUPBY department_id
) ag,job_grades where ag.pjgz between lowest_sal and highest_sal;***select grade_level,ag.department_id from(selectavg(salary) pjgz,department_id from employees GROUPBY department_id
) ag innerjoin job_grades on ag.pjgz between lowest_sal and highest_sal;--10.查询有员工的部门名#in:select department_name from departments where department_id in(select department_id from employees );#exists:select department_name from departments whereexists(select*from employees e where e.department_id=departments.department_id
);--11.查询没有女朋友的男神信息#inSELECT bo.*FROM boys bo
WHERE bo.id NOTIN(SELECT boyfriend_id
FROM beauty
);#existsSELECT bo.*FROM boys bo
WHERENOTEXISTS(SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`);