데이터베이스 SQL 오라클 where 조건절, like 키워드, order by, 그룹함수, 시퀀스

-- ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
-- where 조건절
-- 모든 데이터를 가져오는 것이 아니라 사용자가 원하는 데이터만 조회할 경우 사용됨.
-- where 절은 from 테이블 이름 뒤에 기술해야 한다.

-- 형식) select 컬럼명1, 컬럼명2... 컬럼명n from 테이블 where 조건식;
-- where 조건절은 자료를 필터링 할 때 사용되는 키워드 

-- 1) = : 조건이 같은가?

-- 2) < : 조건이 작은가?

-- 3) <= : 조건이 작거나 같은가?

-- 4) > : 조건이 큰가?

-- 5) >= : 조건이 크거나 같은가?

-- 6) != : 조건이 같지 않은가?

-- 7) <> : 조건이 같지 않은가? !=와 같은 의미 

-- 8) between A and B : A와 B사이에 있는가?
--    주의) 작은 값을 앞에 기술하고 큰 값을 뒤에 기술해야 한다.

-- 9) in(list) : list 값 중에 어느 하나와 일치하는가? 

-- 10) not between A and B : A와 B사이에 있지 않은가?
--                          (A, B 값을 포함하지 않음.)

-- 11) not in(list) : list 값과 일치하지 않는가?

예제)

-- emp 테이블에서 담당업무가 'MANAGER'인 사원의 모든 정보를 화면에 보여주세요.
select * from emp where job = 'MANAGER';

-- emp 테이블에서 담당업무가 'SALESMAN'인 사원의 모든 정보를 화면에 보여주세요.
select * from emp where job = 'SALESMAN';

emp 테이블에서 담당업무가 'CLERK'인 사원의 사번,이름, 담당업무를 보여주세요. 
select empno, ename, job from emp where job = 'CLERK';

emp 테이블에서 1982년 1월 1일 이후 입사한 사원의 사번,이름, 담당업무, 급여 입사일자를 보여주세요.
select empno, ename, job, sal, hiredate from emp where hiredate >= '82/01/01';

emp 테이블에서 급여가 1355~1500 사이인 사원의 이름, 담당업무, 급여, 부서번호를 보여주세요.
select ename, job, sal, deptno from emp where sal >= 1300 and sal <= 1500;
select ename, job, sal, deptno from emp where sal between 1300 and 1500;

emp 테이블에서 사번이 7902 7788 7566인 사원의 사번, 이름, 담당업무를 보여주세요.
select empno, ename, job from  emp where empno in(7902, 7788, 7566);
select empno, ename, job from  emp where empno = 7902 or empno=7788 or empno=7566;

emp 테이블에서 보너스가 300이거나 500이거나 1400인 사원의 모든정보를 보여주세요.
select * from emp where comm in(300,500,1400);
select * from emp where comm = 300 or comm = 500 or comm = 1400;

member10 테이블에서 이름이 사나이면서 직업이 학생인 회원의 모든 정보를 화면에 보여주세요.
select * from member10 where memname = '사나' and job = '대학생';

products 테이블에서 제조사가 '삼성' 또는 '애플'이면서 입고가가 100만원 이하의 상품명의 입고가, 출고가를 화면에 보여주세요
select company, input_price, output_price from products where company in('삼성', '쿠쿠') 
            and input_price <= 1000000;

emp 테이블에서 급여가 1100이상이고, 담당업무가 'MANAGER'인 사원의 사번, 이름, 담당업무, 급여를 화면에 보여주세요.
select empno, ename, job, sal from emp where sal >= 1100 and job = 'MANAGER';


---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

-- like 키워드 : 검색을 하는 키워드.
-- where ename like '%S%';
-- ==> ename 칼럼에 S자를 포함하는 사원의 이름을 검색. 

-- where ename like 'S%'
-- ==> ename 컬럼에 첫글자가 S자로 시작하는 사원의 이름을 검색.

-- where ename like '%S'
-- ==> ename 컬럼에 마지막 글자가 S자로 끝나는 사원의 이름을 검색.

-- where ename like '_S%'
-- ==> ename 컬럼에 두번째 글자가 S자를 포함하는 사원의 이름을 검색.
--      '__S%'  언더바가 두개면 세번째 글자가 S자를 포함하는 사원의 이름 검색.

