Functions

Question 1

What performs a simple operation on exactly two inputs?

Answer 1

A binary operator

5.6

Question 2

What is the syntax of a binary operator?

Answer 2

value1 operator value2

where value1 and value2 have similar data types.

(5.6)

Question 3

What can arithmetic operators be use with?

Answer 3

Numeric values

5.6

Question 4

What 5 arithmetic operators does the training companion list?

Answer 4

1. Addition ( + )
2. Subtraction ( - )
3. Multiplication ( * )
4. Division ( / )
5. Modulus: the remainder when one number is divided by another ( % )

(5.6)

Question 5

What arithmetic operator can be used to add whole or partial day (represented by a numeric value) to a date/time value?

Answer 5

The addition ( + ) operator

5.6

Question 6

What arithmetic operator can be used to subtract whole or partial days (represented by a numeric value) to a date/time value?

Answer 6

The subtraction ( - ) operator

5.6

Question 7

What is the concatenation operator and what does it do?

Answer 7

The concatenation operator ( + ) appends two string values together. If either value is null, the result is NULL.

(5.7)

Question 8

Define a function.

Answer 8

A function takes a number of inputs, also called argument, and returns some sort of output, also called a result.

(5.8)

Question 9

What does CURRENT_TIMESTAMP return?

Answer 9

The current date and time from the system. This is incredibly useful in comparing dates and lengths of time, such as in queries where you want to find all patient within a certain age range, or all new charges from last month.

(5.8)

Question 10

What is the MONTH function argument?

Answer 10

A date.

Syntax: MONTH ( Date )
EX: Select MONTH (‘01-04-2009’)

(5.8)

Question 11

What does the MONTH function return?

Answer 11

The month value from the date.

EX: Select MONTH (‘01-04-2009’)
=1

(5.8)

Question 12

What is the DATEADD function argument?

Answer 12

1. Interval (datepart)
2. Number
3. Date

EX: Select DATEADD (d, 15, ‘04 JAN 2009 23:42’) - - = 19 JAN 2009

Question 13

What do functions require?

Answer 13

Arguments of the correct data type in the correct order.

For example, the DATEADD function requires one datepart, one number, and one date or datetime, in that order. Any invalid input will cause an error, and the query will not run.

(5.10)

Question 14

Does using a function in the SELECT clause create a new column in your results?

Answer 14

Yes.

5.10

Question 15

Do functions with no arguments require parentheses?

Answer 15

No.

EX: Select CURRENT_TIMESTAMP “Current Date”

(5.11)

Question 16

True or False: In SQL, you can’t combine different types of data without first converting the data types to be compatible.

Answer 16

True (5.13)

Question 17

How is the datetime format returned?

Answer 17

With a time down to the millisecond.

5.14

Question 18

In SQL Server, what is the default length of the data type in the CAST function?

Answer 18

30.

5.13

Question 19

What does the CAST function do?

Answer 19

Changes data from one type to another.

5.13

Question 20

Is CONVERT similar to CAST?

Answer 20

The CONVERT function works similarly to the CAST function but is specific to SQL Server and has additional optional parameters.

(5.14)

Question 21

True or False;

When filtering based on dates, you will often need to use a range.

Answer 21

True

5.15

Question 22

What is the DATEDIFF syntax?

Answer 22

DATEDIFF (datepart, start date, end date)

5.16

Question 23

What are the arguments for DATEFROMPARTS?

Answer 23

1. Year - an integer expression that specifies a year.
2. Month - an integer expression that specifies a month, from 1 to 12
3. Day - an integer expression that specifies a day.

Question 24

What function can control the number of decimal places returned?

Answer 24

The STR function.

5.17

Question 25

How would you append additional text to a column and have there be a space between the result and the appended text?

Answer 25

By leaving a space
• After the single quote
• Before the appended text

Question 26

What happens if just one of the values being concatenated is NULL?

Answer 26

The concatenation returns NULL.

5.19

Question 27

How many string_value arguments does the CONCAT function require?

Answer 27

At least two and no more than 254 string_value arguments.

Question 28

What does COALESCE return?

Answer 28

The first non-null input.

5.24

Question 29

How many arguments can COALESCE use?

Answer 29

As many as needed, but they all must be the same data type.

You can utilize CAST to convert a data type as necessary.

(5.20)

Question 30

What are three SQL Server functions for data type conversion?

Answer 30

1. CAST
2. CONVERT
3. STR

(5.21)

Question 31

What are 8 SQL Server Date and Time functions?

Answer 31

1. CURRENT_TIMESTAMP
2. GETDATE( )
3. DATEADD
4. DATEFROMPARTS
5. DAY
6. MONTH
7. YEAR
8. DATEDIFF

(5.22)

Question 32

What are 4 SQL Server String functions?

Answer 32

1. CONCAT
2. LEN
3. CHARINDEX
4. SUBSTRING

(5.23)

Question 33

What are 2 SQL Server Null functions?

Answer 33

1. COALESCE
2. ISNULL

(5.24)