\documentclass[10pt,a4paper]{article}

% Packages
\usepackage{fancyhdr}           % For header and footer
\usepackage{multicol}           % Allows multicols in tables
\usepackage{tabularx}           % Intelligent column widths
\usepackage{tabulary}           % Used in header and footer
\usepackage{hhline}             % Border under tables
\usepackage{graphicx}           % For images
\usepackage{xcolor}             % For hex colours
%\usepackage[utf8x]{inputenc}    % For unicode character support
\usepackage[T1]{fontenc}        % Without this we get weird character replacements
\usepackage{colortbl}           % For coloured tables
\usepackage{setspace}           % For line height
\usepackage{lastpage}           % Needed for total page number
\usepackage{seqsplit}           % Splits long words.
%\usepackage{opensans}          % Can't make this work so far. Shame. Would be lovely.
\usepackage[normalem]{ulem}     % For underlining links
% Most of the following are not required for the majority
% of cheat sheets but are needed for some symbol support.
\usepackage{amsmath}            % Symbols
\usepackage{MnSymbol}           % Symbols
\usepackage{wasysym}            % Symbols
%\usepackage[english,german,french,spanish,italian]{babel}              % Languages

% Document Info
\author{nollae93}
\pdfinfo{
  /Title (mssql.pdf)
  /Creator (Cheatography)
  /Author (nollae93)
  /Subject (MsSQL Cheat Sheet)
}

% Lengths and widths
\addtolength{\textwidth}{6cm}
\addtolength{\textheight}{-1cm}
\addtolength{\hoffset}{-3cm}
\addtolength{\voffset}{-2cm}
\setlength{\tabcolsep}{0.2cm} % Space between columns
\setlength{\headsep}{-12pt} % Reduce space between header and content
\setlength{\headheight}{85pt} % If less, LaTeX automatically increases it
\renewcommand{\footrulewidth}{0pt} % Remove footer line
\renewcommand{\headrulewidth}{0pt} % Remove header line
\renewcommand{\seqinsert}{\ifmmode\allowbreak\else\-\fi} % Hyphens in seqsplit
% This two commands together give roughly
% the right line height in the tables
\renewcommand{\arraystretch}{1.3}
\onehalfspacing

% Commands
\newcommand{\SetRowColor}[1]{\noalign{\gdef\RowColorName{#1}}\rowcolor{\RowColorName}} % Shortcut for row colour
\newcommand{\mymulticolumn}[3]{\multicolumn{#1}{>{\columncolor{\RowColorName}}#2}{#3}} % For coloured multi-cols
\newcolumntype{x}[1]{>{\raggedright}p{#1}} % New column types for ragged-right paragraph columns
\newcommand{\tn}{\tabularnewline} % Required as custom column type in use

% Font and Colours
\definecolor{HeadBackground}{HTML}{333333}
\definecolor{FootBackground}{HTML}{666666}
\definecolor{TextColor}{HTML}{333333}
\definecolor{DarkBackground}{HTML}{6E42FF}
\definecolor{LightBackground}{HTML}{F5F3FF}
\renewcommand{\familydefault}{\sfdefault}
\color{TextColor}

% Header and Footer
\pagestyle{fancy}
\fancyhead{} % Set header to blank
\fancyfoot{} % Set footer to blank
\fancyhead[L]{
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{C}
    \SetRowColor{DarkBackground}
    \vspace{-7pt}
    {\parbox{\dimexpr\textwidth-2\fboxsep\relax}{\noindent
        \hspace*{-6pt}\includegraphics[width=5.8cm]{/web/www.cheatography.com/public/images/cheatography_logo.pdf}}
    }
\end{tabulary}
\columnbreak
\begin{tabulary}{11cm}{L}
    \vspace{-2pt}\large{\bf{\textcolor{DarkBackground}{\textrm{MsSQL Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{nollae93} via \textcolor{DarkBackground}{\uline{cheatography.com/72104/cs/19184/}}}
\end{tabulary}
\end{multicols}}

\fancyfoot[L]{ \footnotesize
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{LL}
  \SetRowColor{FootBackground}
  \mymulticolumn{2}{p{5.377cm}}{\bf\textcolor{white}{Cheatographer}}  \\
  \vspace{-2pt}nollae93 \\
  \uline{cheatography.com/nollae93} \\
  \end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Cheat Sheet}}  \\
   \vspace{-2pt}Not Yet Published.\\
   Updated 7th September, 2020.\\
   Page {\thepage} of \pageref{LastPage}.
\end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Sponsor}}  \\
  \SetRowColor{white}
  \vspace{-5pt}
  %\includegraphics[width=48px,height=48px]{dave.jpeg}
  Measure your website readability!\\
  www.readability-score.com
\end{tabulary}
\end{multicols}}




\begin{document}
\raggedright
\raggedcolumns

% Set font size to small. Switch to any value
% from this page to resize cheat sheet text:
% www.emerson.emory.edu/services/latex/latex_169.html
\footnotesize % Small font.


\begin{tabularx}{17.67cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{17.67cm}}{\bf\textcolor{white}{Syntex}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{SQL 구문은 (;)으로 끝난다.} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{대소문자 구분이 없다.} \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{키워드는 대문자, 테이블명은 첫 문자만 대문다, 그 외 컬럼명 등은 소문자 관례} \tn 
% Row Count 5 (+ 3)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{Table에 저장한 문자열의 경우는 대문자, 소문자를 구별. 'ABC'와 'abc'} \tn 
% Row Count 7 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{SQL 구문은 작성하는 순서가 있어 순서대로 기재해야 한다.} \tn 
% Row Count 9 (+ 2)
% Row 5
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{SQL에 직접 기술하는 문자열, 날짜, 숫자 등을 상수라고 한다.} \tn 
% Row Count 11 (+ 2)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{문자열, 날짜 상수은 ('), 숫자 상수는 숫자 그대로 사용} \tn 
% Row Count 13 (+ 2)
% Row 7
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{한글 별명은 (") 사용} \tn 
% Row Count 14 (+ 1)
% Row 8
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{단어와 단어 사이는 공백 또는 줄바꿈으로 구분} \tn 
% Row Count 16 (+ 2)
% Row 9
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{1행 주석은 (-{}-) 뒤에} \tn 
% Row Count 17 (+ 1)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{복수행 주석은 (/*  */) 사이에} \tn 
% Row Count 18 (+ 1)
% Row 11
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{database, table, column 이름은 영문자, 숫자, 언더바 만 사용 가능.} \tn 
% Row Count 20 (+ 2)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{이름 첫 글자는 '영문자'로 해야 함.} \tn 
% Row Count 21 (+ 1)
% Row 13
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{가능하면 \seqsplit{집계함수(집약함수)를} 사용하는 것이 성능향상에 좋다. 성능차 많이 난다.} \tn 
% Row Count 24 (+ 3)
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{{\bf{기술 순서}} : SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY} \tn 
% Row Count 26 (+ 2)
% Row 15
\SetRowColor{white}
\mymulticolumn{1}{x{17.67cm}}{{\bf{실행 순서}} : FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY} \tn 
% Row Count 28 (+ 2)
% Row 16
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{17.67cm}}{쿼리문을 만들 때 열 이름에는 가공을 하지 말아라. Index가 있어도 가공을 하면 전체 Scan을 하여 속도가 느려진다.} \tn 
% Row Count 31 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Backup }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
BACKUP & {\bf{USE}} tempDB;\{\{nl\}\}{\bf{BACKUP DATABASE}} ShopDB {\bf{TO DISK}} = 'D:\textbackslash{}sqlDB2019.bak' {\bf{WITH INIT}}; \tn 
% Row Count 6 (+ 5)
% Row 2
\SetRowColor{LightBackground}
RESTORE & {\bf{USE}} tempDB;\{\{nl\}\}{\bf{RESTORE DATABASE}} ShopDB {\bf{FROM DISK =}} 'D:\textbackslash{}ShopDB.bak'{\bf{ WITH REPLACE}};\{\{nl\}\}{\bf{USE}} ShopDB; \tn 
% Row Count 12 (+ 6)
% Row 3
\SetRowColor{white}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Database }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 13 (+ 1)
% Row 4
\SetRowColor{LightBackground}
CREATE & USE tempdb;\{\{nl\}\}CREATE DATABASE shopDB; \tn 
% Row Count 15 (+ 2)
% Row 5
\SetRowColor{white}
EXCUTE & EXEC sp\_helpdb; \tn 
% Row Count 16 (+ 1)
% Row 6
\SetRowColor{LightBackground}
USE & USE shopDB; \tn 
% Row Count 17 (+ 1)
% Row 7
\SetRowColor{white}
DROP & USE tempdb;\{\{nl\}\}DROP DATABASE shopDB;~~~~-{}- Table과 Data 있어도 삭제 됨. \tn 
% Row Count 22 (+ 5)
% Row 8
\SetRowColor{LightBackground}
{\bf{\textless{}\textless{}{\emph{ Schema }}\textgreater{}\textgreater{}}} & CREATE SCHEMA userSchema; \tn 
% Row Count 24 (+ 2)
% Row 9
\SetRowColor{white}
 & CREATE TABLE userSchema.userTbl (id CHAR(8)); \tn 
% Row Count 27 (+ 3)
% Row 10
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Table }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 28 (+ 1)
% Row 11
\SetRowColor{white}
EXECUTE & EXEC sp\_tables @table\_type = " ' TABLE' "; \tn 
% Row Count 31 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
CREATE & USE shopDB;\{\{nl\}\}CREATE TABLE userTbl ( \{\{nl\}\}~~ ~~userID CHAR(8) NOT NULL PRIMARY KEY, ~~~~-{}- CONSTRAINT PF\_name PRIMARY KEY CLUSTERED \{\{nl\}\}~~ ~~name CHAR(8) NOT NULL UNIQUE,\{\{nl\}\}~~ ~~birthYear SMALLINT NOT NULL DEFAULT YEAR(GETDATE( )) CHECK (birthYear\textgreater{}1900),\{\{nl\}\}~~ ~~hobby NVARCHAR(10) SPARSE NULL); \tn 
% Row Count 21 (+ 21)
% Row 13
\SetRowColor{white}
 & CREATE TABLE buyTbl (\{\{nl\}\}~~ ~~num INT NOT NULL IDENTITY(1, 1),\{\{nl\}\}~~ ~~userID CHAR(8) NOT NULL FOREIGN KEY REFERENCES userTbl(userID),\{\{nl\}\}~~ ~~prodName NVARCHAR(20) NOTNULL,\{\{nl\}\}~~ ~~amount INT,\{\{nl\}\}~~ ~~CONSTRAINT PK\_num PRIMARY KEY CLUSTERED (userID) ), \{\{nl\}\}~~ ~~CONSTRAINT CK\_birthYear CHECK (birthYear\textgreater{}1900); \tn 
% Row Count 44 (+ 23)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 14
\SetRowColor{LightBackground}
DROP & DROP TABLE userTbl; \tn 
% Row Count 1 (+ 1)
% Row 15
\SetRowColor{white}
RENAME & sp\_rename 'userTbl', 'userTbl1'; \tn 
% Row Count 3 (+ 2)
% Row 16
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Alter - Column }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 4 (+ 1)
% Row 17
\SetRowColor{white}
EXEC & EXEC sp\_help buyTbl; \tn 
% Row Count 5 (+ 1)
% Row 18
\SetRowColor{LightBackground}
ADD {\emph{colname}} & ALTER TABLE userTbl ADD weight SMALLINT NULL; \tn 
% Row Count 8 (+ 3)
% Row 19
\SetRowColor{white}
ALTER COLUMN & ALTER TABLE userTbl ALTER COLUMN weight INT NULL;~~~~-{}- NOT NULL로 변경 시 이전 값 체크함. \tn 
% Row Count 14 (+ 6)
% Row 20
\SetRowColor{LightBackground}
DROP COLUMN & ALTER TABLE userTbl DROP COLUMN weight;~~~~-{}- Data 있어도 삭제 됨. \tn 
% Row Count 19 (+ 5)
% Row 21
\SetRowColor{white}
RENAME & EXEC sp\_rename 'userTbl.weight', 'myWeight', 'COLUMN'; \tn 
% Row Count 22 (+ 3)
% Row 22
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Alter - Constraint }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 23 (+ 1)
% Row 23
\SetRowColor{white}
ADD CONSTRAINT & -{}- DEFAULT(이전 값 체크 안함), CHECK / UNIQUE (이전 값 체크), \tn 
% Row Count 27 (+ 4)
% Row 24
\SetRowColor{LightBackground}
~~~~PRIMARY KEY & ALTER TABLE userTbl ADD CONSTRAINT PK\_userTbl\_userID PRIMARY KEY (userID, name); \tn 
% Row Count 31 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 25
\SetRowColor{LightBackground}
~~~~FOREIGN KEY & ALTER TABLE buyTbl ADD CONSTRAINT FK\_buyTbl\_userTbl FOREIGN KEY (userID) REFERENCES userTbl(userID); \tn 
% Row Count 5 (+ 5)
% Row 26
\SetRowColor{white}
~~~~~~~~ON UPDATE\{\{nl\}\}~~~~~~~~ON DELETE & ALTER TABLE buyTbl ADD CONSTRAINT FK\_buyTbl\_userTbl FOREIGN KEY (userID) REFERENCES userTbl(userID) \{\{nl\}\}~~~~ON UPDATE CASCADE ON DELETE RESTRICT; \tn 
% Row Count 14 (+ 9)
% Row 27
\SetRowColor{LightBackground}
~~~~UNIQUE & ALTER TABLE userTbl ADD CONSTRAINT UN\_name UNIQUE(name); \tn 
% Row Count 17 (+ 3)
% Row 28
\SetRowColor{white}
~~~~CHECK & ALTER TABLE userTbl ADD CONSTRAINT CK\_birth CHECK (birthYear \textgreater{}= 1900 AND birthYear \textless{}= YEAR(GETDATE())); \tn 
% Row Count 23 (+ 6)
% Row 29
\SetRowColor{LightBackground}
~~~~DEFAULT & ALTER TABLE userTbl ADD CONSTRAINT DF\_birthYear DEFAULT YEAR(GETDATE()) FOR birthYear;\{\{nl\}\}~~~~-{}- INSERT INTO userTbl VALUES (2016, DEFAULT, 195);~~~~-{}- 컬럼 지정 안하면 DEFAULT 입력.\{\{nl\}\}~~~~-{}- INSERT INTO userTbl(birtyYear, height) VALUES ( 2016, 195);~~~~-{}- 아니면 컬럼 지정하여 입력.\{\{nl\}\}~~~~-{}- INSERT INTO userTbl VALUES (2016, N'서울', 195);~~~~-{}- DEFAULT에 다른 값 입력 가능. \tn 
% Row Count 50 (+ 27)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 30
\SetRowColor{LightBackground}
DROP CONSTRAINT & ALTER TABLE buyTbl DROP CONSTRAINT \seqsplit{FK\_buyTbl\_userTbl;} \tn 
% Row Count 3 (+ 3)
% Row 31
\SetRowColor{white}
 & ALTER TABLE userTbl DROP CONSTRAINT \seqsplit{PK\_userTbl\_userID;} \tn 
% Row Count 6 (+ 3)
% Row 32
\SetRowColor{LightBackground}
변경 & 변경은 기존 Constraint DROP 후 새로 ADD CONSTRAINT 한다. \tn 
% Row Count 10 (+ 4)
% Row 33
\SetRowColor{white}
NOCHECK CONSTRAINT & ALTER TABLE userTbl NOCHECK CONSTRAINT CK\_mobile;~~~~-{}- 제약사항 임시 해제. 사용 후 다시 체크 시작할 것. \tn 
% Row Count 18 (+ 8)
% Row 34
\SetRowColor{LightBackground}
CHECK CONSTRAINT & ALTER TABLE userTbl CHECK CONSTRAINT CK\_mobile; \tn 
% Row Count 21 (+ 3)
% Row 35
\SetRowColor{white}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Index }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 22 (+ 1)
% Row 36
\SetRowColor{LightBackground}
CREATE INDEX - ON & CREATE INDEX IDX\_userTbl\_addr ON userTbl(addr); \tn 
% Row Count 25 (+ 3)
% Row 37
\SetRowColor{white}
 & CREATE UNIQUE INDEX IDX\_userTbl\_addr ON userTbl(addr); \tn 
% Row Count 28 (+ 3)
% Row 38
\SetRowColor{LightBackground}
DROP INDEX - ON & DROP INDEX IDX\_userTbl\_addr ON userTbl;~~~~-{}- CREAT TABLE에서 PRIMARY KEY / UNIQUE로 생성된 것은 삭제 안됨 \tn 
% Row Count 35 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 39
\SetRowColor{LightBackground}
EXEC & EXEC sp\_helpindex userTbl; \tn 
% Row Count 2 (+ 2)
% Row 40
\SetRowColor{white}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Identity }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 3 (+ 1)
% Row 41
\SetRowColor{LightBackground}
SET IDENTITY INSERT - ON & CREATE TABLE identTbl (num INT NOT NULL IDENTITY, name CHAR(3));\{\{nl\}\}SET IDENTITY\_INSERT identTbl ON;\{\{nl\}\}INSERT INTO identTbl(num, name) VALUES (10, 'CCC');~~~~-{}- 'num' 열 이름을 기재해야 함. \tn 
% Row Count 15 (+ 12)
% Row 42
\SetRowColor{white}
SET IDENTITY INSERT - OFF & SET IDENTITY\_INSERT identTbl OFF; \tn 
% Row Count 17 (+ 2)
% Row 43
\SetRowColor{LightBackground}
SELECT IDENT\_CURRENT( ) & SELECT IDENT\_CURRENT( 'identTbl' );~~~~-{}- ( ' ) 필요 \tn 
% Row Count 21 (+ 4)
% Row 44
\SetRowColor{white}
SELECT @@IDENTITY & SELECT @@IDENTITY;~~~~-{}- 현재 쿼리 창 기준 \tn 
% Row Count 25 (+ 4)
% Row 45
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Insert }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 26 (+ 1)
% Row 46
\SetRowColor{white}
BEGIM TRAN & BEGIM TRANSACTION; \tn 
% Row Count 27 (+ 1)
% Row 47
\SetRowColor{LightBackground}
INSERT INTO - VALUES & INSERT INTO Goods VALUES ('0001', '의류', NULL, DEFAULT);~~~~-{}- NULL 직접 기술 가능. DEFAULT 기술 추천. \tn 
% Row Count 34 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 48
\SetRowColor{LightBackground}
 & INSERT INTO Goods(id, name) VALUES(0001, '의류');~~~~-{}- NOT NULL 컬럼을 제외하고 특정 컬럼 지정하여 입력 가능. \tn 
% Row Count 8 (+ 8)
% Row 49
\SetRowColor{white}
 & INSERT INTO Goods(id, name) VALUES (0001, '의류'), (0002, '사무');; \tn 
% Row Count 12 (+ 4)
% Row 50
\SetRowColor{LightBackground}
INSERT INTO - SELECT & CREATE TABLE newTbl (e INT, f INT, g INT);~~~~-{}- 입력할 테이블이 만들어져 있어야 한다.\{\{nl\}\}INSERT INTO  newTbl SELECT a, b, c FROM  oldTbl; \tn 
% Row Count 21 (+ 9)
% Row 51
\SetRowColor{white}
 & INSERT INTO  newTbl(e, f) SELECT b, a  FROM  oldTbl; \tn 
% Row Count 24 (+ 3)
% Row 52
\SetRowColor{LightBackground}
 & INSERT INTO targetTbl SELECT * FROM updateTbl WHERE NOT EXITS (SELECT a FROM targetTbl WHERE targetTbl.a = updateTbl.a); \tn 
% Row Count 30 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 53
\SetRowColor{LightBackground}
SAVE TRAN & SAVE TRANSACTION; \tn 
% Row Count 1 (+ 1)
% Row 54
\SetRowColor{white}
ROLLBACK TRAN & ROLLBACK TRANSACTION;~~~~ COMMIT 되기 전 취소. CHECK 제약 조건 때문에 에러 발생한 경우 자동 ROLL BACK 되지는 않는다. \tn 
% Row Count 10 (+ 9)
% Row 55
\SetRowColor{LightBackground}
COMMIT TRAN & COMMIT TRANSACTION; \tn 
% Row Count 11 (+ 1)
% Row 56
\SetRowColor{white}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Update  }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 12 (+ 1)
% Row 57
\SetRowColor{LightBackground}
UPDATE - SET & BEGIN TRY\{\{nl\}\}~~~~BEGIN TRAN\{\{nl\}\}~~~~~~~~UPDATE userTbl SET mData = '0000-00-00';~~~~-{}- 전체 바뀜\{\{nl\}\}~~~~~~~~UPDATE userTbl SET height = height * 0.01, mDate = NULL WHERE addr = '경북';\{\{nl\}\}~~~~~~~~-{}- NULL CLEAR : NOT NULL 조건 없어야 함.\{\{nl\}\}~~~~COMMIT TRAN\{\{nl\}\}END TRY\{\{nl\}\}BEGIN CATCH\{\{nl\}\}~~~~ROLLBACK TRAN\{\{nl\}\}~~~~SELECT ERROR\_NUM( )\{\{nl\}\}~~~~SELECT ERROR\_MESSAGE( )\{\{nl\}\}END CATCH \tn 
% Row Count 45 (+ 33)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{8.635 cm} x{8.635 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Summary (cont)}}  \tn
% Row 58
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{17.67cm}}{{\bf{\textless{}\textless{}{\emph{ Delete }}\textgreater{}\textgreater{}}}} \tn 
% Row Count 1 (+ 1)
% Row 59
\SetRowColor{white}
TRUNCATE TABLE & TRUNCATE TABLE buyTbl;~~~~-{}- 빠름. \tn 
% Row Count 4 (+ 3)
% Row 60
\SetRowColor{LightBackground}
DELETE FROM & DELETE FROM userTbl;~~~~-{}-한 줄씩 삭제. 느림. 테이블은 남음. \tn 
% Row Count 9 (+ 5)
% Row 61
\SetRowColor{white}
 & DELETE FROM userTbl WHERE addr = '서울'; \tn 
