oracle多表简单查询实例代码

-- 笛卡尔积 
select * from emp, dept;

prompt Importing table dept...
set feedback off
set define off
insert into dept (DEPTNO, DNAME, LOC)
values (1, '01事业部', '324');
 
insert into dept (DEPTNO, DNAME, LOC)
values (2, '02事业部', '234');
 
insert into dept (DEPTNO, DNAME, LOC)
values (3, '03事业部', '234');
 
insert into dept (DEPTNO, DNAME, LOC)
values (4, '04事业部', '3244');
 
prompt Done.

prompt Importing table emp...
set feedback off
set define off
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小明', '运维', '9000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '1');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小刚', 'hr', '7000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '1');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小花', 'Vue开发', '6000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '1');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小贝', '软件工程师', '9000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '2');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小涛', '数据中台', '11000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '2');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小胜', '设计', '9000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '2');
 
prompt Done.

prompt
 
prompt Creating table DEPT
prompt ===================
prompt
 
create table USERNAME.DEPT
(
  deptno NUMBER not null,
  dname  VARCHAR2(10) not null,
  loc    VARCHAR2(10) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
 
prompt
 
prompt Creating table EMP
prompt ==================
prompt
 
create table USERNAME.EMP
(
  empno    NVARCHAR2(20),
  ename    NVARCHAR2(20),
  job      NVARCHAR2(20),
  mgr      NVARCHAR2(20),
  hiredate DATE,
  sal      BINARY_DOUBLE,
  comm     BINARY_DOUBLE,
  deptno   NVARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
 
 
prompt Done
spool off
set define on

--查询与SMITH在同一部门的员工
select * from (select   e.deptno from emp e,dept t where  e.deptno=t.deptno and  e.ename='SMITH') s1,emp e
where e.deptno =s1.deptno
 
 
select * from emp where deptno=(select deptno from emp where ename='SMITH');

 
 
--查询与10号部门工作相同的员工名称,工作，工资，部门编号
select ename,job,sal,deptno from emp where job in (select job from emp where deptno=1);
 
--查询比30号部门所有人工资高的员工姓名，职位，工资，部门编号
select ename,sal,job,deptno from emp where sal>all(select sal from emp where deptno=1);
 
--在多行子查询中使用any操作符
--查询比1号部门任意人工资高的员工姓名，职位，工资，部门编号
select ename,sal,job,deptno from emp where sal>any(select sal from emp where deptno=1);

--查询与SMITH的部门和职位完全相同的员工信息
 
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');