mysql 入门第二章

进阶05-03_排序+函数+分组查询_作业

#排序数据作业
#1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
USE myemployees;
SELECT last_name,department_id,salary*12 AS 年薪 FROM employees ORDER BY last_name ;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT  last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC 
#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees WHERE email   LIKE '%e%'  ORDER BY LENGTH(email) DESC , department_id ASC


#单行函数作业
#1. 显示系统时间(注：日期+时间)
  SELECT NOW();

#2. 查询员工号，姓名，工资， 以及工资提高百分之 20%后的结果（new salary）
SELECT department_id AS 员工号 , last_name AS 姓名 , salary AS 工资, salary+(salary/5) AS 'new salary' FROM employees
#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) AS 姓名的长度 FROM employees ORDER BY SUBSTR(last_name,1,1);
#4. 做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,"earns",salary,"monthly but wnats",salary*3) AS "dream salary" FROM employees;
#+-------------------------------------------------+
#| Dream Salary                                    |
#+-------------------------------------------------+
#| K_ing earns 24000.00 monthly but wants 72000.00 |
#+-------------------------------------------------+

#5. 使用 case-when，按照下面的条件：
#job 		grade
#AD_PRES 	A
#ST_MAN 		B
#IT_PROG 	C
#SA_REP 		D
#ST_CLERK 	E
#产生下面的结果
#+-----------+---------+-------+
#| last_name | job     | Grade |
#+-----------+---------+-------+
#| K_ing     | AD_PRES | A     |
#+-----------+---------+-------+

SELECT DISTINCT job_id FROM employees

SELECT job_id AS "job", CASE job_id
WHEN "AD_PRES" THEN 'A'
WHEN "ST_MAN" THEN 'B'
WHEN "IT_PROG" THEN 'C'
WHEN "SA_REP" THEN  "D"
WHEN "ST_CLERK" THEN "E"
END  AS "grade" FROM employees;
#分组查询作业
#1. 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
SELECT  job_id,MAX(salary) AS 员工工资的最大值, MIN(salary) AS 员工工资的最小值, AVG(salary) AS 员工工资的平均值, SUM(salary) AS 员工工资的总和 FROM employees GROUP BY job_id ORDER BY job_id;

#2. 查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFERENCE FROM employees
#3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS
 NOT NULL GROUP BY manager_id HAVING MIN(salary) >=6000;
#4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id
ORDER BY AVG(salary) DESC
#5. 选择具有各个 job_id 的员工人数
SELECT COUNT(*) 个数,job_id FROM employees GROUP BY job_id;

进阶05-04_分组函数_作业

#分组函数作业
#1. 查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees
#2. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))DIFFRENCE FROM employees
#3. 查询部门编号为 90 的员工个数
SELECT COUNT(1) AS '员工个数' FROM employees WHERE department_id =90```