% Row Count 12 (+ 3)
% Row 62
\SetRowColor{LightBackground}
 & DELETE TOP(3) FROM userTbl WHERE addr = '서울';~~~~-{}- 무작위로 3명 삭제됨. \tn 
% Row Count 18 (+ 6)
% Row 63
\SetRowColor{white}
 & DELETE  FROM userTbl WHERE userID IN (SELECT TOP(2) userID FROM userTbl WHERE addr = '서울' ORDER BY height);\{\{nl\}\}-{}- 우선 순위 삭제 방법. DELETE -WHERE문과 하위 SELECT 문에서 기본 KEY를 지정해야 함. \tn 
% Row Count 30 (+ 12)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase}}  \tn
% Row 0
\SetRowColor{LightBackground}
{\bf{\textless{}{\emph{ BACKUP }}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
BACKUP & {\bf{USE}} tempDB;\{\{nl\}\}{\bf{BACKUP DATABASE}} ShopDB {\bf{TO DISK}} = 'D:\textbackslash{}sqlDB2019.bak' {\bf{WITH INIT}}; & DATABASE 단위로 백업함. 폴더 미리 생성. \tn 
% Row Count 12 (+ 6)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{RESTORE} & {\bf{USE}} tempDB;\{\{nl\}\}{\bf{RESTORE DATABASE}} ShopDB {\bf{FROM DISK =}} 'D:\textbackslash{}ShopDB.bak'{\bf{ WITH REPLACE}};\{\{nl\}\}{\bf{USE}} ShopDB; & \seqsplit{데이터베이스를} 일단 다른 곳으로 바꾸고 진행할 것. \tn 
% Row Count 20 (+ 8)
% Row 3
\SetRowColor{white}
{\bf{\textless{}{\emph{ EXECUTE}}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 26 (+ 6)
% Row 4
\SetRowColor{LightBackground}
 & {\bf{EXECUTE sp\_helpdb}}; & SQLServer \tn 
% Row Count 28 (+ 2)
% Row 5
\SetRowColor{white}
 & {\bf{EXECUTE sp\_help}} Goods; & 테이블 정보 \tn 
% Row Count 30 (+ 2)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
 & {\bf{EXECUTE sp\_depends}}  userTbl; & 참조 Table 확인 \tn 
% Row Count 2 (+ 2)
% Row 7
\SetRowColor{white}
 & {\bf{EXECCUTE sp\_helpindex}} userTbl; & index 확인 \tn 
% Row Count 5 (+ 3)
% Row 8
\SetRowColor{LightBackground}
 & {\bf{SELECT \seqsplit{OBJECT\_NAME(object\_id)} as}} {[}뷰 이름{]}, definition {\bf{FROM sys.sql\_modules}}; & View / Procedure 확인 \tn 
% Row Count 11 (+ 6)
% Row 9
\SetRowColor{white}
SHOW & {\bf{SHWO DATABASES}}; & MySQL \tn 
% Row Count 13 (+ 2)
% Row 10
\SetRowColor{LightBackground}
 & {\bf{SHOW TABLES}}; & MySQL \tn 
% Row Count 14 (+ 1)
% Row 11
\SetRowColor{white}
기타 & {\bf{EXECUTE sp\_tables @table\_type = " ' TABLE' "}}; &  \tn 
% Row Count 18 (+ 4)
% Row 12
\SetRowColor{LightBackground}
 & {\bf{EXECUTE sp\_columns @table\_name}} = 'Department', {\bf{@table\_owner}} = \seqsplit{'HumanResources';} &  \tn 
% Row Count 24 (+ 6)
% Row 13
\SetRowColor{white}
USE & {\bf{USE}} shop; &  \tn 
% Row Count 25 (+ 1)
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{} \tn 
% Row Count 25 (+ 0)
% Row 15
\SetRowColor{white}
{\bf{\textless{}{\emph{ \seqsplit{Database} }}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 31 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase (cont)}}  \tn
% Row 16
\SetRowColor{LightBackground}
CREATE \seqsplit{DATABASE} & {\bf{USE}} tempdb;\{\{nl\}\}{\bf{CREATE DATABASE}} shop; &  \tn 
% Row Count 3 (+ 3)
% Row 17
\SetRowColor{white}
USE & {\bf{USE}} shopDB; &  \tn 
% Row Count 4 (+ 1)
% Row 18
\SetRowColor{LightBackground}
DROP & {\bf{USE}} tempdb;\{\{nl\}\}{\bf{DROP DATABASE}} shopDB; & 안에 있는 테이블도 모두 삭제됨. \tn 
% Row Count 7 (+ 3)
% Row 19
\SetRowColor{white}
{\bf{\textless{}{\emph{ Schema }}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 13 (+ 6)
% Row 20
\SetRowColor{LightBackground}
CREATE SCHEMA & {\bf{CREATE SCHEMA}} userSchema; &  \tn 
% Row Count 15 (+ 2)
% Row 21
\SetRowColor{white}
CREATE TABLE & {\bf{CREATE TABLE}} \seqsplit{userSchema.userTbl} (id INT); &  \tn 
% Row Count 18 (+ 3)
% Row 22
\SetRowColor{LightBackground}
SELECT & {\bf{SELECT}} * {\bf{FROM}} \seqsplit{userSchema.userTbl;} &  \tn 
% Row Count 21 (+ 3)
% Row 23
\SetRowColor{white}
{\bf{\textless{}{\emph{ View }}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 27 (+ 6)
% Row 24
\SetRowColor{LightBackground}
Syntex & 원 Table에 NOT NULL이 있는 Column이 있으면 INSERT가 안된다. &  \tn 
% Row Count 32 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase (cont)}}  \tn
% Row 25
\SetRowColor{LightBackground}
 & JOIN 문으로 만들어진 View는 \seqsplit{원칙적으로는} INSERT 가 안된다. INSTEAD OF TRIGGER를 사용해야 한다. &  \tn 
% Row Count 8 (+ 8)
% Row 26
\SetRowColor{white}
 & VIEW 생성 시 WITH CHECH OPTION을 사용햐야 조건에 맞지 않는 data 입력 시 Error를 \seqsplit{발생시켜준다}. &  \tn 
% Row Count 16 (+ 8)
% Row 27
\SetRowColor{LightBackground}
 & VIEW 가 참조하고 있는 Table도 그냥 삭제가 된다. 삭제되면 VIEW는 더 이상 사용할 수 없다. 따라서 삭제 전 EXEC sp\_depends 로 먼저 확인하는 것이 좋다. &  \tn 
% Row Count 28 (+ 12)
% Row 28
\SetRowColor{white}
CREATE VIEW - AS & {\bf{CREATE VIEW}} v\_userTbl {\bf{AS SELECT}} userID, name, addr {\bf{FROM}} userTbl &  \tn 
% Row Count 33 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase (cont)}}  \tn
% Row 29
\SetRowColor{LightBackground}
 & {\bf{CREATE VIEW}} v\_sum {\bf{AS SELECT}} userID, {\bf{SUM}}(price*amount) AS {[}total{]} {\bf{FROM}} buyTbl {\bf{GROUP BY}} userID; &  \tn 
% Row Count 8 (+ 8)
% Row 30
\SetRowColor{white}
- WHTI CHECK OPTION & {\bf{CREATE VIEW}} v\_height177 {\bf{AS SELECT}} * {\bf{FROM}} userTbl {\bf{WHERE}} height \textgreater{}= 177 {\bf{WHTI CHECK OPTION}}; &  \tn 
% Row Count 15 (+ 7)
% Row 31
\SetRowColor{LightBackground}
 & -{}- CAUTION : WITH CHECH OPTION이 없으면 INSERT로 height 177 이상의 data를 입력해도 입력이 된다. 입력후 조회는 안된다. 이를 방지하기 위해 사용. &  \tn 
% Row Count 27 (+ 12)
% Row 32
\SetRowColor{white}
- WITH \seqsplit{ENCRIPTION} & {\bf{ALTER VIEW}} v\_userTbl {\bf{WHTI ENCRIPTION AS SELECT}} userID, name, addr {\bf{FROM}} userTbl & \seqsplit{암호화(복구} 안됨) \tn 
% Row Count 33 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase (cont)}}  \tn
% Row 33
\SetRowColor{LightBackground}
SELECT & {\bf{SELECT}} * {\bf{FROM}} v\_userTbl; &  \tn 
% Row Count 2 (+ 2)
% Row 34
\SetRowColor{white}
 & 읽기 전용으로 사용할자. &  \tn 
% Row Count 5 (+ 3)
% Row 35
\SetRowColor{LightBackground}
 & 복잡한 쿼리문을 View로 만들어 두면 간단하게 SELECT 문으로 조회할 수 있다. &  \tn 
% Row Count 12 (+ 7)
% Row 36
\SetRowColor{white}
ALTER VIEW - AS & {\bf{ALTER VIEW}} v\_suerTbl {\bf{AS  SELECT}} userID AS {[}아이디{]}, name AS {[}이름{]}, addr {\bf{FROM}} userTbl &  \tn 
% Row Count 19 (+ 7)
% Row 37
\SetRowColor{LightBackground}
DROP VIEW & {\bf{DROP VIEW}} v\_userTbl; &  \tn 
% Row Count 21 (+ 2)
% Row 38
\SetRowColor{white}
내용 확인 & {\bf{SELECT \seqsplit{OBJECT\_NAME(object\_id)} as}} {[}뷰 이름{]}, definition {\bf{FROM sys.sql\_modules}}; &  \tn 
% Row Count 27 (+ 6)
% Row 39
\SetRowColor{LightBackground}
\seqsplit{참조관계} 확인 & {\bf{EXEC sp\_depends}}  userTbl; &  \tn 
% Row Count 30 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DataBase (cont)}}  \tn
% Row 40
\SetRowColor{LightBackground}
UPDATE & {\bf{UPDATE}} v\_userTbl {\bf{SET}} addr = N'서울' {\bf{WHERE}} userID = 'JKW'; &  \tn 
% Row Count 5 (+ 5)
% Row 41
\SetRowColor{white}
INSERT & {\bf{INSERT INTO}} v\_userTbl (userID, name, addr) {\bf{VALUES}} ('KBM', '김병만', '충북'); &  \tn 
% Row Count 11 (+ 6)
% Row 42
\SetRowColor{LightBackground}
 & -{}- 원 userTbl의 birthYear이 NOT NULL로 되어 있어 Error이 발행함. 편법적으로 NOT NULL을 NULL로 변경 후 삽입하여야 한다. 원칙적으로 이 경우 INSERT 불가 함. &  \tn 
% Row Count 24 (+ 13)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ Create }}\textgreater{}}}} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
Syntex & NULL도 \seqsplit{습관적으로} 적어주자. NULL이 자주 되는 곳은 SPARSE NULL로 메모리 관리 할 것.  NULL이 별로 없는 경우에 지정하면 더 않좋음. 최소 60\%는 되어야 함. SELECT 검색 속도는 오히려 느려짐 Table 압축도 안된다. &  \tn 
% Row Count 19 (+ 18)
% Row 2
\SetRowColor{LightBackground}
 & 테이블 이름 앞에 '\#'을 붙이면 tempdb에 임시 테이블이 생성 된다. 생성한 \seqsplit{쿼리창에서만} 인식되고 쿼리창을 닫으면 삭제된다. 다른 \seqsplit{쿼리창에서도} \seqsplit{사용하려면} '\#\#'을 붙여서 만들면 된다. &  \tn 
% Row Count 36 (+ 17)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 3
\SetRowColor{LightBackground}
 & 데이터 무결성을 위한 \seqsplit{제약조건으로} NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 5가지가 있다. &  \tn 
% Row Count 8 (+ 8)
% Row 4
\SetRowColor{white}
CREATE TABLE & {\bf{CREATE TABLE}} Goods  \{\{nl\}\} ( id {\bf{CHAR}}(4) NOT NULL {\bf{PRIMARY KEY}}, name {\bf{VARCHAR}}(100) NOT NULL, price {\bf{INTEGER}} NULL {\bf{DEFAULT}} 0, date {\bf{DATE}} NULL); &  \tn 
% Row Count 19 (+ 11)
% Row 5
\SetRowColor{LightBackground}
 & {\bf{CREATE TABLE}} Goods  \{\{nl\}\} ( num {\bf{INT IDENTITY}} NOT NULL, id {\bf{CHAR(8)}} NOT NULL {\bf{FOREIGN KEY REFERENCES}} Goods{\bf{(id)}}, amount {\bf{SMALLINT}} NULL); &  \tn 
% Row Count 30 (+ 11)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
- SPARSE NULL & {\bf{CREATE TABLE}} userTbl (name NCHAR(8) NOT NULL, hobby NVARCHAR(10) {\bf{SPARSE NULL}}); &  \tn 
% Row Count 6 (+ 6)
% Row 7
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ PRIMARY KEY }}\textgreater{}}}} \tn 
% Row Count 7 (+ 1)
% Row 8
\SetRowColor{LightBackground}
CREATE & 중복 값이 없는 열. NULL은 안됨. 키본 키는 하나(두 개 열을 합쳐서 설정 가능) &  \tn 
% Row Count 14 (+ 7)
% Row 9
\SetRowColor{white}
- 바로 지정 & {\bf{CREATE TABLE}} userTbl ( userID CHAR(8) NOT NULL {\bf{PRIMARY KEY}}); &  \tn 
% Row Count 19 (+ 5)
% Row 10
\SetRowColor{LightBackground}
- 이름 생성 & {\bf{CREATE TABLE}} userTbl ( userID CHAR(8) NOT NULL {\bf{CONSTRAINT}} {\emph{PK\_useTbl\_userID}} {\bf{PRIMARY KEY}}); &  \tn 
% Row Count 26 (+ 7)
% Row 11
\SetRowColor{white}
- \seqsplit{마지막에} 지정 & {\bf{CREATE TABLE}} userTbl (userID CHAR(8) NOT NULL{\bf{, CONSTRAINT}} {\emph{PK\_useTbl\_userID}} {\bf{PRIMARY KEY(}}{\emph{ userID}} {\bf{)}}); &  \tn 
% Row Count 34 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
 & , 로 구분하여 마지막에 지정. 지정할 컬럼명을 \seqsplit{넣어줘야함}. &  \tn 
% Row Count 6 (+ 6)
% Row 13
\SetRowColor{white}
ALTER - ADD & {\bf{ALTER TABLE}} userTbl {\bf{ADD CONSTRAINT}} {\emph{PK\_useTbl\_userID}} {\bf{PRIMARY KEY}}(userID) ); &  \tn 
% Row Count 12 (+ 6)
% Row 14
\SetRowColor{LightBackground}
\seqsplit{복수열} 지정 & 복수의 열을 합쳐서 하나의 값으로 지정, 합쳐진 값이 중복되면 안됨. &  \tn 
% Row Count 19 (+ 7)
% Row 15
\SetRowColor{white}
 & {\bf{CREATE TABLE}} prodTbl (prodCode CHAR(3) NOT NULL, prodID CHAR(4) NOT NULL{\bf{,}} \{\{nl\}\}{\bf{CONSTRAINT}} {\emph{PK\_prodTbl\_prodCode\_prodID}} {\bf{PRIMARY KEY(}}{\emph{ prodCode{\bf{,}} prodID}} {\bf{)}} ) &  \tn 
