Mastering Database Management

0 of 81 lessons complete (0%)
Exit Course

Lab Assignment/Tutorial

Lab File

You don’t have access to this lesson

Please register or sign in to access the course content.

DBMS Lab Questions

DBMS Lab Questions

Questions 1: Student-Course Database

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    dept VARCHAR(50)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    credits INT
);

CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
  1. Display all students enrolled in a specific course.
  2. Find students not enrolled in any course.
  3. Create a view of student-course details.
  4. Find students enrolled in more than one course using nested query.
  5. Use UNION to combine student names from two departments.

Questions 2: Employee-Department Database

CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);
  1. List employees with their department names.
  2. Find highest paid employee using nested query.
  3. Show employees without department.
  4. Create a view for employees with salary greater than 50000.
  5. Use INTERSECT for salary comparison.

Questions 3: Library Management

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50)
);

CREATE TABLE Members (
    member_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Issued (
    book_id INT,
    member_id INT,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (member_id) REFERENCES Members(member_id)
);
  1. List books issued to members.
  2. Find books not issued.
  3. Create a view of issued books.
  4. Find members who issued more than 2 books.
  5. Use EXCEPT to find books not issued.

Questions 4: Online Shopping

CREATE TABLE Customers (
    cust_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    cust_id INT,
    FOREIGN KEY (cust_id) REFERENCES Customers(cust_id)
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);
  1. List customers and their orders.
  2. Find customers without orders.
  3. Create a view of customer orders.
  4. Find customers who placed more than one order.
  5. Use UNION of customer and product names.

Questions 5: Hospital Database

CREATE TABLE Doctors (
    doc_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Patients (
    patient_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Appointments (
    doc_id INT,
    patient_id INT,
    FOREIGN KEY (doc_id) REFERENCES Doctors(doc_id),
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);
  1. List doctor-patient appointments.
  2. Find doctors with no patients.
  3. Create a view of appointments.
  4. Find doctors treating more than 2 patients.
  5. Use INTERSECT for patients.

Questions 6: Banking System

CREATE TABLE Customers (
    cust_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Accounts (
    acc_id INT PRIMARY KEY,
    cust_id INT,
    balance INT,
    FOREIGN KEY (cust_id) REFERENCES Customers(cust_id)
);
  1. List customers with account details.
  2. Find customers with no accounts.
  3. Create view for high balance accounts.
  4. Find customer with maximum balance.
  5. Use EXCEPT for customers without accounts.

Questions 7: College Faculty

CREATE TABLE Faculty (
    faculty_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(50)
);

CREATE TABLE Teaching (
    faculty_id INT,
    subject_id INT,
    FOREIGN KEY (faculty_id) REFERENCES Faculty(faculty_id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id)
);
  1. List faculty with subjects.
  2. Find faculty not teaching any subject.
  3. Create teaching view.
  4. Find faculty teaching more than 2 subjects.
  5. Use UNION of faculty and subject names.

Questions 8: Hotel Booking

CREATE TABLE Guests (
    guest_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Rooms (
    room_id INT PRIMARY KEY,
    type VARCHAR(50)
);

CREATE TABLE Booking (
    guest_id INT,
    room_id INT,
    FOREIGN KEY (guest_id) REFERENCES Guests(guest_id),
    FOREIGN KEY (room_id) REFERENCES Rooms(room_id)
);
  1. List guests with booked rooms.
  2. Find unbooked rooms.
  3. Create booking view.
  4. Find guests with multiple bookings.
  5. Use EXCEPT for rooms not booked.

Questions 9: Movie Database

CREATE TABLE Movies (
    movie_id INT PRIMARY KEY,
    title VARCHAR(50)
);

CREATE TABLE Actors (
    actor_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Casts (
    movie_id INT,
    actor_id INT,
    FOREIGN KEY (movie_id) REFERENCES Movies(movie_id),
    FOREIGN KEY (actor_id) REFERENCES Actors(actor_id)
);
  1. List movies with actors.
  2. Find movies without actors.
  3. Create movie-cast view.
  4. Find actors in multiple movies.
  5. Use INTERSECT for actors.

Questions 10: Transport System

CREATE TABLE Drivers (
    driver_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Vehicles (
    vehicle_id INT PRIMARY KEY,
    type VARCHAR(50)
);

CREATE TABLE Assignments (
    driver_id INT,
    vehicle_id INT,
    FOREIGN KEY (driver_id) REFERENCES Drivers(driver_id),
    FOREIGN KEY (vehicle_id) REFERENCES Vehicles(vehicle_id)
);
  1. List drivers with vehicles.
  2. Find drivers without vehicles.
  3. Create assignment view.
  4. Find drivers assigned multiple vehicles.
  5. Use UNION for drivers and vehicle types.