CREATE TABLE STUDENT (
regno VARCHAR(20) PRIMARY KEY,
name VARCHAR(50),
major VARCHAR(20),
bdate DATE
);
CREATE TABLE COURSE (
course# INT PRIMARY KEY,
cname VARCHAR(30),
dept VARCHAR(30)
);
CREATE TABLE ENROLL (
regno VARCHAR(20),
course# INT,
sem INT,
book_isbn INT,
PRIMARY KEY (regno, course#, sem),
FOREIGN KEY (regno) REFERENCES STUDENT(regno),
FOREIGN KEY (course#) REFERENCES COURSE(course#)
);
CREATE TABLE BOOK_ADOPTION (
course# INT,
sem INT,
book_isbn INT,
PRIMARY KEY (course#, sem, book_isbn),
FOREIGN KEY (course#, sem) REFERENCES ENROLL(course#, sem)
);
CREATE TABLE TEXT (
book_isbn INT PRIMARY KEY,
booktitle VARCHAR(50),
publisher VARCHAR(50),
author VARCHAR(50)
);
-----------------------------------------------------------------------------------
-----------------------------------------------------
-- Insert into STUDENT
INSERT INTO STUDENT VALUES ('S1', 'John Doe', 'Computer Science', '2000-01-01');
INSERT INTO STUDENT VALUES ('S2', 'Jane Smith', 'Electrical Engineering', '2001-02-
02');
INSERT INTO STUDENT VALUES ('S3', 'Alice Johnson', 'Mechanical Engineering', '1999-
03-03');
INSERT INTO STUDENT VALUES ('S4', 'Bob Williams', 'Computer Science', '2002-04-
04');
INSERT INTO STUDENT VALUES ('S5', 'Emily Brown', 'Civil Engineering', '2003-05-
05');
-- Insert into COURSE
INSERT INTO COURSE VALUES (101, 'Introduction to Programming', 'Computer Science');
INSERT INTO COURSE VALUES (102, 'Circuit Analysis', 'Electrical Engineering');
INSERT INTO COURSE VALUES (103, 'Thermodynamics', 'Mechanical Engineering');
INSERT INTO COURSE VALUES (104, 'Data Structures', 'Computer Science');
INSERT INTO COURSE VALUES (105, 'Structural Analysis', 'Civil Engineering');
-- Insert into ENROLL
INSERT INTO ENROLL VALUES ('S1', 101, 1, 1001);
INSERT INTO ENROLL VALUES ('S2', 102, 1, 1002);
INSERT INTO ENROLL VALUES ('S3', 103, 1, 1003);
INSERT INTO ENROLL VALUES ('S4', 104, 1, 1004);
INSERT INTO ENROLL VALUES ('S5', 105, 1, 1005);
-- Insert into BOOK_ADOPTION
INSERT INTO BOOK_ADOPTION VALUES (101, 1, 1001);
INSERT INTO BOOK_ADOPTION VALUES (102, 1, 1002);
INSERT INTO BOOK_ADOPTION VALUES (103, 1, 1003);
INSERT INTO BOOK_ADOPTION VALUES (104, 1, 1004);
INSERT INTO BOOK_ADOPTION VALUES (105, 1, 1005);
-- Insert into TEXT
INSERT INTO TEXT VALUES (1001, 'Introduction to Java', 'Pearson', 'John Smith');
INSERT INTO TEXT VALUES (1002, 'Fundamentals of Electric Circuits', 'McGraw-Hill',
'Charles Alexander');
INSERT INTO TEXT VALUES (1003, 'Engineering Thermodynamics', 'Wiley', 'Yunus
Cengel');
INSERT INTO TEXT VALUES (1004, 'Data Structures and Algorithms in C++', 'Pearson',
'Adam Drozdek');
INSERT INTO TEXT VALUES (1005, 'Structural Analysis', 'Wiley', 'Russell Hibbeler');
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT course#, cname
FROM COURSE
WHERE course# IN (SELECT course#
FROM BOOK_ADOPTION
GROUP BY course#
HAVING COUNT(book_isbn) > 1);
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT DISTINCT dept
FROM COURSE
WHERE NOT EXISTS (SELECT *
FROM COURSE C
WHERE COURSE.dept = C.dept
AND C.course# NOT IN (SELECT BA.course#
FROM BOOK_ADOPTION BA
JOIN TEXT T ON BA.book_isbn = T.book_isbn
WHERE T.publisher = 'Pearson'));
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT regno
FROM ENROLL E
JOIN COURSE C ON E.course# = C.course#
GROUP BY regno
HAVING COUNT(DISTINCT C.dept) > 1;
-----------------------------------------------------------------------------------
----------------------------------------------------
SELECT regno
FROM STUDENT
WHERE regno NOT IN (SELECT regno FROM ENROLL);
-----------------------------------------------------------------------------------
-------------------------------------------------
SELECT dept
FROM COURSE
GROUP BY dept
HAVING COUNT(DISTINCT (SELECT T.publisher
FROM BOOK_ADOPTION BA
JOIN TEXT T ON BA.book_isbn = T.book_isbn
WHERE BA.course# = COURSE.course#)) = (SELECT COUNT(DISTINCT
publisher) FROM TEXT);
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT DISTINCT booktitle
FROM TEXT
WHERE book_isbn IN (SELECT book_isbn FROM BOOK_ADOPTION WHERE course# IN (SELECT
course# FROM ENROLL WHERE regno = 'S1'));
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT course#
FROM BOOK_ADOPTION
JOIN TEXT ON BOOK_ADOPTION.book_isbn = TEXT.book_isbn
GROUP BY course#
HAVING COUNT(CASE WHEN publisher = 'Pearson' THEN 1 END) >= 2;
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT regno
FROM (SELECT regno, COUNT(book_isbn) AS num_books
FROM ENROLL
GROUP BY regno
ORDER BY num_books DESC)
WHERE ROWNUM = 1;
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT publisher, COUNT(*) AS num_books
FROM TEXT
GROUP BY publisher;
-----------------------------------------------------------------------------------
-----------------------------------------------------
SELECT regno
FROM ENROLL E
WHERE NOT EXISTS (SELECT *
FROM BOOK_ADOPTION BA
WHERE BA.course# = E.course#
AND NOT EXISTS (SELECT *
FROM TEXT T
WHERE T.book_isbn = BA.book_isbn
AND T.book_isbn NOT IN (SELECT book_isbn
FROM ENROLL
WHERE regno =
E.regno)));
