DBMS Exercises

Database Management System (DBMS) is one of the most important concepts in computer science. It allows us to store, organize, and manage large amounts of data efficiently. Learning DBMS is not only about understanding the theory but also about practicing exercises that strengthen your problem-solving and query-writing skills.

In this post, we’ll explore some DBMS exercises that will help beginners understand the practical side of databases.

---

Why Practice DBMS Exercises?

• To understand how data is stored and retrieved.
• To learn SQL (Structured Query Language) commands.
• To solve real-world problems like managing student records, employee databases, or e-commerce data.
• To prepare for exams, interviews, and competitive programming.

---

Basic DBMS Exercises

1. Create a Database

Write an SQL query to create a database called college.

CREATE DATABASE college;

2. Create a Table

Inside the college database, create a table students with the following fields:

• roll_no (Primary Key)
• name
• age
• course

CREATE TABLE students (
    roll_no INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    course VARCHAR(50)
);

---

Insertion Exercises

3. Insert Records

Insert at least 5 records into the students table.

INSERT INTO students (roll_no, name, age, course) VALUES
(101, 'Riya', 20, 'B.Tech'),
(102, 'Aman', 21, 'BCA'),
(103, 'Sita', 22, 'MBA'),
(104, 'Arjun', 19, 'B.Com'),
(105, 'Meera', 20, 'B.Tech');

---

Retrieval Exercises

4. Display All Records

Show all student details.

SELECT * FROM students;

5. Retrieve Specific Columns

Show only name and course of all students.

SELECT name, course FROM students;

6. Apply a Condition

Find details of students enrolled in B.Tech.

SELECT * FROM students WHERE course = 'B.Tech';

---

Update and Delete Exercises

7. Update Record

Change the course of student Aman to MCA.

UPDATE students SET course = 'MCA' WHERE name = 'Aman';

8. Delete a Record

Delete the record of student with roll number 104.

DELETE FROM students WHERE roll_no = 104;

---

Advanced DBMS Exercises

9. Sorting

Display all students sorted by age in ascending order.

SELECT * FROM students ORDER BY age ASC;

10. Counting Records

Count how many students are enrolled in B.Tech.

SELECT COUNT(*) FROM students WHERE course = 'B.Tech';

11. Grouping

Group students by course and count how many students are in each course.

SELECT course, COUNT(*) 
FROM students 
GROUP BY course;

12. Joins Exercise

Suppose we have another table courses with course details:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    duration VARCHAR(20)
);

Now join the students and courses table to display student names along with course duration.

SELECT s.name, c.duration 
FROM students s
JOIN courses c 
ON s.course = c.course_name;

---

Real-World DBMS Practice Questions

1. Create an employees table with fields: emp_id, emp_name, department, salary.
2. Insert 5 records into the employees table.
3. Write a query to display employees whose salary is greater than 40,000.
4. Update the salary of one employee by 10%.
5. Delete an employee record with a specific emp_id.
6. Write a query to display the average salary of all employees.
7. Create a relation between employees and a departments table using foreign key.

---

Benefits of Solving DBMS Exercises

• Better Understanding: Hands-on practice makes concepts easier.
• Skill Development: You’ll improve your SQL writing skills.
• Problem-Solving Mindset: Real-world exercises prepare you for interviews.
• Strong Foundation: Helps in learning advanced topics like transactions, triggers, and stored procedures.

---

Final Thoughts

DBMS is a vast subject, but practice is the key to mastery. By solving these exercises regularly, you’ll not only understand SQL better but also gain confidence in handling real-life database problems.

Start with simple queries, then move to joins, constraints, and subqueries. The more you practice, the more fluent you’ll become in DBMS.

---

Tip for learners: Create your own sample database (like library management, e-commerce, or hospital system) and practice queries on it. This will make learning fun and practical.