Functions

Question 1

What is a function?

Answer 1

A function is an SQL Server object with a specified name and optional parameters that operates as a single logical unit.

Question 2

What do you need to include to call a function?

Answer 2

Functions are called by providing the name of the function along with a set of parenthesis.

Example: function (arg1, arg2, arg3)

Question 3

SELECT DATE_FORMAT(“2017-06-15”, “%Y”)

In the query above, what does the “DATE_FORMAT” represent?

Answer 3

The function

Question 4

SELECT DATE_FORMAT(“2017-06-15”, “%Y”)

In the query above, what are the values inside the parenthesis called?

Answer 4

Arguments

Question 5

DATE

Answer 5

The DATE() function extracts the date part from a datetime expression.

SELECT DATE(“2017-06-15”);

Question 6

TIME

Answer 6

The TIME() function extracts the time part from a given time/datetime.

SELECT TIME(“19:30:10”);

Question 7

DATETIME

Answer 7

The DATETIME type is used for values that contain both date and time parts.

Question 8

TIMESTAMP

Answer 8

The TIMESTAMP data type is used for values that contain both date and time parts. 

SELECT TIMESTAMP(“2017-07-23”, “13:10:11”);

Question 9

YEAR

Answer 9

The YEAR() function returns the year part for a given date (a number from 1000 to 9999).

SELECT YEAR(“2017-06-15”);

Question 10

What is the format used for date values?

Answer 10

YYYY-MM-DD

Question 11

What is the format used for time values?

Answer 11

hh:mm:ss

Question 12

SELECT DISTINCT

Answer 12

The SELECT DISTINCT statement is used to return only distinct (different) values. It will return only one value for each distinct result.

Question 13

COUNT (Aggregate Function)

Answer 13

Counts how many rows are in a particular column.

Question 14

SUM (Aggregate Function)

Answer 14

Adds together all the values in a particular column.

Question 15

MIN (Aggregate Function)

Answer 15

Returns the lowest value in a particular column.

Question 16

MAX (Aggregate Function)

Answer 16

Returns the highest value in a particular column.

Question 17

AVG (Aggregate Function)

Answer 17

Calculates the average of a group of selected values.

Question 18

What are the 5 Aggregate Functions?

Answer 18

Count, Sum, Min, Max, Avg

Question 19

What is a string?

Answer 19

A string is a collection of characters that you cannot use in arithmetic calculation. The characters you store in these data types can be uppercase or lowercase letters, numerals, and special characters such as the “at” sign (@), ampersand (&), and exclamation point (!) in any combination. It could be a name, an email address, a telephone number.

Question 20

What case conversion function can you use to convert text to uppercase?

Answer 20

UPPER, UCASE

Question 21

What case conversion function can you use to convert text to uppercase?

Answer 21

LOWER, LCASE

Question 22

REPLACE

Answer 22

Is used to replace a string or part of a string with another string.

Example:
You want to change ‘USA’ for ‘United States’ in the country column of a database.

SELECT REPLACE( country, ‘USA’, ‘United States’ )
FROM customers

Question 23

FORMAT

Answer 23

Used to format date/time values and number values.

FORMAT(value, format)

For number values - FORMAT(value, # of decimals)
Example: FORMAT(2034.567, 2)

For dates - FORMAT(value, date format preferred)
Example: FORMAT(getdate(), ‘dd-MM-yy’)

Question 24

ROUND

Answer 24

Rounds a number to a specified number of decimal places.

ROUND(value, # of decimals)

Example:
ROUND(2034.567, 2)

It will return 2034.57, the number rounded to only 2 decimals.

Question 25

TRIM

Answer 25

Removes the space character OR other specified characters from the start or end of a string.

Question 26

SUBSTR

Answer 26

Extracts a substring from a string (starting at any position)

SUBSTR(string, start, length)

Question 27

What is a leading space?

Answer 27

A space in front of the value input in the field

Question 28

What is a trailing space?

Answer 28

A space left after the value input in the field

Question 29

What function should you use to remove a leading space from a value?

Answer 29

ltrim

Question 30

What function should you use to remove a trailing space from a value?

Answer 30

rtrim

Question 31

CONCAT

Answer 31

Adds two or more strings together.

CONCAT(string1, string2, …., string_n) [Within the paranthesis you include all the string you want to join together]

Question 32

CONCAT_WS

Answer 32

The CONCAT_WS() function adds two or more strings together with a separator.

CONCAT_WS(separator, string1, string2, …., string_n)

Here, the first argument is whatever you want to use as a separator between each string, followed by all the strings you want to join together.