kosta 클라우드 네이티브 애플리케이션 개발 과정 day 23

kosta 클라우드 네이티브 애플리케이션 개발 과정 day 23

 

---

sql 쿼리 연습 ! 

 

-- 2. 평균 급여가 가장 높은 부서는 ?

SELECT AVG(SALARY) avg_salary
FROM EMPLOYEES employees
GROUP BY DEPARTMENT_ID

SELECT MAX(AVG(SALARY)) max_avg_salary
FROM EMPLOYEES employees
GROUP BY DEPARTMENT_ID


SELECT d.DEPARTMENT_NAME
FROM DEPARTMENTS d,
     (SELECT DEPARTMENT_ID
      FROM EMPLOYEES
      GROUP BY DEPARTMENT_ID
      HAVING AVG(EMPLOYEES.salary) = (SELECT MAX(AVG(employees.SALARY)) max_avg_salary
                                      FROM EMPLOYEES employees
                                      GROUP BY DEPARTMENT_ID)) get_department_id
WHERE get_department_id.DEPARTMENT_ID = d.DEPARTMENT_ID


-- 평균 급여가 가장 높은 지역(대륙)은 ?
SELECT REGION_NAME , avg_salary
FROM (SELECT ROWNUM rn, regions_main.REGION_NAME, avg_salary
      FROM REGIONS regions_main,
           (SELECT AVG(SALARY) avg_salary, regions.REGION_ID
            FROM EMPLOYEES employees,
                 DEPARTMENTS departmenrts,
                 Locations locations,
                 Countries countries,
                 REGIONS regions
            WHERE employees.DEPARTMENT_ID = departmenrts.DEPARTMENT_ID
              AND departmenrts.LOCATION_ID = locations.LOCATION_ID
              AND locations.COUNTRY_ID = countries.COUNTRY_ID
              AND countries.REGION_ID = regions.REGION_ID
            GROUP BY regions.REGION_ID
            ORDER BY 1 DESC) sub
      WHERE sub.REGION_ID = regions_main.REGION_ID)
WHERE rn = 1;

 

---

 

 

DCL을 배워보자 ! 

 

CREATE USER

ALTER USER 

DROP USER 

 

CREATE USER user IDENTIFIED BY password
ALTER USER user IDENTIFIED BY password 
DROP USER user [CASCADE]

 

 

일반적으로 CONNECT, RESOURCE의 ROLE을 부여하면 일반사용자 역할을 할 수 있음 

=> 접속, select 하려면 필요 ! 

 

 

계정 관리하기 

connect system/manager

create user webdb identified by 1234
grant resource, connect to webdb; commit

alter user webdb identified by webdb; 

drop user webdb cascade;

 

 

hr 계정에서 로그아웃하고 새로운 계정 만들기 

 

alter user hr IDENTIFIED  by hr account unlock;
commit;

create user webdb IDENTIFIED by <비밀번호>;
grant RESOURCE, connect to webdb;
commit;

 

 

 

테이블 만들기 

 

CREATE TABLE book
(
    book_id  number(5),
    title    varchar2(50),
    author   varchar2(10),
    pub_date date
)

 

char : 고정길이 문자열

varchar2 : 가변길이 문자열 

number : 숫자 데이터 

date : 날짜 + 시간 

 

 

 

pk 설정시엔 테이블당 1개만 설정하다. 단 여러개를 묶어서 설정은 가능 

 

 

book table 지우고 다시 만들고 데이터 입력하기 

DROP TABLE book;

CREATE TABLE book
(
    book_id   number(10),
    title     varchar2(100) NOT NULL,
    pubs      varchar2(100),
    pub_date  date,
    author_id number(10),
    PRIMARY KEY (book_id),
    CONSTRAINT constraint_book_fk FOREIGN KEY (author_id)
        REFERENCES author (author_id)
);

insert into author
VALUES (1, '박경리', '토지 작가')

 

 

업데이트 하기 

 

update author
set author_name = '기안84',
    author_desc = '웹툰작가'
where author_id = 1; 

 

업데이트에서 중요한 것은 where절을 하지 않으면 모든 레코드에 적용된다 ! 

 

 

delete도 마찬가지 

 

delete from author
where author_id = 1

delete from author;

 

 

 

 

 

---

sequence / sysdate 

 

sequence(시퀀스) : 연속적인 일련번호 생성 -> pk에 주로 사용됨 

 

CREATE SEQUENCE seq_author_id 

INCREMENT BY 1

START WITH 1; 

 

객체를 생성하면 schema에 올라가는 것을 확인할 수 있다. 

 

 

테이블 자체에 대해 조회하는 법 

 

SELECT *
from COLS
where TABLE_NAME = 'BOOK';

 

 

시퀀스 사용 

select * from user_sequences;

select seq_author_id.currval from dual;

select seq_author_id.nextval from dual; 

drop sequence seq_author_id;

 

 

 

 

---

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '이문열', '경북 영양')

SELECT *
FROM COLS
WHERE TABLE_NAME = 'BOOK';

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '이문열', '경북 영양')

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '박경리', '경상남도 통영')

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '유시민', '17대 국회의원')

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '기안84', '기안동에서 산 84년생')

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '강풀', '온라인 만화가 1세대')

INSERT INTO author
VALUES (author_sequence_by_id.nextval, '김영하', '알쓸신잡')

SELECT *
FROM author;



INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '우리들의 일그러진 영웅', '다림', TO_DATE('1998/02/22', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '삼국지', '민음사', TO_DATE('2002/03/01', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '토지', '마로니에북스', TO_DATE('2012/08/15', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '유시민의 글쓰기 특강', '생각의길', TO_DATE('2015/04/01', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '패션왕', '중앙북스(books)', TO_DATE('2012/02/22', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '순정만화', '재미주의', TO_DATE('2011/08/03', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '오직두사람', '문학동네', TO_DATE('2017/05/04', 'YYYY/MM/DD'), 1);

INSERT INTO BOOK
VALUES (book_sequence_by_id.nextval, '26년', '재미주의', TO_DATE('2012/02/04', 'YYYY/MM/DD'), 5);


SELECT *
FROM book;



UPDATE book
SET author_id = '6'
WHERE book_id = 7;



SELECT book.book_id, book.title, book.PUBS, book.pub_date, book.author_id, author.author_name, author.author_desc
FROM BOOK book,
     AUTHOR author
WHERE book.author_id = author.author_id;