Brainscape's Knowledge GenomeTM

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


See full index

SQL > Scalar Functions > Flashcards

Scalar Functions Flashcards

1
Q

What is a function in a SQL statement?

A

A function accepts multiple input values like strings, numbers, dates, performs an action on them, and then returns a single value as a result.

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

What does CONCAT() do?

A

It takes multiple string values separated by commas and merges them together on a single line.

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

What does the Left() and Right() function do?

A

These functions list/extract characters from the string starting on the left or right side.

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

What information goes inside the parenthesis of the Left() and Right() function?

A
  1. Expression
  2. Length

LEFT(‘string’, number_of_characters)

EX: LEFT (Hello, World!’, 5)

LEFT(column_name, 5)

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

What is an identifier in SQL?

A

A name used to identify a database object like table, column, index, view, schema, database, schema names, alias names, view names, constraint names.

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

What does the function SUBSTRING() do?

A

It extracts a part of a string.

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

How does SUBSTRING() work?

A

You input a specific starting point and number of characters to extract from a string.

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

What is the SUBSTRING syntax?

A

SUBSTRING(string, starting position, length)

  • String is the source we’re extracting from
  • Start position is where the extraction would begin
  • The number of characters to extract from the starting position.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Explain this section of the function LEN(product_code) - 4
SUBSTRING(product_code, 5, LEN(product_code) - 4)

A

LEN(product_code) - 4: The length of the substring.

LEN(product_code): This function returns the length of the product_code string. This subtracts 4 from the total length of the product_code string, determining how many characters to extract starting from the 5th character.

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

What are the three parameters the SUBSTRING() takes?

A

String, starting point, length to extract

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

What does the function LEN() do?

A

Displays the character count in a string. Spaces count as a character.

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

What does Ltrim() and Rtrim() do?

A

LTrim and RTrim
are functions
used to trim
leading or
trailing spaces
off of a
character
expression

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

How do you remove trailing spaces on both ends of a word?

A

LTRIM(RTRIM(‘string’))

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

What is the syntax for Ltrim() and Rtrim()?

A

RTRIM(‘string’)

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

What does the UPPER() and LOWER() function do?

A

Convert an expression to upper or lower case

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

What is the syntax of the UPPER() and LOWER() function?

A

It’s UPPER(‘string’) or LOWER(‘string’)

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

What does this expression do? UPPER(LEFT(column_name, 1))

A

IT extracts the first character of each word, UPPER() converts it to uppercase

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

What expression reverses the order of the characters in a string?

A

REVERSE(‘string’) or REVERSE(column_name)

Column named NAME

REVERSE(Name)

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

What parameters does the REVERSE() function use?

A
  • Expression
  • String of characters we’re searching for
  • Replacement
    string
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Which function do you use if you want to search for a specific pattern (substring) within a string and return the starting position of the first occurrence of that pattern?

A

PATINDEX(‘%pattern%’, expression)

ex: PATINDEX(‘%world%’, ‘Hello world!’)

NOTE: It does not return the position right before, but the position of the first character.wil

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

What is the syntax of the REVERSE() function?

A

REPLACE(original_string, string_to_replace, replacement_string)

or

REPLACE (string or expression like a column we’re manipulating, ‘the part of the string we’re replacing’, ‘what we’re replacing it with’)

REPLACE(‘Hello World’, ‘World’, ‘Universe’)

‘World’ gets replaced with ‘Universe’

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

What does PATINDEX() share with the LIKE keyword?

A

It takes wildcards.

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

What happens if the string isn’t found with PATINDEX()?

A

The result is zero.

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

What parameters does the CHARINDEX() function take?

A

