DBeaver

통합 쿼리툴

SQL 표준 문법이 통일되어 있어 공통으로 적용할 수 있음은 참 다행스러운 일이다. 그러나 DBMS마다 공식적으로 사용하는 쿼리툴이 제각각이어서 이것 저것 바꿔 가며 쓰는 사람에게는 혼란스럽고 번거로운 일이 많다. 여러 창 사이를 왔다 갔다하는 것도 귀찮고 단축키도 제각각이라 실수할 경우가 많다.

다행히 쿼리툴은 DBMS에 종속적이지 않아 바꿔 가며 쓸 수 있고 모든 DBMS를 다 지원하는 통합 쿼리툴도 많다. 취향에 따라 골라 쓸 수 있는데 학습용 무료툴로는 DBeaver가 무난하다. 멀티 플랫폼을 지원하며 안정성과 편의성이 뛰어나다.

어떤 쿼리툴을 쓸 것인가는 개인의 취향이며 선택 사항일 뿐이다. 여기서는 DBeaver를 소개하고 설치하는 방법을 설명하되 오라클을 주로 쓴다면 SQL Developer가 편하고 SQL Server로 실습을 계속 진행한다면 SSMS가 무난하다. 여러 DBMS를 쓴다면 통합툴을 써 보고 익숙해지면 다른 툴도 경험해 보는 것이 좋다.

DBeaver의 홈 페이지인 https://dbeaver.io를 방문해 보자. 꾸준히 업데이트하고 있는데 2020년 7월 버전은 7.1.1이며 2021년 7.3.1이 최신 버전이다. Windows 64 bit (installer + JRE) 버전을 받아 디폴트 옵션으로 설치하면 된다. 최초 실행시 연결 대화상자가 나타나는데 일단 닫고 옵션부터 적당히 조정하자.

디폴트 옵션이 무난하지만 SQL 자동 완성 페이지의 Insert table aliases(in FROM clause) 옵션은 끄자. 테이블에 별명을 자동으로 달아주는 기능인데 초보자에게 헷갈리는 부작용이 있다. 나머지 옵션은 필요에 따라 조정해 쓰도록 하자. 글꼴 크기나 모양 정도는 취향에 맞게 조정해 두는 것이 좋다.

MariaDB 연결

DBeaver로 MariaDB에 연결해 보자. MariaDB는 기본 그래픽 쿼리툴이 부실해 통합 쿼리툴로 사용하는 것이 좋다. 메뉴에서 데이터베이스/새 테이터베이스 연결 항목을 선택하거나 왼쪽 툴바의 버튼을 누르면 DB 선택 대화상자가 나타난다.

지원하는 DBMS의 목록을 보여주는데 보다시피 웬만한건 다 지원한다. 종류가 얼마나 많은지 카테고리별로 나누어 놓았다. Popular 페이지의 MariaDB를 선택한 후 다음 버튼을 누르면 접속 정보를 묻는 대화상자가 나타난다.

서버 주소와 포트는 이미 입력되어 있어 이대로 받아들이면 된다. IP 주소와 포트 번호를 적절히 지정하면 원격지의 서버에도 접속할 수 있다. Database 이름은 Study로 지정하고 root의 암호를 입력한다. 잘 접속되는지 Test Connection 버튼을 눌러 보자.

접속에 필요한 정보만 제공하면 즉시 연결되며 왼쪽의 DB 탐색기 목록에 MariaDB가 나타난다. 이 항목을 더블클릭하여 펼치면 바로 접속되지만 경우에 따라서는 디바이스 드라이버를 다운로드 받아 설치하기도 한다. 필요한 드라이버를 자동으로 찾아 설치하도록 되어 있어 편리하다.

Study DB를 선택해 놓고 메뉴의 SQL 편집기/SQL 편집기 항목을 선택하거나 단축키 F3을 누르면 중앙에 쿼리문을 입력하는 편집기가 열린다. 여기에 쿼리를 입력한 후 실행(단축키 Ctrl + Enter)를 누르면 즉시 실행하여 결과를 보여준다.

여러 개의 명령을 입력해 놓았을 때는 블록으로 선택한 후 Ctrl+Enter를 누른다. 여러줄의 쿼리를 한꺼번에 실행할 때는 단축키 Alt+X를 누른다. 여러 줄 실행과 한 줄 실행의 단축키가 구분되어 있어 굳이 블록을 선택하지 않아도 되는 이점이 있다.

SQL Server 연결

DBeaver로 SQL Server에 연결하려면 SQL Server의 설정을 약간 바꿔야 한다. 범용 접속툴인 DBeaver는 JDBC 드라이버를 사용하며 TCP/IP 프로토콜을 통해 DB 서버에 접속한다. 하지만 로컬에 설치한 SQL Server는 속도를 위해 공유 메모리를 사용하도록 되어 있어 통신 방법이 맞지 않다. SQL Server 설정을 변경하여 TCP/IP 접속을 사용하도록 해야 한다. SQL Server 구성 관리자를 실행한다.

왼쪽 노드에서 SQLEXPRESS에 대한 프로토콜 항목을 선택하면 현재 사용하는 연결 프로토콜이 나타난다. 디폴트로 공유 메모리를 사용하는데 TCP/IP도 허용한다. TCP/IP의 팝업 메뉴에서 속성을 선택하고 IP 주소 탭 제일 아래쪽의 IPAll의 TCP 포트를 1433으로 지정한다.

그리고 TCP/IP의 팝업 메뉴를 열어 "사용"으로 설정한다. 상태가 사용으로 바뀌면서 재시작하라는 안내 메시지가 나타난다. SQL Server 서비스 노드를 선택하면 서비스가 실행 중임이 표시되어 있는데 변경된 프로토콜을 적용하기 위해 재시작한다.

이제 TCP/IP로도 연결할 수 있어 DBeaver에서 접속 가능하다. DBeaver의 연결 대화상자에서 SQL Server를 선택한다. MS에서 제공하는 드라이버보다는 jTDS 드라이버가 호환성이 더 좋다. 드라이버를 선택하면 접속 관련 정보를 묻는 대화상자가 나타난다.

Host는 디폴트인 localhost를 선택하고 Database는 앞에서 만들어 두었던 Study를 입력한다. 인증 방식은 Windows Authentication을 선택하고 완료 버튼을 누른다. 필요하면 디바이스 드라이버를 다운로드하여 설치한다.

오라클 연결

계속해서 오라클에도 접속해 보자. 새 데이터베이스 연결 버튼을 클릭하고 오라클을 선택한 후 접속 정보를 입력한다. localhost의 Study DB에 접속하되 사용자 이름은 SYSTEM으로 지정하고 비밀번호를 입력한다.

필요시 디바이스 드라이버를 받는데 오라클의 정책상 로그인도 해야 한다. 다운로드 받은 후 Add jar 버튼을 눌러 위치를 알려 주면 된다. 이런 식으로 원하는 DBMS의 접속 정보만 제공하면 하나의 쿼리툴로 여러 서버에 동시에 접속할 수 있다. DB 탐색기에서 서버의 이름만 적당히 편집해 두면 헷갈리지 않는다. 다음은 세 개의 서버에 동시에 접속한 모습이다.

각각의 탭에 서버별로 쿼리창을 열어 놓고 작업할 수 있다. DBMS의 종류나 위치에 상관없이 한꺼번에 접속해 놓고 명령을 내릴 수 있어 편리하다. DBMS마다 쿼리가 조금씩 다른 경우 동시에 실행해 보며 차이점을 학습할 수 있어 초보자에게 특히 유용하다.

DBeaver 외에도 쓸만한 통합 쿼리툴이 많이 발표되어 있으며 약간의 비용을 지불하면 일일이 수작업으로 해야 할 일을 자동으로 처리해 주는 고급툴도 많다. SQL 문법을 마스터하는 것만큼이나 툴을 자유자재로 잘 써 먹는 것도 생산성 향상에 큰 도움이 된다. 지속적으로 관심을 가지고 여러 툴을 사용해 보자.

MariaDB SQL

변수

MariaDB도 다른 DBMS와 마찬가지로 절차적인 프로그래밍 기능을 제공한다. TSQL이나 PL/SQL에 비해 도입이 늦어 미완성인 부분이 약간 있지만 사용 목적이나 용도는 유사해 문법의 차이점만 숙지하면 금방 익숙해질 수 있다. 본문에서 개념은 이미 다 실습했으므로 MariaDB 특유의 문법만 요약하기로 한다.

