231228목_TIL

sql 코드카타

문제 31.

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

 

SELECT name, datetime
from animal_ins left join animal_outs on animal_id
order by datetime
limit 3

 

Column 'name' in field list is ambiguous

SELECT a.name, a.datetime
from animal_ins a left join animal_outs b on a.animal_id = b.animal_id
order by a.datetime
limit 3

join 작성법

SELECT a.name, a.datetime
from animal_ins a left join animal_outs b on a.animal_id = b.animal_id
where b.datetime is null
order by a.datetime
limit 3

left join을 하면 a-b 차집합 같이 된다고 착각을. join 무쟈게 헷갈린다 조인 공부 공부

(+ 오후에 join 공부하니. left join은 left outer join의 준말이고 a와 b를 left outer join 하게 되면 a에 부재하는 b테이블의 값은 삭제 되는 것이 아니라 null 값으로 합쳐진다. 아마도..) 

 

문제 32.

문제

2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

 

 

SELECT a.category, count(b.sales) total_sales
from book a full outer join book_sales b on a.book_id=b.book_id
group by 1
order by 1

총합 & 날짜

 

 

SELECT a.category, SUM(b.sales) AS total_sales
	FROM book a JOIN book_sales b
	ON a.book_id = b.book_id
WHERE b.sales_date LIKE '2022-01%'
GROUP BY a.category
ORDER BY a.category

---

sqld 강의 

9주차

 

*where절 데이터 필터링(조회,수정,삭제 시)

SELECT [ALL|DISTINCT] 칼럼명 [AS 별명] FROM 테이블명 WHERE 조건식;
UPDATE 테이블명 SET 칼럼명=값 WHERE 조건식;
DELETE FROM 테이블명 WHERE 조건식;
SELECT * FROM PLAYER WHERE BACK_NO = 10;
UPDATE PLAYER SET TEAM = '서울FC' WHERE NAME = '메시';
DELETE FROM PLAYER WHERE NAME = '발로차';

 

*비교연산자 ( = , >= 등)

*sql 연산자. 내장 문법.(between, in, like)

'나%' 패턴은 '나'로 시작하는 모든 문자열과 일치함 (나, 나비, 나바아앙) '%도' 배턴은 '도'로 끝나는 모든 문자열과 일치함 (도, 검도, 토도도도) '%감자%' 패턴은 '감자'를 포함하는 모든 문자열과 일치 (감자, 감자튀김, 군감자) '커_' 패턴은 '커'로 시작하고 총 2자리 문자열과 일치 (커피, 커서) '_도' 패턴은 '도'로 끝나는 2자리 문자열과 일치 (수도, 득도)

*Is null / Is not null

NULL은 모르는 값을 의미하며 값의 부재를 뜻함

NULL과 숫자 혹은 날짜를 연산하면 NULL이 됨

논리연산자

and/ or/ not 여러 조건 연결

부정 논리 연산자

같지 않다. ( != , ^=, <>)

~와 같지 않다. (NOT 칼럼명 = )

~보다 크지 않다. (NOT 칼럼명 > )

부정 SQL 연산자

a와 b의 값 사이에 있지 않다. (NOT BETWEEN a AND b)

list 값과 일치하지 않는다. (NOT IN (list))

NULL 값을 갖지 않는다. (IS NOT NULL)

 

연산자 우선순위

1 순위 : 괄호() 2 순위 : NOT 연산자 3 순위 : 비교 연산자, SQL 비교 연산자 4 순위 : AND 5 순위 : OR

 

* pseudo column 의사/가상 칼럼 = 임시로 열 번호를 매김. select 조회만 가능.

* rownum = WHERE ROWNUM <= 3; = 테이블에서 3행까지만 데이터를 출력

* 반환할 행의 개수 = percent : 쿼리 결과 집합에서 몇 %를 반환할지/ with ties : 정해진 개수만큼 출력이 되었는데 마지막 행과 같은 값이 추가로 존재하는 경우 추가 행이 출력되도록 지정, ORDER BY 문이 지정된 경우에만 사용

SELECT TOP(5) WITH TIES host_city FROM olympic WHERE nation LIKE '%국' ORDER BY nation;

 

*rowid : 테이블에 명시하지 않아도 자동적으로 가지고 있는, 데이터를 구분할 수 있는, 유일한 값

 

* 단일행함수. 한 줄에 쓸 수 있는. 함수의 리턴 값은 항상 1개

