CREATE DATABASE database_name
[ ON PRIMARY (
    NAME = logical_name,
    FILENAME = 'path\file.mdf',
    SIZE = size,
    MAXSIZE = max_size,
    FILEGROWTH = growth_increment
  )
]
[ LOG ON (
    NAME = log_logical_name,
    FILENAME = 'path\file.ldf'
  )
];

CREATE DATABASE SalesDB
ON PRIMARY (
    NAME = SalesDB_Data,
    FILENAME = 'C:\SQLData\SalesDB.mdf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = SalesDB_Log,
    FILENAME = 'C:\SQLData\SalesDB_log.ldf',
    SIZE = 20MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 5MB
);

USE SalesDB;
GO

CREATE TABLE Employees (
    EmployeeID   INT           IDENTITY(1,1) PRIMARY KEY,
    FirstName    NVARCHAR(50)  NOT NULL,
    LastName     NVARCHAR(50)  NOT NULL,
    HireDate     DATE          NOT NULL DEFAULT CAST(GETDATE() AS DATE),
    Salary       DECIMAL(10,2) NULL
);

SELECT * FROM Employees;

CREATE TABLE EmployeeContact (
    ContactID   INT PRIMARY KEY,
    EmployeeID  INT UNIQUE NOT NULL,
    Phone       NVARCHAR(20),
    CONSTRAINT fk_emp_contact FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
);

CREATE TABLE Departments (DeptID INT PRIMARY KEY, DeptName NVARCHAR(100) NOT NULL);

CREATE TABLE Staff (
    StaffID INT PRIMARY KEY,
    DeptID  INT NOT NULL,
    Name    NVARCHAR(100),
    CONSTRAINT fk_staff_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

CREATE TABLE Students (StudentID INT PRIMARY KEY, Name NVARCHAR(100));
CREATE TABLE Courses  (CourseID  INT PRIMARY KEY, Title NVARCHAR(100));

CREATE TABLE StudentCourses (
    StudentID INT NOT NULL,
    CourseID  INT NOT NULL,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID)  REFERENCES Courses(CourseID)
);

CREATE TABLE Products (
    ProductID   INT          PRIMARY KEY,
    ProductCode NVARCHAR(20) UNIQUE NOT NULL
);

ALTER TABLE OrderItems
ADD CONSTRAINT fk_product FOREIGN KEY (ProductID)
    REFERENCES Products(ProductID)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE Employees
ADD CONSTRAINT chk_salary   CHECK (Salary > 0),
ADD CONSTRAINT df_hiredate  DEFAULT GETDATE() FOR HireDate;