MariaDB의 변수는 두 가지 종류가 있다. @으로 시작하면 세션내에서 값이 유지되는 전역변수며 별도의 선언없이 바로 사용할 수 있다. SET 문으로 선언 및 초기화하며 초기값에 따라 타입이 자동으로 결정된다. 다음 명령은 정수형의 @score 변수를 123으로 선언 및 초기화한 후 출력한다.

SET @score = 123;

SELECT @score;

PRINT나 PUT_LINE 같은 출력 명령이 따로 없어 SELECT 문을 대신 사용한다. @이 붙은 변수는 전역이며 두 문장은 독립적이어서 꼭 한꺼번에 실행하지 않아도 상관없다. SET 문에 의해 선언 및 초기화된 @score는 세션 내에서 계속 유효하며 언제든지 다른 값을 대입하거나 출력할 수 있다.

SET 문으로 대입할 때는 = 연산자를 사용하는데 비해 쿼리문에서는 = 이 비교 연산자여서 필드값을 변수에 대입할 때 := 연산자를 사용한다. 서브쿼리를 두 문장으로 분리할 때 앞쪽 쿼리 결과를 변수에 저장해 두고 다음 쿼리문에서 사용한다.

SELECT @maxnum := MAX(num) FROM tItem;

SELECT item FROM tItem WHERE num = @maxnum;

MAX(num) 집계 함수의 결과가 @maxnum 변수에 대입된다. 또는 MAX(num) INTO @maxnum 구문으로도 대입할 수 있다. SQL Server 방식과 오라클 방식을 다 지원하는 셈이다. 이 값을 다음 쿼리에 사용하여 재고가 가장 많은 상품을 조사하였다.

변수는 중간 결과를 잠시 저장하거나 계산용으로 쓰는 것이어서 쿼리창에서 직접 사용하기보다는 주로 프로시저 내부에서 사용한다. 따라서 변수나 제어문을 제대로 실습해 보려면 프로시저 정의문부터 알아야 한다. 프로시저의 블록을 명확히 구분하기 위한 DELIMITER 선언문이 먼저 온다.

DELIMITER $$

CREATE [OR REPLACE] PROCEDURE 프로시저 이름()

BEGIN

여기에 코드를 작성한다.

END $$

DELIMITER ;

임의의 기호로 구분자를 정의해 놓고 이 구분자가 다시 나타날 때까지 프로시저 본체로 해석한다. 프로시저 내부의 명령문이 ; 으로 끝나다 보니 ; 만으로 프로시저의 끝을 명확히 판별하기 어렵다. 그래서 $$ 같은 특수한 기호를 구분자로 정의하고 END $$가 나올 때까지 프로시저의 본체로 정의한다.

프로시저 정의가 끝난 후 ; 을 구분자로 다시 지정해야 일반적인 쿼리문을 평소대로 실행할 수 있다. 프로시저나 함수, 트리거는 항상 구분자 정의문과 END $$ 그리고 DELIMITER ; 안에 본체를 작성한다. 프로시저 본체에서 변수를 선언하고 사용하는 코드는 다음과 같다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_proc()

BEGIN

DECLARE score INT DEFAULT 123;

SELECT score;

SET score = score + 1;

SELECT score;

END $$

DELIMITER ;

프로시저내에 선언하는 변수는 해당 블록에서만 사용하는 지역변수다. 전역변수와는 달리 @을 붙이지 않으며 DECLARE로 선언한 후 사용한다. 선언시에 타입을 지정하고 DEFAULT 키워드로 초기값을 지정한다. score를 123으로 선언 및 초기화하여 출력하고 값을 1 증가시킨 후 다시 출력했다.

위 코드는 프로시저를 정의만 할 뿐이어서 별다른 출력은 없다. 프로시저를 실행할 때는 CALL 명령으로 호출하며 괄호 안에 인수를 전달한다. 인수가 없을 때는 괄호를 생략할 수 있지만 가급적 빈 괄호를 적는 것이 좋다. 다음 명령에 의해 SP_proc 프로시저가 호출되며 score 변수의 초기값인 123과 1 증가한 124가 출력된다.

CALL SP_proc();

변수 뿐만 아니라 조건문이나 제어문도 모두 프로시저 안에 이런 형식으로 작성한다. 구분자는 다른 DBMS에는 없는 제약이며 보기도 좋지 않고 쓰기도 불편하다. 장문의 코드 영역을 구분하기 위한 장치라는 합당한 이유가 있지만 다른 DBMS에 비해 구문 분석기의 수준이 낮은 것이다.

제어문

IF 문은 조건을 판별하여 실행할 명령을 선택한다. 매번 새로운 프로시저를 일일이 만들면 번거로우니 앞에서 만든 proc 프로시저를 바꿔 가며 테스트해 보자. 생성문이 CREATE OR REPLACE여서 같은 이름을 계속 사용해도 무방하다. 다음 프로시저는 score가 12인지 아닌지 판별한다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_proc()

BEGIN

DECLARE score INT;

SET score = 12;

IF score = 12 THEN

SELECT '12입니다';

ELSE

SELECT '12가 아닙니다.';

END IF;

END $$

DELIMITER ;

THEN에 걸리는 명령이 여러 개이면 BEGIN END 블록으로 복문을 구성한다. 거짓일 때의 처리를 할 필요가 없으면 ELSE를 생략하고 여러 개의 조건을 각각 점검하려면 ELSEIF 문을 계속 나열한다. 연결할 조건이 많을 때는 CASE 문을 사용하는 것이 좋다. 다음 프로시저는 점수에 따른 학점을 계산한다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_proc()

BEGIN

DECLARE score INT DEFAULT 86;

CASE

WHEN score > 95 THEN SELECT 'A+';

WHEN score > 90 THEN SELECT 'A';

WHEN score > 85 THEN SELECT 'B+';

WHEN score > 80 THEN SELECT 'B';

ELSE SELECT 'C';

END CASE;

END $$

DELIMITER ;

CASE 블록의 WHEN 절에 조건을 쓰고 이 조건을 만족할 때 실행할 명령을 THEN 절에 적는다. 매 조건마다 실행할 명령이 같다면 같은 명령을 반복하는 것보다 값만 선택하는 것이 효율적이다. 값 하나를 선택할 때는 THEN 다음에 값만 적고 그 결과를 다른 변수에 대입한다. 다음 코드는 똑같은 동작을 하되 grade에 학점을 선택해 놓고 최종적으로 한 번만 출력한다는 점이 다르다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_proc()

BEGIN

DECLARE score INT DEFAULT 86;

DECLARE grade CHAR(2);

SET grade = CASE

WHEN score > 95 THEN 'A+'

WHEN score > 90 THEN 'A'

WHEN score > 85 THEN 'B+'

WHEN score > 80 THEN 'B'

ELSE 'C'

END;

SELECT grade;

END $$

DELIMITER ;

SELECT 명령이 반복적으로 나타나지 않아 간결하다. THEN 다음에 명령이 아닌 값이 올 때는 CASE 문 전체가 하나의 값이므로 THEN의 끝에 세미콜론을 붙이지 않는다. 그리고 CASE의 끝이 END CASE가 아닌 END라는 점도 다르다.

반복문은 세 가지가 있다. WHILE 문은 조건이 참인 동안 반복한다. REPEAT UNTIL 문은 조건이 만족할 때까지 즉, 조건이 거짓인 동안 반복한다. 다음 왼쪽 코드는 num이 100보다 작은동안, 오른쪽 코드는 num이 100보다 커질때까지 sum에 num을 누적하여 1~100까지의 합계를 구한다.

DELIMITER $$ CREATE OR REPLACE PROCEDURE SP_proc() BEGIN DECLARE num INT DEFAULT 1; DECLARE sum INT DEFAULT 0; WHILE (num <= 100) DO SET sum = sum + num; SET num = num + 1; END WHILE; SELECT sum; END $$ DELIMITER ;

DELIMITER $$ CREATE OR REPLACE PROCEDURE SP_proc() BEGIN DECLARE num INT DEFAULT 1; DECLARE sum INT DEFAULT 0; REPEAT SET sum = sum + num; SET num = num + 1; UNTIL num > 100 END REPEAT; SELECT sum; END $$ DELIMITER ;

조건과 형식만 다를 뿐 결국 같은 반복문이다. 루프내에서 LEAVE 명령은 해당 루프를 탈출하며 ITERATE는 루프의 선두로 돌아간다. 각각 다른 언어의 break, continue에 해당한다. LOOP 문은 조건을 명시하지 않고 무한히 반복하며 루프 내부에서 조건을 점검하여 적당한 시점에 탈출한다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_proc()

