C#, .NET &
SQL Crash Course

Variables & Types

C# is statically typed — every variable has a fixed type known at compile time. Value types (int, bool, decimal) live on the stack, while reference types (string, arrays, objects) live on the heap. Use var to let the compiler infer the type; use explicit types when clarity matters. The decimal type is essential for money — unlike double, it avoids binary floating-point rounding errors.

// Value types (stack-allocated)
int age = 30;
double price = 9.99;
bool active = true;
char grade = 'A';
decimal total = 149.99m;       // financial precision

// Reference types (heap-allocated)
string name = "Hari";
int[] nums = [1, 2, 3];       // collection expression (C# 12)
object obj = "anything";

// Type inference
var count = 42;                 // int inferred
var devices = new List<Device>();

// Nullable value types
int? maybe = null;
int safe = maybe ?? 0;         // null-coalescing

// Constants
const int MaxRetries = 3;
readonly string _connStr;      // set once (in constructor)C#

Strings

C# strings are immutable reference types. String interpolation with $"..." is the preferred way to embed expressions. Raw string literals (C# 11, triple quotes) let you write JSON, SQL, or regex without any escaping. For large-scale concatenation, prefer StringBuilder to avoid creating many intermediate string objects.

// Interpolation
string greeting = $"Hello, {name}. Age: {age}";

// Raw string literals (C# 11) — no escaping needed
string json = """
    {
        "name": "Hari",
        "role": "admin"
    }
    """;

// Verbatim strings (multi-line, no escape processing)
string path = @"C:\Users\hari\docs";

// Common methods
"hello".ToUpper();              // "HELLO"
"  padded  ".Trim();            // "padded"
"a,b,c".Split(',');             // string[]
string.Join(",", arr);           // "a,b,c"
"hello".Contains("ell");       // true
"hello".Replace("l", "L");    // "heLLo"
"hello".StartsWith("he");     // true
string.IsNullOrWhiteSpace(s);   // null/empty/whitespace checkC#

Control Flow

C# control flow is largely C-style, but the switch expression (C# 8+) is more concise and expression-oriented than the older switch statement — it must be exhaustive and each arm returns a value. Pattern matching inside if lets you test type and destructure in one step, avoiding a separate cast.

// if / else if / else
if (score >= 90)
    grade = "A";
else if (score >= 80)
    grade = "B";
else
    grade = "C";

// Ternary
var status = isEnabled ? "active" : "disabled";

// Switch expression (modern)
var label = statusCode switch
{
    200 => "OK",
    404 => "Not Found",
    >= 500 => "Server Error",
    _ => "Unknown"
};

// Pattern matching
if (device is POSDevice { Status: "online" } pos)
{
    Console.WriteLine(pos.RestaurantId);
}

// Loops
for (int i = 0; i < 10; i++) { }
foreach (var item in collection) { }
while (condition) { }
do { } while (condition);C#

Null Safety

Nullable reference types (enabled by default in .NET 8) make nullability part of the type system — the compiler warns if you pass a possibly-null value where a non-null is expected. The ?. and ?? operators let you write safe null-handling chains without nested null checks.

// Nullable reference types (NRT) — enabled by default in .NET 8
string? maybeNull = null;        // explicitly nullable
string definitelyNotNull = "";   // compiler warns if assigned null

// Null-conditional (?.)
int? length = user?.Name?.Length;

// Null-coalescing (??)
string display = user?.Name ?? "Guest";

// Null-coalescing assignment (??=)
_cache ??= new Dictionary<string, object>();

// Null-forgiving (!.) — "trust me, it's not null"
string value = maybeNull!;       // suppresses warning (use sparingly)

// is null / is not null (prefer over == null)
if (device is not null) { }
if (result is null) { }C#

Exceptions

Exception filters (when clause) let you conditionally catch an exception without unwinding the stack, which preserves the original stack trace for logging. Always use bare throw (not throw ex) when re-throwing — throw ex resets the stack trace and makes debugging much harder.

try
{
    var result = RiskyOperation();
}
catch (HttpRequestException ex) when (ex.StatusCode == 404)
{
    // Exception filter — only catches 404s
    Log($"Not found: {ex.Message}");
}
catch (Exception ex)
{
    Log(ex.Message);
    throw;  // re-throw preserving stack trace
}
finally
{
    Cleanup();
}

// Custom exception
public class DeviceNotFoundException : Exception
{
    public string Serial { get; }
    public DeviceNotFoundException(string serial)
        : base($"Device {serial} not found")
        => Serial = serial;
}C#

Classes

C# classes are reference types with auto-implemented properties, which generate a backing field automatically. The init accessor (C# 9) allows a property to be set only during object initialization, enabling immutable-by-default designs without a constructor for every combination of fields. The required keyword (C# 11) enforces that a property must be provided at the call site.

