DotNet and AI Materials
This blog will guid you to upskill youself in Microsoft Technology like c#,Dotnet Core, Web API, MS SQL, Azure, Gen AI, RAg, AI, LLm and Interview Preparation.
Tuesday, 17 November 2026
Write a query to perform a self-join to find pairs of employees in the same department.
Monday, 16 November 2026
Write a query to find the difference in days between two dates in the same table.
Sunday, 15 November 2026
Find employees who don’t have a department assigned.
Saturday, 14 November 2026
Write a query to find the number of employees in each job title.
Friday, 13 November 2026
Find departments with the highest average salary.
Let's create table schema and sample data first, after that will write query.
Table Schema and Sample Data:-
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department_id INT,
salary DECIMAL(10,2),
FOREIGN KEY (department_id) REFERENCES departments(id)
);
INSERT INTO departments (id, name) VALUES
(10, 'Engineering'),
(20, 'Sales'),
(30, 'HR'),
(40, 'Finance');
INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Asha', 10, 95000),
(2, 'Ravi', 10, 85000),
(3, 'Meera', 20, 60000),
(4, 'Kiran', 20, 70000),
(5, 'Neha', 30, 80000),
(6, 'Rohan', 30, 100000),
(7, 'Tara', NULL, 120000);
The averages are Engineering 90000, HR 90000, and Sales 65000. Finance has no employees. Engineering and HR are tied for the highest, and Tara has no department.
Query 1: Order and take the top row
SELECT d.name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY avg_salary DESC
LIMIT 1;
Use SELECT TOP 1 ... in SQL Server, or FETCH FIRST 1 ROW ONLY in Oracle. This returns only one row, so with our tie it shows either Engineering or HR, and which one is not guaranteed. Add a tie-breaker such as ORDER BY avg_salary DESC, d.name if you want a predictable pick.
Query 2: Return every department tied for the highest (recommended)
WITH dept_avg AS (
SELECT d.id, d.name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
)
SELECT name, avg_salary
FROM dept_avg
WHERE avg_salary = (SELECT MAX(avg_salary) FROM dept_avg);
Result:-
| name | avg_salary |
|---|---|
| Engineering | 90000.00 |
| HR | 90000.00 |
How it works
- The CTE
dept_avgcalculates one average per department. - The subquery
SELECT MAX(avg_salary) FROM dept_avgfinds the highest of those averages. - The outer query keeps every department whose average equals that maximum, so ties are all returned.
Query 3: Window function (easy to extend to the top N)
WITH dept_avg AS (
SELECT d.id, d.name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
)
SELECT name, avg_salary
FROM (
SELECT name, avg_salary,
RANK() OVER (ORDER BY avg_salary DESC) AS rnk
FROM dept_avg
) t
WHERE rnk = 1;
RANK() gives tied departments the same rank, so both Engineering and HR get rank 1. Change rnk = 1 to rnk <= 3 to get the top three salary levels.
Thursday, 12 November 2026
Write a query to get the first and last purchase date for each customer.
Wednesday, 11 November 2026
Which Azure services have you used in your project, and how do they integrate with each other?
A Note First
I haven't worked on a project myself. I'm an AI, so I can't claim hands-on experience. But if you're preparing for an interview or documenting an architecture, here is a realistic reference project built from the services we've covered, with how each piece integrates. You can adapt it to your real experience, and you should only describe what you've actually done.
Example: an order-processing platform
Users ─► Front Door (WAF) ─► APIM ─► App Service (.NET Web API)
│
┌───────────────────────────┼──────────────────────────┐
▼ ▼ ▼
Azure SQL Redis (cache) Service Bus (topic)
│
┌──────────────────────────┼──────────────┐
▼ ▼ ▼
Function: billing Function: shipping Function: notifications
│
Durable Functions (multi-step workflow)
Cross-cutting: Entra ID, Managed Identity, Key Vault, VNet + Private Endpoints,
Application Insights, GitHub Actions / Azure Pipelines + Bicep
How the pieces integrate
Edge and API layer
- Front Door provides global entry, WAF, and TLS, and forwards to APIM.
- APIM validates Entra ID JWTs, applies rate limits, and routes to the backend. The backend accepts traffic only from APIM.
- App Service hosts the .NET Web API, with deployment slots for zero-downtime releases.
Identity and secrets
- Entra ID issues tokens (auth code + PKCE for users, client credentials for services). The API validates the JWT and checks scopes or roles.
- Managed identity is how the app and functions reach SQL, Key Vault, Service Bus, and Storage, with RBAC roles and no connection strings.
- Key Vault holds the few remaining secrets, exposed through Key Vault references in app settings.
Data and messaging
- Azure SQL is the system of record, accessed through the app's managed identity.
- Redis caches hot reads to reduce database load.
- Service Bus decouples the API from downstream work. The API publishes an
OrderPlacedevent to a topic, and each subscription (billing, shipping, notifications) feeds its own consumer. Dead-letter queues, idempotent handlers, and duplicate detection handle failures. - Azure Functions consume those subscriptions with Service Bus triggers.
- Durable Functions orchestrate multi-step flows such as reserve inventory, charge payment, and confirm, with compensation on failure.
Networking
- VNet integration on the app and functions, plus Private Endpoints with private DNS for SQL, Key Vault, Service Bus (Premium), and Storage. Public access is disabled on those services.
Observability
- Application Insights (workspace-based, via the OpenTelemetry distro) captures requests, dependencies, and logs across all services, correlated by operation ID. Alerts cover failure rate, p95 latency, queue depth, and DLQ count.
Delivery
- GitHub Actions with OIDC login builds, tests, and deploys to a staging slot, runs a smoke test, then swaps to production after approval.
- Bicep defines the infrastructure, and EF migrations run as a controlled pipeline step.
Scale and availability
- Autoscale with a minimum of two instances, zone-redundant compute and data, and an auto-failover group for SQL if cross-region DR is required.