CREATE TRIGGER trg_PreventNegativeStock
ON Inventory AFTER UPDATE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE Quantity < 0)
    BEGIN
        RAISERROR('Stock cannot be negative.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

CREATE TABLE Categories (
    CategoryID   INT          NOT NULL PRIMARY KEY,   -- entity integrity
    CategoryName NVARCHAR(50) NOT NULL
);

CREATE TABLE Items (
    ItemID     INT           NOT NULL PRIMARY KEY,
    CategoryID INT           NOT NULL,
    ItemName   NVARCHAR(100) NOT NULL,
    CONSTRAINT fk_category FOREIGN KEY (CategoryID)   -- referential integrity
        REFERENCES Categories(CategoryID)
);

CREATE TABLE #SetA (ID INT);
CREATE TABLE #SetB (ID INT);
INSERT INTO #SetA VALUES (1),(2),(3);
INSERT INTO #SetB VALUES (2),(3),(4);

SELECT ID FROM #SetA UNION     SELECT ID FROM #SetB;   -- 1,2,3,4
SELECT ID FROM #SetA INTERSECT SELECT ID FROM #SetB;   -- 2,3
SELECT ID FROM #SetA EXCEPT    SELECT ID FROM #SetB;   -- 1

SELECT a.ID AS A_ID, b.ID AS B_ID FROM #SetA a CROSS JOIN #SetB b; -- 9 rows

-- 3NF: no transitive dependencies
CREATE TABLE Departments (
    DeptID   INT          PRIMARY KEY,
    DeptName NVARCHAR(100) NOT NULL
);

CREATE TABLE Employees_3NF (
    EmpID   INT          PRIMARY KEY,
    EmpName NVARCHAR(100) NOT NULL,
    DeptID  INT          NOT NULL REFERENCES Departments(DeptID)
);

-- Fix: separate repeating products into OrderLines
CREATE TABLE OrderLines (
    OrderID   INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity  INT NOT NULL,
    PRIMARY KEY (OrderID, ProductID)
);

-- De-normalized reporting table (pre-joined for fast reads)
CREATE TABLE OrderSummary (
    OrderID      INT           PRIMARY KEY,
    CustomerName NVARCHAR(100),
    ProductName  NVARCHAR(100),
    TotalAmount  DECIMAL(10,2),
    OrderDate    DATE
);

INSERT INTO OrderSummary
SELECT o.OrderID, c.Name, p.ProductName, o.Total, o.OrderDate
FROM   Orders o
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products  p ON o.ProductID  = p.ProductID;

CREATE VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName + ' ' + LastName AS FullName, DeptID
FROM   Employees
WHERE  IsActive = 1;
GO

-- Application queries only the view
SELECT * FROM vw_ActiveEmployees;

-- Add a column without breaking the existing view
ALTER TABLE Employees ADD Department NVARCHAR(50) NULL;

SELECT * FROM vw_ActiveEmployees;  -- still works unchanged

CREATE VIEW vw_CustomerOrders AS
SELECT c.CustomerID, c.Name, o.OrderID, o.Total
FROM   Customers c
JOIN   Orders o ON o.CustomerID = c.CustomerID;

-- Application always queries the view — isolated from schema changes
SELECT * FROM vw_CustomerOrders WHERE CustomerID = 5;

CREATE TABLE Customers (
    CustomerID   INT           IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    Email        NVARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE Orders (
    OrderID    INT  IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT  NOT NULL REFERENCES Customers(CustomerID),
    OrderDate  DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE)
);

-- View cardinality estimates via execution plan or DMV
SELECT p.rows AS EstimatedRows
FROM   sys.partitions p
WHERE  p.object_id = OBJECT_ID('Employees')
  AND  p.index_id  IN (0, 1);

-- DDL
CREATE TABLE Logs (LogID INT IDENTITY PRIMARY KEY, Msg NVARCHAR(500));

-- DML
INSERT INTO Logs (Msg) VALUES ('App started');
SELECT * FROM Logs;

-- DCL
GRANT SELECT ON Logs TO [ReportUser];
DENY  DELETE ON Logs TO [ReportUser];

-- TCL
BEGIN TRANSACTION;
    UPDATE Logs SET Msg = 'Updated' WHERE LogID = 1;
COMMIT;

-- VULNERABLE – never do this
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Users WHERE Username = ''' + @Input + '''';
EXEC(@sql);

-- SAFE 1: sp_executesql with parameters
EXEC sp_executesql
    N'SELECT * FROM Users WHERE Username = @u',
    N'@u NVARCHAR(100)',
    @u = @username;

-- SAFE 2: stored procedure (parameters are always data, never code)
CREATE PROCEDURE usp_GetUser @Username NVARCHAR(100)
AS
BEGIN
    SELECT UserID, Username, Email FROM Users WHERE Username = @Username;
END;

EXEC usp_GetUser @Username = N'alice';

-- SAFE 3: least privilege — app role can only EXECUTE procs, not access tables directly
GRANT EXECUTE ON usp_GetUser TO [AppRole];

CREATE TABLE Documents (
    DocID      INT           IDENTITY PRIMARY KEY,
    Title      NVARCHAR(200) NOT NULL,
    Body       NVARCHAR(MAX) NOT NULL,
    Attachment VARBINARY(MAX) NULL
);

CREATE TABLE Demo (Code CHAR(10), Username VARCHAR(100));
INSERT INTO Demo VALUES ('ABC', 'alice');

SELECT DATALENGTH(Code)     AS CharLen,     -- 10
       DATALENGTH(Username) AS VarcharLen   -- 5
FROM   Demo;

CREATE TABLE Products (
    ProductID   INT           IDENTITY PRIMARY KEY,
    SKU         CHAR(8)       NOT NULL,
    Name        NVARCHAR(200) NOT NULL,
    Description NVARCHAR(MAX) NULL,
    ImageData   VARBINARY(MAX) NULL
);

INSERT INTO Products (SKU, Name, Description)
VALUES ('SKU-0001', N'Laptop Pro', N'High-performance laptop');

SELECT SKU, Name FROM Products;

-- Simple
CREATE DATABASE CompanyDB;
GO

-- With explicit file placement (SQL Server 2022)
CREATE DATABASE InventoryDB
ON PRIMARY (
    NAME = InventoryDB_Data,
    FILENAME = 'C:\SQLData\InventoryDB.mdf',
    SIZE = 256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB
)
LOG ON (
    NAME = InventoryDB_Log,
    FILENAME = 'C:\SQLData\InventoryDB_log.ldf',
    SIZE = 64MB, MAXSIZE = 2GB, FILEGROWTH = 32MB
);
GO

-- List all databases
SELECT name, state_desc, recovery_model_desc
FROM   sys.databases ORDER BY name;

CREATE TABLE Employees (
    EmployeeID   INT            IDENTITY(1,1)   NOT NULL,
    FirstName    NVARCHAR(50)                   NOT NULL,
    LastName     NVARCHAR(50)                   NOT NULL,
    Email        NVARCHAR(150)                  NOT NULL,
    HireDate     DATE                           NOT NULL DEFAULT CAST(GETDATE() AS DATE),
    Salary       DECIMAL(12,2)                  NOT NULL,
    DeptID       INT                            NULL,
    IsActive     BIT                            NOT NULL DEFAULT 1,
    CONSTRAINT pk_Employees  PRIMARY KEY CLUSTERED (EmployeeID),
    CONSTRAINT uq_Email      UNIQUE (Email),
    CONSTRAINT chk_Salary    CHECK (Salary >= 0),
    CONSTRAINT fk_Dept       FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);
GO

CREATE TABLE Customers (
    CustomerID INT           IDENTITY(1,1) PRIMARY KEY,
    Name       NVARCHAR(100) NOT NULL,
    Email      NVARCHAR(150) UNIQUE,
    Phone      NVARCHAR(20)
);

-- Inspect columns (fields) of a table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'Customers';

-- Drop table (removes all data, indexes, constraints)
DROP TABLE Customers;

-- Safe drop (SQL Server 2016+)
DROP TABLE IF EXISTS Customers;

CREATE TABLE Logs (LogID INT IDENTITY PRIMARY KEY, Msg NVARCHAR(100));
INSERT INTO Logs VALUES ('A'),('B'),('C');

DELETE FROM Logs WHERE Msg = 'A';  -- 2 rows remain, identity unchanged
TRUNCATE TABLE Logs;               -- 0 rows, IDENTITY resets to 1

TRUNCATE TABLE Logs;      -- table exists, empty
DROP TABLE IF EXISTS Logs; -- table no longer exists

-- Add column
ALTER TABLE Employees ADD MiddleName NVARCHAR(50) NULL;

-- Modify column type
ALTER TABLE Employees ALTER COLUMN Phone NVARCHAR(30) NULL;

-- Drop column
ALTER TABLE Employees DROP COLUMN MiddleName;

-- Add CHECK constraint
ALTER TABLE Employees ADD CONSTRAINT chk_HireDate CHECK (HireDate >= '2000-01-01');

-- Drop constraint
ALTER TABLE Employees DROP CONSTRAINT chk_HireDate;

-- Rename column (SQL Server 2022)
EXEC sp_rename 'Employees.Phone', 'PhoneNumber', 'COLUMN';

-- Heap: no primary key / clustered index
CREATE TABLE StagingOrders (
    OrderID  INT,
    Amount   DECIMAL(10,2),
    LoadedAt DATETIME2 DEFAULT SYSDATETIME()
);

-- Verify it is a heap
SELECT name, type_desc
FROM   sys.indexes
WHERE  object_id = OBJECT_ID('StagingOrders')
  AND  type = 0;   -- 0 = HEAP

-- SELECT
SELECT EmployeeID, FirstName, Salary FROM Employees WHERE IsActive = 1;

-- INSERT
INSERT INTO Employees (FirstName, LastName, Email, Salary, DeptID)
VALUES (N'John', N'Smith', N'john@example.com', 75000, 2);

-- UPDATE
UPDATE Employees SET Salary = Salary * 1.10 WHERE DeptID = 2;

-- DELETE
DELETE FROM Employees WHERE IsActive = 0;

CREATE TABLE #S1 (Region NVARCHAR(50), Amount DECIMAL(10,2));
CREATE TABLE #S2 (Region NVARCHAR(50), Amount DECIMAL(10,2));
INSERT INTO #S1 VALUES ('North',1000),('South',2000);
INSERT INTO #S2 VALUES ('South',2000),('East',1500);

SELECT Region, Amount FROM #S1 UNION     SELECT Region, Amount FROM #S2; -- 3 rows
SELECT Region, Amount FROM #S1 UNION ALL SELECT Region, Amount FROM #S2; -- 4 rows

SELECT EmployeeID, FirstName, Salary
FROM   Employees
WHERE  Salary > (SELECT AVG(Salary) FROM Employees);

SELECT e.EmployeeID, e.FirstName, e.Salary
FROM   Employees e
WHERE  e.Salary = (
    SELECT MAX(e2.Salary) FROM Employees e2 WHERE e2.DeptID = e.DeptID
);

WITH Ranked AS (
    SELECT EmployeeID, FirstName, Salary,
           ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rn
    FROM   Employees
)
SELECT EmployeeID, FirstName, Salary FROM Ranked WHERE rn = 1;

SELECT * FROM Orders    WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM Employees WHERE DeptID IN (1, 3, 5);
SELECT * FROM Employees WHERE LastName LIKE 'Sm%';
SELECT * FROM Employees WHERE DeptID IS NULL;

-- Employees who have placed an order in the last 90 days
SELECT e.EmployeeID, e.FirstName
FROM   Employees e
WHERE  EXISTS (
    SELECT 1 FROM Orders o
    WHERE  o.SalesRepID = e.EmployeeID
      AND  o.OrderDate >= DATEADD(DAY, -90, GETDATE())
);

-- Simple CASE
SELECT EmployeeID,
       CASE DeptID
           WHEN 1 THEN 'Engineering'
           WHEN 2 THEN 'Sales'
           WHEN 3 THEN 'HR'
           ELSE        'Other'
       END AS Department
FROM   Employees;

-- Searched CASE
SELECT EmployeeID, Salary,
       CASE
           WHEN Salary < 40000  THEN 'Junior'
           WHEN Salary < 80000  THEN 'Mid-level'
           WHEN Salary < 150000 THEN 'Senior'
           ELSE                      'Executive'
       END AS SalaryBand
FROM   Employees;

-- Cannot do: WHERE HeadCount > 3 (alias defined in SELECT step)
-- Must wrap:
WITH DeptCounts AS (
    SELECT DeptID, COUNT(*) AS HeadCount
    FROM   Employees
    WHERE  IsActive = 1
    GROUP  BY DeptID
)
SELECT DeptID, HeadCount FROM DeptCounts WHERE HeadCount > 3 ORDER BY HeadCount DESC;

-- Headcount and salary stats per department
SELECT DeptID,
       COUNT(*)    AS Headcount,
       SUM(Salary) AS TotalSalary,
       AVG(Salary) AS AvgSalary,
       MAX(Salary) AS MaxSalary,
       MIN(Salary) AS MinSalary
FROM   Employees
WHERE  IsActive = 1
GROUP  BY DeptID
ORDER  BY TotalSalary DESC;

-- Multiple columns
SELECT DeptID, YEAR(HireDate) AS HireYear, COUNT(*) AS Hires
FROM   Employees
GROUP  BY DeptID, YEAR(HireDate)
ORDER  BY DeptID, HireYear;

-- GROUPING SETS: multiple groupings in a single pass
SELECT DeptID, YEAR(HireDate) AS HireYear, COUNT(*) AS Cnt
FROM   Employees
GROUP  BY GROUPING SETS ((DeptID), (YEAR(HireDate)), ());
-- () = grand-total row

-- TOP N rows
SELECT TOP (5) EmployeeID, FirstName, Salary
FROM   Employees ORDER BY Salary DESC;

-- TOP PERCENT
SELECT TOP (10) PERCENT EmployeeID, FirstName, Salary
FROM   Employees ORDER BY Salary DESC;

-- TOP WITH TIES: include all rows that tie on the last value
SELECT TOP (3) WITH TIES EmployeeID, FirstName, Salary
FROM   Employees ORDER BY Salary DESC;

-- DISTINCT: unique department IDs
SELECT DISTINCT DeptID FROM Employees ORDER BY DeptID;

-- DISTINCT on multiple columns (unique combinations)
SELECT DISTINCT DeptID, YEAR(HireDate) AS HireYear
FROM   Employees ORDER BY DeptID, HireYear;

-- COUNT(DISTINCT col) in aggregate
SELECT COUNT(DISTINCT DeptID) AS UniqueDepts FROM Employees;

-- Basic CTE
WITH HighEarners AS (
    SELECT EmployeeID, FirstName, LastName, Salary, DeptID
    FROM   Employees
    WHERE  Salary > 80000
)
SELECT h.FirstName, h.LastName, h.Salary, d.DeptName
FROM   HighEarners h
JOIN   Departments d ON d.DeptID = h.DeptID
ORDER  BY h.Salary DESC;

-- Multiple CTEs in one query
WITH
DeptStats AS (
    SELECT DeptID, AVG(Salary) AS AvgSalary FROM Employees GROUP BY DeptID
),
AboveAvg AS (
    SELECT e.EmployeeID, e.FirstName, e.Salary, e.DeptID
    FROM   Employees e
    JOIN   DeptStats d ON d.DeptID = e.DeptID
    WHERE  e.Salary > d.AvgSalary
)
SELECT FirstName, Salary FROM AboveAvg ORDER BY Salary DESC;

-- Employee hierarchy (manager → direct reports)
WITH OrgChart AS (
    -- Anchor: employees with no manager (top level)
    SELECT EmployeeID, FirstName, ManagerID, 0 AS Level
    FROM   Employees WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: employees whose manager is in OrgChart
    SELECT e.EmployeeID, e.FirstName, e.ManagerID, oc.Level + 1
    FROM   Employees  e
    JOIN   OrgChart  oc ON oc.EmployeeID = e.ManagerID
)
SELECT EmployeeID, FirstName, ManagerID, Level
FROM   OrgChart
ORDER  BY Level, EmployeeID;

-- Generate a date series (today + 30 days)
WITH Dates AS (
    SELECT CAST(GETDATE() AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM Dates WHERE dt < DATEADD(DAY, 30, GETDATE())
)
SELECT dt FROM Dates OPTION (MAXRECURSION 100);  -- default limit = 100 recursions

SELECT DeptID, COUNT(*) AS HeadCount, AVG(Salary) AS AvgSalary
FROM   Employees
WHERE  IsActive = 1          -- filter rows first
GROUP  BY DeptID
HAVING COUNT(*) > 5          -- then filter groups
   AND AVG(Salary) > 60000;

-- Arithmetic
SELECT EmployeeID, Salary, Salary * 1.10 AS Raised, Salary % 12 AS Remainder
FROM   Employees;

-- IN / NOT IN
SELECT * FROM Employees WHERE DeptID IN (1, 2, 3);
SELECT * FROM Employees WHERE DeptID NOT IN (4, 5);

-- BETWEEN (inclusive on both ends)
SELECT * FROM Employees WHERE Salary      BETWEEN 50000 AND 100000;
SELECT * FROM Orders    WHERE OrderDate   BETWEEN '2024-01-01' AND '2024-12-31';

-- IS NULL / IS NOT NULL
SELECT * FROM Employees WHERE ManagerID IS NULL;      -- top-level managers
SELECT * FROM Employees WHERE ManagerID IS NOT NULL;  -- has a manager

-- Logical AND / OR / NOT
SELECT * FROM Employees WHERE IsActive = 1 AND (DeptID = 1 OR DeptID = 3);
SELECT * FROM Employees WHERE NOT (Salary < 50000 OR IsActive = 0);

-- EXISTS
SELECT DeptID, DeptName FROM Departments d
WHERE  EXISTS (
    SELECT 1 FROM Employees e WHERE e.DeptID = d.DeptID AND e.IsActive = 1
);

-- Sort descending
SELECT EmployeeID, LastName, Salary
FROM   Employees
ORDER  BY Salary DESC;

-- Multi-column sort
SELECT EmployeeID, DeptID, LastName, Salary
FROM   Employees
ORDER  BY DeptID ASC, Salary DESC;

-- TOP N (SQL Server)
SELECT TOP (10) EmployeeID, LastName, Salary
FROM   Employees
ORDER  BY Salary DESC;

-- OFFSET-FETCH pagination (page 2, 10 rows per page)
SELECT EmployeeID, LastName, Salary
FROM   Employees
ORDER  BY Salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- Single row
INSERT INTO Employees (FirstName, LastName, Email, Salary, DeptID)
VALUES (N'Alice', N'Johnson', N'alice@example.com', 85000, 1);

-- Multiple rows
INSERT INTO Employees (FirstName, LastName, Email, Salary, DeptID)
VALUES
    (N'Bob',   N'Lee',   N'bob@example.com',   72000, 2),
    (N'Carol', N'White', N'carol@example.com', 91000, 1);

-- INSERT ... SELECT
INSERT INTO EmployeesArchive (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM   Employees WHERE IsActive = 0;

-- Capture generated IDENTITY value
DECLARE @NewID INT;
INSERT INTO Employees (FirstName, LastName, Email, Salary)
VALUES (N'Eve', N'Davis', N'eve@example.com', 78000);
SET @NewID = SCOPE_IDENTITY();
SELECT @NewID AS NewEmployeeID;

-- Single column
UPDATE Employees SET Salary = 90000 WHERE EmployeeID = 5;

-- Multiple columns
UPDATE Employees SET Salary = Salary * 1.05, IsActive = 1 WHERE DeptID = 2;

-- UPDATE with JOIN
UPDATE e
SET    e.DeptID = d.NewDeptID
FROM   Employees e
JOIN   DeptMapping d ON d.OldDeptID = e.DeptID;

-- UPDATE with OUTPUT clause (capture old and new values)
UPDATE Employees
SET    Salary = Salary * 1.10
OUTPUT deleted.EmployeeID, deleted.Salary AS OldSalary, inserted.Salary AS NewSalary
WHERE  DeptID = 1;

-- Best practice: TRY-CATCH with explicit transaction
BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
    COMMIT TRANSACTION;
    PRINT 'Transfer successful.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;   -- re-raise the original error
END CATCH;

INSERT INTO Products (Name, Price) VALUES (N'Widget', 9.99);

INSERT INTO Products (Name, Price) VALUES (N'Gadget', 19.99),(N'Doohickey', 4.99);

INSERT INTO ProductsBackup SELECT * FROM Products;

UPDATE Products SET Price = Price * 1.05;

UPDATE Products SET Price = 0 WHERE IsDiscontinued = 1;

DELETE FROM Products WHERE IsDiscontinued = 1;

-- MERGE (upsert) – SQL Server 2008+
MERGE INTO Products AS target
USING ProductsStaging AS source ON target.ProductID = source.ProductID
WHEN MATCHED     THEN UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN INSERT (Name, Price) VALUES (source.Name, source.Price);

DELETE FROM Orders WHERE OrderDate < '2020-01-01';  -- conditional, triggers fire

TRUNCATE TABLE StagingOrders;                        -- all rows, IDENTITY resets

CREATE TABLE Departments (DeptID INT NOT NULL CONSTRAINT pk_Dept PRIMARY KEY, DeptName NVARCHAR(100) NOT NULL);

CREATE TABLE Employees (
    EmployeeID INT NOT NULL CONSTRAINT pk_Emp PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    DeptID     INT NULL CONSTRAINT fk_EmpDept FOREIGN KEY REFERENCES Departments(DeptID)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Users (
    UserID   INT           IDENTITY PRIMARY KEY,
    Username NVARCHAR(50)  NOT NULL CONSTRAINT uq_Username UNIQUE,
    Email    NVARCHAR(150) NOT NULL CONSTRAINT uq_Email    UNIQUE
);

-- Composite unique key
ALTER TABLE Products ADD CONSTRAINT uq_SupplierSKU UNIQUE (SupplierID, SKU);

CREATE TABLE Orders (
    OrderID    INT  IDENTITY PRIMARY KEY,
    CustomerID INT  NOT NULL
        CONSTRAINT fk_Order_Customer FOREIGN KEY REFERENCES Customers(CustomerID)
        ON DELETE CASCADE ON UPDATE CASCADE,
    OrderDate  DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE)
);

CREATE TABLE Invoices (
    InvoiceID  INT           IDENTITY PRIMARY KEY,
    Amount     DECIMAL(12,2) NOT NULL CHECK (Amount > 0),
    DueDate    DATE          NOT NULL DEFAULT DATEADD(DAY, 30, GETDATE()),
    Status     NVARCHAR(20)  NOT NULL CHECK (Status IN ('Draft','Sent','Paid','Overdue')),
    CustomerID INT           NOT NULL FOREIGN KEY REFERENCES Customers(CustomerID)
);

-- Inline (column-level)
CREATE TABLE Orders (
    OrderID   INT           NOT NULL PRIMARY KEY,
    OrderDate DATE          NOT NULL DEFAULT GETDATE(),
    Amount    DECIMAL(10,2) NOT NULL CHECK (Amount >= 0)
);

-- Table-level (required for composite keys)
CREATE TABLE OrderItems (
    OrderID   INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity  INT NOT NULL CHECK (Quantity > 0),
    CONSTRAINT pk_OrderItems PRIMARY KEY (OrderID, ProductID),
    CONSTRAINT fk_OI_Order   FOREIGN KEY (OrderID)   REFERENCES Orders(OrderID),
    CONSTRAINT fk_OI_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Add to existing table
ALTER TABLE Employees ADD CONSTRAINT chk_HireYear CHECK (YEAR(HireDate) >= 1990);

CREATE TABLE Employees (
    EmployeeID INT           IDENTITY PRIMARY KEY,  -- chosen PK
    Email      NVARCHAR(150) NOT NULL UNIQUE,        -- candidate key 1
    SSN        CHAR(11)      NOT NULL UNIQUE,        -- candidate key 2
    FirstName  NVARCHAR(50)  NOT NULL
);

CREATE TABLE Orders (
    OrderID   INT  IDENTITY PRIMARY KEY,  -- creates CLUSTERED index
    OrderDate DATE NOT NULL
);

-- Verify
SELECT i.name, i.type_desc, i.is_primary_key
FROM   sys.indexes i
WHERE  i.object_id = OBJECT_ID('Orders');
-- type_desc = 'CLUSTERED', is_primary_key = 1

-- OrderItems: (OrderID + ProductID) is unique; neither column alone is
CREATE TABLE OrderItems (
    OrderID   INT           NOT NULL,
    ProductID INT           NOT NULL,
    Quantity  INT           NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10,2) NOT NULL,
    CONSTRAINT pk_OrderItems PRIMARY KEY (OrderID, ProductID),   -- composite PK
    CONSTRAINT fk_OI_Order   FOREIGN KEY (OrderID)   REFERENCES Orders(OrderID),
    CONSTRAINT fk_OI_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Query using composite key
SELECT * FROM OrderItems WHERE OrderID = 1001 AND ProductID = 5;

-- Inspect composite key columns
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE  TABLE_NAME = 'OrderItems' AND CONSTRAINT_NAME = 'pk_OrderItems';

CREATE TABLE Departments (DeptID INT PRIMARY KEY, DeptName NVARCHAR(100) NOT NULL);

CREATE TABLE Employees (
    EmployeeID INT  IDENTITY PRIMARY KEY,
    FirstName  NVARCHAR(50) NOT NULL,
    DeptID     INT  NULL,
    CONSTRAINT fk_Emp_Dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
        ON DELETE SET NULL   -- if dept deleted → employee DeptID becomes NULL
        ON UPDATE CASCADE    -- if dept PK changes → employee FK updated
);

-- Test ON DELETE SET NULL
DELETE FROM Departments WHERE DeptID = 3;
SELECT * FROM Employees WHERE DeptID IS NULL;  -- affected employees

-- Disable / re-enable a FK constraint
ALTER TABLE Employees NOCHECK CONSTRAINT fk_Emp_Dept;  -- disable
ALTER TABLE Employees CHECK   CONSTRAINT fk_Emp_Dept;  -- re-enable

-- Setup
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name NVARCHAR(100));
CREATE TABLE Orders    (OrderID INT PRIMARY KEY, CustomerID INT, Amount DECIMAL(10,2));

INSERT INTO Customers VALUES (1,'Alice'),(2,'Bob'),(3,'Carol');
INSERT INTO Orders    VALUES (1,1,500),(2,1,300),(3,2,750);

-- INNER JOIN: matched rows only
SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c INNER JOIN Orders o ON o.CustomerID = c.CustomerID;

-- LEFT JOIN: all customers, NULL if no orders (Carol appears)
SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c LEFT JOIN Orders o ON o.CustomerID = c.CustomerID;

-- RIGHT JOIN: all orders
SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID;

-- FULL OUTER JOIN: all rows from both tables
SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID;

-- CROSS JOIN: every customer × every order
SELECT c.Name, o.OrderID FROM Customers c CROSS JOIN Orders o;

SELECT e.EmployeeID,
       e.FirstName + ' ' + e.LastName AS Employee,
       m.FirstName + ' ' + m.LastName AS Manager
FROM   Employees e
LEFT   JOIN Employees m ON e.ManagerID = m.EmployeeID;

SELECT s.SizeName, c.ColorName
FROM   Sizes s CROSS JOIN Colors c;
-- 4 sizes × 5 colors = 20 combinations

SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c FULL OUTER JOIN Orders o ON o.CustomerID = c.CustomerID;

SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c INNER JOIN Orders o ON o.CustomerID = c.CustomerID;

SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c LEFT JOIN Orders o ON o.CustomerID = c.CustomerID;
-- Carol has no orders → NULL OrderID, NULL Amount

SELECT c.Name, o.OrderID, o.Amount
FROM   Customers c RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID;

-- Identical queries:
SELECT c.Name, o.Amount FROM Customers c JOIN       Orders o ON o.CustomerID = c.CustomerID;
SELECT c.Name, o.Amount FROM Customers c INNER JOIN Orders o ON o.CustomerID = c.CustomerID;

-- Three equivalent ways — JOIN is typically fastest

-- 1. JOIN
SELECT DISTINCT c.CustomerID, c.Name
FROM   Customers c
JOIN   Orders o ON o.CustomerID = c.CustomerID
WHERE  o.OrderDate >= '2024-01-01';

-- 2. Subquery with IN
SELECT CustomerID, Name FROM Customers
WHERE  CustomerID IN (
    SELECT CustomerID FROM Orders WHERE OrderDate >= '2024-01-01'
);

-- 3. EXISTS (often same plan as JOIN; stops at first match)
SELECT CustomerID, Name FROM Customers c
WHERE  EXISTS (
    SELECT 1 FROM Orders o
    WHERE  o.CustomerID = c.CustomerID AND o.OrderDate >= '2024-01-01'
);

-- Subquery in FROM (derived table) for aggregated filtering
SELECT d.DeptID, d.AvgSalary
FROM  (SELECT DeptID, AVG(Salary) AS AvgSalary
       FROM Employees GROUP BY DeptID) AS d
WHERE  d.AvgSalary > 70000;

SELECT * FROM Employees WHERE LastName LIKE 'S%';             -- starts with S
SELECT * FROM Employees WHERE LastName LIKE '%son';           -- ends with son
SELECT * FROM Employees WHERE Phone    LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]';
SELECT * FROM Products  WHERE SKU      LIKE '[A-Z][A-Z][0-9][0-9][0-9]'; -- e.g. AB012

-- Escape a literal % sign
SELECT * FROM Products WHERE Description LIKE '%50\%%' ESCAPE '\';

-- PATINDEX: returns position of first match (0 if not found)
SELECT PATINDEX('%[0-9]%', 'abc123');   -- returns 4

-- Create non-clustered index
CREATE INDEX ix_Employees_LastName ON Employees (LastName ASC);

-- Composite index
CREATE INDEX ix_Orders_CustomerDate ON Orders (CustomerID, OrderDate DESC);

-- Unique index
CREATE UNIQUE INDEX uix_Users_Email ON Users (Email);

-- Filtered index (index only active employees)
CREATE INDEX ix_ActiveEmployees ON Employees (DeptID, Salary) WHERE IsActive = 1;

-- Drop index
DROP INDEX ix_Employees_LastName ON Employees;

-- List indexes
SELECT i.name, i.type_desc, i.is_unique
FROM   sys.indexes i
WHERE  i.object_id = OBJECT_ID('Employees') AND i.type > 0;

-- Clustered (created by PK by default)
CREATE TABLE Sales (SaleID INT IDENTITY CONSTRAINT pk_Sales PRIMARY KEY CLUSTERED, Amount DECIMAL(10,2));

-- Non-clustered covering index (INCLUDE avoids key lookup)
CREATE NONCLUSTERED INDEX ix_Sales_Date
ON Sales (SaleDate)
INCLUDE (Amount);

CREATE INDEX ix_LastName  ON Employees (LastName);
CREATE UNIQUE INDEX uix_Email ON Employees (Email);
CREATE CLUSTERED INDEX cx_OrderDate ON Orders (OrderDate);

-- Filtered index
CREATE NONCLUSTERED INDEX ix_RecentOrders ON Orders (CustomerID, OrderDate)
WHERE OrderDate >= '2024-01-01';

-- Columnstore index (for analytics — SQL Server 2012+)
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_Sales ON Sales (SaleDate, Amount, ProductID);

-- Maintenance
ALTER INDEX ix_LastName ON Employees REBUILD;        -- fix fragmentation
ALTER INDEX ix_LastName ON Employees REORGANIZE;     -- online, low overhead

-- Check fragmentation
SELECT i.name, s.avg_fragmentation_in_percent
FROM   sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Employees'), NULL, NULL, 'LIMITED') s
JOIN   sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id;

-- SARGable → Index Seek
SELECT * FROM Employees WHERE EmployeeID = 42;          -- equality on PK
SELECT * FROM Orders    WHERE OrderDate >= '2024-01-01'; -- range on indexed col

-- NOT SARGable → forces Index Scan or Table Scan (avoid these)
SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;     -- function on column
SELECT * FROM Employees WHERE Salary + 0 = 75000;        -- arithmetic on column

-- Fix: rewrite to be SARGable
SELECT * FROM Employees WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';
SELECT * FROM Employees WHERE Salary = 75000;

-- Check index usage (seek vs scan counts)
SELECT i.name, s.user_seeks, s.user_scans, s.user_lookups
FROM   sys.dm_db_index_usage_stats s
JOIN   sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE  s.database_id = DB_ID() AND i.object_id = OBJECT_ID('Employees');

-- A table already has a clustered index (PK default)
CREATE TABLE Orders (
    OrderID   INT  IDENTITY CONSTRAINT pk_Orders PRIMARY KEY CLUSTERED,  -- CI #1
    OrderDate DATE NOT NULL
);

-- Attempting to add a second clustered index will FAIL:
-- CREATE CLUSTERED INDEX cx_OrderDate ON Orders (OrderDate);
-- Msg 1902: Cannot create more than one clustered index on table 'Orders'.

-- Solution 1: create a non-clustered index instead
CREATE NONCLUSTERED INDEX ix_OrderDate ON Orders (OrderDate);

-- Solution 2: move the clustered index off the PK
CREATE TABLE Events (
    EventID   INT           IDENTITY NOT NULL,
    EventDate DATE          NOT NULL,
    EventName NVARCHAR(100) NOT NULL,
    CONSTRAINT pk_Events PRIMARY KEY NONCLUSTERED (EventID)  -- PK = non-clustered
);
CREATE CLUSTERED INDEX cx_EventDate ON Events (EventDate);   -- CI on EventDate

-- Query needs: filter on IsActive, return LastName, Salary, DeptID
SELECT LastName, Salary, DeptID FROM Employees WHERE IsActive = 1;

-- Non-covering: index seek + key lookup (extra cost)
CREATE INDEX ix_Active ON Employees (IsActive);

-- Covering: index seek only (no key lookup)
CREATE NONCLUSTERED INDEX ix_Active_Covering
ON Employees (IsActive)
INCLUDE (LastName, Salary, DeptID);   -- INCLUDE = non-key covering columns

-- Multi-column seek + covering
CREATE NONCLUSTERED INDEX ix_Dept_Active_Covering
ON Employees (DeptID, IsActive)       -- seek columns (order matters)
INCLUDE (LastName, Salary);           -- cover SELECT columns

-- Verify in execution plan: look for "Index Seek" with NO "Key Lookup"

SELECT * FROM Employees WHERE LastName LIKE 'Mc%';           -- starts with Mc
SELECT * FROM Employees WHERE LastName LIKE '_mith';          -- one char then mith
SELECT * FROM Contacts  WHERE Phone    LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]';
SELECT * FROM Products  WHERE SKU      LIKE '[^0-9]%';        -- does not start with digit

-- Escape literal %
SELECT * FROM Products WHERE Description LIKE '%50\%%' ESCAPE '\';

-- 5-character SKU exactly
SELECT * FROM Products WHERE SKU LIKE '_____';

SELECT SYSDATETIME()          AS Now_DT2,          -- DATETIME2(7), high precision
       GETDATE()               AS Now_DT,           -- DATETIME
       SYSDATETIMEOFFSET()     AS Now_DTO,          -- with time zone
       FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss') AS Formatted;

SELECT YEAR(GETDATE())  AS Yr, MONTH(GETDATE()) AS Mo, DAY(GETDATE()) AS Dy;

SELECT DATEADD(DAY,   30, GETDATE()) AS In30Days,
       DATEADD(MONTH, -1, GETDATE()) AS OneMonthAgo,
       DATEDIFF(DAY, HireDate, GETDATE()) AS DaysEmployed
FROM   Employees;

-- Safe string-to-date conversion (returns NULL on failure)
SELECT TRY_CAST('2024-06-15' AS DATE);
SELECT CONVERT(DATE, '2024-06-15', 23);  -- style 23 = yyyy-mm-dd

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE BankAccounts SET Balance = Balance - 500 WHERE AccountID = 1;
    UPDATE BankAccounts SET Balance = Balance + 500 WHERE AccountID = 2;

    IF EXISTS (SELECT 1 FROM BankAccounts WHERE Balance < 0)
        THROW 50001, 'Insufficient funds.', 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

-- Enable READ_COMMITTED_SNAPSHOT isolation (removes reader/writer blocking)
ALTER DATABASE CompanyDB SET READ_COMMITTED_SNAPSHOT ON;

SELECT name, is_read_committed_snapshot_on
FROM   sys.databases WHERE name = 'CompanyDB';

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- default

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;         -- for reporting (no blocking)
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01';

-- Monitor current locks
SELECT request_session_id, resource_type, resource_description, request_mode
FROM   sys.dm_tran_locks
WHERE  request_session_id > 50;

BEGIN TRANSACTION;
BEGIN TRY
    -- Step 1: insert order header
    INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, GETDATE());
    DECLARE @OrderID INT = SCOPE_IDENTITY();

    SAVE TRANSACTION after_header;   -- savepoint

    -- Step 2: insert order lines
    INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, 5, 2);
    INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, 8, 1);

    -- Partial rollback: undo lines only, keep header
    -- ROLLBACK TRANSACTION after_header;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

