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
