Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DATABASE > UNIT 4-6 > Flashcards

UNIT 4-6 Flashcards

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
Q

which returns a NULL if expression1 = expression2. If the expressions are not equal, then expression1 is returned. BOTH LANGUAGES

A

NULLIF(expression1, expression2)

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

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.

A

TOP

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

option that can be used with the TOP function.

A

WITH TIES

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

returns a certain percentage of rows that fall at the top of a specified range.

A

PERCENT

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

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

A

BOTTOM PERCENT

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

used in the SELECT statement to constrain the number of rows in a result set.

A

LIMIT

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

specifies the offset of the first row to return.

A

OFFSET

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

specifies maximum number of rows to return.

A

COUNT

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

The LIMIT clause often used with _____ clause

A

ORDER BY

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

a specialized conversion function that always converts from a number (for example, float or numeric) to a character data type

A

STR(float_expression, character_length, number_of_decimal_places)

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

function omits rows in the result set that contain duplicate data in the selected columns.

A

DISTINCT

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

___ 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

A

FORMAT(X,D)

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

function is also used to explicitly convert to a given data type. But, the ___ function has additional limited formatting capabilities.

A

CONVERT(desired_datatype[(length)], original_expression [, style])

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

convert to date, time, datetime, char

A

mysql> select convert(‘2014-02-28’, date);
result: ‘2014-02-28’

39
Q

____ function returns part of a string.

A

SUBSTRING(stringexpression, startposition, length)

40
Q

The __ function starts from the __ of the stringexpression or column and returns n characters, and

A

LEFT

41
Q

___ function starts from the right of the stringexpression or column and returns n characters.

A

RIGHT

42
Q

removes blanks from the end (right) of a string

A

RTRIM

43
Q

function returns the starting position of a specified pattern

A

CHARINDEX

44
Q

returns the substring from the given string before a specified number of occurrences of a delimiter.

A

SUBSTRING_INDEX(str, delim, count)

45
Q

To produce all the fields in the result set (output) in uppercase or in lowercase, you can use the ___ or ____ functions. SQL SERVER

A

UPPER OR LOWER

46
Q

function returns the length (number of characters) of a desired string excluding trailing blanks.

A

LEN(names) AS [Length of Names]

47
Q

OTHER FUNCTIONS Returns a specified number of records from the top of a result set.

A

TOP

48
Q

OTHER FUNCTIONS Omits rows that contain duplicate data.

A

DISTINCT

49
Q

OTHER FUNCTIONS Return a certain percentage of records that fall at the top of a range specified.

A

PERCENT

50
Q

CONVERSION FUNCTIONS Changes a data type of a column in a result set.

A

CAST

51
Q

CONVERSION FUNCTIONS Explicitly converts to a given data type in a result set.

A

CONVERT

52
Q

AGGREGATE Returns the specified part of the date requested.

A

DATEPART

53
Q

AGGREGATE Extracts a day from a date.

A

DAY

54
Q

AGGREGATE Returns the current system date and time.

A

GETDATE

55
Q

AGGREGATE Extracts the month from a date.

A

MONTH

56
Q

AGGREGATE Changes the format in which SQL Server reads in dates.

A

SET DATEFORMAT

57
Q

AGGREGATE Extracts the year from a date.

A

YEAR

58
Q

AGGREGATE Returns the next larger integer value.

A

CEILING

59
Q

AGGREGATE Returns the next lower integer value.

A

FLOOR

60
Q

AGGREGATE Returns a true value if a data item contains a NULL.

A

ISNULL

61
Q

AGGREGATE Returns a NULL if a certain condition is met in an expression.

A

NULLIF

62
Q

AGGREGATE Rounds numbers to a specified number of decimal places.

A

ROUND

63
Q

AGGREGATE Converts from a number to a character data type.

A

STR

64
Q

AGGREGATE Returns the square root of positive numeric values.

A

SQRT

65
Q

AGGREGATE Returns the square of a number.

A

SQUARE

66
Q

STRING Returns the starting position of a specified pattern.

A

CHARINDEX
INSTR

67
Q

STRING Returns the length of a string.

A

LEN (SERVER) LENGTH (MYSQL)

68
Q

STRING Option that matches a particular pattern.

A

LIKE

69
Q

STRING Converts a string to lower case.

A

LOWER

70
Q

STRING Returns the right portion of a string.

A

RIGHT

71
Q

STRING Removes blanks from the right end of a string.

A

RTRIM

72
Q

STRING Returns part of a string.

A

SUBSTRING (SERVER) SUBSTRING_INDEX(MySQL)

73
Q

STRING Displays all output in upper case.

A

UPPER

74
Q

DATE Adds to a specified part of a date.

A

DATEADD

75
Q

DATE Returns the difference between two dates.

A

DATEDIFF

76
Q

a procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event

A

TRIGGER

77
Q

replaces the existing line ender (;) with the provided symbol. Used for END statement.

A

DELIMITER

78
Q

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.

A

BEFORE

79
Q

is used if we want to execute the trigger before changes are made to the table.

A

AFTER

80
Q

indicates that the trigger will be in effect for all the row changes made inside the table

A

FOR EACH ROW

81
Q

trigger is used for insert operation

A

INSERT

82
Q

trigger is used for update operation

A

UPDATE

83
Q

trigger is used for delete operations

A

DELETE

84
Q

keywords enable you to access columns in the rows affected by a trigger.

A

OLD and NEW

85
Q

In an ____ trigger, only NEW.col_name can be used; there is no old row

A

INSERT

86
Q

. In a ___ trigger, only OLD.col_name can be used

A

DELETE

87
Q

In an ____ trigger, you can use OLD.col_name to refer to the columns of a row before it is ___

A

UPDATE

88
Q

A column named with __ is read only.

A

OLD

89
Q

You can refer to a column named with ___ if you have the ___ privilege for it.

A

NEW , SELECT

90
Q

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.

A

BEFORE

91
Q

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.

A

BEFORE , NEW , AUTO_INCREMENT

92
Q

Executes when INSERT, UPDATE, and DELETE commands modify data in a table or view.

A

Data Manipulation Language (DML) trigger

93
Q

Executes in response to a DDL statement that is often used to make database schema changes. Examples include the CREATE, ALTER, and DROP statements.

A

Data Definition Language (DDL) trigger

DATABASE (5 decks)

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