[Oracle] 3. NULL, 중복 제거(DISTINCT), 조건문 (BETWEEN, IN, LIKE), 정렬(ORDER BY), 집합(UNION)

-- select 컬럼명1, 컬럼명2, ... || *
-- from 테이블명
-- where [조건문] => 레코드 선택의 조건.
--                  where 구문이 없는 경우 모든 행을 선택
-- 컬럼에 사용되는 연산자
--       ||    : 연결연산자. 두개의 컬럼을 연결하여 하나의 컬럼으로 생성
--   산술연산자 : +, -, *, /
--       =     : 등가연산자. 두개의 값이 동일 true or fall
-- >, <, >=, <=: 관계연산자. 크거나, 작거나, 크거나같다, 작거나같다.

-- 테이블의 구조
desc student;
-- Null : 값이 없음. Null 값은 연산이 되지 않음.
-- Not Null : 반드시 값이 존재해야함.

-- distinct : 중복 제거하여 조회하기. 한번만 사용가능
-- 교수가 속한 부서코드를 알고자 한다.
select distinct deptno from professor;
-- 학생이 속한 학과코드를 알고자 한다.
desc student;
select distinct deptno1 from student;
select distinct deptno1, deptno2 from student;

--문제 1 : 학생을 담당하는 지도교수(profno) 번호를 중복없이 출력하기
desc student;
select distinct profno from student;

-- 날짜 컬럼 비교하기
desc student
select name,grade,birthday from student;
-- 날짜타입의 기본형식이 yy/mm/dd 형태로 출력됨
-- 학생 중 생일이 75년 이후에 태어난 학생의 이름,학년,생일 출력하기
select name,grade,birthday from student where birthday >='76/01/01';
-- 현재 session의 날짜 형식을 YYYY-MM-DD 형태로 변경.
-- 다른 session에서는 기본형식이 yy/mm/dd 형태로 설정.
alter session set nls_date_format='YYYY-MM-DD'; --휘발성 설정.
select name,grade,birthday from student;
-- 학생 중 생일이 75년 이후 태어난 학생의 이름,학년,생일 출력하기
select name,grade,birthday from student where birthday >='1976-01-01';

-- between 연산자
-- 컬럼명 between a and b : 칼럼의 값이 a이상 b이하인 경우 조건
-- 101 번 학과의 학생 중 몸무게 50이상 80이하인 학생의 이름(name),몸무게(weight),학과코드(deptno1)를 출력하기
select name,weight,deptno1 from student where deptno1 = 101 and weight between 50 and 80;
select name,weight,deptno1 from student where deptno1 = 101 and weight >= 50 and weight <= 80;

-- 문제 2 : 교수의 급여가 300이상 500이하인 교수의 교수번호, 이름, 급여, 보너스 출력하기
select profno, name, pay, bonus from professor where pay between 300 and 500;
select profno, name, pay, bonus from professor where pay >= 300 and pay <=500;

-- in 연산자
-- 학생 중 101,201 학과 학생의 이름,부서코드,학년을 출력하기
select name, deptno1, grade from student where deptno1 in (101, 201);
select name, deptno1, grade from student where deptno1 = 101 or deptno1 = 201;
-- 학생 중 101,201,301 학과 학생의 이름,부서코드,학년을 출력하기
select name, deptno1, grade from student where deptno1 in (101, 201,301);
select name, deptno1, grade from student where deptno1 = 101 or deptno1 = 201 or deptno1 = 301;

-- like 연산자 : 일부만 비교.
-- % : 임의의 문자 0개 이상
-- _ : 임의의 문자 1개
-- 학생 중 성이 김씨인 학생의 이름과 부서코드 출력하기
select name, deptno1 from student where name like '김%';
-- 학생 중 이름에 진 글자를 가진 학생의 이름과 부서코드 출력하기
select name, deptno1 from student where name like '%진%';
-- 학생 중 이름이 2글자인 학생의 이름과 부서코드 출력하기
select name, deptno1 from student where name like '__';

--문제 3 : 사원테이블(emp)에서 알파벳이 4자인 직원의 이름,부서코드,담당업무 출력하기
select ename,deptno,job from emp where ename like '____';
--문제 4 : 사원테이블(emp)에서 입사일자가(hiredate)가 92년도인 사원의 사원번호,이름,담당업무,급여,입사일자,부서번호를 출력하기
select empno,ename,job,sal,hiredate,deptno from emp where hiredate like '1992%';
-- 기본형식이 yy/mm/dd 형태로 설정.
select empno,ename,job,sal,hiredate,deptno from emp where hiredate like '92%';

-- not like : like의 반대.
-- 학생 중 이름의 끝자가 '훈'이 아닌 학생의 학번,이름,학과코드 출력하기
select studno,name,deptno1 from student where name not like '%훈';
-- 학생 중 성이 '김'이 아닌 학생의 학번,이름,학과코드 출력하기
select studno,name,deptno1 from student where name not like '김%';

