SQL, `SELECT` 구문 연습 문장들

'--' 라고 되어있는 것은 SQL의 주석을 의미합니다.

-- Desc Emp ;

/*
Select * From Emp ;
Select Empno, Sal*12 As "연봉" From Emp ;
*/

Select Ename, Sal As 월급, Sal*12 As 연봉, Comm As 보너스, 
       Sal*12+Nvl(Comm, 0) As 총급여, sal*12+NVL2(comm, 0, 1) as 총급여2 
From Emp ;

Select Ename || ' is a ' || job As 직급 From Emp ;

Select Distinct Deptno From Emp ;

Select Deptno From Emp Group By Deptno ;

Select Ename, Empno, Sal From Emp Where Ename = 'FORD' ;

-- 년도 사이 구분 기호는 /과 - 둘다 쓸 수 있음.
Select Ename, Hiredate From Emp Where Hiredate >= '1982/01-01'  ;

Select Ename, Hiredate From Emp Where Ename Like 'J%' ;

-- 2번째 글자가 A로 시작하는 문자열
Select Ename, Hiredate From Emp Where Ename Like '%_A%' ;

-- #이후 %문자는 와일드 카드가 아닌 실제 문자값
Select Ename, Hiredate From Emp Where Ename Like '%#%%' Escape '#' ;

-- 두 줄이 같은 의미
Select Ename, Hiredate, Comm From Emp Where Comm = 300 Or 
                                                                             Comm = 500 Or 
                                                                             Comm= 700 Or 
                                                                             Sal = 700 ;
Select Ename, Hiredate, Comm From Emp Where Comm In (300, 500, 700) Or 
                                                                              Sal = 700 ;

-- 두 줄이 같은 의미
Select Ename, Hiredate, Sal From Emp Where Sal >= 500 And Sal <= 4000 ;
Select Ename, Hiredate, Sal From Emp Where Sal between 500 And 4000 ;

-- 논리 연산자 AND, OR, NOT
Select Ename, Hiredate, Sal From Emp Where Deptno = 10 And Job ='CLERK' ;
Select Ename, Hiredate, Sal From Emp Where Deptno = 10 Or Sal = 1000 ;

-- 세 줄이 같은 의미
Select Ename, Hiredate, Sal From Emp Where Not Deptno = 10 ;
Select Ename, Hiredate, Sal From Emp Where Deptno <> 10 ;
Select Ename, Hiredate, Sal From Emp Where Deptno != 10 ;

Select Ename, Hiredate, Comm From Emp Where Comm Not In (300, 500, 700) ;

Select Ename From Emp Where Mgr Is Null ;
Select Ename From Emp Where Mgr Is Not Null ;

-- 정렬은 생략하면 ASC. 생략하지 않는 것이 권장된다.
Select Ename, Deptno, Sal From Emp Order By Sal ;
Select Ename, Deptno, Sal From Emp Order By Sal Desc, Deptno Asc ;