% Row Count 31 (+ 12)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 16
\SetRowColor{LightBackground}
 & {\bf{ALTER TABLE}} prodTbl {\bf{ADD CONSTRAINT}} {\emph{PK\_prodTbl\_prodCode\_prodID}} {\bf{PRIMARY KEY}}(prodCode, prodID) ); &  \tn 
% Row Count 8 (+ 8)
% Row 17
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ FOREIGN  KEY }}\textgreater{}}}} \tn 
% Row Count 9 (+ 1)
% Row 18
\SetRowColor{LightBackground}
CREATE & PRIMARY / UNIQUE KEY에만 지정 가능. &  \tn 
% Row Count 12 (+ 3)
% Row 19
\SetRowColor{white}
- 바로 지정 & {\bf{CREATE TABLE}} buyTbl (userID CHAR(8) NOT NULL {\bf{FOREIGN KEY REFERENCES}} \seqsplit{userTbl(userID)} ); &  \tn 
% Row Count 19 (+ 7)
% Row 20
\SetRowColor{LightBackground}
- 이름 생성 & {\bf{CREATE TABLE}} buyTbl  \{\{nl\}\}(userID CHAR(8) NOT NULL {\bf{CONSTRAINT}} {\emph{FK\_userTbl\_buyTbl}} {\bf{FOREIGN KEY REFERENCES}} \seqsplit{userTbl(userID)} ); &  \tn 
% Row Count 29 (+ 10)
% Row 21
\SetRowColor{white}
- \seqsplit{마지막에} & {\bf{CREATE TABLE}} buyTbl (userID CHAR(8) NOT NULL, \{\{nl\}\}{\bf{CONSTRAINT}} {\emph{FK\_userTbl\_buyTbl}} {\bf{FOREIGN KEY}}(userID) {\bf{REFERENCES}} \seqsplit{userTbl(userID)}  ); &  \tn 
% Row Count 39 (+ 10)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 22
\SetRowColor{LightBackground}
ALTER - ADD & {\bf{ALTER TABLE}} buyTbl {\bf{ADD CONSTRAINT}} {\emph{FK\_userTbl\_buyTbl}} {\bf{FOREIGN KEY}}(userID) {\bf{REFERENCES}} \seqsplit{userTbl(userID);} &  \tn 
% Row Count 8 (+ 8)
% Row 23
\SetRowColor{white}
- ON UPDATE CASCADE & {\bf{ALTER TABLE}} buyTbl {\bf{ADD CONSTRAINT}} \seqsplit{FK\_userTbl\_buyTbl} {\bf{FOREIGN KEY}} (userID) {\bf{REFERENCE}} \seqsplit{userTbl(userID)} {\bf{ON UPDATE CASCADE}}; & PK 변경 자동 반영 \tn 
% Row Count 18 (+ 10)
% Row 24
\SetRowColor{LightBackground}
- ON DELETE RESTRICT & {\bf{ALTER TABLE}} buyTbl {\bf{DROP CONSTRAINT}} \seqsplit{FK\_userTbl\_buyTbl;} \{\{nl\}\}{\bf{ALTER TABLE}} buyTbl {\bf{ADD CONSTRAINT}} \seqsplit{FK\_userTbl\_buyTbl} {\bf{FOREIGN KEY}} (userID) {\bf{REFERENCE}} \seqsplit{userTbl(userID)} {\bf{ON UPDATE CASCADE ON DELETE RESTRICT}}; & 제약조건 삭제 후 다시 지정 \tn 
% Row Count 33 (+ 15)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 25
\SetRowColor{LightBackground}
 & -{}-CASCASE / SET NULL / SET DEFAULT / NO ACTION / RESTRICT &  \tn 
% Row Count 4 (+ 4)
% Row 26
\SetRowColor{white}
 & SSMS - Table 우클릭  - 디자인 - Table sheet 바탕화면 우클릭 - 관계 - 추가 - 테이블 및 열 사양 - ...클릭 - (기본 키 테이블 / 지정할 열) \& (외래 키 테이블 / 지정할 열) 선책 &  \tn 
% Row Count 19 (+ 15)
% Row 27
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ UNIQUE }}\textgreater{}}}} \tn 
% Row Count 20 (+ 1)
% Row 28
\SetRowColor{white}
CREATE & 중복되지 않는 유일한 값. NULL 허용 &  \tn 
% Row Count 24 (+ 4)
% Row 29
\SetRowColor{LightBackground}
- 바로 지정 & {\bf{CREATE TABLE}} userTbl (userID CHAR(8) NOT NULL {\bf{PRIMARY KEY}}, email CHAR(30) NULL {\bf{UNIQUE}}); &  \tn 
% Row Count 31 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 30
\SetRowColor{LightBackground}
- 이름 생성 & {\bf{CREATE TABLE}} userTbl (userID CHAR(8) NOT NULL {\bf{PRIMARY KEY}}, email CHAR(30) NULL {\bf{CONSTRAINT}} AK\_email {\bf{UNIQUE}}); &  \tn 
% Row Count 9 (+ 9)
% Row 31
\SetRowColor{white}
- \seqsplit{마지막에} 지정 & {\bf{CREATE TABLE}} userTbl (userID CHAR(8) NOT NULL {\bf{PRIMARY KEY}}, email CHAR(30) NULL, {\bf{CONSTRAINT}} AK\_email {\bf{UNIQUE(}}email{\bf{)}}); &  \tn 
% Row Count 18 (+ 9)
% Row 32
\SetRowColor{LightBackground}
ALTET - ADD & {\bf{ALTER TABLE}} userTbl {\bf{ADD CONSTRAINT}} UN\_name {\bf{UNIQUE}}(name); &  \tn 
% Row Count 23 (+ 5)
% Row 33
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ CHECK }}\textgreater{}}}} \tn 
% Row Count 24 (+ 1)
% Row 34
\SetRowColor{LightBackground}
CREATE & {\bf{CREATE TABLE}} userID (birthYear NULL {\bf{CHECK}} (birthYear\textgreater{}1900)); &  \tn 
% Row Count 29 (+ 5)
% Row 35
\SetRowColor{white}
 & {\bf{CREATE TABLE}} userID (birthYear NULL, {\bf{CONSTRAINT}} CK\_birthYear {\bf{CHECK}} (birthYear\textgreater{}1900)); &  \tn 
% Row Count 36 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 36
\SetRowColor{LightBackground}
ALTER - ADD & {\bf{ALTER TABLE}} userTbl {\bf{ADD CONSTRAINT}} CK\_birth {\bf{CHECK}} (birthYear \textgreater{}= 1900 AND birthYear \textless{}= YEAR(GETDATE( ))); &  \tn 
% Row Count 8 (+ 8)
% Row 37
\SetRowColor{white}
 & {\bf{ALTER TABLE}} userTbl {\bf{ADD CONSTRAINT}} CK\_mobile1 {\bf{CHECK}} (mobile1 IN('010', '011', '016')); &  \tn 
% Row Count 15 (+ 7)
% Row 38
\SetRowColor{LightBackground}
 & {\bf{ALTER TABLE}} DocExc {\bf{ADD}} ColumnD INT NULL {\bf{CONSTRAINT}} \seqsplit{CHK\_ColumnD\_DocExc} {\bf{CHECK}} (ColumnD \textgreater{} 10 AND ColumnD \textless{} 50); &  \tn 
% Row Count 24 (+ 9)
% Row 39
\SetRowColor{white}
ALTER - WITH NOCHECK ADD & {\bf{ALTER TABLE}} userTbl {\bf{WITH NOCHECK ADD CONSTRAINT}} CK\_mobile {\bf{CHECK}} (mobile1 IN('010', '011', '016')); &  \tn 
% Row Count 32 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 40
\SetRowColor{LightBackground}
 & -{}- WITH NOCHECK는 모든 CONSTRAINT 설정 시 사용 가능. &  \tn 
% Row Count 5 (+ 5)
% Row 41
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ DEFALUT }}\textgreater{}}}} \tn 
% Row Count 6 (+ 1)
% Row 42
\SetRowColor{LightBackground}
CREATE & {\bf{CREATE TABLE}} userTbl (birthYear INT NOT NULL {\bf{DEFAULT}} YEAR(GETDATE( ), addr NCHAR(2) NOT NULL {\bf{DEFALUT}} N'서울', height SMALLINT {\bf{DEFAULT}}170); &  \tn 
% Row Count 17 (+ 11)
% Row 43
\SetRowColor{white}
ALTER & {\bf{ALTER TABLE}} userTbl {\bf{ADD CONSTRAINT}} DF\_birthYear {\bf{DEFAULT}} YEAR(GETDATE( )) {\bf{FOR}} birthYear; &  \tn 
% Row Count 24 (+ 7)
% Row 44
\SetRowColor{LightBackground}
INSERT & {\bf{INSERT INTO}} userTbl {\bf{VALUES}} (2016, DEFALUT, 195); &  \tn 
% Row Count 28 (+ 4)
% Row 45
\SetRowColor{white}
 & {\bf{INSERT INTO}} \seqsplit{userTbl(birthYear}, height) {\bf{VALUES}} (2016, 195); &  \tn 
% Row Count 33 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 46
\SetRowColor{LightBackground}
 & {\bf{INSERT INTO}} userTbl {\bf{VALUES}} (2016, N'인천', 195); &  \tn 
% Row Count 4 (+ 4)
% Row 47
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ IDENTITY }}\textgreater{}}}} \tn 
% Row Count 5 (+ 1)
% Row 48
\SetRowColor{LightBackground}
 & 자동 순번 생성(Server) .INSERT 시 값 입력하면 안됨. 자동으로 NOT NULL 지정 됨. &  \tn 
% Row Count 12 (+ 7)
% Row 49
\SetRowColor{white}
 & INT {\bf{IDENTITY}} &  \tn 
% Row Count 14 (+ 2)
% Row 50
\SetRowColor{LightBackground}
 & INT {\bf{IDENTITY(1, 2)}} & 초기 값 1, 증가 값2 \tn 
% Row Count 16 (+ 2)
% Row 51
\SetRowColor{white}
 & SSMS TABLE 생성 - 열 속성 - ID 사양을 '예'로 변경하면 동일한 효과 & PRIMARY KEY로 지정되 것이 ID임.(num) \tn 
% Row Count 22 (+ 6)
% Row 52
\SetRowColor{LightBackground}
- SEQUENCE & IDENTITY 대신 SEQUENCE 객체를 사용할 수 있다. \seqsplit{오라클과의} 호완성을 위해 만듬. Transaxt-SQL 기본(5) 20:00 &  \tn 
% Row Count 31 (+ 9)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 53
\SetRowColor{LightBackground}
{\bf{\textless{}{\emph{ \seqsplit{데이터} 형 }}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 7 (+ 7)
% Row 54
\SetRowColor{white}
- \seqsplit{문자형} & \seqsplit{CHAR(5)/VARCHAR(10)} / VARCHAR(max) & 영어(기호) 고정 5자/가변 10자 / 최대 8천자 \tn 
% Row Count 11 (+ 4)
% Row 55
\SetRowColor{LightBackground}
 & NCHAR(5) / NVARCHAR(10) / NVARCHAR(max) & 한글 고정 5자 / 가변 10자 /최대 4천자 \tn 
% Row Count 15 (+ 4)
% Row 56
\SetRowColor{white}
 & VARBINARY(max) & 이미지, 동영상 저장에 사용 \tn 
% Row Count 18 (+ 3)
% Row 57
\SetRowColor{LightBackground}
 & {\bf{CAST}}(@MovieScript {\bf{AS NVARCHAR}}({\bf{MAX}})) & 로 변형하면 최대 2GB까지 저장 가능 \tn 
% Row Count 22 (+ 4)
% Row 58
\SetRowColor{white}
- \seqsplit{숫자형} & BIT & 0 or 1 \tn 
% Row Count 24 (+ 2)
% Row 59
\SetRowColor{LightBackground}
 & TINYINT / SMALLINT / INT / BIGINT & +255 / ±3.2만(+ 6.5만) / \seqsplit{±21억(+42억)} / 이상 \tn 
% Row Count 28 (+ 4)
% Row 60
\SetRowColor{white}
 & DECIMAL(5,2) & 전체 5자리 중 소수점 이하 2자리 사용 \tn 
% Row Count 32 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 61
\SetRowColor{LightBackground}
 & FLOAT / DOUBLE & 작은 / 큰 \seqsplit{부동소수점} \tn 
% Row Count 2 (+ 2)
% Row 62
\SetRowColor{white}
- \seqsplit{날짜형} & \seqsplit{DATE/TIME/DATETIME2} &  \tn 
% Row Count 4 (+ 2)
% Row 63
\SetRowColor{LightBackground}
- 기타 & CURSOR & T-SQL 커스를 변수로 처리 \tn 
% Row Count 7 (+ 3)
% Row 64
\SetRowColor{white}
 & TABLE & 테이블 자체를 저장. 임시 테이블과 비슷한 기능. \tn 
% Row Count 12 (+ 5)
% Row 65
\SetRowColor{LightBackground}
 & XML & XML 데이터 형식 저장. 최대 2GB \tn 
% Row Count 15 (+ 3)
% Row 66
\SetRowColor{white}
{\bf{\textless{}{\emph{ Rename }}\textgreater{}}} & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}- &  \tn 
% Row Count 22 (+ 7)
% Row 67
\SetRowColor{LightBackground}
RENAME TABLE & {\bf{sp\_rename}} 'Gods', 'Goods'; & SQL Server \tn 
% Row Count 24 (+ 2)
% Row 68
\SetRowColor{white}
 & -{}- RENAME TABLE Gods to Goods; & MySQL \tn 
% Row Count 26 (+ 2)
% Row 69
\SetRowColor{LightBackground}
DROP TABLE & {\bf{DROP TABLE}} Goods; & Table 자체 삭제, FK \seqsplit{테이블부터} 삭제해야 함. \tn 
% Row Count 30 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 70
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ ALTER TABLE  - Column }}\textgreater{}}}} \tn 
% Row Count 1 (+ 1)
% Row 71
\SetRowColor{white}
ADD {\emph{colname}} & {\bf{ALTER TABLE}} Goods {\bf{ADD}} name\_eng VARCHAR(100); & SQL Server Only \tn 
% Row Count 5 (+ 4)
% Row 72
\SetRowColor{LightBackground}
 & -{}- SSMS에서 drag해서 열 순서를 바꿀 수 있다. &  \tn 
% Row Count 9 (+ 4)
% Row 73
\SetRowColor{white}
 & -{}- ALTER TABLE Goods ADD COLUMN name\_eng VARCHAR(100); & MySQL \tn 
% Row Count 13 (+ 4)
% Row 74
\SetRowColor{LightBackground}
ALTER COLUMN & {\bf{ALTER TABLE}} userTbl {\bf{ALTER COLUMN}} hobby NVARCHAR(10) NOT NULL; &  \tn 
% Row Count 18 (+ 5)
% Row 75
\SetRowColor{white}
 & -{}- 데이터 유형, 크기 변경. 숫자는 문자로 가능, 반대는 에러. 크기가 작아도 에러. NULL 있는 데이터를 NOT NULL로 바꿔도 ERROR. 아래와 같이 업데이트 후 사용해야 함. \{\{nl\}\}{\bf{UPDATE}} userTbl  {\bf{SET}} hobby = ' ' {\bf{WHERE}} hobby IS NULL; &  \tn 
% Row Count 38 (+ 20)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 76
\SetRowColor{LightBackground}
DROP COLUMN & {\bf{ALTER TABLE}} Goods {\bf{DROP COLUMN}} name\_eng; & 제약조건 \seqsplit{걸려있으면} 제약조건 먼저 삭제해야 함. \tn 
% Row Count 5 (+ 5)
% Row 77
\SetRowColor{white}
EXEC & {\bf{EXEC sp\_rename}} '{\bf{userTbl.}}uesrID', 'ID', {\bf{'COLUMN';}} & 컬럼명 변경 \tn 
% Row Count 9 (+ 4)
% Row 78
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{{\bf{\textless{}{\emph{ ALTER TABLE  - Constraint }}\textgreater{}}}} \tn 
% Row Count 10 (+ 1)
% Row 79
\SetRowColor{white}
ADD \seqsplit{CONSTRAINT} & {\bf{ALTER TABLE}} userTbl {\bf{ADD CONSTRAINT}} DF\_birthYear {\bf{DEFAULT}} YEAR(GETDATE( )) {\bf{FOR}} birthYear; &  \tn 
% Row Count 17 (+ 7)
% Row 80
\SetRowColor{LightBackground}
DROP \seqsplit{CONSTRAINT} & {\bf{ALTER TABLE}} userTbl {\bf{DROP CONSTRAINT}} DF\_birthYear; &  \tn 
% Row Count 21 (+ 4)
% Row 81
\SetRowColor{white}
NOCHECK \seqsplit{CONSTRAINT} & {\bf{ALTER TABLE}} buyTbl {\bf{NOCHECK CONSTRAINT}} \seqsplit{FK\_userTbl\_buyTbl;} & FK 제약을 중지 \seqsplit{시킨다.(임시로} 사용 가능) \tn 
% Row Count 26 (+ 5)
% Row 82
\SetRowColor{LightBackground}
CHECK \seqsplit{CONSTRAINT} & {\bf{ALTER TABLE}} buyTbl {\bf{CHECK CONSTRAINT}} \seqsplit{FK\_userTbl\_buyTbl;} & 다시 CONSTRAINT 실행 \tn 
% Row Count 31 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.8801 cm} x{6.5793 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DDL - Data Definition Language (cont)}}  \tn
% Row 83
\SetRowColor{LightBackground}
변경 & 기존 Constraint DROP 후 새로 ADD CONSTRAINT 한다. &  \tn 
% Row Count 4 (+ 4)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index}}  \tn
% Row 0
\SetRowColor{LightBackground}
Syntex & 과용하면 성능 저하가 발생한다. &  \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
 & WHERE 절에 Index를 생성한 열의 이름이 나와야 Index 검색을 한다. 따라서 WHERE 절에 사용되는 열에 INDEX를 만들어라. &  \tn 