BEGIN

DECLARE num INT DEFAULT 1;

DECLARE sum INT DEFAULT 0;

sumloop: LOOP

SET sum = sum + num;

SET num = num + 1;

IF num > 100 THEN

LEAVE sumloop;

END IF;

END LOOP;

SELECT sum;

END $$

DELIMITER ;

루프에 sumloop: 라는 이름을 붙여 두고 num이 100을 초과하면 LEAVE 명령으로 루프를 탈출한다. 반복 횟수를 미리 알 수 없을 때는 무한 루프를 구성하고 루프 내부에서 탈출 여부를 결정하는 것이 편리하다. 일정 범위를 반복하는 FOR 문은 지원하지 않는다.

프로시저

프로시저는 일련의 코드에 대해 이름을 붙여 놓은 것이다. 제어문 실습을 위해 인수가 없는 간단한 프로시저만 사용했는데 호출원으로부터 인수를 받을 수도 있다. 인수는 프로시저명 다음의 괄호 안에 목록을 밝히되 입력용은 IN 키워드를 붙이고 출력용은 OUT 키워드를 붙인다.

인수의 개수나 타입에는 별 제약이 없다. 다음 프로시저는 회원 이름을 p_member 인수로 전달받아 이 회원의 예치금을 1000원 증가시킨다. 인수에 @기호가 없다 보니 필드 이름과는 다른 이름을 사용해야 하는데 보통 접두 p_를 붙인다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_GrantBonus(IN p_member CHAR(20))

BEGIN

UPDATE tMember SET money = money + 1000 WHERE member = p_member;

END $$

DELIMITER ;

본체에서 UPDATE 쿼리를 실행하되 WHERE 조건문에서 회원 이름과 인수로 전달받은 이름을 비교한다. CALL SP_GrantBonus('춘향');으로 호출하면 춘향이의 예치금이 1000원 늘어난다.

반복적으로 호출할 수 있고 인수를 바꿔 가며 다른 회원에 대해서도 재사용할 수 있어 편리하다. 출력용 인수는 OUT 키워드를 붙이며 본체에서 이 변수에 계산 결과를 대입한다. 다음 프로시저는 회원수를 구해 출력용 인수에 대입한다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_GetMemberNum(OUT o_member_num INT)

BEGIN

SELECT COUNT(*) INTO o_member_num FROM tMember;

END $$

DELIMITER ;

COUNT(*)으로 구한 레코드 개수를 출력용 인수 o_member_num에 대입하였다. 호출원에서는 전역변수를 프로시저로 전달하고 이 값으로 결과값을 취한다.

CALL SP_GetMemberNum(@num);

SELECT @num;

@num 전역변수에 쿼리 실행 결과가 대입된다. 전역변수이므로 미리 선언해 둘 필요는 없으며 호출문의 인수 목록에 이름만 적어 두면 된다. 프로시저 호출 후 이 값을 출력하면 실행 결과를 알 수 있다.

스토어드 함수

함수는 프로시저에 비해 값을 리턴한다는 점이 다르다. 인수는 모두 입력용이며 출력용 인수는 사용할 수 없다. 함수 선언문에 RETURNS 구문으로 리턴 타입을 명시하며 본체에서 RETURN 명령으로 값을 리턴한다.

값 하나를 리턴하므로 함수 자체가 하나의 값으로 취급되며 따라서 함수 호출문을 SELECT나 다른 수식 내부에서 바로 사용할 수 있다. 다음 함수는 인수로 전달한 두 정수의 합계를 구해 리턴한다.

DELIMITER $$

CREATE OR REPLACE FUNCTION FN_AddInt(a INT, b INT) RETURNS INT

BEGIN

RETURN a + b;

END $$

DELIMITER ;

a, b 두 개의 정수형 인수를 받으며 정수값을 리턴한다. 본체에서 a 와 b의 합을 구해 RETURN 명령으로 반환한다. 호출원에서는 이 값을 곧바로 출력하거나 다른 연산에 사용할 수 있다.

SELECT FN_AddInt(2, 3);

FN_AddInt(2, 3)의 결과가 5와 같으며 정수가 들어갈 수 있는 모든 곳에 함수 호출문을 쓸 수 있다. 프로시저는 동작을 처리하는 데 비해 함수는 값을 계산하는 역할을 한다.

커서

커서는 결과셋의 한 지점을 가리키는 포인터이다. SQL Server나 오라클의 커서와 개념상 같고 정의, 오픈, 순회 방법도 유사하다. 다만 결과셋의 끝을 판별하는 방법이 독특하다. NOT FOUND 에러 발생시 특별한 변수를 설정하도록 등록해 놓고 LOOP 문으로 무한 루프를 돌다가 에러 발생시 탈출하는 형식이다. 다음 프로시저는 커서를 사용하여 tCity 테이블의 도시명을 순서대로 읽는다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE SP_dumpCity()

BEGIN

DECLARE city_name CHAR(10);

DECLARE eof INT DEFAULT 0;

DECLARE testcursor CURSOR FOR SELECT name FROM tCity;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = 1;

OPEN testcursor;

fetch_loop: LOOP

FETCH testcursor INTO city_name;

IF eof THEN

LEAVE fetch_loop;

END IF;

SELECT city_name;

END LOOP;

CLOSE testcursor;

END $$

DELIMITER ;

SELECT name FROM tCity 쿼리에 대해 커서를 생성하여 도시명을 순서대로 읽는 커서를 만든다. 끝 판별을 위해 NOT FOUND 에러 발생시 eof에 1을 대입하는 핸들러를 설치해 두고 커서를 연다. LOOP에서 FETCH 문으로 커서의 다음값을 읽되 eof가 1이 되면 탈출한다.

tCity의 도시명이 하나씩 출력된다. 어쨌거나 잘 순회하며 값도 잘 읽어 커서의 본분은 제대로 하는 셈이다. 그러나 결과셋의 끝을 만나는 상황을 예외로 처리하는 문법은 바람직하지 못한 구조이다. EOF는 불가항력적인 예외가 아닌 통상적으로 만나는 상황이므로 리턴값이나 상태값으로 처리하는 것이 옳다.

MariaDB 옹호자는 어쨌거나 돌아가면 되지 않느냐고 변호하겠지만 이런 어색한 문법을 보면 오픈소스가 상용 DBMS에 비해 한수 아래임을 인정하지 않을 수 없다. EOF 검출에 예외를 써 버리는 바람에 진짜 예외가 발생하는 상황과 구분할 수 없는 치명적인 약점이 있다.

트리거

MySQL은 버전 5부터 트리거를 지원하기 시작해 다른 DBMS에 비해 도입이 상당히 늦었으며 아직 기능적으로 미완성인 부분도 많다. 그 후손인 MariaDB도 물론이다. AFTER, BEFORE 트리거만 지원하며 INSTEAD OF 트리거는 아직 지원하지 않는다.

트리거 본체에서 NEW는 삽입 또는 변경한 데이터를 가지는 임시 테이블이며 OLD는 삭제 또는 변경전의 데이터를 가지는 임시 테이블이다. 트리거 본체에서 두 임시 테이블을 참조하면 어떤 데이터가 어떻게 바뀌었는지 알 수 있다. 다음 트리거는 tCity가 변경될 때 인구수의 변화를 보여 준다.

DELIMITER $$

CREATE OR REPLACE TRIGGER TR_Message

AFTER UPDATE ON tCity

FOR EACH ROW

BEGIN

SET @result = CONCAT(OLD.popu, '->', NEW.popu);

END $$

DELIMITER ;

tCity 테이블이 UPDATE되고 난 후에 OLD의 인구수와 NEW의 인구수를 조사하여 @result 전역변수에 대입해 둔다. 트리거가 잘 동작하는지 다음 쿼리로 tCity 테이블을 변경해 보자.

UPDATE tCity SET popu = popu + 1 WHERE name = '서울';

서울의 인구수를 1 증가시키면 트리거가 호출되고 @result에 변경 내역이 저장된다. 이 변수를 출력해 보면 tCity 테이블이 어떻게 바뀌었는지 알 수 있다.

SELECT @result;

트리거는 사용자가 호출하는 것이 아니라 DBMS가 일정한 조건이 될 때 자동으로 호출하는 코드이다. 따라서 본문에서는 출력 명령을 사용할 수 없으며 백그라운드에서 실행되는 트리거의 출력을 받아줄 대상도 없다. 그래서 @result 전역변수에 결과를 저장해 놓고 이 값을 출력하여 트리거의 동작을 확인하였다.

