Complex Queries Flashcards
1
Q
The blank operator is used in a WHERE. Pause to determine if a value matches one of several values
A
IN
2
Q
Like the IN operator, you can use blank in a WHERE clause to determine if a value doesn’t matches one of several values
A
NOT IN
3
Q
The blank provides an alternative to determine if a value is between two other values.
A
BETWEEN operator
4
Q
The BETWEEN operator is written how and is equivalent to value >= minValue AND value <=MaxValue
A
BETWEEN minValue AND maxValue
5
Q
The BETWEEN operator can or cannot be used with string literals
A
Can
6
Q
Which runs faster BETWEEN or other <=>= SQL statements
A
Neither. Same speed
7
Q
The blank when used in a WHERE clause matches text against a pattern using wildcard characters blank and blank
A
LIKE operator
%
_
8
Q
Blank matches any number of characters when used with LIKE
A
%
9
Q
Blank matches only one character when used with LIKE
A
_
10
Q
The LIKe operator performs case-blank patterns matching by default or case-blank pattern matching if followed by the blank keyword
A
Insensitive
Sensitive
BINARY
11
Q
To search for wildcard characters % or _ a blank must precede them
A
/
12
Q
Most relational databases provide other mechanisms to perform advanced pattern matching with blank
A
Regular expressions
13
Q
The blank is used in a SELeCT statement to return any unique or distinct values
A
DISTINCT clause
14
Q
The DISTINCT clause blank from results
A
Removes repeated values
15
Q
Give the syntax for using a distinct clause
A
SELECT DISTINCT ColumnName
FROM TableName
16
Q
A blank selects rows from a table with no guarantee the data will come back in a certain order.
A
SELECT
17
Q
The blank clause orders selected rows by one or more columns in ascending order.
A
ORDER BY
18
Q
The blank keyword with the ORDER BY clause orders rows in descending order
A
DESC
19
Q
A blank operates on an expression in parentheses, called a blank, and returns a value.
A
Function
Argument
20
Q
Each function operates on, and evaluates to, blank
A
Specific data types
21
Q
If the argument is invalid, it returns blank
A
NULL
22
Q
Blank returns the absolute value
A
ABS(n)
23
Q
Blank returns the natural logarithm of n
A
LOG(n)
24
Q
Blank returns x to the power of y
A
POW(x,y)
25
Blank returns a random number between 0(inclusive) and 1(exclusive)
RAND()
26
Blank returns n rounded to d decimal places
ROUND(n,d)
27
Blank returns the square root of n
SQRT(n)
28
Blank manipulate string values
String functions
29
Blank returns the combination of strings
CONCAT(s1,s2)
30
Blank returns lowercase s
LOWER(s)
31
Blank returns s with from changed to to
REPLACE(s,from,to)
32
Blank returns the substring from s starts at pos and has length len
SUBSTRING (s,pos,len)
33
Blank returns the string s without leading or trailing spaces
TRIM(s)
34
Blank returns the uppercase a
UPPER(s)
35
Blank functions operate in DATE, TIME, and DATETIME data types
Date and time
36
Blank returns the current date and time
CURDATE()
CURTIME()
NOW()
37
Blank extracts the date or time
DATE(exp)
TIME(exp)
38
Blank returns the day month or year
DAY(d)
MONTH(d)
YEAR(d)
39
Blank returns hour or minute or second
HOUR(t)
MINUTE(t)
SECOND(t)
40
Blank returns the difference between dates or times
DATEDIFF(expr1,expr2)
TIMEDIFF(expr1,expr2)
41
Some database systems, such as Microsoft SQL Server support a blank. These systems also support table-valued functions that return an entire table rather than an individual value.
Table data type
42
Blank processes values form a set of rows and returns a summary value.
Aggregate functions
43
Blank counts the number of rows in the set.
COUNT()
44
Blank finds the minimum value in the set
MIN()
45
Blank finds the maximum value in the set
MAX()
46
Blank sums all values in the set
SUM()
47
Blank computes the arithmetic mean of all the values in the set
AVG()
48
Aggregate functions appear in a blank clause and process all rows that satisfy the WHERE clause condition
SELECT
49
If a SELECt statement had no WHERE clause, the aggregate function processes blank
All rows
50
Aggregate functions are commonly used with the blank clause
GROUP BY clause
51
The GROUP BY clause consists of the GROUP BY keyword and blank
One or more columns
52
Each simple or composite value of the columns become a what ? The query computes the function separately and returns one row for each what?
Group
53
The GROUP BY clause appears between the blank clause, and if any, the blank clause.
WHERE
ORDER BY
54
Aside from the aggregate function, the blank may contain only columns that appear in the GROUP BY clause.
SELECT clause
55
The blank is used with the GROUP BY clause to filter group results
HAVING clause
56
The optional HAVING follows the blank and precedes the optional blank clause
GROUP BY
ORDER BY
57
Give the syntax for a GROUP BY clause
GROUP BY ColumnName
58
Give the syntax for a HAVING clause
GROUP BY ColumnName
HAVING FunctionResultExpression
59
Aggregate functions ignore blank
Null values
60
Arithmetic functions return blank when either operand is null
Null
61
In relational databases, reports are often generated from data in multiple tables. Multi-table reports are written with what?
Join statements
62
A blank is a selected statement that combines data from two tables
JOIN
63
The JOIN gets data from blank and blank to combine into a single result
Left table and right table
64
In a JOIN, the tables are combined by comparing columns from the left and right tables, usually with the blank
= operator
65
Usually, a join compares a blank of one table to the blank of another table.
Foreign Key
Primary Key
66
The columns in a join must have a comparable blank
Data type
67
When duplicate column names appear in a query, the names must be distinguished with a blank
Prefix
68
The prefix in a query where column names are the same is what
The table name followed by a period
69
Use of a prefix makes column names more complex, to simplify queries or result tables, a column name can be replaced with an blank
Alias
70
In order to create the alias, the alias follows the column name, separated by an optional blank.
AS keyword
71
Give the syntax for using the AS keyword
SELECT ColumnName AS Alias…
72
A blank determines how a join query handles unmatched rows
JOIN clause
73
A blank selects only matching left and right table rows
INNER JOIN
74
A blank selects all left and right table rows regardless of match
FULL JOIN
75
In a blank result table, unmatched left table rows appear with NULL values in the right table columns and vice versa
Full join
76
The join clause appears between a blank and a blank
FROM clause
ON clause
77
In a join query, the blank specifies the left table, and the blank specifies the right table
FROM
INNER JOIN or FULL JOIN
78
The blank specifies the join columns
ON clause
79
Give the syntax for a join query
SELECT ColumnName1, ColumnName2
FROM TableName
INNER JOIN or FULL JOIN TableName2
ON Expression;
80
In some cases, the database user wants to see unmatched rows from the left or right table, but not both. To do this, relational databases support blank and blank
LEFT and RIGHT JOINS
81
A blank selects all the left table rows, but only the matching right table rows
LEFT JOIN
82
A blank selects all the right table rows, but only the matching left table rows
RIGHT JOIN
83
MySQL supports INNER, LEFT, and RIGHT JOINS but not blank
FULL JOINS
84
Give the syntax for a left or right John
SELECT ColumnName1, ColumnName2
FROM TableName1
LEFT JOIN or RIGHT JOIN TableName2
ON Expression;
85
INNER and OUTER JOIN queries can be written without a JOIN clause. However a blank clarifies join behavior and simplifies queries.
JOIN clause
86
Blank keyword combines the results into one table
UNION keyword
87
Blank compares columns of two tables with the = operator.
Equijoin
88
A blank compares columns with an operator other than =, like < and >
Non-equijoin
89
Blank joins a table to itself
Self-join
90
A self-join can compare any columns of a table, as long as the columns have comparable blank
Data types
91
If a blank and a blank are in the same table, a self-join commonly compares these key columns
Foreign key and the referenced primary key
92
In a self-join, lank are necessary to distinguish left and right tables
Aliases
93
Blank combines the tables without comparing columns
CROSS JOIN
94
A cross join used a CROSS JOIN clause without an blank.
ON clause
95
In a CROSS JOIN, all possible blank from both tables appear in the result
Rows
96
Give the syntax for a cross join
SELECT ColumnName1 + ColumnName2
FROM TableName
CROSS JOIN TableName2;
97
A blank, sometimes called a nested query or inner query, is a query within another query.
Subquery
98
The subquery is typically used in a blank’s WHERE clause to return data to the outer query and restrict selected results
SELECT statement
99
The subquery is placed within blank
Parentheses ()
100
A subquery is blank when the subquery’s WHERE clause references a column from the outer query.
Correlated
101
In a correlated subquery, the blank selected depends on what row is currently being examined by the outer row
Rows
102
If the column name in the correlated query is identical to a column name in the outer query, the blank differentiates the columns
TableName.ColumnName
103
A blank can also be used in a correlated subquery to differentiate the column or table using the AS keyword
Alias
104
Correlated sub queries commonly use the blank operator, which returns a TRUEif a subquery selects at least one row and FALSE if no rows are selected
EXISTS
105
The blank operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected
NOT EXISTS
106
Give the syntax for using EXISTS or NOT EXiSTS
SELECT ColumnName1, ColumnName2
FROM TableName
WHERE EXISTS or NOT EXiSTS
(Subquery);
107
Many sub queries can be written as a blank
JOIN
108
Most databases optimize a subquery and outer query separately, whereas blanks are optimized in one pass
JOINS
109
Replacing a subquery with an equivalent JOIN is called blank
Flattening a query
110
Most sub queries that follow blank or blank, or return a blank can be flattened
IN
EXISTS
A single value
111
Most sub queries that follow blank or contain a blank clause cannot be flattened
NOT EXISTS
GROUP BY
112
Name the five steps for flattening a query
1) Retain the outer query SELECT, FROM, GROUP BY, HAVING, and ORDER BY clauses
2) Add INNER JOIN clauses for each subquery table
3) move conparisons between sub query and outer query columns to ON clauses
4) Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses
5) if necessary, remove duplicates with SELECT DISTINCT
113
To create a complex query, name the strategy a user can use (5 steps)
1) Examine a table diagram or other database summary to understand tables and relationships
2) Identify the tables containing the necessary data to answer the question.
3) Determine which columns should appear in the result table.
4) Write a query that joins the tables using the table's primary and foreign keys.
5) Break the problem into simple queries, writing one part of the query at a time.
114
Blank restructure table columns and data types without changes to the underlying database design.
Views
115
A blank is a table name associated with a SELECT statement, called the view query.
View table
116
The blank statement creates a view table and specifies the view name, query, and, optionally, column names.
CREATE VIEW
117
In a CREATE VIEW query, If column names are not specified, column names are the same as in the blank.
view query result table.
118
Name the syntax for a CREATE VIEW query
CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement;
119
A table specified in the view query's FROM clause is called a blank
Base table
120
Unlike base table data, view table data is blank
not normally stored
121
When a view table appears in an SQL statement, the view query is blank.
merged with the SQL query
122
In some databases, view data can be stored. A blank is a view for which data is stored at all times.
materialized view
123
Whenever a base table changes, the corresponding view tables can also change, so materialized views must be blank
Refreshed
124
A view can be defined on other view tables when the view query FROM clause includes additional view tables. In this case, the additional view tables are not base tables. Base tables are always blank, created as tables rather than as views.
source tables
125
View tables have several advantages. Name three.
1) protect sensitive data
2) save complex queries
3) save optimized queries
126
Authorizing users and programmers access to the view but not the underlying table protects the blank
Sensitive data
127
Complex SELECT statements can be saved as a view. Database users can blank without writing the SELECT statement.
Reference the view
128
Often, the same result table can be generated with equivalent SELECT statements. Although the results of equivalent statements are the same, performance may vary. To ensure fast execution, the blank can be saved as a view and distributed to database users.
Optimal query
129
View tables are commonly used in SELECT statements. Using views in INSERT, UPDATE, and DELETE statements is blank
Problematic
130
If a base table blank does not appear in a view, an insert to the view generates a NULL blank. Since blank may not be NULL, the insert is not allowed.
Primary key
131
A view query may contain aggregate functions such as AVG() or SUM(). One aggregate value corresponds to blank. An update or insert to the view may create a new aggregate value, which must be converted to blank. The conversion is undefined, so the insert or update is not allowed.
Many base table values
132
In a join view, foreign keys of one base table may match primary keys of another. A delete from a view might delete foreign key rows only, or primary key rows only, or both the primary and foreign key rows. The effect of the join view delete is blank and therefore not allowed.
Undefined
133
Relational databases either disallow or severely limit view table inserts, updates, and deletes. Regardless of specific database limitations, inserts, updates, and deletes to views should be avoided. Views are best for blank.
Reading data
134
A view insert or update may create a row that does not satisfy the view query WHERE clause. In this case, the inserted or updated row blank
Does not appear in the view table
135
To prevent inserts or updates that appear to fail, databases that support view updates have an optional blank clause.
WITH CHECK OPTION
136
When WITH CHECK OPTION is specified, the database blanks inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.
Rejects
137
Give the syntax for a WITH CHECK OPTION
CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement
[ WITH CHECK OPTION ];
138
In his original paper on the relational model, E. F. Codd introduced formal operations for manipulating tables. Codd's operations, called blank, have since been refined and are the theoretical foundation of SQL.
Relational algebra
139
Relational algebra has nine operations. Each operation is denoted with a blank, often a letter of the Greek alphabet.
Special symbol
140
Select corresponds to Latin letter s, for Select what is the symbol
sigma
141
Project corresponds to Latin letter P, for Project. Name the symbol
Pi
142
Product’s special symbol
X
143
Join’s special symbol
multiplication symbol with vertical bars
144
Union set theory’s special symbol
U
145
Intersect set theory’s special symbol
Upside down U
146
Difference set theory symbol
—
147
Rename special symbol corresponds to Latin letter r, for Rename
rho
148
Aggregate special symbol corresponds to Latin letter g, for group
Gamma
149
Relational algebra is not standardized. Some authors include additional operations, such as blank and blank. Variations of the join operation, such as blank, are sometimes considered distinct operations.
Assign and divide
inner join, full join, and equijoin
150
The select operation selects table rows based on a logical expression. The select operation is written as blank
Sigma symbol expression (table)
151
The project operation selects table columns. The project operation is written as blank
Pi symbol(column1,column2)(table)
152
The product operation combines two tables into one result. The result includes all columns and all combinations of rows from both tables. The product operation is written as blank
Table1 X Table2
153
The join operation, denoted with a "bowtie" symbol, is written as
Table1 |X| expression Table2
154
Because of theta notation, the join operation is sometimes called a blank
Theta join
155
Blank have the same number of columns with the same data types. Column names may be different.
Compatible tables
156
Union, intersect, and difference operate on compatible tables and, collectively, are called blank
set operations
157
The blank combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table.
Union operation
158
The union operation is written as blank
Table1 U Table2
159
Blank operates on two compatible tables and returns only rows that appear in both tables.
Intersect
160
The intersect operation is written as
Table1 upsidedownU Table2
161
The blank operation removes from a table all rows that appear in a second compatible table.
Difference
162
The difference operation is written as
Table1 — Table2
163
The UNION, INTERSECT, and MINUS keywords are part of the SQL standard. MySQL supports UNION but not INTERSECT and MINUS. In MySQL, the intersect and difference operations can be implemented as blank
Joins
164
The blank specifies new table and column names.
Rename operation
165
The rename operation is written as
rhosymbol TableName(ColumnName1, ColumnName2) (Table)
166
In the rename operation, If TableName is omitted, only the blank are changed. If (ColumnName1, ColumnName2, ... ) is omitted, only the blank is changed.
Column names
Table name
167
The blank applies aggregate functions like SUM(), AVG(), MIN(), and MAX().
Aggregate operation
168
The aggregate operation is written as
GroupColumn gammasymbol Function(Column) (Table)
169
In the aggregate operation, If GroupColumn is omitted, the operation is equivalent to SELECT Function(Column) FROM Table and computes a single aggregate value for blank
All rows
170
Relational algebra expressions are blank if the expressions operate on the same tables and generate the same result.
Equivalent
171
A blank converts an SQL query into a sequence of low-level database actions, called the blank
Query optimizer
Query execution plan
172
The blank specifies precisely how to process an SQL statement. A blank is similar to a programming language compiler.
The query execution plan
A query optimizer
173
Query optimizers use equivalent expressions to optimize query execution. The query optimizer performs what five steps:
1) Converts a query to a relational algebra expression.
2) Generates equivalent expressions.
3) Estimates the 'cost' of each operation of each expression.
4) Determines the optimal expression with the lowest total cost.
5) Converts the optimal expression into a query execution plan.
174
The blank of an operation is a numeric estimate of processing time.
Cost
175
The cost estimate usually combines both blank and blank in a single measure.
storage media access and computation time
