assi5

CREATE TABLE book_mst
(
    book_no VARCHAR2(4) primary key,
    book_name VARCHAR2(25) NOT NULL,
    author VARCHAR2(20),
    publisher VARCHAR2(20),
    no_of_copy NUMBER(3),
    price NUMBER(6,2),
    constraints ck_no CHECK(no_of_copy>=0),
    constraints ck_pr CHECK(price>=0)
);
CREATE TABLE stud_mst
(
    lib_no VARCHAR2(4) primary key,
    stud_name VARCHAR2(25) NOT NULL,
    book_no VARCHAR2(4),
    trac_type  VARCHAR2(20),
    constraints  fk_bno foreign key (book_no) references book_mst(book_no),
    constraints  ck_trty CHECK (trac_type IN ('I','R'))
);


--insert;

INSERT INTO  book_mst VALUES('b01','RDBMS','A5','P1',30,150);
INSERT INTO  book_mst VALUES('b02','OOPS','A3','P2',27,70);
INSERT INTO  book_mst VALUES('b03','DS','A2','P3',54,100);
INSERT INTO  book_mst VALUES('b04','SE1','A1','P4',22,76);
INSERT INTO  book_mst VALUES('b05','STATE','A4','P5',24,250);
INSERT INTO  book_mst VALUES('b06','PRACTICLE','A6','P6',10,170);

--insert

INSERT INTO stud_mst VALUES('L01','Ayush','b01','I');
INSERT INTO stud_mst VALUES('L02','Aniket','b01','R');
INSERT INTO stud_mst VALUES('L03','Pritesh','b02','I');
INSERT INTO stud_mst VALUES('L04','Abrar','b03','I');
INSERT INTO stud_mst VALUES('L05','Faran','b02','R');
INSERT INTO stud_mst VALUES('L06','Amar','b03','R');
INSERT INTO stud_mst VALUES('L07','Akbar','b04','I');
INSERT INTO stud_mst VALUES('L08','Anthony','b04','R');
INSERT INTO stud_mst VALUES('L09','Yogi','b05','I');
INSERT INTO stud_mst VALUES('L10','Namo','b05','R');
INSERT INTO stud_mst VALUES('L11','Ayush','b02','I');
INSERT INTO stud_mst VALUES('L12','Aniket','b03','I');
INSERT INTO stud_mst VALUES('L13','Aniket','b04','R');


--Q1>
SELECT stud_name
FROM stud_mst
GROUP BY stud_name
HAVING COUNT(book_no)>2;


--Q2>
SELECT stud_name
FROM stud_mst
WHERE book_no = (   SELECT book_no
                                        FROM book_mst
                                        WHERE book_name='RDBMS'
    );

--Q3>

DELETE FROM stud_mst
WHERE book_no = (SELECT book_no
                                        FROM book_mst
                                        WHERE book_name='OOPS'
);

--Q4>
UPDATE stud_mst
SET trac_type='R'
WHERE  trac_type='I' AND book_no =(SELECT book_no
                                        FROM book_mst
                                        WHERE book_name='DS'
);
SELECT * FROM stud_mst;
--Q5>
SELECT book_name
FROM book_mst
WHERE  book_no NOT IN(
                                        SELECT book_no
                                        FROM stud_mst
                                        GROUP BY book_no
                                    );

--(A);
CREATE TABLE pur_book
(
    pno VARCHAR(4) primary key,
    boi69uokname VARCHAR(20),
    stock NUMBER
);
--insert
INSERT INTO pur_book VALUES('p01','Ruby',25);
INSERT INTO pur_book VALUES('p02','Bootstrap4',89);
INSERT INTO pur_book VALUES('p03','RDBMS',23);
INSERT INTO pur_book VALUES('p04','DS',12);
INSERT INTO pur_book VALUES('p05','OOPS',34);

--package heading
CREATE OR REPLACE PACKAGE pak_bsal AS
    FUNCTION check_bok
    RETURN VARCHAR;
    PROCEDURE pro_up ;
END pak_bsal;
/
--package body
CREATE OR REPLACE PACKAGE BODY pak_bsal
AS
    FUNCTION check_bok(book IN VARCHAR)
    RETURN VARCHAR
    IS
    ans VARCHAR(20);
    bname VARCHAR(20);
    CURSOR c1
    IS SELECT  book_name INTO bname
    FROM book_mst GROUP BY book_name;
    BEGIN
        OPEN c1;
            LOOP
                FETCH c1 INTO bname
                EXIT WHEN c1%notfound;
                    IF(book=bname)THEN
                        DBMS_OUTPUT.put_line(book || ' is  available');
                        ans:=book;
                    ELSE
                        DBMS_OUTPUT.put_line(book || ' is not available');
                        ans:='0';
                    END IF;
                RETURN ans;
    END check_bok;
    PROCEDURE pro_up(ans IN VARCHAR)
    BEGIN

    END pro_up;
END pak_bsal;
/

--
FUNCTION check_bok(book IN VARCHAR)
    RETURN VARCHAR
    IS
    ans VARCHAR(20);
    bname VARCHAR(20);
    CURSOR c1
    IS SELECT  bookname INTO bname
    FROM pur_book GROUP BY bookname;
    BEGIN
        OPEN c1;
            LOOP
                FETCH c1 INTO bname
                EXIT WHEN c1%notfound;
                    IF(book=bname)THEN
                        DBMS_OUTPUT.put_line(book || ' is  available');
                        ans:=book;
                    ELSE

                        ans:='0';
                    END IF;
                RETURN ans;
    END check_bok;




    PROCEDURE pro_up(ans IN VARCHAR) IS
    exp1 EXCEPTION;
    BEGIN
        IF(ans='0')
            RAISE exp1;
        END IF;

        EXCEPTION
            WHEN exp1 THEN
                raise_Application_error(-20008,'book is not avilable');
    END pro_up;