public class Device
{
    // Properties (auto-implemented)
    public int Id { get; set; }
    public string Serial { get; set; } = "";
    public string Name { get; set; } = "";
    public string Status { get; private set; } = "offline";

    // init-only property (set only during construction)
    public DateTime CreatedAt { get; init; } = DateTime.UtcNow;

    // required (C# 11) — must be set during init
    public required string RestaurantId { get; set; }

    // Constructor
    public Device(string serial, string name)
    {
        Serial = serial;
        Name = name;
    }

    // Method
    public void Activate() => Status = "online";
}

// Inheritance
public class POSDevice : Device
{
    public string TerminalId { get; set; } = "";

    public POSDevice(string serial, string name)
        : base(serial, name) { }
}C#

Records (Immutable DTOs)

Records are ideal for DTOs and value objects — they provide structural (value) equality, immutability, and built-in deconstruction without boilerplate. The with expression performs a shallow copy with specific properties changed, which is the safe way to "modify" an immutable record.

// Record — immutable by default, value equality, with deconstruction
public record DeviceDto(
    int Id,
    string Serial,
    string Name,
    string Status
);

// Create
var dto = new DeviceDto(1, "SN-001", "Till 1", "online");

// Non-destructive mutation with 'with'
var updated = dto with { Status = "offline" };

// Value equality (not reference)
dto == new DeviceDto(1, "SN-001", "Till 1", "online")  // true

// Deconstruction
var (id, serial, _, _) = dto;C#

Interfaces & Abstracts

Interfaces define the contract that a class must fulfil and are the primary tool for dependency injection and testability in C#. Abstract classes provide shared implementation but cannot be instantiated directly. Prefer interfaces when you only need a contract; use abstract classes when you want to share code across derived types.

// Interface — contract only
public interface IDeviceService
{
    Task<Device?> GetByIdAsync(int id);
    Task<IEnumerable<Device>> GetAllAsync();
    Task<Device> CreateAsync(CreateDeviceRequest request);

    // Default interface method (C# 8+)
    string GetDisplayName(Device d) => $"{d.Name} ({d.Serial})";
}

// Abstract class — partial implementation
public abstract class BaseRepository<T>
{
    protected readonly AppDbContext _db;

    protected BaseRepository(AppDbContext db) => _db = db;

    public abstract Task<T?> GetByIdAsync(int id);

    public virtual async Task<List<T>> GetAllAsync()
        => await _db.Set<T>().ToListAsync();
}C#

Enums

Enums give named, type-safe constants instead of raw integers or strings. The [Flags] attribute enables bitwise combination of values — each member should be a power of 2. Use HasFlag() to test whether a flag is set rather than raw bitwise operations for clarity.

public enum DeviceStatus
{
    Offline,
    Online,
    Maintenance,
    Decommissioned
}

// Flags enum (bitwise combinations)
[Flags]
public enum Permissions
{
    None     = 0,
    Read     = 1,
    Write    = 2,
    Delete   = 4,
    Admin    = Read | Write | Delete
}

var perms = Permissions.Read | Permissions.Write;
bool canWrite = perms.HasFlag(Permissions.Write); // trueC#

Generics

Generics let you write type-safe, reusable code without boxing or casting. The where T : class constraint restricts the type parameter to reference types, enabling nullable reference type checks. This Result<T> pattern is a common alternative to throwing exceptions for expected error cases.

// Generic class with constraint
public class Result<T> where T : class
{
    public T? Value { get; }
    public string? Error { get; }
    public bool IsSuccess => Error is null;

    private Result(T? value, string? error)
        => (Value, Error) = (value, error);

    public static Result<T> Ok(T value) => new(value, null);
    public static Result<T> Fail(string error) => new(null, error);
}

// Usage
var result = Result<Device>.Ok(device);
var failure = Result<Device>.Fail("Not found");C#

Pattern Matching

