[ 7일차 ] 빅데이터활용 CASE구문, 트리거만들기 +SQL 연습문제 8문제 + JOIN 연습문제 8문제

SQL

[ 7일차 ] 빅데이터활용 CASE구문, 트리거만들기 +SQL 연습문제 8문제 + JOIN 연습문제 8문제

ggosoon 2022. 6. 10. 11:17

*함수와 메소드의 차이

클래스안에 들어가있으면 메소드

클래스밖에 들어가있으면 함수

(파이썬에서는 클래스 안에 들어가있어도 함수라고 함)

bigEmployees.csv

16.04MB

[빅데이터 엑셀파일 가져오기]

테이블 오른쪽마우스클릭 -> 데이터임포트 , 구분자(;)으로 바꿔서 가져오기

CREATE TABLE Test002

( "EMP_NO" NUMBER(7,0),

"BIRTH_DATE" DATE,

"FIRST_NAME" VARCHAR2(26 BYTE),

"LAST_NAME" VARCHAR2(26 BYTE),

"GENDER" VARCHAR2(26 BYTE),

"HIRE_DATE" DATE

);

select * from Test002; --레코드 없음으로 보임

--1. 생년월일에서 값이 5로 시작되는 사람을 출력하시오.

select * from bigdata

where BIRTH_DATE like '5%' ;

select count(*) from bigdata

where BIRTH_DATE like '5%' ; --182,886명

select count(*) from bigdata

where BIRTH_DATE like '52%' ; -- 52년생 21,209명

--2.52년생 데이터를 Test002테이블에 추가하시오. (insert~select 구문)

insert into Test002 select * from bigdata

where BIRTH_DATE like '52%';

select * from Test002;

--3.생년월일을 / / 되어있는 부분을 년,월,일로 바꾸기 (문자열함수 substr)

select substr(BIRTH_DATE,1,2)||'년'||substr(BIRTH_DATE,4,2)||'월'||substr(BIRTH_DATE,7,2)||'일'

as "생년월일" from Test002;

select EMP_NO,

substr(BIRTH_DATE,1,2)||'년'||substr(BIRTH_DATE,4,2)||'월'||substr(BIRTH_DATE,7,2)||'일' as "생년월일",

FIRST_NAME,

LAST_NAME,

GENDER,

HIRE_DATE

from Test002;

--4.GENDER의 F와 M을 바꿔보기 (case 구문)

select EMP_NO,

substr(BIRTH_DATE,1,2)||'년'||substr(BIRTH_DATE,4,2)||'월'||substr(BIRTH_DATE,7,2)||'일' as "생년월일",

FIRST_NAME,

LAST_NAME,

GENDER,

CASE

when GENDER = 'F' then '여성'

when GENDER = 'M' then '남성'

else '기타'

end as "성별",

HIRE_DATE

from Test002;

--5. HIRE_DATE 옆에 80년대입사, 90년대입사 추가해서 만들기 (case 구문)

select EMP_NO,

substr(BIRTH_DATE,1,2)||'년'||substr(BIRTH_DATE,4,2)||'월'||substr(BIRTH_DATE,7,2)||'일' as "생년월일",

FIRST_NAME,

LAST_NAME,

GENDER,

CASE

when GENDER = 'F' then '여성'

when GENDER = 'M' then '남성'

else '기타'

end as "성별",

HIRE_DATE,

CASE

when HIRE_DATE like '8%' then '80년대입사'

when HIRE_DATE like '9%' then '90년대입사'

else '기타' -- else 생략가능

end as "입사일자"

from Test002;

when substr(HIRE_DATE,1,2) >=90 then '90년대입사'

when substr(HIRE_DATE,1,2)<90 then '80년대입사'

(*보통은 문자열 함수로 많이 쓴다)

--6. 위 테이블을 뷰테이블로 만들기

create view v_bigdata1

select EMP_NO,

substr(BIRTH_DATE,1,2)||'년'||substr(BIRTH_DATE,4,2)||'월'||substr(BIRTH_DATE,7,2)||'일' as "생년월일",

FIRST_NAME,

LAST_NAME,

GENDER,

CASE

when GENDER = 'F' then '여성'

when GENDER = 'M' then '남성'

else '기타'

end as "성별",

HIRE_DATE,

CASE

when substr(HIRE_DATE,1,2)<90 then '80년대입사'

when substr(HIRE_DATE,1,2)>=90 then '90년대입사'

else '기타'

end as "입사년도"

from Test002;

select * from v_bigdata1;

--7. 성별 별로 몇명인지 나타내기

select 성별, count(성별) as 인원수 from v_bigdata1

group by 성별;

--8. 위 인원수에서 숫자 0 을 한글 공으로, 숫자 2를 한글 이로, 숫자5를 한글오로 출력하기

[ 교재 252 ] translate

