SQL & MySQL · Chapter 07

Week 1 Revision and SQL Problem Solving

Recap of fundamentals, CRUD, filtering, functions, conditional logic and transactions — plus 30 graded practice problems with solutions, interview questions and a mini project.

◷ 6 min read§ 12 sections? 40 interview questions{ } 30 code examples

#Introduction

You've completed the first week of the plan. The goal of this chapter is not to learn something new — it's to make everything you've learned permanent.

#Week 1 Recap

Chapter	Topics
Fundamentals	Database, DBMS vs RDBMS, MySQL architecture, storage engines, data types, constraints, keys
DDL & CRUD	DDL, DML, `CREATE`, `ALTER`, `DROP`, `INSERT`, `UPDATE`, `DELETE`
Filtering	`WHERE`, `LIKE`, `IN`, `BETWEEN`, `NULL`, `REGEXP`
Functions	`ORDER BY`, string, numeric and date functions
Conditional logic	`CASE`, `IF()`, `COALESCE()`, `IFNULL()`, `NULLIF()`
Transactions	ACID, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, auto-commit, `@Transactional`

#The Schema

id	name	department	salary	city	manager_id	joining_date
1	Rahul	IT	70000	Bangalore	10	2022-01-10
2	Amit	HR	50000	Delhi	NULL	2021-04-10
3	Neha	IT	90000	Mumbai	10	2020-03-10
4	Raj	Finance	60000	Bangalore	20	2023-05-10
5	Ankit	IT	75000	Chennai	10	2022-08-10

#Level 1 Questions (Easy)

Q1Problem 1 — Display all employees.

SQL

SELECT *
FROM employees;

Q2Problem 2 — Display only names.

SQL

SELECT name
FROM employees;

Q3Problem 3 — Display employee names and salaries.

SQL

SELECT name, salary
FROM employees;

Q4Problem 4 — Display all IT employees.

SQL

SELECT *
FROM employees
WHERE department = 'IT';

Q5Problem 5 — Salary greater than 70,000.

SQL

SELECT *
FROM employees
WHERE salary > 70000;

Q6Problem 6 — Employees from Bangalore.

SQL

SELECT *
FROM employees
WHERE city = 'Bangalore';

Q7Problem 7 — Employees from Bangalore working in IT.

SQL

SELECT *
FROM employees
WHERE city = 'Bangalore'
  AND department = 'IT';

Q8Problem 8 — Employees from Bangalore OR Delhi.

SQL

SELECT *
FROM employees
WHERE city IN ('Bangalore', 'Delhi');

Q9Problem 9 — Employees whose salary is between 60K and 80K.

SQL

SELECT *
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

Q10Problem 10 — Employees without managers.

SQL

SELECT *
FROM employees
WHERE manager_id IS NULL;

#Level 2 Questions (Intermediate)

Q1Problem 11 — Names starting with R.

SQL

SELECT *
FROM employees
WHERE name LIKE 'R%';

Q2Problem 12 — Names ending with t.

SQL

SELECT *
FROM employees
WHERE name LIKE '%t';

Q3Problem 13 — Names containing "ah".

SQL

SELECT *
FROM employees
WHERE name LIKE '%ah%';

Q4Problem 14 — Sort by salary descending.

SQL

SELECT *
FROM employees
ORDER BY salary DESC;

Q5Problem 15 — Sort by department, then salary descending.

SQL

SELECT *
FROM employees
ORDER BY department, salary DESC;

Q6Problem 16 — Display names in uppercase.

SQL

SELECT UPPER(name)
FROM employees;

Q7Problem 17 — Display the first 3 letters of each name.

SQL

SELECT SUBSTRING(name, 1, 3)
FROM employees;

Q8Problem 18 — Display each employee's time in the company (days).

SQL

SELECT name,
       DATEDIFF(CURDATE(), joining_date)
FROM employees;

Q9Problem 19 — Display today's date.

SQL

SELECT CURDATE();

Q10Problem 20 — Display the current timestamp.

SQL

SELECT NOW();

#Level 3 Questions (Advanced)

Q1Problem 21 — Salary band.

SQL

SELECT name,
       CASE
           WHEN salary >= 80000 THEN 'High'
           WHEN salary >= 60000 THEN 'Medium'
           ELSE 'Low'
       END
FROM employees;

Q2Problem 22 — Replace a NULL bonus with zero.

SQL

SELECT IFNULL(bonus, 0)
FROM employees;

