Complex Queries

Question 1

The blank operator is used in a WHERE. Pause to determine if a value matches one of several values

Answer 1

IN

Question 2

Like the IN operator, you can use blank in a WHERE clause to determine if a value doesn’t matches one of several values

Answer 2

NOT IN

Question 3

The blank provides an alternative to determine if a value is between two other values.

Answer 3

BETWEEN operator

Question 4

The BETWEEN operator is written how and is equivalent to value >= minValue AND value <=MaxValue

Answer 4

BETWEEN minValue AND maxValue

Question 5

The BETWEEN operator can or cannot be used with string literals

Answer 5

Can

Question 6

Which runs faster BETWEEN or other <=>= SQL statements

Answer 6

Neither. Same speed

Question 7

The blank when used in a WHERE clause matches text against a pattern using wildcard characters blank and blank

Answer 7

LIKE operator
%
_

Question 8

Blank matches any number of characters when used with LIKE

Answer 8

%

Question 9

Blank matches only one character when used with LIKE

Answer 9

_

Question 10

The LIKe operator performs case-blank patterns matching by default or case-blank pattern matching if followed by the blank keyword

Answer 10

Insensitive
Sensitive
BINARY

Question 11

To search for wildcard characters % or _ a blank must precede them

Answer 11

/

Question 12

Most relational databases provide other mechanisms to perform advanced pattern matching with blank

Answer 12

Regular expressions

Question 13

The blank is used in a SELeCT statement to return any unique or distinct values

Answer 13

DISTINCT clause

Question 14

The DISTINCT clause blank from results

Answer 14

Removes repeated values

Question 15

Give the syntax for using a distinct clause

Answer 15

SELECT DISTINCT ColumnName
FROM TableName

Question 16

A blank selects rows from a table with no guarantee the data will come back in a certain order.

Answer 16

SELECT

Question 17

The blank clause orders selected rows by one or more columns in ascending order.

Answer 17

ORDER BY

Question 18

The blank keyword with the ORDER BY clause orders rows in descending order

Answer 18

DESC

Question 19

A blank operates on an expression in parentheses, called a blank, and returns a value.

Answer 19

Function
Argument

Question 20

Each function operates on, and evaluates to, blank

Answer 20

Specific data types

Question 21

If the argument is invalid, it returns blank

Answer 21

NULL

Question 22

Blank returns the absolute value

Answer 22

ABS(n)

Question 23

Blank returns the natural logarithm of n

Answer 23

LOG(n)

Question 24

Blank returns x to the power of y

Answer 24

POW(x,y)

Question 25

Blank returns a random number between 0(inclusive) and 1(exclusive)

Answer 25

RAND()

Question 26

Blank returns n rounded to d decimal places

Answer 26

ROUND(n,d)

Question 27

Blank returns the square root of n

Answer 27

SQRT(n)

Question 28

Blank manipulate string values

Answer 28

String functions

Question 29

Blank returns the combination of strings

Answer 29

CONCAT(s1,s2)

Question 30

Blank returns lowercase s

Answer 30

LOWER(s)

Question 31

Blank returns s with from changed to to

Answer 31

REPLACE(s,from,to)

Question 32

Blank returns the substring from s starts at pos and has length len

Answer 32

SUBSTRING (s,pos,len)

Question 33

Blank returns the string s without leading or trailing spaces

Answer 33

TRIM(s)

Question 34

Blank returns the uppercase a

Answer 34

UPPER(s)

Question 35

Blank functions operate in DATE, TIME, and DATETIME data types

Answer 35

Date and time

Question 36

Blank returns the current date and time

Answer 36

CURDATE()
CURTIME()
NOW()

Question 37

Blank extracts the date or time

Answer 37

DATE(exp)
TIME(exp)

Question 38

Blank returns the day month or year

Answer 38

DAY(d)
MONTH(d)
YEAR(d)

Question 39

Blank returns hour or minute or second

Answer 39

HOUR(t)
MINUTE(t)
SECOND(t)

Question 40

Blank returns the difference between dates or times

Answer 40

DATEDIFF(expr1,expr2)
TIMEDIFF(expr1,expr2)

Question 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.

Answer 41

Table data type

Question 42

Blank processes values form a set of rows and returns a summary value.

Answer 42

Aggregate functions

Question 43

Blank counts the number of rows in the set.

Answer 43

COUNT()

Question 44

Blank finds the minimum value in the set

Answer 44

MIN()

Question 45