형식)  where 컬럼명 like '%문자%' (위의 조건에 맞춰 선택)

select * from emp where ename like '%S%';
select * from emp where ename like '_I%'; -- 두번째 글자에 I가 들어가는 사람

--[문제1] emp테이블에서 이름이 'S'자로 끝나는 사원의 이름과 담당업무, 부서번호를 화면에 보여주세요.
select ename, job, deptno from emp where ename like '%S';

--[문제2] emp테이블에서 이름의 세번째 글자가 'R'이 들어가는 사원의 이름과, 담당업무, 급여를 화면에 보여주세요.
select ename, job, sal from emp where ename like '__R%';

--[문제3] emp 테이블에서 이름의 두번째 글자가 'O'인 사원의 모든정보를 화면에 보여주세요.
select * from emp where ename like '_O%';

--[문제4] emp 테이블에서 이름의 입사년도가 82년도인 사원의 사번,이름,담당업부, 입사일자를 화면에 보여주세요.
select empno, ename, hiredate from emp where hiredate like '82%';

--[문제5] member10 테이블에서 이름이 나로 끝나는 회원의 모든 정보를 화면에 보여주세요.
select * from member10 where memname like '%나';

--[문제6] member10 테이블에서 주소에 '서울시'인 회원의 이름과 주소, 직업을 화면에 보여주세요. 
select memname, addr, job from member10 where addr like '서울%';

--[문제7] products 테이블에서 제품명에 '슈퍼카'를 포함하는 제품의 제품명 입고가, 출고가, 제조사를 화면에 보여주세요. 
select product_name, input_price, output_price, company from products where product_name like '%슈퍼카%';

---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

-- order by 절
-- 자료를 정렬하여 나타낼 때 사용하는 구문.

-- order by 절을 사용할 때는 select 구문의 맨 마지막에 위치해야함.

-- asc : 오름차순 정렬

-- desc : 내림차순 정렬

-- 기본적으로 order by 절 사용 시에는 오름차순 정렬이 default임.

-- 오름차순 정렬인 경우에는 asc도 생략 가능

-- 정렬에서 null 값은 오름차순에서 제일 뒤에 나오고, 내림차순에선 가장 앞에 나옴.


-- member10 테이블에서 이름을 기준으로 오름차순으로 정렬하여 화면에 보여주세요.
-- 단, 이름이 같은 경우에는 나이를 기준으로 내림차순 정렬
select * from member10 order by memname asc, age desc;

--[문제1] emp 테이블에서 부서번호를 기준으로 오름차순 정렬을 하고,
--       부서번호가 같은 경우 급여를 기준으로 내림차순으로 정렬을 하여 화면에 보여주세요.
select * from emp order by deptno asc, sal desc;
         
--[문제2] products 테이블에서 판매가격을 기준으로 내림차순으로 정렬하여 모든 정보를 화면에 보여주세요.
select * from products order by output_price desc;

--[문제3] products 테이블에서 배송비를 기준으로 내림차순으로 정렬하세요.
--       단, 배송비가 같은 경우 마일리지를 기준으로 내림차순으로 정렬하여 모든 정보를 화면에 보여주세요.
select * from products order by trans_cost desc, mileage desc;

--[문제4] emp 테이블에서 입사일자가 오래된 사원부터 최근에 입사한 사원을 기준으로 정렬 하세요. 
--      사원명, 입사일자를 화면에 보여주세요.
select ename, hiredate from emp order by hiredate asc;

--[문제5] emp테이블에서 급여를 기준으로 내림차순으로 정렬하여 모든 정보를 화면에 보여주세요.
select * from emp order by sal desc;

--[문제6] emp테이블에서 급여가 1100 이상인 사원들의 정보를 보여주되, 입사일자 빠른 순으로 정렬하여 화면에 보여주세요.
select * from emp where sal >= 1100 order by hiredate asc;

--[문제7] emp테이블에서 부서번호를 기준으로 오름차순 정렬하고 부서번호가 같은 경우 담당업무를 오름차순으로 정렬.
--       담당업무가 같다면 급여가 많은데서 적은 순으로 정렬하여 화면에 보여주세요.
select * from emp order by deptno asc, job asc, sal desc;


