[ORACLE] ORACLE SQL문 QUERY 1/2 (@ORACLE SQL DEVELOPER)

SELECT * FROM STUDENT;

// 중복된 나이 제거하고 출력하시오.
SELECT distinct AGE FROM STUDENT;

-- 전화번호가 012로 시작하는 학생의 정보를 모두 조회하시오.
select * from STUDENT where SUBSTR("PHONE",1,3) = '012'; 

-- 전화번호를 등록하지 않은 회원 중 생년 월이 7,8,9월인 회원의 모든 정보를 출력하시오.
Select * from STUDENT where "PHONE" is null and SUBSTR("BIRTHDAY",7,1) in (7,8,9);

-- sysdate는 함수. ()없이 사용 가능. 날짜만 추출
select sysdate from dual; 

-- 날짜와 시간까지 추출.
select systimestamp from dual; 

-- 현재 월 추출 as month는 열의 이름 지정해 준 것.
select extract (month from sysdate) as month from dual; 

-- 현재 연도 추출

select extract (year from sysdate) as year from dual; 

-- 날짜 비교, 묵시적으로 date형과 varchar형이 변환되어 문제가 없어보임.
-- 문자 '25', '3'비교시 '3'이 큼.

select * from student where "REGDATE" < '2012-12-25';

-- 현재날짜(sysdate)에서 6개월 전을 추출할 때. add_months함수를 사용해 -6을 한다.
select add_months(sysdate, -6) from dual; 

-- 가입한지 6개월이 안된 회원의 모든 필드를 출력하시오.
select * from student where "REGDATE" > add_months(sysdate,-6) and "REGDATE" < sysdate;

-- 이름 기준으로 내림차순(역순) 정렬하여 조회하시오.
select * from student order by "NAME" DESC;

-- '박'씨 성을 가진 회원 조회. but, 나이 오름차순 정렬.
select * from student where substr("NAME",1,1)='박' order by "AGE" ASC;
select * from student where "NAME" like '박%' order by "AGE" ASC;

-- 1. 나이 기준으로 내림차순 정렬. but, 나이가 같을 경우 2. 아이디 기준으로 오름차순 정렬.
select * from student order by "AGE" DESC, "SID" ASC;

-- 가입한 회원 수
select count("SID") from student;

-- 가입한 회원 수(중복제거)
select count(distinct "NAME") from student;

-- 가입 회원의 성별 회원 수를 조회하시오.
select "GENDER", count("SID") from student group by "GENDER";

-- 나이대별 회원 수 조회.
select AGE, count(SID) from student group by AGE;

-- 성별 평균 나이 조회.
select "GENDER", avg("AGE") from student group by "GENDER";

-- 성씨 별 회원의 수 조회하시오.
-- (from > where > group by(having) > order by 순서는 정해져 있음!)
select substr("NAME",1,1), count("SID") from student 
group by substr("NAME",1,1) order by substr("NAME",1,1) ASC;

-- '박'씨 성의 회원수를 조회하시오.
1. select count("SID") as "박씨 성" from student where "NAME" like '박%';
2. select count("SID") as "박씨 성" from student where substr("NAME",1,1)='박';
3. select count("SID") as "박씨 성" from student
group by substr("NAME",1,1) having substr("NAME",1,1)='박';

// NOTICE테이블 조회하기
select * from NOTICE;

// NOTICE테이블을 작성일 역순으로 조회하기
select * from NOTICE order by "REGDATE" DESC;

// NOTICE테이블을 작성일 역순으로 1-10개 게시물만 조회하기 (서브쿼리 1개)
SELECT * FROM 
(
SELECT ROWNUM NUM, NOTICE.* FROM NOTICE
order by REGDATE DESC
) T
WHERE ROWNUM BETWEEN 1 and 10;

// NOTICE테이블을 작성일 역순으로 11-20개 게시물만 조회하기 (서브쿼리 2개)
SELECT * FROM 
(
select ROWNUM NUM, T.* from
(
SELECT * FROM NOTICE order by REGDATE DESC) T
) N 
WHERE NUM BETWEEN 11 and 20;

// NOTICE테이블에서 제목을 기준으로 검색하기 (필터링은 안쪽에서 하는것이 낫다)
SELECT * FROM 
(
select ROWNUM NUM, T.* from
(
SELECT * FROM NOTICE where TITLE like '%하%' order by REGDATE DESC) T
) N 
WHERE NUM BETWEEN 1 and 10;

// MEMBER의 아이디(UID)와 NOTICE의 작성자(WRITER)가 존재하는지 확인하고 MEMBER 기준의 테이블로 출력
select * from MEMBER
inner join NOTICE on MEMBER."UID" = NOTICE.WRITER;

// 게시글 번호, 제목, 게시자 이름
select N.SEQ, N.TITLE, M.NAME FROM MEMBER M
inner join NOTICE N on M."UID" = N.WRITER;

