Essential SQL Queries collection

Query Collections

Duplicate value

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1

Delete Duplicate Records While Keeping One

Efficiently remove duplicates but keep one copy.

Way 1 Recommended way cte + window

It’s a more modern SQL approach, clearer in intent when dealing with duplicates, and easier to adapt for more complex rules.

WITH cte AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
    FROM your_table
)
DELETE FROM your_table WHERE id IN (SELECT id FROM cte WHERE rn > 1)

Read Further

Way 2: subquery

DELETE FROM students
WHERE id NOT IN (
    SELECT MAX(id)
    FROM students
    GROUP BY roll
);

• When to use MAX(id) subquery method:
  • Your criteria for the “latest” record is simple (e.g., just max id or max timestamp).
  • Your database is older or does not support window functions.
  • You want a straightforward, easy-to-understand query.
  • Dataset size is small to medium.
• When to use ROW_NUMBER() with CTE method:
  • You want more flexibility in defining the latest record (e.g., multiple columns, complex ordering).
  • You want to handle ties or more complex deduplication logic.
  • Your database supports window functions (PostgreSQL, MySQL 8+, SQL Server, Oracle, etc.).
  • Your dataset is large, and performance optimization is important (window functions are often well optimized).
  • You want cleaner control and can maintain slightly more complex SQL.

Nth Largest Value

SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET n-1

Replace query

This query will be helpful if you want to replace a part of the string in the table.

UPDATE table_name SET 'col-1' = REPLACE('col-1', 'old string', 'new-string')

Detect missing values in a sequence

WITH RECURSIVE sequence_generator AS (
SELECT MIN(id) AS id FROM table_name
UNION ALL
SELECT id + 1 FROM sequence_generator
WHERE id < (SELECT MAX(id) FROM table_name)
)
SELECT sg.id AS missing_id
FROM sequence_generator sg
LEFT JOIN table_name o ON sg.id = o.id
WHERE o.id IS NULL;

Good To Know

Delete or truncate row having foreign key constraints

You should be 100% sure if you going to delete or truncate a table with a foreign key as it may break your foreign key constraints

Steps to be followed

• Disable the foreign key check
• Do require operations
• Enable the foreign key check

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

SET the logs of each query

Enabling query logs can be useful for debugging.

Time-based logs

Table-based logging means that MySQL logs queries and activities into a database table (mysql.general_log) instead of a file. This allows you to inspect queries directly within MySQL using SQL commands.

Command

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

View the Logs from the table

SELECT * FROM mysql.general_log;

File Based Logs

File-based logging means MySQL writes query logs to a specified file instead of storing them in a database table. This is useful when you want to analyze logs outside MySQL using text editors, log management tools, or shell commands.

SET GLOBAL log_output = "FILE"; 
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Off

Don’t forget to off the logs, if you are not using them. keeping them on for too long may impact performance.

SET GLOBAL general_log = 'OFF';

Common Table Expression(CTE)

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement in SQL. CTEs are often used to simplify complex queries, improve readability, and reuse query logic.

Syntax

WITH cte_name (column1, column2, ...)
AS (
    -- Query to generate the result set
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

Read Further

What is temporary table

Temporary tables in SQL are special types of tables that exist only for the duration of a session or transaction. They are useful for storing intermediate results, reducing query complexity, and improving performance.

Temporary tables vs CTE

Both Temporary Tables and Common Table Expressions (CTEs) are used for storing intermediate results in SQL, but they serve different purposes and have different behaviors.

Read Further

Stored Procedures 

A Stored Procedure is a named set of SQL statements that are stored in the database and can be executed (called) as needed. It may accept parameters and can return data (via output parameters or result sets).

DELIMITER //

CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //

DELIMITER ;



-- To call this procedure --
CALL GetUsers();

Why Use Stored Procedures?

• Reusability: Write once, use many times.
• Performance: Precompiled and cached in the database.
• Security: Restrict direct access to data; use procedures instead.
• Maintainability: Logic is centralized in one place.
• Abstraction: Hide complex logic from the application.

Where Are Stored Procedures Saved?

Stored procedures are saved inside the database server, specifically:

• In the data dictionary (or system catalog) of the database.
• This is not in your application code or on your filesystem.
• Think of them as database-side scripts managed and executed by the Database Management System (DBMS).

Key Points:

• Stored procedures are server-side, unlike queries sent from an app.
• They are stored in the database, not in your code files.
• You can call them from your app using CALL proc_name(…).

Having

The HAVING clause in SQL is used to filter the results of a GROUP BY query based on aggregate functions like SUM(), COUNT(), AVG(), etc.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING aggregate_function(column2) condition;

WHERE vs HAVING:

• WHERE is used to filter before grouping.
• HAVING is used to filter after grouping.

Example

SELECT Customer, SUM(Amount) AS TotalSpent
FROM Orders
WHERE Status = 'Complete'           -- Filters rows before grouping
GROUP BY Customer
HAVING SUM(Amount) > 200;           -- Filters groups after aggregation