[数据库笔记] SQL练习6 - 10

6.分组查询

（1）查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;

（2）查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）

SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) difference
FROM employees;

（3）查询部门编号为 90 的员工个数

SELECT COUNT(1)
FROM employees
WHERE department_id = 90;

7.SQL99语法连接查询

（1）查询哪个城市没有部门

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

（2）查询部门名为 SAL 或 IT 的员工信息

SELECT *
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND department_name = "SAL" OR department_name="IT";

8.SQL92语法连接查询

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

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

（2）查询 90 号部门员工的 job_id 和 90 号部门的 location_id

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

（3）选择所有有奖金的员工的last_name , department_name , location_id , city

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

（4）选择city在Toronto工作的员工的last_name , job_id , department_id , department_name

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

（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 COUNT(*) > 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";

9.子查询

（1）查询和 Zlotkey 相同部门的员工姓名和工资

SELECT department_id,last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id FROM employees WHERE last_name = 'Zlotkey'
);

（2）查询工资比公司平均工资高的员工的员工号，姓名和工资

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

（3）查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

-- 1.查询各部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

-- 2.连接1结果集和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 的员工在相同部门的员工的员工号和姓名

-- 1.查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE "%u%";

-- 2.
SELECT employee_id, last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE "%u%"
) 
;

（5）查询在部门的 location_id 为 1700 的部门工作的员工的员工号

-- 1.查询部门的 location_id 为 1700 的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

-- 2.连接1结果集和employees表，进行筛选
SELECT employee_id
FROM employees
WHERE department_id = ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);

（6）查询管理者是 King 的员工姓名和工资

-- 1.查询姓名为K_ing的员工编号
SELECT employee_id
FROM employees
WHERE last_name = "K_ing";


-- 2.查询哪个员工的manager_id = 1.
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  CONCAT(first_name, '.' ,last_name) "姓.名"
FROM employees
WHERE salary = (
	SELECT MAX(salary) FROM employees
);

10.数据处理

（1）运行以下脚本创建表 my_employees

Create table my_employees(
	Id int(10),
	First_name varchar(10),
	Last_name varchar(10),
	Userid varchar(10),
	Salary double(10,2) 
);
create table users(
	id int,
	userid varchar(10),
	department_id int
);

（2）显示表 my_employees 的结构

（3）向 my_employees 表中插入下列数据

ID 	FIRST_NAME 		LAST_NAME 		USERID 		SALARY
1 		patel 		Ralph 			Rpatel 		895
2 		Dancs 		Betty 			Bdancs 		860
3 		Biri 		Ben 			Bbiri 		1100
4 		Newman 		Chad 			Cnewman 	750
5 		Ropeburn 	Audrey 			Aropebur 	1550

INSERT INTO my_employees(ID, FIRST_NAME, LAST_NAME, USERID, SALARY) 
VALUES (1 ,'patel', 'Ralph', 'Rpatel', 895),
(2, 'Dancs', 'Betty', 'Bdancs', 860),
(3, 'Biri', 'Ben', 'Bbiri', 1100),
(4, 'Newman', 'Chad', 'Cnewman', 750),
(5, 'Ropebum', 'Audrey', 'Aropebur', 1550)
;

（4）向 users 表中插入数据

1 		Rpatel 			10
2 		Bdancs 			10
3 		Bbiri 			20
4 		Cnewman 		30
5 		Aropebur 		40

INSERT INTO users
VALUES (1 , 'Rpatel', 10),
(2, 'Bdancs', 10),
(3, 'Bbiri', 20),
(4, 'Cnewman', 30),
(5, 'Aropebur', 40)
;

（5）将 3 号员工的 last_name 修改为“drelxer”

UPDATE my_employees SET last_name = "drelxer" WHERE Id = 3;

（6）将所有工资少于 900 的员工的工资修改为 1000

UPDATE my_employees SET Salary = 1000 WHERE Salary < 900;

（7）将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除

DELETE u, e
FROM users u
JOIN my_employees e ON u.`userid`=e.Userid
WHERE u.`userid` = "Bbiri";

（8）删除所有数据

DELETE FROM my_employees;
DELETE FROM users;

（9）检查所作的修正

SELECT * FROM my_employees;
SELECT * FROM users;

（10）清空表 my_employees

TRUNCATE TABLE my_employees;