SQL 구문

기본 형식 정리

SQL은 구문이 복잡해서 다 외우기는 어려우며 자주 사용하지 않는 구문은 항상 헷갈린다. 필요할 때마다 검색해서 찾을 수 있지만 작업의 흐름이 끊어져 생산성이 떨어지며 잘못 검색하여 엉뚱한 실수를 하는 경우도 있다. 기본적인 SQL 구문은 의도적으로 외워 두는 것이 좋다.

SELECT field FROM table WHERE cond ORDER BY sort

OFFSET skip ROWS FETCH NEXT num ROWS ONLY

INSERT INTO table [(f1, f2, ...)] VALUES (v1, v2, ...)

INSERT INTO dest [(f1, f2, ...)] SELECT f1, f2, ... FROM src

CREATE TABLE dest AS SELECT f1, f2, ... FROM src

DELETE FROM table WHERE cond

UPDATE table SET f1=v1, f2=v2 WHERE cond

MERGE INTO dest USING src ON (cond) WHEN MATCHED THEN ... WHEN NOT MATCHED THEN ...

SELECT field FROM t1 [INNER] JOIN t2 ON cond;

CAST(field AS type)

CREATE VIEW name [(f1, f2, ...)] AS SELECT ...

WITH table [(f1, f2, ...)] AS SELECT ...

OVER(PARTITION BY grouping ORDER BY sort)

PIVOT(sum FOR field IN (v1, v2, ...))

특히 전치사의 위치나 종류가 헷갈리는데 필수 구문의 전치사를 누락하면 당장은 잘 실행되더라도 호환성이 떨어지므로 표준 구문의 전치사는 가급적 사용하는 버릇을 들이는 것이 좋다.

DBMS간 구문 비교

DBMS간의 기능 차이는 거의 없어 한쪽에서 구현 가능한 기능은 다른 쪽에서도 항상 구현 가능하다. 다만, 구문상의 차이가 있어 대체 기능을 찾아야 한다. 다음 도표는 세 DBMS의 구문적 차이를 간략하게 도표로 요약한다. 이 도표에서 f는 필드, n은 임의의 정수이다.

기능		오라클	SQL Server		MariaDB
계산식 출력		SELECT 식 FROM dual	SELECT 식		오라클, SQL Server 두 방식 모두 지원
오라클은 FROM 절이 필수이다.
n 행 조회		WHERE rownum <= n	TOP n [PERCENT]		LIMIT [s], n = LIMIT n OFFSET s
오라클은 조건을 건 후 제한하려면 서브쿼리가 필요하다.
부분행 조회		ORDER BY f OFFSET s ROWS FETCH NEXT n ROWS ONLY			미지원
오라클은 ORDER BY와 OFFSET을 모두 생략하여 정렬없이 처음부터 선택할 수 있으나 SQL Server는 모든 구문이 필수여서 생략할 수 없다.
자동 커밋		OFF	ON		ON
디폴트 설정만 다를 뿐 셋 다 자동 커밋 옵션을 변경할 수 있다.
확장 INSERT		미지원	지원		지원
INSERT INTO (필드목록) VALUES (....), (....), (....) 형식으로 여러 행을 한꺼번에 삽입한다.
조회 결과 테이블 생성		CREATE TABLE t AS SELECT ...	SELECT ... INTO t FROM...		오라클과 같음
데이터만 복사할 뿐 기본키나 제약 설정은 복사하지 않는다.
자동 증가열		시퀀스, GENERATED AS IDENTITY	시퀀스, IDENTITY(start, inc)		AUTO_INCREMENT
세 DBMS를 동시에 지원하는 방법은 없다. 시퀀스는 SQL Server도 지원하지만 디폴트 타입이 BIGINT이고 0이 아닌 음수 첫값에서 시작한다.
인라인뷰의 별명		필요할 때만 붙인다. AS는 쓸 수 없다.	사용하지 않아도 붙여야 한다.		SQL Server와 같음
호환성 확보를 위해 인라인뷰에 AS없이 이름만으로 별명을 붙인다. SELECT * FROM (SELECT ...) A
테이블 차집합		MINUS	EXCEPT		SQL Server와 같음
UNION 합집합, INTERSECT 교집합은 같지만 차집합만 키워드가 다르다.
MERGE		MERGE INTO t USING s ON (조건)	MERGE t USING s ON 조건 UPDATE FROM 지원		미지원
SQL Server는 INTO와 ON 절의 괄호가 옵션이며 생략할 수 있다. MERGE와 유사한 UPDATE FROM 구문도 지원한다.
ROLLUP		ROLLUP(f)	오라클, MariaDB 두 방식 모두 지원		f WITH ROLLUP
MariaDB는 CUBE를 지원하지 않는다.
PIVOT		PIVOT(... IN ('f1', 'f2', 'f3'))	PIVOT(... IN (f1, f2, f3)) pvt		미지원
IN절의 필드에 따옴표 사용 여부와 피봇 결과 테이블의 별명 지정 여부가 다르다.
문자열 연결	||			+		CONCAT
오라클은 숫자 덧셈과 문자열 연결 연산자가 구분되어 있어 타입 변환이 자동인데 비해 SQL Server는 + 연산자로 모두 수행하여 애매한 경우가 생기며 타입 변환을 수동으로 지정해야 한다.
문자열 길이	LENGTH			LEN		CHAR_LENGTH
문자의 수인가 바이트 수인가에 따라 함수를 잘 구분해야 한다.
부분 문자열 추출	SUNSTRING			SUBSTR		둘 다 지원
인수의 구조는 유사하다.
부분 문자열 검색	INSTR			CHARINDEX		POSITION
인수의 구조는 유사하다.
NULL 대체	NVL			ISNULL		IFNULL
함수명만 다르며 사용법은 같다.
오늘 날짜	SYSDATE			GETDATE()		NOW()
SYSDATE에는 괄호를 붙이지 않는다.

연습 문제 해설

1장

1.테이블

필드가 모여 하나의 엔터티를 표현하는 레코드가 되며 레코드의 집합이 테이블이다.

2.③

컬럼은 레코드를 구성하는 정보 하나이다. 레코드는 행 또는 튜플이라고도 부른다.

3.②

요구 사항만 전달하며 실행 과정은 DB 엔진이 판단하여 결정한다.

4.VARCHAR

고정 길이는 CHAR 타입을 쓰며 가변 길이는 VARCHAR 타입을 쓴다.

4장

1.SELECT name, depart, grade FROM tStaff;

필드 목록에 name, depart, grade 필드를 콤마로 구분하여 나열한다.

2.SELECT name AS "도시", popu AS "인구(만명)", region AS "지역" FROM tCity;

세 개의 필드를 리스트에 쓰고 AS 다음에 별명을 붙인다. AS는 생략 가능하며 도시, 지역은 특수문자나 공백이 없어 따옴표를 생략해도 상관없다. 그러나 인구는 괄호가 있어 따옴표를 생략할 수 없다.

3.SELECT 60*60*24*365 FROM dual;

초,분,시,일을 모두 곱한 수식을 SELECT로 출력한다. 별명은 꼭 붙이지 않아도 상관없으며 SQL Server와 MariaDB는 FROM dual도 생략 가능하다.

4.SELECT name FROM tCity WHERE popu < 10;

인구 필드인 popu를 10과 비교하는 조건식을 작성하되 미만이라고 했으므로 < 연산자로 비교한다. 10만 이하라면 <=로 비교한다. 이 조건에 맞는 도시의 이름만 출력하라고 했으므로 필드 목록에는 name만 지정했다. 홍천만 출력된다.

5.SELECT * FROM tCity WHERE region = '전라';

지역 필드인 region이 '전라'인 행의 모든 필드를 출력한다. 순천, 전주가 출력된다.

6.SELECT name FROM tStaff WHERE salary >= 400;

이상이라고 했으므로 salary 필드를 >= 연산자로 비교한다. 김유신, 신사임당, 장보고 세 명이 출력된다.

7.SELECT name FROM tStaff WHERE salary < 300 AND score >= 60;

월급이 300미만인 조건과 성취도가 60이상인 조건을 AND로 연결한다.

8.SELECT name FROM tStaff WHERE depart = '영업부' AND gender = '여';

depart가 영업부이고 gender가 여인 조건을 AND로 연결한다.

9.SELECT name FROM tStaff WHERE name LIKE '정%';

