SQL Queries with Examples: Complete Guide from Basic to Advanced (2026)

SQL is one of the most valuable skills for anyone working with databases, data analytics, or software development. However, knowing SQL syntax is only the beginning. The real challenge is writing queries that retrieve accurate results, combine information from multiple tables, and solve practical business problems.

A simple SELECT statement can retrieve employee records, whereas JOINs connect related datasets. GROUP BY helps summarise sales figures, and window functions support advanced calculations without removing individual rows.

This guide covers the most commonly used SQL queries with examples, expected outputs, difficulty levels, and practical applications. You will also learn important SQL interview queries, common mistakes, and the differences between SQL and NoSQL.

Quick Answer

SQL queries help you retrieve, filter, combine, summarise, and analyse structured data for reporting, applications, and technical interviews.

• Basic Retrieval: SELECT, WHERE, and ORDER BY
• Combine Tables: JOIN statements
• Summarise Data: GROUP BY, COUNT(), SUM(), and AVG()
• Filter Groups: HAVING
• Advanced Logic: Subqueries and CTEs
• Analytical Queries: Window functions such as RANK(), ROW_NUMBER(), and LAG()
• Interview Focus: JOINs, duplicates, second-highest values, running totals, and growth calculations

What Are SQL Queries?

SQL queries are commands used to retrieve, filter, combine, update, and analyse data stored in relational databases. Common SQL queries include SELECT, WHERE, JOIN, GROUP BY, subqueries, CTEs, and window functions, which help users solve everything from basic data retrieval tasks to advanced reporting and business analysis.

Some of the Most Common SQL Queries

Need to change a column name after the table is created? Here is how to rename a column in SQL.

SQL is a core skill for data analysts, data scientists, and business intelligence professionals. However, building a career in data requires applying SQL alongside Python, statistics, visualisation, and practical problem-solving. Take the next step with HCL GUVI’s Data Science Course. Develop job-ready skills through live online classes, expert mentorship, hands-on projects, and placement assistance.

1. SELECT Statement

Difficulty: Beginner

SELECT retrieves data from a table. It’s the query you’ll write most often.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 70000;

Output:

Keep these in mind:

• Avoid SELECT * in production code — it’s slower and breaks silently when columns change
• Combine with WHERE, ORDER BY, and LIMIT to control exactly what comes back

2. JOIN Statements

Difficulty: Intermediate

JOINs combine rows from two or more tables using a shared column. Most real applications store data across multiple tables, so this is non-negotiable.

SELECT s.first_name, c.course_name
FROM students s
JOIN courses c ON s.student_id = c.student_id;

Output:

INNER JOIN returns only matching rows. LEFT JOIN keeps every row from the first table even without a match. Mixing these up is one of the most common beginner errors.

3. GROUP BY Clause

Difficulty: Intermediate

GROUP BY summarizes rows into groups, usually paired with COUNT, SUM, or AVG.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Output:

Use HAVING (not WHERE) if you need to filter after grouping — for example, showing only departments where the average salary exceeds ₹80,000.

Also Read: SQL for Data Science

4. Subqueries

Difficulty: Advanced

A subquery is a query nested inside another query. It’s useful when a filter depends on a result you don’t know in advance.

SELECT first_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Output:

CTEs (WITH clauses) do the same job with better readability for multi-step logic, and are the modern preference over deeply nested subqueries.

5. Window Functions

Difficulty: Advanced

Window functions calculate across related rows without collapsing them, using OVER().

SELECT first_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Output:

RANK() skips numbers on ties, ROW_NUMBER() never ties, and LAG()/LEAD() pull values from neighboring rows — handy for period-over-period comparisons.

Know More About SQL Window Functions

SQL Interview Queries Asked at Amazon, Flipkart, TCS, Infosys

Difficulty: Intermediate to Advanced

These companies tend to test the same handful of query patterns. A few you should be able to write cold:

1. Find the second-highest salary (a favorite at TCS and Infosys):

sql

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Output:

2. Find duplicate records (common at Amazon and Flipkart data-heavy rounds):

sql

SELECT email, COUNT(*) 
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Output:

3. Find employees with no matching department (LEFT JOIN + IS NULL, a classic Flipkart question):

sql

SELECT e.first_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Output:

Interviewers are usually less interested in the exact syntax and more in whether you can explain your logic out loud. Practice narrating your thought process, not just typing the query.

Advanced SQL Interview Queries With Examples

Advanced SQL interviews test your ability to solve business problems using multiple SQL concepts. Employers may ask you to calculate rankings, identify missing records, analyse customer behaviour, or compare performance across reporting periods.

Here are five advanced SQL interview queries worth practising:

1. Find the Top Three Highest-Paid Employees in Each Department

Difficulty: Advanced

This query uses DENSE_RANK() to rank employees by salary within each department.