% Row Count 12 (+ 9)
% Row 2
\SetRowColor{LightBackground}
 & WHERE 절에 \seqsplit{사용하더라도} 자주 사용하는 것만 만들어라. \seqsplit{가끔사용하는} 것은 차라리 한 번 느려지는 것이  낫다. 인덱스가 있으면 INSERT / UPDATE /DELETE 등에 더 느려진다. INSERT 를 자주 사용하면 안 만드는 것이 좋다. &  \tn 
% Row Count 29 (+ 17)
% Row 3
\SetRowColor{white}
 & 데이터의 종류가 몇가지 않되는 컬럼에는 INDEX를 만들어도 SQL이 자동으로 사용하지 않는다. 따라서 용량만 커지므로 만들지 않는 것이 \seqsplit{좋다.(핸드폰} 국번 등) &  \tn 
% Row Count 42 (+ 13)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index (cont)}}  \tn
% Row 4
\SetRowColor{LightBackground}
 & 사용하지 않는 Nonclustered Index는 제거하는 것이 좋다. 검색 시간이 더 걸릴 수 있다. &  \tn 
% Row Count 7 (+ 7)
% Row 5
\SetRowColor{white}
 & 외래 키가 사용되는 열에는 인덱스를 되도록 생성해주는 것이 좋다. &  \tn 
% Row Count 13 (+ 6)
% Row 6
\SetRowColor{LightBackground}
 & JOIN에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다. &  \tn 
% Row Count 18 (+ 5)
% Row 7
\SetRowColor{white}
 & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-- &  \tn 
% Row Count 23 (+ 5)
% Row 8
\SetRowColor{LightBackground}
 & Index는 Clusteder 형과 NonClustered 형으로 만들 수 있다. &  \tn 
% Row Count 27 (+ 4)
% Row 9
\SetRowColor{white}
 & Clustered 형은 db를 index별로 정렬하여 별도로 저장하고, NonClustered 형은 Index Page만 만들고 db는 순차적으로 저장한다. &  \tn 
% Row Count 36 (+ 9)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index (cont)}}  \tn
% Row 10
\SetRowColor{LightBackground}
 & Clustered 형 Index는 table당 하나만 만들 수 있다. PRIMARY KEY 또는 UNIQUE 제약으로 만들 수 있다. &  \tn 
% Row Count 7 (+ 7)
% Row 11
\SetRowColor{white}
 & Clustered Index를 생성하는 열은 범위나 집계함수, ORDER BY절에 자주 사용하는 열의 경우에 효과적이다.  이미 정렬되어 있기 때문이다. &  \tn 
% Row Count 18 (+ 11)
% Row 12
\SetRowColor{LightBackground}
 & Insert가 대량으로 일어나는 경우 Clustered index가 있는 경우 성능 저하가 심할 수 있어 무조건 clustered index가 좋은 것은 아니다. PRIMARY KEY NONCLUSTERED 로 지정하여 \seqsplit{Nonclustered형으로} 만드는 것이 좋다. &  \tn 
% Row Count 33 (+ 15)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index (cont)}}  \tn
% Row 13
\SetRowColor{LightBackground}
 & Clustered형은 검색은 빠르나 Insert, Alter에는 \seqsplit{Nonclustered형이} 더 빠르다. &  \tn 
% Row Count 6 (+ 6)
% Row 14
\SetRowColor{white}
 & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-- &  \tn 
% Row Count 11 (+ 5)
% Row 15
\SetRowColor{LightBackground}
 & Index는 하나 또는 여러 열로 만들 수 있다. &  \tn 
% Row Count 15 (+ 4)
% Row 16
\SetRowColor{white}
 & Index는 제약조건 없이 만들 수 없다. &  \tn 
% Row Count 18 (+ 3)
% Row 17
\SetRowColor{LightBackground}
 & Primary Key와 Unique 제약조건을 걸면 자동으로 Index가 생성된다. &  \tn 
% Row Count 23 (+ 5)
% Row 18
\SetRowColor{white}
 & 여러 걸럼에 걸쳐 제약조건을 걸어 Index를 만들려면 CONSTRAINTS 제약조건면 (col1, col2) 로 만든다. &  \tn 
% Row Count 31 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index (cont)}}  \tn
% Row 19
\SetRowColor{LightBackground}
 & PRIMARY KEY를 지정하면 CLUSTERED 형 index가 자동 생성된다. NONCLUSTERED 형으로 별도 지정하여 바꿀 수 있다. &  \tn 
% Row Count 8 (+ 8)
% Row 20
\SetRowColor{white}
 & PRIMARY KET를 NONCLUSTERED로 지정하면 UNIQUE를 CLUSTERED 형으로 지정할 수 있다. &  \tn 
% Row Count 14 (+ 6)
% Row 21
\SetRowColor{LightBackground}
 & PRIMARY KEY로 Clustered Index를 만들어도 UNIQUE 는 Nonclustered Index로 여러 개 만들 수 있다. &  \tn 
% Row Count 21 (+ 7)
% Row 22
\SetRowColor{white}
 & PRIMARY KEY와 UNIQUE 제약 조건으로 자동 생성된 index는 DROP INDEX로 제거할 수 없다. ALTER TABLE - DROP CONSTRAINT로 제거해야 여기에 자동 생성된 index가 제거 된다. &  \tn 
% Row Count 33 (+ 12)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index (cont)}}  \tn
% Row 23
\SetRowColor{LightBackground}
 & -{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-{}-- &  \tn 
% Row Count 5 (+ 5)
% Row 24
\SetRowColor{white}
CREATE INDEX & 따로 지정하지 않으면 NONCLUSTERED 형으로  만들어 진다. &  \tn 
% Row Count 10 (+ 5)
% Row 25
\SetRowColor{LightBackground}
CREATE INDEX - ON & {\bf{CREATE INDEX}} \seqsplit{idx\_userTbl\_addr} {\bf{ON}} userTbl(addr); &  \tn 
% Row Count 14 (+ 4)
% Row 26
\SetRowColor{white}
 & {\bf{CREATE UNIQUE INDEX}} \seqsplit{idx\_userTbl\_name} {\bf{ON}} userTbl(name); &  \tn 
% Row Count 18 (+ 4)
% Row 27
\SetRowColor{LightBackground}
DROP INDEX & PRIMARY KEY와 UNIQUE 제약 조건으로 자동 생성된 index는 DROP INDEX로 제거할 수 없다. ALTER TABLE - DROP CONSTRAINT로 제거해야 여기에 자동 생성된 index가 제거 된다. &  \tn 
% Row Count 30 (+ 12)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.0366 cm} x{7.2541 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Index (cont)}}  \tn
% Row 28
\SetRowColor{LightBackground}
 & {\bf{DROP INDEX}} \seqsplit{idx\_userTbl\_addr} {\bf{ON}} userTbl; &  \tn 
% Row Count 3 (+ 3)
% Row 29
\SetRowColor{white}
 & {\bf{DROP INDEX userTbl.}}idx\_userTbl\_addr; & 테이블 명을 적어줘야 한다. \tn 
% Row Count 6 (+ 3)
% Row 30
\SetRowColor{LightBackground}
ALTER INDEX & {\bf{ ALTER INDEX}} &  \tn 
% Row Count 8 (+ 2)
% Row 31
\SetRowColor{white}
확인 & {\bf{EXEC sp\_helpindex}} userTbl; &  \tn 
% Row Count 10 (+ 2)
% Row 32
\SetRowColor{LightBackground}
 & SSMS - 도구 옵션 - 쿼리 실행 - SQL Server - 고급 - SET STATISTICS IO & 참조 페이지 수 확인 \tn 
% Row Count 15 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.8923 cm} x{10.2907 cm} p{1.687 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Memory Table}}  \tn
% Row 0
\SetRowColor{LightBackground}
준비 & 기본 키 및 비클러스터형 인덱스 필요. NONCLUSTERED 예약어를 PRIMARY KEY와 함께 사용해야 함. &  \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
 & 1. dataBase 생성 &  \tn 
% Row Count 6 (+ 1)
% Row 2
\SetRowColor{LightBackground}
 & 2. dataBase - 속성 - 파일 그룹 -  메모리 액세스에 최적화된 데이터 - 파일 그룹 추가 &  \tn 
% Row Count 11 (+ 5)
% Row 3
\SetRowColor{white}
 & 3. dataBase - 속성 - 파일 - 추가 - 논리적 이름 만들기(임의) - '파일 형식'은 DILESTREAM 데이터로 지정 - '파일 그룹'은 2번 그룹과 동일해야 함. &  \tn 
% Row Count 19 (+ 8)
% Row 4
\SetRowColor{LightBackground}
CREATE TABLE & {\bf{CREATE TABLE}} {\emph{memoryTbl}} (a INT {\bf{PRIMARY KEY NONCLUSTERED}}, b NCHAR(100)) {\bf{WHTH (MEMORY\_OPTIMIZED=ON)}}; &  \tn 
% Row Count 24 (+ 5)
% Row 5
\SetRowColor{white}
CREATE PROCEDURE & {\bf{CREATE PROCEDURE}} usp\_diskInsert\{\{nl\}\}@data NCHAR(100)\{\{nl\}\}{\bf{AS}}\{\{nl\}\} ~~{\bf{DECLARE}} @I INT = 1;  \{\{nl\}\}~~{\bf{WHILE}} @i \textless{}= 500 \{\{nl\}\}~~{\bf{BEGIN}} \{\{nl\}\}~~ ~~{\bf{INSERT INTO}} dbo.diskTable(a, b) {\bf{VALUES}} (@i, @data); \{\{nl\}\}~~ ~~{\bf{SET}} @I += 1;\{\{nl\}\} ~~{\bf{END}} &  \tn 
% Row Count 39 (+ 15)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.8923 cm} x{10.2907 cm} p{1.687 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{Memory Table (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
CREATE PROCEDURE & {\bf{CREATE PROCEDURE}} usp\_memoryInsert\{\{nl\}\}@data NCHAR(100)\{\{nl\}\}{\bf{WITH NATIVE\_COMPLATION, SCHEMABINDING}}\{\{nl\}\}{\bf{AS}}\{\{nl\}\}{\bf{BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'Korea')}}\{\{nl\}\}~~{\bf{DECLARE}} @I INT = 1;  \{\{nl\}\}~~{\bf{WHILE}} @i \textless{}= 500 \{\{nl\}\}~~{\bf{BEGIN}} \{\{nl\}\}~~ ~~{\bf{INSERT INTO}} dbo.diskTable(a, b) {\bf{VALUES}} (@i, @data); \{\{nl\}\}~~ ~~{\bf{SET}} @I += 1;\{\{nl\}\} ~~{\bf{END\{\{nl\}\}END}} &  \tn 
% Row Count 21 (+ 21)
% Row 7
\SetRowColor{white}
 & {\bf{DECLARE}} @sendData nchar(100) = REPLICATE(N'가', 100);\{\{nl\}\}{\bf{EXECUTE}} usp\_diskIsert @sendDatal &  \tn 
% Row Count 26 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{6.4106 cm} x{6.2419 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DML - Data Manipulation Language}}  \tn
% Row 0
\SetRowColor{LightBackground}
BEGIN \seqsplit{TRANSACTION} & {\bf{BEGIN TRANSACTION}};  /  START TRANSACTION; & SQL Server / MySQL Only \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
INSERT INTO - VALUES & {\bf{INSERT  INTO}} Goods {\bf{VALUES (}}'0001', '의류', 1000, '2019-09-20'{\bf{)}} & 열 리스트 생략 가능 \tn 
% Row Count 9 (+ 6)
% Row 2
\SetRowColor{LightBackground}
 & {\bf{INSERT  INTO}} Goods {\bf{VALUES (}}'0001', '의류', NULL, '2019-09-20'{\bf{)}} & NULL 직접 기술 \tn 
% Row Count 15 (+ 6)
% Row 3
\SetRowColor{white}
 & {\bf{INSERT  INTO}} Goods {\bf{VALUES (}}'0001', '의류', 1000, '2019-09-20'{\bf{)}}, {\bf{(}}'0002', '사무', 500, '2009-02–03'{\bf{)}}; & 복수 행 삽입 \tn 
% Row Count 25 (+ 10)
% Row 4
\SetRowColor{LightBackground}
 & {\bf{INSERT  INTO}} Goods {\bf{VALUES (}}'0001', '의류', DEFAULT, '2019-09-20'{\bf{)}} & DEFALUT 직접 기술 추천 \tn 
% Row Count 31 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{6.4106 cm} x{6.2419 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DML - Data Manipulation Language (cont)}}  \tn
% Row 5
\SetRowColor{LightBackground}
 & -{}- 자동 생성 순번은 입력하면 안된다. &  \tn 
% Row Count 4 (+ 4)
% Row 6
\SetRowColor{white}
 & {\bf{INSERT INTO}} Goods(id, name) {\bf{VALUES(}}0001, '의류'); &  \tn 
% Row Count 8 (+ 4)
% Row 7
\SetRowColor{LightBackground}
 & -{}- NOT NULL 컬럼을 제외하고 특정 컬럼 지정하여 값을 넣을 수 있다. &  \tn 
% Row Count 14 (+ 6)
% Row 8
\SetRowColor{white}
INSERT INTO - SELECT - FROM & {\bf{INSERT INTO}} NewTable {\bf{(}}id, name, price{\bf{) SELECT}} id, name, price {\bf{FROM}} Goods;\{\{nl\}\}-{}- 입력할 테이블이 미리 만들어져 있어야 한다. & 다른 \seqsplit{테이블에서} 데이터 복사 \tn 
% Row Count 25 (+ 11)
% Row 9
\SetRowColor{LightBackground}
 & {\bf{INSERT INTO}} GoodClassify {\bf{(}}classify, sum\_price{\bf{) SELECT}} classify, SUM(price) {\bf{FROM}} Goods {\bf{GROUP BY}} classify; &  \tn 
% Row Count 34 (+ 9)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{6.4106 cm} x{6.2419 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DML - Data Manipulation Language (cont)}}  \tn
% Row 10
\SetRowColor{LightBackground}
 & -{}- INSERT 내 SELECT 문에는 WHERE, GROUP BY 등 모두 사용 가는. ORDER BY는 효과 없음 &  \tn 
% Row Count 7 (+ 7)
% Row 11
\SetRowColor{white}
 & {\bf{SELECT}} classify, sum\_price {\bf{INTO}} NewTable {\bf{FROM}} Goods;\{\{nl\}\}-{}- 새로 생성할 테이블 자동으로 만들어 진다. & 위와 동일함 \tn 
% Row Count 16 (+ 9)
% Row 12
\SetRowColor{LightBackground}
SET \seqsplit{IDENTUTY\_INSERT} - OFF & {\bf{SET IDENTITY\_INSERT}} Tbl1 {\bf{ON}};\{\{nl\}\}{\bf{INSERT INTO}} Goods(num, id, name)  {\bf{VALUE}}(11, 0001, '의류'); & 강제로 바꾸로 싶을 때 사용 \{\{nl\}\}num을 지정하지 않으면 error 발생함. \tn 
% Row Count 24 (+ 8)
% Row 13
\SetRowColor{white}
SET \seqsplit{IDENTUTY\_INSERT} - ON & {\bf{SET IDENTITY\_INSERT}} Tbl1 {\bf{OFF}}; & 끝나면 다시 OFF. 12부터 시작 함. \tn 
% Row Count 27 (+ 3)
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{} \tn 
% Row Count 27 (+ 0)
% Row 15
\SetRowColor{white}
SELECT \seqsplit{IDENT\_CURRENT} & {\bf{SELECT IDENT\_CURRENT}}({\bf{'}}Goods{\bf{'}}); & 현재의 IDENTITY 값, ( ' ) 필요 \tn 
% Row Count 30 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{6.4106 cm} x{6.2419 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DML - Data Manipulation Language (cont)}}  \tn
% Row 16
\SetRowColor{LightBackground}
SELECT \seqsplit{@@IDENTITY} & SELECT @@IDENTITY & 현재 쿼리창의 가장 최근 IDENTITY 값 \tn 
% Row Count 4 (+ 4)
% Row 17
\SetRowColor{white}
COMMIT TRAN & {\bf{COMMIT TRAN}}; & SQL Server \tn 
% Row Count 6 (+ 2)
% Row 18
\SetRowColor{LightBackground}
 & {\bf{COMMIT}}; &  \tn 
% Row Count 7 (+ 1)
% Row 19
\SetRowColor{white}
UPDATE - SET & {\bf{UPDATE}} Goods {\bf{SET}} date = '2009-1-2'; & 주의. 선택된 컬럼 전체 변경. 잘 사용 안함. \tn 
% Row Count 12 (+ 5)
% Row 20
\SetRowColor{LightBackground}
UPDATE - SET - WHERE & {\bf{UPDATE}} Goods {\bf{SET}} peice = price * 10 {\bf{WHERE}} classify = '사무'; &  \tn 
% Row Count 18 (+ 6)
% Row 21
\SetRowColor{white}
 & {\bf{UPDATE}} Goods {\bf{SET}} date = {\bf{NULL WHERE}} id = '0008'; & NULL 클리어.  NOT NULL 제약 없어야 함. \tn 
% Row Count 23 (+ 5)
% Row 22
\SetRowColor{LightBackground}
 & {\bf{UPDATE}} Goods {\bf{SET}} peicd = price * 10{\bf{,}} date = '2019-01-02'  {\bf{WHERE}} classify = '사무'; & 복수열 갱신 \tn 
% Row Count 31 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{6.4106 cm} x{6.2419 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DML - Data Manipulation Language (cont)}}  \tn
% Row 23
\SetRowColor{LightBackground}
TRUNCATE TABLE & {\bf{TRUNCATE TABLE}} Goods; & Table 내 모든 \seqsplit{레토드(만)} 삭제, DELETE 보다 빠름. \tn 
% Row Count 5 (+ 5)
% Row 24
\SetRowColor{white}
DELETE FROM & {\bf{DELETE FROM}} Goods; & \textless{}주의\textgreater{} Table 내 레코드만 삭제 \tn 
% Row Count 8 (+ 3)
% Row 25
\SetRowColor{LightBackground}
DELETE FROM - WHERE & {\bf{DELETE FROM}} Goods {\bf{WHERE}} price \textgreater{}= 4000; & WHERE 구문만 가능 \tn 
% Row Count 12 (+ 4)
% Row 26
\SetRowColor{white}
DELETE TOP( ) FROM - WHERE & {\bf{DELETE TOP}}(10) {\bf{FROM}} Goods {\bf{WHERE}} name = 'Kim'; & 임의 순서로 삭제됨. \tn 
% Row Count 16 (+ 4)
% Row 27
\SetRowColor{LightBackground}
 & {\bf{DELETE FROM}} Goods {\bf{WHERE}} {\emph{id}} {\bf{IN}} ({\bf{SELECT TOP}}(10) {\emph{id}} {\bf{FROM}} Goods {\bf{ORDER BY}} price); & 기본 key를 지정해야 함. \tn 
