SQL & databases

#Keys, constraints and data types

API validation cannot protect data from scripts or racing requests on its own. Database constraints are the final defense for relationships and uniqueness.

-- SQL Server / T-SQL unless a block explicitly says PostgreSQL.
CREATE TABLE Customers (
    Id int IDENTITY PRIMARY KEY,                         -- (1)
    Name nvarchar(100) NOT NULL,
    Email nvarchar(256) NOT NULL UNIQUE
);
CREATE TABLE Orders (
    Id int IDENTITY PRIMARY KEY,
    CustomerId int NOT NULL REFERENCES Customers(Id),    -- (2)
    CreatedAt datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Status varchar(20) NOT NULL,
    Total decimal(18,2) NOT NULL CHECK (Total >= 0)
);
CREATE TABLE OrderLines (
    OrderId int NOT NULL REFERENCES Orders(Id),
    LineNo int NOT NULL,
    ProductId int NOT NULL,
    Quantity int NOT NULL CHECK (Quantity > 0),
    PRIMARY KEY (OrderId, LineNo)                         -- (3)
);

A SQL Server single-column unique constraint usually permits one NULL; PostgreSQL permits multiple NULLs by default and supports NULLS NOT DISTINCT. CHAR is fixed-length; VARCHAR is variable-length and uses collation-dependent encoding, including Unicode with UTF-8 collations. NVARCHAR uses UTF-16, so a character does not always occupy two bytes. Use decimal for money with explicit precision and rounding rules.

#Every join on one sample dataset

A join combines rows by a relationship and can repeat a left row for multiple matches. This is an unconstrained teaching staging dataset: order 13 has a missing customer, which the earlier foreign key would prevent.

CREATE TABLE #Customers (Id int, Name varchar(20));
CREATE TABLE #Orders (Id int, CustomerId int, Total decimal(10,2));
INSERT INTO #Customers VALUES (1,'Ali'),(2,'Mona'),(3,'Omar');
INSERT INTO #Orders VALUES (10,1,500),(11,1,200),(12,2,300),(13,99,50);

-- (1) Matched rows only.
SELECT c.Name, o.Id FROM #Customers c
INNER JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12

-- (2) Preserve the left side.
SELECT c.Name, o.Id FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12; Omar NULL

-- (3) Preserve the right side.
SELECT c.Name, o.Id FROM #Customers c
RIGHT JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12; NULL 13

-- (4) Preserve both sides.
SELECT c.Name, o.Id FROM #Customers c
FULL OUTER JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12; Omar NULL; NULL 13

-- (5) Every combination: 3 customers x 4 orders = 12 rows.
SELECT c.Id AS CustomerId, o.Id AS OrderId
FROM #Customers c CROSS JOIN #Orders o;

-- (6) Self join: unordered pairs of orders belonging to the same customer.
SELECT a.Id, b.Id FROM #Orders a JOIN #Orders b
ON a.CustomerId = b.CustomerId AND a.Id < b.Id;
-- 10 11

-- (7) Anti-join: customers without any order.
SELECT c.Name FROM #Customers c LEFT JOIN #Orders o
ON o.CustomerId = c.Id WHERE o.Id IS NULL;
-- Omar

#Logical query order, WHERE and HAVING

Logical processing explains why a SELECT alias is unavailable in WHERE. This is semantic order, not physical execution order; the optimizer can rearrange work while preserving results.

FROM / JOIN / ON -> WHERE -> GROUP BY -> HAVING
-> SELECT -> DISTINCT -> ORDER BY -> paging / limiting

SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Total) AS Revenue
FROM Orders
WHERE Status = 'Paid' AND CreatedAt >= '20260101'         -- (1)
  AND CreatedAt < '20270101'
GROUP BY CustomerId
HAVING SUM(Total) > 1000                                -- (2)
ORDER BY Revenue DESC, CustomerId;                      -- (3)

COUNT(*) counts rows, COUNT(column) counts non-null values, and COUNT(DISTINCT column) counts distinct non-null values. SUM ignores NULL and returns NULL when no values exist; use COALESCE when the business meaning requires zero.

