2021-09-01

1、SELECT NOW();

2、SELECT employee_id,CONCAT(first_name,last_name),salary,salary*1.2 AS ‘new salary’ FROM employees;

3、SELECT CONCAT(first_name,last_name),LENGTH(CONCAT(first_name,last_name)) FROM employees ORDER BY SUBSTR(CONCAT(first_name,last_name));

4、SELECT last_name,'earns',salary,'monthly but wants',salary*3 FROM employees;

5、SELECT last_name,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;

6、SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees GROUP BY job_id ORDER BY job_id;

7、SELECT MAX(salary)-MIN(salary) AS DIFFERENCE FROM employees;

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

9、SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC;

10、SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFRENCE FROM employees;

11、SELECT department_id,COUNT(*) FROM employees GROUP BY department_id HAVING department_id=90