Module 1: SQL Refresher & Query Fundamentals

 Basic SQL syntax & structure

 SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING

 Aggregate functions (SUM, COUNT, AVG, MIN, MAX)

 Common interview-style problems using single-table queries

 Practice: Write queries on sample placement datasets (Students, Employees, Sales, etc.)

customer_id customer_name account_type balance branch city opening_date

101 Ravi Kumar Savings 45000 B001 Hyderabad 2021-03-10

102 Anjali Rao Current 120000 B002 Chennai 2020-07-15

103 Sunil Verma Savings 38000 B001 Hyderabad 2022-01-05

104 Priya Singh Savings 85000 B003 Bengaluru 2019-12-12

105 Rohan Das Current 50000 B002 Chennai 2023-02-10

106 Meera Shah Savings 150000 B004 Mumbai 2018-06-20

107 Arjun Mehta Current 95000 B004 Mumbai 2022-08-25

1️⃣ Find all customers who have a balance greater than ₹50,000.

SELECT customer_name, balance, city

FROM Bank_Customers

WHERE balance > 50000;

✅ Answer:
Anjali Rao, Priya Singh, Meera Shah, Arjun Mehta

2️⃣ List customers who opened their accounts before 2021.

SELECT customer_name, opening_date

FROM Bank_Customers

WHERE opening_date < '2021-01-01';

✅ Answer:
Priya Singh, Meera Shah, Anjali Rao

3️⃣ Display total balance of customers grouped by account type.

SELECT account_type, SUM(balance) AS total_balance

FROM Bank_Customers

GROUP BY account_type;

✅ Answer:

account_type total_balance

Savings 318000

Current 265000

4️⃣ Find the average balance of customers in each city.

SELECT city, AVG(balance) AS avg_balance

FROM Bank_Customers

GROUP BY city;

✅ Answer:

city avg_balance

Hyderabad 41500

Chennai 85000

Bengaluru 85000

Mumbai 122500

5️⃣ Retrieve customer names whose balance is between ₹40,000 and ₹1,00,000.

SELECT customer_name, balance

FROM Bank_Customers

WHERE balance BETWEEN 40000 AND 100000;

✅ Answer:
Ravi Kumar, Priya Singh, Rohan Das, Arjun Mehta

6️⃣ Find the number of customers per branch.

SELECT branch, COUNT(*) AS total_customers

FROM Bank_Customers

GROUP BY branch;

✅ Answer:
branch total_customers

B001 2

B002 2

B003 1

B004 2

7️⃣ Display customers whose name starts with the letter 'R'.

SELECT customer_name

FROM Bank_Customers

WHERE customer_name LIKE 'R%';

✅ Answer:
Ravi Kumar, Rohan Das

8️⃣ List all unique account types available in the bank.

SELECT DISTINCT account_type

FROM Bank_Customers;

✅ Answer:
Savings, Current

9️⃣ Find the city having the maximum total balance.

SELECT city, SUM(balance) AS total_balance

FROM Bank_Customers

GROUP BY city

ORDER BY total_balance DESC

LIMIT 1;

✅ Answer:
Mumbai → ₹245,000

🔟 Retrieve customers who have the minimum balance in each account type.

SELECT account_type, customer_name, balance

FROM Bank_Customers

WHERE (account_type, balance) IN (

 SELECT account_type, MIN(balance)

FROM Bank_Customers

GROUP BY account_type

);

✅ Answer:

account_type customer_name balance

Savings Sunil Verma 38000

Current Rohan Das 50000

 Common interview-style problems using single-table queries

These are based on a Bank_Customers table (as used in your Module 1), focusing on
SELECT, WHERE, GROUP BY, HAVING, ORDER BY, and aggregate
functions.

customer_id customer_name account_type balance branch city opening_date

101 Ravi Kumar Savings 45000 B001 Hyderabad 2021-03-10

102 Anjali Rao Current 120000 B002 Chennai 2020-07-15

103 Sunil Verma Savings 38000 B001 Hyderabad 2022-01-05

104 Priya Singh Savings 85000 B003 Bengaluru 2019-12-12

105 Rohan Das Current 50000 B002 Chennai 2023-02-10

106 Meera Shah Savings 150000 B004 Mumbai 2018-06-20

107 Arjun Mehta Current 95000 B004 Mumbai 2022-08-25

1️⃣ Find the 3 customers having the highest account balances.

SELECT customer_name, balance

FROM Bank_Customers

ORDER BY balance DESC

LIMIT 3;

2️⃣ Display all customers who opened their account in or after 2021.

SELECT customer_name, opening_date

FROM Bank_Customers
WHERE opening_date >= '2021-01-01';

3️⃣ Retrieve customers whose account type is 'Savings' and balance exceeds ₹50,000.

SELECT customer_name, balance

FROM Bank_Customers

WHERE account_type = 'Savings' AND balance > 50000;

4️⃣ Count how many customers belong to each city.

SELECT city, COUNT(*) AS total_customers

FROM Bank_Customers