이름의 첫 글자가 '정'인지 점검한다.

10.SELECT name FROM tStaff WHERE name LIKE '%신%';

이름에 신이 포함된 모든 경우를 검색하며 앞뒤로 임의의 문자가 와도 상관없다.

11.SELECT * FROM tCity WHERE area BETWEEN 500 AND 1000;

또는 WHERE area >= 500 AND area <= 1000 조건으로도 검색할 수 있다.

12.SELECT * FROM tStaff WHERE salary BETWEEN 200 AND 299;

200만원대라면 300만원은 제외되어야 하므로 BETWEEN으로 검색할 때는 299까지만 포함한다. 이렇게 하면 월급이 299만 5천원인 직원이 제외되는 문제가 있지만 salary 필드의 단위가 만원이어서 안전하다. 300미만인 조건은 부등호를 사용하는 것이 더 정확하다.

SELECT * FROM tStaff WHERE salary >= 200 AND salary < 300;

13.SELECT * FROM tStaff WHERE depart IN ('총무부', '영업부');

IN 목록에 총무부와 영업부를 포함한다. 또는 두 조건을 각각 비교하여 OR로 연결한다.

SELECT * FROM tStaff WHERE depart ='총무부' OR depart = '영업부';

14.SELECT * FROM tStaff WHERE depart IN ('인사과', '영업부') AND grade = '대리';

IN 목록에 인사과와 영업부를 포함하고 grade가 대리인 조건을 하나 더 AND로 연결한다. 세 조건을 각각 연결할 때는 OR이 먼저 처리되도록 괄호를 감싸야 한다. 괄호가 없으면 AND의 우선순위가 더 높다.

SELECT * FROM tStaff WHERE (depart ='인사과' OR depart = '영업부') AND grade = '대리';

15.SELECT * FROM tStaff WHERE grade IN('차장', '부장', '이사') AND gender = '여';

차장급 이상인 차장, 부장, 이사를 IN 목록에 포함하고 gender가 여인 조건을 AND로 연결한다. 직급은 수치값이 아니어서 부등 비교할 수 없으며 더 높은 직급의 집합을 만든 후 IN 연산자로 비교해야 한다.

16.SELECT * FROM tStaff ORDER BY salary, score DESC;

1차 정렬 기준을 salary 오름차순으로 지정하고 2차 정렬 기준은 성취도에 대해 내림차순으로 지정한다.

17.SELECT * FROM tStaff WHERE depart = '영업부' ORDER BY joindate;

WHERE 절을 먼저 쓰고 ORDER BY를 뒤에 적음을 유의하자.

18.SELECT DISTINCT depart FROM tStaff WHERE joindate > '20200101';

DISTINCT 키워드를 빼면 총무부 2명, 인사과 1명의 신입 사원이 있었음을 알 수 있다. 여기서 알고 싶은 것은 인원수가 아니라 받은 적이 있는 부서 목록이므로 DISTINCT 키워드가 필요하다.

19.SELECT * FROM tStaff ORDER BY salary DESC OFFSET 11 ROWS FETCH NEXT 4 ROWS ONLY;

12위부터 출력하려면 앞쪽 11명을 건너 뛰어야 하며 16위까지 4명을 출력하면 된다.

5장

1.SELECT name FROM tStaff WHERE score IS NULL;

실적이 없다는 조건은 score 필드가 NULL인지 점검하며 이때 IS NULL 연산자를 사용한다. 직원 목록을 출력하기 위해 COUNT(*) 함수로 개수를 세는 대신 name 필드를 조사한다.

2.SELECT COUNT(*) FROM tStaff WHERE score >= 80;

레코드 개수만 세면 되므로 COUNT(*)를 쓰면 된다. 굳이 COUNT(name)이나 COUNT(score) 식으로 필드를 지정할 필요는 없다.

3.SELECT MAX(salary) FROM tStaff WHERE gender = '여';

WHERE 절에서 여직원만으로 제한하고 MAX(salary)를 조사한다.

4.SELECT MIN(joindate) FROM tStaff WHERE depart = '총무부';

최초 입사일은 joindate가 가장 작은 값을 찾으면 된다. 날짜나 문자열에 대해서도 MIN, MAX 집계 함수를 사용할 수 있다.

5.SELECT region, SUM(popu) FROM tCity GROUP BY region;

region을 기준 필드로 하여 그룹핑하고 SUM(popu)를 집계한다.

6.SELECT region, MAX(area) FROM tCity WHERE popu > 50 GROUP BY region;

인구 50만 이상은 가장 넓은 면적을 구하기 전의 조건이므로 WHERE 절로 지정한다. 강원도는 50만이 넘는 도시가 없어 아예 나타나지 않으며 전라도는 순천이 가장 넓지만 인구가 27만이어서 제외되고 인구 65만의 전주 면적인 205만이 조사된다.

7.SELECT region, AVG(area) FROM tCity GROUP BY region HAVING AVG(area) >= 1000;

지역을 기준으로 그룹핑하고 HAVING 절로 평균 면적을 제한한다. 지역별 평균 면적을 구한 후에 출력할 조건을 지정하므로 HAVING 절로 작성한다. 강원도만 출력된다.

6장

1.INSERT INTO tCity VALUES ('용인',293,98,'n','경기');

도시명, 면적, 인구, 광역시 여부, 지역 필드값을 순서대로 나열한다. 삽입을 확정하려면 COMMIT 명령도 호출해야 한다.

2.INSERT INTO tStaff VALUES ('김상형','총무부','남','2021-8-1','사원',480,10.0);

기존 직원과 이름만 중복되지 않으면 임의의 정보를 삽입할 수 있다.

3.오라클은 확장 INSERT 명령을 지원하지 않아 도시별로 INSERT INTO 명령을 일일이 작성해야 한다. 필드 목록은 선언 순서와 같아 생략해도 상관없다.

INSERT INTO tCity VALUES ('이천',461,21,'n','경기');

INSERT INTO tCity VALUES ('대구',883,248,'y','경상');

INSERT INTO tCity VALUES ('영월',1127,4,'n','강원');

SQL Server와 MariaDB는 확장 INSERT 문으로 세 도시를 한꺼번에 입력할 수 있다.

INSERT INTO tCity VALUES

('이천',461,21,'n','경기'),

('대구',883,248,'y','경상'),

('영월',1127,4,'n','강원');

4.오라클, MariaDB는 다음 쿼리문을 작성한다. score가 80 이상인 직원의 이름과 월급을 조회하여 tStaff_excellent 테이블을 새로 생성한다.

CREATE TABLE tStaff_excellent AS SELECT name, salary FROM tStaff WHERE score >= 80;

SQL Server는 이 명령 대신 SELECT INTO 구문으로 작성한다.

SELECT name, salary INTO tStaff_excellent FROM tStaff WHERE score >= 80;

명령 실행 후 tStaff_excellent 테이블을 조회하면 4명의 직원 목록이 나타난다.

5.DELETE FROM tStaff WHERE depart = '영업부';

7명의 직원이 한꺼번에 사라진다. COMMIT;을 실행하면 해고를 확정한다.

6.UPDATE tStaff SET grade = '대리' WHERE grade = '사원' and gender = '여';

여자이면서 사원인 직원을 찾아야 하므로 두 개의 조건을 AND로 연결한다. 3명의 여사원이 대리가 된다.

7.UPDATE tStaff SET salary = salary * 1.1 WHERE depart = '영업부';

salary 필드를 110%로 갱신하면 된다. SET salary = salary + salary * 0.1로 계산해도 마찬가지이다.

7장

1.기존 tStaff 생성 쿼리문의 각 필드에 DEFAULT 키워드와 기본값을 지정한다.

CREATE TABLE tStaffDefault

(

name CHAR (15) PRIMARY KEY,

depart CHAR (10) DEFAULT('영업부') NOT NULL,

gender CHAR(3) NOT NULL,

joindate DATE NOT NULL,

grade CHAR(10) DEFAULT('수습') NOT NULL,

salary INT DEFAULT(280) NOT NULL,

score DECIMAL(5,2) DEFAULT(1.0) NULL

);

이 테이블에 레코드를 삽입할 때는 기본값이 없는 이름, 성별, 입사일 필드만 입력하면 된다.

INSERT INTO tStaffDefault (name, gender, joindate) VALUES ('이율곡','남','2021-5-5');

나머지 필드는 기본값이 적용된다.

2.depart, gender, salary에 CHECK 조건을 설정한다.

CREATE TABLE tStaffCheck

