Sql Fundamentals Chapter 4

Front 1

T/F SQL functions are broadly divided into those that calculate and return a value for every row in a data set and those that return a single aggregated value for all rows.

Back 1

True

Front 2

What is a function?

Back 2

A function is a program written to optionally accept input parameters, perform an operation, or return a single value.

Front 3

T/F A function returns only one value per execution.

Back 3

True

Front 4

What three components for the basis of defining a function?

Back 4

1. Input parameter list. It specifies zero or more arguments that may be passed to a function as input for processing.

2. Data type of its resultant value. Upon execution, only one value is returned by the function.

3. The third encapsulates the details of the processing performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return value.

Front 5

A _______ is often described as a black box that takes an input, performs a calculation, and returns a value.

Back 5

function

Front 6

T/F Functions may be nested within other functions.

Back 6

True

Front 7

T/F Functions can only operate on certain data types.

Back 7

False. They can operate on any available data types, the most popular being character, date, and numeric data. These operands may be columns or expressions.

Front 8

Functions that operate on character data are broadly classified as case conversion and _____ _____ functions.

Back 8

Character manipulation

Front 9

_____, _____, and _____ are the case conversion functions that convert a given character column, literal, or expression into lowercase, uppercase, or initial case.

Back 9

LOWER, UPPER, and INITCAP

Front 10

The character manipulation functions include which 8 functions?

Back 10

1. LENGTH

2. CONCAT

3. SUBSTR

4. INSTR

5. LPAD

6. RPAD

7. TRIM

8. REPLACE

Front 11

What function uses a character string as an input parameter and returns a numeric value representing the number of characters present in that string?

Back 11

LENGTH(string)

Front 12

What does the CONCAT(string 1, string 2) function do?

Back 12

It takes two strings and concatenates or joins them in the same way that the concatenation operator || does.

Front 13

What does the SUBSTR function do?

Back 13

It accepts three parameters and returns a string consisting of the number of characters extracted from the source string, beginning at the specified start position.

Front 14

What is the syntax for the SUBSTR function?

Back 14

SUBSTR(string, start position, number of characters)

Front 15

What does the INSTR function do?

Back 15

It returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins.

Front 16

What do the LPAD and RPAD functions do?

Back 16

THE LPAD and RPAD functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

Front 17

What does the TRIM function do?

Back 17

The TRIM function literally trims off leading or trailing (or both) character strings from a given s

Front 18

What does the REPLACE function do?

Back 18

It locates the search item in a given string and replaces it with the replacement item, returning a string with replaced values.

Front 19

What does the ROUND function do?

Back 19

It facilitates rounding off a number to the lowest or highest value given a decimal precision format.

Front 20

What does the TRUNC function do?

Back 20

It drops off or truncates the number given a decimal precision value.

Front 21

What does the MOD function do?

Back 21

It returns the remainder of a division operation

Front 22

What does the MONTHS_BETWEEN function do?

Back 22

It returns the number of months between two dates.

Front 23

What does the ADD_MONTHS function do?

Back 23

It returns the date resulting from adding a specified number of months to a date.

Front 24

What does the LAST_DAY function do?

Back 24

It returns the last day of the month that the specified date falls into

Front 25

What does the NEXT_DAY function do?

Back 25

It returns the date on which the next specified day of the week falls after the given date.

Front 26

What does the SYSDATE function do?

Back 26

It takes no parameters and returns a date value that represents the current server date and time.

Front 27

T/F If a query selects 10 rows, the function is executed 10 times, once per row with the values from that row as input to the function.

Back 27

True

Front 28

What are multiple-row functions?

Back 28

Functions that operate on more than one at a time.

Front 29

What are some typical uses of multiple-row functions?

Back 29

- Calculating the sum or average of the numeric column values.

- counting the total number of records in sets.

Front 30

The character case conversion functions serve two important purposes. What are they?

Back 30

1. To modify the appearance of a character data item for display purposes

2. to render them consistent for comparison operations.

Front 31

What does the LOWER function do?

Back 31

- converts a string of characters into their lowercase.

- Does not add extra characters or shorten length.

- Uppercase characters are converted into their lowercase equivalents. Numeric, punctuation, or special characters are ignored.

Front 32

The LOWER function can take only ____ parameter(s).

Back 32

one

Front 33

What is the syntax for the LOWER function?

Back 33

LOWER(s).

Front 34

What does the following query return?

select lower (100) from dual

Back 34

100

Front 35

What does the following query return?

select lower ('The SUM'||'100+100'||'=200') from dual

Back 35

"The sum 100 + 100 = 200

Front 36

What does the UPPER function do?

Back 36

- converts a string of characters into their uppercase equivalents.

- It does not add extra characters or shorten the length of the initial string.

- All lowercase characters are converted into their uppercase equivalents.

- Numeric, punctuation, or special characters are ignored.

Front 37

T/F The UPPER function only take one parameter

Back 37

True.

Front 38

What is the syntax for the UPPER function?

Back 38

UPPER(s)

Front 39

What does the INITCAP function do?

Back 39

- It converts a string of characters into capitalized case.

