学通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 d
where 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) j1
join
(select empno from job_history j2 where deptno=3) j2
on 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'
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';

 select empno,count(*) from job_history group by empno having count(*)>1;

select name,salary
from employees
where salary>(
            select avg(salary)
            from employees
  6              );


NAME           SALARY
---------- ----------
周福生          20000
王五            19100
李四            23900

select name from employees
where 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
)  e2
where 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 employees
where 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 as
select empno,name,salary,hiredate,email from employees 
where email like '%@qq.com'
order by hiredate
with check option;