[오답노트] 2-1. SQL 기본

🎈SQL 문장들의 종류
1. DML: data manipulation language
- select = 데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어, retrieve라고도 함
- insert, update, delete = 데이터베이스의 테이블에 들어있는 데이터에 변형을 가하는 종류의 명령어들(삽입, 수정, 삭제)
2. DDL : data definition language
- create, alter, drop, rename : 테이블과 같은 데이터 구조를 정의하는 데 사용하는 명령어들로, 그러한 구조를 생성/변경/삭제/이름변경 하는데 데이터 구조와 관련된 명령어
-> Alter table 테이블 modify(컬럼, 타입)
3. DCL : data control language
- grant, revoke : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
4. TCL : transaction control language 
- commit, rollback : 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어

🎈SELECT 문
SELECT (all/distinct) 보고싶은 칼럼명...
FROM 테이블
- all : 기본옵션, 모든칼럼들
- distinct : 중복된 데이터가 있는 경우 1건으로 처리

🎈 NULL과 연산
널값과의 사칙연산을 널값리턴(집계함수는 널을 제외하기 때문에 정상작동)
널값과의 비교연산은 false 리턴
특정값과의 비교 불가
1. 표현식1이 널값이면 표현식 2로 대체
[오] NVL(표현식1, 표현식2)
[서] ISNULL(표현식1, 표현식2)
2. 표현식1이 표현식2와 같으면 널값, 아니먄 표현식1 유지
NULLIF(표현식1, 표현식2)
3. 표현식1이 널값이면 표현식2, 표현식2가 널값이면 표현식...
COALESCE(표현식1, 표현식2..)

🎈 EQUI JOIN 문장
조인에 관여하는 테이블간의 칼럼값들이 정확하게 일치하는 경우

1. WHERE 절에 JOIN조건을 넣는다
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼1 = 테이블2.컬럼2;

2. ON 절에 JOIN조건을 넣는다
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 JOIN 테이블2 
ON 테이블1.컬럼1 = 테이블2.컬럼2;

🎈집합연산자
조건: 집합연산이 되는 두 질의는 컬럼수 동일, 상호호환 가능한 타입, 별칭을 사용할 경우 꼭 별칭을 사용
1. UNION : 중복제거가 된 합집합
2. UNION ALL : 중복이 허용된 합집합(속도빠름) 
3. INTERSECT : 중복제거된 교집합
4. MINUS(ORACLE)/ EXCEPT(MS_SQL): 앞의 SQL문의 결과에서 뒤의 SQL결과를 뺀 차집합


🎈정렬
기본적인 정렬은 오름차순
오라클에서 널값을 가장 큰 순으로 간주, SQL server은 가장 작은순으로 간주
일반적으로 select절에 없는 컬럼 및 계산으로 정렬이 가능하지만 집계함수시에는 꼭 명시된 컬럼만 가능

🎈순수관계연산자
select연산은 where절로 구현
project 연산은 select절로 구현
join 연산은 다양한 join기능을 구현
divide 현재 사용하지 않음

 

1.

CREATE TABLE 서비스
(
서비스번호 varchar2(10) primary key,
서비스명 varchar2(100) null,
개시일자 date not null );

#ㄱ
select * from 서비스 where 서비스번호 = 1; 
#ㄴ
insert into 서비스 value ('999', '' , '2015-11-11');
#ㄷ
select * from 서비스 where 서비스명 = '';
#ㄹ
select * from 서비스 where 서비스명 is null

 

• 서비스번호 칼럼의 모든 레코드가 '001'과 같은 숫자형식으로 입력되어 있으면 오류가 발생하지 않는다
• ㄴ과 같이 데이터( ' ' ) 를 입력하면 서비스명 칼럼의 데이터에 대해서 오라클에서는 NULL 값으로 입력된다
• ㄴ과 같이 데이터가 입력되어있을 때 오라클에서 데이터를 조회하려면 서비스명 is null 조건으로 입력한다 
• ㄴ과 같이 데이터가 입력되어있을 때 SQL server 에서 데이터를 조회하려면 서비스명 = ' ' 조건으로 입력한다

2. to chart 와 to date

• where to_chart(end_date, 'YYYYMM')=  '201501' -> 2015년 1월 31일 23시 59분
• -> chart일 경우 'YYYYMM'의 'day는 월말
• where to_date('201501', 'YYYYMM') ='end_date -> 2015년 1월 1일 00시 00분
• -> date일 경우 'YYYYMM'의 'day는 월초

 

3.  2023년 1월 10시 10분

select to_chart(to_date('2023.01.10.10', 'YYYY.MM.DD HH24')
# 2023년 01월10일 10시에서
+ 1/24/(60/10),'YYYY.MM.DD HH24:Mi:SS') from dual
# 10분을 더함

1/24/60 = 1분

1/24/(60/10) = 10분

 

4. case 문

case when loc = 'new york' then 'east' else 'etc' end as area
case loc when 'new york' then 'east' else 'etc' end as area
decode(loc, 'new york', 'east', 'ect' )

 

5. 광고매채 ID별 최초로 게시한 광고명과 광고시작일 출력

[광고]광고ID	[광고게시] 광고게시번호	[광고매체] 광고매체ID
광고명	광고ID(FK) 광고매체ID(FK) 광고시작일자 광고종료일자	광고매체명

 

from 광고게시 a, 광고 b, 광고매체 c,
	(select 광고매체ID, MIN(광고시작일)as 광고시작일자
    from 광고게시
    group by 광고매체id) d
