Tuesday, 17 November 2026

Write a query to perform a self-join to find pairs of employees in the same department.

Let's create Table schema and sample data first, then will write query to fetch data.

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, 
FOREIGN KEY (department_id) REFERENCES departments(id) 
); 

  INSERT INTO departments (id, name) VALUES 
(10, 'Engineering'), (20, 'Sales'), 
(30, 'HR'); 

  INSERT INTO employees (id, name, department_id) VALUES 
(1, 'Asha', 10), 
(2, 'Ravi', 10), 
(3, 'Meera', 10), 
(4, 'Kiran', 20), 
(5, 'Neha', 20), 
(6, 'Rohan', 30), 
(7, 'Tara', NULL);

Query(MS SQL):-

SELECT 
e1.name AS employee_1, 
e2.name AS employee_2, 
e1.department_id 
FROM employees e1 
JOIN employees e2 
ON e1.department_id = e2.department_id 
AND e1.id < e2.id 
ORDER BY e1.department_id, e1.id, e2.id;

Monday, 16 November 2026

Write a query to find the difference in days between two dates in the same table.

Let's create  table schema and sample data.

Table schema and sample data:-

CREATE TABLE orders (
  order_id INT PRIMARY KEY, 
order_date DATE NOT NULL, 
ship_date DATE 
); 

INSERT INTO orders (order_id, order_date, ship_date) VALUES 
(1, '2026-10-01', '2026-10-05'), 
(2, '2026-10-10', '2026-10-10'), 
(3, '2026-09-28', '2026-10-12'), 
(4, '2026-10-15', NULL), 
(5, '2026-10-20', '2026-10-18');

Query(MS SQL):-

SELECT 
order_id, 
order_date, 
ship_date, 
DATEDIFF(DAY, order_date, ship_date) AS days_to_ship 
FROM orders;

Sunday, 15 November 2026

Find employees who don’t have a department assigned.

Let's create table schema and sample data.

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, 
FOREIGN KEY (department_id) REFERENCES departments(id) 
); 

INSERT INTO departments (id, name) VALUES 
(10, 'Engineering'), (20, 'Sales'), 
(30, 'HR'); 

INSERT INTO employees (id, name, department_id) VALUES 
(1, 'Asha', 10), (2, 'Ravi', 20), 
(3, 'Meera', NULL), 
(4, 'Kiran', 30), 
(5, 'Neha', NULL);

Query:-

SELECT * 
FROM employees 
WHERE department_id IS NULL;

Saturday, 14 November 2026

Write a query to find the number of employees in each job title.

Let's create table schema and sample data.

Table Schema and Sample data:-

CREATE TABLE employees 
( 
id INT PRIMARY KEY, 
name VARCHAR(50) NOT NULL, 
job_title VARCHAR(50) 
); 

INSERT INTO employees (id, name, job_title) VALUES 
(1, 'Asha', 'Software Engineer'), 
(2, 'Ravi', 'Software Engineer'), 
(3, 'Meera', 'Sales Executive'), 
(4, 'Kiran', 'Sales Executive'), 
(5, 'Neha', 'Sales Executive'), 
(6, 'Rohan', 'HR Manager'), 
(7, 'Tara', NULL);

Query:-

SELECT job_title, 
COUNT(*) AS employee_count 
FROM employees 
GROUP BY job_title 
ORDER BY employee_count DESC;



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

  1. The CTE dept_avg calculates one average per department.
  2. The subquery SELECT MAX(avg_salary) FROM dept_avg finds the highest of those averages.
  3. 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.

Let's create Table schema and feed data into table.

Table schema and sample data:-

CREATE TABLE customers (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  amount      DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (id, name) VALUES
(1, 'Asha'),
(2, 'Ravi'),
(3, 'Meera'),
(4, 'Kiran');

INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
(1, 1, '2026-01-10', 500),
(2, 1, '2026-03-05', 300),
(3, 1, '2026-09-20', 450),
(4, 2, '2026-02-14', 200),
(5, 2, '2026-02-14', 150),
(6, 3, '2026-06-01', 800);


Query:-
Show  customer names, and include customers who never purchased

SELECT c.id, c.name, 
MIN(o.order_date) AS first_purchase_date, 
MAX(o.order_date) AS last_purchase_date 
FROM customers c 
LEFT JOIN orders o ON o.customer_id = c.id 
GROUP BY c.id, c.name ORDER BY c.id;


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 OrderPlaced event 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.