어제 문제
--1. professor, department, student 테이블을 사용하여
-- position별로 가장 적은 급여(pay)를 받는
-- 교수의 이름, position, pay, 학과명, 지도학생수 출력
--1-1) 다중컬럼
select * from professor;
select * from department;
select * from student;
select p.name, p.position, p.pay, d.dname, count(s.studno) as 지도학생수
from professor P, department d, student s
where p.deptno = d.deptno
and p.profno = s.profno(+) -- 선택적 관계니깐 outer join 해야겠다고 생각하기
and (p.position, p.pay) in (select position, min(pay)
from professor
group by position)
group by p.name, p.position, p.pay, d.dname;
--1-2) 인라인뷰 - 대부분의 경우에 셋 중에 성능이 가장 좋다. 가끔 아닐 수도 있음.
select p.name, p.position, p.pay, d.DNAME, count(s.STUDNO)
from professor p,
(select position, min(pay) as min_pay
from professor
group by position) i,
department d,
student s
where p.pay = i.min_pay
and p.position = i.position
and p.deptno = d.DEPTNO
and p.profno = s.PROFNO(+)
group by p.name, p.position, p.pay, d.DNAME;
--1-3) 상호연관
select p.name, p.position, p.pay, d.dname, count(s.studno) as 지도학생수
from professor P, department d, student s
where p.deptno = d.deptno
and p.profno = s.profno(+)
and p.pay = (select min(pay)
from professor p2
where p2.position = p.position)
group by p.name, p.position, p.pay, d.dname;--2. subway.csv 파일을 subway 테이블을 만들어 적재한 뒤
-- 노선별 승차가 가장 많은 시간대 확인
-- (시간 506 => 5시~6시를 의미)
create table subway(
노선번호 varchar2(10),
시간 number,
승차 number,
하차 number);
select * from subway;
--2-1) 다중컬럼
select 노선번호,max(승차)
from subway
group by 노선번호;
select *
from subway
where (노선번호, 승차) in (select 노선번호,max(승차)
from subway
group by 노선번호);
--2-2) 인라인뷰
select *
from subway s,
(select 노선번호, max(승차) as max_승차
from subway
group by 노선번호) i
where s.노선번호=i.노선번호
and s.승차 = i.max_승차;
--2-3) 상호연관
select *
from subway s1
where 승차 = (select max(승차)
from subway s2
where s2.노선번호=s1.노선번호);--3. 아래 테이블 생성 후 delivery.csv 파일을 적재한 뒤
-- 해당 테이블을 사용하여 각 시간대별 가장 인기 있는 음식 출력(자유)
create table delivery(
일자 varchar2(10),
시간대 varchar2(10),
업종 varchar2(50),
시도 varchar2(50),
시군구 varchar2(50),
읍면동 varchar2(50),
통화건수 number);
select * from delivery;
select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대;
select 시간대, max(d.통화건수)
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) D
group by 시간대;
select 시간대, 업종
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) d
where (시간대, 통화건수) in (select 시간대, max(d.통화건수)
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) D
group by 시간대);
-- 인라인뷰 - 이건 0.05초. 위에서 다중컬럼으로 한 코드는 1.5초. 약12만건의 데이터에서 1.5초 차이나네. 무조건 인라인뷰부터 만들어야겠다. 그리고 내가 실력적으로 인라인뷰 만드는 게 부족한듯
select d.시간대, d.업종
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) d,
(select 시간대, max(d.통화건수) as max_통화건수
from (select 시간대, 업종, sum(통화건수) as 통화건수
from delivery
group by 시간대, 업종
order by 시간대) D
group by 시간대) i
where d.시간대=i.시간대
and d.통화건수=i.max_통화건수;수업 내용
— [with문]
— 쿼리 내 임시 테이블을 만드는 문법(해당 쿼리에서만 유효, 실제 저장되지는 X)
— 쿼리의 가독성을 높이기 위해 사용
— select문에서 주로 사용(dml에서도 사용가능)
— 문법
— with 임시테이블명(컬럼1,컬럼2,…)
— as
— (select …) — 저장하고자하는 데이터 형태 정의
— sql 문장; — 임시테이블을 사용하여 처리할 sql문장
-- 예)
with std_test(name, grade, height)
as
(select name, grade, height
from student
where grade=4)
select * from std_test;-- 예제) student, exam_01을 조인하여 학번, 이름, 학년, 시험성적에 대한 데이터를
-- with문을 사용하여 임시테이블로 설정, 임시테이블을 사용하여 각 학년별 최고시험성적 출력
with std_exam(studno, name, grade, total)
as
(select s.studno, s.name, s.grade, e.total
from student s, exam_01 E
where s.studno=e.studno)
select grade, max(total)
from std_exam
group by grade;— [DDL]
— 1. CREATE
— 2. DROP
— 3. ALTER
— 1) 컬럼추가
— – 맨 마지막에 추가
— – 기존에 데이터가 있을 경우 새로 추가된 컬럼의 데이터는 null로 입력됨
— – 기존에 데이터의 새로 추가된 컬럼의 데이터를 null이 아닌 값으로 설정 시 default값 선언 필요
— – 동시에 여러 컬럼 추가 가능(반드시 괄호 사용)
alter table 테이블명 add (컬럼1 데이터타입 [default] [제약조건],
컬럼2 데이터타입 [default] [제약조건]);
— 2) 컬럼 삭제
— 동시에 여러 컬럼 삭제 불가
— 데이터 유무 상관없이 항상 삭제 가능
— recyclebin에 저장되지 않음 + AUTOCOMMIT => 복원불가 조심하자
alter table 테이블명 drop column 컬럼명;
-- 예제)
select * from test1;
alter table test1 drop column 상호명;
alter table test1 drop column 매출;
select * from user_recyclebin;— 3) 컬럼 변경
— 사이즈, 데이터타입, default값, 컬럼명 변경 시
— 동시에 여러 컬럼 수정 가능(괄호 필수)
alter table 테이블명 modify (컬럼명1 데이터타입 [default값]
컬럼명2 데이터타입 [default값]);
— 3-1) 사이즈 변경
— 늘리는 건 언제나 가능
— 줄이는 건 데이터가 있으면 그 데이터의 max size 까지만 가능
— number의 사이즈를 지정하지 않은 상태에서 사이즈를 지정하려고 하면 비어있어야 함
desc test1;
alter table test1 modify 지역2 varchar2(5);
— 예제) test1테이블의 지역1, 지역2 컬럼 사이즈를 동시에 10으로 변경
alter table test1 modify (지역1 char(10), 지역2 varchar(10));
— 예제) test1테이블의 지역1 컬럼 사이즈를 5로 변경
alter table test1 modify 지역1 char(5); — error : 일부 값이 너무 커서 열 길이를 줄일 수 없음
— 3-2) 데이터타입 변경
— null인 컬럼은 데이터타입 변경 가능
— null이 아니더라도 char<->varchar2 는 가능
desc test1;
select * from test1;
alter table test1 add code number default 100;
alter table test1 add code2 number(5);
alter table test1 modify code number(5); — error : 정도 또는 자리수를 축소할 열은 비어있어야 합니다
— 예제) test1 테이블의 code, code2 컬럼의 데이터타입을 varchar2로 변경
alter table test1 modify code varchar2(10); — error : 데이터유형을 변경할 열은 비어있어야 합니다
alter table test1 modify code2 varchar2(10);
— 예제) test1 테이블의 지역1 컬럼은 varchar2로, 지역2 컬럼의 데이터타입을 char로 동시변경
alter table test1 modify (지역1 varchar2(10), 지역2 char(10));
— 3-3) default값 변경
— 기존의 값들은 default값으로 변경되지 않음. 변경 이후 입력되는 데이터에 대해서만 반영.
— add column에서 default값 설정하면 기존의 값들 변경
— null을 지정하면 default값 무시하고 null로 들어감
— 즉, 언급이 안됐을 때만 default값으로 들어감.
desc test1;
select * from test1;
— 예제) test1의 code2 컬럼의 default값을 200으로 변경
alter table test1 modify code2 default 200;
— 예제) test1 테이블에 한 행의 데이터 삽입(삽입 시 code2 입력 x)
insert into test1 values(‘강원’, ‘강원’, 10, 100); — error : 값의 수가 충분하지 않음
insert into test1(지역1, 지역2, 매출, code) values(‘강원’, ‘강원’, 10, 100); — default값 입력
— 예제) test1 테이블에 한 행의 데이터 삽입(code2 값은 null로 입력)
insert into test1 values(‘강원2′,’강원2’, 20, 100, null); — null 입력
— 예제) test1 테이블의 code, code2 컬럼의 default값 삭제
alter table test1 modify (code default null, code2 default null);
— 각 컬럼의 default 값 확인 방법
select TABLE_NAME, column_name, data_default
from user_tab_columns
where TABLE_NAME = ‘TEST1’;
— 3-4) 컬럼명 변경
— 항상 가능
— 한 컬럼만 가능 – 명령어에 column 들어가면 1개만 가능하다 생각
alter table 테이블명 rename column old_name to new_name;
— 예제) test1 테이블의 상호명 컬럼 이름을 상점명으로 변경
alter table test1 rename column 상호명 to 상점명;
select * from test1;
— 객체(테이블, 뷰, 인덱스)의 이름 변경하는 방법
— rename 은 AUTOCOMMIT 이므로 굳이 분류하면 DDL
rename old_name to new_name;
— test1 테이블 이름을 test10으로 변경
rename test1 to test10;
select * from test10;
— 4. truncate
— 테이블 구조는 남기고 데이터만 모두 삭제
— 일부 데이터 삭제 불가 – 일부만 삭제하려면 DELETE 사용
— delete는 rollback을 대비해서 어딘가에 저장되어야해서 느림
— 하지만 truncate는 그런거 없어서 속도가 빠름
-- [연습문제 - 10일차 폴더에 DDL_예제 문제]
-- 1.
create table member2 (
userid varchar2(10),
username varchar2(10),
passwd varchar2(10),
idnum varchar2(13),
phone number(13),
address varchar2(20),
regdate DATE,
interest varchar2(15));
-- 2.
alter table member2 modify phone varchar2(13);
insert into member2 values('sunshinLee','이순신','ssl000','8701011120200',
'02)333-2123', '서울', to_date('2015/07/05','YYYY/MM/DD'), '컴퓨터', null);
-- 3.
create table member_second
as
select * from member2;
-- 4.
create table member_third
as
select userid, username, passwd
from member2;
-- 5.**
create table member_forth
as
select * from member2;
truncate table member_forth;
-- 강사님 방법
create table member_forth
as
select * from member2 where 1=2;
-- 6.
alter table member2 add email varchar2(50);
desc member2;
-- 7.**
alter table member2 add country varchar2(20) default 'Korea';
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'MEMBER2';
-- 8.**
alter table member2 drop column email;
-- 9.
alter table member2 modify address varchar2(30);
-- 10.**
rename member_second to potential_member;
select * from member_second;
select * from potential_member;— [DML]
— 데이터 수정 언어(구조 변경 없이)
— TCL(COMMIT,ROLLBACK,SAVEPOINT)을 필요로 함.
— 만약 DDL을 써버리면 AUTOCOMMIT 돼서 다 적용되어버린다.
— 1. INSERT
— 행 단위 삽입
— insert into 테이블명 values(값1,값2,…) : 테이블의 모든 컬럼의 값을 입력 시
— insert into 테이블명(컬럼1,컬럼2,…) values(값1,값2,…) : 테이블의 일부 컬럼의 값을 입력 시, not null 선언된 컬럼은 반드시 입력
— not null이랑 default가 같이 있으면 생략해도 되나??
— ORACLE에서는 일반적으로 INSERT로 다중행 입력 불가(단, 서브쿼리 사용 시 가능)
-- 예제) student_test1 테이블을 student 구조는 동일, 데이터 없이 생성한 후
-- 두 건의 학생 데이터 입력(첫번째 입력은 모든 컬럼 값 정의, 두번째 입력은 필수 컬럼만 정의)
create table student_test1
as
select * from student where 1=2;
select * from student_test1;
desc student_test1;
insert into student_test1 values(1234, 'sunghyun', 'kim', 4, '0009073075124',
to_date('20000907','YYYYMMDD'),
'010-8982-6638', 166, 62, 101, null, 4006);
insert into student_test1(studno, name, id, jumin) values(5678, 'kim', 'sh',
'0101014023975');
delete student_test1;
commit;-- 예제) emp와 구조는 동일하지만 데이터가 없는 emp_insert1, emp_insert2 생성 후
-- 각각 10번, 20번 부서원 정보를 입력
create table emp_insert1
as
select * from emp where 1=2;
create table emp_insert2
as
select * from emp where 1=2;
-- 방법1) 각각 입력
Insert into emp_insert1
select * from emp where DEPTno=10;
insert into emp_insert2
select * from emp where deptno=20;
rollback;
select * from emp_insert1;
select * from emp_insert2;
-- 방법2) 하나의 insert문으로 동시에 (insert all)
insert all
into 테이블명1 values(…)
into 테이블명2 valuse(…)
select …;
-- 조건을 이용해서 구분하기
insert all
when 조건1 then into 테이블명1 values(…)
when 조건2 then into 테이블명2 valuse(…)
select …;
insert all
when deptno=10 then into emp_insert1
when deptno=20 then into emp_insert2
select * from emp;— 2. UPDATE
— 셀 단위 수정(여러 셀 수정 가능)
— 일부 컬럼의 일부 행을 수정
update 테이블명
set 컬럼=수정할 값 — 서브쿼리 가능
where 조건; — 서브쿼리 가능
-- 예제) emp_test1(emp에서 부서번호 30번 애들만 뺀 거)에서
-- king의 급여를 5500으로 수정
drop table emp_test1 purge;
create table emp_test1 as select * from emp where deptno!=30;
select * from emp_test1;
update emp_test1
set sal=5500
where ename='KING';
commit;-- 예제) emp_test1에서 comm이 없는 직원의 comm을 급여의 10%로 수정
update emp_test1
-- set comm = (select sal from emp_test1 e1 where e.empno = e1.empno)/10 -- 이럴 필요가 없다
set comm = sal/10
where comm is null;-- 예제) emp_test1에서 10번부서원들의 sal을 전체 직원의 평균급여로 수정
update emp_test1
set sal = (select avg(sal) from emp)
where deptno=10;
commit;-- 예제) emp_test2를 emp와 동일하게 생성 후 각 직원의 급여를 소속부서의 평균급여로 수정
create table emp_test2
as
select * from emp;
select * from emp_test2;
update emp_test2 e2
set sal = (select avg(sal) from emp e where e.deptno = e2.deptno);
commit;오늘 문제
--1. professor_1을 professor와 동일하게 만들고
--홈페이지가 없는 직원들은 email_id를 사용하여 다음과 같이 변경
--양선희의 경우 => http://www.itwill.com/lamb1
create table professor_1 as select * from professor;
select * from professor_1;
update professor_1
set hpage = 'http://www.itwill.com/'||substr(email, 1, instr(email,'@')-1)
where hpage is null;
rollback;
commit;--2. student2 테이블을 student과 동일하게 만들고 생성 후
--모든 학생의 주민번호를 아래와 같은 형태로 수정(그에 필요한 적절한 조치까지)
--7510231901813 => 751023-1901813
create table student2 as select * from student;
select * from student;
desc student2;
select * from student2;
alter table student2 modify jumin varchar2(14);
update student2
set jumin = substr(jumin,1,6)||'-'||substr(jumin,7);--3. movie 테이블을 사용하여
--요일별로 영화 이용비율이 가장 높은 성별을 확인, 요일은 월화수목금토일 순서대로 정렬
select * from movie;
-- 요일 확인
select to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day')
from movie;
select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day');
select 요일, max(성별이용비율)
from (select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day'))
group by 요일;
select i1.요일, i1.성별
from (select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day')) i1,
(select 요일, max(성별이용비율) as 최대비율
from (select 성별,to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day') as 요일, sum(이용비율) as 성별이용비율
from movie
group by 성별, to_char(to_date(년||to_char(월,'00')||to_char(일,'00'), 'YYYYMMDD'),'day'))
group by 요일) i2
where i1.성별이용비율 = i2.최대비율
and i1.요일=i2.요일
order by decode(i1.요일, '월요일',1, '화요일',2, '수요일',3, '목요일',4, '금요일',5, '토요일',6, '일요일',7);