Entry-level Data Analysis Interview Preparation Questions with Answers
❤3
What are SQL functions?
SQL functions are predefined operations that can be invoked with specific arguments to perform computations, manipulate data, or return specific results.
These functions can be categorized into several types based on their purpose:
1. Scalar Functions: These functions operate on a single value and return a single value. eg: LEN(), CAST() etc.
2. Aggregate Functions: These functions operate on a set of values and return a single value summarizing that set. eg: SUM(), AVG() etc.
3. Analytic Functions (Window Functions): These functions perform calculations across a set of rows related to the current row. eg: ROW_NUMBER() etc.
4. Table-Valued Functions: Unlike scalar functions, table-valued functions return a table as a result. They can accept parameters and perform complex processing to generate the output table.
AGGREGATE FUNCTIONS:
An aggregate function is a function that performs a calculation on a set of values, and returns a single value. We often use aggregate functions when you have a lot of data and you want to summarize it in some way, like finding totals, averages, or extremes.
For example, imagine you have a bunch of numbers and you want to find the total, average, or maximum number. Aggregate functions help you do that.
We can also use these functions with a GROUP BY clause, which is like organizing your data into groups based on certain criteria. So, you can get summaries for each group separately.
Some examples for aggregate functions are given below:
1. MIN() - returns the smallest value within the selected column.
2. MAX() - returns the largest value within the selected column.
3. COUNT() - returns the number of rows in a set.
4. SUM() - returns the total sum of a numerical column.
5. AVG() - returns the average value of a numerical column.
📌Other than COUNT(), aggregate functions ignore null values.
LIKE OPERATOR:
"LIKE" is an operator that helps you search for patterns within text data. Like finding all the words that start with a certain letter or contain a specific sequence of characters.
What are wildcards?
Wildcards are symbols used in conjunction with the "LIKE" operator in SQL to represent one or more characters in a search pattern. They act as placeholders for unknown or variable characters, allowing you to search for patterns rather than exact matches.
Types:
1. % (Percent Sign): This wildcard represents zero, one, or multiple characters. It can be used to match any sequence of characters.
2. _(Underscore): This wildcard represents a single character. It matches any single character in that position of the string.
SQL functions are predefined operations that can be invoked with specific arguments to perform computations, manipulate data, or return specific results.
These functions can be categorized into several types based on their purpose:
1. Scalar Functions: These functions operate on a single value and return a single value. eg: LEN(), CAST() etc.
2. Aggregate Functions: These functions operate on a set of values and return a single value summarizing that set. eg: SUM(), AVG() etc.
3. Analytic Functions (Window Functions): These functions perform calculations across a set of rows related to the current row. eg: ROW_NUMBER() etc.
4. Table-Valued Functions: Unlike scalar functions, table-valued functions return a table as a result. They can accept parameters and perform complex processing to generate the output table.
AGGREGATE FUNCTIONS:
An aggregate function is a function that performs a calculation on a set of values, and returns a single value. We often use aggregate functions when you have a lot of data and you want to summarize it in some way, like finding totals, averages, or extremes.
For example, imagine you have a bunch of numbers and you want to find the total, average, or maximum number. Aggregate functions help you do that.
We can also use these functions with a GROUP BY clause, which is like organizing your data into groups based on certain criteria. So, you can get summaries for each group separately.
Some examples for aggregate functions are given below:
1. MIN() - returns the smallest value within the selected column.
2. MAX() - returns the largest value within the selected column.
3. COUNT() - returns the number of rows in a set.
4. SUM() - returns the total sum of a numerical column.
5. AVG() - returns the average value of a numerical column.
📌Other than COUNT(), aggregate functions ignore null values.
LIKE OPERATOR:
"LIKE" is an operator that helps you search for patterns within text data. Like finding all the words that start with a certain letter or contain a specific sequence of characters.
What are wildcards?
Wildcards are symbols used in conjunction with the "LIKE" operator in SQL to represent one or more characters in a search pattern. They act as placeholders for unknown or variable characters, allowing you to search for patterns rather than exact matches.
Types:
1. % (Percent Sign): This wildcard represents zero, one, or multiple characters. It can be used to match any sequence of characters.
2. _(Underscore): This wildcard represents a single character. It matches any single character in that position of the string.