Wednesday, 18 November 2026

Calculate the moving average of salaries over the last 3 employees ordered by hire date.

Let's create table schema and sample data first.

Table Schema and sample:-

CREATE TABLE employees (
  id INT PRIMARY KEY, 
name VARCHAR(50) NOT NULL, 
hire_date DATE NOT NULL, 
salary DECIMAL(10,2) 
); 

INSERT INTO employees (id, name, hire_date, salary) VALUES 
(1, 'Asha', '2024-01-15', 50000), 
(2, 'Ravi', '2024-03-10', 60000), 
(3, 'Meera', '2024-05-20', 70000), 
(4, 'Kiran', '2024-08-01', 80000), 
(5, 'Neha', '2025-01-12', 90000),
(6, 'Rohan', '2025-06-30', 100000);

Query(MS SQL):-

SELECT 
id, 
name, 
hire_date, salary, 
AVG(salary) OVER ( ORDER BY hire_date, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 
FROM employees 
ORDER BY hire_date, id;


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;