Info management finals

aggregate function

performs a calculation on a set of values and

returns a single value.

MIN

the minimum attribute value encountered in a given

column

MAX

the maximum attribute value encountered in a given

column

COUNT

the number of rows containing non-null values

SUM

the sum of all values for a given column

AVG

the arithmetic mean (average) for a specified column

GROUP BY

command option is often used with aggregate

functions to group the result-set by one or more columns.

alias

to assign a temporary name to the column. It can also be

given to tables.

HAVING

command option is used to restrict the output of a

GROUP BY query by applying conditional criteria to the grouped rows.

YEAR

returns a four-digit year

MONTH

returns the number of the month

DAY

returns the number of the day

GETDATE

returns the current date and time

DATEADD

adds a number of selected time/date periods to

a date then returns the date

DATEDIFF

returns the difference between two (2) dates

ABS

returns the absolute value of a number

ROUND

rounds a number to a specified number of decimal

places

CEILING

returns the smallest integer value that is greater

than or equal to a number

FLOOR

returns the largest integer value that is less than or

equal to a number

JOIN

clause combines rows from two or more tables based on a

common column.

INNER JOIN

returns rows that have matching values in both

tables

LEFT JOIN

returns all rows from the left table and the

matched records from the right table. If there is no match from

the right side, the result is NULL.

RIGHT JOIN

returns all rows from the right table and the

matched records from the left table. If there is no match from

the left side, the result is NULL.

FULL JOIN

returns all records when there is a match on

either the left or right table. If there is no match, the missing

side will contain NULL.

CONCAT

joins two or more strings together

LEN

returns the number of characters in a string

TRIM

removes the spaces or specific characters from start

or end of a string

SUBSTRING

returns a part of a string