[TIL] 프로그래머스 SQL 끗

이번주 SQL 공부한 것들 정리해본다. 계속 우선순위에 밀리고 밀려 SQL 공부에 사실은 집중을 하지 못했다.

1. 쉬운코드 유튜브

• 백엔드 개발자분께서 재능기부를 하신 것 같은데, 놓치고 있던 기본 개념을 다시 정리하기에 좋았다. 특히 서브쿼리편
• - 1부 : https://youtu.be/dTBwgWMUguE - 2부 : https://youtu.be/lwmwlA2WhFc - 3부 : https://youtu.be/y_7rOoOodCY - 4부 : https://youtu.be/E-khvKjjVv4 - 5부 : https://youtu.be/rG8yQ7yKGTE

2-1. NULL의 의미

• unknown, unavailable or withheld, not applicable
• NULL은 IS, IS NOT 연산자를 사용해야 한다
• where절에 있는 condition의 결과가 True인 Tuple만 선택되며, 결과가 false나 unknown의 경우 tuple은 선택되지 않는다
• NOT IN의 주의 사항 : NULL값으 여부에 따라 UNKNOWN이 나올 수 있어요

2-2. NOT IN과 NULL

• NOT NULL을 사용하다 발생할 수 있는 오류
  • birth_date에 NULL값이 있으면, 아무 결과도 나오지 않는다

  SELECT D.id, D.name
  FROM department AS D
  WHERE D.id NOT IN(
  	SELECT E.dept_id
  	FROM employee E
  	WHERE E.birth_date >= '2000-01-01'
  );
  

• 대처법
  • NOT NULL
  • SELECT D.id, D.name FROM department AS D WHERE D.id NOT IN( SELECT E.dept_id FROM employee E WHERE E.birth_date >= '2000-01-01' AND E.dept_id IS NOT NULL );
  • NOT EXISTS
  • SELECT D.id, D.name FROM department AS D WHERE NOT_EXISTS ( SELECT * FROM employee E WHERE E.dept_id = D.id AND E.birth_date >= '2000-01-01' );

3-1. JOIN

• ID가 1인 임직원이 속한 부서의 이름

SELECT D.name
FROM employee E, department D
WHERE E.id=1 AND E.dept_id=D.id

• → implicit 이 아니라 explicit join은 가독성이 좋다
• INNER JOIN

SELECT D.name
FROM employee AS E JOIN department AS D ON E.dept_id=D.id
WHERE E.id=1;

• OUTER JOIN
  • LEFT JOIN, RIGHT JOIN, FULL JOIN
• USING : 같은 attribute 기준으로 equi join하면, 중복되지 않게 처리해줌

SELECT *
FROM employee E INNER JOIN department D USING (dept_id);

• NATURAL JOIN : 같은 이름을 가진 모든 attribute pair에 대해 equi join 수행
• CROSS JOIN : 두 테이블의 tuple pair로 만들 수 있는 모든 조합을 result table로 반환
• SELF JOIN

• ID가 1003인 부서에 속하는 임직원 중 리더를 제외한 부서원의 id, 이름, 연봉 알려조

SELECT E.id, E.name, E.salary
FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id=D.id
WHERE D.id=1003 AND E.id != D.leader_id

• ID가 2001인 프로젝트에 참여한 임직원 이름, 직군,소속부서 이름

SELECT E.name, E.position, D.name
FROM works_on W JOIN EMPLOYEE E ON W.empl_id=E.id
JOIN DEPARTMENT D ON E.dept_id=D.id
WHERE W.proj_id = 2001;

4. GROUP BY/ORDER BY/AGG

• 회사 전체 평균 연봉보다 평균 연봉이 적은 부서들의 평균 연봉

SELECT dept_id, AVG(salary)
FROM employee
GROUP BY 1
HAVING AVG(salary) < (SELECT AVG(salary) FROM employee)

• 각 프로젝트별 프로젝트에 참여한 90년대생 숫자와 평균연봉

SELECT proj_id, count(*), ROUND(AVG(salary),0)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id

• 프로젝트 참여 인원이 7명 이상인 프로젝트에 한정해서 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 평균 연봉

SELECT proj_id, COUNT(*), ROUND(AVG(salary),0)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1999-01-01' AND '1999-12-31'
	AND W.proj_id IN (SELECT proj_id FROM works_on GROUP BY proj_id HAVING COUNT(*)>7)
GROUP BY W.proj_id
ORDER BY W.proj_id 

5. Stored Procedure

• 사용자가 정의한 프로시져
• RDBMS에 저장되고 사용되는 프로시져
• 두 수의 곱셈 결과를 가져오는 프로시져를 작성해줘

delimiter $$
CREATE PROCEDURE product(IN a int, IN b int, OUT result int)
BEGIN 
	SET result = a*b;
END
$$
delimiter ;

call product(5,7, @result);

select @result;
>> 35

• 두 정수를 맞바꾸는 프로시저를 작성해줘

delimiter$$
CREATE PROCEDURE swap(INOUT a int, INOUT b int)
BEGIN 
	SET @temp = a;
	SET a = b;
	SET b = @temp;
END
$$
delimiter;

set @a=5, @b=7;
call swap(@a, @b);

select @result;
>> 3,2

• 사용자가 프로필 닉네임을 바꾸면, 이전 닉네임을 로그에 저장하고 새 닉네임으로 업데이트 하는 프로시져를 작성해줘
  • users : id, nickname
  • nickname_logs : id, prev_nickname, until

delimiter $$
CREATE PROCEDURE change_nickname(user_id INT, new_nick varchar(30))
BEGIN
	insert into nickname_logs(
		SELECT id, nickname,now()
		FROM users where id = user_id);
	update users set nickname = new_nick where id=user_id;
END
$$
delimiter ;

call change_nickname(1,'ZIDANE');

STORED PROCEDURE STORED FUNCTION

create문법	CREATE STORED PROCEDURE
return키워드로 값 반환	불가능	가능
파라미터 값 반환	가능	일부 가능
값을 꼭 반환해야하나	필수 아님	필수
SQL statement에서 호출	불가능	가능
transaction 사용	가능	대부분 불가능
주된 사용 목적	비즈니스 로직	컴퓨팅