% Row Count 23 (+ 7)
% Row 28
\SetRowColor{white}
MERGE & {\bf{MERGE}} memberTBL AS M & 변경될 테이블 \tn 
% Row Count 25 (+ 2)
% Row 29
\SetRowColor{LightBackground}
USING & {\bf{USING}} changeTbl AS C & 변경할 기준이 되는 테이블 \tn 
% Row Count 28 (+ 3)
% Row 30
\SetRowColor{white}
ON & {\bf{ON}} M.userID = C.userID & 두 테이블 비교할 기준 \tn 
% Row Count 31 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{6.4106 cm} x{6.2419 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DML - Data Manipulation Language (cont)}}  \tn
% Row 31
\SetRowColor{LightBackground}
WHEN & {\bf{WHEN NOT MATCHED AND}} changeType = '신규가입' {\bf{THEN}} &  \tn 
% Row Count 5 (+ 5)
% Row 32
\SetRowColor{white}
 & ..........{\bf{INSERT}}(userID, name, addr) {\bf{VALUES}}(C.userID, C.name, C.addr) &  \tn 
% Row Count 11 (+ 6)
% Row 33
\SetRowColor{LightBackground}
 & {\bf{WHEN MATCHED AND}} changeType = '주소변경' {\bf{THEN}} &  \tn 
% Row Count 15 (+ 4)
% Row 34
\SetRowColor{white}
 & ..........{\bf{UPDATE SET}} M.addr = C.addr &  \tn 
% Row Count 18 (+ 3)
% Row 35
\SetRowColor{LightBackground}
 & {\bf{WHEN MATCJED AND}} changeType = '회원탈퇴' {\bf{THEN}} &  \tn 
% Row Count 22 (+ 4)
% Row 36
\SetRowColor{white}
 & ..........{\bf{DELETE}}; &  \tn 
% Row Count 24 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.1086 cm} x{14.1614 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{WITH - Common Table Expression}}  \tn
% Row 0
\SetRowColor{LightBackground}
Syntex & 임시 테이블을 만들어서 기 테이블의 값을 다시 SELECT로 사용. \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
WITH - AS & {\bf{WITH}} cte\_Tbl1(addr, maxHeight) {\bf{AS (SELECT}} addr, {\bf{MAX}}(height) {\bf{FROM}} Tbl1 {\bf{GROUP BY}} addr{\bf{)}} \tn 
% Row Count 7 (+ 4)
% Row 2
\SetRowColor{LightBackground}
 & ..........{\bf{SELECT AVG}}(maxHeight*1.0) {\bf{AS}} '지역별 최고 키' {\bf{FROM}} cte\_Tbl1; \tn 
% Row Count 10 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.7114 cm} x{6.5793 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{SELECT}}  \tn
% Row 0
\SetRowColor{LightBackground}
Syntex & 반환하는 결과값은 무작위 순이다. 메모리에 임시 생성하고 버린다. &  \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
 & {\bf{SELECT}} 열 이름 {\bf{FROM}} 테이블 이름 {\bf{WHERE}} 행 필터; &  \tn 
% Row Count 11 (+ 5)
% Row 2
\SetRowColor{LightBackground}
SELECT & {\bf{SELECT * FROM}} Goods; &  \tn 
% Row Count 13 (+ 2)
% Row 3
\SetRowColor{white}
 & SELECT id, name FROM Goods; &  \tn 
% Row Count 15 (+ 2)
% Row 4
\SetRowColor{LightBackground}
-{}- AS & {\bf{SELECT}} id, name {\bf{AS}} nm {\bf{FROM}} Goods; &  \tn 
% Row Count 18 (+ 3)
% Row 5
\SetRowColor{white}
 & {\bf{SELECT}} id {\bf{AS "}}아이디{\bf{"}}, name AS {\bf{"}}이름{\bf{" FROM}} Goods; & 한글 별명 \tn 
% Row Count 23 (+ 5)
% Row 6
\SetRowColor{LightBackground}
 & {\bf{SELECT '}}상품{\bf{' AS "}}구분{\bf{"}}, 10 {\bf{AS}} num, {\bf{'}}2009-02-24{\bf{' AS}} date {\bf{FROM}} Goods; & COLUMN에 상수 할당 \tn 
% Row Count 30 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.7114 cm} x{6.5793 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{SELECT (cont)}}  \tn
% Row 7
\SetRowColor{LightBackground}
 & - 구분, num, date 컬럼 생성하고 값으로 '상물, 10, '209-02-24'를 모든 행에 추가한다. &  \tn 
% Row Count 8 (+ 8)
% Row 8
\SetRowColor{white}
-{}- \seqsplit{DISTINCT} & {\bf{SELECT DISTINCT}} classify, date {\bf{FROM}} Goods; & 해당 열에서 중복 제거하고 표시 \tn 
% Row Count 12 (+ 4)
% Row 9
\SetRowColor{LightBackground}
-{}- TOP() & {\bf{SELECT TOP(10)}} CreditCardID {\bf{FROM}} Sales {\bf{WHERE}}  Type = 'Vista' {\bf{ORDER BY}} ExpYear; &  \tn 
% Row Count 19 (+ 7)
% Row 10
\SetRowColor{white}
 & {\bf{SELECT TOP}}({\bf{SELECT}} COUNT(*)/100 {\bf{FROM}} Sales) CreditCardID FROM Sales {\bf{WHERE}}  Type = 'Vista' {\bf{ORDER BY}} ExpYear; &  \tn 
% Row Count 28 (+ 9)
% Row 11
\SetRowColor{LightBackground}
-{}- TOP( ) PERCENT & {\bf{SELECT TOP(0.1) PERCENT}} CreditCardID {\bf{FROM}} Sales {\bf{WHERE}}  Type = 'Vista' {\bf{ORDER BY}} ExpYear; & 동률 출력 안함 \tn 
% Row Count 35 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.7114 cm} x{6.5793 cm} x{6.5793 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{SELECT (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
-{}- TOP( ) WITH TIES & {\bf{SELECT TOP(0.1) PERCENT WITH TIES}} CreditCardID {\bf{FROM}} Sales {\bf{WHERE}}  Type = 'Vista' {\bf{ORDER BY}} ExpYear; & 동률 전부 출력 \tn 
% Row Count 8 (+ 8)
% Row 13
\SetRowColor{white}
-{}- INTO & TABLE을 새로 생성한다. Primary Key, Foreign Key는 복사되지 않는다. &  \tn 
% Row Count 14 (+ 6)
% Row 14
\SetRowColor{LightBackground}
 & {\bf{SELECT}} * {\bf{INTO}} Tbl2 {\bf{FROM}} Tbl1; &  \tn 
% Row Count 17 (+ 3)
% Row 15
\SetRowColor{white}
 & {\bf{SELECT}} ID, name {\bf{INTO}} Tbl2 {\bf{FROM}} Tbl1; &  \tn 
% Row Count 21 (+ 4)
% Row 16
\SetRowColor{LightBackground}
FROM & {\bf{SLEECT}} * {\bf{FROM}} Sales {\bf{TABLESAMPLE( 5 ROWS)}}; & 샘플 무작위 생성 \tn 
% Row Count 25 (+ 4)
% Row 17
\SetRowColor{white}
-{}- \seqsplit{TABLESAMPLE(} 5 ROWS) & {\bf{SLEECT TOP(500)}} * {\bf{FROM}} Sales {\bf{TABLESAMPLES( 5 PERCENT)}}; &  \tn 
% Row Count 30 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.6992 cm} x{7.0854 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{WHERE 술어}}  \tn
% Row 0
\SetRowColor{LightBackground}
LIKE & {\bf{SELECT}} * {\bf{FROM}} Sample {\bf{WHERE}} srt {\bf{LIKE}} '\_\_ddd{\bf{\%}}'; &  \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
 & -{}- str 컬럼에 '두 글자 + 중간에 ddd + 마지막 임의의 문자열'이 있는 문자열 &  \tn 
% Row Count 12 (+ 7)
% Row 2
\SetRowColor{LightBackground}
 & \textless{}{\bf{CAUTION}}\textgreater{} {\bf{SELECT}} * {\bf{FROM}} Sample {\bf{WHERE}} srt {\bf{=}} 'ddd{\bf{\%}}'; & 특수문자 사용하는 곳에 '=' 사용하면 결과가 없는 것으로 나옴. \tn 
% Row Count 18 (+ 6)
% Row 3
\SetRowColor{white}
IS NULL & {\bf{SELECT}} * {\bf{FROM}}  Goods {\bf{WHERE}} price {\bf{IS NULL}}; & price가 NULL인 column \tn 
% Row Count 22 (+ 4)
% Row 4
\SetRowColor{LightBackground}
IS NOT NULL & {\bf{SELECT}} * {\bf{FROM}}  Goods {\bf{WHERE}} price {\bf{IS NOT NULL}}; & price가 NULL이 아닌 column \tn 
% Row Count 26 (+ 4)
% Row 5
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{} \tn 
% Row Count 26 (+ 0)
% Row 6
\SetRowColor{LightBackground}
AND & {\bf{SELECT}} name, classify {\bf{FROM}} Goods {\bf{WHERE}} classfy {\bf{!=}} '의류' {\bf{AND}} price \textgreater{}= 1000; & FROM 뒤 \tn 
% Row Count 33 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.6992 cm} x{7.0854 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{WHERE 술어 (cont)}}  \tn
% Row 7
\SetRowColor{LightBackground}
OR & {\bf{SELECT name, classify }}FROM{\bf{ Goods }}WHERE{\bf{ classfy = '의류' }}OR** price \textgreater{}= 1000; &  \tn 
% Row Count 6 (+ 6)
% Row 8
\SetRowColor{white}
\seqsplit{BETWEEN} & {\bf{SELECT}} * {\bf{FROM}}  Goods {\bf{WHERE}} price {\bf{BETWEEN}} 100 {\bf{AND}} 1000 & 처음과 끝 포함 \tn 
% Row Count 11 (+ 5)
% Row 9
\SetRowColor{LightBackground}
\seqsplit{IN(=or)} & {\bf{SELECT}} * {\bf{FROM}}  Goods {\bf{WHERE}} price {\bf{IN(}}320, 500{\bf{)}} & 320 or 500이 있는 column. NULL 안됨 \tn 
% Row Count 16 (+ 5)
% Row 10
\SetRowColor{white}
NOR IN & {\bf{SELECT}} * {\bf{FROM}}  Goods {\bf{WHERE}} price {\bf{NOT IN(}}320, 500{\bf{)}} & 320이나 500이 없는 column. NULL 안됨. \tn 
% Row Count 21 (+ 5)
% Row 11
\SetRowColor{LightBackground}
\seqsplit{Subscript} & SELECT name, Height FROM userTBL {\bf{WHERE}} height \textgreater{} {\bf{(SELECT}} height FROM userTBL {\bf{WHERE}} Name = '김경호'{\bf{)}}; &  \tn 
% Row Count 29 (+ 8)
% Row 12
\SetRowColor{white}
-{}- ANY & {\bf{SELECT}} name, height {\bf{FROM}} userTBL {\bf{WHERE}} height = {\bf{ ANY (SELECT}} height FROM userTBL {\bf{WHERE}} add = '경남'{\bf{)}}; &  \tn 
% Row Count 37 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.6992 cm} x{7.0854 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{WHERE 술어 (cont)}}  \tn
% Row 13
\SetRowColor{LightBackground}
-{}- ALL & {\bf{SELECT}} name, height {\bf{FROM}} userTBL {\bf{WHERE}} height \textgreater{} {\bf{ ALL (SELECT}} height FROM userTBL {\bf{WHERE}} add = '경남'{\bf{)}}; &  \tn 
% Row Count 8 (+ 8)
% Row 14
\SetRowColor{white}
-{}- IN & {\bf{SELECT}} name, price {\bf{FROM}} Goods {\bf{WHERE}} id {\bf{IN}}({\bf{SELECT}} id {\bf{FROM}} Store {\bf{WHERE}} store\_id = '부산'); &  \tn 
% Row Count 16 (+ 8)
% Row 15
\SetRowColor{LightBackground}
- \seqsplit{EXISTS} \seqsplit{술어} & {\bf{SELECT}} name, price FROM Goods AS S {\bf{WHERE EXISTS (SELECT}} * {\bf{FROM}} Store AS TS {\bf{WHERE}} TS.id = '부산' {\bf{AND}} TS.id = S.id{\bf{)}}; &  \tn 
% Row Count 25 (+ 9)
% Row 16
\SetRowColor{white}
 & -{}- EXISTS 인수는 항상 상관 서브쿼리로 작성해야 함. &  \tn 
% Row Count 30 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.6992 cm} x{8.0976 cm} x{6.0732 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{GROUP BY}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Syntex} & 집약 KEY에 NULL이 포함되면 결과에도 \seqsplit{불명(공란)으로} 존재한다. &  \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
 & SELECT 구에 상수, 집약 함수, GROUP BY에서 사용한 집약 키 외의 열명을 쓸 수 없다.  집약키와 1:1 대응 가능해야 한다. &  \tn 
% Row Count 13 (+ 8)
% Row 2
\SetRowColor{LightBackground}
 & SELECT 구에서 지정한 별명을 사용할 수 없다. 별명 순서가 나중 이므로. &  \tn 
% Row Count 18 (+ 5)
% Row 3
\SetRowColor{white}
 & 결과가 sort된 것이 아니다. order by 사용해야 한다. &  \tn 
% Row Count 22 (+ 4)
% Row 4
\SetRowColor{LightBackground}
 & WHERE 구문에서 집약함수로 조건지은 후 GROUP BY을 사용할 수 없다. - HAVING 사용 &  \tn 
% Row Count 28 (+ 6)
% Row 5
\SetRowColor{white}
GROUP BY & {\bf{SELECT}} userID,  {\bf{SUM}}(amount) {\bf{AS}} '총 구매 개수' {\bf{FROM}} Goods {\bf{GROUP BY}} userID; & NULL 값의 개수도 집계됨 \tn 
% Row Count 34 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.6992 cm} x{8.0976 cm} x{6.0732 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{GROUP BY (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
 & {\bf{SELECT}} userID,  {\bf{SUM}}(amount*price) {\bf{AS}} '총 구매액' {\bf{FROM}} Goods {\bf{GROUP BY}} userID; &  \tn 
% Row Count 6 (+ 6)
% Row 7
\SetRowColor{white}
 & {\bf{SELECT}} userID,  {\bf{SUM}}(amount*price) {\bf{AS}} '총 구매액' {\bf{FROM}} Goods {\bf{WHERE}} classify = '의류' {\bf{GROUP BY}} userID; &  \tn 
% Row Count 13 (+ 7)
% Row 8
\SetRowColor{LightBackground}
-{}- \seqsplit{ROLLUP} & {\bf{SELECT}} classity, {\bf{SUM}}(amount*price) {\bf{AS}} '총 구매액' {\bf{FROM}} Goods {\bf{GROUP BY ROLLUP}}(classify); & 소합계, 총합계만 \tn 
% Row Count 19 (+ 6)
% Row 9
\SetRowColor{white}
 & {\bf{SELECT}} userID, classify, {\bf{SUM}}(amount*price) {\bf{AS}} '총 구매액' {\bf{FROM}} Goods {\bf{GROUP BY ROLLUP}}(classify, userID); &  \tn 
% Row Count 26 (+ 7)
% Row 10
\SetRowColor{LightBackground}
 & -{}- classify별 각 userID의  '총 구매액'과 classify 별 소합계,  마지막 총합계 &  \tn 
% Row Count 31 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.6992 cm} x{8.0976 cm} x{6.0732 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{GROUP BY (cont)}}  \tn
% Row 11
\SetRowColor{LightBackground}
-{}- CUBE & {\bf{SELECT}} productName, color, {\bf{SUM}}(amount) {\bf{AS}} '수량합계' {\bf{FROM}} Tbl1 {\bf{GROPU BY CUBE}}(color, productName); &  \tn 
% Row Count 7 (+ 7)
% Row 12
\SetRowColor{white}
 & -{}- 제품별 소합계, 총합계, 색상별 소합계 &  \tn 
% Row Count 10 (+ 3)
% Row 13
\SetRowColor{LightBackground}
-{}- \seqsplit{GROUPING\_ID} & {\bf{SELECT}} groupName, {\bf{SUM}}(price*amount) {\bf{AS}} '비용', {\bf{GROUPING\_ID}}(groupName) {\bf{AS}} '추가행 여부' {\bf{FROM}} Tbl1 {\bf{GROUP BY ROLLUP}}(gropuName); &  \tn 
% Row Count 19 (+ 9)
% Row 14
\SetRowColor{white}
 & -{}- 총합계 표시를 위해 추가된 행에 '1' 표시 &  \tn 
% Row Count 22 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.2175 cm} x{10.9655 cm} p{1.687 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{HAVING}}  \tn
% Row 0
\SetRowColor{LightBackground}
작성 순서 & SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY &  \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
Syntex & WHERE 구는 '행'에 대한 조건을 지정, HAVING 구는 '그룹'에 대한 조건을 지정. &  \tn 
% Row Count 7 (+ 4)
% Row 2
\SetRowColor{LightBackground}
 & 집약 KEY에 대한 조건은 HAVING이 아닌 WHERE 구에 작성한다. &  \tn 
% Row Count 10 (+ 3)
% Row 3
\SetRowColor{white}
 & HAVING 구에는 상수, 집약 함수, GROUP BY에서 사용한 집약 KEY만 사용 가능. &  \tn 
% Row Count 14 (+ 4)
% Row 4
\SetRowColor{LightBackground}
HAVING & {\bf{SELECT}} classify, COUNT(*) {\bf{FROM}} Goods {\bf{GROUP BY}} classify {\bf{HAVING}} COUNT(*) = 2; &  \tn 
% Row Count 18 (+ 4)
% Row 5
\SetRowColor{white}
 & {\bf{SELECT}} classify, AVG(price{\bf{*1.0}}) {\bf{FROM}} Goods {\bf{GROUP BY}} classify {\bf{HAVING}} AVG(price{\bf{*1.0}}) \textgreater{}= 2500; &  \tn 
% Row Count 23 (+ 5)
% Row 6
\SetRowColor{LightBackground}
 & {\bf{SELECT}} classify, AVG(price{\bf{*1.0}}) {\bf{FROM}} Goods {\bf{WHERE}}  store = '부산' {\bf{GROUP BY}} classify {\bf{HAVING}} AVG(price{\bf{*1.0}}) \textgreater{}= 2500; &  \tn 
% Row Count 29 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{ORDER BY}}  \tn
% Row 0
\SetRowColor{LightBackground}
Syntex & SELECT 구문의 가장 마지막에 기술 &  \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
 & {\bf{\textless{}CAUTION\textgreater{}}}  성능이 떨어지므로 가급적 사용하지 말 것. &  \tn 