Blank finds the maximum value in the set

Answer 45

MAX()

Question 46

Blank sums all values in the set

Answer 46

SUM()

Question 47

Blank computes the arithmetic mean of all the values in the set

Answer 47

AVG()

Question 48

Aggregate functions appear in a blank clause and process all rows that satisfy the WHERE clause condition

Answer 48

SELECT

Question 49

If a SELECt statement had no WHERE clause, the aggregate function processes blank

Answer 49

All rows

Question 50

Aggregate functions are commonly used with the blank clause

Answer 50

GROUP BY clause

Question 51

The GROUP BY clause consists of the GROUP BY keyword and blank

Answer 51

One or more columns

Question 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?

Answer 52

Group

Question 53

The GROUP BY clause appears between the blank clause, and if any, the blank clause.

Answer 53

WHERE
ORDER BY

Question 54

Aside from the aggregate function, the blank may contain only columns that appear in the GROUP BY clause.

Answer 54

SELECT clause

Question 55

The blank is used with the GROUP BY clause to filter group results

Answer 55

HAVING clause

Question 56

The optional HAVING follows the blank and precedes the optional blank clause

Answer 56

GROUP BY
ORDER BY

Question 57

Give the syntax for a GROUP BY clause

Answer 57

GROUP BY ColumnName

Question 58

Give the syntax for a HAVING clause

Answer 58

GROUP BY ColumnName
HAVING FunctionResultExpression

Question 59

Aggregate functions ignore blank

Answer 59

Null values

Question 60

Arithmetic functions return blank when either operand is null

Answer 60

Null

Question 61

In relational databases, reports are often generated from data in multiple tables. Multi-table reports are written with what?

Answer 61

Join statements

Question 62

A blank is a selected statement that combines data from two tables

Answer 62

JOIN

Question 63

The JOIN gets data from blank and blank to combine into a single result

Answer 63

Left table and right table

Question 64

In a JOIN, the tables are combined by comparing columns from the left and right tables, usually with the blank

Answer 64

= operator

Question 65

Usually, a join compares a blank of one table to the blank of another table.

Answer 65

Foreign Key
Primary Key

Question 66

The columns in a join must have a comparable blank

Answer 66

Data type

Question 67

When duplicate column names appear in a query, the names must be distinguished with a blank

Answer 67

Prefix

Question 68

The prefix in a query where column names are the same is what

Answer 68

The table name followed by a period

Question 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

Answer 69

Alias

Question 70

In order to create the alias, the alias follows the column name, separated by an optional blank.

Answer 70

AS keyword

Question 71

Give the syntax for using the AS keyword

Answer 71

SELECT ColumnName AS Alias…

Question 72

A blank determines how a join query handles unmatched rows

Answer 72

JOIN clause

Question 73

A blank selects only matching left and right table rows

Answer 73

INNER JOIN

Question 74

A blank selects all left and right table rows regardless of match

Answer 74

FULL JOIN

Question 75

In a blank result table, unmatched left table rows appear with NULL values in the right table columns and vice versa

Answer 75

Full join

Question 76

The join clause appears between a blank and a blank

Answer 76

FROM clause
ON clause

Question 77

In a join query, the blank specifies the left table, and the blank specifies the right table

Answer 77

FROM
INNER JOIN or FULL JOIN

Question 78

The blank specifies the join columns

Answer 78

ON clause

Question 79

Give the syntax for a join query

Answer 79

SELECT ColumnName1, ColumnName2
FROM TableName
INNER JOIN or FULL JOIN TableName2
ON Expression;

Question 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

Answer 80

LEFT and RIGHT JOINS

Question 81

A blank selects all the left table rows, but only the matching right table rows

Answer 81

LEFT JOIN

Question 82

A blank selects all the right table rows, but only the matching left table rows

Answer 82

RIGHT JOIN

Question 83

MySQL supports INNER, LEFT, and RIGHT JOINS but not blank

Answer 83

FULL JOINS

Question 84

Give the syntax for a left or right John

Answer 84

SELECT ColumnName1, ColumnName2
FROM TableName1
LEFT JOIN or RIGHT JOIN TableName2
ON Expression;

Question 85

INNER and OUTER JOIN queries can be written without a JOIN clause. However a blank clarifies join behavior and simplifies queries.

Answer 85

JOIN clause

Question 86

Blank keyword combines the results into one table

Answer 86

UNION keyword

Question 87

Blank compares columns of two tables with the = operator.

