SQL - SELECT문

1. basic select

-- 베이직 select  --
--1. * col,col
SELECT * FROM EMP;
select empno, ename, job from emp;
select * from dept;
--2. 산술연산 (+-*/, null)
SELECT sal, SAL*100
FROM EMP;
 
SELECT SAL, COMM, SAL+COMM
from emp;
 
--3. alias as
SELECT * FROM EMP;
SELECT EMPNO, HIREDATE    HDATE, DEPTNO FROM EMP;
SELECT EMPNO as "E NO", HIREDATE AS HDATE, DEPTNO as dno 
FROM EMP;
 
SELECT * FROM EMP;
SELECT * FROM EMP E;
select * from emp AS e;  -- 에러
 
--4. 연결 || SMITH7736  SMITH_7736
SELECT EMPNO, ENAME 
      , ENAME||EMPNO nano
      , ENAME||'-'||EMPNO  as nano2
FROM EMP;
 
--5. DISTINCT 유니크하게 딱 한번씩만 출력
SELECT DEPTNO FROM EMP;
SELECT DISTINCT DEPTNO FROM EMP;
 
SELECT JOB FROM EMP;
select distinct job from emp;
 
select distinct job, distinct deptno from emp;  --에러
 
SELECT DISTINCT      JOB, DEPTNO FROM EMP;
 
--6. desc describe
DESC EMP;
DESCRIBE EMP;

 

2. select 제한조건 (where)

 -- select : 제한(where) --
--1. =(문자열,날짜), <=,  <> !=  BETWEEN, IN(NOT IN), LIKE, IS NULL(IS NOT NULL)
--10번 부서 사람만 출력
SELECT * 
FROM EMP
WHERE DEPTNO=10;
 
--직업이 CLERK인 사람만 출력
SELECT *
FROM EMP
where job = 'CLERK';
 
--2. AND, OR, (우선순위) NOT
-- 직업(job)이 CLERK이면서 급여(sal)가 1000 이상인 사람
SELECT *
FROM EMP
WHERE  SAL >= 1000 and
      job = 'CLERK';
      
-- 직업(job)이 CLERK이면서 급여(sal)가 1000 이상이거나 
-- 또는 직업이 MANAGER인 사람
 
SELECT *
FROM EMP
WHERE (JOB = 'CLERK' AND SAL >= 1000) 
   or (job = 'MANAGER');
 
--직업이 MANAGER가 아닌 사람
SELECT *
FROM EMP
WHERE 
  (JOB != 'MANAGER' AND  
  JOB !='CLERK' AND
  job !='CLERK') 
  OR
  (JOB !='CLERK'AND
  JOB !='CLERK')
  ;
 
SELECT *
FROM EMP
where job <> 'MANAGER';
 
 
SELECT *
FROM EMP
WHERE job NOT IN ('MANAGER','CLERK');
 
--직업이 MANAGER 이거나 CLERK인 사람
SELECT *
FROM EMP
where job in ('MANAGER','CLERK');
--where job='MANAGER' or job='CLERK';
 
--직업이 MANAGER가 아니거나 CLERK이 아닌 사람
SELECT *
FROM EMP
where job not in ('MANAGER','CLERK');
--where job!='MANAGER' and job!='CLERK';
--where job<>'MANAGER' and job<>'CLERK';
 
-- 급여가 1000이상 2000이하인 사람
select *
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000;
--where sal>=1000 and sal<=2000;
 
-- 커미션을 받는 사람
SELECT *
FROM EMP
WHERE COMM IS NOT NULL AND COMM > 0;
-- 커미션을 받지 않는 사람
SELECT *
FROM EMP
where comm is null or comm=0;
 
--이름이 A로 시작하는 사람
SELECT *
FROM EMP
WHERE ENAME LIKE 'A%'; 
 
--이름의 두번째 글자에 A가 들어간 사람
SELECT *
FROM EMP
where ename like '_A%';
 
--이름의 끝에 S가 들어간 사람
SELECT *
FROM EMP
WHERE ENAME LIKE '%S';
 
--이름에 LA가 들어간 사람
SELECT *
FROM EMP
WHERE ENAME LIKE '%LA%';
 

 

3. 정렬 order by (asc, desc)

--3. ORDER BY ASC DESC  ALIAS, (SAL ASC, NAME DESC) 
--급여를 많이 받는 사람 순으로 출력
SELECT * FROM EMP
order by sal desc;
 
--급여를 적게 받는 사람 순으로 출력
SELECT * FROM EMP
order by sal asc;
 
SELECT * FROM EMP
order by sal;
 
-- 부서별 오름차순, 급여가 높은순 출력
SELECT * FROM EMP
ORDER BY 
     DEPTNO ASC, 
     sal desc;
 
-- 연봉(sal*12)이 높은 순 출력
SELECT SAL*12 as ann 
FROM EMP
ORDER BY SAL*12 DESC;
 
SELECT SAL*12 as ann 
FROM EMP
order by ann desc;