SW과정 Database 8일차 - 0804

1. Foreign Key/Primary Key

하나의 테이블에 모든 컬럼을 다 넣으면 부하가 많이 걸리므로 테이블을 나눈다. 테이블을 연결할 때 자식과 부모 관계에서 자식의 마지막 컬럼이 Foreign Key이고 부모의 첫번째 컬럼이 Primary key이다.

2. SmallTalk

한국에서 금융 정보가 유출되면 고객 책임이나 미국에서는 그 회사가 모든 책임을 지게된다. 아마존의 경우 이러한 고객정보를 보호하기 위해서 대규모 클라우드 서비스를 시작했다.

AR, VR 산업 엄청나게 뜨고 있다. 에코델타시티도 디지털 트윈으로 다 설계랑 구현을 한 다음에 실제 공사를 착수함

3. Insert 문

테이블 = Relation
데이터 값 = Tuple
열 = Column
작업 후 commit 해야지 영구적으로 기록됨
rollback : commit 한 부분 앞까지 취소됨

4. Functions

Oracle DATE 데이터의 연산

sysdate : 현재 날짜

연산	설명
날짜 데이터+숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜
날짜 데이터-숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜
날짜 데이터-날짜 데이터	두 날짜 데이터 간의 일수:차이
날짜 데이터+날짜 데이터	연산 불가, 지원하지 않음＊

---

5. 전체 코드

9. 사원번호가 115번인 사원의 first_name과 사수의 first_name을 출력하시오(self 조인)

--내코드
select first_name
from employees e
where e.employee_id=115 and e.employee_id=115.manager_id

--강사님 코드

select e.first_name as "사원 이름", e1.first_name as "사수 이름"
from employees e, employees e1
where e.employee_id=115 and e.manager_id = e1.employee_id


10. 사원번호가 125번인 사원보다 급여가 높은 사원의 이름을 출력하시오(self 조인)
    1) 조인을 사용해서
    2) 서브쿼리를 사용해서
    
-- 내코드
1) 1번 코드
select e1.first_name
from employees e, employees e1
where e.employee_id=125 and e.salary < e1.employee_id and e1.salary

2) 2번 코드
select e.first_name
from employees

-- 강사님 코드

1) 1번 코드
select e1.first_name
from employees e, employees e1
where e.employee_id=125 and e.salary < e1.salary

2) 2번 코드
select first_name, salary
from employees
where salary > (select salary
                from employees
                where employee_id=125
                )
 order by salary asc
 
 
 
 11. 커미션을 받는 사원의 이름과 부서이름을 출력하시오
 
 -- 강사님 코드
 select e.first_name, d.department_name
 from employees e, departments d
where e.commission_pct is not null
and e.department_id=d.department_id



12. 부서번호가 30인 부서에 근무에는 사원들의 이름(last_name)과 부서이름, 부서위치(city),
직급(JOB_TITLE)을 출력하시오

-- 내 코드
    select e.first_name, d.department_id, l.city, j.job_id
    from employees e, departments d, locations l, jobs j
    where d.department_id=30
        and l.location_id = d.location_id
        and d.department_id = e.department_id
        and j.job_id = e.job_id
        
-- 강사님
    select e.last_name, d.department_name, l.city, j.job_title
    from employees e, departments d, locations l, jobs j
    where e.department_id=30 
    and e.department_id = d.department_id
    and d.location_id = l.location_id
    and e.job_id = j.job_id
    
    
13. 모든 사원의 이름과 각 사원의 사수의 이름을 출력하시오

-- 내코드
select e.first_name as "사원 이름", e1.first_name as "사수 이름"
from employees e, employees e1
where e.employee_id = "1*" and e.manager_id = e1.employee_id

-- 강사님 코드
select e.first_name as "사원 이름", e1.first_name as "사수 이름"
from employees e, employees e1
where e.manager_id = e1.employee_id(+) /* null을 포함하는 반대쪽에 
outer join(외부조인) 둘 다 null값이 있으면 두개를 나누어서 해야함*/

/*null(아무것도 없다는 뜻 아니고, null값이 있다) ex) 주소가 없으면 default로 경기도
주면됨(경기도 인구 : 1200만명) 
1) null은 연산을 하면 null이 됨
2) 조인시에 컬럼에 null이 있으면 제외됨*/

ANSI SQL (지금까지 오라클 문법 사용중)

14. 모든 사원의 이름과 연봉을 출력하시오(연봉은 salary*12 + commission_pct*salary)

-- 내코드

select first_name as "name", (salary*12)+(commission_pct*salary) as "income"
from employees

-- 강사님 코드

select first_name, salary*12+salary*nvl(commission_pct,0) as "연봉"
from employees

15. 급여가 5000이상 10000이하인 사원의 이름, 급여, 부서이름, 부서가 위치한 도시(city)
이름을 출력하시오

-- 내코드

select e.first_name, e.salary, d.department_name, l.city
from employees e, departments d, locations l
where e.salary between 5000 and 10000 
and e.department_id = d.department_id(+)
and d.location_id = l.location_id

-- 강사님 코드

select e.last_name, e.salary, d.department_name, l.city 
from employees e, departments d, locations l
where (e.salary between 5000 and 10000)
and e.department_id = d.department_id(+)/*(departments에서 department_id는 PK임)*/
and d.location_id = l.location_id(+)




16. 직급이 Account, Programmer가 아닌 사원들의 이름과 직급을 출력하시오!

