1/35
Comprehensive vocabulary flashcards covering fundamental and advanced MySQL concepts, clauses, functions, subqueries, joins, CTEs, and data cleaning.
SELECT Statement
A statement used to work with columns and specify which columns you want to see in your output.
PEMDAS Rule in SQL
The mathematical order of operations followed by SQL functions: Parentheses, Exponents, Multiplication, Division, Addition, Subtraction.
WHERE Clause
A clause used to filter records or rows of data, returning only the rows that fulfil a specific condition.
LIKE Statement
A statement used in a WHERE clause to search for a specified pattern in a column.
% (Percent Sign Wildcard)
A wildcard symbol used in a LIKE statement that represents anything and can appear at the beginning, end, or both.
_ (Underscore Wildcard)
A wildcard symbol used in a LIKE statement that represents a single specific value.
GROUP BY
A clause that groups together rows that have the same values in specified columns, allowing aggregate functions to be executed on those rows.
ORDER BY
A clause that sorts query results in ascending or descending order.
HAVING Clause
A clause that filters rows after aggregation, applying only to groups as a whole rather than individual rows.
LIMIT
A clause specifying how many rows to return in the output, which can accept an additional offset parameter separated by a comma.
Aliasing
A method to change the display name of a column or table within a query using the AS keyword.
Inner Join
A join that returns rows that are the same in both columns in both tables, functioning as the default join type in MySQL.
Left Outer Join
A join that takes everything from the left table, even if there is no match, and returns only the matching rows from the right table.
Self Join
A join where a table is tied to itself.
UNION
An operation that combines the rows from multiple SELECT statements, removing duplicates by default (UNION DISTINCT).
UNION ALL
An operation that combines the rows from multiple SELECT statements without removing duplicate rows.
LENGTH()
A string function that returns the length of a string in number of characters.
TRIM
A string function that removes whitespace anywhere in a string, with LTRIM removing spaces on the left and RTRIM removing spaces on the right.
SUBSTRING()
A string function that allows extraction of specific characters from anywhere in a string.
REPLACE()
A string function that replaces specific characters in a string with a different designated character.
LOCATE()
A string function that returns the position where specified character(s) are found within a string.
CONCAT()
A string function that combines multiple columns into a single column.
CASE Statement
A SQL statement structure that allows conditional if-else logic to be added to queries.
Subquery
A query written inside parentheses within another query, which can be placed in WHERE, SELECT, or FROM clauses.
Window Functions
Functions that look at a partition or group while retaining each row's unique identity in the output, typically using an OVER clause.
ROW_NUMBER()
A window function that numbers each row sequentially without accounting for duplicates.
RANK()
A window function that assigns duplicate rows the same rank number, leaving gaps for positional numbers.
DENSE_RANK()
A window function that gives duplicate rows the same rank number and assigns the next immediate numerical number without skipping positions.
Common Table Expression (CTE)
A named subquery block defined using WITH and AS that can only be referenced immediately after creation and is not updatable.
Temporary Table
A table visible only to the session in which it was created, used to store intermediate results for complex queries and manipulate data before insertion into permanent tables.
Stored Procedure
A saved block of SQL code that can be reused and executed repeatedly by calling its name.
Parameter (Stored Procedure)
A variable passed as an input into a stored procedure that allows the procedure to accept an external value and incorporate it into the query code.
Trigger
A block of code that executes automatically when a specific event (such as an INSERT) takes place on a table.
Event
A block of SQL code that executes automatically when it is scheduled to run, useful for automated tasks.
Data Cleaning
The process of getting data into a more usable format by fixing issues with raw data, such as removing duplicates, standardizing data, handling nulls, and removing unwanted columns.
Staging Table
A duplicate table created to clean and manipulate data without altering or risking the original raw dataset.