[Query] Day 2 - 명령어 (그룹함수/JOIN문)

ORACLE

: data -> row-> table -> tablespace-> database

data(가장 작은 단위) / row(행↓ ,record) / tablespace(저장공간- 데이터 구분 / MS-SQL에는 없음) / database (무조건 DB 1개)

MS-SQL

: data -> row -> table -> database

: [query] - [옵션] - [쿼리편집기] - [Fetch - 모든으로 변경]

💻 작업

• 그룹함수 (결과 1개 출력 -> 몇명? 개수?)

1. count

• (count : 전체 데이터 세어줌) (자주사용)

select count(*) from personnel;
select count(manager) from PERSONNEl;
select count(bonus) from personnel;

--급여가 3000이상인 사원의 수를 구하여라
select count(*) from personnel where pay>=3000;

2. avg

• (AVG : 평균)

select avg(pay) from personnel;

select avg(bonus) from personnel;
select avg(nvl(bonus,0)) from personnel;

3. sum

• (sum : 합계)

select sum(pay) from personnel;

• (분석함수)

4. Distinct avg( ) over (partition by )

• (부서별 평균 구하기) : distinct 컬럼, avg() over(partition by 컬럼) from ~

SELECT DNO, AVG(PAY) OVER(PARTITION BY DNO) FROM PERSONNEL; --dno로 파티션으로 구분
select distinct dno, avg(pay) over(partition by dno) B_AVG from PERSONNEL; --중복된거 제외= DISTINCT

5. rank( )

• (월급순위) : rank( )

select pname,pay,rank() over(order by pay desc) as rank from personnel; -- pay를 큰순서대로 정렬 + rank() 랭킹을 매겨라

• (특정 컬럼 받는 - 개수? 몇명? 점수? ) : rank(xxx) within group(order by

--월급 1200받는 직원
select rank(1200) within group(order by pay desc) from personnel; 

6. group by( )

• group by +컬럼명

select pname from PERSONNEL; --다중값 반환
select pname from PERSONNEL where pay = 1600; --다중값 반환

select count(*) from personnel;--단일값 반환 (개수 10개)

• ~~~별 평균 급여 구하기

--부서별 평균 급여 구하기
-- SELECT DNO, AVG(PAY) OVER(PARTITION BY DNO) FROM PERSONNEL; 와도 같음
select dno,avg(pay) from personnel group by dno; -- ~별 : group by


--직업별 평균 급여 구하기
--다중,단일값 동시 사용 불가 => group by + select옆에 있는 가장 처음꺼
select job,round(avg(pay)) j_avg, count(*) j_cnt from personnel 
group by job; 


--지역별 인원수, point 합계
select addr1, count(*),sum(point) from custom group by addr1;

--나이별대 인원수
select age,count(*) from custom group by age;

--성별로 인원수
select sex,count(*) from custom group by sex;

• 평균급여 decode / sign

--각 부서의 평균 급여가 전체 평균 급여보다 크면 GOOD, 작으면 POOR라고 표현
select avg(pay) from personnel; --전체 평균
select dno,avg(pay) from personnel group by dno; --부서별 평균

select dno,avg(pay),decode(sign(avg(pay) - (select avg(pay) from personnel)),1,'GOOD','POOR')grade from personnel group by dno;

6. 조건문 - having

• 조건문 where / having

--select의 조건문 : where
--group by의 조건문 : having

--부서별 평균급여 + 3000이상
select dno, avg(pay) from personnel group by dno
having avg(pay)>=3000; -- 별칭가지고 와서 쓰는건 order by는 가능하지만, having은 해당 쿼리를 가지고 와야함


select pno no, pname name, startdate s_date from personnel order by s_date;-- 별칭가능 - order by

7. join

• join

• join : 여러개의 table에서 정보를 가지고올때 사용 / 공통으로 존재하는 것을 가지고 있어야함
• equi-join (inner join) - join문에서 가장 많이 사용함 : 같은것끼리 조인함
• non-equi join : 다른것들끼리 조인함
• outer join
• cross join
• self join

-- transaction SQL
-- ansi sql : 표준 sql

select * from custom where userid = '978072';
select * from company where userid = '978072';
select * from sales where userid = '978072';

--userid는 위의 세군데에 다 있고, username은 custom에만 있기때문에 table.컬럼으로 씀
--table과 컬럼의 이름이 같으며 안됨

/*
select custom.userid, username,age,addr1,company,posit,pay
from custom A,company B
where custom.userid = company.userid;

↓ 별칭을 사용해서 바꿔보기
*/

inner join (교집합)

• custom 테이블과 point 테이블을 조인
• 두개이상의 테이블에서 원하는 컬럼만 가져와서 table 출력
  -> (반드시 공통분모를 가져야함)

select * from point;
select * from custom;

select a.userid,age,addr1,point,member,product,login,poll
from custom a, point b
where a.userid = b.userid;


--각각의 회사에서 만드는 join문) a=b userid가 같으면서 로그인이 10회이상인 사람
select a.userid,age,addr1,point,member,product,login,poll
from custom a, point b
where a.userid = b.userid and login>=10;

