823oracle

--分组再排序
select dept_no from employee group by dept_no ,name order by dept_no desc
 
--实现查询部们人数2个以上的部门ID
select dept_no from employee group by dept_no having count (dept_no)>1
--创建表对象
select e.sex from employee e
--dual空表，补充
select sysdate from dual
--关联查询
select * from employee
select * from department
select d.name,e.name from  department d left join employee e on d.dept_no=e.dept_no
select d.name,e.name from employee e full join department d on d.dept_no=e.dept_no
select d.name,e.name from  department d right join  employee e on d.dept_no=e.dept_no
select d.name,e.name from  department d  join  employee e on d.dept_no=e.dept_no 
where e.dept_no in(select dept_no from employee group by dept_no having count (dept_no)>1)


select * from employee where dept_no between 1 and 2


delete from employee t where rownum<2 and t.dept_no=1  


select rownum,t.* from(select * from employee) t


select dept_no a, name b from employee union select dept_no, name from department
--创建索引
create index index_employee on employee (id)


select  to_char(sysdate,"yyyy-mm-dd") from dual
--三元运算符
select nvl(null,1) from dual --第一个为空返回第二个
select nvl(1,2) from dual --第一个不为空返回第一个
select nvl2(null,1,2) from dual --为空则返回第二个
select nvl2(0,1,2) from dual --不为空则返回第一个
select nullif(2,3) from dual --不同返回前面的
select nullif(2,2) from dual --相同返回null


select name ,case when sex=0 then '男' else '女' end from employee
select name ,case when sex=0 then '南' else '女' end from employee
select name,sex,age,case when dept_no=1 then '总经办ha' when dept_no =2 then '业务ha' else '质量部ha' end from employee