Section 3 Flashcards
(25 cards)
The BETWEEN operator
Provides an alternative way to determine if a value is between two other values
The LIKE operator
When used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _
% matches any number of characters. Ex: LIKE ‘L%t’ matches “Lt”, “Lot”, and “ Lol cat”.
_ matches exactly one character. Ex: LIKE ‘L_t’ matches “Lot”, and “Lit” but not “Lt” and “Loot”
The ORDER BY clause
Orders selected rows by one or more columns in ascending( alphabetic or increasing) order.
The DESC keyword with the ORDER BY clause orders rows in descending order.
Function: ABS(n)
Description: ?
Ex: ?
Description: Returns the absolute value of n
Ex: SELECT ABS(-5);
returns 5
Function: LOWER(s)
Description: ?
Ex: ?
Description: Returns the lowercase
Ex: SELECT LOWER(‘MySQL’);
returns ‘mysql’
Function: TRIM(s)
Description: ?
Ex: ?
Description: Returns the string s without leading and trailing spaces
Ex: SELECT TRIM(‘ test ‘);
returns ‘test’
Function:
HOUR(t)
MINUTE(t)
SECOND(t)
Description:?
Ex:?
Description: Returns the hour, minute or second from time t
An aggregate function
Processes values from a set of rows and returns a summary value.
Common aggregate functions are:
-COUNT() counts the number of rows in the set
-MIN() finds the minimum value in the set
-MAX() finds the maximum value in the set
-SUM() sums all the values in the set
-AVG() computes the arithmetic mean of all the values in the set.
The HAVING clause
Is used with the GROUP BY clause to filter group results
A join is
A SELECT statement that combines data from two tables, known as the left table and right table, into a single result.
The tables are combined by comparing columns from the left and right tables, usually with the = operator
Inner join
Selects only 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 right table rows
Right join
Selects all right table rows, but only matching left table rows.
An Outer join
Is any join that selects unmatched rows, including left, right and full joins
The UNION keyword
Combines the two results into one table
An equijoin
Compares columns of two tables with the = operator. Most joins are equijoins.
A non-equijoin
Compares columns with an operator other than =, such as < and >.
A self-join
Joins a table to itself
A cross-join
Combines two tables without comparing columns. A —– uses a —– clause without an ON clause
A subquery
Sometimes called a nested query or inner query, is a query within another SQL query.
An alias
Is a temporary name assigned to a column or table.
The AS keyword follows a column or table name to create an —-
A materialized view
Is a view for which data is stored at all times. Whenever a base table changes, the corresponding view tables can also change, so —– must be refreshed.