// 회원별 작성한 게시글 수를 조회하시오. (게시글 없으면 출력 안함, inner join)
select M."UID", M.NAME, count(N.SEQ) as "COUNT" from MEMBER M
inner join NOTICE N on M."UID" = N.WRITER 
group by M."UID", M.NAME
order by "COUNT" DESC;

// 특정 회원이 작성한 게시글 수를 조회하시오. (having)
select M."UID", M.NAME, count(N.SEQ) as "COUNT" from MEMBER M
inner join NOTICE N on M."UID" = N.WRITER 
group by M."UID", M.NAME
having M."UID"='HelloToT';

// 특정 회원이 작성한 게시글 수를 조회하시오. (where)
select M."UID", M.NAME, count(N.SEQ) as "COUNT" from MEMBER M
inner join NOTICE N on M."UID" = N.WRITER 
where M."UID"='HelloToT'
group by M."UID", M.NAME;

// 모든 회원이 작성한 게시글 수를 조회하시오. (게시글이 없어도 출력, outer join)
select M."UID", M.NAME, count(N.SEQ) as "COUNT" from MEMBER M
left outer join NOTICE N on M."UID" = N.WRITER 
group by M."UID", M.NAME
order by "COUNT" DESC;

// 회원의 담당자를 조회하시오. (inner join으로 같은 테이블을 참조함-self join 역할)
select M."UID" 회원아이디, M.NAME 회원명, B."UID" 담당자아이디, B.NAME 담당자명 
from MEMBER M , MEMBER B
where M.BOSS = B."UID";

// 덧글이 있는 게시물을 출력하시오. (inner join)
select N.SEQ, N.TITLE, N.WRITER, M.NAME, COUNT(C.SEQ) from MEMBER M
inner join NOTICE N on M."UID" = N.WRITER 
inner join "COMMENT" C on N.SEQ = C.NOTICESEQ
group by N.SEQ, N.TITLE, N.WRITER, M.NAME;

// 덧글이 없는 게시물도 모두 출력하시오. (outer join)
select N.SEQ, N.TITLE, N.WRITER, M.NAME, COUNT(C.SEQ) from MEMBER M
right outer join NOTICE N on M."UID" = N.WRITER 
left outer join "COMMENT" C on N.SEQ = C.NOTICESEQ
group by N.SEQ, N.TITLE, N.WRITER, M.NAME;

// 연산을 하기 위해 임시적으로 만든 것이 view
// 덧글이 없는 게시물도 모두 출력할 view 생성.

create view NOTICEVIEW
as 
select N.SEQ, N.TITLE, N.WRITER, M.NAME, COUNT(C.SEQ) from MEMBER M
right outer join NOTICE N on M."UID" = N.WRITER 
left outer join "COMMENT" C on N.SEQ = C.NOTICESEQ
group by N.SEQ, N.TITLE, N.WRITER, M.NAME;

select * from NOTICEVIEW;

// 13.5.8 제약조건 수업(1)

create table TEST690
(
seq number not null,
title varchar2(200) not null,
writer varchar2(50) not null,
content varchar2(4000) null,
regDate date default sysdate not null,
hit number default 0 not null
)

select * from test690;

// content컬럼의 자료형 길이를 4000으로 테이블을 수정하시오.
alter table test690 modify(content varchar(4000) not null);

// seq컬럼에 null값이 가능하도록 테이블을 수정하시오.
alter table test690 modify(seq number null);

// phone컬럼에 체크 제약조건을 넣어 TEST690테이블을 생성하시오.
1. create table TEST690
(
"uid" varchar2(50) not null,
phone varchar2(200) check(phone like '010-%-____') not null,
email varchar2(200) not null
)

2. create table TEST690
(
"uid" varchar2(50) not null,
phone varchar2(200) check(regexp_like(phone, '^01[016789]-\d{3,4}-\d{4}')) not null,
email varchar2(200) not null
)

// 2013. 5. 9 제약조건 수업(2)

drop table test690;

create table TEST690
(
seq varchar2(50),
title varchar2(200),
writer varchar2(200),

constraint pk_test690_writer primary key(writer) // writer를 기본키로 제약조건을 준다.
)

// 5/21수업

//notice에서 가장 큰 seq만 출력, seq는 string형, 그래서 앞자리가 가장큰것부터 나옴.
select max(seq) from NOTICE;

// seq를 숫자로 바꾸면서 가장 큰 seq출력하기.
select max(to_number(seq)) from notice;

// 새로 글 등록할 때 마다 seq가 하나씩 증가하게 하기.
select max(to_number(seq))+1 from notice;

// 6/5 공지사항 쿼리 공부.
select * from NOTICE;

// 공지사항 첫 목록페이지(1번부터 15번까지 15개 출력)
select ROWNUM num, N.* from 
  (select * from NOTICE order by regdate desc) N 
    where ROWNUM between 1 and 15;

// 공지사항 두번째 목록페이지(16번부터 30번까지 15개 출력)
select * from 
  (select ROWNUM num, N.* from 
    (select * from NOTICE order by regdate desc) N) 
where num between 16 and 30;