Showing the 12 most recent of 17 on this concept.

1. CBSE 2026Set 91/41 markMCQ
   Q.Which aggregate function in SQL returns the smallest value from a column in a table ? (A) MIN() (B) MAX() (C) SMALL() (D) LOWER()

   ›Reveal solutionSolution

   The aggregate function that returns the smallest value from a column in SQL is MIN().

   In SQL, aggregate functions are used to perform calculations on a set of rows and return a single summary value. They are a core part of querying databases, especially when you need to analyse data — finding totals, averages, counts, or extremes. Among these, the function that specifically returns the smallest value from a column is MIN().

   The MIN() function works on numeric, date, or text columns. For text, it returns the value that comes first alphabetically. For dates, it returns the earliest date. This makes it versatile for many real-world queries — for example, finding the lowest price in a products table, the earliest order date, or the shortest employee name.

   Note

   The MIN() function ignores NULL values by default. If a column has NULLs, they are simply not considered in the result.

   Let’s look at the options given:

   • (A) MIN() — Correct. This is the standard SQL aggregate function for the smallest value.
   • (B) MAX() — Returns the largest value, not the smallest.
   • (C) SMALL() — This is not a valid SQL aggregate function. No such function exists in standard SQL or in any major database system. …

   🔒 Start your 14-day free trial to see the full solution →