(

name CHAR (15) PRIMARY KEY,

depart CHAR (10) NOT NULL CHECK(depart IN ('영업부','총무부','인사과')),

gender CHAR(3) NOT NULL CHECK(gender = '남' OR gender = '여'),

joindate DATE NOT NULL,

grade CHAR(10) NOT NULL,

salary INT NOT NULL CHECK(salary > 0),

score DECIMAL(5,2) NULL

);

체크 조건을 위반하는 레코드는 삽입할 수 없다.

3.테이블 제약으로 세 필드를 묶어 복합키로 지정한다.

CREATE TABLE tStaffCompoKey

(

name CHAR (15) NOT NULL,

depart CHAR (10) NOT NULL,

gender CHAR(3) NOT NULL,

joindate DATE NOT NULL,

grade CHAR(10) NOT NULL,

salary INT NOT NULL,

score DECIMAL(5,2) NULL,

CONSTRAINT PK_tStaff PRIMARY KEY (name, depart, gender)

);

이름이 같은 두 명의 사원이 다른 부서에 입사할 수 있다.

INSERT INTO tStaffCompoKey VALUES ('김철수','총무부','남','2021-1-1','사원',280,10);

INSERT INTO tStaffCompoKey VALUES ('김철수','영업부','남','2021-2-1','사원',290,20);

이름과 부서가 같아도 성별이 다르면 중복 가능하다.

8장

1.거주지는 회원에 대해 종속적이므로 별도 테이블로 분리해야 하지만 배송지는 회원뿐만 아니라 주문번호에도 종속적이므로 주문 테이블에 남겨 두어야 한다. 보라돌이가 노트북을 서울 사는 친구에게 선물한다면 배송지는 춘천이 아닌 서울일 수도 있다.

9장

1. SELECT name FROM tStaff WHERE score = (SELECT MAX(score) FROM tStaff);

MAX 집계 함수로 성취도의 최고점을 찾고 이 점수로부터 직원 이름을 조사한다. 두 개의 쿼리문을 작성한 후 하나로 합치기만 하면 된다. 신사임당을 출력한다.

2.SELECT name FROM tStaff WHERE salary >= (SELECT AVG(salary) FROM tStaff);

같은 방식이다. 평균 월급을 구하는 쿼리를 먼저 작성하고 이 평균값보다 월급이 더 크거나 같은 직원 목록을 출력하면 된다.

3.SELECT name, salary FROM tEmployee WHERE name =

(SELECT employee FROM tProject WHERE project = '노조 협상건');

tProject에서 노조 협상건을 맡은 직원의 이름을 먼저 구하고 이 이름으로부터 tEmployee 테이블을 검색하여 월급을 조사한다.

4.SELECT member, addr FROM tMember WHERE age = (SELECT MAX(age) FROM tMember);

나이의 최대값을 찾고 이 나이와 같은 사람을 찾는다. 방자로 조사되는데 여러 명 있어도 상관없다.

5.SELECT member, money FROM tMember WHERE member =

(SELECT member FROM tOrder WHERE item = '대추');

tOrder에서 대추를 구입한 회원을 조사하고 tMember에서 해당 회원의 이름과 예치금을 조사한다.

6.SELECT price FROM tItem WHERE item = (SELECT item FROM tOrder WHERE member = '춘향');

춘향이가 구입한 상품의 이름은 tOrder에서 조사하고 이 상품의 가격은 tItem에서 조사한다.

7.SELECT item, (SELECT delivery FROM tCategory WHERE category = I.category) AS delivery FROM tItem I;

상품명은 tItem 테이블에 있고 배송비 정보는 유형 테이블에 있다. tItem을 읽으며 서브쿼리로 상품의 유형을 전달하여 배송비를 조사하면 된다.

8.SELECT item FROM (SELECT * FROM tOrder WHERE status = 1) A WHERE A.num >= 10;

주문받은 목록을 먼저 구하고 그 중에 수량이 10개 이상인 것을 고르면 된다. 10개 이상인 상품은 대추밖에 없다.

9.

MERGE INTO tStaff T USING tStaff2 S ON (S.name = T.name)

WHEN MATCHED THEN

UPDATE SET T.depart = S.depart, T.salary = S.salary, T.score = S.score

WHEN NOT MATCHED THEN

INSERT VALUES (S.name, S.depart, S.gender, S.joindate, S.grade, S.salary, S.score);

안창호의 월급과 성삼문의 소속이 바뀌며 어우동이 새로 추가되어 tStaff은 총 21명의 직원으로 늘어난다.

10.MERGE INTO tStaff T USING (SELECT * FROM tStaff2 WHERE grade = '대리') S ON (S.name = T.name)

....

서브쿼리로 tStaff2의 대리급 사원만 읽어 병합한다. ON 절에 조건을 작성하는 것이 아님을 유의하자.

10장

1. SELECT name, salary, project FROM tEmployee, tProject WHERE tEmployee.name = tProject.employee;

FROM 절에 두 테이블을 콤마로 구분하여 적고 WHERE 절에서 외래키인 직원명이 같다는 조건을 설정하며 필드 목록에서 이름, 월급과 프로젝트명을 출력한다.

2.SELECT name, salary, project FROM tEmployee INNER JOIN tProject ON tEmployee.name = tProject.employee;

콤마를 INNER JOIN으로 변경하고 WHERE 절을 ON 절로 변경하면 된다. 출력 결과는 같다.

3.세 명령 모두 완전히 같은 결과셋을 출력한다. 두 테이블은 외래키 제약에 의해 무결성을 완벽하게 지키기 때문에 정보가 없는 경우가 없다. 따라서 어떤 방식으로 조인하나 결과는 같다.

4.SELECT P.project, P.cost, E.name project FROM (SELECT * FROM tProject WHERE cost < 500) P

INNER JOIN tEmployee E ON E.name = P.employee;

tProject 테이블에 비용 500만원 미만 조건을 지정한 후 직원 테이블과 조인한다.

5.SELECT I.*, C.discount, C.delivery FROM tItem I

INNER JOIN tCategory C ON I.category = C.category;

상품 정보는 tItem 테이블에 있지만 할인율과 배송비는 tCategory에 있다. 두 테이블을 조인하여 tItem의 모든 필드와 tCategory의 discount, delivery 필드를 같이 출력한다.

6.SELECT * FROM tOrder O INNER JOIN tItem I ON O.item = I.item WHERE I.category ='식품';

tOrder에는 주문 받은 제품명만 있을 뿐 그 제품의 유형 정보는 없다. 이 정보를 조사하기 위해 tCategory와 조인하고 WHERE 조건절에서 식품 유형만 출력한다.

11장

1.SELECT name, area, ROUND(SQRT(area), 2) FROM tCity;

면적의 제곱근을 구하는 문제이며 SQRT 함수를 사용한다. 단, 오라클의 경우 소수점 이하가 너무 길게 표시되므로 적당한 자리에서 반올림 처리하는 것이 보기 좋다.

2.SELECT name, area, popu, ROUND(popu * 10000 / area, 1) AS "인구밀도" FROM tCity;

인구를 면적으로 나누는 수식을 ROUND 함수로 감싸고 두 번째 인수를 1로 지정한다. 소수점 이하 2번째 자리에서 반올림한다.

3.DBMS마다 글자수를 세는 함수명이 달라 각각 따로 작성해야 한다. 함수 이름만 다를 뿐 사용하는 방법은 비슷하다.

오라클 : SELECT * FROM tStaff WHERE LENGTH(TRIM(name)) = 4;

SQL Server : SELECT * FROM tStaff WHERE LEN(name) = 4;

MariaDB : SELECT * FROM tStaff WHERE CHAR_LENGTH(name) = 4;

오라클은 CHAR 타입의 공백도 글자수로 세기 때문에 TRIM 함수로 불필요한 공백을 제거한 후 비교해야 한다. tStaff의 name 필드가 CHAR 타입이어서 뒤쪽에 공백이 있는데 VARCHAR 타입이라면 TRIM 함수가 없어도 상관없다.

4.SELECT SUBSTR('950314-1919234', 5, 2) FROM dual;

주민등록번호의 구조를 알고 있으니 5째 자리부터 2 글자를 추출하면 된다.

5.SELECT name, CONCAT(SUBSTR(name, 1, 1), grade) FROM tStaff;

이름의 첫 글자를 추출하고 뒤에 직급을 붙인다. 오라클은 || 연산자, SQL Server는 + 연산자를 사용할 수 있지만 호환성 확보를 위해 CONCAT 함수를 사용하였다.

