SQL Library Functions MCQs: 12 Solved Math, Aggregate, String and Date Questions

SQL function questions become error-prone when a query nests functions, switches dialect, or introduces NULL, even though each function looks simple alone. Choose an option, write each intermediate value, and only then compare it with the worked trace. For SELECT, joins, GROUP BY and subqueries, use DBMS SQL Query MCQs; numeric, string, date and aggregate function evaluation begins below. Use GATE CS Exam Preparation for the wider DBMS sequence after the function drill.

The four function families and the evaluation order to use

Syntax varies by DBMS, so follow the question's dialect. Evaluate nested functions inside out:

SELECT POWER(ROUND(4.5), 12 MOD 5);

ROUND(4.5) = 5 and 12 MOD 5 = 2, so POWER(5, 2) = 25. Likewise, TRIM(' MySQL ') produces 'MySQL', then LENGTH('MySQL') = 5. For broader context, study SQL Queries and Joins in DBMS.

Reuse this COACH table for aggregates:

Here, COUNT(GAME) = 3, COUNT(*) = 4, and AVG(SALARY) = (60000 + 80000) / 2 = 70000. The aggregate ignores the two NULL salaries.

The fourth trace uses a separate four-row COACH example. Its GAME values are Cricket, Football, NULL and Hockey; its SALARY values are 60000, 70000, 80000 and NULL. The results remain COUNT(GAME) = 3 and AVG(SALARY) = 70000.

Numeric-function MCQs 1-3: nested evaluation and rounding positions

Work inside out, recording inner results. In ROUND, 2 keeps two decimals and -1 rounds to tens.

Question 1, EMRS 2026

Analyze the following MySQL query and identify the correct numerical output from the given options:

SELECT POWER(ROUND(4.5), 12 MOD 5);

• A. 20.25

• B. 32

• C. 16

• D. 25

Correct option: D. 25

Start with the two inner expressions separately: ROUND(4.5) = 5, while 12 MOD 5 = 2. Substitution gives POWER(5, 2) = 5 * 5 = 25. The outer POWER receives these results, not the original arguments, so writing both inner values first prevents mixed steps during the final operation. Option A, 20.25, resembles 4.5^2, which skips ROUND and is precisely the trap.

Question 2, KVS 2026

What result will MySQL return for the following mathematical function?

SELECT ROUND(15.786, 2);

• A. 15.78

• B. 15.8

• C. 15.79

• D. 16.0

Correct option: C. 15.79

In 15.786, the hundredths digit is 8 and the next digit, the thousandths digit, is 6. Since 6 rounds upward, the hundredths digit becomes 9, producing 15.79. The second argument asks MySQL to retain two decimal places, so the result needs two digits after the decimal. Option A truncates instead of rounding, while option B keeps only one decimal place.

Question 3, BPSC PGT Tier-1 2023

The SQL statement

SELECT ROUND (65.726, -1) FROM DUAL;

prints

• A. 70

• B. garbage

• C. 726

• D. More than one of the above

• E. None of the above

Correct option: A. 70

The precision -1 moves the rounding position one place left of the decimal, to the tens place. The value to the right is 5.726, so 65.726 rounds upward to 70. The sign controls the direction: positive positions move right of the decimal, while negative positions move left. Negative precision is neither invalid nor an instruction to remove the decimal point.

String-function MCQs 4-7: trim, length, positions and nested slices

Record every intermediate string. MySQL uses one-based positions here, so the first character is at 1.

Question 4, KVS 2026

What is the final output of the following nested function?

SELECT LENGTH(TRIM(' MySQL '));

• A. 5

• B. 6

• C. 7

• D. 11

Correct option: A. 5

The input visibly has spaces on both sides. TRIM(' MySQL ') removes those outer spaces and produces 'MySQL'. Its remaining characters are M-y-S-Q-L, exactly five characters, so LENGTH(...) = 5. The larger distractors count characters before applying TRIM.

Question 5, KVS 2026

What will be the output of the following MySQL query?

SELECT CONCAT(UPPER(SUBSTR('MyKid',1,2)),'Sys');

• A. mysys

• B. MySys

• C. MYSys

• D. YKSys

Correct option: C. MYSys

Trace the expression in three steps: SUBSTR('MyKid',1,2) = 'My', then UPPER('My') = 'MY', and finally CONCAT('MY','Sys') = 'MYSys'. UPPER changes only the extracted substring. The separate literal 'Sys' keeps its original case.

Question 6, EMRS 2026

What is the output of the following MySQL command?

SELECT INSTR('MISSISSIPPI', 'S');

• A. 1

• B. 2

• C. 3

• D. 4

Correct option: C. 3

Number only until the first match: M(1), I(2), S(3). INSTR returns the one-based position of the first occurrence, so the result is 3. It does not return the number of occurrences, and it does not use a zero-based index.

Question 7, EMRS 2026

Analyze the following MySQL query:

SELECT RIGHT (LEFT('COMPUTER SCIENCE', 6), 3);

