Program 6: Student Enrollment and Book Adoption Database
1. Table Creation
CREATE TABLE STUDENT (
StudentID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(100),
Major VARCHAR(50),
BirthDate DATE
);
CREATE TABLE COURSE (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100)
);
CREATE TABLE TEXT (
TextID VARCHAR(10) PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100)
);
CREATE TABLE ENROLL (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
);
CREATE TABLE BOOK_ADOPTION (
CourseID VARCHAR(10),
TextID VARCHAR(10),
PRIMARY KEY (CourseID, TextID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID),
FOREIGN KEY (TextID) REFERENCES TEXT(TextID)
);
2. Insert Statements
-- STUDENT
INSERT INTO STUDENT VALUES ('S001', 'Alice', 'Computer Science', TO_DATE('2000-01-
15','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S002', 'Bob', 'Electronics', TO_DATE('1999-03-20','YYYY-
MM-DD'));
INSERT INTO STUDENT VALUES ('S003', 'Charlie', 'Computer Science', TO_DATE('1998-11-
10','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S004', 'David', 'Mechanical', TO_DATE('2001-07-
25','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S005', 'Eva', 'Computer Science', TO_DATE('1999-05-
14','YYYY-MM-DD'));
INSERT INTO STUDENT VALUES ('S006', 'Fay', 'Electronics', TO_DATE('2000-12-05','YYYY-
MM-DD'));
INSERT INTO STUDENT VALUES ('S007', 'George', 'Computer Science', TO_DATE('1997-08-
30','YYYY-MM-DD'));
-- COURSE
INSERT INTO COURSE VALUES ('C001', 'Database Systems', 'Dr. Smith');
INSERT INTO COURSE VALUES ('C002', 'Data Structures', 'Dr. Johnson');
INSERT INTO COURSE VALUES ('C003', 'Operating Systems', 'Dr. Brown');
INSERT INTO COURSE VALUES ('C004', 'Networks', 'Dr. White');
-- TEXT
INSERT INTO TEXT VALUES ('T001', 'Database Management Systems', 'Raghu
Ramakrishnan');
INSERT INTO TEXT VALUES ('T002', 'Data Structures Using C', 'Reema Thareja');
INSERT INTO TEXT VALUES ('T003', 'Operating System Concepts', 'Silberschatz');
INSERT INTO TEXT VALUES ('T004', 'Computer Networking', 'Kurose and Ross');
-- ENROLL
INSERT INTO ENROLL VALUES ('S001', 'C001');
INSERT INTO ENROLL VALUES ('S001', 'C002');
INSERT INTO ENROLL VALUES ('S002', 'C002');
INSERT INTO ENROLL VALUES ('S003', 'C003');
INSERT INTO ENROLL VALUES ('S004', 'C001');
INSERT INTO ENROLL VALUES ('S005', 'C004');
INSERT INTO ENROLL VALUES ('S006', 'C003');
INSERT INTO ENROLL VALUES ('S007', 'C001');
-- BOOK_ADOPTION
INSERT INTO BOOK_ADOPTION VALUES ('C001', 'T001');
INSERT INTO BOOK_ADOPTION VALUES ('C002', 'T002');
INSERT INTO BOOK_ADOPTION VALUES ('C003', 'T003');
INSERT INTO BOOK_ADOPTION VALUES ('C004', 'T004');
3. Queries
1. List all students majoring in Computer Science.
SELECT * FROM STUDENT WHERE Major = 'Computer Science';
2. Find all courses taught by 'Dr. Johnson'.
SELECT * FROM COURSE WHERE Instructor = 'Dr. Johnson';
3. List all students enrolled in the 'Database Systems' course.
SELECT S.StudentID, S.Name
FROM STUDENT S
JOIN ENROLL E ON S.StudentID = E.StudentID
JOIN COURSE C ON E.CourseID = C.CourseID
WHERE C.CourseName = 'Database Systems';
4. Find the courses along with the titles of textbooks adopted.
SELECT C.CourseName, T.Title
FROM COURSE C
JOIN BOOK_ADOPTION B ON C.CourseID = B.CourseID
JOIN TEXT T ON B.TextID = T.TextID;
5. Find the names of students enrolled in more than one course.
SELECT S.Name
FROM STUDENT S
JOIN ENROLL E ON S.StudentID = E.StudentID
GROUP BY S.Name
HAVING COUNT(*) > 1;
6. Display course names and the number of students enrolled in each course.
SELECT C.CourseName, COUNT(E.StudentID) AS NumStudents
FROM COURSE C
JOIN ENROLL E ON C.CourseID = E.CourseID
GROUP BY C.CourseName;
7. Find the name and major of the youngest student.
SELECT Name, Major
FROM STUDENT
WHERE BirthDate = (SELECT MAX(BirthDate) FROM STUDENT);
8. Find the title and author of the book adopted for 'Operating Systems' course.
SELECT T.Title, T.Author
FROM COURSE C
JOIN BOOK_ADOPTION B ON C.CourseID = B.CourseID
JOIN TEXT T ON B.TextID = T.TextID
WHERE C.CourseName = 'Operating Systems';
