Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: SQL Modes

Explore how to modify SQL modes in MySQL sessions to control query behavior and data validation. Understand the effects of ANSI_QUOTES on identifier quoting and ONLY_FULL_GROUP_BY on grouping rules. This lesson helps you configure session-specific SQL modes to write strict and deterministic SQL queries for accurate reporting.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
SET SESSION sql_mode = CONCAT_WS(
    ',',
    @@SESSION.sql_mode,
    'ANSI_QUOTES',
    'ONLY_FULL_GROUP_BY'
);
-- Uncomment to check the current session SQL mode
-- SELECT @@SESSION.sql_mode AS reporting_session_sql_mode;
SELECT   "c"."CustomerID"      AS CustomerID,
         "c"."CustomerName"    AS CustomerName,
         SUM("o"."TotalAmount") AS TotalSpent
FROM     "Customers" AS "c"
JOIN     "Orders" AS "o"
ON       "c"."CustomerID" = "o"."CustomerID"
GROUP BY "c"."CustomerID", "c"."CustomerName"
ORDER BY TotalSpent DESC;

Explanation

The explanation of the solution code is given below:

  • Lines 1–6: These modify the SQL mode only for the current session.

    • @@SESSION.sql_mode returns the current list of active SQL modes as a comma-separated string.

    • CONCAT_WS(',', ...) builds a new list by appending ANSI_QUOTES and ONLY_FULL_GROUP_BY to the existing modes, inserting commas as needed and avoiding duplicate separators.

    • SET SESSION sql_mode = ... applies the updated mode list only to this connection, without changing server-wide defaults.

  • Line 9: This selects the effective SQL mode for the current session and aliases it as reporting_session_sql_mode. This provides a quick verification step to confirm that ANSI_QUOTES and ONLY_FULL_GROUP_BY are now active.

  • Lines 11–18: These define the main reporting query.

    • The SELECT list returns each customer’s ID and name along with TotalSpent, computed using the SUM aggregate function.

    • Double quotes around "c", "o", "CustomerID", "CustomerName", and ...