Q.Keshav has written the following query to find out the sum of bonus earned by the employees of WEST zone: SELECT zone, TOTAL (bonus) FROM employee HAVING zone = 'WEST'; But he got an error. Identify the errors and rewrite the query by underlining the corrections(s) done.

Keshav's query contains three main errors related to SQL syntax and clause usage.

First, TOTAL is not a standard aggregate function in SQL; the correct function to calculate the sum of values in a column is SUM. Second, the HAVING clause is used to filter groups of rows after aggregation, typically following a GROUP BY clause. To filter individual rows based on a condition before any aggregation, the WHERE clause must be used. In this case, zone = 'WEST' is a condition on individual rows. Finally, when a SELECT statement includes both an aggregate function like SUM(bonus) and a non-aggregated column like zone, a GROUP BY clause is essential to specify …

Imagine you're trying to gather specific information from a vast ledger, like an employee database. You wouldn't just shout out vague instructions; you'd use precise language to get exactly what you need. SQL, or Structured Query Language, is that precise language for databases. It allows us to ask questions and retrieve data in a structured way. Keshav's query, while aiming to find the total bonus for employees in the 'WEST' zone, contains a few common misunderstandings about how SQL processes information, particularly when it comes to summarizing data.

When we want to perform calculations across multiple rows, like finding a sum, an average, or a count, we use what are called 'aggregate functions'. These functions take a set of values and return a single summary value.

Here's a breakdown of the issues in Keshav's query:

• Error 1: The TOTAL function

  SQL has a standard set of aggregate functions, and TOTAL is not one of them. To calculate the sum of values in a column, the correct function to use is SUM(). For instance, SUM(bonus) would correctly add up all the bonus amounts. Other common aggregate functions include AVG() for average, COUNT() for counting rows, MIN() for the minimum value, and MAX() for the maximum value.

• Error 2: Misuse of the HAVING clause

  The HAVING clause is specifically designed to filter groups of rows after they have been aggregated using a GROUP BY clause. Think of it as a WHERE clause for groups. If you want to filter individual rows before any aggregation happens, or based on a condition that applies to each row, you must use the WHERE clause. Keshav wants to select employees whose zone is 'WEST' before summing their bonuses. This is a row-level condition, not a group-level condition, and thus belongs in the WHERE clause.

• Error 3: Missing GROUP BY clause …