Assignment 2 DBMS
Instructor: Maharshi M Chaudhary
A. Creating the University database schema
CREATE DATABASE University;
USE University;
B. Creating Tables:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Gender VARCHAR(10),
Address VARCHAR(255)
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100),
Credits INT
);
--Enrollments Table created
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
-- Insert records into Students table
INSERT INTO Students (StudentID, Name, Age, Gender, Address) VALUES (101, 'Alice', 20, 'Female', '123
Main Street');
INSERT INTO Students (StudentID, Name, Age, Gender, Address) VALUES (102, 'Bob', 22, 'Male', '456 Elm
Avenue');
INSERT INTO Students (StudentID, Name, Age, Gender, Address) VALUES (103, 'Charlie', 19, 'Male', '789
Oak Lane');
INSERT INTO Students (StudentID, Name, Age, Gender, Address) VALUES (104, 'Diana', 21, 'Female', '101
Pine Street');
INSERT INTO Students (StudentID, Name, Age, Gender, Address) VALUES (105, 'Emily', 23, 'Female', '202
Cedar Road');
-- Insert records into Courses table
INSERT INTO Courses (CourseID, CourseName, Instructor, Credits) VALUES (201, 'Introduction to AI', 'Dr.
Smith', 3);
INSERT INTO Courses (CourseID, CourseName, Instructor, Credits) VALUES (202, 'Machine Learning', 'Prof.
Johnson', 4);
INSERT INTO Courses (CourseID, CourseName, Instructor, Credits) VALUES (203, 'Deep Learning', 'Dr.
Brown', 4);
INSERT INTO Courses (CourseID, CourseName, Instructor, Credits) VALUES (204, 'Data Science', 'Prof.
White', 3);
INSERT INTO Courses (CourseID, CourseName, Instructor, Credits) VALUES (205, 'Natural Language
Processing', 'Dr. Green', 3);
-- Insert records into Enrollments table
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrollmentDate) VALUES (1, 101, 201,
TO_DATE('2024-03-15', 'YYYY-MM-DD'));
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrollmentDate) VALUES (2, 102, 202,
TO_DATE('2024-03-16', 'YYYY-MM-DD'));
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrollmentDate) VALUES (3, 103, 203,
TO_DATE('2024-03-17', 'YYYY-MM-DD'));
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrollmentDate) VALUES (4, 104, 204,
TO_DATE('2024-03-18', 'YYYY-MM-DD'));
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, EnrollmentDate) VALUES (5, 105, 205,
TO_DATE('2024-03-19', 'YYYY-MM-DD'));
QUESTIONS
1. Update the age of the student named "Charlie" to 20 years old.
SQL queries:
UPDATE Students
SET Age = 20
WHERE Name = 'Charlie';
2. Delete the enrollment records for the student named "Diana".
SQLqueires:
DELETE FROM Enrollments
WHERE StudentID IN (SELECT StudentID FROM Students WHERE Name = 'Diana');
3. Update the instructor for the course with CourseID 205 to "Prof. Martinez".
SQL Queries:
UPDATE Courses
SET Instructor = 'Prof. Martinez'
WHERE CourseID = 205;
4. Delete the enrollment record for the student with StudentID 102 who enrolled in the course with
CourseID 202.
SQL Queries:
DELETE FROM Enrollments
WHERE StudentID = 102 AND CourseID = 202;
Retrieve the following details:
Name and Age of all students.
SELECT Name, Age
FROM Students;
CourseName and Instructor of all courses.
SELECT CourseName, Instructor
FROM Courses;
EnrollmentDate of all enrollments.
SELECT EnrollmentDate
FROM Enrollments;
