Data Management - Foundations - D426 (3/6)

ORDER BYColumnNameDESC (clause)

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

SUBSTRING(s, pos, len)(String functions)

Returns the substring from s that starts at position pos and has length len

DATEDIFF(expr1, expr2)(Date and time functions)

Returns expr1 - expr2 in number of days given expr1 and expr2 are date, time, or datetime values

CROSS JOIN(Join queries)

A cross-join uses a _____ ____ clause without an ON clause.

NOT EXISTS(operator)(Correlated Subqueries)

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

Save complex queries(Advantages of views)

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

Save optimized queries(Advantages of views)

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 optimal statement can be saved as a view and distributed to database users.

select operation(Relational algebra)

The ______ _________ selects table rows based on a logical expression. The select operation is written as SELECT * FROM Table WHERE expression.

product operation(Relational algebra)

The _______ _________ combines two tables into one result. The result includes all columns and all combinations of rows from both tables. The _______ _________ is written as SELECT * FROM Table1 CROSS JOIN Table2.

Compatible tables(Relational algebra)

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

rename operation(Relational algebra)

The ______ operation specifies new table and column names.

query optimizer / query execution plan (Relational algebra)

A _____ _________ converts an SQL query into a sequence of low-level database actions, called the _____ _________ ____. The _____ _________ ____ specifies precisely how to process an SQL statement.

Pi \n\ncorresponds to Latin letter P, for Project(Relational algebra)

Ⅱ

multiplication symbol Product(Relational algebra)

Χ \n\n

set theory Union(Relational algebra)

∪ \n\n

set theory Intersect(Relational algebra)

∩ \n\n

rho \n\ncorresponds to Latin letter r, for Rename(Relational algebra)

ρ

gamma \n\n corresponds to Latin letter g, for \n Aggregate(Relational algebra)

γ

IN (operator)

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

BETWEEN (operator)

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

LIKE (operator)

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

LIKEBINARY (operator)

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

DISTINCT (clause)

The ________ clause is used with a SELECT statement to return only unique or 'distinct' values.

ORDER BY (clause)

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

function (Numeric functions)

A________ operates on an expression enclosed in parentheses, called an argument, and returns a value. Some ________ have several arguments, separated by commas, and a few have no arguments at all.

argument (Numeric functions)

Usually, the ________ is a simple expression, such as a column name or fixed value.

ABS(n) (Numeric functions)

Returns the absolute value of n

LOG(n) (Numeric functions)

Returns the natural logarithm of n

POW(x, y) (Numeric functions)

Returns x to the power of y

RAND() (Numeric functions)

Returns a random number between 0 (inclusive) and 1 (exclusive)

ROUND(n, d) (Numeric functions)

Returns n rounded to d decimal places

SQRT(n) (Numeric functions)

Returns the square root of n

String functions(String functions)

manipulate string values.

CONCAT(s1, s2, ...)(String functions)

Returns the string that results from concatenating the string arguments

LOWER(s)(String functions)

Returns the lowercase s

REPLACE(s, from, to)(String functions)

Returns the string s with all occurrences of from replaced with to

TRIM(s)(String functions)

Returns the string s without leading and trailing spaces

UPPER(s)(String functions)

Returns the uppercase s

Date and time functions (Date and time functions)

operate on DATE, TIME, and DATETIME data types.

CURDATE()(Date and time functions)

Returns the current date, time, or date and time in'YYYY-MM-DD'

CURTIME()(Date and time functions)

'HH:MM:SS'

NOW()(Date and time functions)

'YYYY-MM-DD', 'HH:MM:SS', or\n'YYYY-MM-DD HH:MM:SS' format

DATE(expr)(Date and time functions)

Extracts the date from a date expression expr\n2013-03-25'

TIME(expr)(Date and time functions)

Extracts the time from a datetime expression expr\n'22:11:45'

DAY(d)(Date and time functions)

Returns the day from date d

MONTH(d)(Date and time functions)

Returns the month from date d

YEAR(d)(Date and time functions)

Returns the year from date d

HOUR(t)(Date and time functions)

Returns the hour from time t

MINUTE(t)(Date and time functions)

Returns the minute from time t

SECOND(t)(Date and time functions)

Returns the second from time t

TIMEDIFF(expr1, expr2)(Date and time functions)

Returns expr1 - expr2 in number of timevalues, given expr1 and expr2 are date, time, or datetime values

