[인터넷DB응용] SQL 활용 - 1

Select 문

구조

Select 속성1, 속성2, ...
From 테이블1, 테이블2, ...
Where 조건1 (and/or) 조건2 (and/or) ...;

 

처리 과정

 

1. 테이블들의 카티젼 곱을 구한다.
2. 카티젼 곱의 결과 테이블에서 조건에 맞는 투플들만 선택한다.
   1. 조건이 여러 개일 경우 각각의 투플들에 대해 참과 거짓을 판단하여 참인 투플들만 선택한다.
3. 주어진 속성들을 추출한다.

 

조건문 만들기

 

• 숫자 데이터: 속성 (=, <, >, <=, >=, <>)
• 문자 데이터: = 또는 Like(패턴 검색을 할 때)
• 논리 연산자: and, or, not

 

✨나는 솔로 테이블

이름	전화번호	주소	직업
광수	010-1234-5678	서울	소프트웨어 프로그래머
영철	010-2345-6789	부산	난초 농사
상철	010-3456-7891	울산	한의사

 

Select 이름 from 나는솔로 where 직업 = '난초 농사';
Select 이름 from 나는솔로 where 직업 like '%의사';
Select 이름 from 나는솔로 where not 직업 like '%의사';
Select 이름 from 나는솔로 where 직업 like '%의사' and 지역 = '부산';

 

연산자의 종류

 

• 산술 연산자: (),  *, / , +, - 순으로 우선순위가 결정됨
• 비교 연산자: =, <>, !=, <, > 등
• 논리 연산자: NOT, AND, OR
• 기타 연산자: IN, BETWEEN, IS NULL, LIKE, EXISTS 등
• 결합 연산자: || (스트링 값 결합 시 사용)
• 집합 연산자: UNION ALL, UNION, INTERSECT, MINUS

 

산술 연산자

-- +
SELECT 학번, 이름, 이수학점 + 20 FROM 학생;

-- -
SELECT 학번, 이름, 140 - 이수학점 FROM 학생;

-- *
SELECT 과목이름, 최대인원 * 1.1 FROM 과목;

-- /
SELECT 과목이름, 최대인원/2 FROM 과목;

-- =
SELECT * FROM 학생 WHERE 이수학점 = 100;

-- !=, <>
SELECT * FROM 학생 WHERE 이수학점 != 100;

-- >, <
SELECT * FROM 학생 WHERE 이수학점 > 100;
SELECT * FROM 학생 WHERE 이수학점 < 100;

-- >=, <=
SELECT * FROM 학생 WHERE 이수학점 >= 100;
SELECT * FROM 학생 WHERE 이수학점 <= 100;

 

논리 연산자

-- AND: 두개의 조건 모두를 만족해야 참인 경우
SELECT * FROM 학생 WHERE 이수학점 > 100 and 평점 >= 3;

-- OR: 두개의 조건 중 하나를 만족하면 참인 경우
SELECT * FROM 학생 WHERE 이수학점 > 100 or 평점 >=3;

-- NOT: 조건을 만족하지 못하면 참인 경우
SELECT * FROM 학생 WHERE not 평점 >= 3;

 

 기타 연산자

-- IN: 여러 개의 조건 값 중 하나만 만족하면 참인 경우
SELECT * FROM 수강 WHERE 과목번호 IN ('cs100', 'cs200');

-- LIKE: 조건 값을 명확히 알지 못하는 경우(패턴 검색)
SELECT * FROM 학생 WHERE 학번 LIKE '99%';

-- BETWEEN A AND B: 범위를 가진 조건 값으로 검색해야 하는 겨우
SELECT * FROM 학생 WHERE 평점 BETWEEN 3 AND 4;

-- IS NULL: NULL 값을 가진 컬럼을 검색하는 경우
SELECT * FROM 과목 WHERE 최대인원 IS NULL;

-- ||
SELECT 학번 || '의 평점은 ' || 평점 AS 평균평점 FROM 학생;

-- ANY, SOME
SELECT * FROM 학생
WHERE 이수학점 > SOME (SELECT 이수학점 FROM 학생
WHERE 학과번호 = '020');

-- ALL
SELECT * FROM 학생
WHERE 이수학점 > ALL (SELECT 이수학점 FROM 학생
WHERE 학과번호 = '020');

 