--표준 join문 ) inner join의 조건문 ON
--inner join의 조건은 on은 기본이고 + where절을 써도되고, and로 해도 됨
select a.userid,age,addr1,point,member,product,login,poll
from custom a inner join point b
on a.userid = b.userid --and login>=10;
where login>=10;

• cross join (곱하기)

select count(*) from custom; --459
select count(*) from company; --464

select a.userid, username, addr1, company, dept from custom a cross join company b;

outer join

-- : transaction SQL
select a.userid,username,addr1,company,dept from custom a, company b where a.userid = b .userid; --기본 inner join
select a.userid,username,addr1,company,dept from custom a, company b where a.userid = b .userid(+); --적은걸 a 기준으로 함 => 459rows
select a.userid,username,addr1,company,dept from custom a, company b where a.userid(+) = b .userid
order by addr1; --(오름차순) 오른쪽 b company가 기준 => 464rows - 비어있는 gap차이는 왼쪽 a에  null로 표시됨


-- : ansi sql : 표준 sql
select a.userid,username,addr1,company,dept from custom a left outer JOIN company b 
on a.userid = b .userid;-- => 459

select a.userid,username,addr1,company,dept from custom a right outer JOIN company b 
on a.userid = b .userid; -- => 464

• self join

• 특정컬럼에 동명이인, distinct 중복값이 있는지 찾을 때?
  :중복값을 제거함 -> 데이터 관리 용이 / 하나의 테이블에 넣어둠으로써 가독성 좋게하기 위함

--a의 모든걸 꺼내라
select distinct a.* from custom a, custom b 
where a.username = b.username  --table이름 바꾸고
and a.userid<>b.userid  -- 해당 부분은 고정
order by a.username;


--주소가 제주도인 사람 중에 동명이인을 검사
select distinct a.* from custom a, custom b 
where a.username = b.username
and a.userid<>b.userid and a.addr1 = '제주도' and b.addr1 = '제주도'
order by a.username;

• non equi join
  :중복값을 제거함으 -> 데이터 관리 용이 / 하나의 테이블에 넣어둠으로써 가독성 좋게하기 위함

select * from personnel;
select * from paygrade;

select pname,pay, grade
from personnel a,paygrade b
where pay between lowpay and highpay;

• operator
  :중복값을 제거함으 -> 데이터 관리 용이 / 하나의 테이블에 넣어둠으로써 가독성 좋게하기 위함

a = [1,2,5,6], b = [1,2,4];

a union all b = [1,2,5,6,1,2,4]; -- all 나옴
a union b =  [1,2,5,6,4] -- 중복값 제외
a minus b = [5,6]
a intersect b = [1,2] --교집합

----------------------------------------------------

--a union all b : 전부
select * from personnel union ALL select * from personnel;

--a union b : 중복제외
select * from personnel union select * from personnel;

--a minus b : a - b
select dno from division --10,20,30,40
minus select dno from personnel; --10,20,30

--a intersect b : 교집합
select dno from division 
intersect select dno from personnel; 

• table 생성

create table jeju
AS
select * from custom where addr1 = '제주도';

select * from jeju;
------------------------------------------------
create table seoul
AS
select * from custom where addr1 like '서울%';

select * from seoul;
------------------------------------------------
create table GG
AS
select * from custom where addr1 like '경기%';

select * from GG;
------------------------------------------------
데이터 하나로 불러올 때 사용 [ union ]

select * from jeju
union
select * from seoul
union
select * from GG;

---

8. system 관리

system 관리

select * from dba_users; --dba_users; : dictionary - 무조건 system에서만 사용가능


select * from dba_tablespaces;

select * from dba_data_files;

ALTER TABLESPACE USERS -- alter 변경 / add 추가
add datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS1.DBF' size 100M;

ALTER TABLESPACE USERS -- drop 지워짐
drop datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS1.DBF'; -- 기본 100M


create tablespace insa
datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\INSA.DBF' SIZE 10M
segment space management auto; -- 자동으로 저장소 관리해

/*
create user kim
identitied by bob
default tablespace insa
temporary tablespace temp;
*/

drop tablespace including contents and datafiles cascade CONSTRAINTS;