Which of the following queries will produce the same output as the one shown above?

• A. SELECT MID('COMPUTER SCIENCE', 3, 4);

• B. SELECT MID('COMPUTER SCIENCE', 4, 3);

• C. SELECT INSTR('COMPUTER SCIENCE', 4, 3);

• D. SELECT INSTR('COMPUTER SCIENCE', 3, 4);

Correct option: B. SELECT MID('COMPUTER SCIENCE', 4, 3);

First, LEFT('COMPUTER SCIENCE', 6) = 'COMPUT'. Then RIGHT('COMPUT', 3) = 'PUT'. In the original string, C1 O2 M3 P4 U5 T6, so MID(...,4,3) also returns positions 4 to 6, or 'PUT'. INSTR searches for a substring and does not take start-and-length arguments in the form shown.

Date-function MCQs 8-9: extracting a weekday and respecting dialect

Keep extraction separate from comparison: DAYNAME labels a weekday, while dialect-specific DATEDIFF measures a difference.

Question 8, KVS 2026

Consider the date '2026-02-27'. What will be the output of the following query?

SELECT DAYNAME('2026-02-27');

• A. 27

• B. February

• C. Friday

• D. 02

Correct option: C. Friday

DAYNAME returns a weekday name, not a day number or month. For this date, 2026-02-27 -> Friday, so option C is correct. Options A, B and D merely extract visible date parts.

Question 9, DSSSB TGT 2024

Which of the following statement is correct for DATEDIFF function in SQL?

I. This function reports difference between the arguments ‘startdate’ and ‘enddate’.

II. In this, ‘datepart’ argument/value can be specified in a variable.

• A. Only I

• B. Neither I nor II

• C. Both I and II

• D. Only II

Correct option: A. Only I

The wording uses SQL Server syntax. In DATEDIFF(datepart, startdate, enddate), the result is the number of specified date-part boundaries crossed from startdate to enddate, so Statement I is true. The datepart keyword is fixed in the syntax and cannot be supplied through a variable, so Statement II is false. MySQL uses a different two-argument form.

Aggregate-function MCQs 10-12: NULL, HAVING and COUNT syntax

For column aggregates, ignore only NULL inputs, not rows. WHERE filters rows before aggregation; HAVING filters grouped results after it.

Question 10, KVS 2023

Find the output of the MySQL query based on the given table COACH (ignore the output header).

SELECT COUNT(GAME), AVG(SALARY)
FROM COACH;

• A. 3, 70000

• B. 3, 5000

• C. 7, 50000

• D. 3,4000

Correct option: A. 3, 70000

The non-NULL GAME values are CRICKET, ATHLATICS and CRICKET, so COUNT(GAME) = 3. Only two salaries are non-NULL, giving AVG(SALARY) = (60000 + 80000) / 2 = 140000 / 2 = 70000. These aggregates ignore NULL; they do not convert it to zero.

Question 11, Bihar STET 2025

Aggregate functions can be used with ............ clause. They can not be used with ............ clause.

• A. WHERE, HAVING

• B. HAVING, WHERE

• C. GROUP BY, HAVING

• D. SELECT, HAVING

Correct option: B. HAVING, WHERE

Fill the blanks directly: aggregates can be used with HAVING, which filters grouped results, but not directly with WHERE, which runs before aggregation. GROUP BY GAME HAVING AVG(SALARY) >= 70000 is valid. WHERE AVG(SALARY) >= 70000 is invalid in the same query block.

Question 12, DSSSB 2021

Which of the following statement(s) is/are correct regarding COUNT functions in Structured Query Language of Relational Database Management System?

I. COUNT(*) is used to count the number of values in a column.

II. COUNT() is used to count the number of rows of the query result.

• A. Only I

• B. Only II

• C. Both I and II

• D. Neither I nor II

Correct option: D. Neither I nor II

Statement I is false because COUNT(*) counts result rows, including rows whose individual columns contain NULL. COUNT(column) counts that column's non-NULL values. Statement II is false because bare COUNT() is invalid; the row-counting form is COUNT(*). On the four-row table, COUNT(*) = 4 and COUNT(GAME) = 3.

Retry SQL library functions from the first failed step

A raw total hides the useful diagnosis. Sort every miss by the first wrong intermediate value:

• Questions 1-3: write the rounding position, remainder and exponent before evaluating the outer function.

• Questions 4-7: write the exact intermediate string and number its character positions from one.

• Questions 8-9: name the DBMS and function signature before evaluating date behaviour.

• Questions 10-12: list the rows that remain after NULL handling, then separate row filtering from group filtering.

Keep this five-line checklist beside you:

1. Evaluate nested functions inside out.

2. Mark the requested rounding position.

3. Use one-based string indices when the dialect does.

4. Identify the DBMS before applying date syntax.

5. Decide whether NULL is ignored before computing an aggregate.

Next, try SQL Queries for Placement Interviews. For a structured DBMS and GATE CS sequence, follow GATE Guidance by Sanchit Sir, then return to a timed set.