-- 1. Access tables in a consistent order across all transactions

-- 2. Keep transactions short — commit as soon as possible
BEGIN TRANSACTION;
    UPDATE Orders SET Status = 'Processing' WHERE OrderID = @ID;
COMMIT;  -- commit immediately; don\'t hold open during application logic

-- 3. Use READ_COMMITTED_SNAPSHOT isolation (readers don\'t block writers)
ALTER DATABASE CompanyDB SET READ_COMMITTED_SNAPSHOT ON;

-- 4. Add indexes to reduce lock duration
CREATE INDEX ix_OrderStatus ON Orders (Status) INCLUDE (OrderID);

-- 5. Detect deadlocks via Extended Events / System Health session
SELECT xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
       xdr.query('.')                        AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM   sys.dm_xe_session_targets t
    JOIN   sys.dm_xe_sessions s ON s.address = t.event_session_address
    WHERE  s.name = 'system_health' AND t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr);

-- Scalar UDF
CREATE FUNCTION dbo.fn_FullName (@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
WITH SCHEMABINDING
AS
BEGIN
    RETURN LTRIM(RTRIM(@First)) + N' ' + LTRIM(RTRIM(@Last));
END;
GO

SELECT dbo.fn_FullName(FirstName, LastName) AS FullName FROM Employees;

-- Inline table-valued function (best performance — inlined by optimiser)
CREATE FUNCTION dbo.fn_EmployeesByDept (@DeptID INT)
RETURNS TABLE WITH SCHEMABINDING
AS
    RETURN SELECT EmployeeID, FirstName, LastName, Salary
           FROM   dbo.Employees WHERE DeptID = @DeptID;
GO

SELECT * FROM dbo.fn_EmployeesByDept(2);

SELECT
    COUNT(*)               AS TotalRows,
    COUNT(DISTINCT Region) AS UniqueRegions,
    SUM(Amount)            AS TotalRevenue,
    AVG(Amount)            AS AvgSale,
    MIN(Amount)            AS MinSale,
    MAX(Amount)            AS MaxSale,
    STDEV(Amount)          AS StdDev
FROM Sales;

-- Grouped aggregates
SELECT Region, COUNT(*) AS Cnt, SUM(Amount) AS Total, AVG(Amount) AS Avg
FROM   Sales
GROUP  BY Region
HAVING SUM(Amount) > 2000
ORDER  BY Total DESC;

-- STRING_AGG (SQL Server 2017+)
SELECT DeptID,
       STRING_AGG(FirstName, ', ') WITHIN GROUP (ORDER BY FirstName) AS Team
FROM   Employees
GROUP  BY DeptID;

-- Scalar functions
SELECT
    LEN(N'Hello World')                 AS Len,          -- 11
    UPPER(N'hello')                     AS Upper,        -- HELLO
    TRIM(N'  spaces  ')                 AS Trimmed,      -- spaces
    REPLACE(N'foo bar', N'bar', N'baz') AS Replaced,     -- foo baz
    CONCAT(N'SQL', N' ', N'Server')     AS Concat,       -- SQL Server
    ROUND(3.14159, 2)                   AS Rounded,      -- 3.14
    ABS(-42)                            AS AbsVal,       -- 42
    ISNULL(NULL, N'default')            AS NullCheck,    -- default
    COALESCE(NULL, NULL, N'first')      AS Coalesced;    -- first

SELECT ISNULL(NULL, 'default');           -- 'default'
SELECT COALESCE(NULL, NULL, 'first');     -- 'first'

-- COALESCE with multiple fallbacks
SELECT EmployeeID,
       COALESCE(PreferredName, NickName, FirstName, 'Unknown') AS DisplayName
FROM   Employees;

-- ISNULL in aggregates (treats NULL as 0)
SELECT AVG(ISNULL(Bonus, 0)) AS AvgBonusInclNull FROM Employees;
SELECT AVG(Bonus)            AS AvgBonusExclNull FROM Employees;  -- skips NULLs

-- COALESCE in UPDATE
UPDATE Employees
SET    Phone = COALESCE(MobilePhone, OfficePhone, 'N/A')
WHERE  Phone IS NULL;

-- LEN: character count (excludes trailing spaces; use DATALENGTH for bytes)
SELECT LEN('Hello World');                   -- 11
SELECT DATALENGTH(N'Hello');                 -- 10 bytes (Unicode, 2 bytes/char)

-- SUBSTRING(string, start, length)  -- 1-based index
SELECT SUBSTRING('SQL Server 2022', 5, 6);  -- 'Server'

-- Extract username from email
SELECT SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS Username
FROM   Employees;

-- CHARINDEX(find, string [, startPos])  -- returns 0 if not found
SELECT CHARINDEX('Server', 'SQL Server 2022');  -- 5
SELECT CHARINDEX('@', 'alice@example.com');      -- 6

-- Extract domain from email
SELECT Email,
       SUBSTRING(Email,
                 CHARINDEX('@', Email) + 1,
                 LEN(Email) - CHARINDEX('@', Email)) AS Domain
FROM   Employees;

-- LEFT / RIGHT shortcuts
SELECT LEFT('SQL Server', 3);   -- 'SQL'
SELECT RIGHT('SQL Server', 6);  -- 'Server'

-- PATINDEX: find position of a pattern (0 = not found)
SELECT PATINDEX('%[0-9]%', 'abc123');  -- 4

-- CAST
SELECT CAST(12345   AS NVARCHAR(10));   -- '12345'
SELECT CAST('2024-06-15' AS DATE);      -- 2024-06-15
SELECT CAST(3.14159 AS DECIMAL(5,2));   -- 3.14

-- CONVERT with date styles
SELECT CONVERT(NVARCHAR(20), GETDATE(), 103);  -- dd/mm/yyyy
SELECT CONVERT(NVARCHAR(20), GETDATE(), 23);   -- yyyy-mm-dd
SELECT CONVERT(NVARCHAR(20), GETDATE(), 108);  -- hh:mm:ss

-- TRY_CAST / TRY_CONVERT – return NULL on failure instead of error
SELECT TRY_CAST('abc'        AS INT);   -- NULL (no error)
SELECT TRY_CONVERT(INT, 'abc');         -- NULL
SELECT TRY_CAST('2024-06-15' AS DATE);  -- 2024-06-15

-- Deterministic (safe in persisted columns / indexed views)
SELECT LEN('hello');                        -- always 5
SELECT UPPER('sql');                        -- always 'SQL'
SELECT DATEADD(DAY, 7, '2024-01-01');       -- always '2024-01-08'

-- Non-deterministic (avoid in indexed views / persisted columns)
SELECT GETDATE();   -- current timestamp, changes every call
SELECT NEWID();     -- new GUID every call
SELECT RAND();      -- random float every call

-- Persisted computed column requires deterministic expression
ALTER TABLE Employees
ADD FullName AS (FirstName + ' ' + LastName) PERSISTED;  -- deterministic ✔

-- This would FAIL (GETDATE() is non-deterministic):
-- ADD LoadedAt AS (GETDATE()) PERSISTED;

-- Create view
CREATE VIEW vw_ActiveEmployees AS
SELECT e.EmployeeID,
       e.FirstName + ' ' + e.LastName AS FullName,
       d.DeptName, e.Salary
FROM   Employees e
JOIN   Departments d ON d.DeptID = e.DeptID
WHERE  e.IsActive = 1;
GO

SELECT FullName, DeptName, Salary FROM vw_ActiveEmployees WHERE Salary > 80000;

-- Indexed (materialized) view — stores result physically
CREATE VIEW vw_SalesByRegion WITH SCHEMABINDING AS
SELECT Region, COUNT_BIG(*) AS SaleCount, SUM(Amount) AS TotalAmount
FROM   dbo.Sales GROUP BY Region;
GO

CREATE UNIQUE CLUSTERED INDEX ucx_SalesByRegion ON vw_SalesByRegion (Region);

-- Drop view
DROP VIEW IF EXISTS vw_ActiveEmployees;

-- UPDATABLE view (single table, no aggregates)
CREATE VIEW vw_SalesEmployees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM   Employees WHERE DeptID = 2;
GO

UPDATE vw_SalesEmployees SET Salary = 85000 WHERE EmployeeID = 10;  -- works
INSERT INTO vw_SalesEmployees (FirstName, LastName, Salary)
VALUES (N'Tom', N'Hardy', 70000);                                   -- works

-- NON-UPDATABLE view (GROUP BY)
CREATE VIEW vw_DeptSalary AS
SELECT DeptID, COUNT(*) AS Headcount, AVG(Salary) AS AvgSalary
FROM   Employees GROUP BY DeptID;
GO
-- UPDATE vw_DeptSalary SET AvgSalary = 90000 WHERE DeptID = 1;  -- ERROR

-- WITH CHECK OPTION: reject rows that the view\'s WHERE would not see
CREATE VIEW vw_HighSalary AS
SELECT EmployeeID, FirstName, Salary FROM Employees WHERE Salary > 100000
WITH CHECK OPTION;
GO
-- INSERT INTO vw_HighSalary (FirstName, Salary) VALUES ('Test', 50000); -- FAILS

-- AFTER INSERT: log new employee
CREATE TRIGGER trg_LogEmployeeInsert
ON Employees AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO AuditLog (TableName, Action, RecordID, ChangedAt)
    SELECT 'Employees', 'INSERT', EmployeeID, SYSDATETIME() FROM inserted;
END;
GO

-- AFTER UPDATE: track salary changes
CREATE TRIGGER trg_TrackSalaryChange
ON Employees AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Salary)
        INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangedAt)
        SELECT d.EmployeeID, d.Salary, i.Salary, SYSDATETIME()
        FROM   deleted d JOIN inserted i ON i.EmployeeID = d.EmployeeID
        WHERE  d.Salary <> i.Salary;
