1.sql92查询

#1、显示所有员工的姓名，部门号和部门名称。

SELECT last_name,e.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;

#2、查询 90 号部门员工的 job_id 和 90 号部门的 location_id

SELECT job_id,location_id,d.department_id
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`department_id`=90;

#3、选择所有有奖金的员工的

#last_name , department_name , location_id , city
SELECT last_name,department_name,l.location_id,city
FROM employees e,locations l,departments d
WHERE l.`location_id`=d.`location_id`
AND d.`department_id`=e.`department_id`
AND e.commission_pct IS NOT NULL;

#4、选择city在Toronto工作的员工的

#last_name , job_id , department_id , department_name
SELECT last_name , job_id , d.department_id , department_name
FROM locations l,departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND d.`location_id`=l.`location_id`
AND city='Toronto';

#5、查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;

#6、查询每个国家下的部门个数大于 2 的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE  d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数 >2;

#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

#employees   Emp#    manager   Mgr#
#kochhar      101      king    100
SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.`manager_id`=m.employee_id
AND e.`last_name`='kochhar';

sql99

（1）等值连接

#1、查询员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

#2、查询名字中包含e的员工名和工种名

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';

#3、查询部门个数>3的城市名和部门个数

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING 部门个数>3;

#4、查询那个部门的员工个数>3的部门名和员工个数，并按个位数降序

SELECT department_name,COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING  员工个数 >3
ORDER BY 员工个数 DESC;

#5.查询员工名、部门名、工种名、并按照部门名降序

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;

（2）外连接（查询一个表中有另一个没有）

#1、查询男朋友不在男神表的女神名

SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

#2.查询那个部门没有员工

SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE  e.`employee_id` IS NULL;

#3、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充

SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;

#4、查询哪个城市没有部门

SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;

#5、查询部门名为 SAL 或 IT 的员工信息、

SELECT d.department_name,e.*
FROM departments d
RIGHT OUTER JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE d.`department_name`='SAL' OR d.`department_name`='IT';