assi3

CREATE TABLE emp_mst
(
    empno VARCHAR(20) primary key,
    ename VARCHAR(20) NOT NULL,
    city VARCHAR(20),
    dob DATE
);
CREATE TABLE branch
(
    b_code VARCHAR(20) primary key,
    bname VARCHAR(20) NOT NULL,
    br_city VARCHAR(20)
);
CREATE TABLE emp_detail
(
    b_code VARCHAR(20),
    empno VARCHAR(20),
    job VARCHAR(20) NOT NULL,
    basic_sal NUMBER  CHECK(basic_sal>0),
    doj DATE,
    manager_no VARCHAR(20),
    constraints fk_bcode foreign key (b_code) references branch(b_code) ON DELETE cascade,
    constraints fk_eno foreign key (empno) references emp_mst(empno) ON DELETE cascade
);
-- insert
INSERT INTO emp_mst VALUES('E001', 'Youtube','Surat',DATE '2000-05-11');
INSERT INTO emp_mst VALUES('E002', 'Google','Navsari',DATE '1999-11-12');
INSERT INTO emp_mst VALUES('E003', 'Yahoo','Baroda',DATE '2001-01-23');
INSERT INTO emp_mst VALUES('E004', 'Amazon','Bharuch',DATE '2003-07-27');
INSERT INTO emp_mst VALUES('E005', 'Flipkart','Katchh',DATE '2002-06-02');
INSERT INTO emp_mst VALUES('E006', 'Android','Amroli',DATE '2001-07 -13');
INSERT INTO emp_mst VALUES('E007', 'Ios','Ahemdabad',DATE '1992-01 -21');
INSERT INTO emp_mst VALUES('E008', 'Alexa','Jamnagar',DATE '2002-01 -29');
INSERT INTO emp_mst VALUES('E009', 'Snapdragon','Bardoli',DATE '2001-12-14');
INSERT INTO emp_mst VALUES('E010', 'Kapil','Patna',DATE '2001-09-16');

--insert
INSERT INTO branch VALUES('B001', 'Vadifaliya','Surat');
INSERT INTO branch VALUES('B002', 'Kotsafil','Bardoli');
INSERT INTO branch VALUES('B003', 'Bhagal','Amroli');
INSERT INTO branch VALUES('B004', 'Sagrampura','Katchh');
INSERT INTO branch VALUES('B005', 'Begampura','Ahemdabad');
INSERT INTO branch VALUES('B006', 'Gopipura','Patna');
INSERT INTO branch VALUES('B007', 'Mahidharpura','Bharuch');
--insert;
INSERT INTO emp_detail VALUES('B001','E002','Staff',15000,DATE '2015-02-21','M01');
INSERT INTO emp_detail VALUES('B002','E003','Clarck',13400,DATE '2016-04-12','M02');
INSERT INTO emp_detail VALUES('B003','E001','Staff',,14500date '2015-03-15','M03');
INSERT INTO emp_detail VALUES('B004','E004','Clarck',15000,DATE '2017-01-29','M04');
INSERT INTO emp_detail VALUES('B005','E005','HR',16900,DATE '2016-05-17','M05');
INSERT INTO emp_detail VALUES('B006','E006','Clarck',20000,DATE '2015-03-15','M06');
INSERT INTO emp_detail VALUES('B001','E007','Staff',14000,DATE '2016-02-23','M01');
INSERT INTO emp_detail VALUES('B003','E008','Staff',175000,DATE '2016-01-22','M03');
INSERT INTO emp_detail VALUES('B004','E009','Clark',14300,DATE '2016-04-21','M04');
INSERT INTO emp_detail VALUES('B005','E010','Staff',16000,DATE '2016-04-14','M05');


--Q1>
    CREATE OR REPLACE PROCEDURE  p1
    IS

    sum_sal NUMBER :=0;
    brh_name VARCHAR(20);
    E_no VARCHAR(20);
    E_name VARCHAR(20);
    d_o_j DATE;
    City VARCHAR(20);
    sal NUMBER;
        CURSOR  c1 IS
        SELECT  bname, empno,ename,doj,city,basic_sal INTO  brh_name,E_no,E_name,d_o_j,City,sal
        FROM(SELECT  bname ,"a".empno ,"b".ename ,doj AS ,"b".city ,basic_sal
                    FROM   (SELECT *
                                    FROM  emp_detail
                                    ORDER BY doj DESC) "a"
                    join  emp_mst "b"
                    ON "b".empno ="a".empno
                    join  branch "c"
                    ON "c".b_code="a".b_code
                    WHERE   "a".empno ="b".empno AND "c".b_code = "a".b_code
                    ORDER BY doj DESC
                                )
        WHERE rownum<=5;

    BEGIN
        OPEN  c1;
        LOOP
            FETCH c1 INTO  brh_name,E_no,E_name,d_o_j,City,sal;
            EXIT WHEN c1%notfound;
                DBMS_OUTPUT.put_line(brh_name ||' = ' ||E_no||' = ' ||E_name||' = ' ||d_o_j||' = ' ||City||' = ' ||sal);
                sum_sal:=sum_sal+sal;
        END LOOP;
        CLOSE c1;
                DBMS_OUTPUT.put_line(' Total Salary  =>  ' ||sum_sal);
    END  p1;
/
BEGIN
    p1();
END;
/
--Q2
    CREATE  OR REPLACE TRIGGER tg_wh
    before DELETE ON emp_mst
    FOR each ROW

    BEGIN
        IF  TO_CHAR(SYSDATE,'dy') ='sun' THEN
            raise_application_error(-20002,'It is sun day CHAMAN');
        END IF;
        IF  TO_CHAR(SYSDATE,'HH24')<10 OR  TO_CHAR(SYSDATE,'HH24')>=17 THEN
            raise_application_error(-20002,'It is  Out of Time  Dude');
        END IF;
    END tg_wh;

    --  delete from emp_mst where empno='E001';