Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

DATABASE > UNIT 4-6 > Flashcards

UNIT 4-6 Flashcards

(93 cards)

Start Studying
1
Q

returns a four-digit year

A

YEAR(date_value)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

returns a two-digit month code

A

MONTH(date_value)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

returns the number of the day

A

DAY(date_value)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

GETDATE()

A

SQL SERVER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

DATE()

A

MS ACCESS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

___ function produces a date by adding a specified number to a specified part of a date

A

DATEADD(DATEPART, NUM, DATE) SQL SERVER

DATE_ADD() MYSQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

function returns the difference between two parts of a date

A

DATEDIFF(datepart, stardate, enddate)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

function subtracts a specified time interval from a date.

A

date_sub(date, interval expr type)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

function returns the specified part of the date requested. SQL SERVER

A

datepart(datepart, date_field)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc. MYSQL

A

EXTRACT(unit from date)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

UNIT VALUES

A

MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Returns the current date and time

A

NOW()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Returns the current date

A

CURDATE()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Returns the current time

A

CURTIME()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Returns the current utc date

A

UTC_DATE()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Returns the current utc time

A

UTC_TIME()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

function rounds numbers to a specified number of decimal places.

A

ROUND(numeric value, number of decimal places[optional])

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

operate on values in single rows, one row at a time. A ____“function” can be used to perform an arithmetic operation on a column

A

Row-level Functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

which returns the next larger integer value when a number contains decimal places.

A

CEILING()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

which returns the next lower integer value when a number contains decimal places.

A

FLOOR()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

which returns the square root of positive numeric values.

A

SQRT()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

which returns the absolute value of any numeric value.

A

ABS()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

which returns a number squared.

A

SQUARE()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

ISNULL(expression1, ValueIfNull)

IFNULL()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
which returns a NULL if expression1 = expression2. If the expressions are not equal, then expression1 is returned. BOTH LANGUAGES
NULLIF(expression1, expression2)
26
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
27
option that can be used with the TOP function.
WITH TIES
28
returns a certain percentage of rows that fall at the top of a specified range.
PERCENT
29
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
30
used in the SELECT statement to constrain the number of rows in a result set.
LIMIT
31
specifies the offset of the first row to return.
OFFSET
32
specifies maximum number of rows to return.
COUNT
33
The LIMIT clause often used with _____ clause
ORDER BY
34
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)
35
function omits rows in the result set that contain duplicate data in the selected columns.
DISTINCT
36
___ 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)
37
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])
38
convert to date, time, datetime, char
mysql> select convert('2014-02-28', date); result: '2014-02-28'
39
____ function returns part of a string.
SUBSTRING(stringexpression, startposition, length)
40
The __ function starts from the __ of the stringexpression or column and returns n characters, and
LEFT
41
___ function starts from the right of the stringexpression or column and returns n characters.
RIGHT
42
removes blanks from the end (right) of a string
RTRIM
43
function returns the starting position of a specified pattern
CHARINDEX
44
returns the substring from the given string before a specified number of occurrences of a delimiter.
SUBSTRING_INDEX(str, delim, count)
45
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
46
function returns the length (number of characters) of a desired string excluding trailing blanks.
LEN(names) AS [Length of Names]
47
OTHER FUNCTIONS Returns a specified number of records from the top of a result set.
TOP
48
OTHER FUNCTIONS Omits rows that contain duplicate data.
DISTINCT
49
OTHER FUNCTIONS Return a certain percentage of records that fall at the top of a range specified.
PERCENT
50
CONVERSION FUNCTIONS Changes a data type of a column in a result set.
CAST
51
CONVERSION FUNCTIONS Explicitly converts to a given data type in a result set.
CONVERT
52
AGGREGATE Returns the specified part of the date requested.
DATEPART
53
AGGREGATE Extracts a day from a date.
DAY
54
AGGREGATE Returns the current system date and time.
GETDATE
55
AGGREGATE Extracts the month from a date.
MONTH
56
AGGREGATE Changes the format in which SQL Server reads in dates.
SET DATEFORMAT
57
AGGREGATE Extracts the year from a date.
YEAR
58
AGGREGATE Returns the next larger integer value.
CEILING
59
AGGREGATE Returns the next lower integer value.
FLOOR
60
AGGREGATE Returns a true value if a data item contains a NULL.
ISNULL
61
AGGREGATE Returns a NULL if a certain condition is met in an expression.
NULLIF
62
AGGREGATE Rounds numbers to a specified number of decimal places.
ROUND
63
AGGREGATE Converts from a number to a character data type.
STR
64
AGGREGATE Returns the square root of positive numeric values.
SQRT
65
AGGREGATE Returns the square of a number.
SQUARE
66
STRING Returns the starting position of a specified pattern.
CHARINDEX INSTR
67
STRING Returns the length of a string.
LEN (SERVER) LENGTH (MYSQL)
68
STRING Option that matches a particular pattern.
LIKE
69
STRING Converts a string to lower case.
LOWER
70
STRING Returns the right portion of a string.
RIGHT
71
STRING Removes blanks from the right end of a string.
RTRIM
72
STRING Returns part of a string.
SUBSTRING (SERVER) SUBSTRING_INDEX(MySQL)
73
STRING Displays all output in upper case.
UPPER
74
DATE Adds to a specified part of a date.
DATEADD
75
DATE Returns the difference between two dates.
DATEDIFF
76
a procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event
TRIGGER
77
replaces the existing line ender (;) with the provided symbol. Used for END statement.
DELIMITER
78
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
79
is used if we want to execute the trigger before changes are made to the table.
AFTER
80
indicates that the trigger will be in effect for all the row changes made inside the table
FOR EACH ROW
81
trigger is used for insert operation
INSERT
82
trigger is used for update operation
UPDATE
83
trigger is used for delete operations
DELETE
84
keywords enable you to access columns in the rows affected by a trigger.
OLD and NEW
85
In an ____ trigger, only NEW.col_name can be used; there is no old row
INSERT
86
. In a ___ trigger, only OLD.col_name can be used
DELETE
87
In an ____ trigger, you can use OLD.col_name to refer to the columns of a row before it is ___
UPDATE
88
A column named with __ is read only.
OLD
89
You can refer to a column named with ___ if you have the ___ privilege for it.
NEW , SELECT
90
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
91
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
92
Executes when INSERT, UPDATE, and DELETE commands modify data in a table or view.
Data Manipulation Language (DML) trigger
93
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

DATABASE (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions