ORCLE 문법

3강 부터 6강 까지-조회,검색,비교,논리연산,집합연산,함수와문자함수

/*유트브 이수안컴퓨터연구소*/
/*https://www.youtube.com/watch?v=b5pPDWYpea0&list=PL7ZVZgsnLwEEDr612UQtAdbhEXVgRniaU&index=7*/
/*실행 단축키 -> CTRL + ENTER*/
/*새로 생성한 계정 비밀번호 설정*/
ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY 1111;

/*<데이터 조회>*/
/*전체 테이블 조회*/
SELECT * FROM DEPARTMENTS;
/*툭정 colum조회*/
SELECT department_id, department_name FROM DEPARTMENTS;
/*칼럼명 변경*/
select department_id as 부서아이디, department_name as 부서명
from departments;
/*중복을 제외하고 출력*/
SELECT DISTINCT LOCATION_ID
FROM departments;
/*특정 칼럼 두개를 합쳐서 조회*/
select department_id || department_name
from departments;
/*문자를 결합한 후 조회*/
select 'department of ' || department_name
from departments;
/*문자를 결합한 후 조회+칼럼명 변경*/
select 'department of ' || department_name as 부서명
from departments;
/*산술연산+선택한 칼럼중 salary에 500을 연산*/
select first_name, last_name, salary + 500
from employees;
select first_name, last_name, salary -500
from employees;
select first_name, last_name, salary * 500
from employees;
select first_name, last_name, salary / 500
from employees;

/*3장[실습]*/
select*from countries;
select country_id, country_name from countries;
select country_id as 국가아이디, country_name as 국가명 from countries;
select distinct region_id from countries;
select country_name || country_id as 국가명 from countries;
select street_address, city from locations;
select job_id as 직업아이디, job_title as 직업명  from jobs;
select job_title, min_salary * 1.1 , max_salary * 1.1 from jobs;
select first_name ||' '|| last_name as 이름 from employees;
select distinct job_id from employees;


/*<조건검색,비교,논리연산>*/
/*특정값 조회 id가 110*/
select * from employees where employee_id=110;
/*특정값 조회 id가 110이 아닌*/
select * from employees where employee_id<>110;
/*특정값 조회 id가 110보다 작은*/
select * from employees where employee_id<110;
/*특정값 조회 id가 110보다 큰*/
select * from employees where employee_id>110;
/*특정값 조회 id가 110작거나 같은*/
select * from employees where employee_id<=110;
/*특정값 조회 id가 110크거나 같다*/
select * from employees where employee_id>=110;
/*여러조건 조회 id가 120이상130보다 작거나 같은*/
select * from employees 
where employee_id>=120 
and employee_id<=130;
/*여러조건 조회 salary가 10000이상12000보다 작거나 같은 and*/
select * from employees 
where salary>=10000 
and salary<=12000;
/*두개의 조건 중 하나만이라도 일치 조회 or*/
select * from employees
where manager_id=100 or manager_id=120;
select * from employees
where last_name='King' or last_name='Smith';
/*조건을 만족하지 않는 것만 조회 NOT*/
select * from departments
where not department_id=50;
/*id값이 50이거나 80이 아닌 값을 조회*/
select * from departments
where not department_id=50 and not department_id=80;

/*4장[실습]*/
select * from employees
where first_name='David';
select * from jobs
where min_salary=4000;
select * from jobs
where min_salary>8000;
select * from jobs
where max_salary<=10000;
select * from jobs
where min_salary>=4000 and max_salary<10000;
select * from employees
where job_id='IT_PROG' and salary>5000;

/*정렬과 집합*/
/*정렬 order by*/
select first_name, last_name 
from employees   
order by first_name;
/*내림차순 정렬 desc*/
select first_name, last_name 
from employees   
order by first_name DESC;
/*오름차순 정렬 asc(default값임)*/
select first_name, last_name 
from employees   
order by first_name ASC;
/*부서명으로 정렬 */
select department_name
from departments  
order by department_name;
/*country의 id, city로 정렬*/
select country_id, city 
from locations
order by country_id, city;
/*id 내림차순에서 name정렬*/
select location_id, department_name
from departments
order by location_id DESC, department_name;
/*두 값의 범위안의 내용만 출력 btween*/
select * 
from employees
where employee_id 
between 120 and 130;
/*salary기준*/
select*
from employees
where salary between 10000 and 12000;
/*여러개 데이터값에서 일치값만 IN*/
select *
from employees
where first_name in('Steven','Jhon','Peter');

select*
from countries
where country_id in('US','IL','SG');
/*'Sao Paulo','London','Southlake'가 아닌 것만 선택*/
select * 
from locations
where city not in ('Sao Paulo','London','Southlake');
/*is null*/
select *
from locations
where state_province is null;
select * 
from employees
where commission_pct is not null;
/*문자열 속성 부분 일치 조회 LIKE*/
/* %->0개이상의 문자 */
/* _->1개의 문자 */
/* South'로 시작하는 문자 찾는*/
select *
from locations
where city like 'South%';
/*ST 로 끝나는 데이터 조회*/
select *
from locations
where street_address like '%St';
/*south뒤에 문자4개 인 것만 조회*/
select *
from locations
where city like 'South____';
/*집합 연산*/
/*같은 속성이 일치하는 데이터 합침*/
select employee_id, first_name, department_id
from employees
where department_id=60
union
select employee_id, first_name, department_id
from employees
where department_id=80;
/*140이상과 160이하를 합치고 중복 제거*/
select employee_id ,first_name
from employees
where employee_id <=160
union
select employee_id, first_name
from employees
where employee_id>=140;
/*140이상과 160이하를 합치고 중복 포함*/
select employee_id ,first_name
from employees
where employee_id <=160
union all
select employee_id, first_name
from employees
where employee_id>=140;
/*140이상과 160이하를 제거하고 합침*/
select employee_id ,first_name
from employees
where employee_id <=160
minus
select employee_id, first_name
from employees
where employee_id>=140;
/*140이상과 160이하의 교집합 intersact*/
select employee_id ,first_name
from employees
where employee_id <=160
intersect
select employee_id, first_name
from employees
where employee_id>=140;

/*5장 [실습]*/
select job_title
from jobs
order by job_title;

select country_name
from countries
order by country_name desc;

select *
from employees
where salary between 10000 and 12000;

select *
from employees
where job_id in ('IT_PROG' , 'ST_MAN');

select *
from employees
where manager_id is NUll;

select *
from departments
where manager_id is not null;

select * 
from employees
where job_id like 'AD%';

select *
from employees
where first_name like '%ni%';

/*합집합*/
select location_id, street_address, city
from locations
where location_id <= 3000
union
select location_id, street_address, city
from locations
where location_id >= 2000;
/*교집합*/
select location_id, street_address, city
from locations
where location_id <= 3000
intersect
select location_id, street_address, city
from locations
where location_id >= 2000;
/*차집합*/
select location_id, street_address, city
from locations
where location_id <= 3000
minus 
select location_id, street_address, city
from locations
where location_id >= 2000;

/*문자함수*/
/*Lower,upper,initcap -> 소문자대문자 첫문자 변환*/
select first_name, lower(first_name), upper(first_name), initcap(first_name)
from employees;
/*substr 지정된 길이 만큼의 문자만 추출*/
select job_id, substr(job_id,1,2), substr(job_id,4)/*4번째 부터 끝까지*/
from employees;
/*특정문자를 찾아서 교체 replace*/
select job_id, replace(job_id, 'MGR','MANAGER')
from employees;
select job_id, replace(job_id, 'PROG','PROGRAMMER')
from employees;
/*concat 두개 문자열 하나로*/
select concat (first_name,concat(' ',last_name))
from employees;
/*lenght 문자열의 길이를 반환*/
select first_name, length(first_name)
from employees;
/*instr 문자열 위치값 반환*/
select first_name, instr(first_name,'a')
from employees;
/*LPAD,RPAD 특정문자를 왼쪽,오른쪽 부터 채우는 함수*/
select LPAD(first_name,10,'*'), RPAD(first_name,10,'*')
from employees;
/*LREIM,RTRIM원하는 부분을 제거*/
select job_id, LTRIM(job_id,'A'), RTRIM(job_id,'T')
from employees;
/*그냥TRIM은 전체 공백 제거*/
select TRIM(' Suan '), TRIM(' Su an')
from dual;
/*단순히 임시 테이블 dual이고 출력만 해보려할때 사용*/
select *
from dual;

/*6장[실습]*/
select job_title , lower(job_title), upper(job_title)
from jobs;
                /*first_name에서 1부터 1개만 가져와라*/
select substr(first_name,1,1), last_name
from employees;

select job_id, replace(job_id,'REP','REPRESENTATIVE')
from employees;

select concat(substr(first_name,1,1),concat(' ',last_name))
from employees;

select length(first_name) + length(last_name)
from employees;

select job_id, instr(job_id,'A')
from employees;

select city, LPAD(city,15,'.'), RPAD(city,15,'.')
from locations;