END;
GO

-- DDL trigger: prevent accidental DROP TABLE
CREATE TRIGGER trg_PreventDrop
ON DATABASE FOR DROP_TABLE
AS
BEGIN
    PRINT 'DROP TABLE is not allowed. Contact the DBA.';
    ROLLBACK;
END;
GO

-- List all triggers
SELECT name, type_desc, parent_class_desc, is_disabled
FROM   sys.triggers ORDER BY name;

-- AFTER INSERT: INSERTED holds new rows
CREATE TRIGGER trg_Audit_Insert
ON Employees AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO AuditLog (TableName, Action, RecordID, NewValue, ChangedAt)
    SELECT 'Employees', 'INSERT', EmployeeID,
           FirstName + ' ' + LastName + ' | Salary: ' + CAST(Salary AS NVARCHAR(20)),
           SYSDATETIME()
    FROM   inserted;
END;
GO

-- AFTER DELETE: DELETED holds removed rows
CREATE TRIGGER trg_Audit_Delete
ON Employees AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO AuditLog (TableName, Action, RecordID, OldValue, ChangedAt)
    SELECT 'Employees', 'DELETE', EmployeeID,
           FirstName + ' ' + LastName, SYSDATETIME()
    FROM   deleted;
END;
GO

-- AFTER UPDATE: DELETED = old values, INSERTED = new values
CREATE TRIGGER trg_Audit_SalaryUpdate
ON Employees AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Salary)
        INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangedAt)
        SELECT d.EmployeeID, d.Salary AS OldSalary, i.Salary AS NewSalary, SYSDATETIME()
        FROM   deleted  d
        JOIN   inserted i ON i.EmployeeID = d.EmployeeID
        WHERE  d.Salary <> i.Salary;