#Subqueries, EXISTS and recursive CTEs

A subquery expresses a query depending on another result. A CTE names an intermediate step for readability; it is not automatically a materialized temporary table.

SELECT Id, Total FROM Orders
WHERE Total > (SELECT AVG(Total) FROM Orders);           -- (1)

SELECT c.Id FROM Customers c
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);                                                     -- (2)

-- Categories(Id, ParentId, Name); roots have ParentId NULL.
WITH Tree AS (
    SELECT Id, ParentId, Name, 0 AS Depth
    FROM Categories WHERE ParentId IS NULL              -- (3)
    UNION ALL
    SELECT c.Id, c.ParentId, c.Name, t.Depth + 1
    FROM Categories c JOIN Tree t ON c.ParentId = t.Id   -- (4)
)
SELECT Id, Name, Depth FROM Tree OPTION (MAXRECURSION 100);

#Window functions without collapsing rows

Latest orders and running totals often require keeping individual rows. GROUP BY collapses them; window functions compute across related rows while retaining each row.

SELECT Id, CustomerId, Total,
    ROW_NUMBER() OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id) AS rn, -- (1)
    RANK() OVER (ORDER BY Total DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY Total DESC) AS dense_rnk,  -- (2)
    SUM(Total) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id
        ROWS UNBOUNDED PRECEDING) AS RunningTotal,        -- (3)
    LAG(Total) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id) AS PreviousTotal,
    LEAD(Total) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id) AS NextTotal,
    100.0 * Total / NULLIF(SUM(Total) OVER (), 0) AS PercentOfTotal
FROM Orders;

Filter rn in an outer query or CTE, not WHERE at the same level. Sorting and memory grants can be expensive; a suitable index may reduce the cost.

#UNION, DDL and database programmability

The wrong command can remove a schema instead of rows or add unnecessary deduplication.

CREATE OR ALTER VIEW dbo.PaidOrderTotals AS             -- (1)
SELECT CustomerId, SUM(Total) AS Revenue
FROM dbo.Orders WHERE Status = 'Paid'
GROUP BY CustomerId;
-- Run as a separate batch:
SELECT CustomerId, Revenue FROM dbo.PaidOrderTotals;

BEGIN TRANSACTION;
TRUNCATE TABLE dbo.ImportStaging;                       -- (2)
ROLLBACK TRANSACTION;

Procedures can reduce round trips and expose narrow permissions but need versioning and tests. Plan caching also applies to parameterized SQL. Per-row scalar UDFs can cost heavily, although supported inlining changes the outcome; measure. Triggers can support auditing but add hidden behavior and lock duration.

#Normalization from 1NF to BCNF

Repeating a customer's current address across orders creates update anomalies. Normalization organizes facts by dependencies, not simply by table count.

Raw: Orders(OrderId, CustomerId, CustomerCity, Products="Mug,Pen")
1NF: Orders + OrderLines(OrderId, ProductId, ProductName, Qty)
2NF: Products(ProductId, ProductName) + OrderLines without ProductName
3NF: Customers(CustomerId, CurrentCity) + Orders without CurrentCity
Snapshot: OrderLines(UnitPriceAtSale) and Orders(ShippingAddressAtPurchase)

1. 1NF: single values appropriate to the domain, without repeating groups or a list of products in one field.
2. 2NF: 1NF with no non-prime attribute dependent on a proper subset of any candidate key. With an (OrderId, ProductId) line key when each product appears once, ProductName depends only on ProductId.
3. 3NF: for every nontrivial dependency X → A, X is a superkey or A is prime. CustomerId → CurrentCity belongs in Customers.
4. BCNF: every nontrivial determinant must be a superkey. This removes 3NF's exception for prime attributes.

For BCNF, consider ProductSupplier(ProductId, SupplierId, AgentId), with (ProductId, SupplierId) → AgentId and AgentId → SupplierId. Candidate keys are (ProductId, SupplierId) and (ProductId, AgentId). AgentId is not a superkey, although SupplierId is prime, so the relation can satisfy 3NF while violating BCNF. Decomposition into AgentSupplier and ProductAgent is lossless, but preserving all dependencies may need extra enforcement.

