D426: Data Management Foundations 3

IN operator

used in a WHERE clause to determine if a value matches one of several in a list

WHERE ColumnName IN (value1, value2, …)

syntax for IN clause

BETWEEN operator

provides an alternate way to determine if a value is between two other values; is inclusive

LIKE operator

used in a WHERE clause to match against two wildcard characters

%

wildcard character that can stand in for any number of characters

_

wildcard character that stands in for only a single character

LIKE BINARY

case sensitive version of the LIKE operator

DISTINCT clause

used with a SELECT statement to return only values that are unique given the column or combination of columns passed to the clause

ORDER BY clause

sorts values by a specific column in either ascending or descending (DESC) order; sort levels are ordered by the order of the columns

RAND()

generates a random number from within the set [0, 1)

REPLACE(‘s’, from, to)

returns ‘s’ with all occurrences of ‘from’ replaced with ‘to’

SUBSTRING(‘s,’ pos, len)

returns the substring from ‘s’ that is len characters long and starts with the character at position pos

TRIM(s)

returns string ‘s’ with extra white space removed

UPPER(‘s’)

returns ‘s’ in all caps

LOWER(‘s’)

returns ‘s’ as all lowercase letters

CONCAT(s1, s2, …)

returns the string arguments as one combined string

CURDATE()

returns current date in format ‘YYYY-MM-DD’

CURTIME()

returns current time in format ‘HH:MM:SS’

NOW()

returns current time and date in format ‘YYYY-MM-DD HH:MM:SS’

DATE(expr)

extracts the date from a date or datetime expression

TIME(expr)

extracts the time from a time or datetime expression

DAY(d)

returns the day from date “d”

MONTH(d)

returns the month from date “d”

YEAR(d)

returns the year from date “d”

HOUR(t)

returns the hour from time “t”

MINUTE(t)

returns the minute from time “t”

SECOND(t)

returns the seconds value from time “t”

DATEDIFF(expr1, expr2)

returns the value of expr1 - expr2 in days

TIMEDIFF(expr1, expr2)

returns the value of expr1 - expr2 in time values

GROUP BY

appears between the WHERE and ORDER BY clauses of a SELECT statement and defines columns whose values should be used to define the groupings for an aggregate function. One row will be returned for each group.

HAVING clause

used with the GROUP BY clause to filter group results

join

a select statement that combines data from two tables into a single result by comparing the values in two columns with the same data type, usually primary and foreign keys

AS

keyword to define a column alias to distinguish columns that would otherwise have the same name after a join statement

INNER JOIN

selects only matching left and right table rows

FULL JOIN

selects all left and right table rows, regardless of match. Unmatched left table rows appear with NULL values in right table columns and vice versa. Not supported in MySQL.

ON clause

specified join columns of two tables

LEFT JOIN

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

RIGHT JOIN

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

outer join

any join that selects unmatched rows, including left, right, and full jions

UNION

keyword placed between two select statements to join the results into one table

equijoin

compares the columns of two tables with the = operator

non-equijoin

compares the columns of two tables with an operator other than =, such as < or >

self-join

joins a table to itself. Aliases are specified after the table name in the FROM and JOIN clauses

CROSS JOIN

combines two tables without comparing columns and results in all possible combinations of rows from both tables

subquery

nested query

inner query

a query within another SQL query, typically used in a SELECT statement’s WHERE clause to return data to the outer query and restrict the selected results

correlated subqueries

when a subquery’s WHERE clause references a column from the outer query. In this case, the rows selected depend on what row is currently being examined by the outer query

EXISTS operator

returns TRUE if a subquery selects at least one row and FALSE if no rows are selected

operator

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

flattening

when a subquery is replaced with an equivalent join. Criteria are complex and depend on the SQL implementation in each database system.

most subqueries that follow IN or EXISTS, or return a single value

subqueries that can be flattened

most subqueries that follow NOT EXISTS or contain a GROUP BY clause

subqueries that cannot be flattened

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

First step for flattening a query

Add INNER JOIN clauses for each subquery table

Second step for flattening a query

Move comparisons between subquery and outer query columns to ON clauses

Third step for flattening a query

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

Fourth step for flattening a query

If necessary, remove duplicate rows with SELECT DISTINCT

Fifth step for flattening a query

Test the flattened query and adjust to achieve the correct result.

Final step for flattening a query

view table

a table name associated with a SELECT statement

view query

the SELECT statement associated with a specific view table

CREATE VIEW statement

specifies the view name, query, and optional column names for a view table

base table

a table specified in the view query’s FROM clause, unless it is another view table

materialized view

a view for which data is stored at all times. Not supported by MySQL and many other databases as it requires a lot of overhead to refresh every time a base table is changed.

• Protect sensitive data

• Save complex queries

• Save optimized queries

Advantages of view tables

WITH CHECK OPTION clause

an option for CREATE VIEW statements that cause the database to reject inserts and updates that do not satisfy the view query WHERE clause

relational algebra

A set of nine operations that form the theoretical foundation of SQL and were first laid out by E.F. Codd in his paper on the relational model

compatible tables

tables with the same number of columns with the same data types

set operations

the collective term for union, intersect, and difference operations, which are performed on compatible tables

equivalent expressions

expressions that operate on the same tables and generate the same result

query optimizer

software similar to a programming language compiler that converts an SQL query into a sequence of low-level database actions

query execution plan

the sequence of low-level database actions that a query optimizer converts an SQL query into

cost of an operation

a numeric estimate of processing time for an operation, which usually combines both storage media access and computation time in a single measure