Why built-in functions matter in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Using built-in functions to calculate averages

Given a table Sales with a column amount, what is the output of this query?

SELECT AVG(amount) FROM Sales;

SQL

CREATE TABLE Sales(amount INT);
INSERT INTO Sales VALUES (100), (200), (300);

A 200

B 600

C 100

D SyntaxError

💡 Hint

Think about what the AVG function does with the numbers in the column.

The AVG function calculates the average value of the column. Here, (100 + 200 + 300) / 3 = 200.

📝 Syntax

intermediate

Identifying syntax error with built-in functions

Which option will cause a syntax error when trying to convert a string to uppercase in SQL?

A SELECT UPPER['hello'];

B SELECT upper('hello');

C SELECT UPPER('hello');

D SELECT UPPER("hello");

💡 Hint

Check the correct syntax for calling functions in SQL.

Functions in SQL use parentheses (), not square brackets []. Option A uses brackets causing a syntax error.

❓ query_result

advanced

Effect of built-in functions on NULL values

Consider a table Employees with a column salary containing values (5000, NULL, 7000). What is the result of this query?

SELECT SUM(salary) FROM Employees;

SQL

CREATE TABLE Employees(salary INT);
INSERT INTO Employees VALUES (5000), (NULL), (7000);

A 7000

B NULL

C 12000

D SyntaxError

💡 Hint

Think about how SUM treats NULL values in SQL.

SUM ignores NULL values and adds only the non-null salaries: 5000 + 7000 = 12000.

❓ optimization

advanced

Choosing built-in functions for date extraction

You want to extract the year from a date column order_date. Which query is the most efficient and correct in standard SQL?

A SELECT DATEPART(year, order_date) FROM Orders;

B SELECT EXTRACT(YEAR FROM order_date) FROM Orders;

C SELECT TO_CHAR(order_date, 'YYYY') FROM Orders;

D SELECT YEAR(order_date) FROM Orders;

💡 Hint

Consider which function is standard SQL and widely supported.

EXTRACT(YEAR FROM ...) is the standard SQL function for extracting year. Others are dialect-specific.

🧠 Conceptual

expert

Why built-in functions improve database performance

Which statement best explains why using built-in functions in SQL queries improves performance compared to user-defined functions?

A User-defined functions always run faster because they are customized.

B User-defined functions are compiled into machine code automatically.

C Built-in functions require more memory and slow down queries.

D Built-in functions are optimized and run inside the database engine, reducing overhead.

💡 Hint

Think about how databases handle built-in vs user-defined functions internally.

Built-in functions are pre-compiled and optimized by the database engine, making them faster and more efficient.