assi8

CREATE TABLE customer
(
    customerid VARCHAR(4) primary key,
    customername VARCHAR(20)
);
CREATE TABLE account
(
    accountid VARCHAR(4) primary key,
    customerid VARCHAR(4),
    accounttype VARCHAR(20),
    balance NUMBER,
    constraints fk_cid foreign key (customerid) references customer(customerid) ON DELETE cascade
);

--insert on customer;
INSERT INTO customer VALUES('c01','Aniket');
INSERT INTO customer VALUES('c02','Sahil');
INSERT INTO customer VALUES('c03','Dev');
INSERT INTO customer VALUES('c04','Brijal');
INSERT INTO customer VALUES('c05','Devanshi');
INSERT INTO customer VALUES('c06','Shivangi');
INSERT INTO customer VALUES('c07','Aditi');
INSERT INTO customer VALUES('c08','Aadesh');

--insert in account
INSERT INTO account VALUES('A01','c01','Currant',46000);
INSERT INTO account VALUES('A02','c02','Saving',20000);
INSERT INTO account VALUES('A03','c03','Saving',1000);
INSERT INTO account VALUES('A04','c04','Saving',12500);
INSERT INTO account VALUES('A05','c05','Currant',22000);
INSERT INTO account VALUES('A06','c06','Currant',11000);
INSERT INTO account VALUES('A07','c07','Saving',10200);
INSERT INTO account VALUES('A08','c08','Saving',21500);
INSERT INTO account VALUES('A09','c01','Saving',12000);
INSERT INTO account VALUES('A10','c02','Currant',2000);
INSERT INTO account VALUES('A11','c03','Currant',2750);
INSERT INTO account VALUES('A12','c07','Currant',12000);
INSERT INTO account VALUES('A13','c08','Currant',22000);



--Q1
DECLARE
ano VARCHAR(4):='&ano';
aid VARCHAR(4);
cid VARCHAR(4);
aty VARCHAR(20);
bal NUMBER;
T NUMBER:=0;
exp4 EXCEPTION;
CURSOR c1
IS
SELECT * INTO aid,cid,aty,bal
    FROM account
    WHERE accountid=ano;

BEGIN
        OPEN c1;
        LOOP
            FETCH c1 INTO aid,cid,aty,bal;
            EXIT WHEN c1%notfound;
            IF (bal-5000)<1000  THEN
                DBMS_OUTPUT.put_line(aid ||' => ' || cid||' => ' ||aty||' => ' ||bal);
                raise_application_error(-20008,'Account has minimum amount 1000 after debit  5000 rs so it will not debited');
                T:=1;
            ELSE
                DBMS_OUTPUT.put_line(' 5000 RS Debited From A\c no: '|| aid);
                UPDATE account
                SET balance=balance-5000
                WHERE accountid=aid;
                DBMS_OUTPUT.put_line(aid ||' => ' || cid||' => ' ||aty||' => ' ||bal);
                T:=1;
            END IF;
        END LOOP;
        CLOSE c1;
        IF T=0 THEN
            RAISE exp4;
        END IF;

        EXCEPTION
            WHEN exp4 THEN
                raise_application_error(-20001,'THe Account no is INVALID');
END;
/

--Q2

CREATE OR REPLACE TRIGGER trg_day
before UPDATE ON account
FOR each ROW
BEGIN
    IF  TO_CHAR(SYSDATE,'Dy') IN ('Sun','Sat') THEN
        raise_application_error(-20012,'This is Weekend Boss no entry');
    END IF;
END trg_day;
/

UPDATE account
SET balance=30000
WHERE accountid='A02';
--select to_char(sysdate,'Dy') from dual;