\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{Arshdeep}
\pdfinfo{
  /Title (mysql-concepts.pdf)
  /Creator (Cheatography)
  /Author (Arshdeep)
  /Subject (MySQL Concepts 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}{254BA3}
\definecolor{LightBackground}{HTML}{F1F3F9}
\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{MySQL Concepts Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{Arshdeep} via \textcolor{DarkBackground}{\uline{cheatography.com/201979/cs/42813/}}}
\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}Arshdeep \\
  \uline{cheatography.com/arshdeep} \\
  \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 23rd March, 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*}{2}

\begin{tabularx}{8.4cm}{x{1.976 cm} x{2.812 cm} x{2.812 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Operators in MySQL}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Comparison} Operators & Logical Operators & Arithmetic Operators \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
=: Equal to & AND: Returns true if all conditions separated by AND are true & +: Addition \tn 
% Row Count 7 (+ 5)
% Row 2
\SetRowColor{LightBackground}
\textless{}\textgreater{} or !=: Not equal to & OR: Returns true if any condition separated by OR is true & -: Subtraction \tn 
% Row Count 12 (+ 5)
% Row 3
\SetRowColor{white}
\textless{}: Less than & NOT: Reverses the value of the following condition & *: \seqsplit{Multiplication} \tn 
% Row Count 16 (+ 4)
% Row 4
\SetRowColor{LightBackground}
\textgreater{}: Greater than & NULL Operators & /: Division \tn 
% Row Count 18 (+ 2)
% Row 5
\SetRowColor{white}
\textless{}=: Less than or equal to & IS NULL: Checks if a value is NULL & \%: Modulus (Returns the remainder of a division) \tn 
% Row Count 22 (+ 4)
% Row 6
\SetRowColor{LightBackground}
\textgreater{}=: Greater than or equal to & IS NOT NULL: Checks if a value is not NULL & LIKE: Used for pattern matching in strings \tn 
% Row Count 25 (+ 3)
\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}{String Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Function} & Explanation & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\seqsplit{CONCAT()} & Concatenates two or more strings. & SELECT CONCAT('Hello ', 'World') AS \seqsplit{ConcatenatedString;} -{}- Output: Hello World \tn 
% Row Count 7 (+ 5)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{SUBSTRING()} & Extracts a substring from a string. & SELECT \seqsplit{SUBSTRING('MySQL'}, 2, 3) AS SubstringResult; -{}- Output: ySQ \tn 
% Row Count 11 (+ 4)
% Row 3
\SetRowColor{white}
\seqsplit{UPPER()} & Converts a string to uppercase. & SELECT UPPER('mysql') AS UppercaseString; -{}- Output: MYSQL \tn 
% Row Count 15 (+ 4)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{LOWER()} & Converts a string to lowercase. & SELECT LOWER('MYSQL') AS LowercaseString; -{}- Output: mysql \tn 
% Row Count 19 (+ 4)
% Row 5
\SetRowColor{white}
\seqsplit{LENGTH()} & Returns the length of a string. & SELECT LENGTH('MySQL') AS StringLength; -{}- Output: 5 \tn 
% Row Count 23 (+ 4)
% Row 6
\SetRowColor{LightBackground}
\seqsplit{TRIM()} & Removes leading and trailing spaces from a string. & SELECT TRIM('  MySQL  ') AS TrimmedString; -{}- Output: MySQL \tn 
% Row Count 27 (+ 4)
% Row 7
\SetRowColor{white}
\seqsplit{REPLACE()} & Replaces occurrences of a specified substring within a string. & SELECT REPLACE('Hello World', 'World', 'MySQL') AS ReplacedString; -{}- Output: Hello MySQL \tn 
% Row Count 33 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{0.836 cm} x{3.116 cm} x{3.648 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Date and Time Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Function} & Explanation & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
NOW() & Returns the current date and time. & SELECT NOW() AS CurrentDateTime; -{}- Output: Current date and time in 'YYYY-MM-DD HH:MM:SS' format \tn 
% Row Count 8 (+ 6)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{CURDATE()} & Returns the current date. & SELECT CURDATE() AS CurrentDate; -{}- Output: Current date in 'YYYY-MM-DD' format \tn 
% Row Count 13 (+ 5)
% Row 3
\SetRowColor{white}
\seqsplit{CURTIME()} & Returns the current time. & SELECT CURTIME() AS CurrentTime; -{}- Output: Current time in 'HH:MM:SS' format \tn 
% Row Count 18 (+ 5)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{YEAR()} & Extracts the year from a date. & SELECT YEAR('2024-03-23') AS ExtractedYear; -{}- Output: 2024 \tn 
% Row Count 22 (+ 4)
% Row 5
\SetRowColor{white}
\seqsplit{MONTH()} & Extracts the month from a date. & SELECT \seqsplit{MONTH('2024-03-23')} AS ExtractedMonth; -{}- Output: 3 \tn 
% Row Count 26 (+ 4)
% Row 6
\SetRowColor{LightBackground}
DAY() & Extracts the day from a date. & SELECT DAY('2024-03-23') AS ExtractedDay; -{}- Output: 23 \tn 
% Row Count 29 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.292 cm} x{3.116 cm} x{3.192 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Window Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Function} & Explanation & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\seqsplit{ROW\_NUMBER()} & This function assigns a unique integer to each row within a partition according to the specified order. It starts from 1 for the first row and increments by 1 for each subsequent row. & SELECT      name,      ROW\_NUMBER() OVER (ORDER BY salary DESC) AS row\_num FROM      employees; \tn 
% Row Count 14 (+ 12)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{RANK()} & Similar to ROW\_NUMBER(), but RANK() assigns the same rank to rows with equal values and leaves gaps in the sequence for ties. & SELECT      name,      RANK() OVER (ORDER BY score DESC) AS rank FROM      students; \tn 
% Row Count 22 (+ 8)
% Row 3
\SetRowColor{white}
\seqsplit{DENSE\_RANK()} & DENSE\_RANK() is similar to RANK(), but it does not leave gaps in the ranking sequence for ties. & SELECT      name,      DENSE\_RANK() OVER (ORDER BY age) AS dense\_rank FROM      users; \tn 
% Row Count 28 (+ 6)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{NTILE()} & This function divides the result set into a specified number of buckets and assigns a bucket number to each row. It ensures an approximately equal number of rows in each bucket. & SELECT      name,      salary,     NTILE(4) OVER (ORDER BY salary) AS quartile FROM      employees; \tn 
% Row Count 40 (+ 12)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{1.292 cm} x{3.116 cm} x{3.192 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Window Functions (cont)}}  \tn
% Row 5
\SetRowColor{LightBackground}
\seqsplit{LEAD()} and LAG() & LEAD() and LAG() functions allow you to access data from a subsequent or previous row in the result set, respectively. & SELECT      name,      salary,     LEAD(salary) OVER (ORDER BY salary) AS next\_salary,     LAG(salary) OVER (ORDER BY salary) AS \seqsplit{previous\_salary} FROM      employees; \tn 
% Row Count 11 (+ 11)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.512 cm} x{1.944 cm} x{1.872 cm} x{1.872 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{8.4cm}}{\bf\textcolor{white}{Joins}}  \tn
% Row 0
\SetRowColor{LightBackground}
Join & \seqsplit{Explanation} & Syntax & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
INNER JOIN & Returns records that have matching values in both tables. & SELECT columns FROM table1 INNER JOIN table2 ON \seqsplit{table1.column} = \seqsplit{table2.column;} & SELECT \seqsplit{orders.order\_id}, \seqsplit{customers.customer\_name} FROM orders INNER JOIN customers ON \seqsplit{orders.customer\_id} = \seqsplit{customers.customer\_id;} \tn 
% Row Count 15 (+ 13)
% Row 2
\SetRowColor{LightBackground}
LEFT JOIN (or LEFT OUTER JOIN) & Returns all records from the left table and the matched records from the right table. If there's no match, the result is NULL on the right side. & SELECT columns FROM table1 LEFT JOIN table2 ON \seqsplit{table1.column} = \seqsplit{table2.column;} & SELECT \seqsplit{customers.customer\_name}, \seqsplit{orders.order\_id} FROM customers LEFT JOIN orders ON \seqsplit{customers.customer\_id} = \seqsplit{orders.customer\_id;} \tn 
% Row Count 30 (+ 15)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{1.512 cm} x{1.944 cm} x{1.872 cm} x{1.872 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{8.4cm}}{\bf\textcolor{white}{Joins (cont)}}  \tn
% Row 3
\SetRowColor{LightBackground}
RIGHT JOIN (or RIGHT OUTER JOIN) & Returns all records from the right table and the matched records from the left table. If there's no match, the result is NULL on the left side. & SELECT columns FROM table1 RIGHT JOIN table2 ON \seqsplit{table1.column} = \seqsplit{table2.column;} & SELECT \seqsplit{orders.order\_id}, \seqsplit{customers.customer\_name} FROM orders RIGHT JOIN customers ON \seqsplit{orders.customer\_id} = \seqsplit{customers.customer\_id;} \tn 
% Row Count 15 (+ 15)
% Row 4
\SetRowColor{white}
FULL JOIN (or FULL OUTER JOIN) & Returns all records when there's a match in either left or right table. If there's no match, the result is NULL on the unmatched side. & SELECT columns FROM table1 FULL JOIN table2 ON \seqsplit{table1.column} = \seqsplit{table2.column;} & SELECT \seqsplit{customers.customer\_name}, \seqsplit{orders.order\_id} FROM customers FULL JOIN orders ON \seqsplit{customers.customer\_id} = \seqsplit{orders.customer\_id;} \tn 
% Row Count 29 (+ 14)
% Row 5
\SetRowColor{LightBackground}
CROSS JOIN & Returns the Cartesian product of the two tables, i.e., all possible \seqsplit{combinations} of rows. & SELECT columns FROM table1 CROSS JOIN table2; & SELECT * FROM employees CROSS JOIN \seqsplit{departments;} \tn 
% Row Count 38 (+ 9)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{1.512 cm} x{1.944 cm} x{1.872 cm} x{1.872 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{8.4cm}}{\bf\textcolor{white}{Joins (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
\seqsplit{Self-Join} & Joins a table with itself, typically used to compare rows within the same table. & SELECT columns FROM table1 alias1 INNER JOIN table1 alias2 ON \seqsplit{alias1.column} = \seqsplit{alias2.column;} & SELECT \seqsplit{e1.employee\_name}, \seqsplit{e2.manager\_name} FROM employees e1 INNER JOIN employees e2 ON \seqsplit{e1.manager\_id} = \seqsplit{e2.employee\_id;} \tn 
% Row Count 12 (+ 12)
\hhline{>{\arrayrulecolor{DarkBackground}}----}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.2 cm} x{4.8 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Stored Procedure}}  \tn
% Row 0
\SetRowColor{LightBackground}
Definition & A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. It's like a function in a traditional programming language. \tn 
% Row Count 7 (+ 7)
% Row 1
\SetRowColor{white}
Syntax & CREATE PROCEDURE procedure\_name (parameters) BEGIN     -{}- SQL statements END; \tn 
% Row Count 11 (+ 4)
% Row 2
\SetRowColor{LightBackground}
Parameters & Stored procedures can accept input parameters, which can be used within the procedure's SQL statements. \tn 
% Row Count 16 (+ 5)
% Row 3
\SetRowColor{white}
Example & CREATE PROCEDURE GetEmployee(IN emp\_id INT) BEGIN     SELECT * FROM employees WHERE employee\_id = emp\_id; END; \tn 
% Row Count 21 (+ 5)
% Row 4
\SetRowColor{LightBackground}
Calling a Stored Procedure & CALL \seqsplit{procedure\_name(arguments);} \tn 
% Row Count 23 (+ 2)
% Row 5
\SetRowColor{white}
Example & CALL GetEmployee(1001); \tn 
% Row Count 24 (+ 1)
% Row 6
\SetRowColor{LightBackground}
Dropping a Stored Procedure & DROP PROCEDURE IF EXISTS procedure\_name; \tn 
% Row Count 26 (+ 2)
% Row 7
\SetRowColor{white}
Example & DROP PROCEDURE IF EXISTS GetEmployee; \tn 
% Row Count 28 (+ 2)
% Row 8
\SetRowColor{LightBackground}
Variables & Stored procedures can declare and use variables within their code. \tn 
% Row Count 31 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{3.2 cm} x{4.8 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Stored Procedure (cont)}}  \tn
% Row 9
\SetRowColor{LightBackground}
Example & CREATE PROCEDURE UpdateSalary(IN emp\_id INT, IN salary DECIMAL(10, 2)) BEGIN     DECLARE emp\_name VARCHAR(50);     SELECT employee\_name INTO emp\_name FROM employees WHERE employee\_id = emp\_id;     UPDATE employees SET employee\_salary = salary WHERE employee\_id = emp\_id; END; \tn 
% Row Count 12 (+ 12)
% Row 10
\SetRowColor{white}
Control Flow & Stored procedures support control flow constructs such as IF, CASE, and LOOP. \tn 
% Row Count 16 (+ 4)
% Row 11
\SetRowColor{LightBackground}
Example & CREATE PROCEDURE CheckAge(IN age INT) BEGIN     IF age \textless{} 18 THEN         SELECT 'Minor';     ELSEIF age BETWEEN 18 AND 64 THEN         SELECT 'Adult';     ELSE         SELECT 'Senior';     END IF; END; \tn 
% Row Count 25 (+ 9)
% Row 12
\SetRowColor{white}
Cursors & Stored procedures can use cursors to process multiple rows returned by a query. \tn 
% Row Count 29 (+ 4)
% Row 13
\SetRowColor{LightBackground}
Example & CREATE PROCEDURE DisplayEmployees() BEGIN     DECLARE done BOOLEAN DEFAULT FALSE;     DECLARE emp\_name VARCHAR(50);     DECLARE emp\_salary DECIMAL(10, 2);     DECLARE emp\_cursor CURSOR FOR SELECT employee\_name, employee\_salary FROM employees;     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;      OPEN emp\_cursor;     read\_loop: LOOP         FETCH emp\_cursor INTO emp\_name, emp\_salary;         IF done THEN             LEAVE read\_loop;         END IF;         -{}- Process fetched data     END LOOP;     CLOSE emp\_cursor; END; \tn 
% Row Count 52 (+ 23)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{2.56 cm} x{5.44 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Indexing}}  \tn
% Row 0
\SetRowColor{LightBackground}
Indexing & Indexing is a way to optimize database performance by quickly locating rows in a table. It allows for faster retrieval of data by creating a sorted reference to the data in a table. \tn 
% Row Count 7 (+ 7)
% Row 1
\SetRowColor{white}
Types & Single Column Index, Composite Index, Unique Index, Primary Key, and Foreign Key \tn 
% Row Count 10 (+ 3)
% Row 2
\SetRowColor{LightBackground}
Single Column Index & Index created on a single column. \tn 
% Row Count 12 (+ 2)
% Row 3
\SetRowColor{white}
Composite Index & Index created on multiple columns. \tn 
% Row Count 14 (+ 2)
% Row 4
\SetRowColor{LightBackground}
Unique Index & Index where all values must be unique (no duplicate values). \tn 
% Row Count 17 (+ 3)
% Row 5
\SetRowColor{white}
Primary Key & Unique index with the constraint that all values must be unique and not NULL. Typically used to uniquely identify each row in a table. \tn 
% Row Count 22 (+ 5)
% Row 6
\SetRowColor{LightBackground}
Foreign Key & Index that references the primary key in another table. Used to establish relationships between tables. \tn 
% Row Count 26 (+ 4)
% Row 7
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{Creating Indexes} \tn 
% Row Count 27 (+ 1)
% Row 8
\SetRowColor{LightBackground}
Syntax & CREATE {[}UNIQUE{]} INDEX index\_name ON table\_name (column\_name); \tn 
% Row Count 30 (+ 3)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{2.56 cm} x{5.44 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Indexing (cont)}}  \tn
% Row 9
\SetRowColor{LightBackground}
Example & CREATE INDEX idx\_lastname ON employees (last\_name); \tn 
% Row Count 2 (+ 2)
% Row 10
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{Dropping Indexes:} \tn 
% Row Count 3 (+ 1)
% Row 11
\SetRowColor{LightBackground}
Syntax & DROP INDEX index\_name ON table\_name; \tn 
% Row Count 5 (+ 2)
% Row 12
\SetRowColor{white}
Example & DROP INDEX idx\_lastname ON employees; \tn 
% Row Count 7 (+ 2)
% Row 13
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Viewing Indexes:} \tn 
% Row Count 8 (+ 1)
% Row 14
\SetRowColor{white}
Syntax & SHOW INDEX FROM table\_name; \tn 
% Row Count 9 (+ 1)
% Row 15
\SetRowColor{LightBackground}
Example & SHOW INDEX FROM employees; \tn 
% Row Count 10 (+ 1)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Types of SQL Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Scalar Functions: Scalar functions operate on individual rows and return a single result per row. They can be used in SELECT, WHERE, ORDER BY, and other clauses.} \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Aggregate Functions: Aggregate functions operate on sets of rows and return a single result that summarizes the entire set. They are commonly used with the GROUP BY clause.} \tn 
% Row Count 8 (+ 4)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{Window Functions: Window functions perform calculations across a set of rows related to the current row, without collapsing the result set into a single row. They are used with the OVER() clause.} \tn 
% Row Count 12 (+ 4)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{Control Flow Functions: Control flow functions allow conditional execution of logic within SQL statements. They are often used to implement branching or conditional behavior.} \tn 
% Row Count 16 (+ 4)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{User-Defined Functions (UDFs): User-defined functions are custom functions created by users to perform specific tasks that are not provided by built-in functions. They can be written in languages like SQL, C, or C++ and loaded into MySQL.} \tn 
% Row Count 21 (+ 5)
\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}{Numeric Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Function} & Explanation & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
ABS() & Returns the absolute value of a number. & SELECT ABS(-10) AS AbsoluteValue; -{}- Output: 10 \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\seqsplit{ROUND()} & Rounds a number to a specified number of decimal places. & SELECT ROUND(3.14159, 2) AS RoundedNumber; -{}- Output: 3.14 \tn 
% Row Count 9 (+ 4)
% Row 3
\SetRowColor{white}
\seqsplit{CEIL()} & Returns the smallest integer greater than or equal to a number. & SELECT CEIL(3.2) AS CeilingValue; -{}- Output: 4 \tn 
% Row Count 13 (+ 4)
% Row 4
\SetRowColor{LightBackground}
\seqsplit{FLOOR()} & Returns the largest integer less than or equal to a number. & SELECT FLOOR(3.8) AS FloorValue; -{}- Output: 3 \tn 
% Row Count 17 (+ 4)
% Row 5
\SetRowColor{white}
MOD() & Returns the remainder of a division operation. & SELECT MOD(10, 3) AS ModulusValue; -{}- Output: 1 \tn 
% Row Count 20 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.14 cm} x{3.192 cm} x{3.268 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Aggregate Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Function} & Explanation & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\seqsplit{COUNT()} & The COUNT() function returns the number of rows that match a specified condition. & SELECT COUNT(*) AS total\_customers FROM customers; \tn 
% Row Count 8 (+ 6)
% Row 2
\SetRowColor{LightBackground}
SUM() & The SUM() function calculates the sum of values in a column. & SELECT SUM(quantity) AS total\_quantity FROM orders; \tn 
% Row Count 12 (+ 4)
% Row 3
\SetRowColor{white}
AVG() & The AVG() function calculates the average of values in a column. & SELECT AVG(price) AS average\_price FROM products; \tn 
% Row Count 16 (+ 4)
% Row 4
\SetRowColor{LightBackground}
MAX() & The MAX() function returns the maximum value in a column. & SELECT MAX(salary) AS max\_salary FROM employees; \tn 
% Row Count 20 (+ 4)
% Row 5
\SetRowColor{white}
MIN() & The MIN() function returns the minimum value in a column. & SELECT MIN(age) AS min\_age FROM users; \tn 
% Row Count 24 (+ 4)
% Row 6
\SetRowColor{LightBackground}
\seqsplit{GROUP\_CONCAT()} & The GROUP\_CONCAT() function concatenates the values of a column into a single string. & SELECT \seqsplit{GROUP\_CONCAT(product\_name)} AS product\_list FROM products; \tn 
% Row Count 30 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{1.14 cm} x{3.192 cm} x{3.268 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{3}{x{8.4cm}}{\bf\textcolor{white}{Aggregate Functions (cont)}}  \tn
% Row 7
\SetRowColor{LightBackground}
STD() & The STD() function calculates the standard deviation of values in a column. & SELECT STD(sales) AS \seqsplit{sales\_std\_deviation} FROM monthly\_sales; \tn 
% Row Count 5 (+ 5)
% Row 8
\SetRowColor{white}
\seqsplit{VARIANCE()} & The VARIANCE() function calculates the variance of values in a column. & SELECT VARIANCE(height) AS height\_variance FROM students; \tn 
% Row Count 10 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}---}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.008 cm} x{2.016 cm} x{2.088 cm} x{2.088 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{8.4cm}}{\bf\textcolor{white}{Control Flow Functions}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Function} & \seqsplit{Explanation} & Syntax & Example \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
CASE \seqsplit{Statement} & The CASE statement evaluates a list of conditions and returns one of multiple possible result \seqsplit{expressions}. It's similar to a switch or if-else statement in other \seqsplit{programming} languages. & CASE     WHEN condition1 THEN result1     WHEN condition2 THEN result2     ...     ELSE \seqsplit{default\_result} END & SELECT      CASE          WHEN age \textless{} 18 THEN 'Minor'         WHEN age BETWEEN 18 AND 64 THEN 'Adult'         ELSE 'Senior'     END AS age\_group FROM      persons; \tn 
% Row Count 19 (+ 17)
% Row 2
\SetRowColor{LightBackground}
IF() \seqsplit{Function} & The IF() function returns one value if a condition is TRUE and another value if the condition is FALSE. & \seqsplit{IF(condition}, \seqsplit{value\_if\_true}, \seqsplit{value\_if\_false)} & SELECT      IF(score \textgreater{}= 60, 'Pass', 'Fail') AS result FROM      students; \tn 
% Row Count 29 (+ 10)
% Row 3
\SetRowColor{white}
\seqsplit{COALESCE()} \seqsplit{Function} & The COALESCE() function returns the first non-NULL value in a list of \seqsplit{expressions}. & \seqsplit{COALESCE(value1}, value2, ...) & SELECT      \seqsplit{COALESCE(first\_name}, \seqsplit{'Anonymous')} AS \seqsplit{display\_name} FROM      users; \tn 
% Row Count 37 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{1.008 cm} x{2.016 cm} x{2.088 cm} x{2.088 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{4}{x{8.4cm}}{\bf\textcolor{white}{Control Flow Functions (cont)}}  \tn
% Row 4
\SetRowColor{LightBackground}
\seqsplit{NULLIF()} \seqsplit{Function} & The NULLIF() function returns NULL if the two specified \seqsplit{expressions} are equal; otherwise, it returns the first \seqsplit{expression.} & \seqsplit{NULLIF(expression1}, \seqsplit{expression2)} & SELECT      \seqsplit{NULLIF(dividend}, 0) AS result FROM      \seqsplit{calculations;} \tn 
% Row Count 12 (+ 12)
\hhline{>{\arrayrulecolor{DarkBackground}}----}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{4 cm} x{4 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Subqueries}}  \tn
% Row 0
\SetRowColor{LightBackground}
Subquery & Example \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
A subquery, also known as a nested query or inner query, is a query nested within another SQL statement. It allows you to use the result of one query as a part of another query. & SELECT column\_name FROM table\_name WHERE column\_name OPERATOR (SELECT column\_name FROM table\_name WHERE condition); \tn 
% Row Count 10 (+ 9)
% Row 2
\SetRowColor{LightBackground}
Single-Row Subquery: Returns only one row of results. & SELECT name FROM employees WHERE employee\_id = (SELECT manager\_id FROM departments WHERE department\_id = 100); \tn 
% Row Count 16 (+ 6)
% Row 3
\SetRowColor{white}
Multiple-Row Subquery: Returns multiple rows of results. & SELECT product\_name FROM products WHERE category\_id IN (SELECT category\_id FROM categories WHERE category\_name = 'Electronics'); \tn 
% Row Count 23 (+ 7)
% Row 4
\SetRowColor{LightBackground}
Inline View Subquery: Creates a temporary table within a query. & SELECT * FROM (SELECT employee\_id, first\_name, last\_name FROM employees) AS emp\_info WHERE \seqsplit{emp\_info.employee\_id} \textgreater{} 100; \tn 
% Row Count 29 (+ 6)
% Row 5
\SetRowColor{white}
Correlated Subquery: References one or more columns in the outer query. & SELECT product\_name FROM products p WHERE p.unit\_price \textgreater{} (SELECT AVG(unit\_price) FROM products WHERE category\_id = p.category\_id); \tn 
% Row Count 36 (+ 7)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{1.76 cm} x{6.24 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Common Table Expressions (CTE)}}  \tn
% Row 0
\SetRowColor{LightBackground}
\seqsplit{Explanation} & Common Table Expressions (CTEs) provide a way to define temporary result sets that can be referenced within a single SELECT, INSERT, UPDATE, or DELETE statement. They enhance the readability and maintainability of complex queries. \tn 
% Row Count 8 (+ 8)
% Row 1
\SetRowColor{white}
Syntax & WITH cte\_name (column1, column2, ...) AS (     -{}- CTE query     SELECT ...     FROM ...     WHERE ... ) -{}- Main query using the CTE SELECT ... FROM cte\_name; \tn 
% Row Count 14 (+ 6)
% Row 2
\SetRowColor{LightBackground}
Example & -{}- Define a CTE to get the top 5 customers with the highest total orders WITH top\_customers AS (     SELECT customer\_id, SUM(order\_total) AS total\_spent     FROM orders     GROUP BY customer\_id     ORDER BY total\_spent DESC     LIMIT 5 ) -{}- Use the CTE to get detailed information about the top customers SELECT c.customer\_id, c.customer\_name, tc.total\_spent FROM customers c JOIN top\_customers tc ON c.customer\_id = tc.customer\_id; \tn 
% Row Count 28 (+ 14)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.04 cm} x{4.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Views}}  \tn
% Row 0
\SetRowColor{LightBackground}
Explanation & Views in MySQL are virtual tables created by executing a SELECT query and are stored in the database. They allow users to simplify complex queries, restrict access to certain columns, and provide a layer of abstraction over the underlying tables. \tn 
% Row Count 11 (+ 11)
% Row 1
\SetRowColor{white}
Syntax to Create Views & CREATE VIEW view\_name AS SELECT column1, column2, ... FROM table\_name WHERE condition; \tn 
% Row Count 15 (+ 4)
% Row 2
\SetRowColor{LightBackground}
Example to Create Views & CREATE VIEW customer\_contacts AS SELECT customer\_id, first\_name, last\_name, email FROM customers WHERE subscription\_status = 'active'; \tn 
% Row Count 21 (+ 6)
% Row 3
\SetRowColor{white}
Syntax to Drop Views & DROP VIEW view\_name; \tn 
% Row Count 23 (+ 2)
% Row 4
\SetRowColor{LightBackground}
Example to Drop Views & DROP VIEW customer\_contacts; \tn 
% Row Count 25 (+ 2)
% Row 5
\SetRowColor{white}
Syntax to Update View & CREATE OR REPLACE VIEW view\_name AS SELECT new\_column1, new\_column2, ... FROM new\_table WHERE new\_condition; \tn 
% Row Count 30 (+ 5)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{3.04 cm} x{4.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Views (cont)}}  \tn
% Row 6
\SetRowColor{LightBackground}
Example to Update View & CREATE OR REPLACE VIEW active\_customers AS SELECT customer\_id, first\_name, last\_name, email FROM customers WHERE subscription\_status = 'active'; \tn 
% Row Count 6 (+ 6)
% Row 7
\SetRowColor{white}
Syntax to Retrieve Data & SELECT * FROM view\_name; \tn 
% Row Count 8 (+ 2)
% Row 8
\SetRowColor{LightBackground}
Example to Retrieve Data & SELECT * FROM customer\_contacts; \tn 
% Row Count 10 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.04 cm} x{4.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Trigger}}  \tn
% Row 0
\SetRowColor{LightBackground}
Introduction & A trigger is a database object that automatically performs an action in response to certain events on a particular table. \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
Syntax & CREATE TRIGGER trigger\_name \{BEFORE | AFTER\} \{INSERT | UPDATE | DELETE\} ON table\_name FOR EACH ROW trigger\_body \tn 
% Row Count 11 (+ 5)
% Row 2
\SetRowColor{LightBackground}
trigger\_name & Name of the trigger. \tn 
% Row Count 12 (+ 1)
% Row 3
\SetRowColor{white}
BEFORE | AFTER & Specifies when the trigger should be fired, before or after the event. \tn 
% Row Count 15 (+ 3)
% Row 4
\SetRowColor{LightBackground}
INSERT | UPDATE | DELETE & Specifies the event that triggers the action. \tn 
% Row Count 17 (+ 2)
% Row 5
\SetRowColor{white}
table\_name & Name of the table on which the trigger operates. \tn 
% Row Count 19 (+ 2)
% Row 6
\SetRowColor{LightBackground}
FOR EACH ROW & Indicates that the trigger will be fired for each row affected by the triggering event. \tn 
% Row Count 23 (+ 4)
% Row 7
\SetRowColor{white}
trigger\_body & Actions to be performed when the trigger is fired. \tn 
% Row Count 26 (+ 3)
% Row 8
\SetRowColor{LightBackground}
Example & CREATE TRIGGER audit\_trigger AFTER INSERT ON employees FOR EACH ROW BEGIN     INSERT INTO audit\_log (event\_type, event\_time, user\_id)     VALUES ('INSERT', NOW(), NEW.id); END; \tn 
% Row Count 34 (+ 8)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{3.04 cm} x{4.96 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Trigger (cont)}}  \tn
% Row 9
\SetRowColor{LightBackground}
BEFORE Triggers & Fired before the triggering action occurs. Can be used to modify data before it is inserted, updated, or deleted. \tn 
% Row Count 5 (+ 5)
% Row 10
\SetRowColor{white}
AFTER Triggers & Fired after the triggering action occurs. Can be used for logging, auditing, or other post-action tasks. \tn 
% Row Count 10 (+ 5)
% Row 11
\SetRowColor{LightBackground}
Accessing Data & Use NEW.column\_name to access the new value of a column in an INSERT or UPDATE trigger. Use OLD.column\_name to access the old value of a column in an UPDATE or DELETE trigger. \tn 
% Row Count 18 (+ 8)
% Row 12
\SetRowColor{white}
Dropping a Trigger & DROP TRIGGER {[}IF EXISTS{]} trigger\_name; \tn 
% Row Count 20 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{x{3.92 cm} x{4.08 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Performance Optimization}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{2}{x{8.4cm}}{Indexing:} \tn 
% Row Count 1 (+ 1)
% Row 1
\SetRowColor{white}
Use Indexes & Indexes help in speeding up the data retrieval process by creating efficient lookup paths. \tn 
% Row Count 6 (+ 5)
% Row 2
\SetRowColor{LightBackground}
Choose the Right Columns & Identify columns frequently used in WHERE, JOIN, and ORDER BY clauses for indexing. \tn 
% Row Count 11 (+ 5)
% Row 3
\SetRowColor{white}
Avoid Overindexing & Unnecessary indexes can slow down write operations and consume disk space. \tn 
% Row Count 15 (+ 4)
% Row 4
\SetRowColor{LightBackground}
Regularly Analyze and Optimize Indexes & Monitor index usage and performance regularly. Use tools like EXPLAIN to analyze query execution plans. \tn 
% Row Count 21 (+ 6)
% Row 5
\SetRowColor{white}
\mymulticolumn{2}{x{8.4cm}}{Query Optimization:} \tn 
% Row Count 22 (+ 1)
% Row 6
\SetRowColor{LightBackground}
Optimize Queries & Write efficient queries by avoiding unnecessary joins, using appropriate WHERE clauses, and minimizing data retrieval. \tn 
% Row Count 28 (+ 6)
% Row 7
\SetRowColor{white}
Use LIMIT & When fetching a large dataset, limit the number of rows returned to reduce the workload on the server. \tn 
% Row Count 34 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{x{3.92 cm} x{4.08 cm} }
\SetRowColor{DarkBackground}
\mymulticolumn{2}{x{8.4cm}}{\bf\textcolor{white}{Performance Optimization (cont)}}  \tn
% Row 8
\SetRowColor{LightBackground}
Avoid SELECT & Explicitly specify only the required columns in SELECT statements to reduce data transfer overhead. \tn 
% Row Count 5 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}--}
\end{tabularx}
\par\addvspace{1.3em}


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

\end{document}