GROUP BY city;

5️⃣ Find the average balance of all 'Current' account holders.

SELECT AVG(balance) AS avg_current_balance

FROM Bank_Customers

WHERE account_type = 'Current';

6️⃣ Display customers who have balance greater than the average balance of all customers.

SELECT customer_name, balance

FROM Bank_Customers

WHERE balance > (SELECT AVG(balance) FROM Bank_Customers);

7️⃣ Retrieve the customer with the earliest (oldest) account opening date.

SELECT customer_name, opening_date

FROM Bank_Customers

ORDER BY opening_date ASC

LIMIT 1;

8️⃣ List all branches with total balance above ₹1,00,000.

SELECT branch, SUM(balance) AS total_branch_balance

FROM Bank_Customers
GROUP BY branch

HAVING SUM(balance) > 100000;

9️⃣ Find all customers whose name ends with ‘a’ or ‘h’.

SELECT customer_name

FROM Bank_Customers

WHERE customer_name LIKE '%a' OR customer_name LIKE '%h';

🔟 Show all customers ordered by their balance in descending order, and within same balance, by
name alphabetically.

SELECT customer_name, balance

FROM Bank_Customers

ORDER BY balance DESC, customer_name ASC;

----------------------------------------------------------------------------------

Course, using three realistic tables:

 Bank_Branch

 Customer

 Loan

Practice Queries (Placement-Level)

A. Single-Table Queries (Customer Table)

1. List all customers who have a balance greater than ₹75,000.

2. Find all customers who opened their account after 2021.

3. Display the number of customers per branch.

4. Retrieve the total balance maintained by all 'Savings' account holders.

5. Show customers whose name starts with ‘R’ or ends with ‘a’.

These questions test SELECT, WHERE, ORDER BY, LIKE, GROUP BY, and aggregate functions using one
table only.

A. Single-Table Queries (Bank_Branch Table)

branch_id branch_name city manager total_employees established_year

B001 Main Branch Hyderabad Mr. Sharma 25 2005

branch_id branch_name city manager total_employees established_year

B002 South Branch Chennai Ms. Lakshmi 18 2010

B003 City Center Bengaluru Mr. Reddy 20 2012

B004 West Branch Mumbai Mr. Iqbal 22 2008

B005 North Point Delhi Ms. Neha 15 2015

B006 East Branch Kolkata Mr. Khan 19 2009

Single-Table Practice Queries — Bank_Branch

1️⃣ Display all details of all bank branches.

SELECT * FROM Bank_Branch;

2️⃣ List all branch names and cities.

SELECT branch_name, city FROM Bank_Branch;

3️⃣ Find all branches located in Mumbai or Chennai.

SELECT branch_id, branch_name, city

FROM Bank_Branch

WHERE city IN ('Mumbai', 'Chennai');

4️⃣ Retrieve the branches that were established before 2010.

SELECT branch_name, established_year

FROM Bank_Branch

WHERE established_year < 2010;

5️⃣ Display all branches where total employees are greater than 20.

SELECT branch_name, total_employees

FROM Bank_Branch

WHERE total_employees > 20;

6️⃣ Find the total number of employees across all branches.

SELECT SUM(total_employees) AS total_bank_staff

FROM Bank_Branch;

7️⃣ List all managers whose names start with 'M'.

SELECT manager

FROM Bank_Branch

WHERE manager LIKE 'M%';

8️⃣ Find the average number of employees per branch.

SELECT AVG(total_employees) AS avg_branch_staff

FROM Bank_Branch;

9️⃣ Display the oldest and newest branches based on establishment year.

SELECT branch_name, established_year

FROM Bank_Branch

ORDER BY established_year ASC;

-- (Oldest branch first)

🔟 Retrieve the city that has the branch with the maximum employees.

SELECT city, total_employees

FROM Bank_Branch

ORDER BY total_employees DESC

LIMIT 1;

1️⃣1️⃣ Count how many branches are located in each city (if multiple per city).

SELECT city, COUNT(*) AS total_branches

FROM Bank_Branch

GROUP BY city;
1️⃣2️⃣ Show branches established after 2008 sorted by number of employees (descending).

SELECT branch_name, established_year, total_employees

FROM Bank_Branch

WHERE established_year > 2008

ORDER BY total_employees DESC;

1️⃣3️⃣ Display the branch name and a computed column showing staff category:

If total_employees > 20 → ‘Large Branch’

Else → ‘Small Branch’

SELECT branch_name,

total_employees,

CASE

WHEN total_employees > 20 THEN 'Large Branch'

ELSE 'Small Branch'

END AS branch_category

FROM Bank_Branch;

1️⃣4️⃣ Find branches established between 2008 and 2012.

SELECT branch_name, established_year

FROM Bank_Branch

WHERE established_year BETWEEN 2008 AND 2012;

1️⃣5️⃣ Retrieve all managers and their cities, sorted alphabetically by manager name.

SELECT manager, city

FROM Bank_Branch

ORDER BY manager ASC;