- It is often used for data presentation purposes.

- The first letters of each word in the string are converted to their uppercase equivalents, while the remaining letters of each word are converted to their lowercase equivalents.

Front 40

T/F The INITCAP function can take more than one parameter.

Back 40

False. It can only take one.

Front 41

What is the syntax for INITCAP function?

Back 41

INITCAP(s)

Front 42

Assuming SYSDATE is 17-DEC-07, what would the following query return?

select initcap (SYSDATE) from dual

Back 42

17-Dec-07

Front 43

What function joins two character literals, columns or expressions to yield one larger character expression?

Back 43

The CONCAT function

Front 44

How many parameters does the CONCAT function take?

Back 44

Two

Front 45

What is the syntax for the CONCAT function?

Back 45

CONCAT(s1, s2) where s1 and s2 represent string literals, character column values, or expressions resulting in character values.

Front 46

What is the result of the following query:

select concat (1+2.14, 'approximates pi') from dual

Back 46

3.14 approximates pi

Front 47

What does the LENGTH function do?

Back 47

It returns the number of characters that constitute a character string. This includes character literals, columns, or expressions. Numeric and date literals are automatically cast as characters when they occur as parameters to the LENGTH function.

Front 48

T/F Blank spaces, tabs, and special characters are not counted by the LENGTH function.

Back 48

False. They are all counted by LENGTH function.

Front 49

How many parameters does the LENGTH function take?

Back 49

only one

Front 50

What is the syntax for the LENGTH function?

Back 50

LENGTH(s)

Front 51

What is the result from the following query:

select length(1+2.14 ||'approximates pi') from dual

Back 51

- 20

- The numeric expression is evaluated to return the number 3.14. This number is cast as the character string "3.14" which is then concatenated to the character literal "approximates pi". The resultant character string contains 20 characters.

Front 52

What is the result of the following query is the SYSDATE is 17-DEC-07?

select length (SYSDATE) from dual

Back 52

- 9

- The query first evaluates SYSDATE function, which returns the current system date.

- This value is automatically converted to a character string whose length is then determined.

Front 53

What do the LPAD and RPAD functions do?

Back 53

They return a string padded with a specified number of characters to the left or right of the source string.

- Numeric and date literals are implicitly cast as characters when they occur as parameters to the LPAD and RPAD functions.

Front 54

How many parameters do the LPAD and RPAD functions take?

Back 54

Three

Front 55

What is the syntax for LPAD and RPAD?

Back 55

LPAD(s,n,p) and RPAD(s,n,p)

- s represents the source string

- n represents the final length of the string returned

- p specifies the character string to be used as padding.

Front 56

If LPAD is used, the padding characters ___ are added to the left of the ______ _____ s until it reaches length ____.

Back 56

p, source string, n.

Front 57

If RPAD is used, the padding characters p are added to the ______ of the source string s until it reaches length n.

Back 57

right

Front 58

T/F If the parameter n is smaller than or equal to the length of the source string s, then no padding occurs and only the first n characters of s are returned.

Back 58

True.

Front 59

What are the results of the following query?

select lpad (1000+200.55, 14, '*') from dual

Back 59

*******1200.55

Front 60

What are the results of the following query?

select rpad (1000+200.55, 14, '*') from dual

Back 60

1200.55*******

Front 61

What are the results of the following query if SYSDATE is 17-DEC-07?

select lpad(SYSDATE, 14, '$#') from dual

Back 61

$#$#$17-DEC-07

- The date is converted into a string, and the padding string is systematically applied to reach the target length.

Front 62

What is the result of the following query if SYSDATE is 17-DEC-07?

select rpad(SYSDATE, 4, '$#') from dual

Back 62

17-D

- The RPAD function in the query has a target length of 4 characters, but the SYSDATE function alone returns a 9 character value. Therefore no padding occurs and the first four characters of the date are returned.

Front 63

What does the TRIM function do?

Back 63

It removes characters from the beginning or end of character literals, columns or expressions to yield one potentially shorter character item.

Front 64

T/F Numeric and data literals are automatically cast as characters when they occur as parameters to the TRIM function.

Back 64

True.

Front 65

T/F Numeric or date expressions are evaluated last after being converted to strings ready to be trimmed.

Back 65

False. They are evaluated first before being converted to strings ready to be trimmed.

Front 66

The TRIM function takes a parameter made up of an _____ and a _____ component

Back 66

optional, mandatory

Front 67

What is the syntax for the TRIM function?

Back 67

TRIM([trailing|leading|both] trimstring from s)

- The string to be trimmed (s) is mandatory.

Front 68

T/F TRIM(s) removes spaces from both sides of the input string.

Back 68

True.

Front 69

What does TRIM(trailing trimstring from s) do?

Back 69

It removes all occurrences of trimstring from the end of the string s if it is present.

Front 70

What does TRIM(leading trimstring from s) do?

Back 70

It removes all occurrences of trimstring from the beginning of the string s if it is present.

Front 71

What does TRIM(both trimstring from s) do?

Back 71

It removes all occurrences of trimstring from the beginning and end of the string s if it is present.