DB 복습 2

- employees 테이블에서 부서를 배정받지 못한 사원들의 이름과 이메일 정보를 검색하라

 SELECT Last_Name, email
 FROM employees
 where department_id is null;

 

 

 

부서번호 오름차순으로 사원이름, 부서번호, 급여액을 표시하되 부서를 배정받지 못한 사원은 가장 먼저 출력한다
(Order by ~ nulls first/last 사용)

SELECT Last_Name, Department_ID, salary
FROM employees
ORDER BY Department_ID nulls FIRST;


or

SELECT Last_Name, Department_ID, salary
FROM employees
ORDER BY Department_ID nulls FIRST, salary ASC;  // 한 컬럼을 기준으로 정렬 후 그 내에서 다른 컬럼을 기준으로 정렬
// 승점이 똑같다면 득실차로 순위를 구분하는 것과 같은 정렬

 

--------------------------------------------------------------------------------------------------------------------------------------------
사원들을 입사일 기준으로 고참부터 정렬하되
번호, 이름, 급여를 표시하고 컬럼명을 출력하지 않고
번호, 이름, 급여 처럼 별칭을 사용하시오


SELECT employee_id "번호", last_name "이름", salary "급여"
FROM employees
ORDER BY hire_date;

--------------------------------------------------------------------------------------------------------------------------------------------
논리표현식
(SQL은 할당식 "="이 없다)   -> '=' 논리비교로서 사용(같냐 다르냐)
'!=' -> C++과 동일
'not' -> 뒤에 오는 논리값을 부정한다

부서번호가 50도 아니고 60도 아닌 사원들 중에서 입사일이 03년 이전이 아닌 사원들의 이름, 부서번호, 입사일을 표시하라 


SELECT last_name "이름", department_Id "부서번호", hire_date "입사일"
FROM employees
where department_id != 50 and department_id != 60 and hire_date >= to_date('02/12/31');

--------------------------------------------------------------------------------------------------------------------------------------------
비교표현식
- Column between A and B : Column이 A와 B사이에 있다면 출력 (범위지정)
- Column In(A, B) : 각 Column의 행 값이 A 또는 B와 일치한다면 출력

사원들의 직무(Job)이 ST_Man도 아니고, ST CLERK도 아닌 사원들의 이름, 직무를 표시 (in사용할 것)

SELECT last_name "이름", job_Id "직무"
FROM employees
where job_id not in('ST_MAN', 'ST_CLERK');
--------------------------------------------------------------------------------------------------------------------------------------------

Like 패턴검색 %, _ 
% : 문자가 한개도 없거나 한개 이상인 경우의 와일드 카드 예) Like 'A%' -> 시작 단어가 A로 시작하는 단어 모두 출력
_ : 문자 한개 예) Like 'A__m' -> A00m(A와 m사이의 글자가 2개인 사람-_가 2개라서)

last_name이 'e'로 끝나는 모든 사원의 last_name을 표시하라

SELECT last_name "성"
FROM employees
where last_name like('%e')
Order By last_name ASC;

--------------------------------------------------------------------------------------------------------------------------------------------
case문
- case + 표현식: 정리하면 값이 나오는 것들?(1+2같은 것)(예- Column) + when + 값 + then + 결과 + (else 결과)  + end
  -> 표현식과 값을 비교했을때 같으면 then 뒤의 결과 출력
      다르면 다음으로 넘어가서 else에서 끝(else에 있는 결과 출력)

- 표현식이 없는 경우
 case + when + condition(조건) + then

10~50번 부서번호를 운영팀, 개발팀, 연구팀, 생산팀, 마케팅으로 변경하여 각 사원의 이름, 부서번호, 부서명을 표시하라
SELECT first_name "사원명", department_id "부서번호", case department_id
    when 10 then '운영팀'
    when 20 then '개발팀'
    when 30 then '연구팀'
    when 40 then '생산팀'
    else '마케팅'
    end 부서명
FROM employees
where department_id<=50
Order by department_id;

--------------------------------------------------------------------------------------------------------------------------------------------
03년도 입사자와 50번 부서의 직원들은 5%급여를 인상하여 사원의 이름, 입사일, 원래급여, 인상된 급여를 표시하라

SELECT first_name "사원명", hire_date "입사일", salary "원래 급여",
        case when department_id = 50 then salary*1.05 
        when hire_date between to_date('03/01/01') and to_date('03/12/31') then salary*1.05 
        end "인상된 급여"
FROM employees
order by hire_date;

--------------------------------------------------------------------------------------------------------------------------------------------
SubQuery = Query 안에 있는 Query(아래에 있는 Query)

select * from employees
where department_id In(select department_id from employees);
select 안에서 select를 한번 더

부서가 50이거나 job_id가 IT_PROG인 모든 사원의 정보를 표시하라
☆☆☆다시풀기☆☆☆
->> 이런 subQuery는 '인라인뷰'라고 한다


