\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{garydeez}
\pdfinfo{
  /Title (sql-data-management.pdf)
  /Creator (Cheatography)
  /Author (garydeez)
  /Subject (SQL Data Management 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}{A3A3A3}
\definecolor{LightBackground}{HTML}{F3F3F3}
\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{SQL Data Management Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{garydeez} via \textcolor{DarkBackground}{\uline{cheatography.com/18516/cs/35577/}}}
\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}garydeez \\
  \uline{cheatography.com/garydeez} \\
  \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 29th November, 2022.\\
   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{multicols*}{2}

\begin{tabularx}{8.4cm}{x{2.584 cm} x{2.508 cm} x{2.508 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Date \& Time Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
CURDATE() CURTIME() NOW() & Returns the current date, time, or date and time in 'YYYY-MM-DD', 'HH:MM:SS', or 'YYYY-MM-DD HH:MM:SS' format & SELECT CURDATE();  returns '2019-01-25' SELECT CURTIME();  returns '21:05:44' SELECT NOW();  returns '2019-01-25 21:05:44' \tn 
% Row Count 10 (+ 10)
% Row 1
\SetRowColor{white}
DATE(expr) TIME(expr) & Extracts the date or time from a date or datetime expression expr & SELECT \seqsplit{DATE('2013-03-25} 22:11:45');   returns '2013-03-25' SELECT \seqsplit{TIME('2013-03-25} 22:11:45');   returns '22:11:45' \tn 
% Row Count 19 (+ 9)
% Row 2
\SetRowColor{LightBackground}
DAY(d) MONTH(d) YEAR(d) & Returns the day, month, or year from date d & SELECT \seqsplit{DAY('2016-10-25');} returns 25 SELECT \seqsplit{MONTH('2016-10-25');} returns 10 SELECT \seqsplit{YEAR('2016-10-25');} returns 2016 \tn 
% Row Count 28 (+ 9)
% Row 3
\SetRowColor{white}
HOUR(t) MINUTE(t) SECOND(t) & Returns the hour, minute, or second from time t & SELECT \seqsplit{HOUR('22:11:45');} returns 22 SELECT \seqsplit{MINUTE('22:11:45');} returns 11 SELECT \seqsplit{SECOND('22:11:45');} returns 45 \tn 
% Row Count 37 (+ 9)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{2.584 cm} x{2.508 cm} x{2.508 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Date \& Time Functions (cont)}}  \tn
% Row 4
\SetRowColor{LightBackground}
\seqsplit{DATEDIFF(expr1}, expr2) \seqsplit{TIMEDIFF(expr1}, expr2) & Returns expr1 - expr2 in number of days or time values, given expr1 and expr2 are date, time, or datetime values & SELECT \seqsplit{DATEDIFF('2013-03-10'}, \seqsplit{'2013-03-04');}   returns 6 SELECT \seqsplit{TIMEDIFF('10:00:00'}, '09:45:30');   returns 00:14:30 \tn 
% Row Count 9 (+ 9)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.76 cm} x{3.192 cm} x{3.648 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Aggregate Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{COUNT()} & Count number of rows in the set. & SELECT COUNT(*)  FROM Employee  WHERE Bonus \textgreater{} 500; \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
MIN() & Find minimum value in set. & SELECT MIN(Salary)  FROM Employee; \tn 
% Row Count 5 (+ 2)
% Row 2
\SetRowColor{LightBackground}
MAX() & Find max value in set. & SELECT MAX(Salary)  FROM Employee; \tn 
% Row Count 7 (+ 2)
% Row 3
\SetRowColor{white}
SUM() & Sum all values in set. & SELECT SUM(Salary)  FROM Employee; \tn 
% Row Count 9 (+ 2)
% Row 4
\SetRowColor{LightBackground}
AVG() & Compute mean of all values in set. & SELECT AVG(Salary)  FROM Employee; \tn 
% Row Count 12 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{8.4cm}}{An aggregate function processes values from a set of rows and returns a summary value. They appear in a SELECT clause and process all rows that satisfy the WHERE clause condition. If a SELECT statement has no WHERE clause, the aggregate function processes all rows. \newline  \newline Ignores NULL values.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{0.912 cm} x{3.344 cm} x{3.344 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Numeric Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{ABS(n)} & Returns the absolute value of n & SELECT ABS(-5);  returns 5 \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
LOG(n & Returns the natural logarithm of n & SELECT LOG(10);  returns 2.30258509299404 \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
POW(x, y) & Returns x to the power of y & SELECT POW(2, 3);  returns 8 \tn 
% Row Count 8 (+ 3)
% Row 3
\SetRowColor{white}
\seqsplit{RAND()} & Returns a random number between 0 (inclusive) and 1 (exclusive & SELECT RAND();  returns \seqsplit{0.1183182570322586} \tn 
% Row Count 12 (+ 4)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{ROUND(n}, d) & Returns n rounded to d decimal places & SELECT ROUND(16.25, 1);  returns 16.3 \tn 
% Row Count 15 (+ 3)
% Row 5
\SetRowColor{white}
\seqsplit{SQRT(n)} & Returns the square root of n & SELECT SQRT(25);  returns 5 \tn 
% Row Count 17 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.672 cm} x{2.964 cm} x{2.964 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{String Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{CONCAT(s1}, s2, ...) & Returns the string that results from concatenating the string arguments & SELECT CONCAT('Dis', 'en', 'gage');  returns 'Disengage' \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
\seqsplit{LOWER(s)} & Returns the lowercase s & SELECT \seqsplit{LOWER('MySQL');}  returns 'mysql' \tn 
% Row Count 8 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{REPLACE(s}, from, to) & Returns the string s with all occurrences of from replaced with to & SELECT REPLACE('This and that', 'and', 'or');  returns 'This or that' \tn 
% Row Count 13 (+ 5)
% Row 3
\SetRowColor{white}
\seqsplit{SUBSTRING(s}, pos, len) & Returns the substring from s that starts at position pos and has length len & SELECT \seqsplit{SUBSTRING('Boomerang'}, 1, 4);  returns 'Boom' \tn 
% Row Count 18 (+ 5)
% Row 4
\SetRowColor{LightBackground}
TRIM(s) & Returns the string s without leading and trailing spaces & SELECT TRIM('   test   ');  returns 'test' \tn 
% Row Count 22 (+ 4)
% Row 5
\SetRowColor{white}
\seqsplit{UPPER(s)} & Returns the uppercase s & SELECT \seqsplit{UPPER('mysql');}  returns 'MYSQL' \tn 
% Row Count 25 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.76 cm} x{3.42 cm} x{3.42 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{SQL Sublanguages}}  \tn
% Row 0
\SetRowColor{LightBackground}
DDL & Data Definition Language & Defines DB structure. \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
DQL & Data Query Language & Retrieve data from DB. \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
DML & Data Manipulation Language & Manipulate data stored in DB. \tn 
% Row Count 6 (+ 2)
% Row 3
\SetRowColor{white}
DCL & Data Control Language & Control DB user access. \tn 
% Row Count 8 (+ 2)
% Row 4
\SetRowColor{LightBackground}
DTL & Data Transaction Language & Manage DB transactions. \tn 
% Row Count 10 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Comments}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{-{}- single line comment \newline /{\emph{ multi-line  \newline    Comment }}/} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Literals}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{'String' \newline "String" \newline 123 \newline x'0fa2'} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{{[}S{]}ingle quotes are for {[}S{]}trings Literals (date literals are also strings); \newline {[}D{]}ouble quotes are for {[}D{]}atabase Identifiers; \newline Explicit values that are string, numeric, or binary.  \newline Strings must be surrounded by single quotes or double quotes. \newline Binary values are represented with x'0' where the 0 is any hex value.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.052 cm} x{2.964 cm} x{2.584 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{USE \& SHOW}}  \tn
% Row 0
\SetRowColor{LightBackground}
USE \seqsplit{DatabaseName} & Select default db for use. & Use World; \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
SHOW DATABASES & lists all databases in the database system instance. & SHOW DATABASES; \tn 
% Row Count 6 (+ 4)
% Row 2
\SetRowColor{LightBackground}
SHOW TABLES & lists all tables in the default database. & SHOW TABLES; \tn 
% Row Count 9 (+ 3)
% Row 3
\SetRowColor{white}
SHOW COLUMNS FROM TableName & lists all columns in the TableName table of the default database. & SHOW COLUMNS FROM \seqsplit{CountryLanguage;} \tn 
% Row Count 14 (+ 5)
% Row 4
\SetRowColor{LightBackground}
SHOW CREATE TABLE TableName & shows the CREATE TABLE statement for the TableName table of the default database. &  \tn 
% Row Count 20 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\SetRowColor{LightBackground}
\mymulticolumn{3}{x{8.4cm}}{Additional SHOW statements generate information about system errors, configuration, privileges, logs, etc.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Create \& Drop Databases}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{CREATE DATABASE petStore; \newline DROP DATABASE petStore;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Create \& Drop Table}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{CREATE TABLE Horse ( \newline     ID SMALLINT UNSIGNED AUTO\_INCREMENT, \newline     Name VARCHAR(15) NOT NULL DEFAULT 'Sam', \newline     BREED VARCHAR(20) CHECK (Breed = 'Quarter Horse' OR 'Saddlebred'), \newline     PRIMARY KEY (ID) \newline ); \newline DROP TABLE Horse;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{INSERT}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{INSERT INTO Product (ID, Name, ProductType, OriginDate, Weight) VALUES  \newline   (100, 'Tricorder', 'TC', '2020-08-11', 2.4), \newline   (200, 'Food replicator', 'FOD', '2020-09-21', 54.2), \newline   (300, 'Cloaking device', 'CD', '2019-02-04', 177.9);} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.84 cm} x{6.16 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{MySQL Data Types}}  \tn
% Row 0
\SetRowColor{LightBackground}
CHAR & String (0-255) \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
VARCHAR & String (0-255 \tn 
% Row Count 2 (+ 1)
% Row 2
\SetRowColor{LightBackground}
TINYINT & -128 to 127 \tn 
% Row Count 3 (+ 1)
% Row 3
\SetRowColor{white}
SMALLINT & -32768 to 32767 \tn 
% Row Count 4 (+ 1)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{MEDIUMINT} & -8388608 to 8388607 \tn 
% Row Count 5 (+ 1)
% Row 5
\SetRowColor{white}
INT & -2147483648 to 8388607 \tn 
% Row Count 6 (+ 1)
% Row 6
\SetRowColor{LightBackground}
BIGINT & -9223372036854775808 to 9223372036854775807 \tn 
% Row Count 8 (+ 2)
% Row 7
\SetRowColor{white}
DATE & YYYY-MM-DD \tn 
% Row Count 9 (+ 1)
% Row 8
\SetRowColor{LightBackground}
DATETIME & YYYY-MM-DD HH:MM:SS \tn 
% Row Count 10 (+ 1)
% Row 9
\SetRowColor{white}
\seqsplit{DECIMAL(M},D) & Numeric values with M digits, of which D digits follow the decimal point \tn 
% Row Count 13 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{UNSIGNED Integers have the same range but start from zero.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.8 cm} p{0.8 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Arithmetic Operators}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{} \tn 
% Row Count 0 (+ 0)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Returns NULL when either operand is NULL}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{p{0.76 cm} x{1.672 cm} x{5.168 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{ALTER TABLE}}  \tn
% Row 0
\SetRowColor{LightBackground}
ADD & Add column & ALTER TABLE TableName  ADD ColumnName DataType; \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\seqsplit{CHANGE} & Modify column & ALTER TABLE TableName  CHANGE CurrentColumnName NewColumnName NewDataType; \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
DROP & Delete column & ALTER TABLE TableName  DROP ColumnName; \tn 
% Row Count 7 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Create View}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{CREATE VIEW ViewName {[} ( Column1, Column2, ... ) {]} \newline AS SelectStatement; \newline  \newline CREATE VIEW ManagerView   \newline AS SELECT DepartmentName, EmployeeName AS ManagerName \newline    FROM Department, Employee \newline    WHERE ManagerID = EmployeeID; \newline  \newline CREATE VIEW ViewName {[} ( Column1, Column2, ... ) {]} \newline AS SelectStatement \newline {[} WITH CHECK OPTION {]};} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{DISTINCT}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT DISTINCT Language \newline FROM CountryLanguage \newline WHERE IsOfficial = 'F';} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Unique values.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{WHERE IN}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT *  \newline FROM CountryLanguage  \newline WHERE Language IN ('Dutch', 'Kongo', 'Albanian');} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Determine if a value matches one of several values.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{BETWEEN}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Name \newline FROM Employee \newline WHERE HireDate BETWEEN '2000-01-01' AND '2020-01-01';} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Value BETWEEN minValue AND maxValue and is equivalent to value \textgreater{}= minValue AND value \textless{}= maxValue.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{LIKE}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT * \newline FROM CountryLanguage \newline WHERE CountryCode LIKE 'A\_W';} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\% matches any number of characters. Ex: LIKE 'L\%t' matches "Lt", "Lot", "Lift", and "Lol cat". \newline \_ matches exactly one character. Ex: LIKE 'L\_t' matches "Lot" and "Lit" but not "Lt" and "Loot". \newline  \newline Case-insensitive by default Case-sensitive if followed by the BINARY keyword. Ex: LIKE BINARY 'L\%t' matches 'Left' but not 'left'. \newline  \newline Wildcard search \% or \_, a backslash (\textbackslash{}) must precede \% or \_. Ex: LIKE 'a\textbackslash{}\%' matches "a\%".}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{ORDER BY}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{-{}- Order by Language (ascending) \newline SELECT {\emph{  \newline FROM CountryLanguage  \newline ORDER BY Language; \newline  \newline -{}- Order by Language (descending) \newline SELECT }}  \newline FROM CountryLanguage  \newline ORDER BY Language DESC; \newline  \newline -{}- Order by CountryCode, then Language \newline SELECT *  \newline FROM CountryLanguage  \newline ORDER BY CountryCode, Language;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Order selected rows by one or more columns in ascending order. DESC orders rows in descending order.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{GROUP BY}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT CountryCode, SUM(Population) \newline FROM City \newline GROUP BY CountryCode; \newline  \newline SELECT CountryCode, District, COUNT(*) \newline FROM City \newline GROUP BY CountryCode, District;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Commonly used with aggregate functions. GROUP BY and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group. \newline  \newline Appears between the WHERE clause, if any, and the ORDER BY clause.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{HAVING}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT CountryCode, SUM(Population) \newline FROM City \newline GROUP BY CountryCode \newline HAVING SUM(Population) \textgreater{} 2300000; \newline  \newline SELECT CountryCode, District, COUNT({\emph{) \newline FROM City \newline GROUP BY CountryCode, District \newline HAVING COUNT(}}) \textgreater{}= 2;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Used with GROUP By to filter group results. Follows GROUP BY and precedes ORDER BY.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Prefix \& Alias}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Department.Name AS Group,  \newline        Employee.Name AS Supervisor \newline FROM Department, Employee \newline WHERE Manager = ID;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Prefix is the TableName.ColumnName. \newline Alias = AS \newline The AS keyword is optional and may be omitted. Ex: SELECT Name N FROM Country C.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Join Query}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT DepartmentName, EmployeeName \newline FROM Department, Employee \newline WHERE Manager = ID; \newline  \newline SELECT Department.Name AS Group, \newline        Employee.Name AS Supervisor \newline FROM Department \newline INNER JOIN  Employee \newline ON Manager = ID;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT LeftColumn, RightColumn \newline FROM LeftTable, RightTable \newline WHERE Key = Key; \newline  \newline FROM specifies the left table. \newline INNER JOIN or FULL JOIN specifies the right table. \newline ON specifies the join columns.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.48 cm} x{5.52 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Join Types}}  \tn
% Row 0
\SetRowColor{LightBackground}
INNER JOIN & Default. Only matching left and right table rows. \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
FULL (OUTER) JOIN & Many DB do not support. All left and right table rows, regardless of match. \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
LEFT(OUTER) JOIN & All left table rows, only matching right table rows. \tn 
% Row Count 7 (+ 2)
% Row 3
\SetRowColor{white}
RIGHT (OUTER) JOIN & Many DB do not support. All right table rows, only matching left table rows. \tn 
% Row Count 10 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Union Full Join}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT {\emph{ FROM Table1 \newline LEFT OUTER JOIN Table2 \newline ON \seqsplit{Table1.column\_match=Table2.column\_match} \newline UNION \newline SELECT }} FROM Table1 \newline RIGHT OUTER JOIN Table2 \newline ON \seqsplit{Table1.column\_match=Table2.column\_match;}} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Use for FULL JOINS in MtSQL. Similar to JOIN but JOIN is good practice. The first SELECT returns matching rows and the second SELECT returns unmatched Department rows. The UNION keyword combines the two results into one table. \newline  \newline Table1: First Table in Database. \newline Table2: Second Table in Database. \newline column\_match: The column common to both the tables.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Non Equijoin}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Name, Address \newline FROM Buyer \newline LEFT JOIN Property \newline ON Price \textless{} MaxPrice;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Compares columns with an operator other than =, such as \textless{} and \textgreater{}.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Self Join}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT A.Name, B.Name \newline FROM EmployeeManager A \newline INNER JOIN EmployeeManager B \newline ON B.ID = A.Manager;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Joins a table to itself. A self-join can compare any columns of a table, as long as the columns have comparable data types. If a foreign key and the referenced primary key are in the same table, a self-join commonly compares those key columns. In a self-join, aliases are necessary to distinguish left and right tables.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Cross Join}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Model, Gigabytes, IPhone.Price + Storage.Price \newline FROM IPhone \newline CROSS JOIN Storage;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Combines two tables without comparing columns. Uses a CROSS JOIN clause without an ON clause. As a result, all possible combinations of rows from both tables appear in the result.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{SubQuery}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Language, Percentage \newline FROM CountryLanguage \newline WHERE Percentage \textgreater{}  \newline     (SELECT Percentage \newline     FROM CountryLanguage \newline     WHERE CountryCode = 'ABW' \newline         AND IsOfficial = 'T');} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Sometimes called a {\bf{nested query}} or {\bf{inner query}}, is a query within another SQL query. Subquery runs first. \newline  \newline The subquery is typically used in a SELECT statement's WHERE clause to return data to the outer query and restrict the selected results. The subquery is placed inside parentheses ().}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Correlated Subquery}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Name, CountryCode, Population \newline FROM City C  \newline WHERE Population \textgreater{}  \newline     (SELECT AVG(Population) \newline     FROM City \newline     WHERE CountryCode = C.CountryCode);} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{When the subquery's WHERE clause references a column from the outer query. The rows selected depend on what row is currently being examined by the outer query. \newline  \newline If a column name is identical to a column name in the outer query, the TableName.ColumnName differentiates the columns. Ex: City.CountryCode refers to the City table's CountryCode column. An alias can also help differentiate the columns.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Exists Operator}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Name, CountryCode \newline FROM City C \newline WHERE EXISTS  \newline     (SELECT * \newline     FROM CountryLanguage \newline     WHERE CountryCode = C.CountryCode \newline         AND Percentage \textgreater{} 97);} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Returns TRUE if a subquery selects at least one row and FALSE if no rows are selected. The NOT EXISTS operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Flatten Subquery}}  \tn
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{SELECT Name \newline FROM Country \newline WHERE Code IN \newline    (SELECT CountryCode \newline     FROM City \newline     WHERE Population \textgreater{} 1000000); \newline  \newline -{}- REPLACED BY \newline  \newline SELECT DISTINCT Name \newline FROM Country  \newline INNER JOIN City ON Code = CountryCode \newline WHERE Population \textgreater{} 1000000;} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Replacing with a join. Most subqueries that follow NOT EXISTS or contain a GROUP BY clause cannot be flattened. \newline  \newline Steps: \newline 1. Retain the outer query SELECT, FROM, GROUP BY, HAVING, and ORDER BY clauses. \newline 2. Add INNER JOIN clauses for each subquery table. \newline 3. Move comparisons between subquery and outer query columns to ON clauses. \newline 4. Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses. \newline 5. Remove duplicate rows with SELECT DISTINCT.}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}


% That's all folks
\end{multicols*}

\end{document}