CHARINDEX(‘substring’, string or expression like the column, starting position (optional)

ex.

CHARINDEX(‘in’, Name, 8)

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

How is CHARINDEX different from PATINDEX?

A

CHARINDEX is useful for finding later instances of a character
pattern in an expression. You can skip over the first couple if you want to by identifying a starting point to start the function in the script.

CHARINDEX also doesn’t take wildcards.

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

What three functions return the current date time?

A

GetDate(), Current_TimeStamp,
and GetUTCDate()

*These functions are used in the SELECT clause.

26
Q

What parameters does these functions take? GetDate(),
and GetUTCDate()

A

None!

27
Q

What timezones do each return?

A

TheGetDate and
Current_TimeStamp return local datetime. GetUTCDate returns Coordinated Universal Time which is the datetime without a timezone offset.

28
Q

What does Day(), Month() and Year() do?

A

It returns the datepart as an integer. An integer is a number.

For example, if you ask for Year(InvoiceDate) you would get 2009 or something.

An example of datepart is day, month, year.

29
Q

What parameter does Day(), Month() and Year() take?

A

Takes a date, like a hiring date or invoice date, as a
parameter.

It looks like DAY(InvoiceDate) AS ColumnName

30
Q

What parameter does DateName() take? What format is the output?

A

It takes datepart and date. A date part is like quarter, week, weekday, and month.

ex: DATENAME(DayofYear,HireDate)

Whenever possible this function will spell out the name like Monday, or April.

The format is in string form, even if sometimes it shows numbers like for Quarter.

31
Q

What are some examples of date parts?

A

Quarter, Month, Week, Weekdate,

32
Q

What are parameters of
DatePart() and in what order?

A

datepart, like month, day week,
date like invoicedate or hire date

33
Q

What parameters are required for DateFromParts()?

A

Year
Month
Day

But use the numbers and separate them with commas

DateFromParts(2009,11,12)

33
Q

What is the output of a DatePart() function?

A

Integer

34
Q

What does DateDiff() do?

A

It will return the difference between two dates.

35
Q

What parameters does DateDiff() take?

A

DatePart (year, day, month)
StartDate
EndDate

DATEDIFF(YEAR,BirtheDate, HireDate) As AgeWhenHired

DATEDIFF(DAY,HireDate, ‘1/1/2005’) AS DaysEmployed

36
Q

What is the purpose of the DatePart parameter in the DateDiff() function?

A

DatePart defines the unit of measurement

37
Q

What does DateAdd() do?

A

It allows you to adjust a date by adding or subtracting a specified number of units based on a defined measurement

38
Q

What parameters does DateAdd() take?

A
  • DatePart
  • Number to add or subtract by
  • Date
39
Q

How do you subtract a number from a date using the function DateAdd()

A

You would use a negative number as a parameter.

40
Q

What does the IsDate() funtion do?

A

It checks to see if the input is a valid date or not.

For example.

IsDate(LastName) wouldn’t be a valid date but

IsDate(HireDate) would be.

It will return a 1 if valid. It will return a 0 if not valid.

41
Q

What does the EOMONTH() function do?

A

It returns the last day of the month for a given date. An example of a given date input would be something like HireDate.

EOMONTH(HireDate)

42
Q

What parameters does EOMONTH() take?

A
  • The Start Date. For example a HireDate Input or ‘2024-05-27’
  • Months to add or subtract (optional) - must be written as an integer

Example EOMONTH(HireDate,6) - this would tell you the last day of the month six months from the HireDate as the start date.

43
Q

What function would you use to find the square root of something?

A

SQRT()

SQRT(16) or SQRT(column_name) to return the square root of each value in that column.

Must be a positive number or you’ll likely get an error.

44
Q

What function would you use to square a number?

A

SQUARE()

45
Q

What parameters does the function POWER() take?

A

Base: The number that is to be raised to a power. It can also be entered as a column base.

exponent: The power to which the base number is raised.

POWER(2, 3)
2^3=8

46
Q

What does IsNull() do?

A

It will check for null values in an expression and replace nulls with a replacement expression for example N/A

,ISNULL(Column, ‘N/A’) As NameYouDecideForColumn

47
Q

What parameters does IsNull() take?

A
  • The value or column to be checked for NULL.
  • replacement: The value that will be returned if the expression is NULL.

ISNULL(column_name, ‘Default Value’)

ISNULL(column_name, 0) - this would return null values as 0

48
Q

What does IsNumeric() do?

A

It identifies whether a string is a valid number or not.

Examples:
- IsNumeric(PostalCode)
- ISNUMERIC(column_name)
- ISNUMERIC(‘12345’)

49
Q

What’s does a 1 output mean for an IsNumeric() function?

A

It means the string is a valid number.

50
Q

Why might you use the IsNumeric() expression?

A

To identify and filter out non-numeric values in a dataset.

51
Q

What is the purpose of the IIF() function?

A

It evaluates an expression and returns one value if the expression is true and another value if the expression is false.

Ex: SELECT IIF(salary > 50000, ‘High Salary’, ‘Low Salary’)

Categorizes each employee’s salary as ‘High Salary’ or ‘Low Salary’ based on whether the salary is greater than 50000.

52
Q

What is a boolean expression?

A

A boolean expression is an expression that evaluates to a boolean value, which can be either TRUE or FALSE.

53
Q

What parameters does IIF() take?

A

IIF(boolean_expression, true_value, false_value)

IIF(1 = 1, ‘True’, ‘False’)

54
Q

What a some different types of inputs that you can use as conditions in the boolean_expression parameter?

A
  1. Comparison Operators.You can use comparison operators to compare two values and evaluate if the comparison is true or false.
    Examples of comparison operators include =, <>, <, >, <=, and >=.

SELECT IIF(salary > 50000, ‘High Salary’, ‘Low Salary’) AS salary_category

  1. Logical Operators
    Examples of logical operators include AND, OR, and NOT.

IIIF(age >= 18 AND age < 65, ‘Adult’, ‘Not Adult’)

  1. IS NULL / IS NOT NULL

IIF(name IS NULL, ‘Name is null’, ‘Name is not null’)

  1. Functions. You can use functions that return a boolean value as conditions. For example, you might use functions like LEN() to check the length of a string or ISNUMERIC() to check if a value is numeric.

IIF(LEN(name) > 0, ‘Name is not empty’, ‘Name is empty’)

  1. Subqueries You can use subqueries to generate boolean values as conditions.
    IIF((SELECT COUNT(*) FROM orders WHERE customer_id = 123) > 0, ‘Has orders’, ‘No orders’)

This subquery counts the number of orders for the customer with customer_id = 123

  1. Case Expressions

IIF(CASE WHEN salary > 50000 THEN 1 ELSE 0 END = 1, ‘High Salary’, ‘Low Salary’)

CASE WHEN salary > 50000 THEN 1 ELSE 0 END: This CASE statement checks if the salary is greater than 50000. If it is, it returns 1; otherwise, it returns 0

  1. Nested IIF statements

SELECT employee_id, salary,
IIF(salary > 70000, ‘Very High Salary’,
IIF(salary > 50000, ‘High Salary’, ‘Low Salary’)) AS salary_category
FROM employees;

If salary > 70000 is true, it returns ‘Very High Salary’.
If salary > 70000 is false, it proceeds to the nested IIF function.

  1. Boolean Constants You can use boolean constants TRUE and FALSE directly as conditions.
    IIF(is_active = TRUE, ‘Active’, ‘Inactive’)
55
Q

What does the CAST() function do?

A

Function is used to convert an expression from one datatype to another.

56
Q

What parameters does CAST() take?

A
  • Expression: The value or column to be converted.
  • Datatype to convert to: The target datatype you want to convert the expression to.

CAST(expression AS datatype)
SELECT
CAST(‘123’ AS INT);

Expression: ‘123’ (a string)

SELECT CAST(123.45 AS VARCHAR(10));
Expression: 123.45 (a float)
Datatype to convert to: VARCHAR(10)

SELECT CAST(GETDATE() AS VARCHAR(30));

Expression: GETDATE() (current date and time)
Datatype to convert to: VARCHAR(30)

57
Q

What parameters does CONVERT() take?

A
  • Datatype: The target datatype you want to convert the expression to.
  • Expression: The value or column to be converted.
  • Style (optional): A code that specifies the format for the conversion, primarily used with date and time datatypes.

CONVERT(datatype, expression, [style])
SELECT CONVERT(DATE, ‘2024-05-28’, 120);
SELECT CONVERT(VARCHAR(10), 123.45);

58
Q

What are typical use cases for CONVERT()?

A

Convert a String to a Date
Convert a Float to a String
Convert a DateTime to a String in a Specific Format:

59
Q

What’s the difference between CAST and CONVERT?

A

CAST() does not provide options to format the output.

60
Q

What’s a literal?

A

A literal is a fixed value that is explicitly stated within a query. It is a constant and not a reference to a database object like a column or table. Literals can represent various types of data, including strings, numbers, dates, and other data types.

text values, integer or decimal numbers, ‘2024-05-28’ Represent boolean values (true/false).

61
Q

What happens if a subquery returns multiple values or rows in a Left() function?

A

An error will occur. If the subquery returns multiple rows, the LEFT() or RIGHT() function may not know which specific value to operate on.

62
Q
A

SQL (12 decks)

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