SQL 예제 정리 01. 데이터 검색

학생, 과목, 수강신청 테이블 생성하여 테스트함. 

db환경 : mariadb

db관리 툴 : mariadb 사용시에 같이 설치된 heidiSql

 

1. 테이블 생성 구문

(1) 과목 테이블 생성

CREATE TABLE `course` (
	`no` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
	`name` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`credit` INT(11) NULL DEFAULT NULL,
	`dept` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`pr_name` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	PRIMARY KEY (`no`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

(2) 학생 테이블 

CREATE TABLE `student` (
	`no` INT(11) NOT NULL,
	`name` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`year` INT(11) NULL DEFAULT NULL,
	`dept` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	PRIMARY KEY (`no`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

(3) 수강신청 테이블

CREATE TABLE `enrol` (
	`sno` INT(11) NULL DEFAULT NULL,
	`cno` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`grade` CHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`midterm` INT(11) NULL DEFAULT NULL,
	`final` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

2. 테이블에 데이터 삽입

(1) 과목 테이블에 데이터 삽입

INSERT INTO `course` (`no`, `name`, `credit`, `dept`, `pr_name`) VALUES ('C123', '프로그래밍', 3, '컴퓨터', '김성국');
INSERT INTO `course` (`no`, `name`, `credit`, `dept`, `pr_name`) VALUES ('C312', '자료구조', 3, '컴퓨터', '황수관');
INSERT INTO `course` (`no`, `name`, `credit`, `dept`, `pr_name`) VALUES ('C324', '파일구조', 3, '컴퓨터', '이규찬');
INSERT INTO `course` (`no`, `name`, `credit`, `dept`, `pr_name`) VALUES ('C413', '데이터베이스', 3, '컴퓨터', '이일로');
INSERT INTO `course` (`no`, `name`, `credit`, `dept`, `pr_name`) VALUES ('E412', '반도체', 3, '전자', '홍봉진');

(2) 학생 테이블에 데이터 삽입

INSERT INTO `student` (`no`, `name`, `year`, `dept`) VALUES (100, '나수영', 4, '컴퓨터');
INSERT INTO `student` (`no`, `name`, `year`, `dept`) VALUES (200, '이찬수', 3, '전기');
INSERT INTO `student` (`no`, `name`, `year`, `dept`) VALUES (300, '정기태', 1, '컴퓨터');
INSERT INTO `student` (`no`, `name`, `year`, `dept`) VALUES (400, '송병길', 4, '컴퓨터');
INSERT INTO `student` (`no`, `name`, `year`, `dept`) VALUES (500, '박종화', 2, '산공');

(3) 수강신청 테이블에 데이터 삽입

INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (100, 'C413', 'A', 90, 95);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (100, 'E412', 'A', 95, 95);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (200, 'C123', 'B', 85, 80);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (300, 'C312', 'A', 90, 95);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (300, 'C324', 'C', 75, 75);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (300, 'C413', 'A', 95, 90);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (400, 'C312', 'A', 90, 95);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (400, 'C324', 'A', 95, 90);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (400, 'C413', 'B', 80, 85);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (400, 'E412', 'C', 65, 75);
INSERT INTO `enrol` (`sno`, `cno`, `grade`, `midterm`, `final`) VALUES (500, 'C312', 'B', 85, 80);

3. 예제

(1) 검색 결과에 중복 레코드 제거

- 학생 테이블에 어떤 학과 들이 있는지 검색

[질의문]

SELECT  DISTINCT student.dept FROM student

검색 결과 레코드에 중복을 제거하기 위해 distinct 명세

[결과]

 

(2) 테이블의 열 전부를 검색하는 경우

- 학생 테이블 전부를 검색하라

[질의문]

SELECT * FROM student;

* : asterisk

[결과]

(3) 조건 검색

- 학생 테이블에서 학과(dept)가 '컴퓨터'이고 학년이 4인 학생의 학번과 이름을 검색하라

[질의문]

SELECT student.no, student.name FROM student WHERE student.dept='컴퓨터' AND student.year=4

where 절에 나오는 조건식에는 비교연산자와 불리언 연산자 and, or, not을 사용할수 있으며 필요한 경우 괄호 사용 가능. 연산자 대신 키워드로도 표현 가능하다. year>=3 and year <=4  대신 year between 3 and 4로 표현 가능

[결과]

 

(4) 순서를 명세하는 검색

- 수강신청 테이블에서 중간성적이 90점 이상인 학생의 학번과 과목 번호를 검색하되 학번에 대해서는 내림차순으로, 그리고 같은 학번에 대해서는 과목 번호의 오름차순으로 검색하라

[질의문]

SELECT enrol.sno, enrol.cno 
FROM enrol
WHERE enrol.midterm>=90
ORDER BY enrol.sno DESC, enrol.cno asc

오름차순 asc, 내림차순 desc

처음나온 sno는 1차정렬, 두번째 나온 cno는 2차정렬

2차 정렬은 1차 정렬 범위 내에서 정렬 시키는 것을 말함

[결과]

(5) 산술식, 문자스트링, 새로운 열 이름이 명세된 검색

- 수강신청 테이블에서 과목번호가 'C312'인 중간 성적에 3점을 더한 점수를 '학번', '중간성적='이란 텍스트 내용을 '시험', 그리고 '점수'라는 열 이름으로 검색하라

[질의문]

SELECT enrol.sno AS '학번', '중간성적=' AS '시험', enrol.midterm AS '점수' 
FROM enrol
WHERE enrol.cno = 'C312'

AS~ : 별명을 지정한 것. 생략가능

[결과]

(6) 복수 테이블로부터의 검색

-과목 번호 'C413'에 등록한 학생의 이름, 학과, 성적을 검색하라

[질의문]

SELECT s.name, s.dept, e.grade 
FROM student s, enrol e
WHERE s.no = e.sno AND e.cno='C413'

S, E는 투플 변수(range variable)임. 

조인 질의문에서 어떤 조건으로 조인을 하느냐를 표현하는 것으로 조인 조건 또는 조인 프레디팃(Join predicate)이라한다. 

다음과 같이도 위 질의문을 표현할 수 있다.

SELECT student.name, student.dept, enrol.grade 
FROM student join enrol on(student.`no` = enrol.sno)
WHERE enrol.cno='C413'

SELECT student.name, student.dept, enrol.grade
FROM student join enrol USING(sno) //student 테이블의 NO가 sno로 변경되어야됨. 컬럼명이 다름.
WHERE enrol.cno='C413'

SELECT student.name, student.dept, enrol.grade
FROM student NATURAL JOIN enrol
WHERE enrol.cno='C413'

[결과]

(7) 자기 자신의 테이블에 조인하는 검색

- 같은 학과 학생들의 학번을 쌍으로 검색하라. 단, 첫번째 학번은 두번째 학번보다 작게 하라

[질의문]

SELECT s1.no AS S1학번, s2.no AS s2학번
FROM student s1, student s2
WHERE s1.dept = s2.dept 
AND	s1.`no` < s2.`no`

[결과]

(8) 집계 함수(Aggregate function or 열함수(column function))를 이용한 검색

- 학생 테이블의 학생 수가 얼마인가를 검색하라

[질의문]

SELECT COUNT(*) as 학생수
FROM student;

count, sum, avg, max, min이 있음

[결과]

- 학번이 300인 학생이 등록한 과목은 몇개인가?

[질의문]

SELECT COUNT(DISTINCT enrol.cno)
FROM enrol
WHERE enrol.sno=300

[결과]

- 과목 'C413'에 대한 중간 성적의 평균은 얼마인가?

[질의문]

SELECT AVG(enrol.midterm) AS 중간평균
FROM enrol
WHERE enrol.cno='C413'

avg 함수 결과 값의 타입은 해당 열의 데이타 타입과 같음

[결과]

(9) GROUP BY를 이용한 검색

- 과목별 기말 성적의 평균을 검색하라

[질의문]

SELECT cno, AVG(enrol.final) AS 기말평균
FROM enrol 
GROUP BY enrol.cno

group by는 논리적으로 from 절에 있는 테이블을 group by 절에 명세된 열의 값에 따라 그룹으로 분할한다.

[결과]

 

(10) HAVING을 사용한 검색

- 3명 이상 등록한 과목의 기말 평균 성적을 검색하라

[질의문]

SELECT cno, AVG(enrol.final) AS 기말평균
FROM enrol
GROUP BY enrol.cno
HAVING COUNT(*) >= 3

HAVING은 각 그룹의 구성 요건을 명세함. 만일 group by가 생략되면 테이블 전체를 하나의 그룹으로 취급함.

[결과]

(11) 부속 질의문(Subquery)을 사용한 검색

- 과목 번호 'C413'에 등록한 학생이름을 검색하라

SELECT NAME 
FROM student
WHERE no IN(
	SELECT sno 
	FROM enrol 
	WHERE cno = 'C413'
	)

subquery는 다른 질의문에 중첩(nested)되어 사용되는 검색문. 

select-from-where-group by-having의 형태를 취한다.

부속 질의문을 포함하고 있는 질의문을 중첩 질의문(nested query)라고 함. 

위의 질의문은 다음과 같음

SELECT NAME 
FROM student
WHERE no IN(100,200,300)

SELECT s.NAME 
FROM student s, enrol e
WHERE s.`no` = e.sno 
AND e.cno='C413'

- 과목번호 'C413'에 등록하지 않은 학생의 이름을 검색하라

[질의문]

SELECT NAME 
FROM student
WHERE no NOT IN(
	SELECT sno 
	FROM enrol 
	WHERE cno = 'C413'
	)

[결과]

 

- 학생 '정기태'와 같은 학과에 속하는 학생의 이름과 학과를 검색하라

[질의문]

SELECT NAME, dept 
FROM student
WHERE dept IN(
	SELECT dept 
	FROM student 
	WHERE name= '정기태'
	)

[결과]

 

- 수강신청 테이블에서 학번이 500인 학생의 모든 기말 성적보다 좋은 학기말 성적을 받은 학생의 학번과 과목번호를 검색

[질의문]

SELECT sno, cno
FROM enrol
WHERE final > ALL (
	SELECT final 
	FROM enrol 
	WHERE sno=500
	)

이 부속 질의문은 또한 다음과 같이 ALL, ANY, SOME과 같은 키워드와 함께 조건식에서도 사용할 수 있음

[결과]

(12) LIKE를 사용하는 검색

- 과목번호가 C로 시작하는 과목의 과목 번호와 과목이름을 검색하라

[질의문]

SELECT NO, name
FROM course
WHERE NO LIKE 'C%'

% : 서브 스트링 패턴을 명세

위의 C%는 C로 시작하기만 하면 어떤 문자 스트링이든 상관없다는 뜻

LIKE 'S_ _ _ ' : S로 시작되는 세문자 스트링

LIKE '%S_ _ ' : 스트링 끝에서 세번째가 'S'인 스트링

LIKE '%S%' : S가 포함된 스트링

[결과]

(13) NULL을 사용한 검색

- 학생 테이블에 학번이 600, 이름 '김길동', 학과가 NULL인 투플이 있다고 가정.

-> insert문 

INSERT INTO `student` (`no`, `name`, `year`, `dept`) VALUES (600, '김길동', 2, NULL);

- 학과가 NULL인 학생의 학번과 이름을 검색

[질의문]

SELECT NO, name
FROM student
WHERE dept IS null

열이름 IS [NOT] NULL의 형식만 허용됨.

열이름 = NULL의 형식은 불법적인 것으로 허용되지 않는다.

null과의 어떠한 비교 연산자든 모두 거짓임

 

NULL : 누락정보로서 (missing information) 값은 있지만 모르는 값(Unknown value), 해당되지 않는 값(unapplicable value) 또는 의도적으로 유보한값(withheld value)을 나타냄. 

Null은 참도 아니고 거짓도 아닌 미정(unknwon)이 된다.

[결과]

(14) EXIST를 사용하는 검색

- 과목 'C413'에 등록한 학생의 이름을 검색하라

[질의문]

SELECT name
FROM student
WHERE EXISTS
	(SELECT *
	FROM enrol
	WHERE enrol.sno = student.`no`
	AND enrol.cno = 'C413'
	)

EXIST : 존재 정량자(existential quantifier). exists 다음에 나오는 검색문의 실행 결과로 검색된 투플이 존재하는가를 검사함. 이 부속질의문은 exists(select*from)을 실행한뒤 결과가 공집합이 아니면 참이되고, 공집하이면 거짓이 된다.

[결과]

-과목 'C413'에 등록하지 않은 학생의 이름을 검색

[질의문]

SELECT name
FROM student
WHERE NOT EXISTS
	(SELECT *
	FROM enrol
	WHERE enrol.sno = student.`no`
	AND enrol.cno = 'C413'
	)

[결과]

(15) UNION이 관련된 검색

- 3학년이거나 또는 과목 'C324'에 등록한 학생의 학번을 검색하라

[질의문]

SELECT no
FROM student
WHERE year=3 
UNION
	SELECT sno
	FROM enrol
	WHERE cno='C324'

UNION : 일반 집합론의 합집합과 같다. 결과 테이블에서 중복되는 투플은 제거된다. intersection(교집합), except(차집합) 연산이있다.