select city, LTRIM(city,'S'),RTRIM(city,'e')
from locations;

 

7강-숫자,날짜,변환, 일반 함수

/*7장*/
/*숫자,날짜,변환,일반함수*/
/*정수 올림,내림*/
select salary, salary/21,
        ceil(salary/21),floor(salary/21)
from employees;   
/*반올림, 절삭*/
select salary, salary/21,                       /*(-1)은 반올림 해줘야하는 위치가 반대로 ->소수점 기준 왼쪽으로 1칸에서 반올림*/
        round(salary/21), round(salary/21,2),round(salary/21,-1),
        trunc(salary/21), trunc(salary/21,2),trunc(salary/21,-1)
 from employees;
/*나머지 값*/
select salary, salary/21, mod (salary,21)
from employees;
/*sign 양수->1,음수->-1 나머지는 0을 반환*/
select sign(-123), sign(0), sign(123)
from dual;
/*거듭제곱*/
select power(4,3), sqrt(4)
from dual;

/*날짜 함수*/
/*현재시스템의 시간 제공*/
select sysdate, sysdate+1, sysdate-1
from dual;

select sysdate, hire_date, months_between(sysdate,hire_date)
from employees;

select hire_date, add_months(hire_date, 2), add_months(hire_date,-2)
from employees;

select hire_date,
        next_day(hire_date,3), /*현재는 화요일 3은 일월화 ->그다음 화요일 언제인지*/
        next_day(hire_date, '수요일'),
        last_day(hire_date)
from employees;

select hire_date,
        round(hire_date, 'year'),
        trunc(hire_date, 'month')
from employees;

/*변환함수*/
/*자동(암묵적)변환*/
select 1+'2'
from dual;
/*현재시간을 출력*/
select to_char(sysdate, 'yyyy/mm/dd')
from  dual;
/*몇 주차의 요일*/
select to_char(sysdate, 'w day') 
from dual;
/*숫자 날짜를 문자로*/
/*오전:시간:분:초*/
select to_char(sysdate, 'am:hh:mi:ss')
from dual;  
select to_char(sysdate, 'yy-mm-dd')
from dual;
select to_char(sysdate, 'yy"년"mm"월"dd"일"')
from dual;
/*숫자 지정 형식*/
/*9갯수만큼 자리수를 채움(구분안가면앞에0)*/
select to_char(salary, '0999999')
from employees;
select to_char(salary, '$999999')
from employees;
select to_char(salary, 'L999999')
from employees;
select to_char(salary, '99999.99')
from employees;
select to_char(salary, '9,999,999')
from employees;
/*숫자로 변환*/
select to_number('123')
from dual;
select to_number('123.123')
from dual;
select to_date('20210909','yymmdd')
from dual;
/*일반함수*/
/*null값을 치환*/
select department_name, NVL(manager_id, 100)
from departments;

select NVL(state_province, 'none')
from locations
order by state_province;

/*null과null이 아닌값 구분해서 치환*/
select department_name,
        NVL2(manager_id,'관리자있음','관리자없음')
from departments;
/*조건 논리*/
select job_title, min_salary,
        decode(min_salary,2500, min_salary*1.1, min_salary)
from jobs;

select job_title, max_salary,
        decode(max_salary,40000, max_salary*0.90, max_salary)
from jobs;

/*복잡한 논리 조건*/
select job_title, min_salary,
    case
        when min_salary <4000 then min_salary*1.2
        when min_salary between 4000 and 6000 then min_salary*1.1
        else min_salary
    end as 최소급여변동
from jobs;

/*순위함수*/
select first_name, salary,
    rank() over(order by salary desc)dense_rank,
    dense_rank() over(order by salary desc) dense_rank,
    row_number()over(order by salary desc) row_number
from employees;

/*7장 [실습]*/
select min_salary/30, 
        ceil(min_salary/30),
        floor(min_salary/30)
from jobs;

select  max_salary/30,
        round(max_salary/30,2),
        round(max_salary/30,-1)
from jobs;

select trunc(max_salary/30,2),
        trunc(max_salary/30,-2)        
from jobs;

select sysdate, add_months(sysdate,1)
from dual;
select sysdate, next_day(sysdate,2), next_day(sysdate,6)
from dual;

select to_char(sysdate,'mm/dd day')
from dual;

select to_char(sysdate, 'am hh:mm')
from dual;

select to_char(to_date('20210101','yymmdd'),'day')
from dual;

select salary, commission_pct, salary+salary*nvl(commission_pct,0)
from employees 
order by commission_pct;

select first_name, last_name, department_id, salary,
    decode(department_id, 50 , salary * 1.1, salary) as 급여인상,
    decode(department_id, 100 , salary * 0.9, salary) as 급여감소 
from employees;


select job_title, max_salary,
    case
        when max_salary >= 20000 then '상위'
        when max_salary between 10000 and 20000 then '중위'
        else '하위'
    end as 급여등급
from jobs;

 

8강 집계 및 그룹함수

/*8강 집계 및 그룹함수*/
/*count열의 행개수 */
select count (salary)
from employees;
select count(*)
from employees;
/*sum average */
select sum(salary),avg(salary)
from employees;
/*누적합*/
select first_name, salary,
    sum(salary) over(order by first_name)
from employees;
/*최대값 최소값*/
select min(salary), max(salary)
from employees;
/*표준편차 분산값*/
select stddev(salary), variance(salary)
from employees;

/*그룹 group by*/
select job_id, sum(salary), avg(salary)
from employees
group by job_id;

select job_id, sum(salary), avg(salary)
from employees
where department_id = 50
group by job_id;

select department_id, min(salary), max(salary)
from employees
group by department_id;

select country_id, count(country_id)
from locations
group by country_id
order by country_id;

select job_id, department_id, sum(salary), avg(salary)
from employees
where department_id between 50 and 100
group by job_id, department_id
order by job_id;
/*javing 그룹조건절*/
select job_id, sum(salary),avg(salary)
from employees
group by job_id
having avg(salary) > 10000;

select department_id, min(salary),max(salary)
from employees
group by department_id
having max(salary) > 7000;

select country_id, count(country_id)
from locations
group by country_id
having count(country_id) > 2
order by country_id;

/*8강 [실습]*/
select count(*)
from employees
where salary >=8000;

select count(*)
from employees
where hire_date >'20070101';

select sum(max_salary), avg(max_salary)
from jobs;

select sum(salary), avg(salary)
from employees
where job_id ='IT_PROG';

select first_name, salary , 
    avg(NVL(commission_pct, 0)) over (order by first_name)
from employees
where department_id between 50 and 80;

select min(max_salary), max(max_salary)
from jobs;

select min(max_salary),max(max_salary)
from jobs
where job_title='Programmer';

select min(hire_date), max(hire_date)
from employees
where department_id = 50;


select first_name, salary, 
    variance(salary) over (order by hire_date)
from employees
where department_id =100;

select job_id, min(salary), max(salary)
from employees
where hire_date between '20040101' and '20061231'
group by job_id;

select department_id, job_id, sum(salary),min(salary),max(salary)
from employees
where department_id in (50,80)
group by department_id, job_id
order by job_id;

select department_id, job_id, min(salary), max(salary), avg(salary)
from employees
group by department_id, job_id
having avg(salary) >= 12000
order by department_id;

9강- 조인 10강- 서브쿼리

/*9장 조인연산*/
/*카티션 곱-공통되는 칼럼 없이 조인 조건이 없어서 모든 데이터 조회*/
select*
from employees, departments;
/*내부조인(inner,교집합,동등조인)*/
select *
from jobs, job_history
where jobs.job_id = job_history.job_id;

select *
from countries C, locations L
where C.country_id = L.country_id;

/*비동등조인(공통 조건 없이 조인)*/
select*
from employees E, jobs J
where E.salary between j.min_salary and J.max_Salary;

/*외부조인(Outer,외부조인)*/
select *
from jobs, job_history
where jobs.job_id = job_history.job_id(+);

/*자체조인(self)*/
select E.first_name, E.last_name, M.first_name, M.last_name
from employees E, employees M
where E.manager_id = M.employee_id;

/*9장 [실습]*/
select C.country_name, L.state_province, L.street_address
from countries C, locations L
where C.country_id = L.country_id;

select J.job_id, J.job_title, H.start_date, H.end_date
from jobs J, job_history H
where J.job_id = H.job_id;

select E.first_name, E.last_name, D.department_name, J.job_title
from employees E, departments D, jobs J
where E.department_id = D.department_id
    and E.job_id = J.job_id;
    
select C.country_id, C.country_name, L.city    
from countries C, locations L
where C.country_id = L.country_id(+);

select E.employee_id, E.first_name, E.last_name, D.department_name
from employees E, departments D
where E.department_id = D.department_id(+)
order by E.employee_id;

select E.first_name ||  ' ' || E.last_name  employee,
       M.first_name ||  ' ' || M.last_name manager
from employees E, employees M
where E.manager_id = M.employee_id
order by E.employee_id;

