Complex Queries Flashcards
(116 cards)
1
Q
IN
A
Operator is used in a WHERE clause to determine if a value matches one of several values
2
Q
BETWEEN
A
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
3
Q
LIKE
A
Operator when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and __.
4
Q
BINARY
A
Like operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword
5
Q
DISTINCT
A
used with a SELECT statement to return only unique or ‘distinct’ values
6
Q
ORDER BY
A
Clause orders selected rows by one or more columns in ascending (alphabetic or increasing order)
7
Q
DESC
A
When used with ORDER BY orders rows in descending order.
8
Q
Function/Argument
A
Operates on an expression enclosed in parentheses, called an argument, and returns a value.
9
Q
Aggregate function
A
processes values from a set of rows and returns a summary value
10
Q
COUNT()
A
Counts the numbers of rows in the set
11
Q
MIN()
A
Finds the minimum value in the set
12
Q
MAX()
A
Finds the maximum value in the set
13
Q
SUM()
A
sums all the values in the set
14
Q
AVG
A
Computes the arithmetic mean of all the values in the set
15
Q
GROUP BY
A
Each simple or composite value of the columns becomes a group. Query computes the aggregate function separately, and reutns one row, for each group
16
Q
HAVING
A
used with the GROUP BY clause to filter group results
17
Q
JOIN
A
JOIN is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result
18
Q
AS
A
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.
19
Q
Join clause
A
Determines how a join query handles unmatched rows
20
Q
INNER JOIN
A
selects only matching left and right table rows
21
Q
FULL JOIN
A
selects all left and right table rows, regardless of match
22
Q
On
A
Specifies the join columns.
23
Q
LEFT JOIN
A
Selects all left table rows, but only matching right table rows
24
Q
RIGHT JOIN
A
Selects all right table rows, but only matching left table rows
25
OUTER JOIN
Any join that selects unmatched rows, including left, right and full joins
26
UNION
Combines the two results into one table
27
Equijoin
Compares columns of two tables with the = Operator
28
non-equijoin
Compares the colums with an operator other than =, such as < and >
29
Self-Join
Joins a table to itself
30
Cross-Join
Combines two tables without comparing columns
31
CROSS JOIN
The actual cluase for a cross join command
32
Subquery/Nested Query/Inner Query
Query within another SQL query
33
Correlated
When the subquery's WHERE clause references a column from the outer query
34
Alias
Temporary name assigned to a column or table
35
AS
Keyword follows a column or table name to create an alias
36
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.
37
NOT EXISTS
True if a subquery selects no rows, and FALSE if at least one row is selected
38
Flattening
Replacing a subquery with an equivalent join is called flattening a query
39
View Table/View Query
table name associated with a SELECT statement, called the view query
40
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
41
base table
Specified in the views query's FROM clause is called a base table.
42
materialized view
View for which data is stored at all times
43
WITH CHECK OPTION
Database rejects inserts and updates that do not satisfy the view query WHERE Clause
44
Select Operation
Operation that selects table rows based on a logical expression
45
Project Operation
Operation selects table columns
46
Product operation
Combines two tables into one result. The result includes all columns and all combination of rows from both tables
47
join
denoted with a bowtie symbol is identical to a product followed by a select
48
Theta Join
Because of the theta notation, the join operation is sometimes called a theta join
49
Compatible tables
Same number of columns with the same data types. Column names may be different
50
Set Operations
Union, intersect, and difference operate on compatible tables, and collectively, are called set operations
51
Union
operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table
52
Intersect
Operates on two compatible tables and returns only rows that appear in both tables
53
Difference
Operation removes from a table all rows that appear in a second compatible table
54
Rename Operation
specifies a new table and column names
55
Aggregate operation
Applies aggregate functions like SUM(),AVG(),MIN, and MAX().
56
Equivalent
Relational algebra expression are equivalent if the expression operate on the same tables and generate the same result
57
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
58
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
59
Operator is used in a WHERE clause to determine if a value matches one of several values
IN
60
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
61
Operator when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and __.
LIKE
62
Like operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword
BINARY
63
used with a SELECT statement to return only unique or 'distinct' values
DISTINCT
64
Clause orders selected rows by one or more columns in ascending (alphabetic or increasing order)
ORDER BY
65
When used with ORDER BY orders rows in descending order.
DESC
66
Operates on an expression enclosed in parentheses, called an argument, and returns a value.
Function/Argument
67
processes values from a set of rows and returns a summary value
Aggregate function
68
Counts the numbers of rows in the set
COUNT()
69
Finds the minimum value in the set
MIN()
70
Finds the maximum value in the set
MAX()
71
sums all the values in the set
SUM()
72
Computes the arithmetic mean of all the values in the set
AVG
73
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
74
used with the GROUP BY clause to filter group results
HAVING
75
JOIN is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result
JOIN
76
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
77
Determines how a join query handles unmatched rows
Join clause
78
selects only matching left and right table rows
INNER JOIN
79
selects all left and right table rows, regardless of match
FULL JOIN
80
Specifies the join columns.
On
81
Selects all left table rows, but only matching right table rows
LEFT JOIN
82
Selects all right table rows, but only matching left table rows
RIGHT JOIN
83
Any join that selects unmatched rows, including left, right and full joins
OUTER JOIN
84
Combines the two results into one table
UNION
85
Compares columns of two tables with the = Operator
Equijoin
86
Compares the colums with an operator other than =, such as < and >
non-equijoin
87
Joins a table to itself
Self-Join
88
Combines two tables without comparing columns
Cross-Join
89
The actual cluase for a cross join command
CROSS JOIN
90
Query within another SQL query
Subquery/Nested Query/Inner Query
91
When the subquery's WHERE clause references a column from the outer query
Correlated
92
Temporary name assigned to a column or table
Alias
93
Keyword follows a column or table name to create an alias
AS
94
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
95
True if a subquery selects no rows, and FALSE if at least one row is selected
NOT EXISTS
96
Replacing a subquery with an equivalent join is called flattening a query
Flattening
97
table name associated with a SELECT statement, called the view query
View Table/View Query
98
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
99
Specified in the views query's FROM clause is called a base table.
base table
100
View for which data is stored at all times
materialized view
101
Database rejects inserts and updates that do not satisfy the view query WHERE Clause
WITH CHECK OPTION
102
Operation that selects table rows based on a logical expression
Select Operation
103
Operation selects table columns
Project Operation
104
Combines two tables into one result. The result includes all columns and all combination of rows from both tables
Product operation
105
denoted with a bowtie symbol is identical to a product followed by a select
join
106
Because of the theta notation, the join operation is sometimes called a theta join
Theta Join
107
Same number of columns with the same data types. Column names may be different
Compatible tables
108
Union, intersect, and difference operate on compatible tables, and collectively, are called set operations
Set Operations
109
operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table
Union
110
Operates on two compatible tables and returns only rows that appear in both tables
Intersect
111
Operation removes from a table all rows that appear in a second compatible table
Difference
112
specifies a new table and column names
Rename Operation
113
Applies aggregate functions like SUM(),AVG(),MIN, and MAX().
Aggregate operation
114
Relational algebra expression are equivalent if the expression operate on the same tables and generate the same result
Equivalent
115
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
116
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
