MySQL/第七章、练习题.sql

#第七章、练习题

#1、显示系统时间（注：日期+时间）
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()
FROM DUAL;

#2、查询员工号，姓名，工资，以及工资提高20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary*1.2 AS "new salary"
FROM employees;

#3、将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LEFT(last_name,1) ,LENGTH(last_name) AS length
FROM employees
#ORDER BY LEFT(last_name,1);
ORDER BY last_name ASC;#老师做的

#4、查询员工id，last_name，salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT_WS('-',employee_id,last_name,salary) AS OUT_PUT
FROM employees;

#老师做的
SELECT CONCAT(employee_id,',',last_name,',',salary) AS "OUT_PUT"
FROM employees;

#5、查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT last_name,hire_date,DATEDIFF(NOW(),hire_date)/365 AS "workYears",DATEDIFF(NOW(),hire_date) AS "workDay"
FROM employees
ORDER BY workYears DESC;

#老师做的
SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) "worked_days",TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;

#6、查询员工姓名，hire_date，department_id，满足以下条件：
#雇佣时间在1997年之后，department_id为80或90或100，commission_pct不为空。
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN(80,90,100)
AND commission_pct IS NOT NULL
AND DATE_FORMAT(hire_date,'%Y') >= '1997';

#老师做的
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN(80,90,100)
AND commission_pct IS NOT NULL
#AND hire_date >= '1997';#也可以写成这样，但是日期最好写完整，否则容易出错
#AND hire_date >= '1997-01-01';#隐式转换
#AND DATE_FORMAT(hire_date,'%Y-%m-%d') >= '1997-01-01'; #显式转换操作，格式化：日期 ---> 字符串
#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,DATEDIFF(NOW(),hire_date) AS "workDays"
FROM employees
WHERE DATEDIFF(NOW(),hire_date) >= 10000;

#8、做一个查询，产生下面的结果：
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0),' monthly but wants ',TRUNCATE(salary*3,0)) "Dream Salary"
FROM employees;

#9、使用case-when，按照下面的条件：
/*
job				grade
AD_PRES   A
ST_MAN    B
IT_PROG   C
SA_REP    D
ST_CLERK  E
产生下面的结果
*/

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 'undefined' END "grade"
FROM employees;