—
练习
同学们使用我提供的样本数据进行练习会得到和我这里给出的一样的结果。
参考答案如下:
select ename,sal from emp where sal between 10000 and 15000;
参考答案
update emp set sal=sal*1.1 where deptno
(select deptno from dept where loc='CHICAGO');
select * from emp where ename like '%n' or '_i%';
参考答案如下:
select ename,deptno,hiredate from emp order by deptno asc,hiredate asc;
参考答案如下:
postgres=# select ename,sal,sal*1.1 "Updated Salary",hiredate from emp where hiredate<'2010-01-01';
ename | sal | Updated Salary | hiredate
------------+----------+----------------+------------
Grace | 12000.00 | 13200.000 | 2000-12-02
Ben | 8000.00 | 8800.000 | 2004-09-11
(2 rows)
delete from emp where hiredate<'2010-01-01';
参考答案
delete from emp where deptno=(select deptno from dept where loc='NEW YORK');
参考答案:
select ename,sal,comm,sal+comm total_salary from emp where comm is not null;
参考答案如下:
select ename,deptno from emp where deptno not in (1,3);
select dname,(select sum(sal) from emp where deptno=d.deptno) dept_sum_sal from dept d;
早于2001之前入职员工列为创始人
在2001年和2019年之间的列为老员工
在2020年之后的列为新员工
select ename,hiredate,'Founder' as "Hire Date" from emp where hiredate <'2001-01-01'
union
select ename,hiredate,'Senior Employees' as "Hire Date" from emp where hiredate between '2001-01-01' and '2019-12-31'
union
select ename,hiredate,'Junior Employees' as "Hire Date" from emp where hiredate >'2019-12-31';
NOT IN表示不在集合中。
我们先向部门表中插入新一条记录
insert into dept(deptno,dname) values(5,'Operation');
查询部门表中一个员工也没有的部门
mysql> select deptno,dname from dept where deptno not in (select deptno from emp);
+--------+-----------+
| deptno | dname |
+--------+-----------+
| 5 | Operation |
+--------+-----------+
1 row in set (0.00 sec)
思考题
如果这个时候有个员工的部门号为空,比如这个员工是总裁,他不属于任何部门。
insert into emp(empno,ename,deptno,sal) values(10,'Frank',NULL,12000.00);
再次执行前面的SQL,得到的结果集为空,也就是说这个SQL有bug。
mysql> select deptno,dname from dept where deptno not in (select deptno from emp);
Empty set (0.00 sec)
使用两种方法对这个SQL进行改写,解决这个问题
mysql> select deptno,dname from dept where deptno not in (select deptno from emp where 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)