Pattern matching extends the is operator and switch expressions to test both type and shape in a single expression. Property patterns match against named properties, and relational patterns (<, >=) work directly in switch arms. List patterns (C# 11) can destructure array contents, including a .. spread to match any number of middle elements.

// Type patterns
if (shape is Circle c)
    Console.WriteLine(c.Radius);

// Property patterns
if (order is { Total: > 100, Status: "pending" })
    ApplyDiscount(order);

// Relational & logical patterns
string Classify(int temp) => temp switch
{
    < 0              => "Freezing",
    >= 0 and < 15   => "Cold",
    >= 15 and < 25  => "Moderate",
    >= 25            => "Hot",
};

// List patterns (C# 11)
if (numbers is [var first, .., var last])
    Console.WriteLine($"First: {first}, Last: {last}");C#

Primary Constructors (C# 12)

Primary constructors move constructor parameters to the class declaration, eliminating boilerplate field assignments. The parameters are captured as part of the class scope and can be used anywhere in the class body. This pairs naturally with dependency injection, where services are injected through the constructor.

// Parameters become available throughout the class body
public class DeviceService(
    IDeviceRepository repo,
    ILogger<DeviceService> logger) : IDeviceService
{
    public async Task<Device?> GetByIdAsync(int id)
    {
        logger.LogInformation("Fetching device {Id}", id);
        return await repo.GetByIdAsync(id);
    }
}C#

Collection Expressions (C# 12)

Collection expressions provide a single [...] syntax for initializing arrays, List<T>, Span<T>, and other collection types — the compiler picks the most efficient construction strategy for the target type. The spread operator .. inlines another collection's elements inline without a separate concat call.

// Unified syntax for arrays, lists, spans
int[] nums = [1, 2, 3];
List<string> tags = ["api", "v2"];
Span<int> span = [4, 5, 6];

// Spread operator (..)
int[] combined = [..nums, 4, 5, ..otherNums];C#

Disposable & Using

The using declaration (without braces) disposes the resource at the end of the enclosing scope, keeping code flat and readable. Always use using for anything implementing IDisposable — file handles, database connections, HTTP clients — to guarantee cleanup even if an exception is thrown. For async disposal, use await using.

// using declaration (scoped to block) — no braces needed
using var stream = File.OpenRead("data.json");
using var reader = new StreamReader(stream);
var content = await reader.ReadToEndAsync();
// disposed at end of enclosing scope

// IAsyncDisposable
await using var conn = new SqlConnection(connStr);
await conn.OpenAsync();C#

Tuples & Deconstruction

Value tuples let a method return multiple values without defining a dedicated type. Name the tuple elements for readability — unnamed access via .Item1 / .Item2 gets confusing fast. Deconstruction unpacks a tuple (or any type with a Deconstruct method) into individual variables using var (a, b) = ....

// Named tuples
(string Name, int Age) person = ("Hari", 30);
Console.WriteLine(person.Name);

// Return multiple values
public (bool Success, string? Error) Validate(string input)
{
    if (string.IsNullOrEmpty(input))
        return (false, "Input required");
    return (true, null);
}

// Deconstruct
var (success, error) = Validate("hello");C#

Core Collections

var items = new List<string> { "a", "b" };
items.Add("c");
items.Remove("a");
items.Insert(0, "z");
items.Contains("b");  // true
items.Count;           // 2

var map = new Dictionary<string, int>
{
    ["apples"] = 5,
    ["bananas"] = 3
};
map.TryGetValue("apples", out var count);
map.ContainsKey("pears"); // false

var tags = new HashSet<string>
    { "api", "v2" };
tags.Add("api");   // false (dup)
tags.Contains("v2"); // O(1)

var q = new Queue<string>();
q.Enqueue("first");
q.Dequeue();  // "first"

var s = new Stack<int>();
s.Push(1); s.Pop(); // 1

LINQ — Language Integrated Query

LINQ methods are lazily evaluated — they build a query pipeline that only executes when you call a terminal operator like ToList(), Count(), or First(). When used with EF Core, the pipeline translates to SQL; when used on in-memory collections, it runs as C# lambdas. Calling ToList() mid-chain forces immediate execution and materialises the results.

var devices = GetDevices();

// Filtering
var online = devices.Where(d => d.Status == "online");

// Projection (transform)
var names = devices.Select(d => d.Name);
var dtos = devices.Select(d => new DeviceDto(d.Id, d.Serial, d.Name, d.Status));

// Ordering
var sorted = devices.OrderBy(d => d.Name).ThenByDescending(d => d.CreatedAt);

// Aggregation
int total = devices.Count();
int onlineCount = devices.Count(d => d.Status == "online");
bool anyOffline = devices.Any(d => d.Status == "offline");
decimal maxPrice = orders.Max(o => o.Total);

// Single items
var first = devices.First();
var maybe = devices.FirstOrDefault(d => d.Serial == "SN-999");
var single = devices.Single(d => d.Id == 42);  // throws if 0 or 2+

// Grouping
var byRestaurant = devices
    .GroupBy(d => d.RestaurantId)
    .Select(g => new { Restaurant = g.Key, Count = g.Count() });

// Pagination
var page = devices.Skip(20).Take(10);

// Flattening nested collections
var allOrders = restaurants.SelectMany(r => r.Orders);

// Chaining — build complex queries fluently
var report = devices
    .Where(d => d.CreatedAt > DateTime.UtcNow.AddDays(-30))
    .GroupBy(d => d.RestaurantId)
    .Select(g => new { g.Key, Total = g.Count(), Online = g.Count(d => d.Status == "online") })
    .OrderByDescending(x => x.Total)
    .ToList();C#

Useful LINQ One-Liners

These utilities handle common data-shaping tasks concisely. ToDictionary throws if two elements share the same key — use ToLookup when a key can map to multiple values. Chunk is useful for batching operations such as bulk database inserts.

// To dictionary
var lookup = devices.ToDictionary(d => d.Serial, d => d);

// To lookup (one-to-many)
var grouped = devices.ToLookup(d => d.RestaurantId);

// Distinct
var uniqueTags = orders.SelectMany(o => o.Tags).Distinct();

// Zip two sequences
var pairs = names.Zip(scores, (n, s) => new { Name = n, Score = s });

// Chunk (C# 12 / .NET 8)
var batches = devices.Chunk(50);  // IEnumerable<Device[]>C#

C#'s async/await model is built on Task and Task<T> — when you await a task, the current thread is released back to the thread pool until the result is ready, enabling high-throughput I/O without blocking threads. Use Task.WhenAll to fan out multiple async calls concurrently instead of awaiting them one by one. Always accept a CancellationToken in long-running methods so callers can cancel gracefully.

// Basic async method
public async Task<Device?> GetDeviceAsync(int id)
{
    using var client = new HttpClient();
    var json = await client.GetStringAsync($"https://api.example.com/devices/{id}");
    return JsonSerializer.Deserialize<Device>(json);
}

// Concurrent calls (like Promise.all)
public async Task<DashboardData> GetDashboardAsync()
{
    var devicesTask = _deviceService.GetAllAsync();
    var alertsTask = _alertService.GetActiveAsync();
    var statsTask = _statsService.GetTodayAsync();

    await Task.WhenAll(devicesTask, alertsTask, statsTask);

    return new DashboardData(
        devicesTask.Result,
        alertsTask.Result,
        statsTask.Result
    );
}

// Async streams (IAsyncEnumerable)
public async IAsyncEnumerable<Device> StreamDevicesAsync()
{
    await foreach (var device in _db.Devices.AsAsyncEnumerable())
    {
        yield return device;
    }
}

// Cancellation
public async Task LongRunningAsync(CancellationToken ct)
{
    for (int i = 0; i < 100; i++)
    {
        ct.ThrowIfCancellationRequested();
        await Task.Delay(100, ct);
    }
}C#

Project Structure

A conventional ASP.NET Core Web API separates concerns into Controllers (routing and HTTP), Services (business logic), Data (EF Core context), DTOs (request/response shapes), and Middleware (cross-cutting concerns). Keeping this structure consistent makes onboarding and testing straightforward.

MyApi/
├── Program.cs                // App entry + DI + middleware
├── appsettings.json
├── Controllers/
│   └── DevicesController.cs
├── Models/
│   └── Device.cs             // EF Core entities
├── DTOs/
│   ├── CreateDeviceRequest.cs
│   └── DeviceResponse.cs     // Records
├── Services/
│   ├── IDeviceService.cs
│   └── DeviceService.cs
├── Data/
│   └── AppDbContext.cs
└── Middleware/
    └── ExceptionMiddleware.csstructure

Minimal API (Program.cs)

Program.cs is the single entry point where you register services into the DI container (builder phase) and configure the middleware pipeline (app phase). The order of middleware registration matters — authentication must come before authorization, and CORS before the endpoint handler.

var builder = WebApplication.CreateBuilder(args);

// Register services (DI container)
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Default")));
builder.Services.AddScoped<IDeviceService, DeviceService>();

// CORS
builder.Services.AddCors(o => o.AddDefaultPolicy(p =>
    p.WithOrigins("https://yourdomain.com")
     .AllowAnyMethod().AllowAnyHeader()));

var app = builder.Build();

// Middleware pipeline
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseCors();
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();

// Minimal API endpoints (alternative to controllers)
app.MapGet("/health", () => Results.Ok(new { status = "ok" }));

app.Run();Program.cs

Controller

The [ApiController] attribute enables automatic model validation, binding source inference, and problem-detail error responses. Route parameters can be constrained with types (e.g. {id:int}) to prevent invalid inputs from reaching the action. CreatedAtAction returns HTTP 201 with a Location header pointing to the newly created resource.

[ApiController]
[Route("api/[controller]")]
public class DevicesController(
    IDeviceService service,
    ILogger<DevicesController> logger) : ControllerBase
{
    // GET api/devices?status=online&page=1&size=20
    [HttpGet]
    public async Task<ActionResult<List<DeviceResponse>>> GetAll(
        [FromQuery] string? status,
        [FromQuery] int page = 1,
        [FromQuery] int size = 20)
    {
        var devices = await service.GetAllAsync(status, page, size);
        return Ok(devices);
    }

    // GET api/devices/42
    [HttpGet("{id:int}")]
    public async Task<ActionResult<DeviceResponse>> GetById(int id)
    {
        var device = await service.GetByIdAsync(id);
        return device is null ? NotFound() : Ok(device);
    }

    // POST api/devices
    [HttpPost]
    public async Task<ActionResult<DeviceResponse>> Create(
        [FromBody] CreateDeviceRequest request)
    {
        var created = await service.CreateAsync(request);
        return CreatedAtAction(nameof(GetById), new { id = created.Id }, created);
    }

    // DELETE api/devices/42
    [HttpDelete("{id:int}")]
    public async Task<IActionResult> Delete(int id)
    {
        await service.DeleteAsync(id);
        return NoContent();
    }
}DevicesController.cs

Dependency Injection

ASP.NET Core has a built-in DI container. Lifetime choice is critical: Singleton services are shared across all requests (must be thread-safe), Scoped services live for one HTTP request (typical for EF Core contexts and service classes), and Transient services are created fresh every time they are requested. Never inject a Scoped service into a Singleton — it will outlive its intended scope.

// Lifetimes
builder.Services.AddSingleton<ICacheService, CacheService>();  // one for entire app
builder.Services.AddScoped<IDeviceService, DeviceService>();    // one per HTTP request
builder.Services.AddTransient<IEmailSender, EmailSender>();    // new every time

// Keyed services (.NET 8)
builder.Services.AddKeyedSingleton<IStorage, S3Storage>("s3");
builder.Services.AddKeyedSingleton<IStorage, AzureStorage>("azure");

// Inject keyed service
public class UploadService(
    [FromKeyedServices("s3")] IStorage storage) { }C#

Middleware

Middleware components form a pipeline: each one calls next(ctx) to pass the request to the next component, or short-circuits by writing a response directly. A global exception-catching middleware placed at the start of the pipeline is the standard way to return consistent error responses and log unhandled exceptions without try/catch in every controller.

public class ExceptionMiddleware(RequestDelegate next, ILogger<ExceptionMiddleware> logger)
{
    public async Task InvokeAsync(HttpContext ctx)
    {
        try
        {
            await next(ctx);
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "Unhandled exception");
            ctx.Response.StatusCode = 500;
            await ctx.Response.WriteAsJsonAsync(new
            {
                error = "Internal server error",
                traceId = ctx.TraceIdentifier
            });
        }
    }
}

// Register: app.UseMiddleware<ExceptionMiddleware>();C#

Configuration & Options

The Options pattern binds a strongly-typed class to a section of appsettings.json, giving you IntelliSense and compile-time safety instead of raw string keys. Inject IOptions<T> for values fixed at startup, IOptionsSnapshot<T> for per-request reloading, or IOptionsMonitor<T> for real-time change notifications in singletons.

// appsettings.json
// { "Api": { "Key": "secret", "BaseUrl": "https://..." } }

public class ApiSettings
{
    public string Key { get; set; } = "";
    public string BaseUrl { get; set; } = "";
}

// Register
builder.Services.Configure<ApiSettings>(
    builder.Configuration.GetSection("Api"));

// Inject
public class ExternalApiClient(IOptions<ApiSettings> options)
{
    private readonly ApiSettings _settings = options.Value;
}C#

DbContext & Entities

DbContext is the unit-of-work and repository combined — it tracks entity changes and translates LINQ queries into SQL. Configure entity shape and relationships in OnModelCreating using the Fluent API, which is more explicit than data annotations and keeps configuration out of your model classes. Register DbContext as Scoped so a new instance is created per HTTP request.

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Device> Devices => Set<Device>();
    public DbSet<Restaurant> Restaurants => Set<Restaurant>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity<Device>(e =>
        {
            e.HasKey(d => d.Id);
            e.HasIndex(d => d.Serial).IsUnique();
            e.Property(d => d.Name).HasMaxLength(100).IsRequired();
            e.HasOne(d => d.Restaurant)
             .WithMany(r => r.Devices)
             .HasForeignKey(d => d.RestaurantId);
        });
    }
}AppDbContext.cs