Answer 87

Equijoin

Question 88

A blank compares columns with an operator other than =, like < and >

Answer 88

Non-equijoin

Question 89

Blank joins a table to itself

Answer 89

Self-join

Question 90

A self-join can compare any columns of a table, as long as the columns have comparable blank

Answer 90

Data types

Question 91

If a blank and a blank are in the same table, a self-join commonly compares these key columns

Answer 91

Foreign key and the referenced primary key

Question 92

In a self-join, lank are necessary to distinguish left and right tables

Answer 92

Aliases

Question 93

Blank combines the tables without comparing columns

Answer 93

CROSS JOIN

Question 94

A cross join used a CROSS JOIN clause without an blank.

Answer 94

ON clause

Question 95

In a CROSS JOIN, all possible blank from both tables appear in the result

Answer 95

Rows

Question 96

Give the syntax for a cross join

Answer 96

SELECT ColumnName1 + ColumnName2
FROM TableName
CROSS JOIN TableName2;

Question 97

A blank, sometimes called a nested query or inner query, is a query within another query.

Answer 97

Subquery

Question 98

The subquery is typically used in a blank’s WHERE clause to return data to the outer query and restrict selected results

Answer 98

SELECT statement

Question 99

The subquery is placed within blank

Answer 99

Parentheses ()

Question 100

A subquery is blank when the subquery’s WHERE clause references a column from the outer query.

Answer 100

Correlated

Question 101

In a correlated subquery, the blank selected depends on what row is currently being examined by the outer row

Answer 101

Rows

Question 102

If the column name in the correlated query is identical to a column name in the outer query, the blank differentiates the columns

Answer 102

TableName.ColumnName

Question 103

A blank can also be used in a correlated subquery to differentiate the column or table using the AS keyword

Answer 103

Alias

Question 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

Answer 104

EXISTS

Question 105

The blank operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected

Answer 105

NOT EXISTS

Question 106

Give the syntax for using EXISTS or NOT EXiSTS

Answer 106

SELECT ColumnName1, ColumnName2
FROM TableName
WHERE EXISTS or NOT EXiSTS
(Subquery);

Question 107

Many sub queries can be written as a blank

Answer 107

JOIN

Question 108

Most databases optimize a subquery and outer query separately, whereas blanks are optimized in one pass

Answer 108

JOINS

Question 109

Replacing a subquery with an equivalent JOIN is called blank

Answer 109

Flattening a query

Question 110

Most sub queries that follow blank or blank, or return a blank can be flattened

Answer 110

IN
EXISTS
A single value

Question 111

Most sub queries that follow blank or contain a blank clause cannot be flattened

Answer 111

NOT EXISTS
GROUP BY

Question 112

Name the five steps for flattening a query

Answer 112

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

Question 113

To create a complex query, name the strategy a user can use (5 steps)

Answer 113

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.

Question 114

Blank restructure table columns and data types without changes to the underlying database design.

Answer 114

Views

Question 115

A blank is a table name associated with a SELECT statement, called the view query.

Answer 115

View table

Question 116

The blank statement creates a view table and specifies the view name, query, and, optionally, column names.

Answer 116

CREATE VIEW

Question 117

In a CREATE VIEW query, If column names are not specified, column names are the same as in the blank.

Answer 117

view query result table.

Question 118

Name the syntax for a CREATE VIEW query

Answer 118

CREATE VIEW ViewName [ ( Column1, Column2, … ) ]
AS SelectStatement;

Question 119

A table specified in the view query’s FROM clause is called a blank

Answer 119

Base table

Question 120

Unlike base table data, view table data is blank

Answer 120

not normally stored

Question 121

When a view table appears in an SQL statement, the view query is blank.

Answer 121

merged with the SQL query

Question 122

In some databases, view data can be stored. A blank is a view for which data is stored at all times.

Answer 122

materialized view

Question 123

Whenever a base table changes, the corresponding view tables can also change, so materialized views must be blank

Answer 123

Refreshed

Question 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.

Answer 124

source tables

Question 125

View tables have several advantages. Name three.

Answer 125

1) protect sensitive data
2) save complex queries
3) save optimized queries

Question 126

Authorizing users and programmers access to the view but not the underlying table protects the blank

Answer 126

Sensitive data

Question 127

Complex SELECT statements can be saved as a view. Database users can blank without writing the SELECT statement.

Answer 127

Reference the view

Question 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.

Answer 128

Optimal query

Question 129