종류	설명	함수
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환	LOWER, UPPER, ASCII, CHR, CONCAT, SUBSTR, LENGTH, LTRIM, RTRIM, TRIM, LPAD, RPAD
숫자형 함수	숫자를 입력하면 숫자 값을 반환	ABS, SIGN, MOD, CEIL, FLOOR, ROUND, TRUNC, POWER, SQRT, LOG, EXP, LN, SIN, COS, TAN
날짜형 함수	DATE 타입의 값을 연산	EXTRACT, SYSDATE
변환형 함수	문자, 숫자, 날짜형의 데이터형을 다른 데이터형으로 형변환	TO_CHAR, TO_NUMBER, TO_DATE
NULL 관련 함수	NULL 값을 처리	NVL, NULLIF, COALESCE
분기 처리 함수	분기 처리에 사용	DECODE

-- LTRIM(문자열[, 지정문자])
SELECT LTRIM('xxxYYZZxYZ', 'x') FROM DUAL;
--> 'YYZZxYZ'

-- RTRIM(문자열[, 지정문자])
SELECT RTRIM('XXYYzzXYzz', 'z') FROM DUAL; --> 'XXYYzzXY'

-- 양쪽 제거
SELECT TRIM('x' FROM 'xxYYZZxYZxx') FROM DUAL;
--> 'YYZZxYZ'
-- 왼쪽 제거 (LTRIM)
SELECT TRIM(leading'x' FROM 'xxYYZZxYZxx') FROM DUAL;
--> YYZZxYZxx

-- 오른쪽 제거 (RTRIM)
SELECT TRIM(trailing 'x' FROM 'xxYYZZxYZxx') FROM DUAL;
--> xxYYZZxYZ

LPAD : 문자열1을 n자리만큼 늘리고, 왼쪽 빈 공간을 문자열2로 채워서 리턴

(문자열2가 생략되면 기본값은 공백)

RPAD : 문자열1을 n자리만큼 늘리고, 오른쪽 빈 공간을 문자열2로 채워서 리턴

(문자열2가 생략되면 기본 값은 공백)

MOD(숫자1, 숫자2)

숫자1을 숫자2로 나누어 나머지 값을 계산

% 연산자로도 대체 가능

ROUND(숫자 [, m])

숫자를 소수점 m자리에서 반올림하여 리턴

m이 생략되면 default 값은

 TRUNC(숫자 [, m])

숫자를 소수 m자리에서 잘라서 버린 값을 리턴. m이 생략되면 default 값은 0

EXP(n) : e의 n제곱을 반환

POWER(m, n) : m의 n제곱을 반환

SQRT(n) : n의 제곱근을 반환합니다. n은 음수가 될 수 없음

LOG(m, n) : 밑을 m으로 한 n의 로그 값을 반환. m은 0 또는 1이외의 정수이며 n은 양수 값으로 설정

LN(n) : n의 자연 로그 값을 반환

 

Oracle : NVL / SQL Server : ISNULL 함수

결괏값을 NULL이 아닌 다른 값을 얻고자 할 때 Oracle은 NVL, SQL Server는 ISNULL 함수를 사용

 NULL 관련 함수 중 가장 많이 사용됨

NVL(NULL_판단_대상, NULL일_때_대체값)

ISNULL(NULL_판단_대상, NULL일_때_대체값)

 

NULLIF

NULLIF 함수는 특정 값을 NULL로 대체하는 경우 사용

NULLIF 함수는 두 조건 EXPR1과 EXPR2를 비교하고 같다면 NULL 을 리턴하고, 같지 않으면 첫 번째 EXPR1을 리턴

타 NULL 관련 함수 (COALESCE)

COALESCE 함수는 여러 값 중에서 NULL이 아닌 첫 번째 값을 찾을 때 사용

만약에 모든 값들이 NULL이라면 NULL을 리턴

DECODE 함수

조건을 평가하여 값을 반환하는 함수로, 주로 간단한 조건과 대응하는 결과 값을 처리할 때 사용

CASE 문과 유사한 역할을 수행하지만, 보다 간단한 형식으로 작성 가능

-- 기본 형태
SELECT DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ..., default])
FROM 테이블명;

-- player 테이블에서 선수 이름과
-- position이 MF 이면 미드필더, FW 면 공격수를 리턴하고
-- 둘 다 아니라면 교체선수를 리턴합니다.
SELECT player_name, DECODE(position, 'MF', '미드필더', 'FW', '공격수', '교체선수')
FROM player;

 

---

 

10주차

 group by/ having

집계 함수는 COUNT(*)를 제외하고는 NULL 값은 제외

집계 함수는 WHERE 절에 올 수 없음

GROUP BY 절을 사용할 때는 SELECT 절과는 다르게 ALIAS 명을 사용할 수가 없음

 

그럼 만약, GROUP BY로 그룹을 지은 상태에서 원하는 조건으로 필터링 하고싶다면?

이때 HAVING 절을 사용. HAVING 절은 GROUP BY절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시 가능

 

SELECT [DISTINCT] 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼이나 표현식]

[HAVING 그룹조건식] ;

HAVING 절의 위치는 GROUP BY 뒤에 오는 것이 적절

 

SELECT문 실행 순서로 옳은 것은?

fROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

 

TOP N = 상위 N개의 데이터

SELECT TOP(3) WITH TIES name, attr, height
FROM pokemon ORDER BY height, name;

 

ROWNUM = 조회된 데이터에 번호를 매겨 원하는 개수의 데이터를 가져옴

SELECT ename, sal FROM emp WHERE ROWNUM < 4 ORDER BY sal DESC;

--> sal 이 제일 높은 3개의 데이터를 조회하는 것이 아닌
-- 3개를 랜덤하게 가져온 후 정렬을 하게 됨

 

TCL(Transaction Control Language)

= 데이터를 삽입, 삭제, 수정하는 DML의 수행 이후

COMMIT이나 ROLLBACK을 하기 위해 사용

COMMIT 입력한 자료나 수정한 자료, 혹은 삭제한 자료에 대해서 문제가 없다고 판단이 되었을 때, 최종 트랜잭션을 확정

SAVEPOINT 트랜잭션 중간에 SAVEPOINT를 설정하여 처음으로 되돌리는 것이 아닌 SAVEPOINT 설정한 지점까지만 되돌려서 시스템의 부하를 줄일 수 있음

SAVEPOINT SVPT1;
INSERT INTO PLAYER VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
ROLLBACK TO SVPT1;

---

 

11주차

 

join

EQUI(등가) JOIN = 두 테이블 간에 칼럼의 값들이 서로 같은 경우 두 데이터를 하나의 데이터로 합치는 JOIN

JOIN 할 두 테이블을 FROM 절에 나열

JOIN의 조건은 WHERE 절에 = 연산자를 사용하여 표현

SELECT 
	'사원'.employee_id, 
	'사원'.last_name, 
	'사원'.department_id, 
	'부서'.department_name
FROM employees '사원', departments '부서'
WHERE '사원'.department_id = '부서'.department_id;

Non EQUI(비등가) JOIN 

두 개의 테이블 간에 칼럼들이 서로 정확하게 일치하는 것이 아닌 특정 범위 내에 있는 경우 JOIN을 하고자 할 때 사용

그래서 = 연산자가 아닌 다른 (BETWEEN, >, >=, <, <= ) 연산자들을 사용

SELECT e.ename '사원명', e.sal '급여', s.grade '급여등급'
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

SELECT COUNT(*) AS CNT
FROM emp_tbl a, rule_tbl b
WHERE a.ename LIKE b.rule;

3개 이상 TABLE JOIN

SELECT P.PLAYER_NAME '선수명', 
P.POSITION '포지션', 
T.REGION_NAME '연고지', 
T.TEAM_NAME '팀명', 
S.STADIUM_NAME '구장명' 
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID ORDER BY '선수명';

 

표준조인

조인 형태	설명
INNER JOIN	- JOIN 조건에서 일치하는 행만 반환 - EQUI 조인이라고도 함
NATURAL JOIN	- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼에 대해 INNER JOIN 수행 - Oracle에서만 지원하며 SQL Server에서는 지원하지 않음
USING 조건절	- NATURAL JOIN에 FROM 절에서 USING 조건을 이용하면 같은 이름을 가진 칼럼에서 원하는 칼럼만 선택적으로 INNER JOIN 가능 - Oracle에서만 지원하며 SQL Server에서는 지원하지 않음
ON 조건절	- 명시적으로 JOIN 조건을 지정하는 데 사용 - 칼럼명이 달라도 JOIN 조건을 사용할 수 있다
CROSS JOIN	- PRODUCT의 개념으로 테이블 간의 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 의미
OUTER JOIN	- INNER JOIN과 대비하여 JOIN 조건에서 동일한 값이 없는 행도 결과 집합에 포함시킬 때 사용

 

 

 

