单行函数，聚合函数课后练习
#第七章 单行函数的课后练习

1.显示系统时间(注：日期+时间)

#以下的用哪一个都可以
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()
FROM DUAL;

2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT employee_id,last_name,salary,salary*1.2 AS newsalary
FROM employees;

3.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name ASC;
#ORDER BY last_name DESC;
#升序为ASC，降序为DESC

4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id,‘,’,last_name,‘,’,salary)OUT_PUT
FROM employees;

5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT employee_id,last_name,CURDATE(),hire_date,(CURDATE()-hire_date)/10000 “工作年数”,(CURDATE()-hire_date)/10000*365 “工作天数”
FROM employees
ORDER BY (CURDATE()-hire_date)/10000 DESC;
#上面自己写的不完全正确存在较大误差，以下是标准答案
SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 “worked_years”,DATEDIFF(CURDATE(),hire_date)“worked days”
FROM employees;

6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id

为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE commission_pct IS NOT NULL AND(CURDATE()-hire_date)/10000-24<0 AND department_id IN(80,90,110);

#以下是标准答案
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN(80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >=‘1997-01-01’ #该行存在隐式转换，可以这样写也比较简单
#可以显式转换：字符串转化为date日期或者date日期转化为字符串
#and date_format(hire_date,‘%Y’)>=‘1997’ #显式转换，格式化,日期转化为字符串
AND hire_date>=STR_TO_DATE(‘1997-01-01’,‘%Y-%m-%d’);#显式转化，解析，字符串转化为日期

7.查询公司中入职超过10000天的员工姓名、入职时间

SELECT last_name,hire_date,(CURDATE()-hire_date)/10000365
FROM employees
WHERE (CURDATE()-hire_date)/10000365>10000
#下面是标准答案
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date)>=10000

8.做一个查询，产生下面的结果

earns monthly but wants 3>
– earns monthly but wants 3>
– 例如 Dream Salary
– King earns 24000 monthly but wants 72000
#trun
SELECT CONCAT(last_name, ’ earns ', TRUNCATE(salary, 0) , ’ monthly but wants ',TRUNCATE(salary * 3, 0)) “Dream Salary”
FROM employees;
SELECT TRUNCATE(133.35567,3),TRUNCATE(5003.10,4)

9.使用case-when，按照下面的条件：

job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
#以job_id作为case选择条件
SELECT last_name , Job_id, 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’
ELSE ‘F’
END
“grade”
FROM employees
#end代表case循环查询结束，"grade"是给查询结果起的别名

#第八章 聚合函数的课后练习

#1.where子句可否使用组函数进行过滤?
#不能，group by紧跟着having

#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;

#4.选择具有各个job_id的员工人数
SELECT job_id,ROUND(SUM(salary)/AVG(salary))AS"job人数",COUNT(1),COUNT(*) #round()使其取整
FROM employees
GROUP BY job_id;

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) AS"difference"
FROM employees;

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，

没有管理者的员工不计算在内 此题重要

SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

#题目中有所有，因此要考虑外连接的问题
#下面能筛选出27行结果，写count(字段)的方式而不是count(1)或count(*)因为部门空的话也显示1
SELECT department_name,e.department_id,location_id, COUNT(employee_id), AVG(salary) avg_sal
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;

8.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT e.department_id,e.job_id,MIN(salary)
FROM employees e JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name,job_id;
#上面去掉了没有人的部门，没有人的部门最低工资为null

#下面查询的存在没有人的部门，最低工资为null,left join的话会把部门列的很全
SELECT d.department_name,job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.department_id=e.department_id
GROUP BY department_name,job_id;