📘 DBMS Scenario-Based + Table-Based Questions with
Answers
🏥 Scenario 1: Hospital Management System
📋 Table: Patients
PatientID Name Age Gender DoctorID
1 Ali Raza 30 Male 101
2 Sana Khan 25 Female 102
3 Usman Ali 40 Male 101
📋 Table: Doctors
DoctorID Name Specialty
101 Dr. Ahmad Cardiologist
102 Dr. Fatima Dermatologist
❓Q1: Write a query to display all patients with their assigned doctor names.
✅ Answer:
SELECT Patients.Name AS PatientName, Doctors.Name AS DoctorName
FROM Patients
INNER JOIN Doctors ON Patients.DoctorID = Doctors.DoctorID;
❓Q2: Write a query to add a new patient.
✅ Answer:
INSERT INTO Patients (PatientID, Name, Age, Gender, DoctorID)
VALUES (4, 'Ayesha Noor', 29, 'Female', 102);
❓Q3: Update the age of the patient 'Ali Raza' to 31.
✅ Answer:
UPDATE Patients
SET Age = 31
WHERE Name = 'Ali Raza';
❓Q4: Delete patient record where PatientID = 3.
✅ Answer:
DELETE FROM Patients
WHERE PatientID = 3;
❓Q5: Add a column PhoneNumber to Patients table.
✅ Answer:
ALTER TABLE Patients
ADD PhoneNumber VARCHAR(15);
🏫 Scenario 2: Student Course Enrollment
📋 Table: Students
StudentID Name Age Department
1 Ali Khan 20 CS
2 Maria Aslam 21 IT
📋 Table: Courses
CourseID CourseName Credits
101 DBMS 3
102 Web Dev 4
📋 Table: Enrollments
StudentID CourseID
1 101
1 102
2 101
❓Q6: Show all students with the courses they are enrolled in.
✅ Answer:
SELECT Students.Name, Courses.CourseName
FROM Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
❓Q7: Create the Enrollments table with foreign keys.
✅ Answer:
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Q8: Get the total number of students enrolled in DBMS.
✅ Answer:
sql
CopyEdit
SELECT COUNT(*) AS TotalStudents
FROM Enrollments
WHERE CourseID = 101;
📚 Topic: Library Management System
📋 Table: Books
BookID Title Author Available
1 DBMS Concepts Elmasri Yes
2 OS Principles Silberschatz No
📋 Table: Members
MemberID Name JoinDate
101 Ali Raza 2023-01-15
102 Sara Khan 2023-02-20
📋 Table: Borrow
MemberID BookID BorrowDate
101 2 2024-05-10
102 1 2024-05-11
📝 SQL Questions and Answers
❓ Q1: Create the Books table
✅ Answer:
sql
CopyEdit
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100),
Available VARCHAR(10)
);
❓ Q2: Insert a new book
✅ Answer:
sql
CopyEdit
INSERT INTO Books (BookID, Title, Author, Available)
VALUES (3, 'Data Structures', 'Lipschutz', 'Yes');
❓ Q3: Select all available books
✅ Answer:
sql
CopyEdit
SELECT * FROM Books WHERE Available = 'Yes';
❓ Q4: Add a column 'Genre' to Books
✅ Answer:
sql
CopyEdit
ALTER TABLE Books ADD Genre VARCHAR(50);
❓ Q5: Update availability of BookID = 2 to 'Yes'
✅ Answer:
sql
CopyEdit
UPDATE Books SET Available = 'Yes' WHERE BookID = 2;
❓ Q6: Delete a book record where BookID = 3
✅ Answer:
sql
CopyEdit
DELETE FROM Books WHERE BookID = 3;
❓ Q7: Select all members who borrowed books with book titles
✅ Answer:
sql
CopyEdit
SELECT Members.Name, Books.Title, Borrow.BorrowDate
FROM Borrow
JOIN Members ON Borrow.MemberID = Members.MemberID
JOIN Books ON Borrow.BookID = Books.BookID;
❓ Q8: Select members who borrowed books on or after '2024-05-10'
✅ Answer:
sql
CopyEdit
SELECT Members.Name FROM Borrow
JOIN Members ON Borrow.MemberID = Members.MemberID
WHERE Borrow.BorrowDate >= '2024-05-10';
❓ Q9: Select books not yet borrowed (using subquery)
✅ Answer:
sql
CopyEdit
SELECT * FROM Books
WHERE BookID NOT IN (SELECT BookID FROM Borrow);
� Additional Basic SQL Tasks
✅ Create Table Example
sql
CopyEdit
CREATE TABLE Teachers (
TeacherID INT PRIMARY KEY,
Name VARCHAR(50),
Subject VARCHAR(50)
);
✅ Insert Example
sql
CopyEdit
INSERT INTO Teachers (TeacherID, Name, Subject)
VALUES (1, 'Mr. Bilal', 'Mathematics');