END;
GO

DECLARE @EmpID INT, @Salary DECIMAL(12,2);

DECLARE emp_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT EmployeeID, Salary FROM Employees WHERE IsActive = 1;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @EmpID, @Salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Salary < 50000
        UPDATE Employees SET Salary = 50000 WHERE EmployeeID = @EmpID;

    FETCH NEXT FROM emp_cursor INTO @EmpID, @Salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

UPDATE Employees SET Salary = 50000 WHERE IsActive = 1 AND Salary < 50000;

-- Basic stored procedure
CREATE PROCEDURE dbo.usp_GetEmployeesByDept @DeptID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT EmployeeID, FirstName + ' ' + LastName AS FullName, Salary
    FROM   Employees
    WHERE  DeptID = @DeptID AND IsActive = 1
    ORDER  BY LastName;
END;
GO

EXEC dbo.usp_GetEmployeesByDept @DeptID = 2;

-- With OUTPUT parameter
CREATE PROCEDURE dbo.usp_CreateEmployee
    @FirstName NVARCHAR(50),
    @LastName  NVARCHAR(50),
    @Email     NVARCHAR(150),
    @Salary    DECIMAL(12,2),
    @DeptID    INT,
    @NewID     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Employees (FirstName, LastName, Email, Salary, DeptID)
    VALUES (@FirstName, @LastName, @Email, @Salary, @DeptID);
    SET @NewID = SCOPE_IDENTITY();
