SQL

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/35

flashcard set

Earn XP

Description and Tags

Comprehensive vocabulary flashcards covering fundamental and advanced MySQL concepts, clauses, functions, subqueries, joins, CTEs, and data cleaning.

36 Terms

1
New cards

SELECT Statement

A statement used to work with columns and specify which columns you want to see in your output.

2
New cards

PEMDAS Rule in SQL

The mathematical order of operations followed by SQL functions: Parentheses, Exponents, Multiplication, Division, Addition, Subtraction.

3
New cards

WHERE Clause

A clause used to filter records or rows of data, returning only the rows that fulfil a specific condition.

4
New cards

LIKE Statement

A statement used in a WHERE clause to search for a specified pattern in a column.

5
New cards

% (Percent Sign Wildcard)

A wildcard symbol used in a LIKE statement that represents anything and can appear at the beginning, end, or both.

6
New cards

_ (Underscore Wildcard)

A wildcard symbol used in a LIKE statement that represents a single specific value.

7
New cards

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.

8
New cards

ORDER BY

A clause that sorts query results in ascending or descending order.

9
New cards

HAVING Clause

A clause that filters rows after aggregation, applying only to groups as a whole rather than individual rows.

10
New cards

LIMIT

A clause specifying how many rows to return in the output, which can accept an additional offset parameter separated by a comma.

11
New cards

Aliasing

A method to change the display name of a column or table within a query using the AS keyword.

12
New cards

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.

13
New cards

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.

14
New cards

Self Join

A join where a table is tied to itself.

15
New cards

UNION

An operation that combines the rows from multiple SELECT statements, removing duplicates by default (UNION DISTINCT).

16
New cards

UNION ALL

An operation that combines the rows from multiple SELECT statements without removing duplicate rows.

17
New cards

LENGTH()

A string function that returns the length of a string in number of characters.

18
New cards

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.

19
New cards

SUBSTRING()

A string function that allows extraction of specific characters from anywhere in a string.

20
New cards

REPLACE()

A string function that replaces specific characters in a string with a different designated character.

21
New cards

LOCATE()

A string function that returns the position where specified character(s) are found within a string.

22
New cards

CONCAT()

A string function that combines multiple columns into a single column.

23
New cards

CASE Statement

A SQL statement structure that allows conditional if-else logic to be added to queries.

24
New cards

Subquery

A query written inside parentheses within another query, which can be placed in WHERE, SELECT, or FROM clauses.

25
New cards

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.

26
New cards

ROW_NUMBER()

A window function that numbers each row sequentially without accounting for duplicates.

27
New cards

RANK()

A window function that assigns duplicate rows the same rank number, leaving gaps for positional numbers.

28
New cards

DENSE_RANK()

A window function that gives duplicate rows the same rank number and assigns the next immediate numerical number without skipping positions.

29
New cards

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.

30
New cards

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.

31
New cards

Stored Procedure

A saved block of SQL code that can be reused and executed repeatedly by calling its name.

32
New cards

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.

33
New cards

Trigger

A block of code that executes automatically when a specific event (such as an INSERT) takes place on a table.

34
New cards

Event

A block of SQL code that executes automatically when it is scheduled to run, useful for automated tasks.

35
New cards

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.

36
New cards

Staging Table

A duplicate table created to clean and manipulate data without altering or risking the original raw dataset.