\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{DarioPittera (aggialavura)}
\pdfinfo{
  /Title (sql.pdf)
  /Creator (Cheatography)
  /Author (DarioPittera (aggialavura))
  /Subject (SQL 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}{43C4B7}
\definecolor{LightBackground}{HTML}{F3FBFA}
\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 Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{DarioPittera (aggialavura)} via \textcolor{DarkBackground}{\uline{cheatography.com/83764/cs/44620/}}}
\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}DarioPittera (aggialavura) \\
  \uline{cheatography.com/aggialavura} \\
        \uline{\seqsplit{www}.dariopittera.com}
  \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 16th October, 2024.\\
   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*}{3}

\begin{tabularx}{5.377cm}{x{2.4885 cm} x{2.4885 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{5.377cm}}{\bf\textcolor{white}{General commands}}  \tn
% Row 0
\SetRowColor{LightBackground}
create database db\_name & create a database \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
use db\_name & use a database \tn 
% Row Count 3 (+ 1)
% Row 2
\SetRowColor{LightBackground}
select distinct & select unique values \tn 
% Row Count 4 (+ 1)
% Row 3
\SetRowColor{white}
select * into new\_tbl & copy a table \tn 
% Row Count 6 (+ 2)
% Row 4
\SetRowColor{LightBackground}
select top (n) & select the first X rows \tn 
% Row Count 8 (+ 2)
% Row 5
\SetRowColor{white}
select top (0) * into new\_tbl from original\_tbl & copy only the headers \tn 
% Row Count 11 (+ 3)
% Row 6
\SetRowColor{LightBackground}
order by & order by a column \tn 
% Row Count 12 (+ 1)
% Row 7
\SetRowColor{white}
and / or & AND OR operators \tn 
% Row Count 13 (+ 1)
% Row 8
\SetRowColor{LightBackground}
where & conditional selection \tn 
% Row Count 15 (+ 2)
% Row 9
\SetRowColor{white}
between & to choose values between two \tn 
% Row Count 17 (+ 2)
% Row 10
\SetRowColor{LightBackground}
in ('','') & check if contained in a list \tn 
% Row Count 19 (+ 2)
% Row 11
\SetRowColor{white}
insert into tbl | values () & insert values into a table \tn 
% Row Count 21 (+ 2)
% Row 12
\SetRowColor{LightBackground}
where is null & select null values \tn 
% Row Count 22 (+ 1)
% Row 13
\SetRowColor{white}
update | set | where col is null & insert values into null cells of a col \tn 
% Row Count 24 (+ 2)
% Row 14
\SetRowColor{LightBackground}
delete from tbl where xx & delete certain values \tn 
% Row Count 26 (+ 2)
% Row 15
\SetRowColor{white}
sum(), count(), avg() - groupby & sum, count, and avg functions \tn 
% Row Count 28 (+ 2)
% Row 16
\SetRowColor{LightBackground}
having & condition on grouped data \tn 
% Row Count 30 (+ 2)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{5.377cm}{x{2.4885 cm} x{2.4885 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{5.377cm}}{\bf\textcolor{white}{General commands (cont)}}  \tn
% Row 17
\SetRowColor{LightBackground}
inner join / join | on & return rows with match in both tables \tn 
% Row Count 2 (+ 2)
% Row 18
\SetRowColor{white}
left / right join | on & return common rows + left or right unique values \tn 
% Row Count 5 (+ 3)
% Row 19
\SetRowColor{LightBackground}
full outer join / outer join | on & return matching and non-matching values \tn 
% Row Count 7 (+ 2)
% Row 20
\SetRowColor{white}
inner join/ join | on with same tbl & = self join - duplicate entries \tn 
% Row Count 9 (+ 2)
% Row 21
\SetRowColor{LightBackground}
union all & merge two tables into one WITH DUPLICATES \tn 
% Row Count 12 (+ 3)
% Row 22
\SetRowColor{white}
union & merge two tables into one WITHOUT DUPLICATES \tn 
% Row Count 15 (+ 3)
% Row 23
\SetRowColor{LightBackground}
like | \% - \_ & to match a condition with multiple chars (\%) or one char (\_) \tn 
% Row Count 18 (+ 3)
% Row 24
\SetRowColor{white}
case & create new col based on value of a different one \tn 
% Row Count 21 (+ 3)
% Row 25
\SetRowColor{LightBackground}
create table tbl\_name & create a new table \tn 
% Row Count 23 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{5.377cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{5.377cm}}{\bf\textcolor{white}{Data types}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{5.377cm}}{{\bf{STRINGS}}: \newline % Row Count 1 (+ 1)
- char() - when we know it is a set len we can provide it between parenthesis \newline % Row Count 3 (+ 2)
- varchar() - when the length is variable - we can set the max length \newline % Row Count 5 (+ 2)
- nchar() - like char but supports unicode chars \newline % Row Count 6 (+ 1)
- nvarchar() - like varchar but supports unicode chars \newline % Row Count 8 (+ 2)
{\bf{NUMBERS}} \newline % Row Count 9 (+ 1)
- int \newline % Row Count 10 (+ 1)
- bigint \newline % Row Count 11 (+ 1)
- smallint \newline % Row Count 12 (+ 1)
- tinyint \newline % Row Count 13 (+ 1)
- decimal(p,s) - here p = total digits, s = digits after comma -\textgreater{} 999.99 = decimal(5,2) \newline % Row Count 15 (+ 2)
{\bf{DATE}}: \{\{link="https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16 \newline % Row Count 18 (+ 3)
"\}\}link text\{\{/link\}\} \newline % Row Count 19 (+ 1)
- data  \newline % Row Count 20 (+ 1)
- time data \newline % Row Count 21 (+ 1)
- datetime% Row Count 22 (+ 1)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{5.377cm}{x{2.4885 cm} x{2.4885 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{5.377cm}}{\bf\textcolor{white}{Constraints}}  \tn
% Row 0
\SetRowColor{LightBackground}
not null \textbackslash{} alter column col\_name type not null & value must be not null \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
unique \textbackslash{} add unique(col) & must have a unique value \tn 
% Row Count 5 (+ 2)
% Row 2
\SetRowColor{LightBackground}
check(col\_name \textgreater{}= x) \textbackslash{} add check(col\_name\textgreater{}=x) & check if satisfy a condition \tn 
% Row Count 8 (+ 3)
% Row 3
\SetRowColor{white}
default \textbackslash{} add default col\_name x & add a default if nothing has been provided \tn 
% Row Count 11 (+ 3)
% Row 4
\SetRowColor{LightBackground}
primary key \textbackslash{} add primary key (col) & assign the primary key to a col - has to be not null \tn 
% Row Count 14 (+ 3)
% Row 5
\SetRowColor{white}
foreign key references tbl\_name(primary\_key\_col)\textbackslash{} add foreign key (col) references \seqsplit{tbl(primary\_key\_col)} & assign foreign key to a col - cannot have values outside the primary key \tn 
% Row Count 20 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{5.377cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{5.377cm}}{\bf\textcolor{white}{Order of execution}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{5.377cm}}{1. from and join \newline % Row Count 1 (+ 1)
2. where \newline % Row Count 2 (+ 1)
3. group by \newline % Row Count 3 (+ 1)
4. having \newline % Row Count 4 (+ 1)
5. select \newline % Row Count 5 (+ 1)
6. distinct \newline % Row Count 6 (+ 1)
7. order by \newline % Row Count 7 (+ 1)
8. top \newline % Row Count 8 (+ 1)
This means that for example if you give an alias in SELECT and then you try to use it in HAVING, it will throw an error because the select piece did not happened yet.% Row Count 12 (+ 4)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{5.377cm}{x{2.4885 cm} x{2.4885 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{5.377cm}}{\bf\textcolor{white}{Window functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
raw\_number() over() & assign a unique number to each row, if there are ties, the numbers are assigned randomly \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
rank() over() & assign a number based on ranking. If there are ties, it will skip a number/s \tn 
% Row Count 9 (+ 4)
% Row 2
\SetRowColor{LightBackground}
dense\_rank() over() & assign a number based on ranking. If there are ties, it won't skip the number/s \tn 
% Row Count 13 (+ 4)
% Row 3
\SetRowColor{white}
lead(col) over() & allows you to access data from the subsequent row \tn 
% Row Count 16 (+ 3)
% Row 4
\SetRowColor{LightBackground}
lag() over() & access the previous row \tn 
% Row Count 18 (+ 2)
% Row 5
\SetRowColor{white}
isnull(col, \seqsplit{'value\_to\_insert')} & accepts two parameters: where to look, what to input insteadinput instead \tn 
% Row Count 22 (+ 4)
% Row 6
\SetRowColor{LightBackground}
coalescence(col1,col2,col3,'value\_to\_insert') & accepts multiple parameters and it will output the first NON-NULL value \tn 
% Row Count 26 (+ 4)
% Row 7
\SetRowColor{white}
first\_value(col) over() & will create a new column where the first cell value of an expression is written \tn 
% Row Count 30 (+ 4)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{5.377cm}{x{2.4885 cm} x{2.4885 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{5.377cm}}{\bf\textcolor{white}{Window functions (cont)}}  \tn
% Row 8
\SetRowColor{LightBackground}
last\_value(col) over(rows between unbounded preceding and unbounded following) & will create a new column where the last cell value of an expression is written \tn 
% Row Count 4 (+ 4)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{5.377cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{5.377cm}}{\bf\textcolor{white}{Stored procedures}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{5.377cm}}{Stored procedures are precompiled set of SQL statements stored in the database that can be executed as a single unit.  \newline % Row Count 3 (+ 3)
To create them we will use {\bf{CREATE PROCEDURE}} \_name\_ {\bf{AS}} {\bf{BEGIN}} \_code\_ {\bf{END}}. \newline % Row Count 5 (+ 2)
Example: \newline % Row Count 6 (+ 1)
create procedure see\_all -{}- or create proc \newline % Row Count 7 (+ 1)
as \newline % Row Count 8 (+ 1)
begin \newline % Row Count 9 (+ 1)
	select * from Employees \newline % Row Count 10 (+ 1)
end \newline % Row Count 11 (+ 1)
-{}- call the procedure \newline % Row Count 12 (+ 1)
execute see\_all \newline % Row Count 13 (+ 1)
exec see\_all \newline % Row Count 14 (+ 1)
see\_all \newline % Row Count 15 (+ 1)
It can also accept parameters: \newline % Row Count 16 (+ 1)
-{}- create a procedure that accepts parameters \newline % Row Count 17 (+ 1)
CREATE PROCEDURE GetEmployeesByDepartment \newline % Row Count 18 (+ 1)
    @DepartmentID INT -{}- = 101 to set default to 101 for instance \newline % Row Count 20 (+ 2)
AS \newline % Row Count 21 (+ 1)
BEGIN \newline % Row Count 22 (+ 1)
    SELECT EmployeeID, FirstName, LastName \newline % Row Count 23 (+ 1)
    FROM Employees \newline % Row Count 24 (+ 1)
    WHERE DepartmentID = @DepartmentID; \newline % Row Count 25 (+ 1)
END; \newline % Row Count 26 (+ 1)
exec GetEmployeesByDepartment 101% Row Count 27 (+ 1)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{5.377cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{5.377cm}}{\bf\textcolor{white}{Views}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{5.377cm}}{A {\bf{view}} in SQL is a virtual table based on the result of a SELECT query. Unlike physical tables, views don't store data themselves; they display data dynamically from the underlying tables whenever queried.  \newline % Row Count 5 (+ 5)
Views are helpful for: \newline % Row Count 6 (+ 1)
- simplifying complex queries,  \newline % Row Count 7 (+ 1)
- improving code maintainability, and  \newline % Row Count 8 (+ 1)
- enhancing security by controlling data access. \newline % Row Count 9 (+ 1)
CREATE VIEW view1 AS  \newline % Row Count 10 (+ 1)
SELECT column1, column2, ...  \newline % Row Count 11 (+ 1)
FROM table\_name  \newline % Row Count 12 (+ 1)
WHERE condition; \newline % Row Count 13 (+ 1)
select * from view1 \newline % Row Count 14 (+ 1)
NOTE!!! If you make changes on the View, they will be reflected on the original table as well!% Row Count 16 (+ 2)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}


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

\end{document}