---

Select 문에 사용되는 키워드

중복된 투플 관련

 

• Distinct: 중복된 투플을 한 번만 표시하라.
• All: 중복된 투플을 그냥 다 보여준다.
• 디폴트는 all

Select 직업 from 나는솔로;
Select distinct 직업 from 나는솔로;
Select all 직업 from 나는솔로;

 

투플 정렬하기, 별명 지정하기

 

• Order by 속성이름 [asc/desc]
  • 특정 속성 값은 순서대로 정렬해서 나타내기
  • 디폴트는 asc
• Where 절이 있으면 where 절 다음에 표시

Select * from 학생 order by 이름;
Select * from 학생 order by 이름 asc;
Select * from 학생 order by 이름 desc;
Select * from 학생 where 학번 like '99%' order by 이름;
Select 이름 as 학생_이름 from 학생;

 

---

Select 문의 활용

자연 조인

 

• 카티젼 조인은 의미없는 레코드들을 포함한다.
• 자연 조인을 통해 의미 있는 레코드들만 뽑아낸다.
• 두 테이블의 공통 속성은 거의 대부분, 한 테이블에서는 기본키이고 한 테이블에서는 외래키이다.
• 공통 속성을 가지는 테이블들은 연관이 있다고 본다.
• 자연 조인을 하면 하위 테이블의 레코드 개수에 맞춰서 레코드 개수가 나오게 된다.
• 속성 또한 겹치는게 있으면 한개만 보여준다.

 

1. 두 테이블의 카티젼 곱을 구한다. From 학생, 수강
2. 카티젼 곱의 결과 테이블에서 공통 속성의 값이 같은 투플들만 남긴다. where 학생.학번 = 수강.학번
3. 공통 속성은 한 번만 추출하고 나머지 속성들을 모두 추출하는 속성 추출하는 속성 추출 연산을 한다. Select 학생.학번, 학생.이름, ...

 

✅ n개의 테이블을 자연조인 하기 위해서는 비교식이 n-1개가 필요하다.

 

 자연 조인의 두 가지 방법

 

1. 기본적인 Select 문을 이용
2. Natural JOIN을 이용
   • 문제점: 속성 이름을 추가로 명시해주면, 정확하게 인지하지 못할 수 있다.

-- 1
Select 학생.*, 수강.과목번호 from 학생, 수강
where 학생.학번=수강.학번;

-- 2
Select * from 학생 natural join 수강;

 

자연 조인의 활용

-- 과목번호 cs100을 수강하는 학생의 학번을 구하라.
Select 학번 from 수강 where 과목번호 = 'cs100';

-- 과목번호 cs100을 수강하는 학생의 학번과 이름을 구하라.
Select 학생.학번, 학생.이름
from 학생, 수강
where 학생.학번 = 수강.학번
and 수강.과목번호 = 'cs100';

-- 이번 학기 수강 내역(학생의 이름, 과목이름)을 모두 구하라.
Select 학생.이름, 과목.과목이름 from 학생, 과목; (x)
Select 학생.이름, 과목.과목이름 from 학생, 과목, 수강
where 학생.학번 = 수강.학번 and 과목.과목번호 = 수강.과목번호;

 

계산 쿼리

 

• 저장되어 있는 값을 바탕으로 필요한 값들을 만들어내는 쿼리.
• 속성이 임시로 하나 추가되는 것과 같다.

Select 이름, 140-이수학점 as 남은학점 from 학생;

 

---

문제

-- 학번 '9922222'인 학생의 모든 정보를 구해보자.(수강 신청 내역은 제외)
select * from 학생 where 학번='9922222';

-- 한 과목이라도 수강 신청한 학번을 모두 구하는데 중복이 안 되게 하자.
select distinct 학번 from 수강;

-- 과목번호가 'cs'로 시작하는 과목의 과목이름을 모두 구해 보자.
select 과목이름 from 과목 where 과목이름 like 'cs%';

-- 성이 김씨가 아닌 학생의 학번을 구해 보자.
select 학번 from 학생 where not 이름 like '김%';

-- 학과번호가 '010'이면서 평점이 3이상인 학생들의 학번과 이름을 구해 보자.
select 학번, 이름 from 학생 where 학과번호='010' and 평점 >= 3;