WITH ranked_employees AS (
    SELECT
        first_name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT *
FROM ranked_employees
WHERE salary_rank <= 3;

Output:

DENSE_RANK() assigns the same rank to employees with equal salaries. It does not skip ranking numbers after ties. Consequently, the query may return more than three employees per department.

2. Calculate a Running Total of Monthly Sales

Difficulty: Advanced

Running totals help analysts track cumulative revenue and business growth.

SELECT
    sale_month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY sale_month
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS running_total
FROM monthly_sales;

Output:

The SUM() window function calculates cumulative revenue without combining individual monthly records.

3. Find Customers Who Have Never Placed an Order

Difficulty: Intermediate to Advanced

This query identifies customers without matching transactions using NOT EXISTS.

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Output:

NOT EXISTS checks whether a matching order exists for each customer. This approach is useful for identifying inactive customers and investigating conversion problems.

4. Calculate Month-over-Month Revenue Growth

Difficulty: Advanced

Month-over-month analysis measures how revenue changes between consecutive reporting periods.

WITH revenue_comparison AS (
    SELECT
        sale_month,
        revenue,
        LAG(revenue) OVER (
            ORDER BY sale_month
        ) AS previous_revenue
    FROM monthly_sales
)
SELECT
    sale_month,
    revenue,
    ROUND(
        (revenue - previous_revenue) * 100.0
        / NULLIF(previous_revenue, 0),
        2
    ) AS growth_percentage
FROM revenue_comparison;

Output:

LAG() retrieves revenue from the preceding row. NULLIF() prevents division-by-zero errors when previous revenue is zero.

The first month returns NULL because no earlier period exists.

5. Find Products Generating More Revenue Than Their Category Average

Difficulty: Advanced

This query compares individual product revenue against the average revenue within its category.

WITH product_comparison AS (
    SELECT
        product_name,
        category,
        revenue,
        AVG(revenue) OVER (
            PARTITION BY category
        ) AS category_avg
    FROM products
)
SELECT
    product_name,
    category,
    revenue
FROM product_comparison
WHERE revenue > category_avg;

Output:

The window function calculates category-level averages while retaining individual product records. The outer query then identifies products performing above their category average.

These examples use illustrative datasets. Actual outputs depend on the underlying records and database system.

Related Blog: SQL Interview Questions

SQL vs NoSQL- When to Use Which?

Difficulty: Beginner

This question comes up constantly once you move past query basics, especially in system design rounds.

Simple rule of thumb: if your data has clear relationships and needs strong consistency, go SQL. If it’s high-volume, semi-structured, and needs to scale horizontally, NoSQL fits better. Many real systems use both.

Common Mistakes to Avoid

• Confusing INNER JOIN with LEFT JOIN and losing rows silently
• Ignoring NULLs — use IS NULL or COALESCE instead of = NULL
• Writing unindexed queries on large tables, which slows everything down
• Skipping practice on real datasets and only memorizing syntax

Master SQL queries from basic to advanced with HCL GUVI’s Mastering MySQL Course. Learn database fundamentals, SQL commands, joins, subqueries, and practical query writing to strengthen your data management and analysis skills. Enrol today to build confidence in writing SQL queries for real-world applications.

Common Mistakes Beginners Make in SQL

Beginners often know basic SQL syntax but struggle with query logic, data relationships, and result accuracy.

Common mistakes include:

• Using the Wrong JOIN: Choosing an incorrect join type can remove needed rows or create unexpected duplicates.
• Ignoring NULL Values: Comparisons with NULL require IS NULL or IS NOT NULL, not standard equality operators.
• Forgetting GROUP BY Rules: Selecting non-aggregated columns without grouping them correctly can cause errors or misleading results.
• Confusing WHERE and HAVING: Use WHERE before aggregation and HAVING to filter grouped results.
• *Overusing SELECT : Retrieving every column can reduce readability and increase unnecessary processing.
• Missing JOIN Conditions: Incomplete join logic can produce duplicate-heavy or Cartesian results.
• Ignoring Duplicate Records: Duplicate rows can distort counts, averages, and business metrics.
• Writing Complex Queries Too Early: Beginners often combine too much logic in one query instead of breaking it into smaller steps.
• Not Checking Data Types: Comparing incompatible data types can cause errors or incorrect filtering.
• Using Incorrect Aggregate Logic: Functions such as COUNT, SUM, and AVG can produce misleading results when grouping is poorly defined.
• Skipping Query Validation: Always check row counts, sample records, and expected outputs before trusting results.
• Ignoring Query Readability: Poor aliases, inconsistent formatting, and deeply nested logic make SQL harder to debug and maintain.
• Memorising Without Practising: SQL becomes easier through hands-on problems involving joins, filtering, aggregation, and real datasets.
• Not Considering Performance: Repeated subqueries, unnecessary columns, and inefficient filtering can slow large database queries.

Know About SQL Project Ideas

Ready to go from SQL beginner to pro?HCL GUVI’s SQL Handbook guides you through practical examples, real-world projects, and hands-on exercises — all online and self-paced.

Wrapping it Up

Mastering SQL requires more than memorising commands. Understanding how SELECT, JOIN, GROUP BY, subqueries, and window functions work together helps you solve real database problems confidently.

Start with basic retrieval and filtering queries before progressing towards complex joins and analytical functions. Practise each concept using real datasets and review your results for accuracy.

Consistent practice will strengthen your SQL fundamentals and prepare you for technical interviews, data analysis projects, and database-related roles.

FAQs