END;
GO

DECLARE @CreatedID INT;
EXEC dbo.usp_CreateEmployee 'Jane','Doe','jane@example.com',82000,1,@NewID=@CreatedID OUTPUT;
SELECT @CreatedID AS NewEmployeeID;

-- Scalar function
CREATE FUNCTION dbo.fn_TaxAmount (@Salary DECIMAL(12,2))
RETURNS DECIMAL(12,2) WITH SCHEMABINDING
AS BEGIN RETURN @Salary * 0.25; END;
GO

SELECT EmployeeID, Salary, dbo.fn_TaxAmount(Salary) AS Tax FROM Employees;

-- Inline TVF (inlined by optimiser — best performance)
CREATE FUNCTION dbo.fn_SalesByRegion (@Region NVARCHAR(50))
RETURNS TABLE WITH SCHEMABINDING
AS
    RETURN SELECT SaleID, ProductID, Amount, SaleDate
           FROM   dbo.Sales WHERE Region = @Region;
GO

SELECT * FROM dbo.fn_SalesByRegion(N'North') ORDER BY SaleDate;

CREATE PROCEDURE dbo.usp_WithdrawFunds @AccountID INT, @Amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        IF @Amount <= 0
            THROW 50001, 'Withdrawal amount must be positive.', 1;

        DECLARE @Balance DECIMAL(12,2);
        SELECT @Balance = Balance FROM BankAccounts WHERE AccountID = @AccountID;

        IF @Balance IS NULL
            THROW 50002, 'Account not found.', 1;

        IF @Balance < @Amount
            THROW 50003, 'Insufficient funds.', 1;

        UPDATE BankAccounts SET Balance = Balance - @Amount WHERE AccountID = @AccountID;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

