UNIT 4-6 Flashcards
returns a four-digit year
YEAR(date_value)
returns a two-digit month code
MONTH(date_value)
returns the number of the day
DAY(date_value)
GETDATE()
SQL SERVER
DATE()
MS ACCESS
___ function produces a date by adding a specified number to a specified part of a date
DATEADD(DATEPART, NUM, DATE) SQL SERVER
DATE_ADD() MYSQL
function returns the difference between two parts of a date
DATEDIFF(datepart, stardate, enddate)
function subtracts a specified time interval from a date.
date_sub(date, interval expr type)
function returns the specified part of the date requested. SQL SERVER
datepart(datepart, date_field)
function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc. MYSQL
EXTRACT(unit from date)
UNIT VALUES
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
Returns the current date and time
NOW()
Returns the current date
CURDATE()
Returns the current time
CURTIME()
Returns the current utc date
UTC_DATE()
Returns the current utc time
UTC_TIME()
function rounds numbers to a specified number of decimal places.
ROUND(numeric value, number of decimal places[optional])
operate on values in single rows, one row at a time. A ____“function” can be used to perform an arithmetic operation on a column
Row-level Functions
which returns the next larger integer value when a number contains decimal places.
CEILING()
which returns the next lower integer value when a number contains decimal places.
FLOOR()
which returns the square root of positive numeric values.
SQRT()
which returns the absolute value of any numeric value.
ABS()
which returns a number squared.
SQUARE()
function says that if the expression (or column value) is not null, return the value, but if the value is null, return ValueIfNull. SQL SERVER
ISNULL(expression1, ValueIfNull)
IFNULL()
which returns a NULL if expression1 = expression2. If the expressions are not equal, then expression1 is returned. BOTH LANGUAGES
NULLIF(expression1, expression2)
function is used to display or return from a result set the rows that fall at the top of a range specified by an ORDER BY clause.
TOP
option that can be used with the TOP function.
WITH TIES
returns a certain percentage of rows that fall at the top of a specified range.
PERCENT
so in order to get the bottom 10 percent, you would have to order the sname column in descending order and then select the top 10 percent
BOTTOM PERCENT
used in the SELECT statement to constrain the number of rows in a result set.
LIMIT
specifies the offset of the first row to return.
OFFSET
specifies maximum number of rows to return.
COUNT
The LIMIT clause often used with _____ clause
ORDER BY
a specialized conversion function that always converts from a number (for example, float or numeric) to a character data type
STR(float_expression, character_length, number_of_decimal_places)
function omits rows in the result set that contain duplicate data in the selected columns.
DISTINCT
___ the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. D should be a constant value. MYSQL
FORMAT(X,D)
function is also used to explicitly convert to a given data type. But, the ___ function has additional limited formatting capabilities.
CONVERT(desired_datatype[(length)], original_expression [, style])
convert to date, time, datetime, char
mysql> select convert(‘2014-02-28’, date);
result: ‘2014-02-28’
____ function returns part of a string.
SUBSTRING(stringexpression, startposition, length)
The __ function starts from the __ of the stringexpression or column and returns n characters, and
LEFT
___ function starts from the right of the stringexpression or column and returns n characters.
RIGHT
removes blanks from the end (right) of a string
RTRIM
function returns the starting position of a specified pattern
CHARINDEX
returns the substring from the given string before a specified number of occurrences of a delimiter.
SUBSTRING_INDEX(str, delim, count)
To produce all the fields in the result set (output) in uppercase or in lowercase, you can use the ___ or ____ functions. SQL SERVER
UPPER OR LOWER
function returns the length (number of characters) of a desired string excluding trailing blanks.
LEN(names) AS [Length of Names]
OTHER FUNCTIONS Returns a specified number of records from the top of a result set.
TOP
OTHER FUNCTIONS Omits rows that contain duplicate data.
DISTINCT
OTHER FUNCTIONS Return a certain percentage of records that fall at the top of a range specified.
PERCENT
CONVERSION FUNCTIONS Changes a data type of a column in a result set.
CAST
CONVERSION FUNCTIONS Explicitly converts to a given data type in a result set.
CONVERT
AGGREGATE Returns the specified part of the date requested.
DATEPART
AGGREGATE Extracts a day from a date.
DAY
AGGREGATE Returns the current system date and time.
GETDATE
AGGREGATE Extracts the month from a date.
MONTH
AGGREGATE Changes the format in which SQL Server reads in dates.
SET DATEFORMAT
AGGREGATE Extracts the year from a date.
YEAR
AGGREGATE Returns the next larger integer value.
CEILING
AGGREGATE Returns the next lower integer value.
FLOOR
AGGREGATE Returns a true value if a data item contains a NULL.
ISNULL
AGGREGATE Returns a NULL if a certain condition is met in an expression.
NULLIF
AGGREGATE Rounds numbers to a specified number of decimal places.
ROUND
AGGREGATE Converts from a number to a character data type.
STR
AGGREGATE Returns the square root of positive numeric values.
SQRT
AGGREGATE Returns the square of a number.
SQUARE
STRING Returns the starting position of a specified pattern.
CHARINDEX
INSTR
STRING Returns the length of a string.
LEN (SERVER) LENGTH (MYSQL)
STRING Option that matches a particular pattern.
LIKE
STRING Converts a string to lower case.
LOWER
STRING Returns the right portion of a string.
RIGHT
STRING Removes blanks from the right end of a string.
RTRIM
STRING Returns part of a string.
SUBSTRING (SERVER) SUBSTRING_INDEX(MySQL)
STRING Displays all output in upper case.
UPPER
DATE Adds to a specified part of a date.
DATEADD
DATE Returns the difference between two dates.
DATEDIFF
a procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event
TRIGGER
replaces the existing line ender (;) with the provided symbol. Used for END statement.
DELIMITER
is used if we want to execute the trigger before changes are made to the table. Meaning it will be executed regardless of a successful operation.
BEFORE
is used if we want to execute the trigger before changes are made to the table.
AFTER
indicates that the trigger will be in effect for all the row changes made inside the table
FOR EACH ROW
trigger is used for insert operation
INSERT
trigger is used for update operation
UPDATE
trigger is used for delete operations
DELETE
keywords enable you to access columns in the rows affected by a trigger.
OLD and NEW
In an ____ trigger, only NEW.col_name can be used; there is no old row
INSERT
. In a ___ trigger, only OLD.col_name can be used
DELETE
In an ____ trigger, you can use OLD.col_name to refer to the columns of a row before it is ___
UPDATE
A column named with __ is read only.
OLD
You can refer to a column named with ___ if you have the ___ privilege for it.
NEW , SELECT
In a ____ trigger, you can also change its value with SET NEW.col_name = value. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row.
BEFORE
In a ___ trigger, the ___ value for an _____column is 0, not the sequence number that is generated automatically when the new row actually is inserted.
BEFORE , NEW , AUTO_INCREMENT
Executes when INSERT, UPDATE, and DELETE commands modify data in a table or view.
Data Manipulation Language (DML) trigger
Executes in response to a DDL statement that is often used to make database schema changes. Examples include the CREATE, ALTER, and DROP statements.
Data Definition Language (DDL) trigger
