求助，也不太清楚是属于引索还是查询
mysql> select * from emp;+-----+-------+------+-----------+--------+--------+
| sid | sname | sage | sjob | salary | deptno |
+-----+-------+------+-----------+--------+--------+
| 1 | jack| 21 | tester | 5100 | 10 |
| 2 | alice | 21 | personnel | 3100 | 20 |
| 3 | yoli| 21 | personnel | 3100 | 20 |
| 4 | clink | 25 | tester | 6000 | 10 |
| 5 | hallo | 32 | developer | 9000 | 30 |
+-----+-------+------+-----------+--------+--------+
mysql> select * from dept;
+-----------------------------+--------+------------+------------+
| dname | deptno | dtelephone | location |
+-----------------------------+--------+------------+------------+
| sales department | 10 | 111111 | nanjing101 |
| personnel department | 20 | 222222 | nanjing102 |
| testing department | 30 | 333333 | nanjing103 |
| customer service department | 40 | 666666 | nanjing104 |
+-----------------------------+--------+------------+------------+
是作业题，老师让我们预习，实在不知道该怎么做,从这两张表中: 1.查询jack的工作地址。2.查询工资比jack高的其他员工信息。3.查询clink的工资等级。4.查找和jack同部门的员工。 那个工资等级指什么?其它如下图:
# 查询jack的工作地址
SELECT d.location FROM emp e INNER JOIN dept d ON e.deptno = d.deptno AND e.sname = 'jack';
# 查询工资比jack高的其他员工信息
SELECT e.sid, e.sname, e.sage, e.sjob, e.salary, d.deptno, d.dname, d.dtelephone, d.location FROM
emp e INNER JOIN dept d ON e.deptno = d.deptno AND e.salary > 5100;
# 查询clink的工资等级(工资等级指什么)
# 查找和jack同部门的员工(输出时包括jack)
SELECT e.sname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno AND e.deptno = 10;
# 若只知道jack,不知道jack的deptno值,则可以这样写:
SELECT e.sname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno AND e.deptno IN (
SELECT deptno FROM emp WHERE sname = 'jack');
dimdark 发表于 2017-8-24 00:05
那个工资等级指什么?其它如下图:
# 查询jack的工作地址
谢谢。
mysql> select * from salgrade
;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 1 |1000 |3000 |
| 2 |3001 |5000 |
| 3 |5001 |7000 |
| 4 |7001 | 10000 |
+-------+-------+-------+
这是薪资等级的表格 # 查询clink的工资等级
select grade from salgrade where (select salary from emp where sname='clink') between losal and hisal;
页:
[1]