23122금_TIL

sql 코드카타

문제 33.

 

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

 

 

SELECT p.price*s.sales_amount 
from product p join offline_sale s on p.product_id = s.product_id
group by p.product_code
order by 1 desc, p.product_code

SELECT p.product_code, sum(p.price*s.sales_amount )
from product p join offline_sale s on p.product_id = s.product_id
group by p.product_code
order by 2 desc, p.product_code

문제 34.

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

 

SELECT i.animal_id, i.name
from animal_ins i join animal_outs o on i.animal_id = o.animal_id
where i.datetime > o.datetime
order by i.datetime

---

sqld 12주차

집합연산자

2개 이상의 쿼리 수행 결과를 하나의 결과로 합침

집합 연산자	설명
UNION (합집합 중복 허용 x)	- SQL문 결과에 대한 합집합 - 모든 중복된 행을 하나의 행으로 만들어서 합집합의 결과를 반환 - 중복 배제하기 위한 정렬 연산이 있어 시스템의 부하가 있음
UNION ALL (합집합 - 중복 허용 o)	- SQL문 결과에 대한 합집합 - 중복된 행도 그대로 포함하여 합집합 결과를 반환하는 연산자 - 주로 여러 쿼리문의 결과가 상호 배타적일 때 많이 사용
INTERSECT (INTERSECTION)	- SQL문 결과에 대한 교집합 - 중복을 허용하지 않고 하나의 행을 만들어 줌
MINUS(EXCEPT)	- SQL문 결과에 대한 차집합 - 특정한 SQL문 결과에서 다른 SQL문 결과를 뺀 값 - 중복을 허용하지 않고 하나의 행으로 만들어 줌

 

사용예시 이렇게 둘의 합집합을 만들 수

*서브쿼리 

 

서브쿼리; 메인쿼리의 칼럼 모두 사용 가능

메인쿼리; 서브쿼리의 칼럼 사용 불가

* 연관 서브쿼리; 서브쿼리 내에 메인쿼리 칼럼이 사용됨. = 서브쿼리 값 결정이 메인쿼리에 의존

SELECT A.EMPNO
, A.ENAME
, A.DEPTNO
, A.SAL 
FROM EMP A
WHERE A.SAL >= (SELECT AVG(X.SAL)
		FROM EMP X
		WHERE X.DEPTNO = A.DEPTNO
		GROUP BY X.DEPTNO )

 

* EXIST 서브쿼리; 연관 서브쿼리, 서브쿼리의 결과가 참이라면 결과 집합에 포함. 서브쿼리 결과를 만족하는 값이 존재하는 지 여부 확인

SELECT DISTINCT country_name
FROM nation n
WHERE EXISTS (
    SELECT 1 -- * 동일
    FROM participant p
    WHERE p.nation_id = n.nation_id);

 

*비연관쿼리 ; 서브쿼리가 메인쿼리의 어떤 것도 참조하지 않고 단독으로 사용됨, 메인쿼리 없이 서브쿼리 자체로 실행될 수 있는 완전한 쿼리문

 

동작방식	설명
단일행 서브쿼리 (Single Row)	- 실행 결과가 항상 1건 이하인 서브쿼리입니다. - 단일 행 비교 연산자인 =, <, ≤, >, ≥, <>과 함께 사용합니다.
다중행 서브쿼리 (Multi Row)	- 실행 결과가 여러 건인 서브쿼리입니다. - 다중 행 비교 연산자인 IN, ALL, ANY, SOME, EXISTS와 함께 사용합니다.
다중칼럼 서브쿼리 (Multi Column)	- 실행 결과로 여러 칼럼을 반환하는 서브쿼리입니다. - 메인쿼리의 조건절에 여러 칼럼을 동시 비교할 수 있습니다. - 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 위치가 동일해야 합니다.

 

 

다중행 비교 연산자

IN (서브쿼리): 서브쿼리 결과에 존재하는 임의의 값과 동일한 조건을 의미

ALL (서브쿼리):서브쿼리 결과에 존재하는 모든 값이라도 만족하는 조건을 의미

ANY (서브쿼리) / SOME (서브쿼리):서브쿼리 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미

EXISTS (서브쿼리):서브쿼리 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미

 

*다중칼럼 서브쿼리

 

서브쿼리의 결과로 여러개의 칼럼이 반환됨

SELECT deptno, dname, empno, ename, sal 
FROM ( SELECT e.deptno, d.dname, e.empno, e.ename, e.sal, 
RANK() OVER (PARTITION BY e.deptno 
ORDER BY e.sal DESC) AS sal_rank 
FROM emp e JOIN dept d ON e.deptno = d.deptno ) 
WHERE sal_rank = 1;

 

*기타서브쿼리

select; 스칼라 서브쿼리

from; 인라인뷰 서브쿼리

where; 서브쿼리

having; 서브쿼리

insert 문의 values; 서브쿼리

update 문의 set; 서브쿼리

 

*뷰

데이터베이스에 저장된 정보를 더 편리하게 보여주기 위해 사용되는 가상의 테이블. 

CREATE VIEW V_DEPT_EMP AS
SELECT E.EMPNO,
       E.ENAME,
       E.JOB,
       E.SAL,
       D.DNAME
       FROM DEPT D,
     EMP E
WHERE D.DEPTNO = E.DEPTNO;

 

-- 이미 존재하는 뷰를 참조해서도 생성 가능
CREATE VIEW V_DEPT_EMP_FILTER AS
SELECT ENAME,
       JOB
FROM V_DEPT_EMP
WHERE EMPNO IN (7698, 7788);

 

-- 뷰 제거; drop view
DROP VIEW V_DEPT_EMP;
DROP VIEW V_DEPT_EMP_FILTER;

-- 테이블 조인을 통해 뷰테이블 생성 가능
CREATE VIEW v_p_nation AS
SELECT p.first_name,
       p.last_name,
       p.main_sport_id,
       p.height,
       p.weight,
       n.country_name,
       n.population
FROM participant p, nation n
WHERE p.nation_id = n.nation_id;

---

 

10시에 과제 공개..

문득 교수님이 생각나는 대목이었다.. 이거 어떻게 하누.. 함서.... my sql이랑 디디버를 연결하려고.. 일단 mysql 설치하는데.. 대충 두시간 끌어안고 있었지만 실패했다. 노트북을 바라보며 주먹이 살짝 울었으나, 노트북보다 비싼 사람이 되어야지가 오늘의 교훈입니다.