Project: Bank Customer Classification
1️. Question: How do you check whether data is loaded correctly?
Answer:
First, I verify the total number of records and preview sample rows.
SELECT COUNT(*) AS total_records
FROM bank_classification;
SELECT *
FROM bank_classification
LIMIT 10;
2️. Question: How do you identify NULL values in important columns?
Answer:
By checking each business-critical column for NULLs.
SELECT *
FROM bank_classification
WHERE age IS NULL
OR job IS NULL
OR balance IS NULL
OR education IS NULL;
3️. Question: How do you replace NULL values in real-time projects?
Answer:
I update NULLs with meaningful default values to maintain data consistency.
UPDATE bank_classification
SET job = 'unknown'
WHERE job IS NULL;
UPDATE bank_classification
SET education = 'unknown'
WHERE education IS NULL;
4️. Question: How do you remove invalid data?
Answer:
By deleting records that fall outside valid business ranges.
DELETE
FROM bank_classification
WHERE age < 18
OR age > 100;
5️. Question: How do you create age groups for analysis?
Answer:
Using a derived column with CASE logic.
ALTER TABLE bank_classification
ADD COLUMN age_group VARCHAR(20);
UPDATE bank_classification
SET age_group =
CASE
WHEN age < 30 THEN 'Young'
WHEN age BETWEEN 30 AND 50 THEN 'Middle'
ELSE 'Senior'
END;
6️. Question: How do you find total subscribers vs non-subscribers?
Answer:
By grouping data on the subscription column.
SELECT y,
COUNT(*) AS total_customers
FROM bank_classification
GROUP BY y;
7️. Question: How do you analyze job-wise subscriptions?
Answer:
This helps marketing understand which job group converts better.
SELECT job,
COUNT(*) AS total_customers,
SUM(CASE WHEN y = 'yes' THEN 1 ELSE 0 END) AS subscribed
FROM bank_classification
GROUP BY job
ORDER BY subscribed DESC;
8️. Question: How do you optimize query performance?
Answer:
By creating indexes on frequently filtered columns.
CREATE INDEX idx_job ON bank_classification(job);
CREATE INDEX idx_y ON bank_classification(y);
CREATE INDEX idx_age ON bank_classification(age);
9️ . Question: How do you create views for reporting teams?
Answer:
Views simplify complex queries and provide secure access.
CREATE VIEW vw_subscription_summary AS
SELECT age_group,
job,
marital,
COUNT(*) AS total_customers,
SUM(CASE WHEN y = 'yes' THEN 1 ELSE 0 END) AS subscribers
FROM bank_classification
GROUP BY age_group, job, marital;
10. Question: How do you create stored procedures in real time?
Answer:
Stored procedures encapsulate business logic for reuse.
DELIMITER $$
CREATE PROCEDURE get_subscribed_customers (
IN p_job VARCHAR(50),
IN p_age_group VARCHAR(20)
)
BEGIN
SELECT *
FROM bank_classification
WHERE job = p_job
AND age_group = p_age_group
AND y = 'yes';
END $$
DELIMITER ;
CALL get_subscribed_customers('management', 'Middle');
11. Question: How do you find high-balance customers who did not
subscribe?
Answer:
This is commonly asked in production support.
SELECT *
FROM bank_classification
WHERE balance > 50000
AND y = 'no';
12. Question: Difference between WHERE and HAVING?
Answer:
WHERE filters rows, HAVING filters grouped results.
SELECT job,
COUNT(*) AS total_customers
FROM bank_classification
GROUP BY job
HAVING COUNT(*) > 100;
1️3. Question: How do you find top 3 jobs with highest subscriptions?
SELECT job,
COUNT(*) AS total_subscriptions
FROM bank_classification
WHERE y = 'yes'
GROUP BY job
ORDER BY total_subscriptions DESC
LIMIT 3;
CRT INTERVIEW FINAL ANSWER (IMPORTANT)
Question: What do you do as a SQL developer in real time?
Answer:
In my real-time bank_classification project, I validate imported data, clean
invalid records, create derived columns, optimize queries using indexes, build
views and stored procedures for reporting teams, and support production
issues.