assi4

-----------------------------------------------------------------------
CREATE TABLE doctor
 (
    D_id VARCHAR(20) primary key,
    D_name VARCHAR(20) NOT NULL,
    D_city VARCHAR(20)
) ;
CREATE TABLE patient
 (
    P_id VARCHAR(20) primary key,
    P_name VARCHAR(20),
    P_city VARCHAR(20)
) ;
CREATE TABLE bill
 (
    B_id VARCHAR(20) primary key,
    P_id VARCHAR(20),
    D_id VARCHAR(20),
    bill_date DATE,
    bill_amt NUMBER CHECK(bill_amt>0),
    constraints fk_P_id foreign key  (P_id) references  patient(P_id) ON DELETE cascade,
    constraints fk_D_id foreign key  (D_id) references  doctor(D_id) ON DELETE cascade
) ;

INSERT INTO doctor VALUES('D01','Carryminati','Surat');
INSERT INTO doctor VALUES('D02','Bhuvan','Navsari');
INSERT INTO doctor VALUES('D03','Aashish','Bharuch');
INSERT INTO doctor VALUES('D04','Harsh','Ahemdabad');
INSERT INTO doctor VALUES('D05','billgates','Porbandar');
INSERT INTO doctor VALUES('D06','Titu','Jamnagar');



-- insert
INSERT INTO patient   VALUES('a','Aniket','Surat');
INSERT INTO patient   VALUES('b','Sahil','Navsari');
INSERT INTO patient   VALUES('c','Abbas','Aanand');
INSERT INTO patient   VALUES('d','Tiger','Katchh');
INSERT INTO patient   VALUES('e','Faran','Jammu');
INSERT INTO patient   VALUES('f','Raju','Girnar');
INSERT INTO patient   VALUES('g','Hardik','Patya');
INSERT INTO patient   VALUES('h','Dhoni','Jamnagar');
INSERT INTO patient   VALUES('i','Baburav','Baroda');
INSERT INTO patient   VALUES('j','Kachrasheth','Vadodara');

--select s1.currval from dual;
--TRUNCATE TABLE PATIENT;
--insert  bill
INSERT INTO bill VALUES( 'B01','PO46','D01',DATE '2019-06-15' ,25000);
INSERT INTO bill VALUES( 'B02','PO47','D02',DATE '2019-04-21' ,12000);
INSERT INTO bill VALUES( 'B03','PO48','D03',DATE '2019-08-02' ,14000);
INSERT INTO bill VALUES( 'B04','PO49','D04',DATE '2019-05-09' ,19500);
INSERT INTO bill VALUES( 'B05','PO50','D05',DATE '2019-07-23' ,21500);
INSERT INTO bill VALUES( 'B06','PO51','D06',DATE '2019-10-08' ,30000);
INSERT INTO bill VALUES( 'B07','PO46','D01',DATE  '2019-10-21' ,19200);
INSERT INTO bill VALUES( 'B08','PO55','D02',DATE '2019-12-12' ,19000);
INSERT INTO bill VALUES( 'B09','PO53','D03',DATE '2019-02-19' ,28400);
INSERT INTO bill VALUES( 'B10','PO52','D04',DATE '2019-04-21' ,13900);
INSERT INTO bill VALUES( 'B11','PO51','D06',DATE '2019-06-23' ,12300);

--1>
SELECT  *
FROM bill "b"
join doctor "d"
ON  "d".D_id="b".D_id
join patient "p"
ON  "p".P_id="b".P_id
WHERE   "d".D_city="p".P_city;

--2>
SELECT *
FROM patient
WHERE  P_id IN(
    SELECT P_id
    FROM bill
    WHERE  TO_CHAR(bill_date,'mon-dd')='jun-15'
) ;

--3>
SELECT  D_name AS "Doctor name",COUNT(p_id) AS " NO of Patients"
FROM bill "b"
join  doctor "d"
ON "d".D_id="b".D_id
GROUP BY D_name
ORDER BY D_name;
--4>
SELECT P_name AS "Patient name"
FROM patient
WHERE P_id IN( SELECT P_id
                                FROM bill
                                WHERE bill_amt BETWEEN 25000 AND 30000);

--5>
SELECT  P_name AS "Patient's Name",D_name AS "Doctor's Name",bill_amt AS "Bill's amount"
FROM bill "b"
join  doctor "d"
ON "d".D_id="b".D_id
join  patient "p"
ON "p".P_id="b".P_id
ORDER BY P_name;
(A)
CREATE OR REPLACE PROCEDURE pro_bdp
(no1 IN VARCHAR)
IS
pn VARCHAR(20);
pc VARCHAR(20);
dn VARCHAR(20);
amt VARCHAR(20);
t NUMBER;
CURSOR c1 IS
        SELECT  P_name,P_city,D_name,bill_amt INTO  pn,pc,dn,amt
        FROM bill "b"
        join  doctor "d"
        ON "d".D_id="b".D_id
        join  patient "p"
        ON "p".P_id="b".P_id
        WHERE B_id=no1;
    Exp1 EXCEPTION ;

BEGIN
        t:=0;
        OPEN c1;
        DBMS_OUTPUT.put_line('Patients name ||  Patients city   ||  Doctors name    ||  AMOUNT of bill');
        LOOP
            FETCH c1 INTO  pn,pc,dn,amt;
            EXIT WHEN c1%notfound;

                DBMS_OUTPUT.put_line( pn||' ||  '||pc||'     || '||dn||'    ||  '||amt);
                t:=1;
        END LOOP;
        IF  t=0 THEN
            RAISE  Exp1;
        END IF;
        EXCEPTION
            WHEN  Exp1 THEN
                raise_application_error(-20002,'Bid  is invalid');
        CLOSE c1;
END pro_bdp;
/

--run pro_bdp
exec pro_bdp('B02');




(b)
--sequence
    CREATE   SEQUENCE  s1
    START WITH 1
    increment BY 1;

--trigger

CREATE  OR REPLACE TRIGGER seq
after INSERT ON  patient
BEGIN
            IF  inserting THEN
                        UPDATE  patient  SET P_id='PO'||s1.NEXTVAL;
                        DBMS_OUTPUT.put_line('Insertion is complete');

            END IF;

END  seq;
/
    DROP TRIGGER seq;
    DROP  sequence s1;
--INSERT into patient (P_name,P_city) values('Aniket','Surat');
--nsert in patient
SELECT s1.CURRVAL FROM dual;
    SELECT * FROM patient;