서브쿼리

서브쿼리

SELECT 쿼리문 안에 다시 한번 SELECT 쿼리문이 들어있는 쿼리 형태이다.

단일 SELECT 쿼리문 만으로는 어떤 조건식을 만들기 복잡할 때 쓴다.

또는 서로 다른 테이블에서 데이터 값을 조회하여 본 쿼리의 조건으로 쓰고자 할 때도 사용한다.

[!] 기본적인 실습순서

1. 상품테이블과 매출 테이블을 각각 만든다.
2. 두 테이블에 필요한 제약 조건과 각각 기본키와 외래키를 지정한다.
3. 상품 테이블에 상품을 입력한다.
4. 매출 테이블에 매출 데이터를 쿼리로 입력한다.
5. 각각의 테이블에 들어있는 값들을 모두 출력해보고 아래의 요구 쿼리를 작성하시오

[!] 요구쿼리(=요구사항)

1. 제약 조건이 잘 수행되는지 확인하시오.
2. 공급가의 경우 출력시에는 소숫점 이하 생략하시오/ (함수사용)
3. 상품의 판매 수량이 1,000개 이상인 상품에 대해서 출력하시오.(서브 쿼리 사용)

-- 테이블 생성
-- 상품 테이블
CREATE TABLE dbo.productTbl( p_code CHAR(10) PRIMARY KEY, -- 영문, 숫자만 들어오는경우 p_name NVARCHAR(16) NOT NULL, -- 유니코드지원 p_date DATE null, -- 입고일 p_area NVARCHAR(50) DEFAULT N'생산지미정', -- 생산지역 p_money MONEY NULL, -- 공급가격 p_num INT IDENTITY NOT NULL -- identity(1,1) 과 동일

);

-- 매출 테이블
-- [!] 외래키 지정
-- [!][컬럼명][데이터형식] FOREIGN KEY REFERENCES 테이블명
CREATE TABLE dbo.salesTbl ( s_code CHAR(10) PRIMARY KEY, -- 매출코드 s_branch NVARCHAR(10) NOT NULL, -- 매출지사 s_p_code CHAR(10) FOREIGN KEY REFERENCES dbo.productTbl(p_code),-- 상품코드(외래키) s_qty SMALLINT NULL, -- 판매수량 s_date DATE NULL, -- 매출일 s_num INT IDENTITY NOT NULL -- identity(1,1) 과 동일 );

-- 데이터 입력
--상품 입력
INSERT INTO dbo.productTbl VALUES('GD101','나이키','2000-12-12', '미국',900); INSERT INTO dbo.productTbl VALUES('GD102','아디다스','2000-01-13', '독일',450); INSERT INTO dbo.productTbl VALUES('GD103','프로스펙스','2000-02-14', '한국',7200); INSERT INTO dbo.productTbl VALUES('GD104','FILA','2000-03-15', '영국',500);

• • 매출입력
    INSERT INTO dbo.salesTbl VALUES ('ST0001', '서울', 'GD101', '1700', '2022-01-01'); INSERT INTO dbo.salesTbl VALUES ('ST0002', '서울', 'GD102', '1100', '2022-01-02'); INSERT INTO dbo.salesTbl VALUES ('ST0003', '세종', 'GD103', '1500', '2022-01-03'); INSERT INTO dbo.salesTbl VALUES ('ST0004', '부산', 'GD103', '700', '2022-01-04'); INSERT INTO dbo.salesTbl VALUES ('ST0005', '광주', 'GD101', '2900', '2022-01-05'); INSERT INTO dbo.salesTbl VALUES ('ST0006', '제주', 'GD103', '2100', '2022-01-06'); INSERT INTO dbo.salesTbl VALUES ('ST0007', '제주', 'GD104', '300', '2022-01-07');
    - - 데이터 출력
    SELECT * FROM dbo.productTbl order by p_num; SELECT * FROM dbo.salesTbl order by s_num;
    - - 서브쿼리(IN)
    SELECT p_code as '상품코드', p_name as '상품명', p_area as '생산지', p_money as '공급가' //REPLACE(convert(varchar, p_money),'.00','') as '공급가' FROM dbo.productTbl WHERE p_code IN ( SELECT s_p_code FROM dbo.salesTbl WHERE s_qty >= 1000 );

서브쿼리 IN 연산자

IN 연산자는 서브쿼리로 수행되어져서 나오는 결과 레코드(행)와 연관성이 높다.

즉, 서브쿼리가 반환하는 결과 레코드 값이 단일 값이냐 아니냐에 따라 사용여부가 결정된다.

서브쿼리가 단일 반환값이 경우 IN 연산자를 사용해도 또는 안해도 된다.(=등의 기타연산자 사용)

하위 쿼리에서 값을 둘 이상 반환하는 경우에는 신경을 좀 써야 한다.

서브쿼리에 DISTINCT 사용도 가능하다.

서브쿼리 UPDATE

— SELECT 이외에서 서브쿼리 사용

— 매출 테이블에서 판매수량이 제일 적은 최솟값 레코드를 0으로 수정하시오.

SELECT * FROM dbo.salesTbl ORDER BY s_num;

— 서브 쿼리를 모르는 경우

1. 수량 검색 > 2. 해당 레코드 업데이트

SELECT MIN(s_qty)FROM dbo.salesTbl;

UPDATE dbo.salesTbl SET s_qty=0 where s_qty=300;

— 서브 쿼리를 아는 경우

UPDATE dbo.salesTbl SET s_qty=0

WHERE s_qty = (SELECT MIN(s_qty)FROM dbo.salesTbl);

서브쿼리 DELETE

DELETE FROM dbo.salesTbl

WHERE s_qty = (

SELECT MAX(s_qty) FROM dbo.salesTbl

);

상관 서브쿼리

매출 테이블에서 각 상품(브랜드)별 신발 판매 수량이 각 상품별 평균 판매 수량 보다 높은 매출을 출력하시오.

SELECT * FROM dbo.salesTbl AS S1

WHERE s_qty > (

SELECT AVG(s_qty) FROM dbo.salesTbl AS S2 WHERE S2.s_p_code = S1.s_p_code

);

◈ 영어 : Correlated Subquery

• correlation (n)연관성, 상관관계 / correlate (v)(밀접한) 연관성이 있다, 상관관계가 있다.
• 상관 쿼리는 서브쿼리 단독으로 실행할 수 없다. ←일반 서브쿼리와의 차이점.
• 실행되는 순서도 메인쿼리가 먼저 실행이 되서 서브쿼리쪽으로 들어간다.
• 이때 메인쿼리는 서브쿼리로 한 행씩 넘긴다.