CRUD Operations

EF Core's change tracker automatically detects modifications to tracked entities, so you update a property and call SaveChangesAsync — no explicit UPDATE statement needed. Use AsNoTracking() for read-only queries to skip change tracking overhead. The bulk ExecuteUpdateAsync / ExecuteDeleteAsync methods (.NET 7+) bypass the change tracker entirely for efficient mass operations.

// READ
var device = await _db.Devices.FindAsync(id);
var all = await _db.Devices
    .Where(d => d.Status == "online")
    .Include(d => d.Restaurant)         // eager load nav property
    .OrderBy(d => d.Name)
    .Skip(20).Take(10)
    .ToListAsync();

// AsNoTracking for read-only queries (performance)
var readOnly = await _db.Devices
    .AsNoTracking()
    .ToListAsync();

// CREATE
var entity = new Device { Serial = "SN-001", Name = "Till 1" };
_db.Devices.Add(entity);
await _db.SaveChangesAsync();  // entity.Id now set

// UPDATE
var device = await _db.Devices.FindAsync(id);
device!.Status = "maintenance";
await _db.SaveChangesAsync();  // change tracker detects modification

// Bulk update (.NET 7+)
await _db.Devices
    .Where(d => d.Status == "offline")
    .ExecuteUpdateAsync(s => s.SetProperty(d => d.Status, "decommissioned"));

