Q.Which of the following is not an aggregate function in MYSQL ? (A) AVG() (B) MAX() (C) LCASE() (D) MIN()

Showing the 12 most recent of 13 on this concept.

1. CBSE 2025Set 90/1/11 markMCQ
   Q.With respect to SQL, match the function given in column-II with categories given in column-I: Column-I:

   (i) Math function

   (ii) Aggregate function

   (iii) Date function

   (iv) Text function Column-II:

   (a) COUNT()

   (b) ROUND()

   (c) RIGHT()

   (d) YEAR() Options: (A) (i)-(c), (ii)-(d), (iii)-(a), (iv)-(b) (B) (i)-(b), (ii)-(a), (iii)-(d), (iv)-(c) (C) (i)-(d), (ii)-(b), (iii)-(a), (iv)-(c) (D) (i)-(b), (ii)-(c), (iii)-(d), (iv)-(a)

   ›Reveal solutionSolution

   SQL functions fall into distinct categories based on their purpose: Math functions perform numerical calculations, Aggregate functions summarize data across rows, Date functions manipulate temporal values, and Text functions handle string operations.

   SQL organizes its built-in functions into logical families, each designed to solve a particular class of problem. Understanding these categories isn't just about memorization—it's about recognizing what kind of operation you need when writing queries.

   Math functions work with numerical data to perform calculations on individual values. ROUND() is the quintessential example here: it takes a number and rounds it to a specified number of decimal places. When you need to clean up floating-point results or present currency values neatly, ROUND() does the arithmetic work. It operates on one number at a time, transforming it according to mathematical rules.

   Aggregate functions tell a different story—they collapse many rows into a single summary value. COUNT() epitomizes this category. Instead of transforming individual values, it looks across an entire set of rows (or a grouped subset) and returns how many there are. Whether you're counting customers, transactions, or inventory items, COUNT() synthesizes information from multiple records into one meaningful number. Other members of this family include SUM(), AVG(), MAX(), and MIN(), all sharing that same "many-to-one" characteristic.

   Date functions specialize in extracting or manipulating components of temporal data. YEAR() is a classic representative: given a date or datetime value, it pulls out just the year portion as an integer. This becomes invaluable when you need to filter records by year, group sales by annual periods, or calculate ages. Date functions understand the peculiarities of calendars—leap years, month lengths, time zones—so you don't have to parse dates manually. …

   🔒 Start your 14-day free trial to see the full solution →
2. CBSE 2024Set 90/1/11 markMCQ
   Q.Which of the following is not an aggregate function in MYSQL ? (A) AVG() (B) MAX() (C) LCASE() (D) MIN()

   ›Reveal solutionSolution

   LCASE() is a string manipulation function that converts text to lowercase, not an aggregate function that performs calculations across multiple rows.

   Aggregate functions in SQL are designed to perform a calculation 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 "What's the average?" or "What's the highest value?" across an entire dataset or group.

   The three functions AVG(), MAX(), and MIN() are classic examples of aggregate functions. AVG() computes the arithmetic mean of a numeric column — add up all the values and divide by the count. MAX() finds the largest value in a set, whether that's the highest salary, the most recent date, or even the last string alphabetically. MIN() does the opposite, identifying the smallest or earliest value. All three operate on multiple rows and condense them into a single output.

   LCASE(), on the other hand, belongs to an entirely different category: string functions. It takes a single text value and converts all its characters to lowercase. If you pass it "HELLO", it returns "hello". This is a row-by-row transformation — it doesn't summarize or aggregate anything. Each input row gets its own output; there's no calculation across multiple rows. …

   🔒 Start your 14-day free trial to see the full solution →
3. CBSE 2024Set 90/1/11 markMCQ
   Q.Which MySQL command helps to add a primary key constraint to any table that has already been created ? (A) UPDATE (B) INSERT INTO (C) ALTER TABLE (D) ORDER BY

   ›Reveal solutionSolution

   Primary key constraints on existing tables are added through schema modification commands. The answer is (C) ALTER TABLE.

   When you create a table in MySQL, you define its structure: column names, data types, and constraints like primary keys. But what happens when you've already created a table and later realize you need to add a primary key? You need a command that modifies the table's structure itself, not its data.

   This is where the distinction between Data Definition Language (DDL) and Data Manipulation Language (DML) becomes crucial. DDL commands change the schema—the blueprint of your database objects. DML commands work with the data inside those objects.

   Let's examine each option:

   1. UPDATE is a DML command that modifies existing data in rows. You use it to change values in columns, like UPDATE students SET grade = 'A' WHERE id = 5. It cannot alter the table's structure or add constraints.

   2. INSERT INTO is another DML command that adds new rows of data to a table. It populates the table with records but has no capability to modify the table definition itself.

   3. ALTER TABLE is the DDL command specifically designed to modify an existing table's structure. You can add columns, drop columns, change data types, and crucially, add or remove constraints like primary keys, foreign keys, and unique constraints. …

   🔒 Start your 14-day free trial to see the full solution →
