1.排序查询的练习题:
#1.查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;
#2.选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#3.查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
2.单行函数练一练
#1. 显示系统时间(注:日期+时间)
SELECT NOW();
#2. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;
#3. 将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name
FROM employees
ORDER BY 首字符;
#4. 做一个查询,产生下面的结果
<last_na