--내코드

select e.first_name
from employees e, jobs j
where j.job_title != 'accountant' and 'programmer'
and e.job_id = j.job_id


--강사님 코드

select e.last_name, j.job_title
from employees e, jobs j
where e.job_id=j.job_id
  and j.job_title != 'Accountant' and j.job_title <> 'Programmer'
  /* 위의 예문은 일반 조인, 하위질의 in을 사용 할 수 있음 in은 or*/
  
  select e.last_name, j.job_title
  from employees e, jobs j
  where e.job_id = j.job_id
    and e.job_id in (select job_id
                      from jobs
                      where job_title not in ('Accountant', 'Programmer')
                      )
                      
  select e.last_name, j.job_title
  from employees e, jobs j
  where e.job_id = j.job_id
    and job_title not in ('Accountant', 'Programmer')
    
-------------------------------- select end-------------------------------

insert into employees values(216,'Jongseok','Lee','NAVER','010.9935.2459',
sysdate,'ST_MAN',30000,0.5,100,50);

commit
rollback

                      
update employees
set first_name='9999', last_name='0000'
where employee_id=216

delete
from employees 
where employee_id=216

commit


-------------------------- function start ----------------------------------

1. 함수
    select sum(salary)
    from employees
    
    select avg(salary)
    from employees
    
    select max(salary)
    from employees
    
    select min(salary)
    from employees

    select count(*)
    from employees
    
    select count(commission_pct)
    from employees  
    
    각 부서의 월급의 합을 구하시오
    select department_id, sum(salary)
    from employees
    group by department_id 
    having department_id=30
    /* group by 에 대한 조건은 having */
    
    select department_id, sum(salary)
    from employees
    group by department_id 
    having department_id=30
    order by department_id 
    
    SELECT LOWER(FIRST_NAME) /*upper*/
    FROM EMPLOYEES
    
    SELECT FIRST_NAME
    FROM EMPLOYEES
    WHERE LOWER(FIRST_NAME) LIKE '%o%';
    
    SELECT CONCAT(CONCAT(FIRST_NAME, '@'), LAST_NAME)
    FROM EMPLOYEES
    /* or 기호로도 연결 가능*/
    
    SELECT initcap (lower(FIRST_NAME))
    FROM EMPLOYEES
    
    SELECT FIRST_NAME, SUBSTR(FIRST_NAME, 3, 2) --3부터 2개를 잘라냄
    FROM EMPLOYEES
    
    SELECT FIRST_NAME, SUBSTR(FIRST_NAME, 3) -- 인덱스 3이후를 다 잘라냄, 인덱스 1부터 시작
    FROM EMPLOYEES
    
    select first_name, concat(first_name, job_id), length(first_name)
    from employees
    where substr(job_id, 1, 5)='AD_VP'
    
    select first_name, instr(first_name, 's') -- 소문자 s가 나타나는 인덱스 위치
    from employees
    
    select rtrim(ltrim(' human ')) as "가"
    from dual
    -- 왼쪽에 있는 공백을 제거, 회원 가입 로그인 할 때 실수로 스페이스바를 누르면 자동으로 제거해주는 역할을 함
    
    select round(45.112,2), round(45.611,0), round(45.111, -1)
    from dual
    --반올림, 자리수(0.1 = 1)
    
    select trunc(45.115, 2), trunc(45.611,0), trunc(45.111,-1)
    from dual
    --버림
    
    select first_name, salary, commission_pct, mod(salary, commission_pct)
    from employees
    where job_id = 'SA_MAN'
    --mod 나머지
    
    select first_name as "이름", round((sysdate-hire_date)/365,0) as "근무일수"
    from employees
    
    select last_name,
       -- trunc((sysdate-hire_date)/365)||'년'||
        trunc(mod(sysdate-hire_date,365)/30)||'월'     
    from employees
    
    
    select first_name, months_between(sysdate, hire_date) as 개월
    from employees
    
    select add_months (sysdate, 3) --오늘 날짜에서 3개월을 더함
    from dual
    
    select next_day(sysdate)
    from dual
    
    select last_day(sysdate)
    from dual
    
    select first_name
    from employees
    where employee_id = '151'
    
    select to_char(sysdate, 'yyyy"년" mm"월" dd"일"')
    from employees
    
    SELECT TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') A
     , TO_CHAR(HIRE_DATE, 'YEAR') B -- 문자
     , TO_CHAR(HIRE_DATE, 'MONTH') C
     , TO_CHAR(SYSDATE, 'DY') D -- 요일 약어
     , TO_CHAR(SYSDATE, 'DAY') E -- 요일 전체
     , TO_CHAR(HIRE_DATE, 'MON') F     
      FROM EMPLOYEES;
    
    select to_char(hire_date,'mon','nls_date_language=english')
    from employees
    
    select first_name, salary+nvl(commission_pct,0)
    from employees
    
    select first_name, commission_pct, nvl2(commission_pct,'영업','사무직')
    from employees
    
    select first_name, salary
        , decode(job_id, 
            'IT_PROG', salary * 0.10,
            'ST_MAN', salary * 0.15,
            'SA_MAN', salary * 0.20,
            salary) as bonus
        from employees
    
    select first_name, salary
        , case
            when job_id = 'IT_PROG' then salary * 0.1
            when job_id = 'ST_MAN' then salary * 0.15
            when job_id = 'SA_MAN' then salary * 0.20
            else salary
            end as bonus
        from employees