// DELETE
_db.Devices.Remove(device);
await _db.SaveChangesAsync();

// Bulk delete
await _db.Devices
    .Where(d => d.CreatedAt < cutoff)
    .ExecuteDeleteAsync();C#

Migrations

DDL — Data Definition

DDL statements define the schema — tables, columns, constraints, and indexes. IDENTITY(1,1) (SQL Server) or SERIAL / GENERATED ALWAYS AS IDENTITY (PostgreSQL) auto-increments the primary key. Always add indexes on foreign key columns and any columns that appear frequently in WHERE clauses — the database will not create them automatically for you.

CREATE TABLE Devices (
    Id          INT PRIMARY KEY IDENTITY(1,1),
    Serial      NVARCHAR(50)  NOT NULL UNIQUE,
    Name        NVARCHAR(100) NOT NULL,
    RestaurantId INT          NOT NULL,
    Status      NVARCHAR(20)  NOT NULL DEFAULT 'offline',
    CreatedAt   DATETIME2     NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT FK_Device_Restaurant
        FOREIGN KEY (RestaurantId) REFERENCES Restaurants(Id)
);

CREATE INDEX IX_Devices_Status ON Devices(Status);
CREATE INDEX IX_Devices_Restaurant ON Devices(RestaurantId);

-- Alter
ALTER TABLE Devices ADD FirmwareVersion NVARCHAR(20) NULL;
ALTER TABLE Devices DROP COLUMN FirmwareVersion;SQL

