DB) #4 SQL 기초 (2)

지우·2026년 3월 28일

database

목록 보기
4/7

데이터 조작어 - 검색

부속질의

SELECT 문 안에 또 다른 SELECT 문을 포함하는 질의

부속 질의문(서브 질의문) : 괄호로 묶어서 작성, ORDER BY 절 사용 X
부속 질의문 먼저 수행 -> 상위 질의문 수행
부속 질의문과 상위 질의문을 연결하는 연산자 필요
연산자

예시 )
Q1. 가장 비싼 도서의 이름을 나타내세요

Q2. 도서를 구매한 적이 있는 고객의 이름을 검색하세요

orders 테이블에 custid가 존재하는 customer의 name

Q3. 대한미디어에서 출판한 도서를 구매한 고객의 이름을 나타내세요

실행 순서 :
1. 출판사가 '대한미디어'인 책의 bookid
2. 해당 bookid를 주문한 적이 있는 고객의 custid
3. 해당 custid의 고객 이름

안쪽 SELECT문부터 순차적으로 실행

상관 부속 질의

상위 부속질의와 하위 부속질의가 독립적이지 않고 서로 관련됨
겉으로 반복문이 보이진 않지만 내부적으로 계속 반복됨 ( 행 단위로 반복 실행 )

예시 )
Q1. 출판사별로 출판사의 평균 도서 가격보다 비싼 도서를 구하세요

실행 순서 :
1. 외부쿼리(book b1)에서 한 행을 가져옴
2. 내부쿼리 실행 : pulisher 비교
3. 외부쿼리의 현재 책 가격과 비교

동일한 테이블을 b1과 b2로 나타내서 구분함
겉으로 보기엔 반복문이 없지만, b1 테이블의 투플을 하나씩 가져가서 b2의 투플과 비교하기 때문에 위의 실행 순서가 계속 반복됨

집합 연산

UNION

합집합

UNION : 중복 제거
UNION ALL : 중복 포함

MINUS, INTERSECT -> NOT IN, IN

제외 / 포함

예시 )
Q1. 대한민국에 거주하는 고객의 이름에서 도서를 주문한 고객의 이름을 제외하고 나타내세요

Q2. 대한민국에 거주하는 고객 중 도서를 주문한 고객의 이름을 나타내세요

EXISTS, NOT EXISTS

상관 부속질의문 형식으로 부속질의의 겨로가가 존재하는지 여부를 확인

EXISTS : 부속질의문이 한 행이라도 반환하면 true
NOT EXISTS : 반환행이 하나도 존재하지 않으면 true

예시 )
Q1. 주문이 있는 고객의 이름과 주소를 나타내세요

cs의 한 행에 대해 od 테이블의 모든 행과 비교
EXISTS 내부의 SELECT절은 존재 여부만 확인하는 용도이기 때문에 주로 * 사용

퀴즈

Q1. 마당서점의 고객이 요구하는 다음 질문에 대해 SQL 문을 작성하시오
(5) 박지성이 구매한 도서의 출판사 수

(6) 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이

(7) 박지성이 구매하지 않은 도서의 이름

Q2. 마당서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL 문을 작성하시오
(8) 주문하지 않은 고객의 이름 (부속질의 사용)

(9) 주문 금액의 총액과 주문의 평균 금액

(10) 고객의 이름과 고객별 구매액

(11) 고객의 이름과 고객이 구매한 도서 목록

(12) 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
이건 다시 풀어보기,,,

(13) 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름

ROUND 사용!

데이터 정의어

CREATE TABLE

테이블 생성

CREATE TABLE 테이블이름 (
	속성이름 데이터타입 
    ...
    );

속성과 속성에 대한 제약 정의 : NULL / NOT NULL / DEFAULT
데이터 타입 : INTEGER / CHAR / VARCHAR / DATE

CHAR과 VARCHAR : 고정 크기가 아닐 땐 varchar 사용
DATE와 DATETIME : date는 연/월/일(%Y-%m-%d), datetime은 시/분/초 까지 다 나옴

예시 )
Q1. bookname은 NULL 값을 가질 수 없고, publisher에는 같은 값이 있으면 안된다. price에 값이 입력되지 않을 경우 기본값 10000을 저장한다. 또 가격은 최소 1000원 이상으로 한다. 앞의 조건을 모두 만족하는 NewBook 테이블을 생성하세요.

외래키 지정시
ON DELETE CASCADE : 관련 투플을 함께 삭제
ON DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경
ON DELETE RESTRICT : 부모행이 참조되고 있으면 삭제/수정 불가

특정 행 말고 전체를 지울 때는 자식 먼저 지우고 부모를 지워야 함