2. CBSE 2026Set 90/1/11 mark
   Q.State whether the following statement is True or False : In SQL, an aggregate function returns multiple values for each column on which it is applied.

   ›Reveal solutionSolution

   Aggregate functions in SQL collapse multiple rows into a single summary value per group. The statement is False.

   Why aggregate functions exist

   SQL aggregate functions exist to summarize data across rows. When you have a table with thousands of transactions and want to know the total sales, you don't want thousands of numbers back—you want one number that represents the sum. That's the entire purpose of aggregation: to reduce many values to a single, meaningful summary.

   Think of it this way: if you ask "What is the average age of students in this class?", you expect one number as the answer, not a list repeating some value for each student. Aggregate functions work the same way.

   How aggregate functions actually behave

   The standard aggregate functions in SQL are:

   Function	Purpose	Output
   COUNT()	Counts rows	Single number
   SUM()	Adds values	Single number
   AVG()	Computes mean	Single number
   MAX()	Finds maximum	Single value
   MIN()	Finds minimum	Single value

   Each of these takes multiple input rows and produces one output value.

   For example:

   SELECT AVG(salary) FROM employees;
   

   If the employees table has 500 rows, this query returns exactly one row with one value—the average salary across all 500 employees.

   What about GROUP BY?

   When you use GROUP BY, aggregate functions return one value per group, not one value per original row:

   SELECT department, AVG(salary) 
   FROM employees 
   GROUP BY department;
   ``` …
   

   🔒 Start your 14-day free trial to see the full solution →
3. CBSE 2026Set 90/1/11 markMCQ
   Q.What will be the result of the following SQL command ? SELECT LENGTH ('Data Base'); (Note : There is single space between the words Data and Base.) (A) 7 (B) 8 (C) 9 (D) Error

   ›Reveal solutionSolution

   The LENGTH function counts every character in the string including spaces, so 'Data Base' returns 9.

   The LENGTH function in SQL is one of the most straightforward string functions you'll encounter. It does exactly what its name suggests: it counts the number of characters in a string and returns that count as an integer. The beauty of this function lies in its simplicity, but that simplicity can sometimes trip up students who forget one crucial detail.

   When SQL evaluates LENGTH('Data Base'), it examines the string character by character. The string contains:

   • The letter D
   • The letter a
   • The letter t
   • The letter a
   • A space character (this is the key!)
   • The letter B
   • The letter a
   • The letter s
   • The letter e

   That gives us a total of nine characters. The space between "Data" and "Base" is not invisible to the function — it's a legitimate character that occupies a position in the string, just like any letter or number would. Many students instinctively think of spaces as "nothing" or as mere separators, but in the world of string processing, a space is as real as any other character. …

   🔒 Start your 14-day free trial to see the full solution →
4. CBSE 2026Set 90/1/11 mark
   Q.State whether the following statement is True or False : The INSTR(string1, string2) function in SQL returns 0 if string2 is not present as a substring in string1.

   ›Reveal solutionSolution

   The INSTR function in SQL returns the starting position of a substring; if the substring is not found, it returns 0. Therefore, the statement is True.

   The INSTR function in SQL is designed to locate the starting position of a specified substring (string2) within a larger string (string1). Understanding its behavior, especially when the substring is absent, is crucial for writing correct SQL queries. The core idea is that if a substring is found, INSTR gives you its 1-based starting index. If it's not found, it needs a distinct value to signal this absence, and 0 is the standard convention for INSTR.

   Let's break down how INSTR works.

   1. Understanding INSTR Functionality:

      The INSTR(string1, string2) function searches for the first occurrence of string2 within string1. It returns an integer representing the starting position of string2 in string1. SQL string positions are typically 1-based, meaning the first character is at position 1, the second at position 2, and so on.

      For example:

      SELECT INSTR('HELLO WORLD', 'WORLD');
      -- This would return 7, because 'W' is the 7th character.
      

      SELECT INSTR('ANUMEDHA', 'NUM');
      -- This would return 2, because 'N' is the 2nd character.
      

   2. Behavior When Substring is Not Found:

      When string2 is not present anywhere within string1, the INSTR function cannot return a valid 1-based position. In such cases, it returns 0 to indicate that the substring was not found. This 0 acts as a clear signal for conditional logic in your SQL queries.

      For example:

      SELECT INSTR('HELLO WORLD', 'Z');
      -- This would return 0, because 'Z' is not in 'HELLO WORLD'. …
      

   🔒 Start your 14-day free trial to see the full solution →
5. CBSE 2026Set 90/1/11 markMCQ
   Q.Q. 20 and Q. 21 are Assertion (A) and Reason (R) Type questions. Choose the correct option as : (A) Both (A) and (R) are True, and (R) correctly explains (A). (B) Both (A) and (R) are True, but (R) does not correctly explain (A). (C) (A) is True, but (R) is False. (D) (A) is False, but (R) is True. Assertion (A) : The output of the SQL query SELECT COUNT (name) FROM students; will differ from the output of SELECT COUNT () FROM students; if there are some NULL values in the name column. Reason (R) : COUNT(column_name) returns the count of NON NULL values in that column, whereas COUNT() returns the number of records in the table.

   ›Reveal solutionSolution

   COUNT(column_name) excludes NULL values while COUNT(*) counts all rows; therefore the two queries will differ when the name column contains NULLs. Both assertion and reason are true, and the reason correctly explains the assertion.

   The heart of this question lies in understanding how SQL's COUNT function behaves differently depending on what you pass to it.

   When you write COUNT(*), you're asking SQL to count every row in the table, regardless of whether any particular column contains NULL values. It's a row counter, pure and simple.

   When you write COUNT(column_name), you're asking SQL to count only those rows where column_name is NOT NULL. SQL silently skips over any row where that column holds a NULL value.

   This distinction matters enormously in practice. Imagine a students table with 100 rows, but 5 students have NULL in the name column (perhaps their names weren't recorded yet). Then:

   • SELECT COUNT(*) FROM students; returns 100 (all rows)
   • SELECT COUNT(name) FROM students; returns 95 (only non-NULL names)

   Now let's evaluate the assertion and reason:

   Assertion (A): "The output of SELECT COUNT(name) FROM students; will differ from SELECT COUNT(*) FROM students; if there are some NULL values in the name column."

   This is true. As we just saw, the presence of NULLs in the name column causes COUNT(name) to return a smaller number than COUNT(*). …

   🔒 Start your 14-day free trial to see the full solution →
6. CBSE 2025Set 91/41 markMCQ
   Q.Which aggregate function in SQL displays the number of values in the specified column ignoring the NULL values ? (A) len() (B) count() (C) number() (D) num()

   ›Reveal solutionSolution

   The COUNT() aggregate function in SQL is used to display the number of values in a specified column, inherently ignoring NULL values.

   In SQL, aggregate functions are powerful tools designed to perform calculations on a set of rows and return a single summary value. These functions are fundamental for data analysis, allowing us to derive insights like sums, averages, maximums, minimums, and counts from large datasets. When dealing with real-world data, it's common to encounter NULL values, which represent missing or unknown data. How these NULL values are handled by aggregate functions is a crucial aspect of understanding their behavior.

   The question specifically asks for an aggregate function that counts the number of values in a column while ignoring NULL entries. Among the standard SQL aggregate functions, COUNT() is precisely designed for this purpose.

   Let's break down how COUNT() works:

   • COUNT(column_name): When you specify a column name inside the COUNT() function, like COUNT(EmployeeID) or COUNT(ProductName), SQL will iterate through each row in that column. For every row where the specified column_name has a non-NULL value, it increments the count. Rows where the column_name is NULL are simply skipped and do not contribute to the total count. This is the exact behavior requested by the question.

   • COUNT(*): It's important to distinguish COUNT(column_name) from COUNT(*). The COUNT(*) function counts the total number of rows in the result set, regardless of whether any specific column in those rows contains NULL values. It essentially counts the rows themselves, not the non-NULL values within a particular column.

   Important

   COUNT(column_name) counts only non-NULL values in the specified column, whereas COUNT(*) counts all rows, irrespective of NULLs in any column.

   Now, let's consider the other options provided: …

   🔒 Start your 14-day free trial to see the full solution →
7. CBSE 2025Set 90/1/11 markMCQ
   Q.Which of the following is not an aggregate function in SQL? (A) COUNT(*) (B) MIN() (C) LEFT() (D) AVG()

   ›Reveal solutionSolution

   LEFT() is a string manipulation function, not an aggregate function — it extracts characters from the left side of a string, while COUNT(*), MIN(), and AVG() all perform calculations across multiple rows.

   Aggregate functions in SQL are designed to perform calculations on a set of values and return a single summary value. They work across multiple rows of data, collapsing them into one meaningful result. Think of them as tools that answer questions like "How many records are there?", "What's the smallest value?", or "What's the average?" — they digest entire columns of data and give you one number back.

   COUNT(*) counts the total number of rows in a result set, including those with NULL values. It's the go-to function when you need to know "how many" of something exists in your database.

   MIN() finds the smallest value in a column. Whether you're looking for the lowest price, the earliest date, or the minimum score, MIN() scans through all the rows and returns that single smallest value.

   AVG() calculates the arithmetic mean of a numeric column. It adds up all the values and divides by the count, giving you the average — essential for understanding central tendencies in your data.

   Now LEFT() operates on an entirely different principle. It's a string function that extracts a specified number of characters from the left side of a text string. For example, LEFT('Database', 4) would return 'Data'. Notice what's happening here: LEFT() works on a single string value at a time, not across multiple rows. It doesn't summarize or aggregate anything — it simply manipulates one piece of text. …

   🔒 Start your 14-day free trial to see the full solution →
8. CBSE 2024Set 90/1/11 markMCQ
   Q.What will be the output of the following query ? SELECT SUBSTR(“G20 2023 INDIA”,5,4); (A) G20 2 (B) 2023 (C) INDI (D) 023

   ›Reveal solutionSolution

   The query extracts a substring of length 4 starting from the 5th character of the given string, so the output is "2023".

   The SUBSTR function in SQL is used to extract a portion of a string. It takes three arguments: the original string, the starting position, and the length of the substring to be extracted. The starting position is counted from the left, beginning with 1 for the first character.

   In the query SELECT SUBSTR("G20 2023 INDIA",5,4);, the string is "G20 2023 INDIA". Let's count the characters carefully:

   • Position 1: G
   • Position 2: 2
   • Position 3: 0
   • Position 4: (space)
   • Position 5: 2
   • Position 6: 0
   • Position 7: 2
   • Position 8: 3

   So starting from position 5, the characters are "2", "0", "2", "3". The length specified is 4, so we take exactly these four characters, giving us "2023".

   Note

   In SQL, string positions typically start at 1, not 0. This is different from some programming languages like Python or C where indexing starts at 0.

   Watch out

   A common mistake is to miscount the space at position 4. The space is a valid character and must be counted. If you forget the space, you might think position 5 is "0" instead of "2", leading to the wrong answer.

   Now let's look at the options: …

   🔒 Start your 14-day free trial to see the full solution →
9. CBSE 2024Set 90/1/11 markMCQ
   Q.Which MySQL string function is used to extract a substring from a given string based on a specified starting position and length ? (A) SUBSTRING_INDEX() (B) LENGTH() (C) MID() (D) TRIM()

   ›Reveal solutionSolution

   The MID() function extracts a substring from a string by specifying a starting position and length.

   When working with text data in MySQL, you often need to pull out just a portion of a string rather than the whole thing. Think of a database storing full names where you want only the first few characters, or product codes where specific segments carry meaning. MySQL provides several string functions for different slicing and extraction tasks, and understanding which does what is essential for efficient querying.

   The function designed specifically to extract a substring based on both a starting position and a length is MID(). Its syntax is straightforward: MID(string, start, length). You tell it which string to work with, where to begin counting (the position), and how many characters to grab from that point forward. For example, MID('Database', 5, 4) would return 'base' — starting at the 5th character and taking 4 characters total.

   Let's see why the other options don't fit:

   • SUBSTRING_INDEX() works differently — it splits a string based on a delimiter (like a comma or space) and returns everything before or after a specified occurrence of that delimiter. It doesn't use position and length; it uses pattern matching.

   • LENGTH() doesn't extract anything at all. It simply counts and returns the number of characters in a string, giving you a numeric value rather than a substring. …

   🔒 Start your 14-day free trial to see the full solution →
10. CBSE 2024Set 91/41 markMCQ
    Q.In SQL, the aggregate function which will display the cardinality of the table is ____. (A) sum() (B) count() (C) avg() (D) sum()

    ›Reveal solutionSolution

    The count(*) function returns the total number of rows in a table, which is its cardinality.

    When we talk about the cardinality of a table in database terminology, we mean the number of rows (or tuples) it contains. It's a fundamental measure of the table's size — how many records exist in that relation. SQL provides a family of aggregate functions that perform calculations across multiple rows and return a single summary value, and understanding which one gives us cardinality is essential for querying databases effectively.

    Aggregate functions operate on sets of values from a column (or columns) and collapse them into one result. The most common ones include:

    • sum() — adds up all the values in a numeric column
    • avg() — calculates the arithmetic mean of values in a numeric column
    • count() — counts rows or non-null values
    • max() and min() — find the largest or smallest value

    The question asks specifically which function reveals how many rows are in the table. The count(*) function does exactly this: it counts every row in the table, regardless of whether individual columns contain null values or not. The asterisk * tells SQL to count all rows without filtering by any particular column's content. …

    🔒 Start your 14-day free trial to see the full solution →
11. CBSE 2023Set 90/1/11 markMCQ
    Q.To remove the leading and trailing space from data values in a column of MySql Table, we use (A) Left ( ) (B) Right ( ) (C) Trim ( ) (D) Ltrim ( )

    ›Reveal solutionSolution

    Removing both leading and trailing spaces requires a function that works on both ends of the string; TRIM() does exactly that, while LTRIM() and RTRIM() handle only one side each.

    Why TRIM() is the answer

    When you store text data in a MySQL table, extra spaces can creep in—sometimes users accidentally type a space before or after their input, or data imports include padding. These invisible characters cause problems: searches fail, comparisons break, and your data looks messy.

    MySQL gives you three functions to clean up whitespace:

    • LTRIM() removes spaces from the left (leading) side only
    • RTRIM() removes spaces from the right (trailing) side only
    • TRIM() removes spaces from both ends

    The question asks for a function that handles both leading and trailing spaces. That immediately tells us we need something that works on both ends simultaneously.

    LEFT() and RIGHT() are completely different—they extract a specified number of characters from the left or right side of a string. They don't remove anything; they're substring functions. So options (A) and (B) are out.

    LTRIM() only handles the left side. If your data is " hello ", LTRIM() gives you "hello " with the trailing spaces still there. Option (D) is incomplete. …

    🔒 Start your 14-day free trial to see the full solution →
12. CBSE 2023Set 90/1/11 markMCQ
    Q.If the substring is not present in a string, the INSTR ( ) returns: (A) - 1 (B) 1 (C) NULL (D) 0

    ›Reveal solutionSolution

    The INSTR() function in SQL returns the starting position of a substring within a string. If the substring is not found, it returns 0, not NULL or -1. The correct answer is (D) 0.

    The Concept: Why INSTR() Returns 0

    The INSTR() function is a standard string function in SQL (used in Oracle, MySQL, and others) that searches for a substring inside a larger string. It returns the character position where the substring first appears, counting from 1. This is a positional index, not a boolean flag.

    The key intuition: in SQL, positions are 1-based. If the substring is the very first character, the result is 1. If it's the second character, the result is 2, and so on. But what happens when the substring simply isn't there? The function cannot return a valid position — so it returns 0, meaning "no match found." This is consistent with how many other SQL string functions (like LOCATE() or POSITION()) behave.

    Watch out

    A common mistake is to think INSTR() returns -1 or NULL when no match is found, as some other programming languages do (e.g., Python's str.find() returns -1). But SQL's INSTR() returns 0 — a clean, non-negative integer that fits naturally with 1-based indexing.

    Step-by-Step Reasoning

    1. Understand the function signature.

       INSTR(string, substring) returns the starting position of the first occurrence of substring within string. For example:

       INSTR('Hello World', 'World') returns 7 (since 'W' is the 7th character).

       INSTR('Hello World', 'o') returns 5 (the first 'o' is at position 5).

    2. Consider the case of no match.

       If the substring does not appear anywhere in the string, there is no valid starting position. The function cannot return a position number (1, 2, 3, …), so it returns 0. This is the standard SQL convention.

    3. Eliminate the other options. …

    🔒 Start your 14-day free trial to see the full solution →