DML — Queries

DML covers the everyday operations: SELECT, INSERT, UPDATE, and DELETE. The logical query processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — understanding this explains why you can't reference a SELECT alias in a WHERE clause. Always include a WHERE clause on UPDATE and DELETE to avoid accidentally affecting every row in the table.

-- SELECT basics
SELECT Id, Serial, Name, Status
FROM Devices
WHERE Status = 'online'
  AND CreatedAt > '2025-01-01'
ORDER BY Name ASC;

-- Aliases
SELECT
    d.Name AS DeviceName,
    r.Name AS RestaurantName
FROM Devices d
JOIN Restaurants r ON d.RestaurantId = r.Id;

-- Filtering
WHERE Status IN ('online', 'maintenance')
WHERE Name LIKE 'Till%'                 -- starts with
WHERE CreatedAt BETWEEN '2025-01-01' AND '2025-12-31'
WHERE FirmwareVersion IS NULL
WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE DeviceId = d.Id)

-- INSERT
INSERT INTO Devices (Serial, Name, RestaurantId)
VALUES ('SN-001', 'Till 1', 42);

-- UPDATE
UPDATE Devices
SET Status = 'maintenance',
    Name = 'Till 1 (Primary)'
WHERE Id = 1;

-- DELETE
DELETE FROM Devices WHERE Status = 'decommissioned';SQL

JOINs

JOINs combine rows from two tables based on a related column. INNER JOIN returns only rows with a match in both tables; LEFT JOIN returns all rows from the left table plus matching rows from the right (NULLs where there is no match). A self-join treats the same table as two separate sets, which is useful for hierarchical data like employee-manager relationships.

-- INNER JOIN — only matching rows
SELECT d.*, r.Name AS RestaurantName
FROM Devices d
INNER JOIN Restaurants r ON d.RestaurantId = r.Id;

-- LEFT JOIN — all devices, even those without a restaurant
SELECT d.*, r.Name AS RestaurantName
FROM Devices d
LEFT JOIN Restaurants r ON d.RestaurantId = r.Id;

-- Self-join
SELECT
    e.Name AS Employee,
    m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;

-- Multi-table join
SELECT
    o.Id AS OrderId,
    d.Serial,
    r.Name AS Restaurant,
    oi.ProductName,
    oi.Quantity
FROM Orders o
JOIN Devices d ON o.DeviceId = d.Id
JOIN Restaurants r ON d.RestaurantId = r.Id
JOIN OrderItems oi ON o.Id = oi.OrderId;SQL

Aggregation & Grouping

