\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{lwbayes91}
\pdfinfo{
  /Title (oracle-sql-query-cheatsheet.pdf)
  /Creator (Cheatography)
  /Author (lwbayes91)
  /Subject (Oracle-SQL-Query-Cheatsheet 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{Oracle-SQL-Query-Cheatsheet Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{lwbayes91} via \textcolor{DarkBackground}{\uline{cheatography.com/38072/cs/11895/}}}
\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}lwbayes91 \\
  \uline{cheatography.com/lwbayes91} \\
  \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 24th May, 2017.\\
   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*}{4}

\begin{tabularx}{3.833cm}{x{0.97056 cm} x{1.03122 cm} x{1.03122 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Basic Select}} (chap7.4)}}  \tn
% Row 0
\SetRowColor{LightBackground}
Select columns & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} &  \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
Conditional select & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} WHERE {\emph{colname1}} = {\emph{value1}} & Other operators: \textgreater{}, \textless{}, \textgreater{}=, \textless{}=, \textless{}\textgreater{}(or !=) \tn 
% Row Count 10 (+ 6)
% Row 2
\SetRowColor{LightBackground}
Select based on logical conditions & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} WHERE {\emph{colname1}} = {\emph{value1}} AND {\emph{colname2}}  \textgreater{}  {\emph{value2}} & Other logical operators: OR, NOT \tn 
% Row Count 18 (+ 8)
% Row 3
\SetRowColor{white}
Select based on value in range & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} WHERE {\emph{colname1}} BETWEEN {\emph{lower limit}} AND {\emph{upper limite}} & Smaller value must be first in Oracle \tn 
% Row Count 26 (+ 8)
% Row 4
\SetRowColor{LightBackground}
Select null values & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} WHERE {\emph{colname3}} IS NULL &  \tn 
% Row Count 32 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{3.833cm}{x{0.97056 cm} x{1.03122 cm} x{1.03122 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Basic Select}} (chap7.4) (cont)}}  \tn
% Row 5
\SetRowColor{LightBackground}
Select based on fuzzy string matching & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} WHERE {\emph{colname1}} LIKE {\emph{'\%word\%'}} & '\%' matches one or more characters, '\_' matches exactly one character; LIKE is case sensitive in Oracle \tn 
% Row Count 8 (+ 8)
% Row 6
\SetRowColor{white}
Select based on value in a list & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} WHERE {\emph{colname1}} in ({\emph{value1, value2}}) & all members in list must be same type; can be used in conjuction with subqueries \tn 
% Row Count 15 (+ 7)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{x{0.81891 cm} x{1.09188 cm} x{1.12221 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Intermediate select}}}}  \tn
% Row 0
\SetRowColor{LightBackground}
Order results of select query & SELECT {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} ORDER BY {\emph{colname1}}, {\emph{colname2}} &  \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
List unique values only & SELECT DISTINCT  {\emph{colname1}}, {\emph{colname2}} FROM {\bf{Table1}} & In Oracle, Null value will be included as one distince value; use ORDER BY {\emph{colname}} NULLS FIRST to place nulls at top of list \tn 
% Row Count 15 (+ 9)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{x{1.03122 cm} x{1.00089 cm} x{1.00089 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Aggregate functions}}}}  \tn
% Row 0
\SetRowColor{LightBackground}
Count NON-NULL values of an attribute & SELECT COUNT({\emph{colname1}}) FROM {\bf{Table1}} &  \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
Count unique NON-Null values & SELECT COUNT(DISTINCT({\emph{colname1}})) FROM {\bf{Table1}} &  \tn 
% Row Count 8 (+ 4)
% Row 2
\SetRowColor{LightBackground}
Select maximum and minimum values in an attribute & SELECT MAX/MIN({\emph{colname1)}} FROM {\bf{Table1}} & Yields ONLY ONE VALUE; can only be used inside a SELECT query \tn 
% Row Count 13 (+ 5)
% Row 3
\SetRowColor{white}
Select sum and average of an attribute & SELECT SUM/AVG({\emph{colname1)}} FROM {\bf{Table1}} & Yields ONLY ONE VALUE; can only be used inside a SELECT query \tn 
% Row Count 18 (+ 5)
% Row 4
\SetRowColor{LightBackground}
Use aggregate functions on computed values & SELECT SUM/AVG/MIN/MAX({\emph{colname1 - }}colname2{\emph{)}} FROM {\bf{Table1}} &  \tn 
% Row Count 23 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{x{1.03122 cm} x{1.00089 cm} x{1.00089 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\emph{*Group by clause}}}}  \tn
% Row 0
\SetRowColor{LightBackground}
Calculate aggregate function based on group membership & SELECT {\emph{colname1}}, MAX({\emph{colname2}} ) FROM {\bf{Table1}} GROUP BY {\emph{colname1}} & GROUP BY must be used in conjuction with Aggregate functions; GROUP BY clause must include all columns in the colomn list other than those in the aggregate function; can include columns that are not selected at all \tn 
% Row Count 17 (+ 17)
% Row 1
\SetRowColor{white}
Filter the result of GROUP BY clause with HAVING clause & SELECT {\emph{colname1}}, MAX({\emph{colname2}} )  FROM {\bf{Table1}} GROUP BY {\emph{colname1}} HAVING MAX({\emph{colname2}}) \textgreater{} {\emph{value1}} & In Oracle, column alias can be used for the result of the aggregate function column \tn 
% Row Count 26 (+ 9)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{x{1.75914 cm} x{0.97056 cm} p{0.3033 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Relational Set Operators}}}}  \tn
% Row 0
\SetRowColor{LightBackground}
Get all records from two relations excluding duplicates & Set1 UNION Set2 &  \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
Get all records from two relations including duplicates & Set1 UNION ALL Set2 &  \tn 
% Row Count 6 (+ 3)
% Row 2
\SetRowColor{LightBackground}
Get all records in both relations & Set1 INTERSECT Set2 &  \tn 
% Row Count 8 (+ 2)
% Row 3
\SetRowColor{white}
Get all recods in relations 1 but not in relation 2 & Set1 MINUS Set2 &  \tn 
% Row Count 11 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{x{0.9099 cm} x{1.06155 cm} x{1.06155 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Utility Functions}}}}  \tn
% Row 0
\SetRowColor{LightBackground}
Formatting datetime into character & \seqsplit{TO\_CHAR(date\_value}, fmt) & MONTH; MON; MM; \seqsplit{D(DOW);DD(DOM);} DAY;YYYY;YY \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
Formatting character into datetime & \seqsplit{TO\_DATE(charater\_string)} & Same as above \tn 
% Row Count 7 (+ 3)
% Row 2
\SetRowColor{LightBackground}
Current date & SYSDATE &  \tn 
% Row Count 8 (+ 1)
% Row 3
\SetRowColor{white}
Adding months to a date & \seqsplit{ADD\_MONTHS(date\_value}, n) &  \tn 
% Row Count 10 (+ 2)
% Row 4
\SetRowColor{LightBackground}
Absolute value & ABS() &  \tn 
% Row Count 12 (+ 2)
% Row 5
\SetRowColor{white}
Round falue to a specific precision & \seqsplit{ROUND(numeric\_value}, p) &  \tn 
% Row Count 15 (+ 3)
% Row 6
\SetRowColor{LightBackground}
Ceiling value & \seqsplit{CEIL(numeric\_value)} &  \tn 
% Row Count 17 (+ 2)
% Row 7
\SetRowColor{white}
Floor value & \seqsplit{FLOOR(numeric\_value)} &  \tn 
% Row Count 19 (+ 2)
% Row 8
\SetRowColor{LightBackground}
\seqsplit{Concatenating} String & {\emph{string1}} || {\emph{string2}}|| {\emph{string3}} &  \tn 
% Row Count 22 (+ 3)
% Row 9
\SetRowColor{white}
Uppercase & \seqsplit{UPPER(string\_value)} &  \tn 
% Row Count 24 (+ 2)
% Row 10
\SetRowColor{LightBackground}
Lowercase & \seqsplit{LOWER(string\_value)} &  \tn 
% Row Count 26 (+ 2)
% Row 11
\SetRowColor{white}
Subset a string & \seqsplit{SUBSTR(string\_value}, \seqsplit{starting\_index}, \seqsplit{ending\_index)} &  \tn 
% Row Count 30 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{3.833cm}{x{0.9099 cm} x{1.06155 cm} x{1.06155 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Utility Functions}} (cont)}}  \tn
% Row 12
\SetRowColor{LightBackground}
length of a string & \seqsplit{LENGTH(string\_value)} &  \tn 
% Row Count 2 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{3.833cm}{x{2.7464 cm} p{0.6866 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{3.833cm}}{\bf\textcolor{white}{{\bf{Join}}}}  \tn
% Row 0
\SetRowColor{LightBackground}
Inner joins & INNER JOIN \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
Join two table on common attributes but retain rows in the first table without a match & LEFT JOIN \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
Join two table on common attributes but retain rows in the second table without a match & RIGHT JOIN \tn 
% Row Count 8 (+ 3)
% Row 3
\SetRowColor{white}
Join two table on common attributes but retain unmatched rows & FULL JOIN \tn 
% Row Count 10 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}


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

\end{document}