INNER JOIN

JOIN 조건에서 일치하는(동일한 값이 있는) 행만 반환

INNER JOIN은 그동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시

반드시 USING 조건절이나 ON 조건절을 사용

 

SELECT 테이블1.칼럼1, 테이블2.칼럼2, ..
FROM 테이블1 [INNER] JOIN 테이블2 
	ON 테이블1.칼럼 = 테이블2.칼럼;

SELECT 
	emp.deptno,
	empno,
	ename, 
	dname
FROM emp JOIN dept 
	ON emp.deptno = dept.deptno;

 

NATURAL JOIN

NATURAL JOIN은 INNER JOIN의 하위 개념

두 테이블의 JOIN 되는 칼럼을 별도로 지정하지 않아도 동일한 이름을 갖는 모든 칼럼들에 대해 자동으로 EQUI JOIN을 수행

NATURAL JOIN 사용시 주의해야할 점은 JOIN 기준이되는 칼럼에 ALIAS나 테이블 명과 같은 접두사를 칼럼에 붙일 수 없음

동일한 칼럼명이라도 다른 데이터 유형이 저장되었다면 제대로 동작되지 않음

 

USING 조건절

FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN

결과 집합이 INNER JOIN을 수행한 결과와 동일

NATURAL JOIN 절에서 USING 절을 이용하려면 NATURAL JOIN을 명시하지 않은 상태로 USING절을 사용

USING 조건절도 NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서 ALIAS 나 테이블 이름과 같은 접두사를 붙일 수 없음

 

SELECT empno,  -- JOIN 컬럼에 대해 Alias 사용 불가
       emp.ename,
       dept.loc,
       dept.dname
FROM emp JOIN dept
	USING (deptno); -- 괄호 없으면 에러 발생

Using 사용하여 원하는 컬럼만 선택적으로 조인

Inner join 에서 on 을 사용하는 것과 동일

 

ON 조건절

같은 의미이지만 다른 이름의 칼럼을 사용할 때는 USING 조건절을 사용할 수 없으며 이때는 ON 조건절을 사용

 

OUTER JOIN

OUTER 테이블은 모두 출력되고 INNER 테이블은 매칭되는 데이터만 출력

JOIN 조건에서 동일한 값이 없는 데이터도 반환할 때 사용할

LEFT OUTER JOIN

SELECT A.DEPT_NO , A.DEPT_NM , NVL(B.EMP_NO, 'NULL') AS EMP_NO , NVL(B.EMP_NM, 'NULL') AS EMP_NM 
FROM DEPT_EX A 
LEFT OUTER JOIN EMP_EX B ON (A.DEPT_NO = B.DEPT_NO AND A.DEPT_NM = '개발팀') 
WHERE A.DEPT_NO IS NOT NULL

RIGHT OUTER JOIN

 

각 군인들은 어떤 부대에 속해있는지 RIGHT JOIN을 활용하여 나타내보세요.
SELECT S.first_name, U.unit_name
FROM Unit U RIGHT JOIN Soldier S
ON S.unit_id = U.unit_id
ORDER BY U.unit_name;

FULL OUTER JOIN

FULL OUTER JOIN은 좌측 테이블과 우측 테이블이 동시에 기준이 되는 JOIN 방법으로

LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일

deptno 칼럼을 기준으로 dept와 dept_temp 테이블을 FULL JOIN 하는 방법
SELECT *
FROM dept FULL [OUTER] JOIN dept_temp
ON dept.deptno = dept_temp.deptno;

---

sql 3주차 복습

 

replace 특정 문자 다른 문자로 바꾸기 

select restaurant_name "원래 상점명",
       replace(restaurant_name, '바꿀 문자', '바뀔 문자') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

 

substr 원하는 문자만 남기기

Substr(조회할 칼럼, 시작위치, 글자 수)

 

concat 합치기

Concat(붙이고 싶은값 1, 2, 3)
-- 붙일 수 있는 문자의 종류: 컬럼, 한글 , 영어, 숫가, 기타 특수문자

 

if문

if( 조건, 조건 충족 시 사용할 값, 부합하지 않는 경우 값)

case when

Case when 조건1 then 값(수식)1
 When 조건2. Then 값(수식)2
Else 값(수식)3
End

데이터 타입 변경

-- 숫자형으로 변환
Cast(if(rating=’Not given’, ‘1’, rating) as decimal)

-- 문자형으로 변환
Concat(restaurant_name, ‘-‘, cast(order_id as char))