SQL을 이용한 데이터 조작 by 무궁화

2024-06-02 13:14:18

SQL의 데이터 조작 기능

SQL의 데이터 조작 기능은 원하는 데이터 검색, 새로운 데이터 삽입, 데이터 수정, 데이터 삭제로 분류할 수 있다. 이 네가지 데이터 조작 기능을 위한 SQL 문의 작성 규칙을 차례로 알아보자.

데이터의 검색

SELECT 문은 다양한 검색 유형을 지원한다. 이 절에서는 고객, 제품, 주문 테이블로 이루어진 판매 DB를 기준으로 예제를 진행한다.

※ INSERT 문을 이용해 앞에서 생성한 각 테이블에 튜플을 삽입한 후 예제를 실행하는 것이 좋다.

#고객 테이블 데이터 삽입
INSERT INTO 고객 VALUES ('apple', '정소화', 20, 'gold', '학생', 1000);
INSERT INTO 고객 VALUES ('banana', '김선우', 25, 'vip', '간호사', 2500);
INSERT INTO 고객 VALUES ('carrot', '고명석', 28, 'gold', '교사', 4500);
INSERT INTO 고객 VALUES ('orange', '김용욱', 22, 'silver', '학생', 0);
INSERT INTO 고객 VALUES ('melon', '성원용', 35, 'gold', '회사원', 5000);
INSERT INTO 고객 VALUES ('peach', '오형준', NULL, 'silver', '의사', 300);
INSERT INTO 고객 VALUES ('pear', '채광주', 31, 'silver', '회사원', 500);

#제품 테이블 데이터 삽입
INSERT INTO 제품 VALUES ('p01', '그냥만두', 5000, 4500, '대한식품');
INSERT INTO 제품 VALUES ('p02', '매운쫄면', 2500, 5500, '민국푸드');
INSERT INTO 제품 VALUES ('p03', '쿵떡파이', 3600, 2600, '한빛제과');
INSERT INTO 제품 VALUES ('p04', '맛난초콜릿', 1250, 2500, '한빛제과');
INSERT INTO 제품 VALUES ('p05', '얼큰라면', 2200, 1200, '대한식품');
INSERT INTO 제품 VALUES ('p06', '통통우동', 1000, 1550, '민국푸드');
INSERT INTO 제품 VALUES ('p07', '달콤비스킷', 1650, 1500, '한빛제과');

#주문 테이블 데이터 삽입
INSERT INTO 주문 VALUES ('o01', 'apple', 'p03', 10, '서울시 마포구', '2019-01-01');
INSERT INTO 주문 VALUES ('o02', 'melon', 'p01', 5, '인천시 계양구', '2019-01-10');
INSERT INTO 주문 VALUES ('o03', 'banana', 'p06', 45, '경기도 부천시', '2019-01-11');
INSERT INTO 주문 VALUES ('o04', 'carrot', 'p02', 8, '부산시 금정구', '2019-02-01');
INSERT INTO 주문 VALUES ('o05', 'melon', 'p06', 36, '경기도 용인시', '2019-02-20');
INSERT INTO 주문 VALUES ('o06', 'banana', 'p01', 19, '충청북도 보은군', '2019-03-02');
INSERT INTO 주문 VALUES ('o07', 'apple', 'p03', 22, '서울시 영등포구', '2019-03-15');
INSERT INTO 주문 VALUES ('o08', 'pear', 'p02', 50, '강원도 춘천시', '2019-04-10');
INSERT INTO 주문 VALUES ('o09', 'banana', 'p04', 15, '전라남도 목포시', '2019-04-11');
INSERT INTO 주문 VALUES ('o10', 'carrot', 'p03', 20, '경기도 안양시', '2019-05-22');

기본 검색

기본 검색을 위한 SELECT 문의 기본 형식은 다음과 같다.

SELECT 속성1, 속성2, ..., 속성n FROM 테이블_이름 ;

예제 10

고객 테이블에서 고객아이디, 고객이름, 등급 속성을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객아이디, 고객이름, 등급 FROM 고객 ;