4. CBSE 2024Set 90/1/11 markMCQ
   Q.Which of the following clause cannot work with SELECT statement in MYSQL ? (A) FROM (B) INSERT INTO (C) WHERE (D) GROUP BY

   ›Reveal solutionSolution

   INSERT INTO is a separate DML statement for adding rows, not a clause that works with SELECT. The answer is (B).

   Understanding SQL Clauses vs. Statements

   SQL distinguishes between statements (complete commands) and clauses (components that modify or extend a statement). A SELECT statement retrieves data and can be enhanced with various clauses that filter, group, or specify the source of that data.

   The question asks which option cannot work with a SELECT statement. Let's examine each:

   1. FROM clause – Specifies the table(s) from which to retrieve data. Every practical SELECT needs a FROM clause (except trivial cases like SELECT 1;). This is fundamental to SELECT.

      SELECT name FROM students;
      

   2. WHERE clause – Filters rows based on conditions. This works seamlessly with SELECT to narrow down results.

      SELECT name FROM students WHERE age > 18;
      

   3. GROUP BY clause – Groups rows sharing common values, typically used with aggregate functions. This is a standard SELECT clause.

      SELECT department, COUNT(*) FROM employees GROUP BY department;
      

   4. INSERT INTO – This is not a clause; it's a completely separate DML statement used to add new rows to a table. You cannot "attach" INSERT INTO to a SELECT statement as you would a clause. …

   🔒 Start your 14-day free trial to see the full solution →
5. CBSE 2023Set 91/11 markMCQ
   Q.Fill in the blank: .............. clause is used with SELECT statement to display data in a sorted form with respect to a specified column.

   (a) WHERE

   (b) ORDER BY

   (c) HAVING

   (d) DISTINCT

   ›Reveal solutionSolution

   The ORDER BY clause sorts query results by one or more columns in ascending or descending order.

   When you retrieve data from a database table using a SELECT statement, the rows typically appear in no particular order—often the sequence in which they were inserted, though even that isn't guaranteed. If you want your results organized meaningfully, you need a way to impose order on the output.

   The ORDER BY clause exists precisely for this purpose. It tells the database management system to arrange the result set according to the values in one or more specified columns. By default, ORDER BY sorts in ascending order (smallest to largest for numbers, A to Z for text), but you can explicitly request descending order using the DESC keyword.

   Consider a simple example: if you have a student table with columns for name, age, and marks, writing SELECT * FROM students ORDER BY marks would display all students sorted from lowest to highest marks. Adding ORDER BY marks DESC would reverse that, showing top scorers first.

   Let's quickly examine why the other options don't fit:

   • WHERE filters rows before they're selected, keeping only those that meet a condition (like WHERE age > 15)
   • HAVING filters groups after aggregation, used with GROUP BY to filter summarized data …

   🔒 Start your 14-day free trial to see the full solution →
6. CBSE 2023Set 90/1/11 markMCQ
   Q.Aggregate functions are also known as: (A) Scalar Functions (B) Single Row Functions (C) Multiple Row Functions (D) Hybrid Functions

   ›Reveal solutionSolution

   Aggregate functions are called Multiple Row Functions because they operate on groups of rows and return a single result per group.

   When you're working with databases and SQL, you quickly discover that not all functions behave the same way. Some functions work on individual values—one row at a time—while others need to look at many rows together to produce a meaningful result. Understanding this distinction is fundamental to writing effective queries.

   Aggregate functions belong to the second category. They take multiple rows of data as input and collapse them into a single summary value. Think about what happens when you use SUM(), COUNT(), AVG(), MAX(), or MIN(). Each of these functions examines an entire set of rows—perhaps all the sales in a month, or all the students in a class—and produces one answer. You're asking the database to aggregate information across many records, which is exactly why they're called aggregate functions.

   The terminology "Multiple Row Functions" captures this behavior perfectly. These functions process multiple rows simultaneously and return one result for the entire group. If you have a table with a thousand employee records and you write SELECT AVG(salary) FROM employees, the AVG() function reads all thousand salary values and gives you back a single average. That's multiple-row processing in action.

   Note

   The term "Multiple Row Functions" emphasizes the input side of the operation—these functions consume many rows. The term "aggregate" emphasizes what they do with those rows—they combine or summarize them. …

   🔒 Start your 14-day free trial to see the full solution →