---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
-- not 키워드 : 부정

-- 쿼리문 작성 시 부정이 아닌 긍정의 쿼리문을 작성한 후에 부정의 의미인 not을 붙여줌.

-- emp 테이블에서 담당업무가 'MANAGER', 'CLERK', 'ANALYST'가 아닌 사원의 사번, 이름, 담당업무, 급여를 화면에 보여주세요.
select empno, ename, job, sal 
from emp 
where job not in('MANAGER', 'CLERK', 'ANALYST');

--문제1 emp 테이블에서 이름에 S자가 들어가지 않은 사원
select ename,job from emp where ename not like '%S%';

--문제2 emp 테이블에서 부10번 부서가 아닌 사원 이름 담당업무 부서번호 출력
select ename, job, deptno from emp where deptno != 10;
select ename, job, deptno from emp where deptno <> 10;
select ename, job, deptno from emp where not deptno = 10;

--문제3 member10 테이블에서 주소가 서울이 아닌 회원의 모든정보
select * from member10 where addr not like '서울%';

--문제4 products 테이블에서 출고가가 100만원 미만이 아닌 제품의 상품명과 출고가 
select product_name, output_price from products where output_price >= 1000000;
select product_name, output_price from products where not output_price < 1000000;


---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

-- 그룹함수
-- 여러 행 또는 테이블 전체에 대하여 함수가 적용되어 하나의 결과값을 가져오는 함수

-- 1) avg() : 평균값을 구하는 함수

-- 2) count(): 행(레코드)의 갯수를 구하는 함수
--             null값은 무시하고 행의 갯수를 구함

-- 3) max() : 최대값을 구하는 함수

-- 4) min() : 최소값을 구하는 함수

-- 5) sum() : 총 합을 구하는 함수


-- emp테이블에서 사번을 가지고 있는 모든 사원의 수를 화면에 보여주세요.
select count(empno) from emp;
select count(*) from emp;

--[문제1] emp 테이블에서 관리자(mgr)의 수를 화면에 보여주세요.
select count(distinct mgr) from emp;

--[문제2] emp 테이블에서 보너스를 가진 사원의 수를 출력하세요.
select count(comm) from emp;

--[문제3] emp 테이블에서 모든 SALESMAN의 급여 평균과 급여 최고액, 급여 최소액, 급여 합계액을 화면에 출력.
select avg(sal), max(sal), min(sal), sum(sal)
from emp 
where job = 'SALESMAN';

--[문제4] emp 테이블에서 등록되어 있는 사원의 총 수, 보너스 null이 아닌 인원 수, 보너스의 평균,
--       등록되어있는 부서의 수를 화면에 보여주세요.
select count(*), count(comm), avg(comm), count(distinct deptno) from emp; 

---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

-- 시퀀스
-- 순번을 부여할 때 사용하는 문법.

ex) 스레드로 작동되는 게시판에서 다수가 게시글을 동시 작성 할 때, 먼저 작성되는 순서대로 번호를 부여하는 것.

-- 형식)
--      create sequence 시퀀스이름
--      start with 시작값
--      increment by 증가값 

create table memo(
    bunho number(5),
    title varchar2(100) not null,
    wirter varchar2(50) not null,
    cont varchar2(1000) not null,
    regdate date,
    -- 테이블 레벨상으로 제약 조건 설정하는 방법
    primary key(bunho)
    -- unique(title, writer, cont)
    -- not null 제약 조건은 받느시 컬럼 레벨 상에서만 제약 조건을 주어야 함
);

-- 메모 작성시 사용할 시퀀스를 만들어 보자
create sequence memo_seq start with 1 increment by 1;

create sequence 시퀀스이름 start with 시작값 increment by 증가값 

-- 메모테이블에 데이터를 저장해보자.
insert into memo 
        values(memo_seq.nextval, '메모1', '홍길동', '길동이 글', sysdate);

.nextval  설정한 시퀀스의 초기값부터 증가값을 자동으로 이행해준다. 

insert into memo 
        values(memo_seq.nextval, '메모2', '이순신', '이순신 글', sysdate);

insert into memo
        values(memo_seq.nextval, '메모3', '유관순', '유관순 글', sysdate);