예제 11 == 예제 12

고객 테이블에 존재하는 모든 속성을 검색해보자.

#작성코드
SELECT * FROM 고객 ;

#정답코드
SELECT 고객아이디, 고객이름, 나이, 등급, 직업, 적립금 FROM 고객 ;

테이블에 존재하는 모든 속성을 검색하기 위해 속성의 이름을 전부 나열하지 않고 *를 사용할 수도 있다.

예제 13 == 예제 14

제품 테이블에서 제조업체를 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 제조업체 FROM 제품;

관계 데이터 모델의 일반 릴레이션은 튜플의 집합 개념으로 이해할 수 있으며 튜플의 유일성을 만족해야 하기 때문에 릴레이션 하나에 동일한 튜플이 중복되면 안 된다. 그러나 SELECT 문의 수행 결과로 반환되는 결과 테이블에서는 동일한 튜플이 중복될 수 있다.

결과 테이블이 중복을 허용하도록 ALL 키워드를 명시적으로 사용해도 된다. SELECT ALL 제조업체 FROM 제품;

예제 15

제품 테이블에서 제조업체 속성을 중복 없이 검색해보자.

#작성코드 / 정답코드와 일치
SELECT DISTINCT 제조업체 FROM 제품;

결과 테이블에 출력되는 속성의 이름을 다른 이름으로 바꾸어 출력할 수도 있다. AS 키워드를 변경할 이름과 함께 지정하면 된다. 원래 테이블의 속성 이름이 실제로 바뀌는 것은 아니다. 그리고 AS 키워드는 생략할 수 있다.

예제 16

제품 테이블에서 제품명과 단가를 검색하되, 단가를 가격이라는 새 이름으로 출력해보자.

#작성코드
SELECT 제품명, AS 단가 FROM 제품;

#정답코드
SELECT 제품명, 단가 AS 가격 FROM 제품;

산술식을 이용한 검색

SELECT 키워드와 함께 산술식을 제시할 수 있다. 산술식은 속성의 이름과 +, -, *, / 등의 산술 연산자, 상수로 구성한다.

예제 17

제품 테이블에서 제품명과 단가 속성을 검색하되, 단가에 500원을 더해 '조정 단가'라는 새 이름으로 출력해보자.

#작성코드 / 정답코드와 동일
SELECT 제품명, 단가+500 AS '조정 단가' FROM 제품;

변경할 이름에 띄어쓰기가 들어가는 경우, 따옴표를 사용한다. 제품 테이블에 있는 단가 속성의 값이 실제로 변경되는 것은 아니다. 결과 테이블에서만 계산한 결과 값을 출력한다.

조건 탐색

조건을 만족하는 데이터만 검색하는 SELECT 문의 기본 형식은 다음과 같다.

SELECT 속성1, 속성2, ..., 속성n FROM 테이블_이름 WHERE 조건;

예제 18

제품 테이블에서 한빛제과가 제조한 제품의 제품명, 재고량, 단가를 검색해보자.

#작성코드
SELECT 제품명, 재고량, 단가 FROM 제품 WHERE '한빛제과';

#정답코드
SELECT 제품명, 재고량, 단가 FROM 제품 WHERE 제조업체 = '한빛제과';

예제 19

주문 테이블에서 apple 고객이 15개 이상 주문한 주문제품, 수량, 주문일자를 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 주문제품, 수량, 주문일자 FROM 주문 WHERE 주문고객 = 'apple' AND 수량 >= 15;

예제 20

주문 테이블에서 apple 고객이 주문했거나 15개 이상 주문된 제품의 주문제품, 수량, 주문일자, 주문고객을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 주문제품, 수량, 주문일자, 주문고객 FROM 주문 WHERE 주문고객 = 'apple' OR 수량 >= 15;

예제 21

제품 테이블에서 단가가 2,000원 이상이면서 3,000원 이하인 제품의 제품명, 단가, 제조업체를 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 제품명, 단가, 제조업체 FROM 제품 WHERE 단가 >= 2000 AND 단가 <= 3000;

