学通4中数据库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)

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)

mysql>  select * from employees where deptno<>3 and salary>15000;
mysql>  select * from employees where not (deptno=3 or salary<=15000);

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)

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)

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)

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 into employees(empno,name,deptno,salary) values(17,'张小英',1,DEFAULT);

update employees set salary=salary*1.1 where empno in (select managerno from departments);

delete from employees where deptno=(select deptno from departments where managerno=2);

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;

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';

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

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;

select name from employees where not exists   (select 1 from job_history where employees.empno=job_history.empno);

select dname,(select sum(salary) from employees where deptno=d.deptno) 部门工资总和  from departments d;

select e2.*from(   select e1.* ,   rank() over (partition by deptno order by hiredate) as rank_date   from employees e1)  e2where e2.rank_date=2;

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;

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;