-- EXEC with concatenation – UNSAFE, no plan reuse
DECLARE @DeptID INT = 2;
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Employees WHERE DeptID = ' + CAST(@DeptID AS NVARCHAR);
EXEC(@sql);

-- sp_executesql – SAFE and plan-cache friendly
EXEC sp_executesql
    N'SELECT * FROM Employees WHERE DeptID = @d',
    N'@d INT',
    @d = @DeptID;

-- sp_executesql with OUTPUT parameter
DECLARE @Count INT;
EXEC sp_executesql
    N'SELECT @cnt = COUNT(*) FROM Employees WHERE DeptID = @d',
    N'@d INT, @cnt INT OUTPUT',
    @d   = 2,
    @cnt = @Count OUTPUT;
SELECT @Count AS EmployeeCount;

-- After deploying a DACPAC, verify the deployed schema version stored in the DAC metadata
SELECT
    instance_name,
    type_name,
    description,
    create_date
FROM   msdb.dbo.sysdac_instances;

-- After extraction, inspect what the DACPAC captured
-- (query on the target server after deployment)
SELECT
    d.name           AS DatabaseName,
    p.name           AS PropertyName,
    p.value          AS PropertyValue
FROM   sys.databases d
CROSS APPLY (
    VALUES
        ('CompatibilityLevel', CAST(d.compatibility_level AS NVARCHAR(10))),
        ('CollationName',      d.collation_name),
        ('RecoveryModel',      d.recovery_model_desc)
) AS p(name, value)
WHERE  d.name = 'MyDatabase';

-- SCENARIO 1: Dropping a column
-- Risk: column data is permanently lost.
-- Safe pattern: rename first (deploy), migrate data, then drop in next release.

-- Release 1: add new column, backfill data
ALTER TABLE Orders ADD OrderStatusNew NVARCHAR(50);
UPDATE Orders SET OrderStatusNew = OrderStatus;   -- migrate data