LIKE를 이용한 검색

검색 조건을 부분적으로만 알고 있다면 LIKE 키워드를 이용해 검색할 수 있다. 검색 조건을 정확히 알면 = 연산자로 조건을 표현하면 되지만 부분적으로만 알고 있다면 = 대신 LIKE 키워드를 사용한다. 단 LIKE 키워드는 문자열을 이용하는 조건에만 사용할 수 있다.

예제 22

고객 테이블에서 성이 김 씨인 고객의 고객이름, 나이, 등급, 적립금을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객이름, 나이, 등급, 적립금 FROM 고객 WHERE 고객이름 LIKE '김%';

예제 23

고객 테이블에서 고객아이디가 5자인 고객의 고객아이디, 고객이름, 등급을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객아이디, 고객이름, 등급 FROM 고객 WHERE 고객아이디 LIKE '_____';

NULL을 이용한 검색

검색 조건에서 특정 속성의 값이 널 값인지를 비교하려면 IS NULL 키워드를 사용한다. 반대로 특정 속성의 값이 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용한다.

예제 24

고객 테이블에서 나이가 아직 입력되지 않은 고객의 고객이름을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객이름 FROM 고객 WHERE 나이 IS NULL;

예제 25

고객 테이블에서 나이가 이미 입력된 고객의 고객이름을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객이름 FROM 고객 WHERE 나이 IS NOT NULL;

정렬 검색

SELECT 문의 검색 결과 테이블은 일반적으로 DBMS가 정한 순서로 출력된다. 결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 ORDER BY 키워드를 사용한다.

SELECT 속성1, 속성2, ..., 속성n FROM 테이블_이름 WHERE 조건 ORDER BY 속성 ASC or DESC;

ORDER BY 키워드와 함께 정렬 기준이 되는 속성을 지정하고, 오름차순 정렬이면 ASC, 내림차순 정렬이면 DESC로 표현한다. 특별히 지정하지 않으면 오름차순으로 기본 정렬한다.

예제 26

고객 테이블에서 고객이름, 등급, 나이를 검색하되, 나이를 기준으로 내림차순 정렬해보자.

#작성코드 / 정답코드와 동일
SELECT 고객이름, 등급, 나이 FROM 고객 ORDER BY 나이 DESC;

예제 27

주문 테이블에서 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자를 검색해보자. 단, 주문제품을 기준으로 오름차순 정렬하고, 동일 제품은 수량을 기준으로 내림차순 정렬해보자.

#작성코드 / 정답코드와 동일
SELECT 주문고객, 주문제품, 수량, 주문일자 FROM 주문 WHERE 수량 >= 10 ORDER BY 주문제품 ASC, 수량 DESC;

집계 함수를 이용한 검색

특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 집계 함수(aggregate function)를 이용할 수 있다. 집계 함수는 열 함수(column function)라고도 하며 개수, 합계, 평균, 최댓값, 최솟값의 계산 기능을 제공한다. SUM과 AVG 함수는 숫자 데이터 타입의 속성에만 적용할 수 있고, 나머지 함수는 숫자 뿐 아니라 문자와 날짜 데이터 타입의 속성에도 적용할 수 있다.

집계 함수를 사용할 때 주의해야 할 두 가지 사항

1. 집계 함수는 널인 속성 값은 제외하고 계산한다.

2. 집계 함수는 WHERE 절에서는 사용할 수 없고 SELECT 절이나 HAVING 절에서만 사용할 수 있다.

예제 28

제품 테이블에서 모든 제품의 단가 평균을 검색해보자.

#작성코드
SELECT AVG(단가) AS '단가 평균' FROM 제품;

#정답코드
SELECT AVG(단가) FROM 제품;

예제 29

한빛제과에서 제조한 제품의 재고량 합계를 제품 테이블에서 검색해보자.

#작성코드 / 정답코드와 동일
SELECT SUM(재고량) AS '재고량 합계' FROM 제품 WHERE 제조업체 = '한빛제과';