6.첫 세 글자만 추출하고 ,를 -로 바꾼 후 앞에 'i'를, 뒤에 '인치'를 연결한다. 필요한 정보가 들어 있기만 하다면 문자열을 주물럭거려 원하는 형태로 만들 수 있다.

오라클 : SELECT 'i' || REPLACE(SUBSTR('5,13inch,16RAM,256SSD',1, 4), ',', '-') || '인치' FROM dual;

SQL Server : SELECT 'i' + REPLACE(LEFT('5,13inch,16RAM,256SSD',4), ',', '-') + '인치';

7.SELECT name, DECODE(metro, 'y', '광역시', 'n', '지방도시') FROM tCity;

metro 필드가 'y'이면 광역시를 리턴하고 'n'이면 지방도시를 리턴한다. y, n 두 가지 경우만 있고 그 외의 경우는 없으므로 마지막 인수는 굳이 적을 필요 없다. SQL Server는 CASE를 대신 사용한다.

8.SELECT Y, count(*) FROM (SELECT TO_CHAR(joindate, 'yyyy') Y FROM tStaff) GROUP BY Y ORDER BY Y;

서브쿼리에서 년도만 추출한 필드 Y를 정의하고 Y에 대해 그룹핑, 정렬을 수행하면 쿼리문 길이가 줄어들고 속도도 더 빨라진다.

9.SELECT * FROM tOrder WHERE SYSDATE - orderdate <= 7;

오늘 날짜와 주문 날짜가 7보다 작거나 같으면 반품 가능한 주문이다.

12장

1.CREATE VIEW vItemPrice AS SELECT item, price FROM tItem;

2.CREATE VIEW vDelivering AS SELECT * FROM tOrder WHERE status = 2;

3.CREATE VIEW vStaffBonus (name, bonus) AS SELECT name, salary * score / 100 FROM tStaff;

원본의 AS bonus를 빼고 뷰 이름 다음에 (name, bonus)로 뷰의 필드명을 지정한다.

4.다음 명령을 순서대로 실행한다. 임시 테이블에 사본을 복사하는 코드만 DBMS 마다 다르고 나머지 과정은 같다.

오라클 : CREATE GLOBAL TEMPORARY TABLE tCityTemp AS SELECT * FROM tCity;

INSERT INTO tCityTemp SELECT * FROM tCity WHERE region = '전라';

SQL Server : SELECT * INTO #tTemp FROM tCity WHERE region = '전라';

MariaDB : CREATE TEMPORARY TABLE tCityTemp AS SELECT * FROM tCity WHERE region = '전라';

INSERT INTO tCityTemp SELECT * FROM tCity WHERE area > 500;

DELETE FROM tCityTemp WHERE popu < 50;

UPDATE tCityTemp SET area = area * 30;

중간 과정에서 순천은 두 조건에 모두 맞아 두 번 삽입되는데 임시 테이블은 제약이 없어 PK 충돌 걱정없이 중복 삽입할 수 있다. 1제곱 킬로미터는 302500평이어서 대략 30을 곱하면 된다.

13장

1.SELECT region, AVG(popu) FROM tCity GROUP BY ROLLUP(region);

region으로 그룹핑하되 ROLLUP으로 감싸면 전체 지역의 평균도 같이 계산한다.

2.SELECT year, month, SUM(sales) FROM tMonthSale GROUP BY year, ROLLUP(month);

년도별, 월별로 그룹핑하고 month 필드에 대해서만 소계를 계산하면 각 연도의 모든 월매출 소계를 구한다. 년도에 대해서도 소계를 출력하면 전체 매출 합계까지 구한다. 월별, 년도별 그룹핑은 별 의미가 없다.

3.SELECT name, depart, salary, ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY depart), 2) AS 부서월급비율 FROM tStaff;

부서별로 그룹핑한 월급 총합으로 직원 월급을 나누어 백분율을 구하고 소수점 이하 2자리에서 반올림 처리한다.

4.SELECT year, month, sales, SUM(sales) OVER (ORDER BY year, month) AS 누적매출 FROM tMonthSale;

year, month로 정렬하여 매출 총합을 출력한다. 첫 행부터 누적을 시작하여 마지막 행까지 누적을 반복한다.

5.SELECT year, month, sales, SUM(sales) OVER (PARTITION BY year ORDER BY month) AS 누적매출 FROM tMonthSale;

year를 그룹핑 기준 필드로 지정하고 month로 정렬하여 누적 매출을 구한다. 2021년과 2022년의 그룹이 분리되어 2022년은 1월부터 매출을 새로 누적한다.

6.SELECT depart, AVG(salary) FROM tStaff GROUP BY depart;

SELECT DISTINCT depart, AVG(salary) OVER (PARTITION BY depart) FROM tStaff;

이 두 구문은 결과셋이 같다. OVER의 PARTITION BY로 부서별 평균 월급을 구하되 직원 목록을 다 출력할 필요는 없으므로 DISTINCT 키워드를 붙여 중복행을 제거한다.

7.SELECT RANK() OVER (ORDER BY POPU DESC), tCity.* FROM tCity;

춘천과 순천의 인구가 같아 공동 순위로 처리한다. 공동 순위를 건너 뛰려면 DENSE_RANK 함수를 대신 사용한다.

8.SELECT RANK() OVER (PARTITION BY region ORDER BY area DESC), tCity.* FROM tCity;

PARTITION BY region으로 지역별로 그룹핑하고 ORDER BY area DESC구문으로 면적의 역순으로 정렬하여 순위를 매긴다. 강원도에서는 홍천이 1등, 춘천이 2등이다.

9.SELECT RANK() OVER (PARTITION BY gender ORDER BY salary DESC) AS 순위, tStaff.* FROM tStaff;

gender 기준으로 그룹핑하고 월급 내림차순으로 정렬하여 순위를 매긴다.

10.SELECT NTILE(3) OVER (ORDER BY popu DESC), tCity.* FROM tCity;

인구의 내림차순으로 정렬하여 NTILE(3)으로 3분할한다. 8개의 도시를 3개의 그룹으로 나누면 3, 3, 2개로 분할한다.

11.SELECT depart, name, salary,

salary - PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY depart) AS disc

FROM tStaff ORDER BY depart, name;

부서에 실제 존재하는 월급과의 차이를 구하기 위해 이산 중위값으로 계산했다. 수학적인 중위값과의 차이를 구하려면 PERCENTILE_CONT 함수를 사용한다.

12.SELECT item, 봄, NVL(여름, 0) AS 여름, 가을, NVL(겨울, 0) AS 겨울 FROM tSeason2

PIVOT (SUM(sale) FOR season IN ('봄' AS 봄, '여름' AS 여름, '가을' AS 가을, '겨울' AS 겨울)) pvt

NVL 함수를 사용하면 되는데 어디다 적용할지 헷갈린다. NVL 함수는 최종 출력하기 전에 적용해야 하므로 피봇한 후 SELECT 절에서 지정하면 된다. 편의상 여름, 겨울에 대해서만 NVL을 적용했다. 다음 코드가 아님을 주의하자.

SELECT * FROM tSeason2

PIVOT (SUM(sale) FOR season IN ('봄', NVL('여름', 0), '가을', NVL('겨울', 0))) pvt;

IN 절은 피봇 대상 값만 지정할 뿐 그 값에 대한 처리까지 지정하는 것은 아니다. 피봇은 일단 하되 그 결과를 어떻게 보여줄 것인가는 SELECT 절에서 지정한다.

13.년도별 총 판매량 집계는 다음 쿼리로 구한다.

SELECT year, SUM(sales) FROM tMonthSale GROUP BY year;

이 결과를 피봇해서 년도를 열로 변환하려면 피봇이 필요하다.

SELECT * FROM tMonthSale PIVOT(SUM(sales) FOR year IN(2021, 2022)) pvt;

년도가 열로 전환되지만 월별로 그룹핑되어 년도별 판매량이 아닌 월별 판매량이 된다. 월을 제거하고 년도별로 그룹핑하려면 인라인뷰에서 월을 제외해야 한다.

SELECT * FROM

(

SELECT year, sales FROM tMonthSale

) prepvt

PIVOT(SUM(sales) FOR year IN(2021, 2022)) pvt;

year와 sales 두 개의 필드만 남긴 후 년도의 2021, 2022값에 대해 판매량의 합계를 구한다.

14장

1.ALTER TABLE tStaff ADD tel CHAR(20) NULL;