/*10장 서브쿼리*/
/*단일행*/
select *
from employees
where phone_number = ( select phone_number
                        from  employees
                        where employee_id = 100);                      
select *
from employees
where phone_number != ( select phone_number
                        from  employees
                        where employee_id = 100);
                    
/*다중행*/
/*in,not in*/
select *
from employees
where salary in (select max(salary)
                from employees
                group by department_id);
/*exist*/
select *
from employees
where exists(select * 
            from employees
            where employee_id = 100);
/*any 여러개 중에 하나만이라도 일치하면 all모든 조건을 만족*/
select*
from employees
where salary = any(6000,10000,12000);

select*
from employees
where salary > any(6000,10000,12000);

select*
from employees
where salary < all(6000,10000,12000);

select*
from employees
where salary <> all(6000,10000,12000);

select *  /*직원 salary테이블에서 080101이후 입사자들의 연봉이 작은 직원하나의 기준 맞는것에 대한 전체*/
from employees
where salary < any (select salary
                    from employees
                    where hire_date > '08/01/01');
/*인라인 뷰(inline view*/
select*
from employees E, (select department_id
                    from departments
                    where department_name = 'IT') D
where E.department_id = D.department_id;

/*10강 [실습]*/

select first_name, last_name, job_id, salary
from employees
where department_id = (select department_id
                        from departments
                        where department_name = 'IT');

select department_id, department_name
from departments
where location_id = (select location_id
                    from locations
                    where state_province = 'California');
                    
select city, state_province, street_address
from locations
where country_id IN(select country_id       /*다중행이 되어버림->IN*/
                    from countries           
                    where region_id = '3');

select first_name, last_name, job_id, salary
from employees
where department_id  IN (select department_id
                        from departments
                        where manager_id IS NOT NULL);
                        
select department_id, department_name
from departments
where location_id not in( select location_id    /*seattle에 속하지 않은 부서*/                   
                    from locations
                    where city = 'Seattle');

select city, state_province, street_address
from locations
where country_id in(select country_id
                    from countries
                    where region_id = (select region_id
                                        from regions
                                        where region_name = 'Europe'));

11강-데이터 삽입, 수정, 삭제 

/*11강 데이터 삽입, 수정, 삭제*/
select*
from countries;

/*삽입 insert*/
insert into countries
values ('KR','South Korea', 3);
select*from departments order by department_id desc;
insert into departments( department_id, department_name, location_id)
values(290, 'Data Analytics', 1700);
insert into departments
values (280,'Testing', 200, 1700);
/*수정 update*/
select* from countries;

update countries
set country_name ='Democratic People''s Republic of Korea'    /*싱글쿼테이션안에 싱글쿼테이션을 넣어주려면 연달아서''*/
where country_id='KP';

select*from departments order by department_id desc;

update departments
set department_name = 'IT Quality Assurence'
where department_id = '280';

/*삭제 delete*/
select*from countries;

delete
from countries
where country_id = 'KR';

/*11강 [실습]*/
select* from countries;

insert into countries( country_id, country_name, region_id)
values('AT','Austria',1);
insert into countries( country_id, country_name, region_id)
values('NO','Norway',1);
insert into countries( country_id, country_name, region_id)
values('ID','Indonesia',3);
insert into countries( country_id, country_name, region_id)
values('TR','Turkey',4);

select*from departments order by department_id desc;

INSERT INTO departments
values (280, 'Adevertising', 200, 1700);

INSERT INTO jobs
values ('IT ENG', 'Engineer', 8000, 15000);

insert into departments( department_id, department_name, location_id)
values (300, 'Education', 1700);

select*from jobs where job_id='ADV';
insert into jobs (job_id, job_title, min_salary, max_salary)
values('ADV','Advisor',10000,20000);

insert into jobs (job_id, job_title, min_salary, max_salary)
values('IT ENG','Engineer',8000,15000);

insert into jobs (job_id, job_title, min_salary, max_salary)
values('IT RES','Researcher',12000,22000);

update departments 
set location_id = 2000
where department_id in(280,290);

select* 
from  departments
where department_id in(280,290);

update departments 
set manager_id = 102, location_id = 2000
where department_id in(300,310);

select*from jobs;

update jobs
set min_salary=12000, max_salary=22000
where job_id='ADV';

update jobs
set max_salary=16000
where job_id='IT ENG';

update jobs
set job_title='Research Engineer', max_salary=24000
where job_id='IT RES';

delete 
from countries
where country_id in('AT','NO','ID','TR');

delete from departments
where department_id between 280 and 310;

delete from jobs
where job_id in('ADV','IT ENG','IT RES');