Oracle基礎查詢

標籤：

select * from teacher
--女性老師編號、姓名
select tno,tname,gendar from teacher where gendar=‘女‘
--姓韓的女性老師編號、姓名、社會安全號碼
select tno,tname,tid from teacher where gendar=‘女‘ and tname like ‘韓%‘
--薪水在[10000，20000)之間老師列表
select sal,tname from teacher where sal between 10000 and 20000
--崗位為講師或者研發，按照薪水降序排序
select tname,sal,job from teacher where job in(‘講師‘,‘研發‘)
order by sal desc
--部門所有資料的INSERT語句
select * from dept
--oracle使用|| 來連接字串
insert into dept values(40,‘人力部‘,‘北京海澱‘)
--怎麼查詢
select ‘insert into dept values(‘||deptno||‘,‘‘‘||dname||‘‘‘‘||‘,‘‘‘||loc||‘‘‘);‘
from dept;

 

select * from teacher
--招生部門所有男老師姓名
--使用子查詢
select tname,gendar,deptno
from teacher
where deptno=(select deptno from dept where dname=‘招生部‘)
and gendar=‘男‘
--使用內串連 01
select tname,gendar,d.deptno,dname
from teacher t,dept d
where t.deptno=d.deptno
and gendar=‘男‘ and dname=‘招生部‘

--使用內串連 02
select tname,gendar,d.deptno,dname
from teacher t inner join dept d
on t.deptno=d.deptno
where gendar=‘男‘ and dname=‘招生部‘

--所有老師姓名、部門
select tname,dname
from teacher t,dept d
where t.deptno=d.deptno

--所有老師的姓名以及其導師的姓名 自串連
select t1.tname,t2.tname as 導師姓名 from teacher t1,teacher t2
where t2.tno=t1.mgrno

--所有老師的姓名、部門、導師
select t.tname as 老師姓名,t1.tname as 導師姓名,d.dname as 部門
from teacher t left join teacher t1
on t.mgrno=t1.tno
left join dept d
on t.deptno=d.deptno

--列出所有姓名和出生日期
select tname,birthdate from teacher

 

Oracle基礎查詢