Aggregate functions collapse multiple rows into a single value per group. GROUP BY defines the grouping key; every column in SELECT must either be in GROUP BY or wrapped in an aggregate function. HAVING filters after aggregation (unlike WHERE, which filters before), making it the right place to filter on aggregate results like COUNT(*) > 5.

-- COUNT, SUM, AVG, MIN, MAX
SELECT
    RestaurantId,
    COUNT(*) AS DeviceCount,
    SUM(CASE WHEN Status = 'online' THEN 1 ELSE 0 END) AS OnlineCount
FROM Devices
GROUP BY RestaurantId
HAVING COUNT(*) > 5
ORDER BY DeviceCount DESC;

-- Revenue report
SELECT
    DATEPART(MONTH, o.CreatedAt) AS Month,
    r.Name AS Restaurant,
    SUM(o.Total) AS Revenue,
    COUNT(DISTINCT o.Id) AS OrderCount,
    AVG(o.Total) AS AvgOrderValue
FROM Orders o
JOIN Restaurants r ON o.RestaurantId = r.Id
WHERE o.CreatedAt >= '2025-01-01'
GROUP BY DATEPART(MONTH, o.CreatedAt), r.Name
ORDER BY Month, Revenue DESC;SQL

Subqueries

Subqueries are SELECT statements nested inside another query. A scalar subquery returns exactly one value and can be used anywhere an expression is valid. A correlated subquery references a column from the outer query and re-executes for each outer row — powerful but potentially slow on large tables; consider using a JOIN or CTE instead for better performance.

-- Scalar subquery
SELECT *
FROM Devices
WHERE RestaurantId = (
    SELECT Id FROM Restaurants WHERE Name = 'Pizza Palace'
);

-- IN subquery
SELECT * FROM Restaurants
WHERE Id IN (
    SELECT DISTINCT RestaurantId
    FROM Orders
    WHERE Total > 500
);

-- Correlated subquery
SELECT d.*
FROM Devices d
WHERE d.CreatedAt = (
    SELECT MAX(d2.CreatedAt)
    FROM Devices d2
    WHERE d2.RestaurantId = d.RestaurantId
);SQL

CTEs — Common Table Expressions

A CTE (introduced with WITH) is a named, temporary result set scoped to a single query. CTEs make complex queries far more readable by breaking them into named steps, and they can be referenced multiple times in the same query. Recursive CTEs add a self-referential UNION ALL step, making it straightforward to traverse hierarchical data like organisation charts or category trees.

-- Named temporary result set — cleaner than subqueries
WITH MonthlyRevenue AS (
    SELECT
        RestaurantId,
        DATEPART(MONTH, CreatedAt) AS Month,
        SUM(Total) AS Revenue
    FROM Orders
    WHERE YEAR(CreatedAt) = 2025
    GROUP BY RestaurantId, DATEPART(MONTH, CreatedAt)
),
Ranked AS (
    SELECT *,
        RANK() OVER(PARTITION BY Month ORDER BY Revenue DESC) AS Rnk
    FROM MonthlyRevenue
)
SELECT * FROM Ranked WHERE Rnk <= 3;

-- Recursive CTE (e.g., org chart / category tree)
WITH RECURSIVE OrgChart AS (
    -- Anchor: top-level managers
    SELECT Id, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive: employees under each manager
    SELECT e.Id, e.Name, e.ManagerId, oc.Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerId = oc.Id
)
SELECT * FROM OrgChart ORDER BY Level, Name;SQL

Window Functions

Window functions use an OVER clause to define the "window" of rows to compute against. PARTITION BY divides rows into groups (like GROUP BY, but without collapsing), and ORDER BY within OVER defines row ordering for ranking or running-total calculations. RANK leaves gaps after ties; DENSE_RANK does not; ROW_NUMBER assigns a unique number regardless of ties.

-- ROW_NUMBER / RANK / DENSE_RANK
SELECT
    Name,
    Total,
    ROW_NUMBER() OVER(ORDER BY Total DESC) AS RowNum,
    RANK()       OVER(ORDER BY Total DESC) AS Rank,
    DENSE_RANK() OVER(ORDER BY Total DESC) AS DenseRank
FROM Orders;

-- Partition — rank within each restaurant
SELECT
    r.Name AS Restaurant,
    o.Total,
    ROW_NUMBER() OVER(
        PARTITION BY o.RestaurantId
        ORDER BY o.Total DESC
    ) AS RankInRestaurant
FROM Orders o
JOIN Restaurants r ON o.RestaurantId = r.Id;

-- Running totals
SELECT
    CreatedAt,
    Total,
    SUM(Total) OVER(ORDER BY CreatedAt) AS RunningTotal
FROM Orders;