COUNT 함수는 다른 함수와 달리 테이블의 모든 속성에 적용하여 개수를 계산할 수 있다.

예제 30

고객 테이블에 고객이 몇 명 등록되어 있는지 검색해보자.

#작성코드
SELECT COUNT(고객아이디) FROM 고객;

#정답코드

#고객아이디 속성을 이용해 계산하는 경우
SELECT COUNT(고객아이디) FROM 고객;

#나이 속성을 이용해 계산하는 경우
SELECT COUNT(나이) FROM 고객;

#*를 이용해 계산하는 경우
SELECT COUNT(*) FROM 고객;

COUNT 함수의 경우 어떤 속성을 기준으로 잡느냐에 따라 검색이 가능하다. 고객아이디 속성과 나이 속성을 이용해 고객의 수를 계산하는 과정을 보면 결과가 다르다. 그 이유는 집계 함수는 널 인 속성 값은 제외하기 때문이다.

COUNT(*)의 결과는 테이블에 있는 모든 튜플의 개수를 반환한다. 개수를 정확히 계산하려면 널 값이 없는 속성에 COUNT 함수를 적용하는 것이 좋기 때문에 보통 기본키 속성이나 *를 이용해 계산한다.

DISTINCT 키워드를 사용해 특정 속성 값의 중복을 없애고 집계 함수를 적용할 수도 있다.

예제 31

제품 테이블에서 제조업체의 수를 검색해보자.

#작성코드
SELECT COUNT(DISTINCT 제조업체) FROM 제품;

#정답코드
SELECT COUNT(DISTINCT 제조업체) AS '제조업체의 수' FROM 제품;

그룹별 검색

테이블에서 특정 속성의 값이 같은 튜플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다. 그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용하면 된다. GROUP BY 키워드가 없는 SELECT 문은 테이블 전체를 하나의 그룹으로 보고 검색하는 것이다.

SELECT

GROUP BY 키워드와 함께 그룹을 나누는 기준이 되는 속성을 지정한다. 그리고 그룹에 대한 조건은 HAVING 키워드와 함께 작성한다.

예제 32

주문 테이블에서 주문제품별 수량의 합계를 검색해보자.

#작성코드
SELECT 수량 FROM 주문 GROUP BY 수량 HAVING 주문제품;

#정답코드
SELECT 주문제품, SUM(수량) AS 총주문수량 FROM 주문 GROUP BY 주문제품;

주문제품별 수량의 합계를 구하기 위해 동일 제품을 주문한 튜플을 모아 그룹을 만들고, 그룹별로 수량의 합계를 계산한다. 그룹별로 검색할 때는 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋다. SELECT 절에 그룹을 나누는 기준 속성을 작성하지 않아도 실행은 되지만 어떤 그룹에 대한 검색 결과인지를 결과 테이블에서 확인하기 어렵기 때문이다.

예제 33

제품 테이블에서 제조업체별로 제조한 제품의 개수와 제품 중 가장 비싼 단가를 검색하되, 제품의 개수를 제품수라는 이름으로 출력하고 가장 비싼 단가는 최고가라는 이름으로 출력해보자.

#작성코드
SELECT 제조업체, COUNT(재고량) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체;

#정답코드
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체;

일반적인 검색 조건은 WHERE 절에 작성하지만 그룹에 대한 조건은 HAVING 절에 작성한다. 앞서 설명한 집계 함수는 WHERE 절에는 사용할 수 없지만 HAVING 절에는 사용할 수 있다.

예제 34

제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색해보자.

#작성코드
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가 FROM 제품 COUNT(*) >= 3 GROUP BY 제조업체;

#정답코드
SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가 FROM 제품 GROUP BY 제조업체 HAVING COUNT(*) >= 3;

HAVING 키워드를 사용하여 조건을 작성해야 된다는 것을 기억하자.

예제 35