UPDATE tStaff SET tel = '015-396-1004' WHERE name = '윤봉길';

전화번호는 길이가 일정하고 최대 20자를 넘기지 않으므로 CHAR(20) 타입이 적합하다. 기존 직원은 아직 전화번호가 없으므로 NULL 가능해야 한다. 윤봉길의 전화번호를 입력해 보았다.

2.ALTER TABLE tStaff DROP COLUMN tel;

3.UPDATE tStaff SET score = 10 WHERE score IS NULL;

ALTER TABLE tStaff MODIFY score DECIMAL(5,2) NOT NULL;

이미 NULL인 레코드가 있어 이 값을 임의의 값으로 채운 후 NOT NULL로 변경해야 한다.

15장

1.

DECLARE

v_hour INT := 24;

v_minute INT := 60;

v_second INT := 60;

BEGIN

DBMS_OUTPUT.PUT_LINE('하루는 ' || v_hour * v_minute * v_second || '초입니다.');

END;

문자열을 연결하는 || 연산자보다 곱셈 연산자인 *의 우선 순위가 높아 연산식에 굳이 괄호를 감싸지 않아도 상관없다.

2.

DECLARE

v_maxSalary INT;

v_name tStaff.name%TYPE;

v_depart tStaff.depart%TYPE;

BEGIN

SELECT MAX(salary) INTO v_maxSalary FROM tStaff;

SELECT name, depart INTO v_name, v_depart FROM tStaff WHERE salary = v_maxSalary;

DBMS_OUTPUT.PUT_LINE(TRIM(v_depart) || '의 ' || TRIM(v_name) || '입니다.');

END;

"인사과의 장보고입니다."를 출력한다.

3.

DECLARE

v_score INT := 86;

BEGIN

DBMS_OUTPUT.PUT_LINE(

CASE

WHEN v_score >= 95 THEN 'A+'

WHEN v_score >= 90 THEN 'A'

WHEN v_score >= 85 THEN 'B+'

WHEN v_score >= 80 THEN 'B'

END

);

END;

4.SELECT * FROM tStaff ORDER BY grade;

grade를 기준으로 정렬하면 직급의 가나다순일 뿐 높은 직급순이 아니다. 어떤 직급이 얼마나 높은지 CASE 문으로 일일이 정해 주어야 한다.

SELECT * FROM tStaff ORDER BY

CASE grade

WHEN '이사' THEN 1

WHEN '부장' THEN 2

WHEN '차장' THEN 3

WHEN '과장' THEN 4

WHEN '대리' THEN 5

WHEN '사원' THEN 6

END;

또는 DECODE 함수로 더 간단하게 정렬할 수 있다.

SELECT * FROM tStaff ORDER BY

DECODE(TRIM(grade), '이사', 1, '부장', 2, '차장', 3, '과장', 4, '대리', 5, '사원', 6);

grade 필드가 CHAR 타입이어서 뒤쪽의 공백을 자른 후 비교해야 정확하다.

16장

1.

CREATE OR REPLACE PROCEDURE SP_PrintSalaryScore(p_name tStaff.name%TYPE)

AS

v_salary tStaff.salary%TYPE;

v_score tStaff.score%TYPE;

BEGIN

SELECT salary, score INTO v_salary, v_score FROM tStaff WHERE name = p_name;

DBMS_OUTPUT.PUT_LINE(p_name || '의 월급은 ' || v_salary ||

', 성취도는 ' || v_score || '입니다.');

END;

직원의 이름은 p_name 인수로 전달받는다. tStaff에서 name과 p_name을 비교하여 해당 직원을 찾고 월급과 성취도 필드를 v_salary, v_score 변수에 대입한 후 출력한다.

2.

CREATE OR REPLACE FUNCTION FN_GetPopuDencity(p_name CHAR)

RETURN NUMBER

AS

v_dencity NUMBER;

BEGIN

SELECT (popu * 10000.0)/area INTO v_dencity FROM tCity WHERE name = p_name;

RETURN v_dencity;

END;

인구가 만명 단위로 저장되어 있으므로 10000을 곱해야 한다. 도시명만 전달하면 인구밀도를 구해 리턴한다. 다음 명령은 홍천의 인구밀도 38을 계산하여 출력한다.

SELECT FN_GetPopuDencity('홍천') FROM dual;

서브쿼리의 결과값을 바로 리턴할 수 없어 변수로 대입한 후 변수를 리턴해야 한다. 이에 비해 SQL Server의 경우는 서브쿼리를 바로 리턴할 수 있다.

CREATE FUNCTION GetPopuDens(@p_name CHAR(10)) RETURNS DECIMAL

AS

BEGIN

RETURN (SELECT (popu * 10000.0)/area FROM tCity WHERE name = @p_name);

END

SELECT dbo.GetPopuDens('홍천'); 형식으로 호출한다.

3.

CREATE OR REPLACE FUNCTION FN_GetRangeSum(p_start INT, p_end INT, p_step INT)

RETURN INT

AS

v_num INT := p_start;

v_total INT := 0;

BEGIN

WHILE v_num <= p_end

LOOP

v_total := v_total + v_num;

v_num := v_num + p_step;

END LOOP;

RETURN v_total;

END;

인수를 세 개 받아 p_start는 v_num의 초기값으로, p_end는 WHILE 문의 조건으로, p_step은 v_num의 증가값으로 사용한다. 1씩 증가하는 것이 아니어서 FOR 문은 사용할 수 없다.

17장

1.CURSOR를 직접 정의하여 순회할 수도 있고 FOR 문으로 커서를 생성하여 순회할 수도 있다.

DECLARE

CURSOR v_cursor IS SELECT * FROM tStaff;

v_staff tStaff%ROWTYPE;

BEGIN

OPEN v_cursor;

LOOP

FETCH v_cursor INTO v_staff;

EXIT WHEN v_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_staff.name || v_staff.depart || v_staff.salary);

END LOOP;

CLOSE v_cursor;

END;

DECLARE

BEGIN

FOR v_cursor IN (SELECT * FROM tStaff)

LOOP

DBMS_OUTPUT.PUT_LINE(v_cursor.name || v_cursor.depart || v_cursor.salary);

END LOOP;

END;

2.해당 부서의 직원 목록을 조사하여 출력용 커서를 리턴하는 프로시저를 작성한다.

CREATE OR REPLACE PROCEDURE SP_OutStaffSalary(p_depart IN CHAR, o_cursor OUT SYS_REFCURSOR)

AS

BEGIN

OPEN o_cursor FOR SELECT name, salary FROM tStaff WHERE depart = p_depart;

END;

다음 코드로 테스트한다.

DECLARE

v_cursor SYS_REFCURSOR;

v_name CHAR(15);

v_salary INT;

BEGIN

SP_OutStaffSalary('인사과', v_cursor);

LOOP

FETCH v_cursor INTO v_name, v_salary;

EXIT WHEN v_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(TRIM(v_name) || '의 월급은 ' || v_salary || '만원입니다.');

END LOOP;

END;

직원의 이름과 월급 목록을 출력한다.

18장

1.

CREATE OR REPLACE TRIGGER TR_ChangeSalary

AFTER UPDATE OF salary ON tStaff

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.salary || '->' || :NEW.salary);

END;

월급의 변화에만 관심이 있으므로 AFTER UPDATE 다음에 OF salary를 지정한다. 본체에서는 직원 이름과 이전, 이후 월급을 출력한다. 직원 1명의 월급을 변경하면 메시지 하나를 출력하고 부서의 모든 직원 월급을 변경하면 각 부서원의 월급 변경 내역을 출력한다.

2.

CREATE OR REPLACE TRIGGER TR_ChangeSalary

AFTER UPDATE ON tStaff

FOR EACH ROW

BEGIN

IF :NEW.salary > :OLD.salary * 1.1 THEN

RAISE_APPLICATION_ERROR(-20000, '뭔가 수상한 월급 인상임');

END IF;

END;

새 월급이 이전 월급의 1.1배가 넘으면 예외를 일으켜 갱신을 취소한다.

3.

CREATE OR REPLACE TRIGGER TR_ChangeSalary

BEFORE UPDATE ON tStaff

FOR EACH ROW

BEGIN

IF :NEW.salary > :OLD.salary * 1.1 THEN

:NEW.salary := :OLD.salary * 1.1;

END IF;

END;

값이 변경되기 전의 이벤트를 받아야 하므로 BEFORE 트리거로 변경한다. 새 월급이 이전 월급의 10%를 초과하면 상한선 10% 인상으로 강제 변경한다.