Q3Problem 23 — Display the first available phone.

SQL

SELECT COALESCE(mobile, office_phone, home_phone)
FROM users;

Q4Problem 24 — Department expansion.

SQL

SELECT CASE department
           WHEN 'IT' THEN 'Technology'
           WHEN 'HR' THEN 'Human Resources'
           ELSE department
       END
FROM employees;

Q5Problem 25 — Custom department order.

SQL

SELECT *
FROM employees
ORDER BY CASE
             WHEN department = 'IT'      THEN 1
             WHEN department = 'Finance' THEN 2
             ELSE 3
         END;

#Transaction Questions

Q1Problem 26 — Transfer money.

SQL

START TRANSACTION;

UPDATE account
SET balance = balance - 5000
WHERE id = 1;

UPDATE account
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

Q2Problem 27 — Undo a transaction.

SQL

ROLLBACK;

Q3Problem 28 — Create a savepoint.

SQL

SAVEPOINT payment_done;

Q4Problem 29 — Roll back to a savepoint.

SQL

ROLLBACK TO payment_done;

Q5Problem 30 — Disable auto-commit.

SQL

SET autocommit = 0;

#Interview Questions (Must Know)

Q1Difference between DELETE, TRUNCATE and DROP?

DELETE removes selected rows and can be rolled back; TRUNCATE removes all rows quickly, resets AUTO_INCREMENT and can't be rolled back; DROP removes the table itself.

Q2Difference between CHAR and VARCHAR?

CHAR is fixed-length and padded; VARCHAR is variable-length.

Q3Difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY (covered in the HAVING chapter).

Q4Difference between IFNULL() and COALESCE()?

IFNULL takes two arguments (MySQL-specific); COALESCE takes many and returns the first non-NULL (standard SQL).

Q5Difference between CASE and IF()?

CASE is standard and supports many branches; IF() is a MySQL-specific single true/false choice.

Q6Difference between NOW() and CURDATE()?

NOW() returns date and time; CURDATE() returns only the date.

Q7Difference between COMMIT and ROLLBACK?

COMMIT makes changes permanent; ROLLBACK undoes uncommitted changes.

Q8What is ACID?

Atomicity, Consistency, Isolation and Durability — the guarantees of a reliable transaction.

Q9Difference between DBMS and RDBMS?

An RDBMS stores data in related tables linked by keys, adding referential integrity and SQL; a plain DBMS doesn't enforce relationships.

Q10Explain the MySQL query flow.

Client

JDBC

Parser

Optimizer

Executor

Storage engine

Disk

#Mini Project — Employee Management System

Create the tables employees and departments.
Insert 10 employees.
Write queries for: IT employees, highest salary, lowest salary, employees from Bangalore, salary > 70000, joined after 2022, names starting with A, uppercase names, salary bands and department expansion.
Transactions: increase salaries → roll back → increase again → commit.

#Week 1 Cheat Sheet

SQL Logical Execution Order

FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

LIMIT

LIKE

Wildcard	Meaning
`%`	Any number of characters
`_`	Exactly one character

NULL

NULL ≠ 0 ≠ ''. Always use IS NULL / IS NOT NULL.

CASE, COALESCE and Transactions

Construct	Shape
`CASE`	`CASE WHEN ... THEN ... ELSE ... END`
`COALESCE`	`COALESCE(NULL, NULL, 100)` → `100`
Transaction	`START TRANSACTION` → SQL → `COMMIT` or `ROLLBACK`

#Week 1 Challenge

Try solving these without looking at the answers:

Find employees earning exactly 75,000.
Find employees not in IT.
Find employees whose names have exactly 5 characters.
Show salaries rounded to the nearest thousand.
Display employee names with their city in uppercase.
Replace NULL manager IDs with "No Manager".
Sort employees by salary descending and then name ascending.
Show employees who joined in the last 365 days.
Classify salaries into Low, Medium and High using CASE.
Simulate a money transfer using START TRANSACTION, COMMIT and ROLLBACK.

#What You Can Do Now

CRUD operations and DDL commands
Filtering (WHERE, LIKE, IN, BETWEEN)
Sorting (ORDER BY)
String, numeric and date functions
Conditional logic (CASE, IF, COALESCE, IFNULL, NULLIF)
Transactions (COMMIT, ROLLBACK, SAVEPOINT)
The SQL execution order and common interview questions

This is already enough to handle many day-to-day backend queries in a Spring Boot application.