where a.광고시작일자 = d.광고시작일자 
and a.광고매체id = d.광고매체id
and a.광고id = b.광고id
and a.광고매체id = c.광고매체id
order by c.광고매체명

 

6. [ORACLE] order by + case when

ID
100
100
200
200
200
999
999

 

select ID from TBL
group by ID
having count(*) = 2
order by (case when ID = 999 then 0 else ID end)
# ID별로 그룹화를 해서 갯수가 2인 것만 출력을 하는데, 정렬을 ID가 999인 것을 0번째로 정렬한다

ID

100

999

 

7. to_date 와 to_chart

select to_chart(to_date('2019.02.05','YYYY.MM.DD') + 1/12/(60/30), 'YYYY.MM.DD HH24:MI:SS')
from dual;

1) to_date('2019.02.05','YYYY.MM.DD') = 2019.02.05 -> 날짜로 바꾸기 

2) 2019.02.05 + 1/12/(60/30) = 2019년 2월 5일 1시

• 1/24 = 1시간 = 1일(24시간)을 24로 나누기(60분)
• 1/24/60 = 1분 = 1일을 24시간으로 나누고 60분으로 나누기
• 1/24/(60/10) = 10분 = 1일을 24시간으로 나누고 6분으로 나누기
• 1/12/(60/30) = 1시간 = 1일을 12시간으로 나누고(2시간) 2분으로 나누기
• 1.5/24 = 1시간 20분
• 1.5/24/6 = 15분

8. JOIN에 대한 설명

• 일반적으로 조인은 PK와 FK의 연관성에 의해 성립된다
• DBMS옵티마이저는 FROM절에 나열된 테이블을 임의로 2개정도씩 묶어서 조인을 처리한다
• EQUI JOIN은 조인에 관여하는 테이블 간의 칼럼들이 정확하게 일치하는 경우에 사용되는 방법이다
• EQUI JOIN은 '=' 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하는 경우는 모두 NON EQUI JOIN이다
• 대부분 NON EQUI 조인을 수행할 수 있지만, 때로는 설계상의 이유로 불가능한 경우도 있다

 

9. 

[제품] 제품코드	[생산제품] 라인번호(FK) 제품코드(FK)	[생산라인] 라인번호
제품명 제품유형코드 단위		최종가동일지

 

• 제품, 생산라인, 생산제품 엔터티를 inner join하기 위해서는 생산제품 엔터티는 where절에 최소 2번 나타나야 한다
• 제품과 생산라인 엔터티를 join시 적절한 join조건이 없으므로 카티시안 곱이 발생한다
• * 카티시안곱 = FROM절에 2개 이상의 테이블이 있을 때 두 테이블 사이에 유효한 join조건을 적지 않았을때 해당테이블에 대한 모든 데이터를 전부 결합하여 테이블의 행 갯수를 곱한 만큼의 결과가 출력된다 -> 조인조건을 생략/조인조건이 잘못됨 등
• * cross 조인 = 카티시안곱을 표준화하여 만든 조인
• 특정 생산라인번호에서 생산되는 제품의 제품명을 알기 위해서는 제품, 생산라인까지 2개의 엔터티만 조인하면 된다

 

10.cross 조인

테이블간 조인 조건이 없는 경우 생길 수 있는 모든 데이터의 조합(M*N)

#[1]
select ename, dname
from emp, dept

#[2]
select ename, dname
from emp cross join dept

 

11. 동일한 join 결과

# using 절 -> using절에는 별칭 X
select t.region-name, t.team_name, t.stadium_id, s.stadium_name
from team t inner join stadium s
using(stadium_id)

# on절 -> 별칭사용가능
select team.region-name, team.team_name, team.stadium_id, stadium.stadium_name
from team inner join stadium
on team.stadium_id = stadium.stadium_id

# where 절 -> 별칭
select t.region-name, t.team_name, t.stadium_id, s.stadium_name
from team t , stadium s
where t.stadium_id = s.stadium_id

# where 절
select team.region-name, team.team_name, team.stadium_id, stadium.stadium_name
from team , stadium
where team.stadium_id = stadium.stadium_id

#natural join -> 두테이블간 동일한 칼럼이 stadium_id밖에 없는 경우
select region-name, team_name, stadium_id, stadium_name
from team natural join stadium

*USING = on절과 마찬가지로 where절 대신 join에 대한 조건을 쓸때 사용, join을 만족하는 컬럼만 괄호에 입력

-- where 절
select a.name, b,age
from student a, data b
where a.id = b.id;

-- on 절
select a.name, b,age
from student a join data b
on a.id = b.id;

-- using 절
select a.name, b,age
from student a join data b
using (id);

 

11. outer join과 union

#[1]
select a.id, b.id
from tbl a full outer join tbl2 b
on a.id = b.id

#[2]
select a.id, b.id
from tbl a left outer join tbl2 b
on a.id = b.id
union
select a.id, b.id
from tbl a right outer join tbl2 b
on a.id = b.id

#[3]
select a.id, b.id
from tbl a , tbl2 b
where a.id = b.id # inner
union all
select a.id, null
from tbl a 
where not exists(select 1 from tbl2 b where a.id = b.id) #a-b차집합
union all
select b.id, null
from tbl b 
where not exists(select 1 from tbl2 a where a.id = b.id) #b-a차집합

 

0. SQL 오류

select 지역, sum(매출금액)
from 지역별매출
group by 지역
order by 년 desc #지역별로 그룹화를 했기 때문에 select 절에 기술되지 않는 '년'으로 정렬될 수 없다