% Row Count 8 (+ 5)
% Row 2
\SetRowColor{LightBackground}
 & 소트 KEY에 NULL이 포함되어 있는 경우 제일 처음 또는 제일 마지막에 모아서 표시된다. &  \tn 
% Row Count 16 (+ 8)
% Row 3
\SetRowColor{white}
 & SELECT 구에 부여한 별명 사용 가능. &  \tn 
% Row Count 19 (+ 3)
% Row 4
\SetRowColor{LightBackground}
 & SELECT 구에 포함되지 않는 열이나 집약합수도 사용 가능. 별명도 가능. &  \tn 
% Row Count 25 (+ 6)
% Row 5
\SetRowColor{white}
ORDER BY & SELECT id, name, price FROM Goods {\bf{ORDER BY}} price {\bf{DESC}}; & 내림차순 \tn 
% Row Count 29 (+ 4)
% Row 6
\SetRowColor{LightBackground}
 & SELECT id, name, price FROM Goods {\bf{ORDER BY}} price DESC, id; & 복수 정렬 \tn 
% Row Count 33 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{ORDER BY (cont)}}  \tn
% Row 7
\SetRowColor{LightBackground}
 & SELECT classfy, COUNT(*) FROM Goods {\bf{GROUP BY}} classify {\bf{ORDER BY COUNT}}(*); & 집약함수 사용 가능 \tn 
% Row Count 6 (+ 6)
% Row 8
\SetRowColor{white}
 & SELECT namd {\bf{AS nm}} FROM Goods {\bf{ORDER BY nm}}; & 별명 가능. \tn 
% Row Count 10 (+ 4)
% Row 9
\SetRowColor{LightBackground}
OFFSET / FETCH NEXT & {\bf{SELECT}} ID, nsme {\bf{FROM}} userTBL {\bf{ORDER BY}} birth {\bf{OFFST}} 4 {\bf{ROWS FETCH NEXT }}3 {\bf{ROWS ONLY}}; & 4줄 건너 뛰고 이 후 3줄만 출력 \tn 
% Row Count 17 (+ 7)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{6.7353 cm} x{10.5347 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{연산자 / 형 변환 / 기타}}  \tn
% Row 0
\SetRowColor{LightBackground}
컬럼 연산 & SELECT name, price * 2 AS "price\_x2" FROM Goods; \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
+= & 누적 \tn 
% Row Count 3 (+ 1)
% Row 2
\SetRowColor{LightBackground}
10\%7 & 나머지 -\textgreater{} 3 \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
\textless{}\textgreater{} & SELECT name, prcice FROM Goods WHERE price {\bf{\textless{}\textgreater{}}} 1000; \tn 
% Row Count 7 (+ 3)
% Row 4
\SetRowColor{LightBackground}
NOT & SELECT name, prcice FROM Goods WHERE {\bf{NOT}} price = 1000; \tn 
% Row Count 10 (+ 3)
% Row 5
\SetRowColor{white}
'1-3' \textless{} '2' & 문자열 대소 비교는 사전식 원칙 \tn 
% Row Count 12 (+ 2)
% Row 6
\SetRowColor{LightBackground}
IS NULL & SELECT name, price FROM Goods WHERE price IS NULL; \tn 
% Row Count 15 (+ 3)
% Row 7
\SetRowColor{white}
IS NOT NULL & SELECT name, price FROM Goods WHERE price IS NOT NULL; \tn 
% Row Count 18 (+ 3)
% Row 8
\SetRowColor{LightBackground}
NULL & NULL을 포함한 계산은 무조건 NULL이 된다. \tn 
% Row Count 21 (+ 3)
% Row 9
\SetRowColor{white}
 & NULL/0 도 NULL이 된다. \tn 
% Row Count 23 (+ 2)
% Row 10
\SetRowColor{LightBackground}
 & 비교 연산에서는 NULL 값 행은 반환되지 않는다. \tn 
% Row Count 26 (+ 3)
% Row 11
\SetRowColor{white}
\mymulticolumn{2}{x{17.67cm}}{\textless{} 형 변환 \textgreater{}} \tn 
% Row Count 27 (+ 1)
% Row 12
\SetRowColor{LightBackground}
CAST( ) & {\bf{SELECT}} price, amount, {\bf{CAST(CAST}}(price {\bf{AS}} FLOAT)/amount {\bf{as}} DECIMAL(10,2)) {\bf{AS}} '단가/수량' {\bf{FROM}} Tbl1; \tn 
% Row Count 33 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{6.7353 cm} x{10.5347 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{연산자 / 형 변환 / 기타 (cont)}}  \tn
% Row 13
\SetRowColor{LightBackground}
 & -{}- 가격을 실수로 바꾼 후 나눠야 정수가 안된다. 이 후 소수점 2자리까지만 표시 \tn 
% Row Count 5 (+ 5)
% Row 14
\SetRowColor{white}
CONVERT( ) & {\bf{SELEC}} ANG({\bf{CONVERT}}(FLOAT, amount)) {\bf{AS}} '평균구매개수' {\bf{FROM}} Tbl1; \tn 
% Row Count 9 (+ 4)
% Row 15
\SetRowColor{LightBackground}
STR( ) & {\bf{SELECT}} STR(123); \tn 
% Row Count 10 (+ 1)
% Row 16
\SetRowColor{white}
PARSE( ) & {\bf{SELECT PARSE}}('2019년 9월 9일' {\bf{AS}} DATE); \tn 
% Row Count 13 (+ 3)
% Row 17
\SetRowColor{LightBackground}
TRY\_PARSE( ) & {\bf{SELECT TRY\_PARSE}}('3.14' {\bf{AS}} INT); \tn 
% Row Count 15 (+ 2)
% Row 18
\SetRowColor{white}
 & -{}- PARSE( ) 는 형이 틀리면 오류를 발생시키나 TRY\_PARSE는 Null을 반환하고 계속 진행한다. \tn 
% Row Count 20 (+ 5)
% Row 19
\SetRowColor{LightBackground}
문자 + 문자 & '100'+'200' -\textgreater{} '100200' \tn 
% Row Count 21 (+ 1)
% Row 20
\SetRowColor{white}
문자 + 숫자 & '100' + 200 -\textgreater{} 300 \tn 
% Row Count 22 (+ 1)
% Row 21
\SetRowColor{LightBackground}
 & '100' + 200.0 -\textgreater{} 300.0 \tn 
% Row Count 23 (+ 1)
% Row 22
\SetRowColor{white}
실수 -\textgreater{} 정수 / DECIMAL & 소수점 자리수가 잘린다. \tn 
% Row Count 25 (+ 2)
% Row 23
\SetRowColor{LightBackground}
숫자 -\textgreater{} 문자 & 문자 자리수가 작으면 ERROR 발생 \tn 
% Row Count 27 (+ 2)
% Row 24
\SetRowColor{white}
\mymulticolumn{2}{x{17.67cm}}{\textless{} 기타 \textgreater{}} \tn 
% Row Count 28 (+ 1)
% Row 25
\SetRowColor{LightBackground}
STATISTICS & SET STATISTICS TIME ON; \tn 
% Row Count 29 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.3862 cm} x{10.7968 cm} p{1.687 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{변수}}  \tn
% Row 0
\SetRowColor{LightBackground}
syntex & 마지막 결과 라인까지 한 번에 실행해야 한다.  한 번 실행되고 없어진다. &  \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
DECLARE - SET & {\bf{DECLARE @}}myVar1 INT, {\bf{@}}myVar2 DECIMAL(5,2), {\bf{@}}myVar3 NCHAR(20);\{\{nl\}\}{\bf{DECLAER @}}point INT = 77;\{\{nl\}\}{\bf{SET @}}myVar1 = 5;\{\{nl\}\}{\bf{SET @}}myVar2 = 4.52;\{\{nl\}\}{\bf{SET @}}myVar3 = '가수 이름 =\textgreater{} ';\{\{nl\}\}{\bf{SET @}} myVar1 = {\bf{GETDATE( )}};\{\{nl\}\}{\bf{SELECT}} @myVar1 = HireDate {\bf{FROM}} Tbl1 {\bf{WHERE}} ID = 111;\{\{nl\}\}{\bf{SELECT @}}myVar1 + {\bf{@}}myVar2;\{\{nl\}\}{\bf{SELECT @}}myVar3, Name {\bf{from}} Tbl1 {\bf{WHERE}} height \textgreater{} 180;\{\{nl\}\}{\bf{SELECT TOP}}({\bf{@}}myVar1) Name, height {\bf{FROM}} Tbl1 {\bf{ORDER BY}} height; &  \tn 
% Row Count 25 (+ 21)
% Row 2
\SetRowColor{LightBackground}
Table 변수 & Table 변수로 생성하면 한 번 실행하고 Table이 없어짐. CREATE TABLE \# 으로 만든 임시 테이블은 쿼리 창 종료 시 까지는 존재 함. &  \tn 
% Row Count 32 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{4.3862 cm} x{10.7968 cm} p{1.687 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{변수 (cont)}}  \tn
% Row 3
\SetRowColor{LightBackground}
 & DECLARE @tblVar TABLE (id CHAR(8), name NVARCHAR(10), addr NCHAR(2));\{\{nl\}\}INSERT INTO @tblVar SELECT userID, name, addr FROM userTbl WHERE birthYear \textgreater{}= 1970;\{\{nl\}\}SELECT * FROM @tblVar; &  \tn 
% Row Count 8 (+ 8)
% Row 4
\SetRowColor{white}
CREATE TABEL \# & CREATE TABLE \#tempTbl (id CHAR(8), name NVARCHAR(10), addr NCHAR(2));\{\{nl\}\}INSERT INTO \#tempTbl SELECT userID, name, addr FROM userTbl WHERE birthYear \textgreater{}= 1970;\{\{nl\}\}SELECT * FROM \#tempTbl; &  \tn 
% Row Count 16 (+ 8)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{7.5915 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DateTime}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{GETDATE(} ) & SELECT GETDATE(); & now \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\seqsplit{SYSDATETIME} ( ) & SELECT SYSDATETIME(); & now(동일) \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{DATEADD} & SELECT DATEADD(day, 100, '2019/01/01'); & 100 일 더하기 \tn 
% Row Count 8 (+ 3)
% Row 3
\SetRowColor{white}
 & SELECT DATEADD(hour, 100, '2019/01/01'); & 100 시간 더하기 \tn 
% Row Count 11 (+ 3)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{DATEDIFF} & SELECT DATEDIFF(week, GETDATE(), '2027/10/9'); & DayOfWeek \tn 
% Row Count 14 (+ 3)
% Row 5
\SetRowColor{white}
\seqsplit{DATEPART} & SELECT DATEPART(year, GETDATE()); & 년도만 \tn 
% Row Count 16 (+ 2)
% Row 6
\SetRowColor{LightBackground}
MONTH & SELECT YEAR(GETDATE()); & \seqsplit{년도만(동일)} \tn 
% Row Count 18 (+ 2)
% Row 7
\SetRowColor{white}
 & -{}- year / month / week / hour / minute / second &  \tn 
% Row Count 21 (+ 3)
% Row 8
\SetRowColor{LightBackground}
\seqsplit{DATENAME} & SELECT DATENAME(weekday, GETDATE()); & DayOfWeek \tn 
% Row Count 23 (+ 2)
% Row 9
\SetRowColor{white}
\seqsplit{DATEFROMPARTS} & SELECT \seqsplit{DATEFROMPARTS('2019'}, '10', '09'); & 문자열로 날짜 생성 \tn 
% Row Count 26 (+ 3)
% Row 10
\SetRowColor{LightBackground}
 & -{}- TIMEFROMPARTS() / \seqsplit{DATETIME2FROMPARTS()} &  \tn 
