What are the SQL built-in functions? How do you use them in an SQL query? What is a subquery? When is a subquery executed?
The correct answer and explanation is:
✅ Correct Answer:
1. SQL Built-in Functions:
SQL provides several built-in functions used to perform operations on data. They are categorized as:
- Aggregate Functions: Work on multiple rows and return a single value.
SUM(),AVG(),COUNT(),MAX(),MIN()
- Scalar Functions: Work on a single value and return a single value.
UPPER(),LOWER(),LENGTH(),ROUND(),NOW(),GETDATE()
2. How to Use SQL Functions in a Query:
Example of aggregate functions:
SELECT AVG(salary) AS average_salary FROM employees;
Example of scalar functions:
SELECT UPPER(first_name) AS uppercase_name FROM employees;
You can also combine functions:
SELECT ROUND(AVG(salary), 2) AS avg_salary_rounded FROM employees;
3. What is a Subquery?
A subquery (also called an inner query or nested query) is a query inside another query. It returns data that is used by the main query.
Example:
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Here, the inner query calculates the average salary, and the outer query selects employees earning above that average.
4. When is a Subquery Executed?
A subquery is executed first before the outer (main) query. Its result is passed to the main query for further processing. Depending on how it is used, subqueries can run:
- Once for the entire outer query (scalar subquery)
- Once for each row processed by the outer query (correlated subquery)
✍️ Summary (300 words):
SQL’s built-in functions streamline data analysis and manipulation directly in queries. Aggregate functions like SUM(), AVG(), and COUNT() operate over multiple rows and are often used with GROUP BY to summarize data. Scalar functions, such as UPPER() or ROUND(), act on individual values and are useful for string formatting or mathematical rounding. These functions are embedded in SELECT, WHERE, or HAVING clauses to calculate or filter values dynamically.
Subqueries add another layer of power and flexibility. By embedding one query inside another, subqueries help compare values or filter results based on computations like averages or maximums. For instance, selecting employees with salaries above the average involves a subquery to compute the average salary, followed by an outer query that uses this value for comparison.
Execution timing matters: SQL first executes the subquery and then processes the main query using its result. In non-correlated subqueries, the subquery runs once. In correlated subqueries, it runs for each row of the outer query, which can impact performance.
Used correctly, built-in functions and subqueries together allow SQL developers to write highly expressive and efficient data queries, reducing the need for application-side data processing.