d426 3

operator

is a symbol that computes a value from one or more other values, called operands:

operands	Operator	Description	Example	Value
Arithmetic	+	Adds two numeric values	4 + 3	7
	- (unary)	Reverses the sign of one numeric value	-(-2)	2
	- (binary)	Subtracts one numeric value from another	11 - 5	6
	*	Multiplies two numeric values	3 * 5	15
	/	Divides one numeric value by another	4 / 2	2
	% (modulo)	Divides one numeric value by another and returns the integer remainder	5 % 2	1
Comparison	=	Compares two values for equality	1 = 2	FALSE
	!= <>	Compares two values for inequality	1 != 2 1 <> 2	TRUE
	<	Compares two values with <	2 < 2	FALSE
	<=	Compares two values with ≤	2 <= 2	TRUE
	>	Compares two values with >	'2019-08-13' > '2021-08-13'	FALSE
	>=	Compares two values with ≥	'apple' >= 'banana'	FALSE
Logical	AND	Returns TRUE only when both values are TRUE	TRUE AND FALSE	FALSE
	OR	Returns FALSE only when both values are FALSE	TRUE OR FALSE	TRUE
	NOT	Reverses a logical value	NOT FALSE	TRUE

unary vs binary

unary: 1 operand, arithimetic

binary: 2 operands, most operators, logical, arithmetic

expression

is a string of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. The value of an________may be any data type.

Ex: Salary > 34000 AND Department = 'Marketing'

operator precedence

Precedence	Operators
1	- (unary)
2	^
3	*     /     %
4	+     - (binary)
5	=     !=     <     >     <=     >=
6	NOT
7	AND
8	OR

select

statement selects rows from a table

FROM clause specifies the table from which rows are selected.

*is for all cloumns

-- SELECT with expressions
SELECT Expression1, Expression2, ... 
FROM TableName;

limit

clause that limits the number of rows returned by a SELECT statement.

SELECT *
FROM City
LIMIT 100;

condition

is an expression that evaluates to a logical value.

where

A SELECT statement has an optional _______ clause that specifies a condition for selecting rows. A row is selected when the condition is TRUE for the row values. A row is omitted when the condition is either FALSE or NULL.

SELECT Expression1, Expression2, ...
FROM TableName
WHERE Condition;

is null (in not null)

operators must be used to select NULL values

Value IS NULL returns TRUE when the value is NULL.

Value IS NOT NULL returns TRUE when the value is not NULL.

WHERE Salary = NULL; never returns any rows, because the WHERE clause is always NULL.

truth table

x	y	x AND y	x OR y
TRUE	NULL	NULL	TRUE
NULL	TRUE
FALSE	NULL	FALSE	NULL
NULL	FALSE
NULL	NULL	NULL	NULL

x	NOT x
NULL	NULL

in

operator is used in the WHERE clause to check if a specified column's value matches any value within a provided list.

operator functions as a shorthand for multiple OR conditions, making queries shorter and more readable.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

between

operator provides an alternative way to determine if a value is between two other values.

replaces value >= minValue AND value <= maxValue.

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", "Lift", and "Lol cat".

• _ matches exactly one character. Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot".

• : LIKE BINARY 'L%t' matches 'Left' but not 'left'. to make uery case sensitive

• LIKE 'a\%' matches "a%" matches wildcard characters

distinct

keyword is used with a SELECT statement to return only unique or _____ values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

order by

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

The DESC keyword with the ________clause orders rows in descending order.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

function

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.

numeric functions

Function	Description	Example
ABS(n)	Returns the absolute value of n	SELECT ABS(-5); returns 5
LOG(n)	Returns the natural logarithm of n	SELECT LOG(10); returns 2.302585092994046
POW(x, y)	Returns x to the power of y	SELECT POW(2, 3); returns 8
RAND()	Returns a random number between 0 (inclusive) and 1 (exclusive)	SELECT RAND(); returns 0.11831825703225868
ROUND(n, d)	Returns n rounded to d decimal places	SELECT ROUND(16.25, 1); returns 16.3
SQRT(n)	Returns the square root of n	SELECT SQRT(25); returns 5

string functions

Function	Description	Example
CONCAT(s1, s2, ...)	Returns the string that results from concatenating the string arguments	SELECT CONCAT('Dis', 'en', 'gage'); returns 'Disengage'
LOWER(s)	Returns the lowercase s	SELECT LOWER('MySQL'); returns 'mysql'
REPLACE(s, from, to)	Returns the string s with all occurrences of from replaced with to	SELECT REPLACE('This and that', 'and', 'or'); returns 'This or that'
SUBSTRING(s, pos, len)	Returns the substring from s that starts at position pos and has length len	SELECT SUBSTRING('Boomerang', 1, 4); returns 'Boom'
TRIM(s)	Returns the string s without leading and trailing spaces	SELECT TRIM(' test '); returns 'test'
UPPER(s)	Returns the uppercase s	SELECT UPPER('mysql'); returns 'MYSQL'