% Row Count 29 (+ 3)
% Row 11
\SetRowColor{white}
\seqsplit{EOMONTH} & SELECT \seqsplit{EOMONTH('2019-01-03');} & 1월의 마자막 날 \tn 
% Row Count 31 (+ 2)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{7.5915 cm} x{6.4106 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{DateTime (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
 & SELECT EOMONTH(GETDATE(), 3); & 현재 + 3개월의 마지막 날 \tn 
% Row Count 3 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.3618 cm} x{7.2541 cm} x{7.2541 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{수치 / 논리 / Str 연산}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{\textless{} 수치 연산 \textgreater{}} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
ROUND & SELECT ROUND(153.246, 2); & 소수점 \seqsplit{반올림(153.250)} \tn 
% Row Count 3 (+ 2)
% Row 2
\SetRowColor{LightBackground}
 & SELECT ROUND(153.246, -2); & 소수점 반올림(200) \tn 
% Row Count 5 (+ 2)
% Row 3
\SetRowColor{white}
RAND & SELECT RAND(); & 0\textasciitilde{}1까지의 임의의 수 \tn 
% Row Count 7 (+ 2)
% Row 4
\SetRowColor{LightBackground}
FLOOR & SELECT FLOOR(3.14); & 정수 내림(3) \tn 
% Row Count 9 (+ 2)
% Row 5
\SetRowColor{white}
\seqsplit{CEILING} & SELECT CEILING(3.14); & 정수 올림(4) \tn 
% Row Count 11 (+ 2)
% Row 6
\SetRowColor{LightBackground}
ABS & SELECT ABS(-100); &  \tn 
% Row Count 12 (+ 1)
% Row 7
\SetRowColor{white}
SQRT & SELECT SQRT(10); &  \tn 
% Row Count 13 (+ 1)
% Row 8
\SetRowColor{LightBackground}
POWER & SELECT POWER(3, 2); &  \tn 
% Row Count 15 (+ 2)
% Row 9
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{\textless{} 논리 연산 \textgreater{}} \tn 
% Row Count 16 (+ 1)
% Row 10
\SetRowColor{LightBackground}
\seqsplit{CHOOSE} & SELECT CHOOSE(2, 'a', 'b', 'c', 'd'); &  \tn 
% Row Count 19 (+ 3)
% Row 11
\SetRowColor{white}
IIF & SELECT IIF(1\textgreater{}2, 'TRUE', 'FALSE'); & 조건, 참 값, 거짓 값 \tn 
% Row Count 21 (+ 2)
% Row 12
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{\textless{} String 연산 \textgreater{}} \tn 
% Row Count 22 (+ 1)
% Row 13
\SetRowColor{white}
\seqsplit{CHARINDEX} & SELECT \seqsplit{CHARINDEX('Server'}, 'SQL Server 2017'); & 공백포함 시작 위치 -\textgreater{} 5 \tn 
% Row Count 25 (+ 3)
% Row 14
\SetRowColor{LightBackground}
RIGHT / LEFT & SELECT RIGHT('SQL Server 2017', 4); & 오른쪽에서 3 글자 -\textgreater{} '2017' \tn 
% Row Count 28 (+ 3)
% Row 15
\SetRowColor{white}
\seqsplit{SUBSTRING} & SELECT \seqsplit{SUBSTRING('대한민국만세'}, 3, 2); & 3번째부터 2글자 -\textgreater{} '민국' \tn 
% Row Count 31 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.3618 cm} x{7.2541 cm} x{7.2541 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{수치 / 논리 / Str 연산 (cont)}}  \tn
% Row 16
\SetRowColor{LightBackground}
LEN & SELECT LEN('SQL Server 2017'); & 공백 포함 글자 수 -\textgreater{} 15 \tn 
% Row Count 2 (+ 2)
% Row 17
\SetRowColor{white}
LOWER / UPPER & SELECT LOWER('ABDdef'); & 모두 소문자로 \tn 
% Row Count 5 (+ 3)
% Row 18
\SetRowColor{LightBackground}
LTRIM / RTRIM & SELECT LTRIM('  \seqsplit{공백앞뒤두개}  '); & 왼쪽 공백 제거 -\textgreater{} \seqsplit{'공백앞뒤두개}  ' \tn 
% Row Count 8 (+ 3)
% Row 19
\SetRowColor{white}
\seqsplit{REPLACE} & SELECT REPLACE('SQL Server 2017', 'Server', '서버'); & 'SQL 서버 2017' \tn 
% Row Count 12 (+ 4)
% Row 20
\SetRowColor{LightBackground}
\seqsplit{REPLICATE} & SELECT REPLICATE('SQL, 5); & 'SQL' 다섯번 반복 \tn 
% Row Count 14 (+ 2)
% Row 21
\SetRowColor{white}
\seqsplit{REVERSE} & SELECT REVERSE('SQL Server 2017'); & 7102 revreS LQS \tn 
% Row Count 16 (+ 2)
% Row 22
\SetRowColor{LightBackground}
SPACE & SELECT SPACE(5); & 공백 5개 반환 \tn 
% Row Count 18 (+ 2)
% Row 23
\SetRowColor{white}
STR & SELECT STR(123); & '123' \tn 
% Row Count 19 (+ 1)
% Row 24
\SetRowColor{LightBackground}
STUFF & SELECT STUFF('SQL 서버 2017', 5, 2, 'Server'); & 5번째부터 2글자 삭제 \seqsplit{후'Server'집어} 넣기 -\textgreater{} 'SQL Server 2017' \tn 
% Row Count 24 (+ 5)
% Row 25
\SetRowColor{white}
\seqsplit{FORMAT} & SELECT FORMAT(GETDATE(), 'dd/MM/yyyy'); & '16-09-2017' \tn 
% Row Count 27 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.5427 cm} x{6.5793 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{집약 함수}}  \tn
% Row 0
\SetRowColor{LightBackground}
집약 함수 & 복수의 행을 입력받아 하나의 행을 출력 &  \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
 & 모든 집약 함수는 계산 전 NULL을 \seqsplit{제외시킨다}. 단 COUNT(*)만 \seqsplit{예외적으로} NULL 포함. &  \tn 
% Row Count 11 (+ 7)
% Row 2
\SetRowColor{LightBackground}
 & \seqsplit{집약함수는} SELECT, HAVING, ORDER BY에서만 사용 가능하다. &  \tn 
% Row Count 16 (+ 5)
% Row 3
\SetRowColor{white}
 & \seqsplit{집약함수와} 별도의 이름 열을 함께 \seqsplit{사용하려면} 반드시 GROUP BY와 함께 사용해야 한다. &  \tn 
% Row Count 24 (+ 8)
% Row 4
\SetRowColor{LightBackground}
COUNT( ) & {\bf{SELECT COUNT}}(*) {\bf{FROM}} Goods; & NULL 값 포함 모든 행. 단순히 모든 Data 행의 개수 반환. \tn 
% Row Count 29 (+ 5)
% Row 5
\SetRowColor{white}
 & SELECT {\bf{COUNT}}(price) {\bf{FROM}} Goods; & NULL 값 제외 \tn 
% Row Count 32 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.5427 cm} x{6.5793 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{집약 함수 (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
-{}- \seqsplit{COUNT(DISTINCT}...) & {\bf{SELECT COUNT}}({\bf{DISTINCT}} classify) {\bf{FROM}} Goods; & COUNT 안에 사용해야 함. \tn 
% Row Count 4 (+ 4)
% Row 7
\SetRowColor{white}
\seqsplit{COUNT\_BIG(} ) & {\bf{SELECT COUNT\_BIG}}({\bf{DISTINCT}} classify) {\bf{FROM}} Goods; & 결과값이 BIGINT 형(21억개 이상인 경우 사용) \tn 
% Row Count 8 (+ 4)
% Row 8
\SetRowColor{LightBackground}
SUM( ) & {\bf{SELECT SUM}}(amount) {\bf{FROM}} Goods; &  \tn 
% Row Count 11 (+ 3)
% Row 9
\SetRowColor{white}
 & {\bf{SELECT}} classify, {\bf{SUM}}(amount) {\bf{AS}} '합계' {\bf{GROUP BY}} classify; &  \tn 
% Row Count 16 (+ 5)
% Row 10
\SetRowColor{LightBackground}
AVG( ) & {\bf{SELECT AVG}}(price) {\bf{FROM}} Goods; & NULL 행은 분모 Count에서 제외 \tn 
% Row Count 19 (+ 3)
% Row 11
\SetRowColor{white}
 & {\bf{\textless{}CAUTION\textgreater{}}} : \seqsplit{정수형으로} 지정된 Data를 평균하면 결과 값도 정수형 소수점 때고 나온다. TRUNC와 같이. &  \tn 
% Row Count 28 (+ 9)
% Row 12
\SetRowColor{LightBackground}
 & {\bf{SELECT AVG}}(price{\bf{*1.0}}) {\bf{FROM}} Goods; &  \tn 
% Row Count 32 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.5427 cm} x{6.5793 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{집약 함수 (cont)}}  \tn
% Row 13
\SetRowColor{LightBackground}
 & {\bf{SELECT AVR}}({\bf{CAST}}(price {\bf{AS DECIMAL(10,6)}})) {\bf{AS}} '평균가' {\bf{FROM}} Tbl1; & 총 10자리 중 소숫점 6자리로 표시 \tn 
% Row Count 6 (+ 6)
% Row 14
\SetRowColor{white}
STDEV( ) & {\bf{SELECT STDEV}}(return) {\bf{FROM}} Stock; & 표준편차 \tn 
% Row Count 9 (+ 3)
% Row 15
\SetRowColor{LightBackground}
VAR( ) & {\bf{SELECT VAR}}(return) {\bf{FROM}} Stock; & 분산 \tn 
% Row Count 12 (+ 3)
% Row 16
\SetRowColor{white}
MAX( ). MIN( ) & {\bf{SELECT MAX}}(date), {\bf{MIN}}(date) {\bf{FROM}} Goods; &  \tn 
% Row Count 16 (+ 4)
% Row 17
\SetRowColor{LightBackground}
 & {\bf{SELECT}} name, height {\bf{FROM}} Tbl1 {\bf{WHERE}} height = {\bf{(SELECT MAX}}(height) {\bf{FROM}} Tbl1{\bf{)}} ; &  \tn 
% Row Count 23 (+ 7)
% Row 18
\SetRowColor{white}
 & -{}- Tbl1의 가장 큰 height을 가진  레코드의 name, height &  \tn 
% Row Count 28 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{순위 / 분석 함수}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{ROW\_NUMBER(} ) & SELECT {\bf{ROW\_NUMBER( ) OVER}} ({\bf{ORDER BY}} height DESC, name ASC) '키큰순위', name, addr, height FROM Tbl; & 1-2-3-4 \tn 
% Row Count 7 (+ 7)
% Row 1
\SetRowColor{white}
 & SELECT addr, {\bf{ROW\_NUMBER( ) OVER}} ({\bf{PARTITION BY}} addr {\bf{ORDER BY}} height DESC, name ASC){[}지역별 키큰순위{]}, name, height FROM Tbl; &  \tn 
% Row Count 16 (+ 9)
% Row 2
\SetRowColor{LightBackground}
 & SELECT top(10) * \{\{nl\}\}~~~~FROM (SELECT *, ROW\_NUMBER( ) OVER(PARTITION BY stockCode ORDER BY logDate DESC, logTime DESC) AS rnum \{\{nl\}\}~~~~~~~~FROM LogMinute) AS l2 \{\{nl\}\}~~~~WHERE rnum = 1; &  \tn 
% Row Count 33 (+ 17)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{순위 / 분석 함수 (cont)}}  \tn
% Row 3
\SetRowColor{LightBackground}
RANK( ) & SELECT {\bf{RANK( ) OVER}} ({\bf{ORDER BY}} height DESC){[}키큰순위{]}, name, addr, height FROM Tbl; 1-2-2-4 & 1-2-2-4 \tn 
% Row Count 7 (+ 7)
% Row 4
\SetRowColor{white}
\seqsplit{DENSE\_RANK(} ) & SELECT {\bf{DENSE\_RANK( ) OVER}} ({\bf{ORDER BY}} height DESC){[}키큰순위{]}, name, addr, height FROM Tbl; & 1-2-2-3 \tn 
% Row Count 14 (+ 7)
% Row 5
\SetRowColor{LightBackground}
\seqsplit{NTILE(} ) & SELECT {\bf{NTILE(4) OVER}} ({\bf{ORDER BY}} height DESC){[}반번호{]}, name, addr, height FROM Tbl; & 키 순으로 4개의 그룹으로 나눔(1,2반은 3명씩) \tn 
% Row Count 20 (+ 6)
% Row 6
\SetRowColor{white}
LEAD( ) OVER & SELECT name, addr, height, height - ({\bf{LEAD}}(height, 1, 0) {\bf{OVER}} ({\bf{ORDER BY}} height DESC)) '다음 사람과 키 차이' FROM Tbl1; & 다음 값과의 \seqsplit{차이(마지막은} 자기 값) \tn 
% Row Count 29 (+ 9)
% Row 7
\SetRowColor{LightBackground}
LGR( )  OVER &  & 이전 값과의 차이 \tn 
% Row Count 31 (+ 2)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{순위 / 분석 함수 (cont)}}  \tn
% Row 8
\SetRowColor{LightBackground}
\seqsplit{FIRST\_VALUE} OVER & SELECT addr, name, height, height - ({\bf{FIRST\_VALUE}}(height) {\bf{OVER}} ({\bf{PARTITION BY}} addr {\bf{ORDER BY}} height DESC)) AS '지역별 가장 큰 키와의 차이' FROM Tbl1; & (지역별) 가장 큰 값 \tn 
% Row Count 11 (+ 11)
% Row 9
\SetRowColor{white}
\seqsplit{CUME\_DIST} & SELECT addr, name,height, ({\bf{CUME\_DIST( ) OVER}} ({\bf{PARTITION BY}} addr {\bf{ORDER BY}} height DESC)) * 100 AS '지역별 누적인원 백분율' FROM Tbl1; & 누적함수 동률이면 나중 값 \tn 
% Row Count 21 (+ 10)
% Row 10
\SetRowColor{LightBackground}
\seqsplit{PERCENTILE\_CONT} & SELECT {\bf{DISTINCT}} addr, {\bf{PERCENTILE\_CONT(0.5) WITHIN GROUP}} ({\bf{ORDER BY}} height) {\bf{OVER}} ({\bf{PARTITION BY}} addr) AS '지역별 키의 중간값' FROM Tbl1; & WTHIN GROUP에 정렬할 열 지정. 적적한 값을 보간하여 표시적적한 값을 보간하여 표시. \tn 
% Row Count 32 (+ 11)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{순위 / 분석 함수 (cont)}}  \tn
% Row 11
\SetRowColor{LightBackground}
\seqsplit{PERCENTILE\_DISC} &  & 있는 값 중에서만 추출. \tn 
% Row Count 3 (+ 3)
% Row 12
\SetRowColor{white}
PIVOT & SELECT * FROM pivotTest {\bf{PIVOT}} (SUM(amount) {\bf{FOR}} season {\bf{IN}}('봄', '여름', '가을', '겨울')) AS resultPivot; &  \tn 
% Row Count 11 (+ 8)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{JOIN}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{\textless{}{\emph{ INNER JOIN }}\textgreater{}} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
INNER JOIN & {\bf{SELECT B}}.uesrID, {\bf{U}}.name, {\bf{B}}.prodName, U.addr {\bf{FROM}} buyTbl {\bf{B}} & 구매한 사람만 출력 \tn 
% Row Count 6 (+ 5)
% Row 2
\SetRowColor{LightBackground}
 & ..............{\bf{INNER JOIN}} userTbl {\bf{U ON}} {\bf{B}}.userID = {\bf{U}}.userID  {\bf{WHERE B}}.userid = 'JYP'; & 중목 안된 컬럼명은 U. 생략 가능 \tn 
% Row Count 13 (+ 7)
% Row 3
\SetRowColor{white}
-{}- \seqsplit{DISTINCT} & {\bf{SELECT DISRINCT U}}.uesrID, {\bf{U}}.name, {\bf{U}}.addr, {\bf{FROM}} userTbl {\bf{U}} & 한 번이라도 구매한 사람 목록 \tn 
% Row Count 18 (+ 5)
% Row 4
\SetRowColor{LightBackground}
 & ..............{\bf{INNER JOIN}} buyTbl {\bf{B ON}} {\bf{U}}.userID = {\bf{B}}.userID  {\bf{ORDER BY U}}.userid; &  \tn 
% Row Count 24 (+ 6)
% Row 5
\SetRowColor{white}
-{}- WHERE EXISTS & {\bf{SELECT}} U.userID, U.addr {\bf{FROM}} userTbl U & 동일한 결과 \tn 
% Row Count 27 (+ 3)
% Row 6
\SetRowColor{LightBackground}
 & ..............{\bf{WHERE EXISTS}} ({\bf{SELECT}} * {\bf{FROM buyTbl }}B{\bf{ }}WHERE U{\bf{.userID = }}B.userID); &  \tn 
% Row Count 34 (+ 7)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{JOIN (cont)}}  \tn
% Row 7
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{\textless{}{\emph{ OUTER JOIN }}\textgreater{}} \tn 
% Row Count 1 (+ 1)
% Row 8
\SetRowColor{white}
-{}- LEFT OUTER JOIN & {\bf{SELECT U}}.uesrID, {\bf{U}}.name, {\bf{U}}.prodName, U.addr {\bf{FROM}} userTbl {\bf{U}} & 구매안한 사람도 출력 \tn 
% Row Count 6 (+ 5)
% Row 9
\SetRowColor{LightBackground}
 & ..............{\bf{LEFT OUTER JOIN}} buyTbl {\bf{B ON}} {\bf{U}}.userID = {\bf{B}}.userID  {\bf{ORDER BY}} U.userID; & userTbl 다 나와. \tn 
% Row Count 13 (+ 7)
% Row 10
\SetRowColor{white}
 & ..............{\bf{WHERE}} B.pridName = {\bf{NULL}}; & 한 번도 구매한 적 없는 고객 \tn 
% Row Count 16 (+ 3)
% Row 11
\SetRowColor{LightBackground}
-{}- RIGHT OUTER JOIN & ..............{\bf{FROM}} buyTbl B {\bf{RIGHT OUTER JOIN}} userTbl U {\bf{ON}}... & 테이블 순서 바꾸면 동일한 결과. \tn 
% Row Count 21 (+ 5)
% Row 12
\SetRowColor{white}
 & ..............{\bf{RIGHT OUTER JOIN}} buyTbl {\bf{B ON}} {\bf{U}}.userID = {\bf{B}}.userID  {\bf{ORDER BY}} U.userID; &  \tn 
% Row Count 28 (+ 7)
% Row 13
\SetRowColor{LightBackground}
-{}- FULL OUTER JOIN &  & 양쪽 다 나와. \tn 
% Row Count 31 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{JOIN (cont)}}  \tn
% Row 14
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{\textless{}{\emph{ 다자 조인 }}\textgreater{}} \tn 
% Row Count 1 (+ 1)
% Row 15
\SetRowColor{white}
-{}- INNER JOIN & {\bf{SELECT S}}.stdName, {\bf{S}}.addr, {\bf{C}}.clubName, {\bf{C}}.roomNo  {\bf{FROM}} stdTbl {\bf{S}} & 가입된 사람만. \tn 
% Row Count 7 (+ 6)
% Row 16
\SetRowColor{LightBackground}
 & ..............{\bf{INNER JOIN}} stdclubTbl {\bf{SC  ON}} S.stdname = SC.stdName & S와 SC를 먼저 JOIN하고 \tn 
% Row Count 12 (+ 5)
% Row 17
\SetRowColor{white}
 & ..............{\bf{INNER JOIN}} clubTbl {\bf{C  ON}} SC.clubName = C.clubName & 그 결과를 다시 C와 JOIN \tn 
% Row Count 17 (+ 5)
% Row 18
\SetRowColor{LightBackground}
-{}- LEFT OUTER JOIN & {\bf{SELECT S}}.stdName, {\bf{S}}.addr, {\bf{C}}.clubName, {\bf{C}}.roomNo  {\bf{FROM}} stdTbl {\bf{S}} &  \tn 
% Row Count 23 (+ 6)
% Row 19
\SetRowColor{white}
 & ..............{\bf{ LEFT OUTERJOIN}} stdclubTbl {\bf{SC  ON}} S.stdname = SC.stdName & 가입하지 않은 사람도 다 나와 \tn 
% Row Count 28 (+ 5)
% Row 20
\SetRowColor{LightBackground}
 & ..............{\bf{ LEFT OUTERJOIN}} clubTbl {\bf{C  ON}} SC.clubName = C.clubName &  \tn 
% Row Count 33 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{JOIN (cont)}}  \tn
% Row 21
\SetRowColor{LightBackground}
-{}- RIGHT OUTER JOIN & {\bf{SELECT S}}.stdName, {\bf{S}}.addr, {\bf{C}}.clubName, {\bf{C}}.roomNo  {\bf{FROM}} stdTbl {\bf{S}} &  \tn 
% Row Count 6 (+ 6)
% Row 22
\SetRowColor{white}
 & ..............{\bf{ LEFT OUTERJOIN}} stdclubTbl {\bf{SC  ON}} S.stdname = SC.stdName &  \tn 
% Row Count 11 (+ 5)
% Row 23
\SetRowColor{LightBackground}
 & ..............{\bf{ RIGHT OUTERJOIN}} clubTbl {\bf{C  ON}} SC.clubName = C.clubName & 가입한 학생이 없는 동아리도 다 나와. \tn 
% Row Count 16 (+ 5)
% Row 24
\SetRowColor{white}
-{}- FULL OUTER JOIN & {\bf{SELECT S}}.stdName, {\bf{S}}.addr, {\bf{C}}.clubName, {\bf{C}}.roomNo  {\bf{FROM}} stdTbl {\bf{S}} &  \tn 
% Row Count 22 (+ 6)
% Row 25
\SetRowColor{LightBackground}
 & ..............{\bf{ FULL T OUTERJOIN}} stdclubTbl {\bf{SC  ON}} S.stdname = SC.stdName & 가입하지 않은 학생도 나오고 \tn 
% Row Count 27 (+ 5)
% Row 26
\SetRowColor{white}
 & ..............{\bf{ FULL OUTERJOIN}} clubTbl {\bf{C  ON}} SC.clubName = C.clubName & 가입한 학생이 없는 동아리도 나와 \tn 
% Row Count 32 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{JOIN (cont)}}  \tn
% Row 27
\SetRowColor{LightBackground}
CROSS JON & {\bf{SELECT}} * {\bf{FROM}} buyTbl {\bf{CROSS JOIN}} userTbl; &  \tn 
% Row Count 4 (+ 4)
% Row 28
\SetRowColor{white}
 & -{}- 'ON' 없음.  카티션 곱. 대량 샘플 데이터 만들 대 사용. &  \tn 
% Row Count 9 (+ 5)
% Row 29
\SetRowColor{LightBackground}
SELF JOIN & SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.department AS \seqsplit{'직속상관부서'}  FROM enpTbl A INNER JOIN empTbl B ON A.manager = B.emp WHERE A.emp = '우대리'; &  \tn 
% Row Count 20 (+ 11)
% Row 30
\SetRowColor{white}
\mymulticolumn{3}{x{17.67cm}}{\textless{}{\emph{ 결과 합하기/ 제외하기 }}\textgreater{}} \tn 
% Row Count 21 (+ 1)
% Row 31
\SetRowColor{LightBackground}
UNION (ALL) & {\bf{SELECT}} stdName, addr {\bf{FROM}} stdTbl {\bf{UNION SELECT}} clubName, roomNo {\bf{FROM}} clubTbl; &  \tn 
% Row Count 27 (+ 6)
% Row 32
\SetRowColor{white}
 & -{}- 두 결과 중복 포함 하래로 합침, UNION은 중복 제거 &  \tn 
