Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
📊 GROUP BY = Data Aggregation Raw data is too detailed. GROUP BY summarizes data. Counts, sums, averages — powerful analysis. 📝 GROUP BY Basics — Count by category SELECT category, COUNT(*) as product_count FROM products GROUP BY category; — Sum by customer SELECT customer_id, SUM(total) as total_spent, COUNT(*) as order_count FROM orders GROUP BY […]
📊 Views = Virtual Tables Complex queries repeated everywhere. Views simplify them. Create once, query like a table. 📝 Create View CREATE VIEW active_users AS SELECT id, name, email, created_at FROM users WHERE status = ‘active’ AND last_login > ‘2024-01-01’; — Query the view SELECT * FROM active_users; — View with JOIN CREATE VIEW order_summary […]
⚡ Indexing = Query Performance Slow queries kill performance. Indexing speeds up data access. Find data fast, optimize queries. 📝 Index Types # B-Tree Index (default) CREATE INDEX idx_users_email ON users(email); # Unique Index CREATE UNIQUE INDEX idx_users_email ON users(email); # Composite Index CREATE INDEX idx_users_name_email ON users(name, email); # Full-Text Index CREATE FULLTEXT INDEX […]
🔗 Joins = Data Relationships Data is in multiple tables. Joins combine them. INNER, LEFT, RIGHT, FULL — choose right. 📝 Join Types — INNER JOIN (Only matching) SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id; — LEFT JOIN (All from left) SELECT u.name, o.total FROM users u LEFT […]
📊 Subqueries = Complex Data Single queries are limited. Subqueries nest queries. Complex conditions, derived data. 📝 Subquery Examples — Users with orders over $100 SELECT name, email FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total > 100 ); — Users with no orders SELECT name, email FROM users WHERE id […]
🔗 Foreign Keys = Data Integrity Orphaned data is bad. Foreign keys maintain relationships. Referential integrity, cascading actions. 📝 Foreign Key Basics — Create with foreign key CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2), FOREIGN KEY (user_id) REFERENCES users(id) ); — With ON DELETE CASCADE CREATE TABLE order_items ( id […]
🔔 Triggers = Automated Actions Need to automate database actions? Triggers run automatically. Audit logs, data validation, synchronization. 📝 Trigger Basics — Audit trigger (INSERT) CREATE TRIGGER user_audit_insert ON users AFTER INSERT AS BEGIN INSERT INTO audit_log (action, table_name, record_id, changed_by, changed_at) SELECT ‘INSERT’, ‘users’, id, SYSTEM_USER, GETDATE() FROM inserted; END; — Audit trigger (UPDATE) […]
📊 Views = Virtual Tables Complex queries repeated everywhere. Views simplify them. Create once, use like a table. 📝 Create View CREATE VIEW active_users AS SELECT id, name, email, created_at FROM users WHERE status = ‘active’ AND last_login > ‘2024-01-01’; — Query the view SELECT * FROM active_users; — View with JOIN CREATE VIEW order_summary […]
📦 Stored Procedures = Database Logic Business logic in code is fine. Stored procedures move logic to database. Performance, security, consistency. 📝 Creating Procedures — Basic procedure CREATE PROCEDURE GetUserById @UserId INT AS BEGIN SELECT * FROM Users WHERE Id = @UserId; END; — Execute EXEC GetUserById 1; — With OUTPUT parameter CREATE PROCEDURE GetUserCount […]
📋 JSON in SQL = Modern Data JSON is everywhere. SQL JSON functions work with JSON data. Store, query, modify — flexible data. 📝 JSON Functions # Store JSON CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), metadata JSON ); INSERT INTO users (id, name, metadata) VALUES ( 1, ‘Alice’, ‘{“age”: 30, “city”: […]
⚡ Query Optimization = Database Performance Slow queries kill performance. Query optimization speeds up database. Faster responses, better UX. 📝 Optimization Techniques # 1. Avoid SELECT * — Bad SELECT * FROM users; — Good SELECT id, name, email FROM users; # 2. Use WHERE conditions — Bad SELECT * FROM users; — Good SELECT […]
🔄 Transactions = ACID Compliance Data integrity matters. Transactions ensure ACID. Atomic, Consistent, Isolated, Durable — reliable data operations. 📝 Transaction Basics # Basic transaction BEGIN TRANSACTION; UPDATE accounts SET balance = balance – 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; # With error […]
📦 Stored Procedures = Database Logic Business logic in code is fine. Stored procedures move logic to database. Performance, security, consistency. 📝 Creating Procedures — Basic procedure CREATE PROCEDURE GetUserById @UserId INT AS BEGIN SELECT * FROM Users WHERE Id = @UserId; END; — Execute EXEC GetUserById 1; — With OUTPUT parameter CREATE PROCEDURE GetUserCount […]
⚡ Indexes = Query Performance Slow queries kill performance. Indexes speed up queries. Find data fast, optimize SELECTs. Essential for database performance. 📝 Index Types # B-Tree Index (default) CREATE INDEX idx_users_email ON users(email); # Unique Index (enforces uniqueness) CREATE UNIQUE INDEX idx_users_email ON users(email); # Composite Index (multiple columns) CREATE INDEX idx_users_name_email ON users(name, […]
📊 CTE = Readable Complex Queries Nested subqueries are messy. CTEs make queries readable. Break complex queries into steps. Essential for maintainable SQL. 📝 CTE Syntax WITH cte_name AS ( SELECT column1, column2 FROM table WHERE condition ) SELECT * FROM cte_name WHERE another_condition; — Multiple CTEs WITH cte1 AS ( SELECT … ), cte2 […]
📊 Window Functions = Advanced Analytics Aggregates are limited. Window functions analyze data without grouping. Rankings, running totals, moving averages. 📝 Window Function Syntax SELECT name, salary, department_id, — Window function AVG(salary) OVER (PARTITION BY department_id) as dept_avg, RANK() OVER (ORDER BY salary DESC) as salary_rank, LAG(salary, 1) OVER (ORDER BY salary) as prev_salary FROM […]
📊 Subqueries = Nested Power Single queries are limited. Subqueries nest queries inside queries. Complex conditions, derived data, advanced filtering. 📝 Subquery Examples — Users with orders over $100 SELECT name, email FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total > 100 ); — Users with no orders SELECT name, email […]
📊 Aggregate Functions = Data Insights Need totals, averages, counts? Aggregate functions summarize data. Essential for reporting and analytics. 📝 Basic Aggregates SELECT COUNT(*) AS total_rows, COUNT(DISTINCT column) AS distinct_values, SUM(amount) AS total_sum, AVG(amount) AS average, MIN(amount) AS min_value, MAX(amount) AS max_value, STDDEV(amount) AS std_dev FROM orders; — With GROUP BY SELECT customer_id, COUNT(*) AS […]
📊 Views = Virtual Tables Complex queries repeated everywhere. Views simplify them. Create a view once, query it like a table. 📝 Create View CREATE VIEW active_users AS SELECT id, name, email, created_at FROM users WHERE status = ‘active’ AND last_login > ‘2024-01-01’; — Query the view SELECT * FROM active_users; — View with JOIN […]
🔗 JOIN Types = Which Rows to Include INNER, LEFT, RIGHT, FULL — which to use? Know the difference for correct results. 📝 JOIN Types — INNER JOIN (only matching rows) SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id; — Users without orders are excluded — LEFT JOIN (all […]
🔒 Never Concatenate User Input into SQL SQL injection is #1 web vulnerability. Parameterized queries prevent it. Always use parameters for user input. ❌ Vulnerable (SQL Injection) string query = “SELECT * FROM users WHERE email = ‘” + email + “‘”; var cmd = new SqlCommand(query, connection); // User input: admin’ OR ‘1’=’1 // […]
📊 Normalization = Organized Data Bad design = duplicate data, anomalies. Normalization organizes data. 1NF, 2NF, 3NF — reduce redundancy, improve integrity. 📝 First Normal Form (1NF) ❌ Not 1NF (repeating groups) Order: 1, Customer: Alice, Products: Laptop, Phone, Tablet ✅ 1NF (atomic values, separate rows) OrderID Customer Product 1 Alice Laptop 1 Alice Phone […]
🗑️ DELETE removes rows. TRUNCATE removes all rows. Both remove data. DELETE can use WHERE, fires triggers. TRUNCATE is faster, resets identity, cannot use WHERE. 📝 DELETE DELETE FROM users WHERE id = 123; DELETE FROM users WHERE status = ‘inactive’; – Can use WHERE (selective) – Fires triggers – Slower (logs each row) – […]
📊 SELECT = Read Data from Database Data is useless if you can’t read it. SELECT queries data. The most used SQL command. 📝 Basic SELECT — All columns SELECT * FROM users; — Specific columns SELECT name, email FROM users; — Distinct values SELECT DISTINCT country FROM users; — With alias SELECT name AS […]
🗑️ DELETE = Remove Rows Data needs removal. DELETE removes rows. Be careful — without WHERE, deletes all rows. Use transactions. 📝 Basic DELETE — Delete specific row DELETE FROM users WHERE id = 123; — Delete multiple rows DELETE FROM users WHERE status = ‘inactive’; — Delete all rows (careful!) DELETE FROM users; — […]
✏️ UPDATE = Change Existing Data Data changes over time. UPDATE modifies existing rows. Be careful — without WHERE, updates all rows. 📝 Basic UPDATE — Update single column UPDATE users SET email = ‘newemail@example.com’ WHERE id = 123; — Update multiple columns UPDATE users SET name = ‘Alice Updated’, age = 31 WHERE id […]
📝 INSERT = Add Rows to Table Tables are empty at first. INSERT adds rows. Single rows, multiple rows, from other tables. 📝 Basic INSERT — Insert single row INSERT INTO users (name, email, age) VALUES (‘Alice’, ‘alice@example.com’, 30); — Insert with all columns INSERT INTO users VALUES (1, ‘Bob’, ‘bob@example.com’, 25); — Insert multiple […]
📊 GROUP BY = Summarize Your Data Need totals, averages, counts? GROUP BY aggregates data. Sales by category, users by country, orders by month. 📝 Basic GROUP BY — Count per category SELECT category, COUNT(*) as product_count FROM products GROUP BY category; — Average price per category SELECT category, AVG(price) as avg_price FROM products GROUP […]
📊 ORDER BY = Sort Your Results SELECT returns unsorted. ORDER BY sorts results. Ascending, descending, multiple columns. Essential for reporting. 📝 Basic ORDER BY — Ascending (default) SELECT * FROM users ORDER BY name; SELECT * FROM users ORDER BY name ASC; — Descending SELECT * FROM users ORDER BY age DESC; SELECT * […]
🔍 WHERE is the Most Important SQL Keyword SELECT * FROM users returns everything. WHERE filters results. Get only what you need. Faster queries, less data. 📝 WHERE Examples — Equality SELECT * FROM users WHERE id = 123; SELECT * FROM users WHERE email = ‘alice@example.com’; — Comparison SELECT * FROM users WHERE age […]