SELECT字段的别名练习（答案）
编写一个SQL语句，输出下面的结果
代码语言：javascript复制mysql> select empno 员工号,salary 月薪, salary*14 14薪 from employees;
----------- ---------- -----------
| 员工号 | 月薪 | 14薪 |
----------- ---------- -----------
| 1 | 20000.00 | 280000.00 |
| 2 | 19100.00 | 267400.00 |
| 3 | 23900.00 | 334600.00 |
| 4 | 15000.00 | 210000.00 |
| 5 | 14200.00 | 198800.00 |
| 6 | 9700.00 | 135800.00 |
| 7 | 8900.00 | 124600.00 |
| 8 | 14900.00 | 208600.00 |
| 9 | 15000.00 | 210000.00 |
----------- ---------- -----------
9 rows in set (0.00 sec)WHERE练习答案
代码语言：javascript复制mysql> select name,salary,salary*1.1 "updated salary",hire_date from employees where hire_date<'2010-01-01';
----------- ---------- ---------------- ------------
| name | salary | updated salary | hire_date |
----------- ---------- ---------------- ------------
| 周福生 | 20000.00 | 22000.000 | 2009-12-02 |
| 赵卫华 | 15000.00 | 16500.000 | 2009-11-12 |
----------- ---------- ---------------- ------------
2 rows in set (0.00 sec)AND、OR 和NOT运算符练习
代码语言：javascript复制mysql> select * from employees where deptno<>3 and salary>15000;
mysql> select * from employees where not (deptno=3 or salary<=15000);BETWEEN练习的答案
代码语言：javascript复制mysql> select name,salary from employees where salary between 10000 and 15000;
----------- ----------
| name | salary |
----------- ----------
| 赵六 | 15000.00 |
| 李明 | 14200.00 |
| 程娟 | 14900.00 |
| 赵卫华 | 15000.00 |
----------- ----------
4 rows in set (0.00 sec)LIKE练习的答案
代码语言：javascript复制mysql> select email from employees where email like '_h%@gmail.com';
----------------------
| email |
----------------------
| zhou@gmail.com |
| chengjuan@gmail.com |
| zhaoweihua@gmail.com |
----------------------
3 rows in set (0.00 sec)ORDER BY练习答案
代码语言：javascript复制mysql> select name,deptno,hire_date from employees order by deptno,hire_date desc;
----------- -------- ------------
| name | deptno | hire_date |
----------- -------- ------------
| 赵六 | 1 | 2019-12-01 |
| 王五 | 1 | 2013-01-03 |
| 赵卫华 | 1 | 2009-11-12 |
| 孙军 | 2 | 2022-05-22 |
| 李四 | 2 | 2011-02-10 |
| 周福生 | 2 | 2009-12-02 |
| 李明 | 3 | 2021-09-11 |
| 钱杰 | 3 | 2019-06-12 |
| 程娟 | 3 | 2013-07-22 |
----------- -------- ------------
9 rows in set (0.00 sec)INSERT练习答案
代码语言：javascript复制insert into employees(empno,name,deptno,salary) values(17,'张小英',1,DEFAULT);UPDATE练习答案
代码语言：javascript复制update employees set salary=salary*1.1
where empno in
(select managerno from departments);DELETE练习答案
代码语言：javascript复制delete from employees where deptno=(select deptno from departments where managerno=2);
INNER JION内连接练习答案
代码语言：javascript复制select j.*,e.name,d.dname from job_history j join
employees e on j.empno=e.empno join departments d
on e.deptno=d.deptno;自连接练习答案
代码语言：javascript复制select j1.empno from
(select empno from job_history j1 where deptno=2) j1
join
(select empno from job_history j2 where deptno=3) j2
on j1.empno=j2.empno;外连接练习答案
代码语言：javascript复制select e.empno,name,start_date,j.deptno
from job_history j right join employees e
on
j.empno=e.empno;Union练习的答案
代码语言：javascript复制select name,hire_date,'创始人' 资深程度 from employees where hire_date <'2010-01-01'
union
select name,hire_date,'老员工' 资深程度 from employees where hire_date between '2010-01-01' and '2019-12-31'
union
select name,hire_date,'新员工' 资深程度 from employees where hire_date >'2019-12-31';HAVING练习
代码语言：javascript复制 select empno,count(*) from job_history group by empno having count(*)>1;子查询练习答案
代码语言：javascript复制select name,salary
from employees
where salary>(
select avg(salary)
from employees
6 );
NAME SALARY
---------- ----------
周福生 20000
王五 19100
李四 23900IN运算符中的子查询练习答案
代码语言：javascript复制select name from employees
where empno not in (
select distinct empno
from job_history
);子查询和连接的练习答案
代码语言：javascript复制 select name from employees left join job_history using (empno) where start_date is null;exists的练习
代码语言：javascript复制select name from employees
where not exists
(select 1 from job_history where employees.empno=job_history.empno);SELECT子句中的子查询练习答案
代码语言：javascript复制select dname,(select sum(salary) from employees where deptno=d.deptno) 部门工资总和 from departments d;PARTITION BY的练习答案
代码语言：javascript复制select e2.*
from
(
select e1.* ,
rank() over (partition by deptno order by hiredate) as rank_date
from employees e1
) e2
where e2.rank_date=2;CASE表达式练习的答案
代码语言：javascript复制select name, hiredate,
case
when hiredate<'2010-01-01' then '创始人'
when hiredate between '2010-01-01' and '2019-12-31' then '老员工'
else '新员工' end 资深程度
from employees
where hiredate is not null;CTE练习答案
代码语言：javascript复制with em_ch as
( select * from employees
where empno in
(select distinct empno from job_history)
)
select * from em_ch;视图练习的答案
代码语言：javascript复制create view emp_qq as
select empno,name,salary,hiredate,email from employees
where email like '%@qq.com'
order by hiredate
with check option;
