docker run -d \
  --name sqlserver \
  -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=YourStrong@Passw0rd" \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2022-latest

Server:   localhost,1433
Login:    sa
Password: YourStrong@Passw0rd

CREATE DATABASE Bookstore;
GO

USE Bookstore;
GO

CREATE TABLE Authors (
    Id        INT IDENTITY(1,1) PRIMARY KEY,
    Name      NVARCHAR(200) NOT NULL,
    Email     NVARCHAR(200) UNIQUE,
    Country   NVARCHAR(100),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE Books (
    Id          INT IDENTITY(1,1) PRIMARY KEY,
    Title       NVARCHAR(300) NOT NULL,
    AuthorId    INT NOT NULL REFERENCES Authors(Id),
    Price       DECIMAL(10, 2) NOT NULL
                    CONSTRAINT CHK_Books_Price CHECK (Price >= 0),
    PublishedAt DATE,
    Genre       NVARCHAR(100),
    InStock     BIT DEFAULT 1
);

INSERT INTO Authors (Name, Email, Country)
VALUES
    (N'Alice Johnson', N'alice@example.com', N'Norway'),
    (N'Bob Smith',     N'bob@example.com',   N'UK');

-- Get the inserted ID
INSERT INTO Books (Title, AuthorId, Price, Genre)
OUTPUT INSERTED.Id
VALUES (N'SQL Server Mastery', 1, 39.99, N'Technology');

-- Variables
DECLARE @AuthorName NVARCHAR(200) = N'Alice Johnson';
DECLARE @BookCount  INT;

-- Set with SELECT
SELECT @BookCount = COUNT(*) FROM Books WHERE AuthorId = 1;

PRINT N'Books by ' + @AuthorName + N': ' + CAST(@BookCount AS NVARCHAR);

-- IF/ELSE
IF @BookCount > 5
    PRINT N'Prolific author';
ELSE IF @BookCount > 0
    PRINT N'Has some books';
ELSE
    PRINT N'No books yet';

-- Basic CTE
WITH TopAuthors AS (
    SELECT
        a.Id,
        a.Name,
        COUNT(b.Id)   AS BookCount,
        AVG(b.Price)  AS AvgPrice
    FROM Authors a
    LEFT JOIN Books b ON b.AuthorId = a.Id
    GROUP BY a.Id, a.Name
)
SELECT Name, BookCount, ROUND(AvgPrice, 2) AS AvgPrice
FROM TopAuthors
WHERE BookCount > 0
ORDER BY BookCount DESC;

-- Recursive CTE — employee hierarchy
WITH EmployeeHierarchy AS (
    -- Anchor: top-level employees (no manager)
    SELECT Id, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive: employees reporting to those above
    SELECT e.Id, e.Name, e.ManagerId, h.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy h ON e.ManagerId = h.Id
)
SELECT Name, Level FROM EmployeeHierarchy ORDER BY Level, Name;

CREATE OR ALTER PROCEDURE usp_GetBooksByGenre
    @Genre    NVARCHAR(100),
    @MaxPrice DECIMAL(10,2) = NULL  -- optional parameter
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        b.Title,
        a.Name AS Author,
        b.Price
    FROM Books b
    JOIN Authors a ON b.AuthorId = a.Id
    WHERE b.Genre = @Genre
      AND (@MaxPrice IS NULL OR b.Price <= @MaxPrice)
    ORDER BY b.Price;
END;
GO

-- Execute
EXEC usp_GetBooksByGenre @Genre = N'Technology', @MaxPrice = 40;

-- ROW_NUMBER — rank books by price within each genre
SELECT
    Title,
    Genre,
    Price,
    ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY Price DESC) AS RankInGenre
FROM Books;

-- Running total
SELECT
    Title,
    Price,
    SUM(Price) OVER (ORDER BY PublishedAt ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Books
WHERE PublishedAt IS NOT NULL
ORDER BY PublishedAt;

-- LAG / LEAD — compare to previous/next row
SELECT
    Title,
    Price,
    LAG(Price,  1, 0) OVER (ORDER BY PublishedAt) AS PrevPrice,
    LEAD(Price, 1, 0) OVER (ORDER BY PublishedAt) AS NextPrice
FROM Books
WHERE PublishedAt IS NOT NULL
ORDER BY PublishedAt;

-- Clustered index (one per table — defines physical order)
-- PRIMARY KEY creates a clustered index by default

-- Nonclustered index
CREATE NONCLUSTERED INDEX IX_Books_Genre
    ON Books (Genre)
    INCLUDE (Title, Price);  -- covering index — includes extra columns

-- Filtered index — only index in-stock books
CREATE NONCLUSTERED INDEX IX_Books_InStock
    ON Books (Price)
    WHERE InStock = 1;

-- Check index usage
SELECT name, type_desc, is_unique FROM sys.indexes WHERE object_id = OBJECT_ID('Books');

-- See query plan
SET STATISTICS IO ON;
SELECT * FROM Books WHERE Genre = N'Technology';

// Install: dotnet add package Microsoft.EntityFrameworkCore.SqlServer

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer")));

// appsettings.json
{
  "ConnectionStrings": {
    "SqlServer": "Server=localhost,1433;Database=Bookstore;User Id=sa;Password=YourStrong@Passw0rd;TrustServerCertificate=True"
  }
}

// EF Core migration
dotnet ef migrations add InitialCreate
dotnet ef database update