-- LAG / LEAD — access previous/next row
SELECT
    CreatedAt,
    Total,
    LAG(Total, 1) OVER(ORDER BY CreatedAt) AS PrevTotal,
    Total - LAG(Total, 1) OVER(ORDER BY CreatedAt) AS Diff
FROM Orders;

-- Moving average (last 7 entries)
SELECT
    CreatedAt,
    Total,
    AVG(Total) OVER(
        ORDER BY CreatedAt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS MovingAvg7
FROM Orders;SQL

MERGE (Upsert)

MERGE performs an "upsert" — updating existing rows and inserting new ones in a single atomic statement. It matches rows between the target and source tables on a key, then applies different actions for matched and unmatched cases. Be aware that MERGE has known edge cases in SQL Server with concurrency; wrapping it in a transaction is recommended for production use.

MERGE INTO Devices AS target
USING StagingDevices AS source
    ON target.Serial = source.Serial
WHEN MATCHED THEN
    UPDATE SET
        target.Name = source.Name,
        target.Status = source.Status
WHEN NOT MATCHED THEN
    INSERT (Serial, Name, RestaurantId, Status)
    VALUES (source.Serial, source.Name, source.RestaurantId, source.Status);SQL

Indexes & Performance

Indexes dramatically speed up reads at the cost of slower writes and additional storage. For composite indexes, column order matters — the index is only used for queries that filter on the leftmost columns. A covering index includes non-key columns via INCLUDE so the engine can satisfy a query entirely from the index without touching the base table (avoiding a key lookup).

-- Composite index (order matters — leftmost columns first)
CREATE INDEX IX_Orders_Restaurant_Date
    ON Orders(RestaurantId, CreatedAt DESC);

-- Covering index (includes columns to avoid table lookup)
CREATE INDEX IX_Devices_Status_Incl
    ON Devices(Status)
    INCLUDE (Serial, Name);

-- Filtered index (partial — only index rows that matter)
CREATE INDEX IX_Devices_Online
    ON Devices(RestaurantId)
    WHERE Status = 'online';

-- Check query plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Or prefix with:
EXPLAIN ANALYZE  -- PostgreSQL
SET SHOWPLAN_ALL ON  -- SQL ServerSQL

Transactions

Transactions group multiple statements into an atomic unit — either all succeed (COMMIT) or all are rolled back (ROLLBACK). This guarantees data consistency for operations that must happen together, such as a funds transfer that must debit one account and credit another. Check @@ERROR after each DML statement (SQL Server) or use structured error handling to decide whether to commit or roll back.

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;

-- Check for errors
IF @@ERROR != 0
    ROLLBACK;
ELSE
    COMMIT;SQL

Stored Procedures

Stored procedures are precompiled SQL routines stored in the database. They reduce network round-trips, centralise query logic, and can accept optional parameters with defaults. SET NOCOUNT ON suppresses the "N rows affected" message, which improves performance when called from application code.

CREATE PROCEDURE usp_GetDevicesByRestaurant
    @RestaurantId INT,
    @Status NVARCHAR(20) = NULL
AS BEGIN
    SET NOCOUNT ON;

    SELECT Id, Serial, Name, Status, CreatedAt
    FROM Devices
    WHERE RestaurantId = @RestaurantId
      AND (@Status IS NULL OR Status = @Status)
    ORDER BY Name;
END;

-- Call it
EXEC usp_GetDevicesByRestaurant @RestaurantId = 42, @Status = 'online';SQL

Useful SQL Patterns

These patterns handle recurring real-world requirements. OFFSET/FETCH is the standard SQL pagination syntax; always pair it with ORDER BY or results are non-deterministic. COALESCE returns the first non-NULL value in its argument list, making it the standard NULL-fallback tool. The conditional SUM pivot pattern avoids the non-standard PIVOT operator and works across all major databases.

-- Pagination (SQL Server / PostgreSQL)
SELECT * FROM Devices
ORDER BY Id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- COALESCE — first non-null
SELECT COALESCE(Nickname, Name, 'Unknown') AS DisplayName
FROM Users;

-- CASE expression
SELECT
    Name,
    CASE
        WHEN Total >= 100 THEN 'High'
        WHEN Total >= 50  THEN 'Medium'
        ELSE 'Low'
    END AS Tier
FROM Orders;

-- Pivot (rows → columns)
SELECT RestaurantId,
    SUM(CASE WHEN Status = 'online' THEN 1 ELSE 0 END) AS OnlineCount,
    SUM(CASE WHEN Status = 'offline' THEN 1 ELSE 0 END) AS OfflineCount
FROM Devices
GROUP BY RestaurantId;

-- String aggregation
SELECT
    RestaurantId,
    STRING_AGG(Name, ', ') AS DeviceNames
FROM Devices
GROUP BY RestaurantId;SQL