Denormalization, such as a summary table, improves reads at a synchronization cost. Sale price and shipping address at purchase are historical facts distinct from current values. Do not add joins simply to maximize normal form without understanding constraints and workload.

#ACID, isolation and transactions in .NET

Orders and inventory may need to change together without violating stock invariants. Atomicity is all-or-nothing; consistency preserves constraints and business invariants; isolation controls interference; durability preserves committed results under the storage and configuration guarantees.

using Microsoft.Data.SqlClient;
using System.Data;

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync(ct);
await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(ct); // (1)
await using var cmd = conn.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = "UPDATE Products SET Available = Available - @qty " +
    "WHERE Id = @id AND Available >= @qty";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = productId;
cmd.Parameters.Add("@qty", SqlDbType.Int).Value = quantity;  // (2)
if (quantity <= 0) throw new ArgumentOutOfRangeException(nameof(quantity));
if (await cmd.ExecuteNonQueryAsync(ct) != 1)
    throw new InvalidOperationException("Reservation failed");
// Insert the order using this same connection and transaction.
await tx.CommitAsync(ct);                                 // (3)

A deadlock is a wait cycle; SQL Server chooses a victim with error 1205. Use consistent resource order, short transactions, deadlock graphs and bounded retries of safe units. Row versioning reduces reader-writer blocking but not all writer locks or deadlocks. PostgreSQL isolation implementations differ; topic 12 covers the deeper details.

#Clustered, covering and filtered indexes

An index resembles a book index: faster lookup, with maintenance on writes. In SQL Server rowstore, clustered leaves contain data rows. Their logical key order does not promise contiguous disk sectors or ordered SELECT output.

-- Assumes Orders has a clustered Id key and a StoreId column.
CREATE INDEX IX_Orders_Store_Created
ON Orders (StoreId, CreatedAt DESC, Id DESC)
INCLUDE (Total, Status);                                 -- (1)

SELECT Id, CreatedAt, Total, Status FROM Orders
WHERE StoreId = @store AND CreatedAt >= @from
ORDER BY CreatedAt DESC, Id DESC;

CREATE INDEX IX_Orders_Pending
ON Orders (CreatedAt) WHERE Status = 'Pending';          -- (2)

A table has one clustered index or is a heap; nonclustered indexes use the clustered key or a heap RID as row locator. A primary key need not be clustered. Covering describes an index relative to a query, not a universal index type. A (StoreId, CreatedAt) index supports its leading prefix; date-only predicates usually lack an efficient direct seek on it. Consider write amplification, storage and statistics.

#Sargability, plans and parameter sensitivity

An existing index is not enough. Predicates, data distribution and estimates determine whether it is useful.

SELECT Id FROM Orders WHERE YEAR(CreatedAt) = 2026;

SELECT Id FROM Orders
WHERE CreatedAt >= '20260101' AND CreatedAt < '20270101';

Sargable predicates permit useful index access; they do not force a seek. Watch implicit conversions, leading wildcards such as LIKE '%pen', and collation. Computed or expression indexes can support specific expressions, so functions do not universally prevent every index.

1. Find the query through Query Store or traces and record duration, logical reads, rows and waits.
2. Inspect the actual plan for estimate errors, spills, memory grants, repeated lookups and blocking.
3. Reduce columns and rows, fix N+1, rewrite predicates, tune indexes and inspect statistics.
4. Test varied parameters and production-like volumes. A scan can be right for many rows; a seek can still read extensively.
5. Compare before and after, including write costs and plan stability; use caching or summaries when measurements justify them.

Parameter sniffing: compilation uses current parameter values to improve estimates, but a plan for a small store may perform poorly for a huge one. Compare compiled and runtime values. OPTION(RECOMPILE) costs compilation CPU; OPTIMIZE FOR UNKNOWN may produce a poor compromise. Query Store forcing or query variants require measurement. Modern SQL Server offers Parameter Sensitive Plan optimization for eligible queries under suitable versions and compatibility levels, not every workload.

