-- empno 사원번호
-- ename 이름
-- job 직업
-- mgr 상사번호
-- hiredate 입사일
-- sal 월급
-- comm 보너스
-- empno 부서번호

-- 1. select 기본 문법
SELECT empno, ename
FROM emp;

SELECT *
FROM emp;

-- 2. 특정 행만 골라보기
SELECT *
FROM emp
WHERE empno = 7369;

-- 3. 테이블 스키마 보기
desc emp;

-- 4. 별칭
SELECT empno as '사원번호'
from emp;

SELECT empno 사원번호
from emp;

-- 5. 서로다른 : 뜻 -> 중복제거
SELECT distinct job
from emp;

-- 6. 연결연산자
select concat(ename, '의 직업은 ', job) 소개
from emp;

-- 7. ifnull 함수
select ename, ifnull(comm, 0)+sal
from emp;

-- 8. 이름으로 찾기
select *
from emp
where ename = 'SMITH';

-- 9. 날짜로 찾기 (date)
select *
from emp
where hiredate = '1980-12-17';

select *
from emp
where hiredate = '1980/12/17';

-- 10. null값 찾기
select ename, comm
from emp
where comm is null;

select ename, comm
from emp
where comm is not null;

-- 11. 복잡한 where (or, and, in, between)
select *
from emp
where sal = 800 or sal = 1600;

select *
from emp
where sal = 800 or job = 'manager';

select *
from emp
where sal in (800, 900);

select *
from emp
where sal = 800 and deptno = 20;

select *
from emp
where sal between 800 and 3000;

select *
from emp
where sal >= 800 and sal <= 3000;

-- 12. like 연산자 

select * from emp;

select ename
from emp
where ename like 'S%';

select ename
from emp
where ename like '%T';

select ename
from emp
where ename like '%M%';

select ename
from emp
where ename like '__N%';

-- 13. 정렬 (오름차순 asc는 생략가능, 내림차순) - 퀵솔트사용
-- [3,5,1,2,4,6]
SELECT *
FROM emp
order by sal asc;

SELECT *
FROM emp
order by sal desc;

select *
from emp
order by job asc, sal desc;