7. CBSE 2023Set 90/1/11 markMCQ
   Q.Ravisha has stored the records of all students of her class in a MYSQL table. Suggest a suitable SQL clause that she should use to display the names of students in alphabetical order. (A) SORT BY (B) ALIGN BY (C) GROUP BY (D) ORDER BY

   ›Reveal solutionSolution

   To display student names in alphabetical order, Ravisha should use the ORDER BY clause in her SQL query.

   When working with databases, especially with a large number of records like student information, simply retrieving data isn't always enough. Often, we need to present this data in a structured and readable manner. One of the most common requirements is to sort the data based on certain criteria, such as displaying names alphabetically, scores from highest to lowest, or dates chronologically. This is where SQL clauses designed for ordering come into play.

   The Structured Query Language (SQL) provides specific commands to manipulate and retrieve data from relational databases. To achieve the task of displaying student names in alphabetical order, Ravisha needs a clause that explicitly tells the database system to arrange the retrieved records based on the values in the 'name' column.

   The correct SQL clause for sorting a result set is ORDER BY. This clause allows you to specify one or more columns by which the data should be sorted. By default, ORDER BY sorts data in ascending order (A-Z for text, smallest to largest for numbers, earliest to latest for dates). If you want to sort in descending order, you explicitly use the DESC keyword after the column name. For alphabetical order, the default ascending sort is exactly what is needed.

   For example, if Ravisha's table is named Students and it has a column StudentName, a query to display names alphabetically would conceptually look like:

   SELECT StudentName FROM Students ORDER BY StudentName;

   Important

   The ORDER BY clause is always applied to the result set of a SELECT statement and determines the final presentation order of the rows.

   Let's briefly consider why the other options are not suitable:

   • SORT BY: This is not a standard SQL clause. While the concept of sorting is what we're after, SORT BY is not the correct syntax in MySQL or other standard SQL databases.
   • ALIGN BY: This is also not a recognized SQL clause for sorting or any other standard database operation. It might be confused with formatting or display options in specific reporting tools, but not for SQL itself. …

   🔒 Start your 14-day free trial to see the full solution →
8. CBSE 2019Set 90/1/11 mark
   Q.‘Employee’ table has a column named ‘CITY’ that stores city in which each employee resides. Write SQL query to display details of all rows except those rows that have CITY as ‘DELHI’ or ‘MUMBAI’ or ‘CHANDIGARH’.

   ›Reveal solutionSolution

   To exclude rows where CITY is DELHI, MUMBAI, or CHANDIGARH, use the NOT IN operator with a list of those city names in the WHERE clause.

   When you need to filter out specific values from a column, SQL gives you a clean and readable way to do it. The NOT IN operator is exactly what you need here — it lets you specify a list of values to exclude, and the database returns every row where the column does not match any value in that list.

   Think of it this way: you want all employees except those living in three particular cities. Instead of writing multiple AND conditions with != (which would be messy and error-prone), you simply write CITY NOT IN ('DELHI', 'MUMBAI', 'CHANDIGARH'). The database checks each row: if the CITY value is any of those three, the row is skipped; otherwise, it is included.

   Note

   The NOT IN operator works with any data type — numbers, dates, or text. Just ensure the values in the list match the column's data type exactly (here, text strings in single quotes).

   The full query would look like this:

   SELECT * FROM Employee
   WHERE CITY NOT IN ('DELHI', 'MUMBAI', 'CHANDIGARH');
   

   This returns all columns (*) for every employee whose city is not Delhi, Mumbai, or Chandigarh. If you only need specific columns (like name, salary, or department), replace * with those column names. …

   🔒 Start your 14-day free trial to see the full solution →