date and time functions

Function	Description	Example
CURDATE() CURTIME() NOW()	Returns the current date, time, or date and time in 'YYYY-MM-DD', 'HH:MM:SS', or 'YYYY-MM-DD HH:MM:SS' format	SELECT CURDATE(); returns '2019-01-25' SELECT CURTIME(); returns '21:05:44' SELECT NOW(); returns '2019-01-25 21:05:44'
DATE(expr) TIME(expr)	Extracts the date or time from a date or datetime expression expr	SELECT DATE('2013-03-25 22:11:45'); returns '2013-03-25' SELECT TIME('2013-03-25 22:11:45'); returns '22:11:45'
DAY(d) MONTH(d) YEAR(d)	Returns the day, month, or year from date d	SELECT DAY('2016-10-25'); returns 25 SELECT MONTH('2016-10-25'); returns 10 SELECT YEAR('2016-10-25'); returns 2016
HOUR(t) MINUTE(t) SECOND(t)	Returns the hour, minute, or second from time t	SELECT HOUR('22:11:45'); returns 22 SELECT MINUTE('22:11:45'); returns 11 SELECT SECOND('22:11:45'); returns 45
DATEDIFF(expr1, expr2) TIMEDIFF(expr1, expr2)	Returns expr1 - expr2 in number of days or time values, given expr1 and expr2 are date, time, or datetime values	SELECT DATEDIFF('2013-03-10', '2013-03-04'); returns 6 SELECT TIMEDIFF('10:00:00', '09:45:30'); returns 00:14:30

SELECT *
FROM Movie
WHERE YEAR(ReleaseDate) > 2017 OR MONTH(ReleaseDate) = 11;

aggregate function

processes values from a set of rows and returns a summary value. Common:

• 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.

appear in a SELECT clause and process all rows that satisfy the WHERE clause condition.

ignore null values

group by

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.

SELECT column1, aggregate_function(column2), column3, ...
FROM table_name
WHERE condition
GROUP BY column1, column3
ORDER BY column_name;

having

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

follows the GROUP BY clause and precedes the optional ORDER BY clause.

SELECT column1, aggregate_function(column2), column3, ...
FROM table_name
WHERE condition
GROUP BY column1, column3
HAVING condition -- The condition on grouped data
ORDER BY column_name;

join

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

tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types.

natural join

combines two tables by automatically matching columns that share the same name and have compatible data types.

It eliminates duplicate columns from the result set, producing a streamlined table that contains only one copy of each matched column.

prefix

When duplicate column names appear in a query, the names must be distinguished with a ________

alias

Use of a prefix makes column names more complex. To simplify queries or result tables, a column name can be replaced with an _______.

follows the column name, separated by an optional AS keyword.

SELECT column_name AS alias_name
FROM table_name;

inner join

Returns only rows that have matching values in both tables. no null values

can be written without the JOIN keyword

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

full join

returns all rows when there is a match in either the left or right table. unmatched result as NULL

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

left join

returns all rows from the left table (table1), and only the matched rows from the right table (table2).

If there is no match in the right table, the result for the columns from the right table will be NULL.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

right join

returns all rows from the right table (table2), and only the matched rows from the left table (table1).

If there is no match in the left table, the result for the columns from the left table will be NULL.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

outer join

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

• LEFT (OUTER) JOIN: Returns all rows from the left table, and only the matched rows from the right table

• RIGHT (OUTER) JOIN: Returns all rows from the right table, and only the matched rows from the left table

• FULL (OUTER) JOIN: Returns all rows when there is a match in either the left or right table

can be written with a UNION keyword instead of a JOIN keyword.

union

operator is used to combine the result-set of two or more SELECT statements.

automatically removes duplicate rows from the result set.

• Every SELECT statement within _____ must have the same number of columns

• The columns must also have similar data types

• The columns in every SELECT statement must also be in the same order

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

equijoin

compares columns of two tables with the = operator. Most joins are _____

non-equijoin

compares columns with an operator other than =, such as < and >.

SELECT col1, col2
FROM table1
LEFT JOIN table2
ON col3 < col4;

self join

regular join, but the table is joined with itself.

aliases are necessary to distinguish left and right tables.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

cross join

combines two tables without comparing columns. A cross-join uses a _______ clause without an ON clause. As a result, all possible combinations of rows from both tables appear in the result.

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

view table

a virtual table (or behaves like a table), even though it doesn't usually store data.

• based on a SELECT statement.

• It can change how data is shown (columns, order, names, or values) without changing the database.

• insert, update, delete not recommended

• Used to:

  • Hide sensitive data.

  • Save complex queries.

  • Reuse optimized queries.

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.

CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement;

base table

A table specified in the view query's FROM clause. refrence tables for view query

are always source tables, created as tables rather than as views.

data is stored

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.

with check option

the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation.

CREATE VIEW ViewName [ ( Column1, Column2, ... ) ]
AS SelectStatement
[ WITH CHECK OPTION ];