-- 이수학점이 100 이상인 학생의 학번과 이름을 구해 보자. 단, 순서는 이름의 오름차순으로 나타내시오.
select 학번, 이름 from 학생 where 이수학점 >= 100 order by 이름;

-- 평점이 2.5 이상이고 3.5 이하인 학생의 학번과 이름을 구해 보자.
select 학번, 이름 from 학생 where 평점 >= 2.5 and 평점 <= 3.5;
select 학번, 이름 from 학생 where 평점 between 2.5 and 3.5;

-- 과목 테이블과 수강 테이블을 자연 조인한 결과를 구해 보자.
select 과목.*, 수강.학번 from 과목, 수강 where 과목.과목번호 = 수강.과목번호;
select * from 과목 natural join 수강;

-- 학생, 과목, 수강 테이블을 모두 자연 조인하여 누가 어떤 과목을 듣는지 모두 나타내자.(학번, 이름, 과목번호, 과목이름이 나와야 함)
select 학생.학번, 학생.이름, 과목.과목번호, 과목.과목이름 from 학생, 수강, 과목 where 학생.학번 = 수강.학번 and 과목.과목번호 = 수강.과목번호;

-- 데이터베이스를 수강하는 학생들의 이름을 구해 보자.
select 학생.이름 from 학생, 수강, 과목 where 학생.학번 = 수강.학번 and 과목.과목번호 = 수강.과목번호 and 과목.과목이름 = '데이터베이스';

 

---

실습 문제

--1번: 회원번호가 ‘T006’인 회원의 모든 정보를 구해 보자.
select * from 회원 where 회원번호='T006';

--2번: 주문이 한 번 이상 있었던 상품의 상품번호를 모두 구하는데 중복이 안 되게 하자.
select distinct 상품번호 from 주문상세내역;

--3번: 서울에 사는 회원들의 성명을 모두 구해 보자.
select 성명 from 회원 where 주소 like '서울%';

--4번: 서울에 살지 않는 회원들의 성명을 모두 구해 보자.
select 성명 from 회원 where not 주소 like '서울%';

--5번: 서울에 살면서 성별이 남자인 회원들의 성명을 모두 구해 보자.
select 성명 from 회원 where 주소 like '서울%' and 성별='남';

--6번: 이 쇼핑몰에서 판매하는 마우스들의 상품명을 모두 구해 보자.
select 상품명 from 상품 where 상품명 like '%마우스%';

--7번: 이 쇼핑몰에서 판매하는 모든 상품의 상품명을 단가가 큰 것부터 작은 것의 순서로 구해 보자.
select 상품명 from 상품 order by 단가 desc;

--8번: 단가가 5000원 이상 15000원 이하인 상품들의 상품번호와 상품명을 모두 구해 보자.
select 상품번호, 상품명 from 상품 where 단가 >=5000 and 단가 <=15000;

--9번: 회원 테이블과 주문 테이블을 자연 조인한 결과를 구해 보자.
select * from 회원, 주문 where 회원.회원번호 = 주문.회원번호;
select * from 회원 natural join 주문;

--10번: 주문 테이블과 주문상세내역 테이블을 자연 조인한 결과를 구해 보자.
select * from 주문, 주문상세내역 where 주문.주문번호 = 주문상세내역.주문번호;
select * from 주문 natural join 주문상세내역;

--11번: 상품 테이블과 주문 테이블과 주문상세내역 테이블을 자연 조인한 결과를 구해 보자.
select * from 상품, 주문, 주문상세내역 where 상품.상품번호 = 주문상세내역.상품번호 and 주문.주문번호 = 주문상세내역.주문번호;
select * from 상품 natural join 주문상세내역 natural join 주문;

--12번: 상품, 주문, 주문상세내역 테이블을 모두 자연 조인하여 주문상세내역별로 주문액(단가*수량)을 구해 보자.
select 주문상세번호, 단가 * 수량 as 주문액  from 상품, 주문, 주문상세내역 where 상품.상품번호 = 주문상세내역.상품번호 and 주문.주문번호 = 주문상세내역.주문번호;
select 주문상세번호, 단가 * 수량 as 주문액 from 상품 natural join 주문상세내역 natural join 주문;