Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

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
How is CHARINDEX different from PATINDEX?
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.
25
What three functions return the current date time?
GetDate(), Current_TimeStamp, and GetUTCDate() *These functions are used in the SELECT clause.
26
What parameters does these functions take? GetDate(), and GetUTCDate()
None!
27
What timezones do each return?
TheGetDate and Current_TimeStamp return local datetime. GetUTCDate returns Coordinated Universal Time which is the datetime without a timezone offset.
28
What does Day(), Month() and Year() do?
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
What parameter does Day(), Month() and Year() take?
Takes a date, like a hiring date or invoice date, as a parameter. It looks like DAY(InvoiceDate) AS ColumnName
30
What parameter does DateName() take? What format is the output?
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
What are some examples of date parts?
Quarter, Month, Week, Weekdate,
32
What are parameters of DatePart() and in what order?
datepart, like month, day week, date like invoicedate or hire date
33
What parameters are required for DateFromParts()?
Year Month Day But use the numbers and separate them with commas DateFromParts(2009,11,12)
33
What is the output of a DatePart() function?
Integer
34
What does DateDiff() do?
It will return the difference between two dates.
35
What parameters does DateDiff() take?
DatePart (year, day, month) StartDate EndDate DATEDIFF(YEAR,BirtheDate, HireDate) As AgeWhenHired DATEDIFF(DAY,HireDate, '1/1/2005') AS DaysEmployed
36
What is the purpose of the DatePart parameter in the DateDiff() function?
DatePart defines the unit of measurement
37
What does DateAdd() do?
It allows you to adjust a date by adding or subtracting a specified number of units based on a defined measurement
38
What parameters does DateAdd() take?
* DatePart * Number to add or subtract by * Date
39
How do you subtract a number from a date using the function DateAdd()
You would use a negative number as a parameter.
40
What does the IsDate() funtion do?
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
What does the EOMONTH() function do?
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
What parameters does EOMONTH() take?
- 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
What function would you use to find the square root of something?
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
What function would you use to square a number?
SQUARE()
45
What parameters does the function POWER() take?
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
What does IsNull() do?
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
What parameters does IsNull() take?
- 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
What does IsNumeric() do?
It identifies whether a string is a valid number or not. Examples: - IsNumeric(PostalCode) - ISNUMERIC(column_name) - ISNUMERIC('12345')
49
What's does a 1 output mean for an IsNumeric() function?
It means the string is a valid number.
50
Why might you use the IsNumeric() expression?
To identify and filter out non-numeric values in a dataset.
51
What is the purpose of the IIF() function?
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
What is a boolean expression?
A boolean expression is an expression that evaluates to a boolean value, which can be either TRUE or FALSE.
53
What parameters does IIF() take?
IIF(boolean_expression, true_value, false_value) IIF(1 = 1, 'True', 'False')
54
What a some different types of inputs that you can use as conditions in the boolean_expression parameter?
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 2. Logical Operators Examples of logical operators include AND, OR, and NOT. IIIF(age >= 18 AND age < 65, 'Adult', 'Not Adult') 3. IS NULL / IS NOT NULL IIF(name IS NULL, 'Name is null', 'Name is not null') 4. 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') 5. 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 5. 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 6. 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. 7. Boolean Constants You can use boolean constants TRUE and FALSE directly as conditions. IIF(is_active = TRUE, 'Active', 'Inactive')
55
What does the CAST() function do?
Function is used to convert an expression from one datatype to another.
56
What parameters does CAST() take?
- 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
What parameters does CONVERT() take?
- 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
What are typical use cases for CONVERT()?
Convert a String to a Date Convert a Float to a String Convert a DateTime to a String in a Specific Format:
59
What's the difference between CAST and CONVERT?
CAST() does not provide options to format the output.
60
What's a literal?
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
What happens if a subquery returns multiple values or rows in a Left() function?
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

SQL (12 decks)

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