2. Complex Queries Flashcards

1
Q

IN

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

BETWEEN

A

The BETWEEN operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

LIKE

A

The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

BINARY

A

The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

DISTINCT

A

The DISTINCT clause is used with a SELECT statement to return only unique or ‘distinct’ values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

ORDER BY

A

The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

DESC

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

function / argument

A

A function operates on an expression enclosed in parentheses, called an argument, and returns a value. Usually, the argument is a simple expression, such as a column name or fixed value. Some functions have several arguments, separated by commas, and a few have no arguments at all.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

aggregate function

A

An aggregate function processes values from a set of rows and returns a summary value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

COUNT()

A

COUNT() counts the number of rows in the set.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

MIN()

A

MIN() finds the minimum value in the set.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

MAX()

A

MAX() finds the maximum value in the set.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

SUM()

A

SUM() sums all the values in the set.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

AVG()

A

AVG() computes the arithmetic mean of all the values in the set.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

GROUP BY

A

The GROUP BY clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

HAVING

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

join / left table / right table

A

A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

AS

A

To 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

A join clause determines how a join query handles unmatched rows. Two common join clauses are:

20
Q

INNER JOIN

A

INNER JOIN selects only matching left and right table rows.

21
Q

FULL JOIN

A

FULL JOIN selects all left and right table rows, regardless of match.

22
Q

ON

A

The ON clause specifies the join columns.

23
Q

LEFT JOIN

A

LEFT JOIN selects all left table rows, but only matching right table rows.

24
Q

RIGHT JOIN

A

RIGHT JOIN selects all right table rows, but only matching left table rows.

25
Q

outer join

A

An outer join is any join that selects unmatched rows, including left, right, and full joins.

26
Q

UNION

A

The UNION keyword combines the two results into one table.

27
Q

equijoin

A

An equijoin compares columns of two tables with the = operator.

28
Q

non-equijoin

A

A non-equijoin compares columns with an operator other than =, such as < and >.

29
Q

self-join

A

A self-join joins a table to itself.

30
Q

cross-join

A

A cross-join combines two tables without comparing columns.

31
Q

CROSS JOIN

A

A cross-join uses a CROSS JOIN clause without an ON clause.

32
Q

subquery / nested query / inner query

A

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

33
Q

correlated

A

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

34
Q

alias

A

An alias is a temporary name assigned to a column or table.

35
Q

AS

A

The AS keyword follows a column or table name to create an alias.

36
Q

EXISTS

A

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
Q

NOT EXISTS

A

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

38
Q

flattening

A

Replacing a subquery with an equivalent join

Most subqueries that follow IN or EXISTS, or return a single value, can be flattened. Most subqueries that follow NOT EXISTS or contain a GROUP BY clause cannot be flattened.

The following steps are a first pass at flattening a query:

Retain the outer query SELECT, FROM, GROUP BY, HAVING, and ORDER BY clauses.

Add INNER JOIN clauses for each subquery table.

Move comparisons between subquery and outer query columns to ON clauses.

Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses.

If necessary, remove duplicate rows with SELECT DISTINCT.

After this first pass, test the flattened query and adjust to achieve the correct result. Verify that the original and flattened queries are equivalent against a variety of data.

39
Q

view table / view query

A

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

40
Q

CREATE VIEW

A

The CREATE VIEW statement 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
Q

base table

A

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

42
Q

materialized view

A

A materialized view is a view for which data is stored at all times.

43
Q

WITH CHECK OPTION

A

When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.