고객 테이블에서 적립금 평균이 1,000원 이상인 등급에 대해 등급별 고객수와 적립금 평균을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 등급, COUNT(*) AS 고객수, AVG(적립금) AS 평균적립금 FROM 고객 GROUP BY 등급 HAVING AVG(적립금) >= 1000;

그룹별로 검색할 때는 집계 함수나 GROUP BY 절에 있는 속성 외의 속성은 SELECT 절에 사용할 수 없다.

#잘못된 코드
SELECT 주문제품, 주문고객, SUM(수량) AS 총주문수량 FROM 주문 GROUP BY 주문제품;

GROUP BY 절에 없는 주문고객 속성이 SELECT에 사용되었기 때문에 오류가 발생한다. 이를 해결하기 위해 GROUP BY 절에 주문고객 속성을 추가해야 한다.

예제 36

주문 테이블에서 각 주문고객이 주문한 제품의 총주문수량을 주문제품별로 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 주문제품, 주문고객, SUM(수량) AS 총주문수량 GROUP BY 주문제품, 주문고객;

예제 36에서 주문제품을 기준으로 하여 1차로 그룹을 나누고, 각 그룸에서 주문고객별로 더 작게 2차로 그룹 지어 수량의 합계를 계산한다. GROUP BY 절에는 그룹을 나누는 기준이 되는 주문제품과 주문고객 속성을 순서대로 작성한다.

여러 테이블에 대한 조인 검색

여러 개의 테이블을 연결하여 데이터를 검색하는 것을 조인 검색이라 한다. 조인 검색을 하려면 테이블을 연결해주는 속성이 필요하고 이 속성을 조인 속성이라 한다. 테이블을 연결하려면, 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 한다. 일반적으로 테이블의 관계를 나타내는 외래키를 조인 속성으로 이용한다.

예를 들어 banana 고객이 주문한 제품의 제품명을 검색하려면 제품과 주문 테이블이 필요하다. 검색에 필요한 주문고객 속성은 주문 테이블에 있지만 제품명 속성은 제품 테이블에 있기 때문이다. 두 테이블을 연결하려면 공통으로 가지고 있는 조인 속성이 필요하다. 여기서는 주문제품 속성과 제품번호 속성이 조인 속성의 역할을 한다.

조인 검색을 위한 SQL 문은 FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다. 여러 테이블을 이용하는 조인 검색의 특성상 이름이 같은 속성이 존재할 수 있기 때문에 속성의 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시해주는 것이 좋다. 예를 들어, '주문.주문고객'은 주문 테이블에 있는 주문고객 속성을 의미한다.

예제 37

판매 데이터베이스에서 banana 고객이 주문한 제품의 이름을 검색해보자.

#작성코드
SELECT 주문고객, 제품명 FROM 주문, 제품 WHERE 주문고객 = 'banana' AND 주문.주문제품 = 제품.제품번호;

#정답코드
SELECT 제품.제품명 FROM 제품, 주문 WHERE 주문.주문고객 = 'banana' AND 제품.제품번호 = 주문.주문제품;

원하는 정보가 잘 나와서 좋아했는데, 두 개 이상의 테이블을 사용하다보니 속성이 소속된 테이블의 이름을 표시해주는 것이 좋다는 사실을 잊고 말았다.

#다시 작성한 코드
SELECT 주문.주문고객 AS 주문고객, 제품.제품명 AS 제품명 FROM 주문, 제품 WHERE 주문.주문고객 = 'banana' AND 주문.주문제품 = 제품.제품번호;

속성이 소속된 테이블의 이름을 표시해주었고, 단순히 'banana' 고객이 주문한 제품명을 보여주는 것이 아닌, 주문고객의 이름까지 테이블에서 확인할 수 있게끔 코드를 작성했음.

예제 38

판매 데이터베이스에서 나이가 30세 이상인 고객이 주문한 제품의 주문제품과 주문일자를 검색해보자.

#작성코드
SELECT 주문.주문고객, 고객.나이, 주문.주문제품, 주문.주문일자 FROM 주문, 고객 WHERE 주문.주문고객 = 고객.고객아이디 AND 고객.나이 >= 30;

