Practice Exercises & Next Steps

Set up the practice schema

CREATE DATABASE practice CHARACTER SET utf8mb4;
USE practice;

CREATE TABLE departments (
  code VARCHAR(10) PRIMARY KEY,
  name_en VARCHAR(100) NOT NULL
);

CREATE TABLE students (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name_en VARCHAR(100) NOT NULL,
  name_bn VARCHAR(100),
  dept_code VARCHAR(10),
  cgpa DECIMAL(3,2),
  admitted_on DATE,
  FOREIGN KEY (dept_code) REFERENCES departments(code)
);

CREATE TABLE courses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  credits TINYINT DEFAULT 3
);

CREATE TABLE enrollments (
  student_id INT UNSIGNED,
  course_id INT UNSIGNED,
  grade CHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO departments VALUES ('CSE','Computer Science'),('EEE','Electrical'),('BBA','Business');
INSERT INTO students (name_en, name_bn, dept_code, cgpa, admitted_on) VALUES
 ('Rahim Uddin','রহিম উদ্দিন','CSE',3.85,'2023-01-10'),
 ('Karim Ali','করিম আলী','CSE',3.20,'2023-01-10'),
 ('Fatema Begum','ফাতেমা বেগম','EEE',3.95,'2024-02-01'),
 ('Nusrat Jahan','নুসরাত জাহান','BBA',2.90,'2024-02-01'),
 ('Tanvir Hasan','তানভীর হাসান','CSE',3.60,'2025-01-15');
INSERT INTO courses (title, credits) VALUES ('Database Systems',3),('Algorithms',3),('Accounting',2);
INSERT INTO enrollments VALUES (1,1,'A'),(1,2,'B'),(2,1,'B'),(3,2,'A'),(4,3,'C');

Beginner

1. List every student sorted by CGPA, highest first.
2. Show students admitted in 2024.
3. Count the students in each department.
4. Find students whose English name starts with "T".
5. Show the three highest CGPAs.

Intermediate

6. Show each student with their department's full English name.
7. List students who are not enrolled in any course.
8. Find the average CGPA per department, but only where the department has at least two students.
9. Show each course with how many students take it, including courses nobody takes.
10. Find students whose CGPA is above the overall average.

Advanced

11. Rank students within their department by CGPA using RANK().
12. Show the top student in each department using a CTE.
13. For each student, show their CGPA and the difference from their department's average.
14. Build a monthly admissions report with no gaps for months that had no admissions.
15. Write a stored procedure that takes a department code and returns its student count and average CGPA.
16. Add an audit trigger that records every CGPA change.

Try each one yourself first, then check with EXPLAIN whether your query uses an index.

Learning roadmap

Stage	Focus
Weeks 1–2	SELECT, WHERE, ORDER BY, functions, GROUP BY
Weeks 3–4	Joins, subqueries, CTEs, window functions
Weeks 5–6	Schema design, normalization, keys and constraints
Weeks 7–8	Indexes, EXPLAIN, transactions and locking
Ongoing	Backups, replication, security, tuning

Common interview questions

• DELETE vs TRUNCATE vs DROP
• WHERE vs HAVING
• UNION vs UNION ALL
• INNER JOIN vs LEFT JOIN, and how to find unmatched rows
• What the normal forms are and when you would denormalise
• What ACID means and what each isolation level prevents
• How an index works and when MySQL will not use one
• How you would debug a query that suddenly got slow
• How you would recover from an accidental DELETE in production
• RANK() vs DENSE_RANK() vs ROW_NUMBER()

Where to go next

Read the official MySQL Reference Manual for the exact behaviour of any statement, and practise on real datasets — a public dataset you load yourself will teach you more about indexing and query plans than any tutorial table of five rows.