9. CBSE 2019Set 90/1/11 mark
   Q.Consider the following table ‘Transporter’ that stores the order details about items to be transported. Table : TRANSPORTER

   ORDERNO	DRIVERNAME	DRIVERGRADE	ITEM	TRAVELDATE	DESTINATION
   10012	RAM YADAV	A	TELEVISION	2019-04-19	MUMBAI
   10014	SOMNATH SINGH		FURNITURE	2019-01-12	PUNE
   10016	MOHAN VERMA	B	WASHING MACHINE	2019-06-06	LUCKNOW
   10018	RISHI SINGH	A	REFRIGERATOR	2019-04-07	MUMBAI
   10019	RADHE MOHAN		TELEVISION	2019-05-30	UDAIPUR
   10020	BISHEN PRATAP	B	REFRIGERATOR	2019-05-02	MUMBAI
   10021	RAM		TELEVISION	2019-05-03	PUNE

   Write SQL command for the following statement :

   (i) To display names of drivers and destination city where TELEVISION is being transported.

   ›Reveal solutionSolution

   We need to filter rows where the ITEM is 'TELEVISION' and display only the driver's name and destination city from those matching records.

   When working with a database table, the fundamental task is to retrieve specific information based on conditions. The TRANSPORTER table holds a complete record of transport orders, but we rarely need all columns or all rows at once. SQL's SELECT statement lets us pick exactly what we want to see and which records to include.

   The question asks for two pieces of information: the names of drivers and the cities they're heading to, but only when they're transporting televisions. This is a classic filtering problem. We need to scan through the table, identify rows where the ITEM column contains 'TELEVISION', and then pull out just the DRIVERNAME and DESTINATION columns from those rows.

   Looking at the table, three orders involve televisions: order 10012 (RAM YADAV to MUMBAI), order 10019 (RADHE MOHAN to UDAIPUR), and order 10021 (RAM to PUNE). Our query should return exactly these three driver-destination pairs.

   The structure of the SQL command follows a simple pattern: we SELECT the columns we want, FROM the table that holds them, WHERE a condition is true. The condition here is straightforward—ITEM must equal 'TELEVISION'. In SQL, text values are enclosed in single quotes, and the equality operator is a simple equals sign.

   Note

   SQL is case-insensitive for keywords (SELECT, FROM, WHERE), but it's good practice to write them in uppercase for readability. Column names and table names follow the case sensitivity rules of your database system, though most treat them as case-insensitive. String values in the WHERE clause, however, are case-sensitive by default in many databases. …

   🔒 Start your 14-day free trial to see the full solution →
10. CBSE 2019Set 90/1/11 mark
    Q.Consider the following table ‘Transporter’ that stores the order details about items to be transported. Table : TRANSPORTER

    ORDERNO	DRIVERNAME	DRIVERGRADE	ITEM	TRAVELDATE	DESTINATION
    10012	RAM YADAV	A	TELEVISION	2019-04-19	MUMBAI
    10014	SOMNATH SINGH		FURNITURE	2019-01-12	PUNE
    10016	MOHAN VERMA	B	WASHING MACHINE	2019-06-06	LUCKNOW
    10018	RISHI SINGH	A	REFRIGERATOR	2019-04-07	MUMBAI
    10019	RADHE MOHAN		TELEVISION	2019-05-30	UDAIPUR
    10020	BISHEN PRATAP	B	REFRIGERATOR	2019-05-02	MUMBAI
    10021	RAM		TELEVISION	2019-05-03	PUNE

    Write SQL command for the following statement :

    (ii) To display driver names and destinations where destination is not MUMBAI.

    ›Reveal solutionSolution

    To display driver names and destinations where the destination is not Mumbai, you use the SELECT command with a WHERE clause that excludes 'MUMBAI' using the <> or != operator.

    The table you're working with is called Transporter, and it holds records of orders for transporting items. Each row tells you who the driver is, what grade they hold, which item they're carrying, when the travel happened, and where they're headed. This is a classic example of a relational database table — a structured way to store and retrieve information.

    When you need to pull out specific columns from a table, you use the SELECT statement. In this case, you want only two columns: DRIVERNAME and DESTINATION. But you don't want all destinations — you want to filter out Mumbai. That's where the WHERE clause comes in. It acts like a sieve, letting through only those rows that meet a condition.

    The condition here is "destination is not MUMBAI". In SQL, "not equal to" is written as <> (or sometimes != in some database systems). So the condition becomes DESTINATION <> 'MUMBAI'. Notice that the text value 'MUMBAI' is enclosed in single quotes — that's how SQL distinguishes a literal string from a column name or keyword.

    Note

    SQL is case-insensitive for keywords like SELECT and WHERE, but the data inside quotes is case-sensitive in many systems. So 'MUMBAI' will match exactly that spelling. If the table had 'Mumbai' or 'mumbai', it might not match — always check the actual data.

    Now, let's look at the table. The rows where destination is not Mumbai are:

    • Order 10014: Somnath Singh → Pune
    • Order 10016: Mohan Verma → Lucknow
    • Order 10019: Radhe Mohan → Udaipur
    • Order 10021: Ram → Pune …

    🔒 Start your 14-day free trial to see the full solution →