#정답코드
SELECT 주문.주문제품, 주문.주문일자 FROM 고객, 주문 WHERE 고객.나이 >= 30 AND 고객.고객아이디 = 주문.주문고객;

정답코드보다 더 식별하기 쉬운 코드를 작성했다고 생각함.

테이블 이름이 길면 속성 이름 앞에 소속 테이블을 표기하는 일이 번거로울 수 있다. 이 경우 테이블의 이름을 대신하는 단순한 별명을 사용할 수 있다. FROM 절에 테이블의 이름과 별명을 함께 제시하면 된다.

SELECT 주문제품, 주문일자 FROM 고객 c, 주문 o WHERE c.나이 >= 30 AND o.주문고객 = c.고객아이디;

예제 39

판매 데이터베이스에서 고명석 고객이 주문한 제품의 제품명을 검색해보자.

#작성코드
SELECT 고객이름, 제품명 FROM 고객 c, 제품 p, 주문 o WHERE c.고객이름 = '고명석' AND c.고객아이디 = o.주문고객 AND o.주문제품 = p.제품번호;

#정답코드
SELECT 제품.제품명 FROM 고객, 제품, 주문 WHERE 고객.고객이름 = '고명석' AND 고객.고객아이디 = 주문.주문고객 AND 제품.제품번호 = 주문.주문제품;

큰 차이는 없으나 테이블의 별명을 함께 제시하여 작성해봤음.

부속 질의문을 이용한 검색

SELECT 문 안에 또 다른 SELECT 문을 포함할 수 있다. 이를 부속 질의문 또는 서브 질의문이라 한다. 그리고 이 질의문을 포함하는 SELECT 문을 상위 질의문 또는 주 질의문이라 한다. 부속 질의문은 괄호로 묶어 작성하고 ORDER BY 절을 사용할 수 없으며, 상위 질의문보다 먼저 수행된다.

부속 질의문과 상위 질의문을 연결하는 연산자가 필요한데 부속 질의문의 종류에 따라 사용할 수 있는 연산자가 다르므로 주의해야 한다. 단일 행 부속 질의문은 일반 비교 연산자를 사용할 수 있지만, 다중 행 부속 질의문은 일반 비교 연산자를 사용할 수 없다.

예제 40

판매 데이터베이스에서 달콤비스킷을 생산한 제조업체가 만든 제품들의 제품명과 단가를 검색해보자.

#작성코드
SELECT 제조업체, 제품명, 단가 FROM 제품 WHERE 제품명 = '달콤비스킷';

#정답코드
SELECT 제조업체, 제품명, 단가 FROM 제품 WHERE 제조업체 = (SELECT 제조업체 FROM 제품 WHERE 제품명 = '달콤비스킷');

부속질의문 : '달콤비스킷'을 제조한 제조업체

상위질의문 : 부속질의문을 만족하는 제조업체

예제 41

판매 데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객이름, 적립금 FROM 고객 WHERE 적립금 = (SELECT MAX(적립금) FROM 고객);

처음에 WHERE 절에서 고객이름 = (SELECT 적립금 FROM 고객 WHERE MAX(적립금)); 으로 작성했는데 에러가 발생했다. 알아보니 WHERE 절에서는 집계 함수를 사용할 수 없다고 한다.

예제 42

판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색해보자.

#작성코드
SELECT 주문.주문고객, 제품.제품명, 제품.제조업체 FROM 주문, 제품 WHERE 주문.주문제품 = 제품.제품번호 AND 주문.주문고객 = 'banana';

#정답코드
SELECT 제품명, 제조업체 FROM 제품 WHERE 제품번호 IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

#다시 작성한 코드
SELECT DISTINCT 주문고객, 제품명, 제조업체 FROM 주문, 제품 WHERE 주문고객 = 'banana' AND 제품번호 IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

