CREATE TABLE STUDENT24(
regno VARCHAR(10) PRIMARY KEY,
name VARCHAR(50),
major VARCHAR(50),
bdate DATE
);
CREATE TABLE COURSE2(
course INT PRIMARY KEY,
cname VARCHAR(50),
dept VARCHAR(50)
);
CREATE TABLE TEXT24(
book_ISBN# INT PRIMARY KEY,
book_title VARCHAR(100),
publisher VARCHAR(50),
author VARCHAR(50)
);
CREATE TABLE ENROLL2(
regno VARCHAR(10),
course INT,
sem INT,
marks INT,
PRIMARY KEY (regno, course, sem),
FOREIGN KEY (regno) REFERENCES STUDENT24(regno),
FOREIGN KEY (course) REFERENCES COURSE2(course)
);
CREATE TABLE BOOK_ADOPTION2(
course INT,
sem INT,
book_ISBN# INT,
PRIMARY KEY (course, sem, book_ISBN#),
FOREIGN KEY (course) REFERENCES COURSE2(course),
FOREIGN KEY (book_ISBN#) REFERENCES TEXT24(book_ISBN#)
);
INSERT INTO STUDENT24 VALUES('S101', 'Alice Johnson', 'Computer Science', '20-may-2015');
INSERT INTO STUDENT24 VALUES('S102','Smith','Maths','20-jan-2022');
INSERT INTO STUDENT24 VALUES('S103', 'Charlie Brown', 'Physics', '20-FEB-2010');
INSERT INTO STUDENT24 VALUES('S104', 'David White', 'Computer Science', '20-MAR-2000');
INSERT INTO STUDENT24 VALUES('S105', 'Emma Davis', 'Mathematics', '20-SEP-2012');
INSERT INTO STUDENT24 VALUES('S106', 'Frank Green', 'Physics', '20-JUN-2025');
INSERT INTO STUDENT24 VALUES('S107', 'Grace Hall', 'Computer Science', '20-JAN-2018');
INSERT INTO COURSE2 VALUES(101, 'Data Structures', 'Computer Science');
INSERT INTO COURSE2 VALUES(102, 'Algorithms', 'Computer Science');
INSERT INTO COURSE2 VALUES(103, 'Calculus', 'Mathematics');
INSERT INTO COURSE2 VALUES(104, 'Linear Algebra', 'Mathematics');
INSERT INTO COURSE2 VALUES(105, 'Quantum Physics', 'Physics');
INSERT INTO COURSE2 VALUES(106, 'Thermodynamics', 'Physics');
INSERT INTO TEXT24 VALUES(1001, 'Introduction to Data Structures', 'Pearson', 'John Doe');
INSERT INTO TEXT24 VALUES(1002, 'Advanced Algorithms', 'O’Reilly', 'Jane Smith');
INSERT INTO TEXT24 VALUES(1003, 'Calculus Made Easy', 'Springer', 'James Brown');
INSERT INTO TEXT24 VALUES(1004, 'Linear Algebra for Beginners', 'McGraw-Hill', 'Robert White');
INSERT INTO TEXT24 VALUES(1005, 'Quantum Mechanics Explained', 'Cambridge', 'William Black');
INSERT INTO TEXT24 VALUES(1006, 'Thermodynamics Basics', 'Oxford', 'Emily Adams');
INSERT INTO TEXT24 VALUES(1007, 'Deep Learning Fundamentals', 'Pearson', 'Alice Williams');
INSERT INTO ENROLL2 VALUES('S101', 101, 1, 85);
INSERT INTO ENROLL2 VALUES('S102', 103, 1, 78);
INSERT INTO ENROLL2 VALUES('S103', 105, 1, 90);
INSERT INTO ENROLL2 VALUES('S104', 102, 2, 88);
INSERT INTO ENROLL2 VALUES('S105', 104, 2, 92);
INSERT INTO ENROLL2 VALUES('S106', 106, 2, 76);
INSERT INTO ENROLL2 VALUES('S107', 101, 1, 84);
INSERT INTO BOOK_ADOPTION2 VALUES(101, 1, 1001);
INSERT INTO BOOK_ADOPTION2 VALUES(102, 2, 1002);
INSERT INTO BOOK_ADOPTION2 VALUES(103, 1, 1003);
INSERT INTO BOOK_ADOPTION2 VALUES(104, 2, 1004);
INSERT INTO BOOK_ADOPTION2 VALUES(105, 1, 1005);
INSERT INTO BOOK_ADOPTION2 VALUES(106, 2, 1006);
INSERT INTO BOOK_ADOPTION2 VALUES(101, 1, 1007);
Query 1
SELECT S.regno, S.name, S.major, C.course, C.cname, E.sem
FROM STUDENT24 S
JOIN ENROLL2 E ON S.regno = E.regno
JOIN COURSE2 C ON E.course = C.course
ORDER BY E.sem;
Query 2
SELECT S.regno, S.name, S.major, C.course, C.cname, E.sem
FROM STUDENT24 S
JOIN ENROLL2 E ON S.regno = E.regno
JOIN COURSE2 C ON E.course = C.course
WHERE C.dept = 'Computer Science'
ORDER BY E.sem;
Query 3
SELECT T.book_ISBN#, T.book_title, T.publisher, T.author
FROM TEXT24 T
JOIN BOOK_ADOPTION2 B ON T.book_ISBN# = B.book_ISBN#
WHERE B.course = 101;
Query 4
SELECT C.course, C.cname, COUNT(E.regno) AS student_count FROM COURSE2 C
JOIN ENROLL2 E ON C.course = E.course GROUP BY C.course, C.cname HAVING COUNT(E.regno)>1;
Query 5
SELECT T.publisher, COUNT(T.book_ISBN#) AS book_count
FROM TEXT24 T
GROUP BY T.publisher
HAVING COUNT(T.book_ISBN#) > 1;