MySQL (Note)

_____ does not support table data type and table-valued functions.

aggregate function (Aggregate functions)

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

COUNT() (Aggregate functions)

counts the number of rows in the set.

MIN()(Aggregate functions)

finds the minimum value in the set.

MAX()(Aggregate functions)

finds the maximum value in the set.

SUM()(Aggregate functions)

sums all the values in the set.

AVG()(Aggregate functions)

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

GROUP BY(Clause)(Aggregate functions)

The _____ __ clause consists of the _____ __ 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.

HAVING (Clause)(Aggregate functions)

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

join (Join queries)

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

AS(aliases)

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 __ keyword.

join clause(Join queries)

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

INNER JOIN(Join queries)

selects only matching left and right table rows.

FULL JOIN(Join queries)

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

ON (Join queries)

The __clause specifies the join columns.

LEFT JOIN(Join queries)

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

RIGHT JOIN(Join queries)

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

outer join (Join queries)

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

UNION(Join queries)

The _____ keyword combines the two results into one table.

MYSQL (Note)

_____ supports INNER, LEFT, and RIGHT JOIN but not FULL JOIN.

equijoin(compareoperator)

An ________compares columns of two tables with the = operator.

non-equijoin(compareoperator)

A ___-________compares columns with an operator other than =, such as < and >.

self-join (Join queries)

A ____-____ joins a table to itself.

cross-join(Join queries)

A _____-____ combines two tables without comparing columns.

subquery/nested query/inner query(Subqueries)

is a query within another SQL query.

correlated(Correlated subqueries)

A subquery is __________ when the subquery's WHERE clause references a column from the outer query.

alias (Aliases)

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

AS (Aliases)

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

EXISTS (operator)(Correlated Subqueries)

Correlated subqueries commonly use the ______ operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.

flattening (Join queries)

Replacing a subquery with an equivalent join is called ___________ a query.

view table / view query (view tables)

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

CREATE VIEW(view tables)

The _____ ____ 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.

base table(Querying views)

A table specified in the view query's FROM clause is called a ____ table.

materialized view(Querying views)

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

Protect sensitive data (Advantages of views)

A table may contain sensitive data.

Primary keys (Inserting, updating, and deleting views issues)

If a base table _______ ___ does not appear in a view, an insert to the view generates a NULL _______ ___ value. Since _______ ___s may not be NULL, the insert is not allowed.

Aggregate values (Inserting, updating, and deleting viewsissues)

A view query may contain _________ ______ such as AVG() or SUM(). One aggregate value corresponds to many base table values. An update or insert to the view may create a new aggregate value, which must be converted to many base table values. The conversion is undefined, so the insert or update is not allowed.

Join views(Inserting, updating, and deleting viewsissues)

In a ____ _____, 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 undefined and therefore not allowed.

WITH CHECK OPTION (clause)

When ____ _____ ______ is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.

relational algebra (Relational algebra)

In his original paper on the relational model, E. F. Codd introduced formal operations for manipulating tables. Codd's operations, called _________ _______, have since been refined and are the theoretical foundation of SQL.

project operation(Relational algebra)

The _______ _________ selects table columns. The _______ _________ is written as SELECT Column1, Column2, ... FROM Table.

join(Relational algebra)

The ____ operation, denoted with a "bowtie" symbol, is written as SELECT * FROM Table1 INNER JOIN Table2 ON expression.

theta join (Relational algebra)

Because of theta notation, the join operation is sometimes called a _____ ____.

set operations(Relational algebra)

Union, intersect, and difference operate on compatible tables and, collectively, are called ___ __________.

union(Relational algebra)

The _____ operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table. The _____ operation is written asSELECT FROM Table1 UNION SELECT FROM Table2.

Intersect(Relational algebra)

_________ operates on two compatible tables and returns only rows that appear in both tables. The _________ operation is written as SELECT FROM Table1 INTERSECT SELECT FROM Table2.

difference(Relational algebra)

The ___________ operation removes from a table all rows that appear in a second compatible table. The ___________ operation is written as SELECT FROM Table1 MINUS SELECT FROM Table2.

aggregate operation(Relational algebra)

The __________ operation applies aggregate functions like SUM(), AVG(), MIN(), and MAX(). The __________ operation is written as SELECT GroupColumn, Function(Column) FROM Table GROUP BY GroupColumn.

equivalent (Relational algebra)

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