#SQL92语法连接查询#1. 显示所有员工的姓名,部门号和部门名称。USE myemployees;SELECT e.`last_name`,e.`department_id`,d.`department_name`FROM employees e
LEFTOUTERJOIN departments d
ON e.`department_id`=d.`department_id`;#2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_idSELECT e.`job_id`,d.`location_id`FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`AND e.`department_id`=90#3. 选择所有有奖金的员工的last_name , department_name , location_id , citySELECT e.`last_name`,d.`department_name`,l.`location_id`,l.`city`,e.`commission_pct`FROM employees e, departments d, locations l
WHERE e.`department_id`= d.`department_id`AND d.`location_id`= l.`location_id`AND e.`commission_pct`ISNOTNULL;SELECT last_name,d.`department_name`,l.`location_id`,l.`city`,e.`commission_pct`FROM employees e
LEFTOUTERJOIN departments d
ON e.`department_id`=d.`department_id`LEFTOUTERJOIN locations l
ON d.`location_id`=l.`location_id`WHERE commission_pct ISNOTNULL#4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name SELECT e.`last_name`,e.`job_id`,d.`department_id`,d.`department_name`FROM employees e
LEFTOUTERJOIN departments d
ON e.`department_id`=d.`department_id`LEFTOUTERJOIN locations l
ON d.`location_id`=l.`location_id`WHERE l.`city`='Toronto';#5.查询每个工种、每个部门的部门名、工种名和最低工资SELECT department_name,job_title,MIN(salary)最低工资 FROM departments d,employees e,jobs j
WHERE e.`department_id`=d.`department_id`AND e.`job_id`=j.`job_id`GROUPBY e.`department_id`,e.`job_id`;#6.查询每个国家下的部门个数大于 2 的国家编号SELECTCOUNT(*) 部门个数,country_id
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`GROUPBY l.`country_id`HAVING 部门个数>2;#7、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式#employees Emp# manager Mgr##kochhar 101 king 100SELECT emp.`last_name` employees,emp.`employee_id`'Emp#',
mgr.last_name manager, mgr.employee_id 'Mgr#'FROM employees emp
LEFTOUTERJOIN employees mgr
ON emp.`manager_id`= mgr.employee_id;#一、查询编号>3 的女神的男朋友信息,如果有则列出详细,如果没有,用 null 填充SELECT*FROM beauty b,boys bo
WHERE b.`boyfriend_id`=bo.`id`AND b.`id`>3;#二、查询哪个城市没有部门SELECT city
FROM departments d
RIGHTOUTERJOIN locations l
ON d.`location_id`=l.`location_id`WHERE d.`department_id`ISNULL;#三、查询部门名为 SAL 或 IT 的员工信息SELECT e.*,d.`department_name`,d.`department_id`FROM departments d
LEFTJOIN employees e
ON d.`department_id`=e.`department_id`WHERE d.`department_name`IN('SAL','IT');SELECT*FROM departments
WHERE`department_name`IN('SAL','IT');
进阶07_子查询7_作业
#1. 查询和Zlotkey相同部门的员工姓名和工资#①查询Zlotkey的部门SELECT department_id
FROM employees
WHERE last_name='Zlotkey'#②查询部门号=①的姓名和工资SELECT last_name,salary
FROM employees
WHERE department_id =(SELECT department_id
FROM employees
WHERE last_name='Zlotkey')#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(SELECTAVG(salary)FROM employees
);#①查询平均工资SELECTAVG(salary)FROM employees
#②查询工资>①的员工号,姓名和工资。SELECT last_name,employee_id,salary FROM employees
WHERE salary>(SELECTAVG(salary)FROM employees
)#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资#①查询各部门的平均工资SELECTAVG(salary),department_id
FROM employees
GROUPBY department_id;#②连接①结果集和employees表,进行筛选SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNERJOIN(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名#①查询姓名中包含字母u的员工的部门SELECTDISTINCT department_id FROM employees
WHERE last_name LIKE'%u%'#②查询部门号=①中的任意一个的员工号和姓名SELECT last_name,employee_id
FROM employees
WHERE department_id IN(SELECTDISTINCT department_id
FROM employees
WHERE last_name LIKE'%u%');#5. 查询在部门的location_id为1700的部门工作的员工的员工号#①查询location_id为1700的部门SELECTDISTINCT department_id
FROM departments
WHERE location_id=1700#②查询部门号=①中的任意一个的员工号SELECT employee_id
FROM employees
WHERE department_id =ANY(SELECTDISTINCT department_id
FROM departments
WHERE location_id=1700);#6.查询管理者是King的员工姓名和工资#①查询姓名为king的员工编号SELECT employee_id FROM employees
WHERE last_name ='K_ing'#②查询哪个员工的manager_id = ①SELECT last_name,salary
FROM employees
WHERE manager_id IN(SELECT employee_id
FROM employees
WHERE last_name ='K_ing');#7.查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为 姓.名#①查询最高工资SELECTMAX(salary)FROM employees
#②查询工资=①的姓.名SELECT CONCAT(first_name,last_name)"姓名"FROM employees
WHERE salary=(SELECTMAX(salary)FROM employees
)