SQL Basics, Tables, JOINs & Transactions Masterclass

⚡ C# 12 & .NET 8 🟢 Chapter 36 of 35 📂 Phase 13: Databases & Entity Framework Core 📅 2026 Edition

📌 Covered in this chapter: Database Concepts · Tables · Primary/Foreign Keys · SELECT · INSERT · UPDATE · DELETE · WHERE · ORDER BY · INNER JOIN · Transactions · ACID

Welcome to Phase 13 (Chapter 36): SQL Basics & Relational Database Masterclass! Before using Entity Framework Core, you must understand the SQL language that EF Core generates under the hood. In this chapter, we master relational database concepts (tables, rows, columns, primary/foreign keys), the five core SQL operations (SELECT, INSERT, UPDATE, DELETE, JOIN), filtering, sorting, transactions, and relationships between tables.

1Relational Database Concepts

Concept	Definition	Example
Table	A structured collection of related data organized in rows and columns (like a spreadsheet).	Products, Orders, Customers
Row (Record)	A single entry of data in a table representing one entity instance.	One specific product: Laptop, ₹75000
Column (Field)	A named attribute of the table with a defined data type.	ProductName VARCHAR(100)
Primary Key (PK)	A column (or combination) whose values uniquely identify each row.	ProductId INT PRIMARY KEY
Foreign Key (FK)	A column referencing the Primary Key of another table, enforcing referential integrity.	Orders.CustomerId → Customers.CustomerId
Index	A performance optimization structure for faster data retrieval on specific columns.	INDEX ON Customers(Email)

2Core SQL Operations — SELECT, INSERT, UPDATE, DELETE

SQL — Core CRUD Operations ▶ Run in Compiler

-- 1. CREATE TABLE with Primary Key
CREATE TABLE Products (
    ProductId   INT           PRIMARY KEY IDENTITY(1,1),
    Name        NVARCHAR(100) NOT NULL,
    Price       DECIMAL(10,2) NOT NULL,
    Stock       INT           DEFAULT 0,
    Category    NVARCHAR(50),
    CreatedAt   DATETIME      DEFAULT GETDATE()
);

-- 2. INSERT — Add rows
INSERT INTO Products (Name, Price, Stock, Category)
VALUES ('Laptop', 75000.00, 10, 'Electronics');

INSERT INTO Products (Name, Price, Stock, Category)
VALUES ('Mouse', 1200.00, 50, 'Accessories');

-- 3. SELECT — Read data
SELECT ProductId, Name, Price FROM Products;

-- 4. WHERE — Filter rows
SELECT * FROM Products WHERE Price > 5000;

-- 5. ORDER BY — Sort results
SELECT * FROM Products ORDER BY Price DESC;

-- 6. UPDATE — Modify data
UPDATE Products SET Price = 72000.00 WHERE ProductId = 1;

-- 7. DELETE — Remove rows
DELETE FROM Products WHERE Stock = 0;

3JOINs & Table Relationships

SQL JOINs combine rows from two or more tables based on a related column (Foreign Key relationship). Understanding JOINs is critical before using EF Core's navigation properties and Include() for Eager Loading.

JOIN Type	Returns	Use Case
INNER JOIN	Only rows with matching values in BOTH tables	Get orders that have valid customers
LEFT JOIN	All rows from LEFT table + matched right rows (NULL for unmatched)	Get all products even if never ordered
RIGHT JOIN	All rows from RIGHT table + matched left rows	Get all customers even with no orders
FULL OUTER JOIN	All rows from BOTH tables with NULL for missing matches	Audit reports requiring all records

SQL — INNER JOIN Example ▶ Run in Compiler

-- Orders table with Foreign Key
CREATE TABLE Orders (
    OrderId    INT PRIMARY KEY IDENTITY(1,1),
    CustomerId INT NOT NULL FOREIGN KEY REFERENCES Customers(CustomerId),
    ProductId  INT NOT NULL FOREIGN KEY REFERENCES Products(ProductId),
    Quantity   INT NOT NULL,
    OrderDate  DATETIME DEFAULT GETDATE()
);

-- INNER JOIN: Get order details with customer name and product name
SELECT
    o.OrderId,
    c.Name    AS CustomerName,
    p.Name    AS ProductName,
    o.Quantity,
    (p.Price * o.Quantity) AS TotalAmount
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId
INNER JOIN Products p  ON o.ProductId  = p.ProductId
ORDER BY o.OrderDate DESC;

4Transactions & ACID Properties

SQL — Transaction Example ▶ Run in Compiler

-- Transaction: Transfer money between accounts atomically
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 5000 WHERE AccountId = 2;

    IF @@ERROR != 0
        ROLLBACK TRANSACTION; -- Undo BOTH operations on error
    ELSE
        COMMIT TRANSACTION;   -- Apply BOTH operations on success

ACID Property	Meaning
Atomicity	All operations in a transaction succeed together, or all fail together (no partial commits).
Consistency	A transaction brings the database from one valid state to another, maintaining all defined rules.
Isolation	Concurrent transactions execute as if they were sequential (no dirty reads).
Durability	Committed transactions are permanently saved even after system failures (disk crash, power loss).

5Technical FAQs

Q1: What databases does EF Core support?

EF Core supports SQL Server (via Microsoft.EntityFrameworkCore.SqlServer), PostgreSQL (via Npgsql.EntityFrameworkCore.PostgreSQL), SQLite (via Microsoft.EntityFrameworkCore.Sqlite), MySQL, and others via provider packages.

Q2: What is an Index and why is it important?

An index creates a sorted data structure on a column that allows the database engine to find rows in O(log n) time instead of O(n) full table scan. Always index foreign key columns and columns used frequently in WHERE clauses.