SQL — CRUD Queries & JOINs

SELECT

SELECT * FROM Products;
SELECT Id, Name, Price FROM Products;
SELECT TOP 10 * FROM Products;          -- MS SQL specific
SELECT * FROM Products
ORDER BY Price DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- pagination

WHERE

SELECT * FROM Products
WHERE Price BETWEEN 100 AND 500
  AND Stock > 0
  AND Name LIKE '%phone%'
  AND CategoryId IN (1, 2, 3)
  AND CreatedAt >= '2024-01-01';

INSERT

INSERT INTO Products (Name, Price, Stock, CategoryId)
VALUES ('Pen', 50.00, 100, 1);

-- Multi-row
INSERT INTO Products (Name, Price) VALUES
    ('Book', 150),
    ('Pencil', 20),
    ('Eraser', 5);

-- INSERT ... SELECT
INSERT INTO ProductsArchive (Name, Price)
SELECT Name, Price FROM Products WHERE CreatedAt < '2020-01-01';

-- OUTPUT (return inserted)
INSERT INTO Products (Name, Price) OUTPUT INSERTED.Id
VALUES ('New', 100);

UPDATE

UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = 5;

UPDATE Products
SET Price = 0, Stock = 0
WHERE CreatedAt < DATEADD(YEAR, -2, GETDATE());

-- UPDATE with JOIN
UPDATE p
SET p.Discount = 10
FROM Products p
INNER JOIN Categories c ON p.CategoryId = c.Id
WHERE c.Name = 'Electronics';

DELETE

DELETE FROM Products WHERE Id = 5;
DELETE FROM Products WHERE Stock = 0;

-- All rows (slow)
DELETE FROM Products;

-- Faster, no log per row, reset identity
TRUNCATE TABLE Products;

JOIN Types ⭐

-- INNER JOIN — both sides match
SELECT p.Name, c.Name AS Category
FROM Products p
INNER JOIN Categories c ON p.CategoryId = c.Id;

-- LEFT JOIN — সব products, category থাকুক বা না
SELECT p.Name, c.Name AS Category
FROM Products p
LEFT JOIN Categories c ON p.CategoryId = c.Id;

-- RIGHT JOIN — সব categories
SELECT p.Name, c.Name AS Category
FROM Products p
RIGHT JOIN Categories c ON p.CategoryId = c.Id;

-- FULL OUTER JOIN — দুদিকের সব
SELECT p.Name, c.Name
FROM Products p
FULL OUTER JOIN Categories c ON p.CategoryId = c.Id;

-- CROSS JOIN — Cartesian
SELECT p.Name, s.Code FROM Products p CROSS JOIN Sizes s;

Aggregate Functions ও GROUP BY

SELECT
    CategoryId,
    COUNT(*) AS Total,
    AVG(Price) AS AvgPrice,
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice,
    SUM(Stock) AS TotalStock
FROM Products
GROUP BY CategoryId
HAVING COUNT(*) > 5
ORDER BY Total DESC;

Subquery

-- WHERE
SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

-- IN
SELECT * FROM Products
WHERE CategoryId IN (SELECT Id FROM Categories WHERE Name LIKE 'E%');

-- EXISTS
SELECT * FROM Categories c
WHERE EXISTS (SELECT 1 FROM Products WHERE CategoryId = c.Id);

Multi-table JOIN

SELECT
    o.Id, u.Name, p.Name AS Product, oi.Quantity
FROM Orders o
INNER JOIN Users u ON o.UserId = u.Id
INNER JOIN OrderItems oi ON oi.OrderId = o.Id
INNER JOIN Products p ON oi.ProductId = p.Id
WHERE o.CreatedAt >= '2024-01-01'
ORDER BY o.CreatedAt DESC;