부속 질의문을 사용하되, 주문고객의 이름인 'banana'를 같이 띄우고 싶었음. 이 과정에서 상위 질의문 주문고객 속성 앞에 DISTINCT 를 안 붙이니 이유는 모르겠지만 중복된 튜플이 3개씩 나오는 걸 확인할 수 있었음.

예제 43

판매 데이터베이스에서 banana 고객이 주문하지 않은 제품의 제품명과 제조업체를 검색해보자.

#작성코드
SELECT 제품명, 제조업체 FROM 제품 WHERE 제품번호 IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 != 'banana');

#정답코드
SELECT 제품명, 제조업체 FROM 제품 WHERE 제품번호 NOT IN (SELECT 주문제품 FROM 주문 WHERE 주문고객 = 'banana');

예제 44

판매 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색해보자.

#작성코드
SELECT 제품명, 단가, 제조업체 FROM 제품 WHERE 단가 > (SELECT MAX(단가) FROM 제품 WHERE 제조업체 = '대한식품');

#정답코드
SELECT 제품명, 단가, 제조업체 FROM 제품 WHERE 단가 > ALL (SELECT 단가 FROM 제품 WHERE 제조업체 = '대한식품');

출력 결과는 동일하게 나왔음. 어차피 대한식품이 제조한 모든 제품의 단가보다 비싸다는 것은 단가가 제일 비싼 제품만 고려하면 된다고 생각했음. 하지만 다중 행 부속 질의문에서 사용 가능한 연산사를 학습하기엔 정답코드가 더 유용한 듯 함.

예제 45

판매 데이터베이스에서 2019년 3월 15일에 제품을 주문한 고객의 고객이름을 검색해보자.

#작성코드
SELECT 고객이름 FROM 고객 WHERE 고객아이디 IN (SELECT 주문고객 FROM 주문 WHERE 주문일자 = '2019-03-15');

#정답코드
SELECT 고객이름 FROM 고객 WHERE EXISTS (SELECT * FROM 주문 WHERE 주문일자 = '2019-03-15' AND 주문.주문고객 = 고객.고객아이디);

실습하면서 느낀 건데, 상위 질의문의 WHERE 절과 부속 질의문이 서로 다른 테이블에 있어도 알아서 참조가 되는 듯 싶음.

예제 46

판매 데이터베이스에서 2019년 3월 15일에 제품을 주문하지 않은 고객의 고객이름을 검색해보자.

#작성코드 / 정답코드와 동일
SELECT 고객이름 FROM 고객 WHERE NOT EXISTS (SELECT 주문고객 FROM 주문 WHERE 주문일자 = '2019-03-15' AND 주문.주문고객 = 고객.고객아이디);

데이터 삽입

테이블에 새로운 튜플을 삽입하기 위해 필요한 SQL 문은 INSERT다. INSERT 문을 이용해 튜플을 삽입하는 방법은 두 가지가 있다. 첫 번째는 테이블에 튜플을 직접 삽입하는 방법이고, 둘째는 부속 질의문을 이용해 튜플을 삽입하는 방법이다.

데이터 직접 삽입

#기본형식
INSERT INTO 테이블_이름[(속성_리스트)] VALUES 속성값(속성값_리스트);

INSERT INTO 테이블_이름(속성_리스트) VALUES (속성값_리스트);

INSERT INTO 테이블_이름(가, 나, 다, 라, 마) VALUES ('1', 2, '3', '4', 5);

예제 47

판메 데이터베이스의 고객 테이블에 고객아이디가 strawberry, 고객이름이 최유경, 나이가 30세, 등급이 vip, 직업이 공무원, 적립금이 100원인 새로운 고객의 정보를 삽입해보자. 그런 다음 고객 테이블에 있는 모든 내용을 검색하여 삽입된 새로운 튜플을 확인해보자.

#작성코드
INSERT INTO 고객(고객아이디, 고객이름, 나이, 등급, 직업, 적립금) VALUES ('strawberry', '최유경', 30, 'vip', '공무원', 100);
SELECT * FROM 고객;

속성 이름과 속성 값은 일대일 대응 관계가 되야 한다.

