오라클 sql Practice1 [Select, 비교,논리, Like]

SQL 처음 공부했을 때 썼던 코드 복습하는 느낌으로 기록합니다.

연습 Table은 오라클에서 처음 제공하는 emp, dept 를 이용하였습니다.

desc emp;

select * from emp;
select empno,ename,deptno from emp;
select ename, sal from emp;
select distinct deptno from emp;
select distinct job,deptno from emp;
select ename,sal,sal*12+comm,comm from emp;
select ename, sal,sal*12+comm as annsal,comm from emp;
select ename as 사원이름, sal as 급여, sal*12+comm as 연봉, comm as 추가수당 from emp;
select * from emp order by sal desc;
select * from emp where deptno = 30;
select * from emp where job = 'SALESMAN';
select * from emp where deptno =30 and job ='SALESMAN';
select * from emp where deptno =30 or job ='SALESMAN';
select * from emp where no sal = 3000;
select * from emp where job in('MANAGER' , 'SALESMAN' , 'CLERK')
select * from emp where job = 'MANAGER' and job = 'SALESMAN' and job = 'CLERK';
select * from emp where job != 'MANAGER' and job <> 'SALESMAN' and job ^= 'CLERK';
select * from emp where job not in('MANAGER','SALESMAN','CLERK');
select * from emp where sal >= 2450 and sal<= 3000;
select * from emp where between 2450 and 3000;
select ename from emp where sal >=2000;
select * from emp where ename like 'S%';
select * from emp where ename like '%E%';
select * from emp where ename like '_L%';
select * from emp where enama like '%AM%';
select * from emp where ename not like '%E%';
select * from emp where comm = null;
select * from emp where comm is null;
select * from emp where comm is not null;
select empno,ename,sal,deptno from emp where deptno = 10 union select empno,ename,sal,deptno from where deptno = 20;
select empno,ename,sal,deptno from emp where deptno = 30 union all select empno,ename,sal,deptno from emp where deptno = 30;

select empno, ename, sal from emp;
select ename as 이름, sal as 월급 from emp;
select distinct jop from emp;
select ename, empno from emp where empno in(7521, 7654,7844);

select '사원이름은 ' ||ename|| ' 입니다' as 사원 from emp;
select '사원번호는 ' ||empno|| '이고 사원이름은 ' ||ename|| ' 입니다' as 사원정보 from emp;
select ename|| '의 월급은 ' ||sal|| '원이다' as 월급 from emp;

select * from emp where sal >=2000 or job = 'SALESMAN';
select ename,job,sal from emp where empno = '7698';
select ename, sal, empno from emp where ename = 'SMITH';
select * from emp where ename like '%A%' and ename like '%E%';
select * from emp where ename like '%A%' or ename like '%E%';
select ename, sal from emp where comm is not null;
select * from emp order by deptno asc;

select ename, sal from emp where sal not between 2000 and 3000;
select ename, sal from emp where sal < 2000 or sal > 3000;