-- not in : in 연산자의 반대. 값을 포함하지 않는
-- 사원테이블에서 부서코드가 10,30이 아닌 부서에 근무하는 사원의 이름,부서코드,급여 출력하기
select ename,deptno,sal from emp where deptno not in (10,30);

-- 컬럼명 between a and b
-- in, not in
-- like, not like
--    %
--    _

-- is null, is not null: null 값의 여부를 판단하는 연산자
-- 교수 중 보너스(bonus)가 없는 교수의 교수번호,이름,급여,보너스를 출력하기
-- select profno,name,pay,bonus from professor where bonus = null; 값이 없기 때문에 연산자(+,-,*,/,=) 사용 불가능
select profno,name,pay,bonus from professor where bonus is null;
-- 교수 중 보너스(bonus)가 있는 교수의 교수번호,이름,급여,보너스를 출력하기
select profno,name,pay,bonus from professor where bonus is not null;

-- null + 값 = null
-- null 값과 연산시 결과는 null
-- 교수의 교수번호, 이름, 급여와 상여금, 합계 (급여+상여금) 출력하기
select profno, name, pay, bonus, pay+bonus from professor;

-- 문제 5 : 교수 중 상여금이 있는 교수의 교수번호, 이름, 급여와 상여금, 합계 (급여+상여금) 출력하기
select profno, name, pay, bonus, pay+bonus from professor where bonus is not null;

/*
 order by : 조회된 행(레코드,row)을 기준에 맞도록 정렬
   asc : 오름차순 정렬. 기본값. 생략 가능.
  desc : 내림차순 정렬. 생략 불가능.
   order by 구문은 select 구문의 마지막에 작성해야함
   
  select 컬럼명1,컬럼명2,... || * from 테이블명
  [where 조건문]
  [group by 컬럼명]
  [having 그룹함수]
  [order by 컬럼명 [asc | desc]
*/
-- 1학년 학생의 이름,키를 키가 작은 순서로 출력하기
select name,height from student where grade = 1 order by height; --asc 생략가능  --오름차순
select name,height from student where grade = 1 order by 2; --조회된 (2번째) 컬럼 순서 기준으로 정렬
select name 이름,height 키 from student where grade = 1 order by 키; --별명(alias)으로 정렬
-- 1학년 학생의 이름,키를 키가 큰 순서로 출력하기
select name,height from student where grade = 1 order by height desc;           --내림차순

-- 학생의 이름,키,몸무게,학년을 출력하기
-- 단 키가 큰 순서,몸무게가 작은 순서로 정렬하기
select name,height,weight,grade from student order by height desc, weight;

-- 문제 6 : 교수의 급여를 10% 인상예정임. 교수번호, 교수이름, 학과코드, 급여, 예상급여(10%인상), 출력하기
-- 단, 학과코드 순서로 예상급여의 역순으로 출력하기
select profno, name, deptno, pay, pay*1.1 예상급여 from professor order by deptno, 예상급여 desc;
select profno, name, deptno, pay, pay*1.1 예상급여 from professor order by deptno, pay*1.1 desc;
select profno, name, deptno, pay, pay*1.1 예상급여 from professor order by 3, 5 desc;

-- 집합연산자
-- union     : 합집합. 정렬됨. 중복안됨.
-- union all : 합집합. 정렬되지 않음. 중복결과가 조회됨. 두개의 결과를 합하여 출력함
--   두개에 조회되는 컬럼의 갯수가 같아야 함.
-- 학생중 101학과 학생의 학번,이름,학과번호와, 101학과의 교수의 교수번호,이름,학과번호 출력하기
select studno,name,deptno1 from student where deptno1 = 101
union
select profno,name,deptno from professor where deptno = 101;

select studno,name,deptno1 from student where deptno1 = 101
union all
select profno,name,deptno from professor where deptno = 101;
/*
 조회되는 컬럼의 갯수가 다르므로 오류 발생
select studno,name,deptno1, deptno2 from student where deptno1 = 101
union
select profno,name,deptno from professor where deptno = 101;
*/
-- 문제 7 : 교수테이블에서 보너스가 있는 교수는 연봉을 pay*12+보너스로 하고,
--         보너스가 없는 교수는 연봉을 pay*12로 한다.
--         교수의 이름,직급,현재급여,보너스,연봉을 현재 연봉순서로 정렬하여 출력하기
select * from professor;
select name,position,pay,bonus,pay*12+bonus 연봉 from professor where bonus is not null
union
select name,position,pay,bonus,pay*12 연봉 from professor where bonus is null order by 연봉;

-- where position = '전임강사' => 전임강사인 경우
-- where position != '전임강사' => 전임강사가 아닌 경우
-- 문제 8 : 교수의 급여를 20% 인상하기로 함. 단, 직급이 전임강사는 인상대상이 아님
--         교수의 이름, 직급, 현재급여, 인상예상급여를 인상예상급여가 큰순서로 출력하기
select name,position,pay,pay 인상예상급여 from professor where position ='전임강사'
union
select name,position,pay,pay*1.2 인상예상급여 from professor where position !='전임강사' order by 인상예상급여 desc;

3장 SQL 복수행 함수(그룹함수).pdf

1.11MB