-- Release 2: drop the old column (after confirming data is correct)
ALTER TABLE Orders DROP COLUMN OrderStatus;

-- SCENARIO 2: Shrinking a column (VARCHAR(500) → VARCHAR(100))
-- Risk: truncation of existing values.
-- Safe pattern: validate before shrinking.

-- Check for values that would be truncated
SELECT COUNT(*) AS WillTruncate
FROM   Orders
WHERE  LEN(OrderStatus) > 100;

-- Only shrink if count = 0
ALTER TABLE Orders ALTER COLUMN OrderStatus NVARCHAR(100);

-- SCENARIO 3: Adding a NOT NULL column to a table with existing rows
-- Requires a default; use /p:GenerateSmartDefaults=true OR add default in schema.

-- In the DACPAC source (SSDT), define the column with a default constraint:
ALTER TABLE Orders
    ADD IsArchived BIT NOT NULL
    CONSTRAINT DF_Orders_IsArchived DEFAULT (0);

-- SqlPackage with GenerateSmartDefaults will auto-supply 0 for existing rows.

-- Tables/dbo.Customers.sql
CREATE TABLE [dbo].[Customers]
(
    [CustomerID]   INT           NOT NULL IDENTITY(1,1),
    [Name]         NVARCHAR(200) NOT NULL,
    [Email]        NVARCHAR(320) NOT NULL,
    [City]         NVARCHAR(100) NULL,
    [CreatedAt]    DATETIME2     NOT NULL CONSTRAINT DF_Customers_CreatedAt DEFAULT GETUTCDATE(),
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED ([CustomerID]),
    CONSTRAINT UIX_Customers_Email UNIQUE ([Email])
);

-- PostDeployment/SeedData.sql  (runs after every deployment; use MERGE to be idempotent)
MERGE INTO [dbo].[Customers] AS target
USING (VALUES
    (1, 'Alice',   'alice@example.com',   'London'),
    (2, 'Bob',     'bob@example.com',     'Paris')
) AS source (CustomerID, Name, Email, City)
ON target.CustomerID = source.CustomerID
WHEN NOT MATCHED THEN
    INSERT (CustomerID, Name, Email, City)
    VALUES (source.CustomerID, source.Name, source.Email, source.City);

-- OFFSET-FETCH (SQL Server 2012+)
SELECT DISTINCT Salary FROM Employees
ORDER  BY Salary DESC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;  -- 3rd highest (change offset for N)

-- Window function (handles ties correctly)
WITH Ranked AS (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM   Employees
)
SELECT TOP 1 Salary FROM Ranked WHERE rnk = 3;   -- change 3 to N

SELECT STUFF('Hello World', 7, 5, 'SQL');        -- 'Hello SQL'
SELECT REPLACE('foo bar foo', 'foo', 'baz');     -- 'baz bar baz'

-- Mask a credit card number
DECLARE @CC NVARCHAR(19) = '4111-1111-1111-1234';
SELECT STUFF(@CC, 1, 14, REPLICATE('*', 14));    -- '**************1234'

SELECT EmployeeID, FirstName, Salary,
       ROW_NUMBER()  OVER (ORDER BY Salary DESC) AS RowNum,
       RANK()        OVER (ORDER BY Salary DESC) AS RankNum,
       DENSE_RANK()  OVER (ORDER BY Salary DESC) AS DenseRank,
       NTILE(4)      OVER (ORDER BY Salary DESC) AS Quartile
FROM   Employees;

-- Rank within each department
SELECT EmployeeID, DeptID, Salary,
       RANK() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS DeptRank
FROM   Employees;

-- Find top expensive queries in plan cache
SELECT TOP 10
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       qs.total_elapsed_time  / qs.execution_count AS avg_us,
       SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
           ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER  BY avg_reads DESC;

-- Distinct values
SELECT DISTINCT Region FROM Sales ORDER BY Region;

-- Count duplicates
SELECT Email, COUNT(*) AS Cnt FROM Employees GROUP BY Email HAVING COUNT(*) > 1;

-- Delete duplicates, keep lowest EmployeeID
WITH Dupes AS (
    SELECT EmployeeID, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY EmployeeID) AS rn
    FROM   Employees
)
DELETE FROM Dupes WHERE rn > 1;

-- Local temp table (#)
CREATE TABLE #TempEmployees (
    EmployeeID INT, FullName NVARCHAR(100), Salary DECIMAL(12,2)
);
INSERT INTO #TempEmployees
    SELECT EmployeeID, FirstName + ' ' + LastName, Salary
    FROM   Employees WHERE DeptID = 1;
SELECT * FROM #TempEmployees;
DROP TABLE IF EXISTS #TempEmployees;  -- auto-drops when session ends

-- Global temp table (##) – visible to all sessions
CREATE TABLE ##SharedLookup (Code NVARCHAR(20) PRIMARY KEY, Label NVARCHAR(100));
INSERT INTO ##SharedLookup VALUES ('USD','US Dollar'),('EUR','Euro');
-- Any session can query ##SharedLookup; dropped when creator disconnects

-- Table variable (@) – scoped to current batch
DECLARE @TopEarners TABLE (
    EmployeeID INT PRIMARY KEY,
    FullName   NVARCHAR(100),
    Salary     DECIMAL(12,2)
);
INSERT INTO @TopEarners
    SELECT TOP (10) EmployeeID, FirstName + ' ' + LastName, Salary
    FROM   Employees ORDER BY Salary DESC;
SELECT * FROM @TopEarners;  -- gone after batch ends

-- OLTP: fast transactional writes on normalized tables
BEGIN TRANSACTION;
    INSERT INTO Orders    (CustomerID, OrderDate) VALUES (1, GETDATE());
    INSERT INTO OrderItems (OrderID, ProductID, Qty) VALUES (SCOPE_IDENTITY(), 5, 2);
COMMIT;

-- OLAP: columnstore index for fast analytical reads
CREATE TABLE SalesFact (
    SaleID     BIGINT        NOT NULL,
    DateKey    INT           NOT NULL,
    ProductKey INT           NOT NULL,
    Region     NVARCHAR(50),
    Amount     DECIMAL(12,2) NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SalesFact ON SalesFact;

-- Analytical query benefits from columnstore
SELECT Region, SUM(Amount) AS Revenue, COUNT(*) AS Transactions
FROM   SalesFact
WHERE  DateKey BETWEEN 20240101 AND 20241231
GROUP  BY Region
ORDER  BY Revenue DESC;

-- Syntax: LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

-- Month-over-month sales comparison
SELECT
    SaleMonth,
    TotalSales,
    LAG(TotalSales,  1, 0) OVER (ORDER BY SaleMonth) AS PrevMonthSales,
    LEAD(TotalSales, 1, 0) OVER (ORDER BY SaleMonth) AS NextMonthSales,
    TotalSales - LAG(TotalSales, 1, 0) OVER (ORDER BY SaleMonth) AS MoM_Change
FROM (
    SELECT FORMAT(OrderDate, 'yyyy-MM') AS SaleMonth,
           SUM(Amount) AS TotalSales
    FROM   Orders
    GROUP  BY FORMAT(OrderDate, 'yyyy-MM')
) m
ORDER  BY SaleMonth;

-- Partition by region: compare to previous month within each region
SELECT Region, SaleMonth, TotalSales,
       LAG(TotalSales) OVER (PARTITION BY Region ORDER BY SaleMonth) AS PrevSales,
       TotalSales - LAG(TotalSales) OVER (PARTITION BY Region ORDER BY SaleMonth) AS Diff
FROM (
    SELECT Region, FORMAT(OrderDate, 'yyyy-MM') AS SaleMonth,
           SUM(Amount) AS TotalSales
    FROM   Orders
    GROUP  BY Region, FORMAT(OrderDate, 'yyyy-MM')
) t;

-- Detect gaps in sequential IDs
WITH Gaps AS (
    SELECT OrderID,
           OrderID - LAG(OrderID) OVER (ORDER BY OrderID) AS GapSize
    FROM   Orders
)
SELECT * FROM Gaps WHERE GapSize > 1;