View tables are commonly used in SELECT statements. Using views in INSERT, UPDATE, and DELETE statements is blank

Answer 129

Problematic

Question 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.

Answer 130

Primary key

Question 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.

Answer 131

Many base table values

Question 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.

Answer 132

Undefined

Question 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.

Answer 133

Reading data

Question 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

Answer 134

Does not appear in the view table

Question 135

To prevent inserts or updates that appear to fail, databases that support view updates have an optional blank clause.

Answer 135

WITH CHECK OPTION

Question 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.

Answer 136

Rejects

Question 137

Give the syntax for a WITH CHECK OPTION

Answer 137

CREATE VIEW ViewName [ ( Column1, Column2, … ) ]
AS SelectStatement
[ WITH CHECK OPTION ];

Question 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.

Answer 138

Relational algebra

Question 139

Relational algebra has nine operations. Each operation is denoted with a blank, often a letter of the Greek alphabet.

Answer 139

Special symbol

Question 140

Select corresponds to Latin letter s, for Select what is the symbol

Answer 140

sigma

Question 141

Project corresponds to Latin letter P, for Project. Name the symbol

Answer 141

Pi

Question 142

Product’s special symbol

Answer 142

X

Question 143

Join’s special symbol

Answer 143

multiplication symbol with vertical bars

Question 144

Union set theory’s special symbol

Answer 144

U

Question 145

Intersect set theory’s special symbol

Answer 145

Upside down U

Question 146

Difference set theory symbol

Answer 146

—

Question 147

Rename special symbol corresponds to Latin letter r, for Rename

Answer 147

rho

Question 148

Aggregate special symbol corresponds to Latin letter g, for group

Answer 148

Gamma

Question 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.

Answer 149

Assign and divide
inner join, full join, and equijoin

Question 150

The select operation selects table rows based on a logical expression. The select operation is written as blank

Answer 150

Sigma symbol expression (table)

Question 151

The project operation selects table columns. The project operation is written as blank

Answer 151

Pi symbol(column1,column2)(table)

Question 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

Answer 152

Table1 X Table2

Question 153

The join operation, denoted with a “bowtie” symbol, is written as

Answer 153

Table1 |X| expression Table2

Question 154

Because of theta notation, the join operation is sometimes called a blank

Answer 154

Theta join

Question 155

Blank have the same number of columns with the same data types. Column names may be different.

Answer 155

Compatible tables

Question 156

Union, intersect, and difference operate on compatible tables and, collectively, are called blank

Answer 156

set operations

Question 157

The blank combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table.

Answer 157

Union operation

Question 158

The union operation is written as blank

Answer 158

Table1 U Table2

Question 159

Blank operates on two compatible tables and returns only rows that appear in both tables.

Answer 159

Intersect

Question 160

The intersect operation is written as

Answer 160

Table1 upsidedownU Table2

Question 161

The blank operation removes from a table all rows that appear in a second compatible table.

Answer 161

Difference

Question 162

The difference operation is written as

Answer 162

Table1 — Table2

Question 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

Answer 163

Joins

Question 164

The blank specifies new table and column names.

Answer 164

Rename operation

Question 165

The rename operation is written as

Answer 165

rhosymbol TableName(ColumnName1, ColumnName2) (Table)

Question 166

In the rename operation, If TableName is omitted, only the blank are changed. If (ColumnName1, ColumnName2, … ) is omitted, only the blank is changed.

Answer 166

Column names
Table name

Question 167

The blank applies aggregate functions like SUM(), AVG(), MIN(), and MAX().

Answer 167

Aggregate operation

Question 168

The aggregate operation is written as

Answer 168

GroupColumn gammasymbol Function(Column) (Table)

Question 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

Answer 169

All rows

Question 170

Relational algebra expressions are blank if the expressions operate on the same tables and generate the same result.

Answer 170

Equivalent

Question 171

A blank converts an SQL query into a sequence of low-level database actions, called the blank

Answer 171

Query optimizer
Query execution plan

Question 172

The blank specifies precisely how to process an SQL statement. A blank is similar to a programming language compiler.

Answer 172

The query execution plan
A query optimizer

Question 173

Query optimizers use equivalent expressions to optimize query execution. The query optimizer performs what five steps:

Answer 173

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.

Question 174

The blank of an operation is a numeric estimate of processing time.

Answer 174

Cost

Question 175

The cost estimate usually combines both blank and blank in a single measure.

Answer 175

storage media access and computation time