11. CBSE 2019Set 90/1/11 mark
    Q.Consider the following table ‘Transporter’ that stores the order details about items to be transported. Table : TRANSPORTER

    ORDERNO	DRIVERNAME	DRIVERGRADE	ITEM	TRAVELDATE	DESTINATION
    10012	RAM YADAV	A	TELEVISION	2019-04-19	MUMBAI
    10014	SOMNATH SINGH		FURNITURE	2019-01-12	PUNE
    10016	MOHAN VERMA	B	WASHING MACHINE	2019-06-06	LUCKNOW
    10018	RISHI SINGH	A	REFRIGERATOR	2019-04-07	MUMBAI
    10019	RADHE MOHAN		TELEVISION	2019-05-30	UDAIPUR
    10020	BISHEN PRATAP	B	REFRIGERATOR	2019-05-02	MUMBAI
    10021	RAM		TELEVISION	2019-05-03	PUNE

    Write SQL command for the following statement :

    (iii) To display the names of destination cities where items are being transported. There should be no duplicate values.

    ›Reveal solutionSolution

    To display unique destination cities, use the SELECT DISTINCT command on the DESTINATION column of the TRANSPORTER table.

    When working with databases, we often encounter situations where a particular piece of information, like a city name, might appear multiple times across different records. For instance, in the TRANSPORTER table, many orders might be destined for 'MUMBAI'. If our goal is simply to list all the different cities to which items are transported, rather than every single instance of a city for each order, we need a way to eliminate these repeated entries. This is where the concept of retrieving unique values becomes crucial.

    The SQL DISTINCT keyword serves precisely this purpose. When placed immediately after SELECT and before the column name, it instructs the database management system to return only the unique values from that specified column in the result set. Any duplicate entries for that column will be filtered out, ensuring that each value appears only once. This is particularly useful for generating lists of categories, types, or, in this case, unique destinations, without being overwhelmed by redundant information. …

    🔒 Start your 14-day free trial to see the full solution →
12. CBSE 2019Set 90/1/11 mark
    Q.Consider the following table ‘Transporter’ that stores the order details about items to be transported. Table : TRANSPORTER

    ORDERNO	DRIVERNAME	DRIVERGRADE	ITEM	TRAVELDATE	DESTINATION
    10012	RAM YADAV	A	TELEVISION	2019-04-19	MUMBAI
    10014	SOMNATH SINGH		FURNITURE	2019-01-12	PUNE
    10016	MOHAN VERMA	B	WASHING MACHINE	2019-06-06	LUCKNOW
    10018	RISHI SINGH	A	REFRIGERATOR	2019-04-07	MUMBAI
    10019	RADHE MOHAN		TELEVISION	2019-05-30	UDAIPUR
    10020	BISHEN PRATAP	B	REFRIGERATOR	2019-05-02	MUMBAI
    10021	RAM		TELEVISION	2019-05-03	PUNE

    Write SQL command for the following statement :

    (iv) To display details of rows that have some value in DRIVERGRADE column.

    ›Reveal solutionSolution

    "Some value in DRIVERGRADE" means the column is NOT NULL — use SELECT * FROM TRANSPORTER WHERE DRIVERGRADE IS NOT NULL;. It returns the 4 rows whose grade is A or B.

    Concept — NULL means "no value", and it needs special syntax

    In the TRANSPORTER table three rows (SOMNATH SINGH, RADHE MOHAN, RAM) have an empty DRIVERGRADE — in SQL these are NULL entries: the absence of a value, distinct from 0 or ''. NULL cannot be tested with ordinary comparison operators: DRIVERGRADE = NULL or DRIVERGRADE <> NULL never matches any row, because any comparison with NULL evaluates to UNKNOWN. SQL provides dedicated predicates instead: IS NULL and IS NOT NULL.

    The question asks for rows that have some value in DRIVERGRADE, i.e. rows where the column is not NULL.

    The SQL command

    SELECT *
    FROM TRANSPORTER
    WHERE DRIVERGRADE IS NOT NULL;
    

    Expected output on the given data

    ORDERNO	DRIVERNAME	DRIVERGRADE	ITEM	TRAVELDATE	DESTINATION
    10012	RAM YADAV	A	TELEVISION	2019-04-19	MUMBAI
    10016	MOHAN VERMA	B	WASHING MACHINE	2019-06-06	LUCKNOW
    10018	RISHI SINGH	A	REFRIGERATOR	2019-04-07	MUMBAI

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