Glossary of Key Terms for this Course
KeyTerm Definition
Aggregate functions that are used in
Aggregates window functions, too (e.g., sum,
count, avg).
Shorthand that can be used if there are
Aliases
several window functions in one query.
Converts a value of any type into a
Cast
specific, different data type
Coalesce Returns the first non-null value in a list
Adds two or more expressions
Concat
together
The inner subquery is dependent on
Correlated Subquery
the larger query.
is a statement that creates a new table
CREATE TABLE
in a database.
Common Table Expression in SQL
allows you to define a temporary
CTE
result, such as a table, to then be
referenced in a later part of the query.
Ranking function similar to rank() but
Dense_rank()
ranks are not skipped with ties.
Ranking is the same amongst tied
Dense_rank() values and ranks do not skip for
subsequent values.
Always used in SELECT statements,
and it provides the unique rows for all
DISTINCT
columns written in the SELECT
statement.
is a statement that removes a table in
DROP TABLE
a database.
A common way to view data in a
Entity-relationship diagram (ERD)
database.
is a column in one table that is a
Foreign Key (FK)
primary key in a different table
specifies from which table(s) you want
FROM to select the columns. Notice the
columns need to exist in this table.
Include unmatched rows from all tables
Full Outer Join
being joined.
Used to aggregate data within subsets
of the data. For example, grouping for
GROUP BY
different accounts, different regions, or
different sales representatives.
is the “clean” way to filter a query that
HAVING
has been aggregated
This subquery is used in the same
fashion as the WITH use case above.
Inline However, instead of the temporary
table sitting on top of the larger query,
it’s embedded within the from clause.
is an INNER JOIN that only pulls data
JOIN
that exists in both tables.
Joins Dependencies Cannot stand independently.
A joint view of multiple tables stitched
Joins Output
together using a common “key”.
Fully stitch tables together and have
Joins Use Case full flexibility on what to “select” and
“filter from”.
Calculating differences between rows’
Lag/Lead
values.
Extracts a number of characters from a
Left
string starting from the left
is a JOIN that pulls all the data that
exists in both tables, as well as all of
LEFT JOIN the rows from the table in the FROM
even if they do not exist in the JOIN
statement.
This subquery is used when you’d like
the temporary table to act as a filter
Nested within the larger query, which implies
that it often sits within the where
clause.
A datatype that specifies where no
NULLs
data exists in SQL
Typically precedes the partition by that
Over
signals what to “GROUP BY”.
A subclause of the OVER clause.
Partition by
Similar to GROUP BY.
Defines what percentile a value falls
Percentiles
into over the entire table.
Improving queries to perform better
Performance Tuning
and faster
Returns the position of the first
Position
occurrence of a substring in a string
Primary Key (PK) is a unique column in a particular table
Ranking function where a row could
Rank() get the same rank if they have the
same value.
Ranking is the same amongst tied
Rank() values and ranks skip for subsequent
values.
Extracts a number of characters from a
Right
string starting from the right
is a JOIN pulls all the data that exists
in both tables, as well as all of the rows
RIGHT JOIN
from the table in the JOIN even if they
do not exist in the FROM statement.
Ranking function where each row gets
Row_number()
a different number.
Ranking is distinct amongst records
Row_number() even with ties in what the table is
ranked against.
This subquery is used when you’d like
Scalar to generate a scalar value to be used
as a benchmark of some sort.
allows you to read data and display it.
This is called a query and it specifies
SELECT
from which table(s) you want to select
the columns.
Self Join Joining a table with itself.
The inner subquery is completely
Simple Subquery
independent of the larger query.
Virtual tables that are derived from one
or more base tables. The term virtual
means that the views do not exist
SQL Views physically in a database, instead, they
reside in the memory (not database),
just like the result of any query is
stored in the memory.
Returns the position of a substring
Strpos
within a string
A SQL query where one SQL query is
Subquery
nested within another query
Stand independently and be run as
Subquery Dependencies
complete queries themselves.
Either a scalar (a single value) or rows
Subquery Output
that have met a condition.
Calculate a scalar value to use in a
Subquery Use Case later part of the query (e.g., average
price as a filter).
Extracts a substring from a string
Substr
(starting at any position)
Combine the result sets of 2 or more
SELECT statements. It removes
Union
duplicate rows between the various
SELECT statements.
This subquery is used when you’d like
to “pseudo-create” a table from an
With existing table and visually scope the
temporary table at the top of the larger
query.