ALTER TABLE

생성된 테이블 속성 변경

  • 새로운 속성 추가 => ADD
ALTER TABLE NewBook ADD isbn VARCHAR(13);
  • 속성 변경 => MODIFY
ALTER TABLE NewBook MODIFY isbn INTEGER;
  • 속성 삭제 => DROP COLUMN
ALTER TABLE NewBook DROP COLUMN isbn;

  • NOT NULL 등 제약 조건 추가 적용 가능

  • 속성에 키 추가 가능

DROP TABLE

테이블 삭제 -> 구조, 데이터 모두 삭제하기 때문에 주의해야함
데이터만 삭제하려면 DELETE 사용
삭제할 테이블이 참조되고 있으면 삭제되지 않음

데이터 조작어 - 삽입, 수정, 삭제

INSERT

테이블에 새로운 투플 삽입

INSERT INTO 테이블이름(속성리스트)
	VALUES(값리스트);
  • 속성리스트는 생략 가능 / 값리스트는 생략 불가능
  • 속성 순서 잘 지켜야 함
  • 몇 개의 속성만 입력해야 한다면 해당하는 속성만 명시하면 됨 -> 없는 값은 NULL 처리됨
  • SELECT문을 사용하여 작성할 수도 있음 -> 다른 테이블의 튜플을 가져와서 추가할 떄 사용
    예시 )
INSERT INTO Book(bookid, bookname, price, publisher)
	SELECT bookid, bookname, price, pulisher
    FROM imported_book;

UPDATE

특정 속성값을 수정

UPDATE 테이블이름         
SET 속성이름 = 값 ...  //속성 값을 어떻게 수정할 것인지를 지정
WHERE 검색조건        //제시된 조건을 만족하는 투플만 수정, WHERE절 생략하면 모든 투플이 대상

허가 권한을 1 또는 0으로 수정해야 오류 안나고 잘 됨

#SET SQL_SAFE_UPDATES = 1 또는 0

예시 )
Q1. Book 테이블에서 14번 '스포츠 의학'의 출판사를 imported_book 테이블에 있는 21번 책의 출판사와 동일하게 변경하세요

UPDATE book
SET publisher = (SELECT publisher
					  FROM imported_book
					  WHERE bookid = 21
					  )
WHERE bookid = 14;

DELETE

테이블에 있는 기존 투플을 삭제 

DELETE FROM 테이블이름
WHERE 검색조건 ;      //생략 가능

SQL 문제 풀이

Q1. 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요

SELECT ANIMAL_TYPE, count(ANIMAL_TYPE) 
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

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

SELECT a.name, a.datetime
FROM animal_ins as a
    left join animal_outs as b
    on a.animal_id = b.animal_id
WHERE b.animal_id is null
ORDER BY datetime
limit 3;

왼쪽 외부 조인 사용해서 a와b의 animal_id 비교

b.animal_id is null

테이블 헷갈리지 않고 WHERE문 잘 작성하게 주의!!

Q3. 이 서비스에서는 공간을 둘 이상 등록한 사람을 '헤비 유저'라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

SELECT *
FROM PLACES
WHERE HOST_ID IN (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING count(*) >= 2
)
ORDER BY ID;

Q4. PATIENT 테이블에서 12세 이하인 여자 환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요. 이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

SELECT pt_name, pt_no, gend_cd, age, ifnull(tlno, 'NONE')
FROM patient
WHERE gend_cd = 'W' and age <= 12
ORDER BY age DESC, pt_name ASC;

Q5. 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL문을 작성해주세요.

SELECT a.animal_id, a.animal_type, a.name
FROM animal_ins as a
    left join animal_outs as b
    on a.animal_id = b.animal_id
WHERE a.sex_upon_intake != b.sex_upon_outcome
ORDER BY animal_id;

Q6. DEVELOPER_INFOS 테이블에서 Python 스킬을 가진 개발자의 정보를 조회ㅐ하려 합니다. Python 스킬을 가진 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해주세요. 결과는 ID 기준으로 오름차순 정렬해 주세요.

SELECT id, email, first_name, last_name
FROM developer_infos
WHERE skill_1 = 'python' or skill_2 = 'python' or skill_3 = 'python'
ORDER BY id;

Q7. 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 9:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대순으로 정렬해야 합니다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR;

DATETIME은 시간까지 표시 / DATE는 년월일만 표시함

Q8. 입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 'No name'으로 표시해 주세요. 

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

IFNULL 사용!!

profile
지우
이전 포스트

DB) #3 SQL 기초 (1)

다음 포스트

DB) #5 SQL 고급(1)

0개의 댓글