SELECT * from 
(
    SELECT * from employees
    where department_id = 50 or job_id = 'IT_PROG'
)


david lee와 같은 부서사람들을 출력하라

SELECT * from employees
where department_id in(select department_id from employees where first_name = 'David' and last_name = 'Lee');
--------------------------------------------------------------------------------------------------------------------------------------------
Allan과 Peter, Oliver와 같은 사무실 직원들의 이름, 부서번호, 급여를 표시하고, 부서번호 순으로 정렬하라

SELECT first_name "사원명", department_id "부서번호", salary "급여"
from employees
where department_id in
(
    select department_id from employees
    where first_name in('Allan','Peter','Oliver')
)
Order by salary;



또는

SELECT first_name "사원명", department_id "부서번호", salary "급여"
from employees
where department_id = Any
(
    select department_id from employees
    where first_name in('Allan','Peter','Oliver')
)
Order by salary;

--------------------------------------------------------------------------------------------------------------------------------------------
▣함수
값을 가져오라
예) 오늘의 날짜를 가져오라 sysdate
Select Sysdate as "오늘의 날짜" From dual;
->함수지만 파라미터가 없기 때문에 괄호가 없다

▣Ceil / floor
- Select CEIL(1.234), Floor(1.234) from dual;
-> 2 1

Ceil(A) = A바로 위의 정수
Floor(A) = A바로 밑의 정수

▣ 수학관련 함수 - 모든 언어에 있다
- ABS : 절댓값 출력
예) ABS(-10) => 10

- Sign : 부호 출력 ( -1, 0 ,1)
예) Sign(-10), Sign(0), Sign(10)  -> -1, 0, 1

- Exp : 지수함수의 지수 (e^n 에서 n) -> e = 자연상수? 2.718281828....
예) Exp(1) -> 1.71818

- Sqor : 제곱근
예) SQRT(9) -> 3

- Log : 로그함수
예)Log(3,9) -> 2

- LN : 밑이 e(자연상수)인 로그
예) LN(2.718281828)

- Mod(modulor [모듈러]): 나눗셈의 나머지
예) Mod(10, 3) -> 

- sin, cos, tan : 삼각함수
- Asin, ACos, ATan : ?

▣ 문자열 함수
- Length : 문자열의 길이 출력
예) Select Length('Hello') From dual; -> 5

- ASCII : 해당 문자의 아스키코드 출력
예) ASCII('A'), ASCII('a') from dual -> 65 97

- Chr : 아스키코드를 입력받아 해당하는 문자 출력
예) Chr(65) from dual -> A

- Upper / Lower : 소->대문자 / 대 -> 소문자로 변형해서 추력
예) Upper('abc'), Lower('Hello') from dual -> ABC hello

- Ltrim /Rtrim -> (좌/우)공백 없애기
- Trim -> 공백 없애기
예) 로그인시 공백이 있을 경우 공백 인식 방지

- LPad/RPad('A', B, 'C') : A 왼쪽/오른쪽으로 C를 덧붙여 B가 되게 하라
예) LPad('hello', 10, '-') from dual -> -----hello



모든 first_name에 '-' 2개를 왼쪽에 덧대서 표시하시오

Select LPAD(first_name, Length(first_name) + 2, '-')From employees;
--------------------------------------------------------------------------------------------------------------------------------------------
- Substr('A', B, C) : A를 B번째 부터 C번째 까지 잘라줭
예) (first_name, 0, 3) from employees ->  3글자

Substr을 이용하여 각 사원의 입사 연도만 추출하여 표시하라

Select SubStr(Hire_date, 0 ,2) from employees;


입사연도가 07연도인 사원의 이름과 부서번호, 입사연월일을 표시하라

Select first_name "사원명", department_id "부서번호", hire_date "입사일"
from employees
where substr(hire_Date, 0, 2) = '07'
Order by hire_date;

--------------------------------------------------------------------------------------------------------------------------------------------
Instr('A','b') : A안에 b가 있는지 -> 있으면 1 없으면0?
Replace('A', 'B') : A를 B로 대체한다
Concat('A', 'B') : A와 B를 붙여서 1개의 문자열로 만든다


first_name, last_name을 붙여서 "이름"으로 표시할 때 12자가 넘는 문자열은 13번째에 ...을 붙여서 표시하라

Select Case When Length(Concat(first_name, last_name)) > 12
            then Concat(Substr(Concat(first_name,last_name),0,12), '...')
            else Concat(first_name, last_name)
            end "이름"
from employees;




--------------------강사님 답안------------------------------

Select Case When Length(Concat(first_name, last_name)) > 12
            then Substr(Concat(first_name,last_name),0,12) || '...'
            else first_name || last_name
            end "이름"
from employees;