Complex Queries Flashcards
IN
Operator is used in a WHERE clause to determine if a value matches one of several values
BETWEEN
Operator provides an alternative way to determine if a value is between two other values.
Written as value BETWEEN minValue AND maxValue and is equivalent to valu
LIKE
Operator when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and __.
BINARY
Like operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword
DISTINCT
used with a SELECT statement to return only unique or ‘distinct’ values
ORDER BY
Clause orders selected rows by one or more columns in ascending (alphabetic or increasing order)
DESC
When used with ORDER BY orders rows in descending order.
Function/Argument
Operates on an expression enclosed in parentheses, called an argument, and returns a value.
Aggregate function
processes values from a set of rows and returns a summary value
COUNT()
Counts the numbers of rows in the set
MIN()
Finds the minimum value in the set
MAX()
Finds the maximum value in the set
SUM()
sums all the values in the set
AVG
Computes the arithmetic mean of all the values in the set
GROUP BY
Each simple or composite value of the columns becomes a group. Query computes the aggregate function separately, and reutns one row, for each group
HAVING
used with the GROUP BY clause to filter group results
JOIN
JOIN is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result
AS
Simplify queries or result tables, a column name can be replaced with an Alias. The alias follows the column name, separated by an optional AS keyword.
Join clause
Determines how a join query handles unmatched rows
INNER JOIN
selects only matching left and right table rows
FULL JOIN
selects all left and right table rows, regardless of match
On
Specifies the join columns.
LEFT JOIN
Selects all left table rows, but only matching right table rows
RIGHT JOIN
Selects all right table rows, but only matching left table rows
OUTER JOIN
Any join that selects unmatched rows, including left, right and full joins
UNION
Combines the two results into one table
Equijoin
Compares columns of two tables with the = Operator
non-equijoin
Compares the colums with an operator other than =, such as < and >
Self-Join
Joins a table to itself
Cross-Join
Combines two tables without comparing columns
CROSS JOIN
The actual cluase for a cross join command
Subquery/Nested Query/Inner Query
Query within another SQL query
Correlated
When the subquery’s WHERE clause references a column from the outer query
Alias
Temporary name assigned to a column or table
AS
Keyword follows a column or table name to create an alias
EXISTS
Correlated subqueries commonly use the EXISTS operator, which returns TRUE if a subquery selects at least one row and FALSE If no rows are selected.
NOT EXISTS
True if a subquery selects no rows, and FALSE if at least one row is selected
Flattening
Replacing a subquery with an equivalent join is called flattening a query
View Table/View Query
table name associated with a SELECT statement, called the view query
CREATE VIEW
creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table
base table
Specified in the views query’s FROM clause is called a base table.
materialized view
View for which data is stored at all times
WITH CHECK OPTION
Database rejects inserts and updates that do not satisfy the view query WHERE Clause
Select Operation
Operation that selects table rows based on a logical expression
Project Operation
Operation selects table columns
Product operation
Combines two tables into one result. The result includes all columns and all combination of rows from both tables
join
denoted with a bowtie symbol is identical to a product followed by a select
Theta Join
Because of the theta notation, the join operation is sometimes called a theta join
Compatible tables
Same number of columns with the same data types. Column names may be different
Set Operations
Union, intersect, and difference operate on compatible tables, and collectively, are called set operations
Union
operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table
Intersect
Operates on two compatible tables and returns only rows that appear in both tables
Difference
Operation removes from a table all rows that appear in a second compatible table
Rename Operation
specifies a new table and column names
Aggregate operation
Applies aggregate functions like SUM(),AVG(),MIN, and MAX().
Equivalent
Relational algebra expression are equivalent if the expression operate on the same tables and generate the same result
query Optimizer/QUery execution Plan
converts an SQL query into a sequence of low-level database actions, called the query execution plan. The query execution plan specifies precisely how to process an SQL statement
Cost
operation is a numeric estimate of processing time. The cost estimate usually combines both storage media access and computation time in a single measure
Operator is used in a WHERE clause to determine if a value matches one of several values
IN
Operator provides an alternative way to determine if a value is between two other values.
Written as value BETWEEN minValue AND maxValue and is equivalent to valu
BETWEEN
Operator when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and __.
LIKE
Like operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword
BINARY
used with a SELECT statement to return only unique or ‘distinct’ values
DISTINCT
Clause orders selected rows by one or more columns in ascending (alphabetic or increasing order)
ORDER BY
When used with ORDER BY orders rows in descending order.
DESC
Operates on an expression enclosed in parentheses, called an argument, and returns a value.
Function/Argument
processes values from a set of rows and returns a summary value
Aggregate function
Counts the numbers of rows in the set
COUNT()
Finds the minimum value in the set
MIN()
Finds the maximum value in the set
MAX()
sums all the values in the set
SUM()
Computes the arithmetic mean of all the values in the set
AVG
Each simple or composite value of the columns becomes a group. Query computes the aggregate function separately, and reutns one row, for each group
GROUP BY
used with the GROUP BY clause to filter group results
HAVING
JOIN is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result
JOIN
Simplify queries or result tables, a column name can be replaced with an Alias. The alias follows the column name, separated by an optional AS keyword.
AS
Determines how a join query handles unmatched rows
Join clause
selects only matching left and right table rows
INNER JOIN
selects all left and right table rows, regardless of match
FULL JOIN
Specifies the join columns.
On
Selects all left table rows, but only matching right table rows
LEFT JOIN
Selects all right table rows, but only matching left table rows
RIGHT JOIN
Any join that selects unmatched rows, including left, right and full joins
OUTER JOIN
Combines the two results into one table
UNION
Compares columns of two tables with the = Operator
Equijoin
Compares the colums with an operator other than =, such as < and >
non-equijoin
Joins a table to itself
Self-Join
Combines two tables without comparing columns
Cross-Join
The actual cluase for a cross join command
CROSS JOIN
Query within another SQL query
Subquery/Nested Query/Inner Query
When the subquery’s WHERE clause references a column from the outer query
Correlated
Temporary name assigned to a column or table
Alias
Keyword follows a column or table name to create an alias
AS
Correlated subqueries commonly use the EXISTS operator, which returns TRUE if a subquery selects at least one row and FALSE If no rows are selected.
EXISTS
True if a subquery selects no rows, and FALSE if at least one row is selected
NOT EXISTS
Replacing a subquery with an equivalent join is called flattening a query
Flattening
table name associated with a SELECT statement, called the view query
View Table/View Query
creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table
CREATE VIEW
Specified in the views query’s FROM clause is called a base table.
base table
View for which data is stored at all times
materialized view
Database rejects inserts and updates that do not satisfy the view query WHERE Clause
WITH CHECK OPTION
Operation that selects table rows based on a logical expression
Select Operation
Operation selects table columns
Project Operation
Combines two tables into one result. The result includes all columns and all combination of rows from both tables
Product operation
denoted with a bowtie symbol is identical to a product followed by a select
join
Because of the theta notation, the join operation is sometimes called a theta join
Theta Join
Same number of columns with the same data types. Column names may be different
Compatible tables
Union, intersect, and difference operate on compatible tables, and collectively, are called set operations
Set Operations
operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table
Union
Operates on two compatible tables and returns only rows that appear in both tables
Intersect
Operation removes from a table all rows that appear in a second compatible table
Difference
specifies a new table and column names
Rename Operation
Applies aggregate functions like SUM(),AVG(),MIN, and MAX().
Aggregate operation
Relational algebra expression are equivalent if the expression operate on the same tables and generate the same result
Equivalent
converts an SQL query into a sequence of low-level database actions, called the query execution plan. The query execution plan specifies precisely how to process an SQL statement
query Optimizer/QUery execution Plan
operation is a numeric estimate of processing time. The cost estimate usually combines both storage media access and computation time in a single measure
Cost
