--创建表
create table emp
(
empno int ,
ename varchar(10) ,
job varchar(9) ,
mgr int,
hierdate datetime ,
sal decimal(7,2),
comm decimal(7,2),
deptno int,
)
--插入数据
insert into emp values (7369,'smith','clerk',7902,'1980/12/17',800.00,null,20)
insert into emp values (7499,'allen','salesman',7698,'1981/2/20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981/2/22',1250.00,500.00,30)
insert into emp values (7566,'jones','manager',7839,'1981/4/2',2975.00,null,20)
insert into emp values (7654,'martin','salesman',7698,'1981/9/28',1250.00,1400.00,30)
insert into emp values (7698,'blake','manager',7839,'1981/5/1',2850.00,null,30)
insert into emp values (7782,'clark','manager',7839,'1987/6/9',2450.00,null,10)
insert into emp values (7788,'socctt','analyst',7566,'1987/4/19',3000.00,null,20)
insert into emp values (7844,'turner','salesman',7698,'1981/9/8',1500.00,0.00,30)
insert into emp values (7876,'adams','clerk',7788,'1987/5/23',1100.00,null,20)
insert into emp values (7900,'james','clerk',7698,'1981/12/3',950.00,null,30)
insert into emp values (7902,'foro','analyst',7566,'1981/1/23',3000.00,null,10)
insert into emp values (7934,'miller','clerk',7782,'1982/1/23',1300.00,null,10)create table salgrade
(
gread int,
losal int,
hisal int,
)
insert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
说明:表1
NO 字段 类型 描述
1 empno Number(4) 雇员编号
2 ename Varchar2(10) 雇员姓名
3 job Varchar2(9) 工作职位
4 mgr Number(4) 雇员的领导(经理)编号
5 hierdate date 入职日期
6 sal Number(7,2) 月薪/工资
7 comm Number(7,2) 奖金
8 deptno Number(2) 雇员所属部门的编号
表2
NO 字段 类型 描述
1 Grade number 等级名称
2 Losal number 此等级的最低工资
3 hisal number 此等级的最高工资
问题:求每个雇员的姓名,工资,工资等级,工资等级表(表2)和员工表(表1)没有主外键关系。
create table emp
(
empno int ,
ename varchar(10) ,
job varchar(9) ,
mgr int,
hierdate datetime ,
sal decimal(7,2),
comm decimal(7,2),
deptno int,
)
--插入数据
insert into emp values (7369,'smith','clerk',7902,'1980/12/17',800.00,null,20)
insert into emp values (7499,'allen','salesman',7698,'1981/2/20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981/2/22',1250.00,500.00,30)
insert into emp values (7566,'jones','manager',7839,'1981/4/2',2975.00,null,20)
insert into emp values (7654,'martin','salesman',7698,'1981/9/28',1250.00,1400.00,30)
insert into emp values (7698,'blake','manager',7839,'1981/5/1',2850.00,null,30)
insert into emp values (7782,'clark','manager',7839,'1987/6/9',2450.00,null,10)
insert into emp values (7788,'socctt','analyst',7566,'1987/4/19',3000.00,null,20)
insert into emp values (7844,'turner','salesman',7698,'1981/9/8',1500.00,0.00,30)
insert into emp values (7876,'adams','clerk',7788,'1987/5/23',1100.00,null,20)
insert into emp values (7900,'james','clerk',7698,'1981/12/3',950.00,null,30)
insert into emp values (7902,'foro','analyst',7566,'1981/1/23',3000.00,null,10)
insert into emp values (7934,'miller','clerk',7782,'1982/1/23',1300.00,null,10)create table salgrade
(
gread int,
losal int,
hisal int,
)
insert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
说明:表1
NO 字段 类型 描述
1 empno Number(4) 雇员编号
2 ename Varchar2(10) 雇员姓名
3 job Varchar2(9) 工作职位
4 mgr Number(4) 雇员的领导(经理)编号
5 hierdate date 入职日期
6 sal Number(7,2) 月薪/工资
7 comm Number(7,2) 奖金
8 deptno Number(2) 雇员所属部门的编号
表2
NO 字段 类型 描述
1 Grade number 等级名称
2 Losal number 此等级的最低工资
3 hisal number 此等级的最高工资
问题:求每个雇员的姓名,工资,工资等级,工资等级表(表2)和员工表(表1)没有主外键关系。
sal+comm as 工资,
b.gread as 工资等级
from emp a,salgrade b
where sal+comm between b.losal and hisal/**
姓名 工资 工资等级
---------- --------------------------------------- -----------
allen 1900.00 3
ward 1750.00 3
turner 1500.00 3
martin 2650.00 4(4 行受影响)
**/
select e.ename 姓名,
e.sal 工资,
s.gread 工资等级
from emp e left join salgrade s
on e.sal between s.Losal and s.hisal/*
姓名 工资 工资等级
---------- --------- -----------
smith 800.00 1
allen 1600.00 3
ward 1250.00 2
jones 2975.00 4
martin 1250.00 2
blake 2850.00 4
clark 2450.00 4
socctt 3000.00 4
turner 1500.00 3
adams 1100.00 1
james 950.00 1
foro 3000.00 4
miller 1300.00 2(所影响的行数为 13 行)*/
insert into emp values (7369,'smith','clerk',7902,'1980/12/17',800.00,null,20)
insert into emp values (7499,'allen','salesman',7698,'1981/2/20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981/2/22',1250.00,500.00,30)
insert into emp values (7566,'jones','manager',7839,'1981/4/2',2975.00,null,20)
insert into emp values (7654,'martin','salesman',7698,'1981/9/28',1250.00,1400.00,30)
insert into emp values (7698,'blake','manager',7839,'1981/5/1',2850.00,null,30)
insert into emp values (7782,'clark','manager',7839,'1987/6/9',2450.00,null,10)
insert into emp values (7788,'socctt','analyst',7566,'1987/4/19',3000.00,null,20)
insert into emp values (7844,'turner','salesman',7698,'1981/9/8',1500.00,0.00,30)
insert into emp values (7876,'adams','clerk',7788,'1987/5/23',1100.00,null,20)
insert into emp values (7900,'james','clerk',7698,'1981/12/3',950.00,null,30)
insert into emp values (7902,'foro','analyst',7566,'1981/1/23',3000.00,null,10)
insert into emp values (7934,'miller','clerk',7782,'1982/1/23',1300.00,null,10)
create table salgrade(gread int,losal int,hisal int)
insert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
goselect * from emp m,salgrade n where m.sal between n.losal and n.hisaldrop table emp ,salgrade/*
empno ename job mgr hierdate sal comm deptno gread losal hisal
----------- ---------- --------- ----------- ------------------------------------------------------ --------- --------- ----------- ----------- ----------- -----------
7369 smith clerk 7902 1980-12-17 00:00:00.000 800.00 NULL 20 1 700 1200
7876 adams clerk 7788 1987-05-23 00:00:00.000 1100.00 NULL 20 1 700 1200
7900 james clerk 7698 1981-12-03 00:00:00.000 950.00 NULL 30 1 700 1200
7521 ward salesman 7698 1981-02-22 00:00:00.000 1250.00 500.00 30 2 1201 1400
7654 martin salesman 7698 1981-09-28 00:00:00.000 1250.00 1400.00 30 2 1201 1400
7934 miller clerk 7782 1982-01-23 00:00:00.000 1300.00 NULL 10 2 1201 1400
7499 allen salesman 7698 1981-02-20 00:00:00.000 1600.00 300.00 30 3 1401 2000
7844 turner salesman 7698 1981-09-08 00:00:00.000 1500.00 .00 30 3 1401 2000
7566 jones manager 7839 1981-04-02 00:00:00.000 2975.00 NULL 20 4 2001 3000
7698 blake manager 7839 1981-05-01 00:00:00.000 2850.00 NULL 30 4 2001 3000
7782 clark manager 7839 1987-06-09 00:00:00.000 2450.00 NULL 10 4 2001 3000
7788 socctt analyst 7566 1987-04-19 00:00:00.000 3000.00 NULL 20 4 2001 3000
7902 foro analyst 7566 1981-01-23 00:00:00.000 3000.00 NULL 10 4 2001 3000(所影响的行数为 13 行)
*/
where e.sal between s.Losal and s.hisal group by e.empno
go