Essential SQL Concepts You Must Know (With Examples)

Rishabh MishraRishabh Mishra
3 min read

Whether you're preparing for SQL interviews or building robust real-world applications, mastering these SQL fundamentals is non-negotiable.

This guide breaks down the core SQL concepts you need to know, with simple examples and real-life relevance.


1. Databases & Tables

  • A database stores structured data.

  • A table organizes data into rows (records) and columns (fields).

Think of a table like an Excel sheet — each column has a specific data type, and each row is a data record.


2. SQL Syntax Basics

SELECT column1, column2 
FROM table_name 
WHERE condition;
  • SELECT: Fetch data

  • FROM: Specify the table

  • WHERE: Apply filters and conditions


3. Data Types

  • INT, FLOAT, DECIMAL — Numeric data

  • VARCHAR, TEXT — Strings

  • DATE, DATETIME, TIME — Date/Time

  • BOOLEAN — True/False

  • NULL — Unknown or missing value


4. Filtering Data

SELECT * FROM users 
WHERE age > 25 AND city = 'Mumbai';

🛠️ Use logical operators like:

  • AND, OR, NOT

  • IN, LIKE, BETWEEN, IS NULL


5. Sorting & Limiting

SELECT * FROM orders 
ORDER BY amount DESC 
LIMIT 5;

Sort data using ORDER BY and control the result count with LIMIT.


6. Aggregate Functions

  • COUNT() — Number of rows

  • SUM() — Total sum

  • AVG() — Average

  • MAX() / MIN() — Highest/Lowest

SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

Used mostly with GROUP BY.


7. Joins (Combining Tables)

SELECT e.name, d.name 
FROM employees e 
JOIN departments d ON e.dept_id = d.id;
  • INNER JOIN — Matching records from both tables

  • LEFT JOIN — All from left + matched from right

  • RIGHT JOIN, FULL OUTER JOIN — Other combinations


8. Subqueries

SELECT name 
FROM users 
WHERE id IN (SELECT user_id FROM orders);

Subqueries are queries inside another query.


9. Set Operations

  • UNION — Combine unique results

  • UNION ALL — Includes duplicates

  • INTERSECT, EXCEPT — Set comparisons


10. Constraints

Constraints enforce data rules.

  • PRIMARY KEY — Uniquely identifies a row

  • FOREIGN KEY — References another table

  • UNIQUE, NOT NULL, DEFAULT, CHECK — Additional controls


11. Views

CREATE VIEW top_customers AS 
SELECT name, SUM(amount) AS total 
FROM orders 
GROUP BY name;

Views are virtual tables created from queries — useful for reporting or hiding complex logic.


12. Indexes

CREATE INDEX idx_name ON employees(name);

Indexes boost query performance, especially on large datasets.


13. Transactions & ACID Properties

START TRANSACTION;
-- your SQL statements
COMMIT; -- or ROLLBACK;

Transactions group multiple steps into one unit.

ACID stands for:

  • Atomicity — All or nothing

  • Consistency — Valid data only

  • Isolation — No interference

  • Durability — Changes persist


14. Window Functions

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank 
FROM employees;

Window functions perform calculations across a set of rows without collapsing the result (unlike aggregates).


15. Normalization

Normalization = Breaking larger tables into smaller ones to eliminate redundancy and ensure data integrity.

Forms include:

  • 1NF – Atomic columns

  • 2NF – Remove partial dependencies

  • 3NF – Remove transitive dependencies


Learning SQL isn't just about writing queries—it's about thinking in data. These concepts form the foundation of every backend system, BI tool, and data-driven app.

Bookmark this article for your daily SQL revision.
Keep practicing. Build queries. Break them. Learn by doing.

👉 Found this helpful? Follow DevDaily for more developer blogs every week!

✍ Written by Rishabh Mishra

0
Subscribe to my newsletter

Read articles from Rishabh Mishra directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Rishabh Mishra
Rishabh Mishra

Hey, I’m Rishabh — a developer who writes code like poetry and breaks things just to rebuild them better. .NET Full Stack Dev | Razor, C#, MVC, SQL, Angular — my daily playground. I believe in “learning out loud” — so I write about dev struggles, breakthroughs, and the weird bugs that teach the best lessons. From building ERP apps to tinkering with UI/UX — I turn business logic into beautiful experiences. Self-growth > Comfort zone | Debugging is my meditation Let’s turn curiosity into code — one blog at a time.