SELECT字段的别名练习（答案）
编写一个SQL语句，输出下面的结果
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练习答案
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运算符练习
mysql> select * from employees where deptno<>3 and salary>15000; mysql> select * from employees where not (deptno=3 or salary<=15000);
BETWEEN练习的答案
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练习的答案
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练习答案
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)
NOT IN的坑练习答案
mysql> select deptno,dname from dept dwhere d.deptno not in (select e.deptno from emp e where e.deptno is not null);+--------+-----------+| deptno | dname |+--------+-----------+| 5 | Operation |+--------+-----------+1 row in set (0.00 sec) mysql> select deptno,dname from dept d where not exists (select 1 from emp e where d.deptno=e.deptno);+--------+-----------+| deptno | dname |+--------+-----------+| 5 | Operation |+--------+-----------+1 row in set (0.00 sec)
INSERT练习答案
insert into employees(empno,name,deptno,salary) values(17,'张小英',1,DEFAULT);
UPDATE练习答案
update employees set salary=salary*1.1 where empno in (select managerno from departments);
DELETE练习答案
delete from employees where deptno=(select deptno from departments where managerno=2);
INNER JION内连接练习答案
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;
自连接练习答案
select j1.empno from(select empno from job_history j1 where deptno=2) j1join(select empno from job_history j2 where deptno=3) j2on j1.empno=j2.empno;
外连接练习答案
select e.empno,name,start_date,j.deptno from job_history j right join employees e on j.empno=e.empno;
Union练习的答案
select name,hire_date,'创始人' 资深程度 from employees where hire_date <'2010-01-01'unionselect name,hire_date,'老员工' 资深程度 from employees where hire_date between '2010-01-01' and '2019-12-31'unionselect name,hire_date,'新员工' 资深程度 from employees where hire_date >'2019-12-31';
HAVING练习
select empno,count(*) from job_history group by empno having count(*)>1;
子查询练习答案
select name,salaryfrom employeeswhere salary>( select avg(salary) from employees 6 ); NAME SALARY---------- ----------周福生 20000王五 19100李四 23900
IN运算符中的子查询练习答案
select name from employeeswhere empno not in ( select distinct empno from job_history );
子查询和连接的练习答案
select name from employees left join job_history using (empno) where start_date is null;
exists的练习
select name from employees where not exists (select 1 from job_history where employees.empno=job_history.empno);
SELECT子句中的子查询练习答案
select dname,(select sum(salary) from employees where deptno=d.deptno) 部门工资总和 from departments d;
PARTITION BY的练习答案
select e2.*from( select e1.* , rank() over (partition by deptno order by hiredate) as rank_date from employees e1) e2where e2.rank_date=2;
CASE表达式练习的答案
select name, hiredate,case when hiredate<'2010-01-01' then '创始人' when hiredate between '2010-01-01' and '2019-12-31' then '老员工'else '新员工' end 资深程度from employeeswhere hiredate is not null;
CTE练习答案
with em_ch as( select * from employees where empno in (select distinct empno from job_history))select * from em_ch;
视图练习的答案
create view emp_qq asselect empno,name,salary,hiredate,email from employees where email like '%@qq.com'order by hiredatewith check option;
