Lesson 3 Flashcards
BETWEEN
determines if a value is between two other values
BETWEEN minValue AND maxValue
LIKE
when used in a WHERE clause, matches text against a pattern using the two wildcard character %(percentage symbol) and _(underscore symbol)
ORDER BY
orders selected rows by one or more columns in ascending order
DESC reverses to descending order
function/argument
A function operates on an expression enclosed in parentheses, called an argument, and returns a value
ABS()
returns the absolute value
SELECT ABS(-5); – returns 5
LOWER()
returns lowercase
SELECT LOWER(MySQL); – returns mysql
TRIM()
removes leading and trailing whitespace
SELECT TRIM(‘ hello ‘); – returns ‘hello’
LTRIM removes leading spaces
RTRIM removes traling spaces
HOUR(), MINUTE(), SECOND()
returns hour, minute or second from timestamp
aggregate function
processes values from a set of rows and returns a summary value
COUNT()
count the number of rows
aggregate function
MIN()
finds the minimum value
aggregate function
MAX()
fins the maximum value
aggregate function
SUM()
sums all the values in the set
aggregate function
AVG()
computes the arithmetic mean of all the values
aggregate function
HAVING
used with the GROUPBY clause to filter group results
GROUP BY
used with aggregate functions to produce summary rows
join
A join is a SELECT statement that combines data from two tables
AS
to simplify queries or result tables, a column name can be replaced with an alias
INNER JOIN
selects only matching left and right table rows
FULL JOIN
selects all left and right table rows, regardless of match
LEFT JOIN
selects all left table rows, but only matching left table rows
RIGHT JOIN
selects all tight table rows, but only matching left table rows
confusing, most avoid its usage
UNION
combines the two results into one table
Equijoin
Non-Equijoin
self-join
joins a table to itself
cross-join
combines two tables without comparing columns
subquery
sometimes called nested or inner query, is a query within another SQL query
materialized view
view for which view table data is stored at all times
is automatically changed as the underlying data is changed
IN
used in WHERE clause to determine if a value matches one of several values