select 성별, translate(count(성별),025,'공이오') as 인원수 from v_bigdata1 --일대일로 매칭됨

group by 성별;

--9. 첫번째 이름의 길이를 구하시오

select FIRST_NAME, length(FIRST_NAME) from v_bigdata1;

select FIRST_NAME "이름", length(FIRST_NAME) "길이" from v_bigdata1; --별칭 써주기

정렬하기

select FIRST_NAME "이름", length(FIRST_NAME) "길이" from v_bigdata1

order by length(FIRST_NAME);

select FIRST_NAME "이름", length(FIRST_NAME) "길이" from v_bigdata1

order by 길이; --별칭으로도

select FIRST_NAME "이름", length(FIRST_NAME) "길이" from v_bigdata1

order by 길이 desc; -- 내림차순 정렬하기 제일 긴 사람 14자리

--길이 지정하고 나머지 빈공간을 #으로 채우기 (RPAD함수)

select RPAD(FIRST_NAME,20,'#') "이름", length(FIRST_NAME) "길이" from v_bigdata1

order by 길이 desc;

--대문자로 바꿔주기

select RPAD(upper(FIRST_NAME),20,'#') "이름", length(FIRST_NAME) "길이" from v_bigdata1

order by 길이 desc;

--[교재 255 ] 공백 제거하기 (trim)

select Ltrim(' 1234 ') as "값" from dual; -- 왼쪽 공백 제거

select Ltrim(' 1234 ') as "값1", trim(' 1234 ') as "값2", Rtrim(' 1234 ') as "값3" from dual;

이처럼 select Ltrim('1 2 3 4') 문자 사이의 공백을 제거할수는 없음

--올림, 내림, 반올림 함수 (ceil , floor, round )

select ceil(4.1), ceil(4.5), ceil(4.0)

from v_bigdata1;

-> 5, 5, 4

select floor(4.1), floor(4.5), floor(4.0)

from v_bigdata1;

->4, 4, 4

select round(4.1), round(4.5), round(4.0)

from v_bigdata1;

->4, 5, 4

-- 원본테이블

select * from bigdata;

--트리거 실행 테이블

CREATE TABLE T_bigdata

(

"EMP_NO" NUMBER(7,0),

"BIRTH_DATE" DATE,

"FIRST_NAME" VARCHAR2(26 BYTE),

"LAST_NAME" VARCHAR2(26 BYTE),

"GENDER" VARCHAR2(26 BYTE),

"HIRE_DATE" DATE,

delDate date --추가해서 트리거 실행 테이블 만들기

);

--삭제시 추가가 발생하는 트리거 만들기

create or replace TRIGGER del_bigdata

AFTER delete

on bigdata

for each row

begin

insert into T_bigdata values (:old.EMP_NO,:old.BIRTH_DATE,:old.FIRST_NAME,:old.LAST_NAME,:old.GENDER,sysdate()); --old는 삭제전에 메모리에 안고있는 것들

end;

delete from bigdata where EMP_NO<10006; --5행 삭제

select * from T_bigdata; -- 5행 들어온걸 확인할 수 있음

[연습문제]

drop table enrol;

drop table student;

drop table course;

create table student(

sno varchar2(3) ,

sname nvarchar2(10),

year varchar2(10),

dept nvarchar2(10) ,

constraint student_sno_pk primary key(sno));

create table course(

cno varchar2(5) primary key,

cname nvarchar2(10),

credit varchar2(3),

dept nvarchar2(10),

prname nvarchar2(10));

create table enrol(

sno varchar2(3) ,

cno varchar2(5) ,

grade varchar2(3),

midterm int,

final int ,

constraint enrol_sno_fk foreign key(sno) references student(sno),

constraint enrol_cno_fk foreign key(cno) references course(cno),

constraint enrol_combo_pk primary key(sno, cno));

insert into student(sno,sname,year,dept)

values('100','나연묵','4','컴퓨터');

insert into student(sno,sname,year,dept)

values('200','이찬영','3','전기');

insert into student(sno,sname,year,dept)

values('300','정기태','1','컴퓨터');

insert into student(sno,sname,year,dept)

values('400','송병호','4','컴퓨터');

insert into student(sno,sname,year,dept)

values('500','박종화','2','산공');

insert into course(cno,cname,credit,dept,prname)

values('C123','프로그래밍','3','컴퓨터','김성기');

insert into course(cno,cname,credit,dept,prname)

values('C312','자료구조','3','컴퓨터','황수찬');

insert into course(cno,cname,credit,dept,prname)

values('C324','화일처리','3','컴퓨터','이규철');

insert into course(cno,cname,credit,dept,prname)

values('C413','데이터베이스','3','컴퓨터','이성호');

insert into course(cno,cname,credit,dept,prname)

values('E412','반도체','3','전자','홍봉희');