assi Q1

-RDBMS
    --      Book (Bookld, BookName, Author, Publisher)
    --      Student (Studentld, StudentName)
    --      Booklssue (Booklssueld, Bookld, Studentld, IssueDate, ReturnDate, BookStatus)
    --      Write a PL/SQL code block:
--          1. Accept student name and display all books issued by student.
--          2. Display all the students' name who don't return book on return date.
--          3. Display all the students who don't issue book since last 3 months.
--          4. Display all the students who issue 3 books.

--create
--      Book (Bookld, BookName, Author, Publisher)
CREATE TABLE book
(
    bookid   NUMBER primary key,
    bookname VARCHAR(20),
    author VARCHAR(20),
    publisher VARCHAR(20)
);
--      Student (Studentld, StudentName)
CREATE  TABLE student
(
    studentid  NUMBER primary key,
    studentname VARCHAR(20)
);
    --      Booklssue (Booklssueld, Bookld, Studentld, IssueDate, ReturnDate, BookStatus)
    CREATE TABLE bookissue
    (
        bookissueid NUMBER,
        bookid NUMBER ,
        studentid NUMBER,
        issuedate DATE,
        returndate DATE,
        bookstatus VARCHAR(20),
        constraints  "fk_bookid"
        foreign key (bookid)
        references book(bookid)
        ON DELETE  cascade,
        constraints "fk_studentid"
        foreign key(studentid)
        references student(studentid)
        ON DELETE cascade
        );

    -- Insert
---     Book (Bookld, BookName, Author, Publisher)
INSERT INTO book VALUES(101,'c++','l.k.advani','surya');
INSERT INTO book VALUES(102,'bootstrap4','h.r.mehta','nb');
INSERT INTO book VALUES(103,'php','s.rmanujam','aware');
INSERT INTO book VALUES(104,'oracle','tiger','shroff');
INSERT INTO book VALUES(105,'ruby','kapil','sharma');

--      Student (Studentld, StudentName)
INSERT INTO student VALUES(1,'Aniket');
INSERT INTO student VALUES(2,'Krunal');
INSERT INTO student VALUES(3,'Pritesh');
INSERT INTO student VALUES(4,'Jay');
INSERT INTO student VALUES(5,'Nikul');
INSERT INTO student VALUES(6,'Aadesh');
INSERT INTO student VALUES(7,'Aditi');
INSERT INTO student VALUES(8,'shradha');
INSERT INTO student VALUES(9,'Manav');
INSERT INTO student VALUES(10,'Darshan');
INSERT INTO student VALUES(11,'Dhruv');
INSERT INTO student VALUES(12,'Anikt');
INSERT INTO student VALUES(13,'Ajay');
INSERT INTO student VALUES(14,'Bhuvan');
INSERT INTO student VALUES(15,'Aashish');

--      Booklssue (Booklssueld, Bookld, Studentld, IssueDate, ReturnDate, BookStatus)
INSERT INTO bookissue VALUES(1001,101,1,DATE '2019-09-22',DATE  '2019-10-02','recieved');
INSERT INTO bookissue VALUES(1002,103,2, DATE '2020-02-22',DATE '2020-03-01','not_recieved');
INSERT INTO bookissue VALUES(1003,102,3, DATE '2020-02-21',DATE '2020-02-29','recieved');
INSERT INTO bookissue VALUES(1004,104,1, DATE '2020-03-02',DATE '2020-03-29','recieved');
INSERT INTO bookissue VALUES(1005,105,4, DATE '2020-03-10',DATE '2020-03-19','not_recieved');
INSERT INTO bookissue VALUES(1006,105,1, DATE '2020-04-19',DATE '2020-04-29','not_recieved');
INSERT INTO bookissue VALUES( 1007,103,13, DATE '2020-01-19',DATE '2020-03-19','recieved');
INSERT INTO bookissue VALUES(1008,101,13, DATE '2020-03-11',DATE '2020-04-21','recieved');
INSERT INTO bookissue VALUES( 1009,101,7, DATE '2020-02-11',DATE '2020-02-21','recieved');
INSERT INTO bookissue VALUES(1010,105,7, DATE '2020-04-19',DATE '2020-04-21','not_recieved');
INSERT INTO bookissue VALUES(1011,103,14, DATE '2020-04-19',DATE '2020-05-02','recieved');
INSERT INTO bookissue VALUES(1012,104,14, DATE '2020-05-21',DATE '2020-05-25','not_recieved');
INSERT INTO bookissue VALUES(1013,101,15, DATE '2020-06-01',DATE '2020-06-10','recieved');
INSERT INTO bookissue VALUES(1014,103,5, DATE '2020-05-16',DATE '2020-06-01','recieved');
INSERT INTO bookissue VALUES(1015,102,7, DATE '2020-04-22',DATE '2020-04-13','recieved');