#Practice queries: prices, rankings and reports

These exercises assume Products(Id, CategoryId, ParentProductId, Price), Customers(Id, Email), Orders(Id, CustomerId, StoreId, CreatedAt, Status, Total), OrderLines(OrderId, ProductId, Quantity), and AccountEntries(Id, CustomerId, CreatedAt, Amount). Duplicate-email exercises use import staging before uniqueness is enforced. Familiar salary patterns are expressed as product-price questions to keep one domain.

-- (1) Second DISTINCT highest price; NULL if none exists.
SELECT MAX(Price) AS SecondPrice FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products);

-- (2) Nth distinct price, including all tied products if needed.
WITH Ranked AS (
    SELECT Id, Price, DENSE_RANK() OVER (ORDER BY Price DESC) AS rnk
    FROM Products WHERE Price IS NOT NULL
)
SELECT DISTINCT Price FROM Ranked WHERE rnk = @N;

WITH Ranked AS (
    SELECT Id, CategoryId, Price,
        RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS rnk
    FROM Products WHERE Price IS NOT NULL
)
SELECT Id, CategoryId, Price FROM Ranked WHERE rnk = 1;   -- (3)

SELECT Email, COUNT(*) AS Copies FROM CustomerImport
WHERE Email IS NOT NULL GROUP BY Email HAVING COUNT(*) > 1; -- (4)

WITH Duplicates AS (
    SELECT Id, Email,
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS rn
    FROM CustomerImport WHERE Email IS NOT NULL
)
DELETE FROM Duplicates WHERE rn > 1;                    -- (5)

SELECT c.Id FROM Customers c WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);                                                     -- (6)

SELECT variant.Id, variant.Price, parent.Price AS ParentPrice
FROM Products variant JOIN Products parent
ON variant.ParentProductId = parent.Id
WHERE variant.Price > parent.Price;                     -- (7)

WITH Monthly AS (
    SELECT DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1) AS MonthStart,
        SUM(Total) AS Revenue
    FROM Orders WHERE Status = 'Paid'
    GROUP BY DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1)
)
SELECT MonthStart, Revenue,
    Revenue - LAG(Revenue) OVER (ORDER BY MonthStart) AS Change
FROM Monthly ORDER BY MonthStart;                       -- (8)

WITH Sales AS (
    SELECT o.StoreId, l.ProductId, SUM(l.Quantity) AS Units
    FROM Orders o JOIN OrderLines l ON l.OrderId = o.Id
    WHERE o.Status = 'Paid'
    GROUP BY o.StoreId, l.ProductId
), Ranked AS (
    SELECT StoreId, ProductId, Units,
        ROW_NUMBER() OVER (
            PARTITION BY StoreId ORDER BY Units DESC, ProductId) AS rn
    FROM Sales
)
SELECT StoreId, ProductId, Units FROM Ranked WHERE rn <= 3; -- (9)

SELECT CustomerId, Id, Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id
        ROWS UNBOUNDED PRECEDING) AS Balance
FROM AccountEntries;                                   -- (10)

SELECT COUNT(CASE WHEN Status = 'Paid' THEN 1 END) AS Paid,
    COUNT(CASE WHEN Status = 'Pending' THEN 1 END) AS Pending,
    COUNT(CASE WHEN Status = 'Cancelled' THEN 1 END) AS Cancelled
FROM Orders;                                           -- (11)

WITH Days AS (
    SELECT DISTINCT CAST(CreatedAt AS date) AS OrderDay FROM Orders
), Islands AS (
    SELECT OrderDay,
        DATEADD(day, -CAST(ROW_NUMBER() OVER (ORDER BY OrderDay) AS int),
            OrderDay) AS IslandKey
    FROM Days
)
SELECT MIN(OrderDay) AS StartDay, MAX(OrderDay) AS EndDay, COUNT(*) AS DayCount
FROM Islands GROUP BY IslandKey ORDER BY StartDay;       -- (12)