DBMS Prac5

--1. Book(isbn_no pk ,bname ,name_author,Pub_name)
--2. Department (deptno pk,dname )
--3. Student (uidno pk, fname , dept_no fk)
--4. Faculty(empno pk ,fname , dept_no fk,designation)
--5. Student_book(isbn_no fk, uidno fk, date_of_issue,date_return) pk(isbn, uidno, data of issue)
--6. Faculty_book(isbn_no fk, faculty_id fk, date_of_issue,date_return) pk(isbn, fa id, d o i)

CREATE TABLE Book(isbn_no INT PRIMARY KEY, bname VARCHAR(20), name_author VARCHAR(20), pub_name VARCHAR(20));
CREATE TABLE Department(deptno INT PRIMARY KEY, dname VARCHAR(20));
CREATE TABLE Student (uidno INT PRIMARY KEY, fname VARCHAR(20), dept_no INT, CONSTRAINT stu_fk FOREIGN KEY(dept_no) REFERENCES Department)
CREATE TABLE Faculty (empno INT PRIMARY KEY, fname VARCHAR(20), dept_no INT, designation VARCHAR(20),CONSTRAINT fac_fk FOREIGN KEY(dept_no) REFERENCES Department)
CREATE TABLE Student_book(isbn_no INT, uidno INT, date_of_issue DATE,date_return DATE,
    CONSTRAINT isbn_fk FOREIGN KEY(isbn_no) REFERENCES Book,
    CONSTRAINT uid_fk FOREIGN KEY(uidno) REFERENCES Student,
    CONSTRAINT sb_pk PRIMARY KEY(isbn_no, uidno, date_of_issue))

CREATE TABLE aculty_book(isbn_no INT, faculty_id INT, date_of_issue DATE,date_return DATE,
    CONSTRAINT isbn2_fk FOREIGN KEY(isbn_no) REFERENCES Book,
    CONSTRAINT fa_fk FOREIGN KEY(faculty_id) REFERENCES Faculty,
    CONSTRAINT fb_pk PRIMARY KEY(isbn_no, faculty_id, date_of_issue))

ALTER TABLE Book DROP COLUMN name_author2

-- QUESTIONS
-- 1) Details of students who have taken books from the course IT
SELECT * FROM student
    WHERE uidno IN (SELECT uidno FROM Student_book) AND dept_no = (SELECT deptno FROM Department d2 WHERE dname = 'IT')

-- 2) Details of the books published by ALPHA publisher
SELECT * FROM Book b WHERE pub_name = 'pub1'

-- 3) Details of authors who have published maximum no of books
SELECT name_author FROM Book b GROUP BY name_author
    HAVING COUNT(*) = (SELECT MAX(y.COUNT) FROM (SELECT COUNT(*) 'count' FROM Book GROUP BY name_author) y)

-- 4) Details of author who have published minimum no of books
SELECT name_author FROM Book b GROUP BY name_author
    HAVING COUNT(*) = (SELECT MIN(y.COUNT) FROM (SELECT COUNT(*) 'count' FROM Book GROUP BY name_author) y)


-- 5) Details of students who have taken the book ‘DBMS’
SELECT * FROM Student s WHERE s.uidno =
    (SELECT uidno FROM Student_book sb WHERE isbn_no = (SELECT isbn_no FROM Book b2 WHERE bname = 'book1'))


-- 6) Details of authors who have written more than 1 books
SELECT name_author FROM Book b GROUP BY name_author
    HAVING COUNT(*) > 1


-- 7) Details of students who have taken at least one book
SELECT * FROM Student s WHERE uidno IN (SELECT uidno FROM Student_book sb)

-- 8) Find the number of students studying in each class
SELECT COUNT(*), dept_no FROM Student s GROUP BY dept_no

-- 9) Display the details of the department with maximum number of students
SELECT * FROM department
    WHERE deptno IN (SELECT dept_no FROM student GROUP BY dept_no HAVING COUNT(*) = (SELECT MAX(y.cnt) FROM
        (SELECT COUNT(*) 'cnt' FROM Student s2 GROUP BY dept_no ) y))

-- 10)Details of students who have not yet returned the book.
SELECT * FROM Student s WHERE uidno = (SELECT uidno FROM Student_book sb WHERE date_return IS NULL)

-- 11)Details of the books which have a co author
SELECT * FROM Book b WHERE name_author2 IS NOT NULL

-- 12)Details of the books which have been issued more than 1 times in the year 2020. Try with union
CREATE VIEW stu_book_cnt AS SELECT COUNT(*) 'count', isbn_no FROM Student_book sb WHERE YEAR(date_of_issue) = 2020 GROUP BY isbn_no
CREATE VIEW fac_book_cnt AS SELECT COUNT(*) 'count', isbn_no FROM faculty_book fb WHERE YEAR(date_of_issue) = 2020 GROUP BY isbn_no

SELECT * FROM Book b WHERE isbn_no IN
    (SELECT s.isbn_no FROM stu_book_cnt s, fac_book_cnt f WHERE s.COUNT + f.COUNT > 1 AND s.isbn_no = f.isbn_no)