Mysql入门第三章

进阶06_连接查询sql99+92_作业

#SQL92语法连接查询
#1. 显示所有员工的姓名，部门号和部门名称。
USE myemployees;
SELECT e.`last_name`,e.`department_id`,d.`department_name` 
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;

#2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT 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 , city
SELECT 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` IS NOT NULL;

SELECT last_name,d.`department_name`,l.`location_id`,l.`city`,e.`commission_pct`FROM employees e
LEFT OUTER JOIN	departments d
ON e.`department_id` =d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE commission_pct IS NOT NULL
#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
LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
LEFT OUTER JOIN 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`
GROUP BY e.`department_id`,e.`job_id`;
#6.查询每个国家下的部门个数大于 2 的国家编号
SELECT COUNT(*) 部门个数,country_id
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`country_id`
HAVING 部门个数>2;
#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#employees Emp# manager Mgr#
#kochhar 101 king 100
SELECT emp.`last_name` employees,emp.`employee_id`'Emp#',
mgr.last_name manager, mgr.employee_id 'Mgr#'
FROM employees emp
LEFT OUTER JOIN 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
RIGHT OUTER JOIN locations l 
ON d.`location_id`=l.`location_id`
WHERE  d.`department_id` IS NULL;

#三、查询部门名为 SAL 或 IT 的员工信息
SELECT e.*,d.`department_name`,d.`department_id` FROM departments d
LEFT JOIN 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>(
SELECT AVG(salary)
FROM employees
);

#①查询平均工资
SELECT AVG(salary)
FROM employees
#②查询工资>①的员工号，姓名和工资。

SELECT last_name,employee_id,salary FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
)


#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#②连接①结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id




) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;



#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#①查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id FROM employees
WHERE last_name LIKE '%u%'


#②查询部门号=①中的任意一个的员工号和姓名
SELECT last_name,employee_id
FROM employees 
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'

);


#5. 查询在部门的location_id为1700的部门工作的员工的员工号

#①查询location_id为1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id=1700

#②查询部门号=①中的任意一个的员工号
SELECT employee_id 
FROM employees
WHERE department_id =ANY(
SELECT DISTINCT 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显示为一列，列名为 姓.名


#①查询最高工资
SELECT MAX(salary)
FROM employees
#②查询工资=①的姓.名
SELECT CONCAT(first_name,last_name)"姓名"
FROM employees
WHERE salary=(
  SELECT MAX(salary)
  FROM employees
)