예제 48

판매 데이터베이스의 고객 테이블에 고객아이디가 tomato, 고객이름이 정은심, 나이가 36세, 등급이 gold, 적립금은 4,000원, 직업은 아직 모르는 새로운 고객의 정보를 삽입해보자. 그런 다음 고객 테이블에 있는 모든 내용을 검색하여, 삽입된 정은심 고객의 직업 속성이 널 값인지 확인해보자.

#작성코드 / 정답코드와 동일
INSERT INTO 고객(고객아이디, 고객이름, 나이, 등급, 적립금) VALUES ('tomato', '정은심', 36, 'gold', 4000);
SELECT * FROM 고객;

부속 질의문을 이용한 데이터 삽입

#기본코드
INSERT INTO 테이블_이름[(속성_리스트)] SELECT 문;

제품명, 재고량, 단가 속성으로 구성된 한빛제품 테이블이 존재한다고 가정하고, 부속 질의문을 이용한 INSERT 문의 예를 살펴보자.

INSERT INTO 한빛제품(제품명, 재고량, 단가) SELECT 제품명, 재고량, 단가 FROM 제품 WHERE 제조업체 = '한빛제과';

데이터의 수정

#기본코드
UPDATE 테이블_이름 SET 속성_이름1 = 값1, 속성_이름2 = 값2, ..., 속성_이름n = 값n WHERE 조건;

예제 49

제품 테이블에서 제품번호가 p03인 제품의 제품명을 통큰파이로 수정해보자.

#작성코드
UPDATE 제품 SET 제품번호 = 'p03' WHERE 제품명 = '통큰파이';

#정답코드
UPDATE 제품 SET 제품명 = '통큰파이' WHERE 제품번호 = 'p03';
SELECT * FROM 제품;

예제 50

제품 테이블에 있는 모든 제품의 단가를 10% 인상해보자. 그런 다음 제품 테이블의 모든 내용을 검색하여 인상 내용을 확인해보자.

#작성코드
UPDATE 제품 SET 단가 * 1.1;
SELECT * FROM 제품;

#정답코드
UPDATE 제품 SET 단가 = 단가 * 1.1;
SELECT * FROM 제품;

예제 51

판매 데이터베이스에서 정소화 고객이 주문한 제품의 주문수량을 5개로 수정해보자. 그런 다음 주문 테이블의 모든 내용을 검색하여 수정 내용을 확인해보자.

#작성코드 / 정답코드와 동일
UPDATE 주문 SET 수량 = 5 WHERE 주문고객 IN (SELECT 고객아이디 FROM 고객 WHERE 고객이름 = '정소화');
SELECT * FROM 주문;

데이터의 삭제

#기본코드
DELETE FROM 테이블_이름 WHERE 조건;

예제 52

주문 테이블에서 주문일자가 2019년 5월 22일 주문내역을 삭제해보자. 그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

#작성코드 / 정답코드와 동일
DELETE FROM 주문 WHERE 주문일자 = '2019-05-22';
SELECT * FROM 주문;

예제 53

판매 데이터베이스에서 정소화 고객이 주문한 내역을 주문 테이블에서 삭제해보자. 그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

#작성코드 / 정답코드와 동일
DELETE FROM 주문 WHERE 주문고객 IN (SELECT 고객아이디 FROM 고객 WHERE 고객이름 = '정소화');
SELECT * FROM 주문;

예제 54

판매 데이터베이스에서 주문 테이블에 존재하는 모든 튜플을 삭제해보자. 그런 다음 주문 테이블의 모든 내용을 검색하여 삭제 여부를 확인해보자.

#작성코드 / 정답코드와 동일
DELETE FROM 주문;
SELECT * FROM 주문;

'데이터베이스 > 데이터베이스 언어 SQL' 카테고리의 다른 글

삽입 SQL (0)	2024.06.06
뷰 (0)	2024.06.06
SQL을 이용한 데이터 정의 (0)	2024.06.01
SQL의 소개 (0)	2024.06.01