//Q1
--1. Accept student name and display all books issued by student
DECLARE
name VARCHAR(20):='&name';
sname VARCHAR(20);
bname VARCHAR(20);
CURSOR c1 IS SELECT studentname,bookname
    FROM book  ,student
    WHERE  bookname IN (SELECT bookname FROM book WHERE bookid IN (SELECT bookid FROM bookissue WHERE studentid IN(SELECT studentid FROM student WHERE studentname =name)))
    AND studentname=name;
BEGIN

    OPEN c1;
    LOOP
    FETCH c1 INTO sname,bname;
        EXIT WHEN c1%notfound;
    DBMS_OUTPUT.put_line(sname||' === >'||bname);
    END LOOP;
    CLOSE c1;
END;
/
--Q2
--2. Display all the students' name who don't return book on return date.
DECLARE
sname VARCHAR(20);
CURSOR c2 IS
SELECT studentname FROM student WHERE studentid IN(SELECT studentid FROM bookissue WHERE bookstatus='not_recieved');
BEGIN

    OPEN c2;
    LOOP
    FETCH c2 INTO sname;
    EXIT WHEN c2%notfound;
        DBMS_OUTPUT.put_line(  ' * '||sname );
    END LOOP;
    CLOSE c2;
END;
/

----Q3
--3. Display all the students who don't issue book since last 3 months.
DECLARE
sname VARCHAR(20);
CURSOR c3 IS
SELECT studentname
FROM student
 WHERE studentid IN (SELECT studentid
                                    FROM bookissue
                                    WHERE  (TO_CHAR(SYSDATE,'mm')-TO_CHAR(issuedate,'mm'))>3    )
ORDER BY studentname;
 BEGIN
     OPEN c3;
     LOOP
     FETCH c3 INTO sname;
     EXIT WHEN c3%notfound;
        DBMS_OUTPUT.put_line('* ' || sname);
     END LOOP;
    CLOSE c3;
END;
--q4
--          4. Display all the students who issue 3 books.
DECLARE
sname VARCHAR(20);
CURSOR c4 IS
SELECT StudentName AS"3 books issued" FROM student WHERE studentid IN(SELECT studentid FROM bookissue   GROUP BY studentid HAVING COUNT(bookid)=3 ) ;
BEGIN
        OPEN c4;
        LOOP
            FETCH c4 INTO sname;
            EXIT WHEN c4%notfound;
            DBMS_OUTPUT.put_line('=> '|| sname);
        END LOOP;
        CLOSE c4;
END;
/




--trigger

CREATE OR REPLACE  TRIGGER t1
before INSERT OR UPDATE OR DELETE ON bookissue
FOR each ROW
DECLARE
oprat VARCHAR(10);
BEGIN
IF TO_CHAR(SYSDATE,'d')=7 THEN

                IF inserting THEN
                    oprat:='Insert';
                ELSIF deleting THEN
                    oprat:='Delete';
                ELSIF updating THEN
                    oprat:='Update';
                END IF;
                raise_application_error(-20002,oprat ||'is only possible in working hour');


    END IF;
    IF TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE,'HH24')>16 THEN

                IF inserting THEN
                    oprat:='Insert';
                ELSIF deleting THEN
                    oprat:='Delete';
                ELSIF updating THEN
                    oprat:='Update';
                END IF;
                raise_application_error(-20001,oprat ||'is only possible in working hour');

    END IF;
END t1;
SELECT TO_CHAR(SYSDATE,'HH24') FROM dual;
DELETE FROM bookissue WHERE bookissueid=1015;