% Row Count 32 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{3.2053 cm} x{6.9167 cm} x{6.748 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{JOIN (cont)}}  \tn
% Row 33
\SetRowColor{LightBackground}
EXCEPT & {\bf{SELECT}} name, mobile1+mobile2 AS '전화번호' {\bf{FROM}} userTbl {\bf{EXCEPT SELECT}} name, mobile1+mobile2 {\bf{FROM}} userTbl {\bf{WHERE}} mobile1 {\bf{IS NULL}}; & 첫 번째 쿼리 결과 중 두 번째 결가 제거 \tn 
% Row Count 10 (+ 10)
% Row 34
\SetRowColor{white}
\seqsplit{INTERSECT} & {\bf{SELECT}} name, mobile1+mobile2 AS '전화번호' {\bf{FROM}} userTbl {\bf{INTERSECT SELECT}} name, mobile1+mobile2 {\bf{FROM}} userTbl {\bf{WHERE}} mobile1 {\bf{IS NULL}}; & 첫 번째 쿼리 결과 중 두 번재 쿼리 해당되는 것만. \tn 
% Row Count 20 (+ 10)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{프로그래밍}}  \tn
% Row 0
\SetRowColor{LightBackground}
CASE WHEN - THEN & {\bf{DECLARE @}}credit CHAR(1), @point INT = 77 \{\{nl\}\} {\bf{SET @}}credit =  \{\{nl\}\} ~ ~ {\bf{CASE  \{\{nl\}\} ~ ~ ~ ~ WHEN}} (@point \textgreater{}= 90) {\bf{THEN}} 'A'   \{\{nl\}\} ~ ~ ~ ~ {\bf{WHEN}} (@point \textgreater{}=80) {\bf{THEN}} 'B'  \{\{nl\}\} ~ ~ ~ ~ {\bf{ELSE}} 'C' \{\{nl\}\}  ~ ~ {\bf{END}} \{\{nl\}\} {\bf{PRINT N'}}학점 : {\bf{'}} + @credit & ELSE 는 생략 가능. END는 생략 불가. \tn 
% Row Count 23 (+ 23)
% Row 1
\SetRowColor{white}
-{}-간편 CASE & {\bf{DECLARE @}}credit CHAR(1), @point INT = 77 \{\{nl\}\} {\bf{SET @}}credit =  \{\{nl\}\} ~ ~ {\bf{CASE  @}}point  \{\{nl\}\} ~ ~  ~ ~ {\bf{WHEN}} 90 {\bf{THEN}} 'A'   \{\{nl\}\} ~ ~  ~ ~ {\bf{WHEN}} 80 {\bf{THEN}} 'B'  \{\{nl\}\} ~ ~  ~ ~ {\bf{ELSE}} 'C' \{\{nl\}\}  ~ ~ {\bf{END}} {\bf{PRINT N'}}학점 : {\bf{'}} + @credit &  \tn 
% Row Count 45 (+ 22)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{프로그래밍 (cont)}}  \tn
% Row 2
\SetRowColor{LightBackground}
\seqsplit{행열} \seqsplit{변황} & {\bf{SELECT}} SUM({\bf{CASE WHEN}} classfy = '의류' {\bf{THEN}} price {\bf{ELSE}} 0) AS \seqsplit{sum\_price\_close}, 	 \{\{nl\}\} ~ ~ ~ ~ SUM({\bf{CASE WHEN}} classfy = '주방용품' {\bf{THEN}} price {\bf{ELSE}} 0) AS \seqsplit{sum\_price\_kitchen} \{\{nl\}\} ~ ~ ~ ~ SUM({\bf{CASE WHEN}} classfy = '사무용품' {\bf{THEN}} price {\bf{ELSE}} 0) AS \seqsplit{sum\_price\_office} \{\{nl\}\} ~ ~ {\bf{FROM}} Goods; &  \tn 
% Row Count 26 (+ 26)
% Row 3
\SetRowColor{white}
IF - ELSE & {\bf{IF}} @var1 = 100 PRINT '100' \{\{nl\}\}{\bf{ELSE}} PRINT 'Not 100' & 실행문 여러개면 begin - end \tn 
% Row Count 30 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{프로그래밍 (cont)}}  \tn
% Row 4
\SetRowColor{LightBackground}
WHILE & {\bf{DECLARE}} @i INT = 1, @hap BIGINT = 0 \{\{nl\}\} {\bf{WHILE}} (@i \textless{} 100) \{\{nl\}\} {\bf{BEGIN}} \{\{nl\}\} ~ ~ {\bf{IF}} (@i\%7 = 0) \{\{nl\}\} ~ ~ {\bf{BEGIN}} \{\{nl\}\} ~ ~ ~ ~ {\bf{SET}} @i += 1\{\{nl\}\} ~ ~ ~ ~ {\bf{CONTINUE}} \{\{nl\}\} ~ ~ {\bf{END}} \{\{nl\}\} ~ ~ {\bf{SET}} @hap += @i \{\{nl\}\} ~ ~ {\bf{IF}} (@hap\textgreater{}1000) {\bf{BREAK}} \{\{nl\}\} ~ ~ {\bf{SET}} @i += 1 \{\{nl\}\} {\bf{END}}  \{\{nl\}\} {\bf{PRINT}} {\bf{N'}}합계 : {\bf{'}} + {\bf{CAST}}(@hap AS NCHAR(10)) &  \tn 
% Row Count 31 (+ 31)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{프로그래밍 (cont)}}  \tn
% Row 5
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{17.67cm}}{-{}- CONTINUE / BREAK} \tn 
% Row Count 1 (+ 1)
% Row 6
\SetRowColor{white}
GOTO & {\bf{IF}} (@hap \textgreater{} 1000) {\bf{GOTO {\emph{endpoint}} }} \{\{nl\}\} {\bf{endpoint:}} \{\{nl\}\} PRINT N'합계= ' + CAST(@hap AS NCHAR(10)) & 문자 + 숫자는 형변환 후 사용 \tn 
% Row Count 9 (+ 8)
% Row 7
\SetRowColor{LightBackground}
WAIT FOR DELAY & {\bf{WAIT FOR DELAY}} '00:00:05' & 5초 딜레이 \tn 
% Row Count 12 (+ 3)
% Row 8
\SetRowColor{white}
WAIT FOR TIME & {\bf{WAIT FOR TIME}} '23:59' & 23:55까지 정지 \tn 
% Row Count 15 (+ 3)
% Row 9
\SetRowColor{LightBackground}
TRY / CATCH & {\bf{BEGIN TRY}} 윈래 SQL문 {\bf{END TRY \{\{nl\}\}BEGIN CATCH}} 오류 시 SQL문 {\bf{END CATCH}} &  \tn 
% Row Count 21 (+ 6)
% Row 10
\SetRowColor{white}
-{}- \seqsplit{ERROR\_LINE} & {\bf{BEGIN TRY}} INSERT INTO userTbl VLAUES('LSG', '이상구') {\bf{END TRY \{\{nl\}\}BEGIN CATCH}} PRINT {\bf{ERROR\_LINE}}( ) {\bf{END CATCH}} &  \tn 
% Row Count 29 (+ 8)
% Row 11
\SetRowColor{LightBackground}
 & -{}- ERROR\_MESSAGE( ) / ERROR\_NUM( ) / \seqsplit{ERROR\_PROCEDURE(} ) &  \tn 
% Row Count 33 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{2.8679 cm} x{6.9167 cm} x{7.0854 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{17.67cm}}{\bf\textcolor{white}{프로그래밍 (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
\seqsplit{RAISERROR} & {\bf{RAISERROR}}(N'에러 발생!!', 5, 1); & 수준 5, 상태 1로 Error message 표시 \tn 
% Row Count 3 (+ 3)
% Row 13
\SetRowColor{white}
THROW & {\bf{THORW}} 50000, N'에러 발생!!', 1 & Error 번호 50000, 상태 1로 Error 표시. 50000이상을 적어줘야 함. \tn 
% Row Count 8 (+ 5)
% Row 14
\SetRowColor{LightBackground}
EXEC( ) & {\bf{DECLARE}} @curDATE DATE, @curMonth VCHAR(2), @curDay VCHAR(2), @sql VCHAR(100) \{\{nl\}\} {\bf{SET}} @curDATE = GERDTE( ) \{\{nl\}\} {\bf{SET}} @curMonth = MONTH(@curDATE) \{\{nl\}\} {\bf{SET}} @curDay = DAY(@curDATE) \{\{nl\}\} {\bf{SET}} @sql = 'CREATE TABLE myTbl' + @curMonth + '\_' + @curDay \{\{nl\}\} {\bf{ SET}} @sql {\bf{+=}} '(id INT, name NCHAR(10))' \{\{nl\}\} {\bf{EXEC}}(@sql) &  \tn 
% Row Count 30 (+ 22)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{7.9442 cm} x{9.3258 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Procedure}}  \tn
% Row 0
\SetRowColor{LightBackground}
Syntex & 처음 실행 후 메모리에 저장되어 이후 반복 사용할 경우 실행 속도가 빨라진다. \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
 & Python 에서 불러 사용하기 편하고 수정, 삭제 등 관리가 편해진다. \tn 
% Row Count 10 (+ 5)
% Row 2
\SetRowColor{LightBackground}
 & 저장 프로시저는 SQL 서버에 저장되므로 \seqsplit{클라이언트에서} 실행하는 것보다 네트워크 전송량이 감소한다. \tn 
% Row Count 17 (+ 7)
% Row 3
\SetRowColor{white}
 & 본문 안에 BEGIN - END 없이 사용한다. \tn 
% Row Count 20 (+ 3)
% Row 4
\SetRowColor{LightBackground}
CREATE PROCEDURE & CREATE PROCEDURE usp\_user1 @userBirth INT, @userHeight INT = 178 AS \{\{nl\}\}~~~~SELECT * FROM userTbl WHERE birthYear \textgreater{} @userBirth AND height \textgreater{} @userHeight;\{\{nl\}\}EXEC usp\_user2 1970, 178; EXEC usp\_user2 @userHeight=178, @userBirth=1970;\{\{nl\}\}EXEC usp\_user1 1960, 170;\{\{nl\}\}EXEC usp\_user1 1680; \tn 
% Row Count 35 (+ 15)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{7.9442 cm} x{9.3258 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Procedure (cont)}}  \tn
% Row 5
\SetRowColor{LightBackground}
\{\{nl\}\}~~~~ & CREATE \#usp\_temp AS SELECT * FROM userTbl; -{}- 임시 프로시저 \tn 
% Row Count 4 (+ 4)
% Row 6
\SetRowColor{white}
~~~~Output & CREATE PROCEDURE usp\_user2  @textValue NCHAR(10),  @outValue INT OUTPUT  AS\{\{nl\}\}~~~~INSERT INTO testTbl VALUES (@textValue);\{\{nl\}\}~~~~SELECT @outValue = IDENT\_CURRENT('testTbl');\{\{nl\}\}CREATE TABLE testTbl (id INT IDENTITY, txt NCHAR(10));\{\{nl\}\}DECLARE @myValue INT;\{\{nl\}\}EXEC usp\_user2 '테스트 값1', @myValue OUTPUT;\{\{nl\}\}PRINT '현재 입력된 id 값 ==\textgreater{} ' + CAST(@myValue AS CHAR(5)); \tn 
% Row Count 25 (+ 21)
% Row 7
\SetRowColor{LightBackground}
~~~~RETURN & CREATE PROCEDURE usp\_return @userName NVARCHAR(10) AS \{\{nl\}\}~~~~DECLARE @userID CHAR(8);\{\{nl\}\}~~~~SELECT @userID = userID FROM userTbl WHERE name = @userName; \{\{nl\}\}~~~~IF @userID \textless{}\textgreater{} RETURN 0; \{\{nl\}\}~~~~ELSE RETURN -1;\{\{nl\}\}DECLARE @retVal INT\{\{nl\}\}EXEC @retVal = usp\_return '나몰라';\{\{nl\}\}SELECT @retVal; \tn 
% Row Count 44 (+ 19)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{7.9442 cm} x{9.3258 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Procedure (cont)}}  \tn
% Row 8
\SetRowColor{LightBackground}
~~~~Table Type & CREATE TYPE userTblType AS TABLE (userID CHAR(8), name NVARCHAR(10), birthYear INT, addr NCHAR(2));\{\{nl\}\}CREATE PROCEDURE usp\_tableTypeParam @tblParam userTblType READONLY AS~~~~-{}- 테이블 형식 매개 변수는 READONLU 필수\{\{nl\}\}~~~~SELECT * FROM @tblParam WHERE birthYear \textless{} 1970;\{\{nl\}\}DECLARE @tblVar userTblType;\{\{nl\}\}INSERT INTO @tblVar SELECT userID, name, birthYear, addr FROM userTbl;\{\{nl\}\}EXEC usp\_tableTypeParam @tblVar; \tn 
% Row Count 23 (+ 23)
% Row 9
\SetRowColor{white}
쿼리 내용 확인 & EXEC sp\_helptext usp\_user1; \tn 
% Row Count 25 (+ 2)
% Row 10
\SetRowColor{LightBackground}
 & SELECT o.name, m.definition FROM sys.sql\_modules m JOIN sys.objects o ON m.object\_id = o.object\_id AND o.TYPE = 'P'; \tn 
% Row Count 31 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{6.7353 cm} x{10.5347 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Function}}  \tn
% Row 0
\SetRowColor{LightBackground}
Syntex & 함수 내부에 TRY - CATCH / CREATE / ALTER / DROP 사용 못함 \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
 & 오류가 발생하면 즉시 함수 실행을 몸추고 값을 반환하지 않는다. \tn 
% Row Count 7 (+ 4)
% Row 2
\SetRowColor{LightBackground}
 & BEGIN - END 를 기본 사용 \tn 
% Row Count 9 (+ 2)
% Row 3
\SetRowColor{white}
CREATE FUNCTION & CREATE FUNCTION ufn\_getAge(@bYear INT)\{\{nl\}\}~~~~RETURNS INT AS~~~~-{}- RETURNS에서 'S' 주의\{\{nl\}\}BEGIN\{\{nl\}\}~~~~DECLARE @age INT;\{\{nl\}\}~~~~SET @age = YEAR(GETDATE()) - @bYear;\{\{nl\}\}~~~~RETURN @age;\{\{nl\}\}END \tn 
% Row Count 22 (+ 13)
% Row 4
\SetRowColor{LightBackground}
 & SELECT dbo.ufn\_getAge(1979);-{}-주의 : Schema 이름 적어야 함. \tn 
% Row Count 25 (+ 3)
% Row 5
\SetRowColor{white}
 & SELECT userID, name, \seqsplit{dbo.ufn\_getAge(birthYear)} AS {[}만 나이{]} FROM userTbl; \tn 
% Row Count 29 (+ 4)
% Row 6
\SetRowColor{LightBackground}
ALTER FUNCTION & ALTER FUNCTION ufn\_getAge(@bYear INT)\{\{nl\}\}~~~~RETURNS INT AS~~~~\{\{nl\}\}BEGIN\{\{nl\}\}~~~~DECLARE @age INT;\{\{nl\}\}~~~~SET @age = YEAR(GETDATE()) - @bYear + 1;\{\{nl\}\}~~~~RETURN @age;\{\{nl\}\}END \tn 
% Row Count 41 (+ 12)
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{x{6.7353 cm} x{10.5347 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Function (cont)}}  \tn
% Row 7
\SetRowColor{LightBackground}
DROP FUNCTION & DROP FUNCTION ufn\_getAge; \tn 
% Row Count 2 (+ 2)
% Row 8
\SetRowColor{white}
WITH SCHEMABINDING & -{}- 참조하고 있는 테이블이나 뷰 등을 수정하지 못하게 함. \tn 
% Row Count 6 (+ 4)
% Row 9
\SetRowColor{LightBackground}
Inline Table 반환 & CREATE FUNCTION ufn\_getUser(@ht INT)\{\{nl\}\}~~~~RETURNS TABLE\{\{nl\}\}~~~~WITH SCHEMABINDING~~~~-{}- 여기에서 참조되는 Table의 컬럼 변경이 안된다.\{\{nl\}\}AS\{\{nl\}\}~~~~RETURN (SELECT userID, name, height FROM dbo.userTbl WHERE height \textgreater{} @ht)\{\{nl\}\}~~~~-{}- SCHEMABINDIND을 사용하려면 Table명에 Schema 이름도 붙여줘야 함.\{\{nl\}\}SELECT * FROM dbo.ufn\_getUser(177); \tn 
% Row Count 26 (+ 20)
% Row 10
\SetRowColor{white}
다중문 테이블 반환 & CREATE FUNCTION ufn\_userGrade(@bYear INT)\{\{nl\}\}~~~~RETURNS @retTable TABLE (userID CHAR(8), name NCHAR(10), grade NCHAR(5)) AS\{\{nl\}\}BEGIN\{\{nl\}\}~~~~DECLARE @rowCnt INT;\{\{nl\}\}~~~~SELECT @rowCnt = COUNT(*) FROM userTbl WHERE birthYear \textgreater{}= @bYear;\{\{nl\}\}\{\{nl\}\}~~~~-{}- 행이 하나도 없으면 '없음'을 입력하고 테이블 리턴하고 끝남.\{\{nl\}\}~~~~IF @rowCnt \textless{}= 0 \{\{nl\}\}~~~~BEGIN\{\{nl\}\}~~~~~~~~INSERT INTO @retTable VALUES ('없음', '없음', '없음');\{\{nl\}\}~~~~~~~~RETURN;\{\{nl\}\}~~~~END;\{\{nl\}\}\{\{nl\}\}~~~~-{}- 행이 1개 이상이면 아래 수행\{\{nl\}\}~~~~INSERT INTO @retTable \{\{nl\}\}~~~~~~~~SELECT U.userID, U.name,\{\{nl\}\}~~~~~~~~~~~~CASE\{\{nl\}\}~~~~~~~~~~~~~~~~WHEN (SUM(price*amount) \textgreater{}= 1500) THEN '최우수고객'\{\{nl\}\}~~~~~~~~~~~~~~~~WHEN (SUM(price*amount) \textgreater{}= 1000) THEN '우수고객'\{\{nl\}\}~~~~~~~~~~~~~~~~WHEN (SUM(price*amount) \textgreater{} 1) THEN '일반고객' \{\{nl\}\}~~~~~~~~~~~~~~~~ELSE '유령고객'\{\{nl\}\}~~~~~~~~~~~~END\{\{nl\}\}~~~~~~~~FROM buyTbl B RIGHT OUTER JOIN userTbl U ON B.userID = U.userID\{\{nl\}\}~~~~~~~~WHERE birthYear \textgreater{}= @bYear\{\{nl\}\}~~~~~~~~GROUP BY U.userID, U.name; \{\{nl\}\}~~~~        RETURN;\{\{nl\}\}END;\{\{nl\}\}SELECT * FROM \seqsplit{dbo.ufn\_userGrade(1970);} \tn 
% Row Count 112 (+ 86)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{17.67cm}{p{1.727 cm} x{15.543 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{17.67cm}}{\bf\textcolor{white}{Cussor}}  \tn
% Row 0
\SetRowColor{LightBackground}
 & 커서 선언(DECLARE) - 커서 열기(OPEN) - 커서에서 데이터 가져오기(FETCH) - 데이터 처리 - 커서 닫기(CLOSE) - 커서 해제(DEALLOCATE) \tn 
% Row Count 5 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}



\end{document}