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

SQL queries are commands used to retrieve, insert, update, and delete data in a relational database. If you can write a SELECT, a JOIN, and a GROUP BY confidently, you can handle most real-world database tasks, and most SQL interview rounds too.

TL;DR Summary

• SELECT, WHERE, and ORDER BY handle basic data retrieval
• JOINs combine data across multiple tables
• GROUP BY and aggregate functions summarize data
• Subqueries and window functions solve advanced, interview-favorite problems
• SQL fits structured data; NoSQL fits flexible, high-volume data

Some of the Most Common SQL Queries

Also Read | How To Learn SQL Using Squid Games?

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

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.

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.

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.

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.

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:

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:

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

sql

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

Output:

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.

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

Take your SQL skills to the next level with the MySQL Course. Gain hands-on experience in database management, query optimization, and real-world projects designed to build confidence and practical expertise. Master relational databases and strengthen your career prospects with industry-relevant skills.

Related Blog: SQL Interview Questions

Common Mistakes Beginners Make in SQL

Learning SQL becomes easier when you understand the mistakes that beginners often make. Avoiding these errors helps in building confidence and accuracy:

• Using Wrong Joins: Beginners often confuse inner joins with left joins. This creates incomplete or incorrect results in the output.
  The solution is to practice join queries with small datasets until the difference becomes clear.
• Ignoring NULL Values: Many learners forget to handle NULL values, which leads to misleading query results. The fix is to use functions like IS NULL or COALESCE to handle such cases.
• Writing Unoptimized Queries: Long queries without indexing increase response time. Optimizing queries improves database performance. The improvement comes from using indexes and breaking queries into smaller parts.
• Not Practicing with Real Data: Working only on theory prevents learners from understanding how SQL performs on large datasets. Practical exposure builds better skills. The best way to correct this is to practice with open-source datasets that simulate real projects.

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

These top SQL queries will give you an idea of how to work with databases efficiently. There are many SQL queries, but you can get started with these to kickstart your SQL journey. These SQL queries will help you to deal with databases, whether you’re a beginner or an experienced developer. You should get handy with these queries to enhance your SQL skills and become a proficient data professional.

FAQs

Q1. What is SQL?

Ans. SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases. It enables users to perform various operations such as querying data, updating records, inserting new data, and more.

Q2. Which databases use SQL?

Ans. SQL is used by various relational database management systems (RDBMS) such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database, among others.

Q3. How do I retrieve specific columns from a table?

Ans. You can use this query to retrieve specific columns from a table: SELECT column1, column2 FROM table;

Q4. What is a subquery?

Ans. A subquery is a query nested within another query. It can be used to retrieve data that will be used by the main query’s condition.
Example: SELECT column FROM table WHERE column IN (SELECT column FROM another_table);

Q5. Are subqueries slower than JOINs?

Not always, but JOINs are often more efficient for large datasets. Test both and check the execution plan when performance matters.

Q6. What’s the fastest way to get better at SQL interviews?

Practice writing joins and subqueries by hand instead of just reading solutions — recall matters more than recognition.