MYSQL Database Administrator (DBA) HANDS ON Tasks

DBA Hands On MySQL in caases with this structured learning plan. Includes practical exercises with solutions covering CRUD operations, joins, stored procedures, triggers, and database administration.

Jun 25, 2025 - 11:58 Updated: Jun 25, 2025 - 12:54

MYSQL Database Administrator (DBA) HANDS ON Tasks

Database Administrator MySQL Hands-On Exercises with Solutions

Case 1: Foundations & CRUD Operations
Topics:
- Installing MySQL
- Creating databases and tables
- Data types
- Basic CRUD (Create, Read, Update, Delete)
Exercises:

1. Install MySQL and create a database called `school`.
2. Create a table `students` with fields: `id`, `name`, `age`, `grade`, `email`.
3. Insert 5 sample students into the table.
4. Write SELECT queries to:
- Get all students
- Get students older than 18
- Sort students by grade
5. Update a student’s grade.
6. Delete one student.

Case 2: Advanced Queries, Joins & Subqueries
Topics:
- Filtering data
- Aggregate functions
- Joins (INNER, LEFT, RIGHT, SELF)
- Subqueries
Exercises:

1. Create a `courses` table and `enrollments` table.
2. Practice JOIN to get:
- All students with their enrolled courses
- Students without courses (LEFT JOIN)
3. Use GROUP BY to count students per course.

4. Use subqueries to:
- Get students in the highest grade
- Find courses with more than 2 students

Case 3: Views, Procedures, Triggers & Transactions
Topics:
- Views
- Stored Procedures
- Triggers
- Transactions
Exercises:

1. Create a view that shows student names and course names.
2. Write a stored procedure to add a new student and enroll them in a course.
3. Create a trigger that logs changes in the `students` table.
4. Perform a transaction:
- Add a student
- Enroll them in a course
- Rollback if the course doesn’t exist

Case 4: User Management, Security & Backup
Topics:
- User roles and privileges
- Backups and restores
- Indexing and optimization
- Import/Export
Exercises:

1. Create a new MySQL user with limited privileges.
2. Use GRANT and REVOKE.
3. Export your `school` database using `mysqldump`.
4. Restore the database from backup.
5. Create indexes and analyze their effect using EXPLAIN.

Case 1: Foundations & CRUD Operations

Exercise 1: Install MySQL and create database

CREATE DATABASE school;
USE school;

Exercise 2: Create students table

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade CHAR(1),
    email VARCHAR(100)
);

Exercise 3: Insert sample data

INSERT INTO students (name, age, grade, email) VALUES
('John Doe', 17, 'A', 'john@example.com'),
('Jane Smith', 18, 'B', 'jane@example.com'),
('Mike Johnson', 16, 'C', 'mike@example.com'),
('Sarah Williams', 19, 'A', 'sarah@example.com'),
('David Brown', 18, 'B', 'david@example.com');

Exercise 4: SELECT queries

-- Get all students
SELECT * FROM students;

-- Get students older than 18
SELECT * FROM students WHERE age > 18;

-- Sort students by grade
SELECT * FROM students ORDER BY grade;

Exercise 5: UPDATE operation

UPDATE students SET grade = 'A' WHERE name = 'Mike Johnson';

Exercise 6: DELETE operation

DELETE FROM students WHERE name = 'David Brown';

Case 2: Advanced Queries, Joins & Subqueries

Exercise 1: Create related tables

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    instructor VARCHAR(50)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Exercise 2: JOIN operations

-- All students with their enrolled courses
SELECT s.name, c.course_name 
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

-- Students without courses
SELECT s.name 
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.enrollment_id IS NULL;

Exercise 3: GROUP BY

SELECT c.course_name, COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;

Exercise 4: Subqueries

-- Students in the highest grade
SELECT name FROM students 
WHERE grade = (SELECT MAX(grade) FROM students);

-- Courses with more than 2 students
SELECT course_name FROM courses
WHERE course_id IN (
    SELECT course_id FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 2
);

Case 3: Views, Procedures, Triggers & Transactions

Exercise 1: Create view

CREATE VIEW student_courses_view AS
SELECT s.name AS student_name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Exercise 2: Stored procedure

DELIMITER //
CREATE PROCEDURE enroll_student(
    IN s_name VARCHAR(50),
    IN s_age INT,
    IN s_grade CHAR(1),
    IN s_email VARCHAR(100),
    IN c_id INT
)
BEGIN
    DECLARE s_id INT;
    INSERT INTO students (name, age, grade, email) VALUES (s_name, s_age, s_grade, s_email);
    SET s_id = LAST_INSERT_ID();
    INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (s_id, c_id, CURDATE());
END //
DELIMITER ;

Exercise 3: Trigger

CREATE TABLE student_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    action VARCHAR(20),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_audit (student_id, action)
    VALUES (NEW.id, 'UPDATE');
END //
DELIMITER ;

Exercise 4: Transaction

START TRANSACTION;
INSERT INTO students (name, age, grade, email) VALUES ('New Student', 20, 'A', 'new@example.com');
SET @last_student = LAST_INSERT_ID();
-- This will fail if course_id 999 doesn't exist
INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (@last_student, 999, CURDATE());
-- Check if error occurred
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

Case 4: User Management, Security & Backup

Exercise 1: Create user

CREATE USER 'school_admin'@'localhost' IDENTIFIED BY 'securepassword123';

Exercise 2: GRANT and REVOKE

GRANT SELECT, INSERT ON school.* TO 'school_admin'@'localhost';
REVOKE INSERT ON school.* FROM 'school_admin'@'localhost';

Exercise 3: Database backup

mysqldump -u root -p school > school_backup.sql

Exercise 4: Restore database

mysql -u root -p school < school_backup.sql

Exercise 5: Indexing

CREATE INDEX idx_student_name ON students(name);
EXPLAIN SELECT * FROM students WHERE name = 'John Doe';

This structured 4-week plan provides:
✅ Progressive learning path from basics to advanced topics
✅ Ready-to-run SQL solutions for all exercises
✅ Real-world database scenarios
✅ Comprehensive coverage of essential MySQL skills