· 8 years ago · Jul 04, 2018, 08:10 AM
11. Create the following tables and insert data
22. Create 2 sequences for DEPTNO and EMPNO.
3DEPT_<EMPNO>
4DEPTNO(PK) DNAME(NN) LOC(NN)
510 RESEARCH HDC
620 SALES BDC
7
830 COMPUTERS CDC
9
10EMP_<EMPNO>
11EMPNO(PK) ENAME SAL(CHECK SAL>5000) COMM DEPTNO(FK)
12101 ANTHONY 6000 10
13102 JOHN 7600 1000 10
14103 ANDERSON 4500 20
15104 KEVIN 2300 500 30
16ANSWER 1 & 2)
17 create table DEPT_20017417(deptno number(4) primary key,dname not null varchar2(30),loc not null varchar2(10));
18 create table EMP_20017417(empno number(10) primary key,ename varchar2(20) , sal decimal(10,2) , comm decimal(10,2) , deptno number(10), foreign key(deptno) references dept(deptno), check(sal>5000));
19 create sequence deptno_id start with 10 minvalue 10 increment by 10 ;
20 insert into DEPT_20017417 values(deptno_id.nextval,'&dname','&loc');
21 create sequence emp_id start with 100 minvalue 100 increment by 1;
22 create sequence emp_id start with 103 minvalue 103 increment by 1;
23 insert into EMP_20017417 values(emp_id.nextval,'&ename',&sal,&comm,&deptno);
243. Update the ENAME of employees working in HDC location to lower case
25ANSWER 3)
26 update (select ename,loc from EMP_20017417,DEPT_20017417 where DEPT_20017417.deptno = EMP_20017417.deptno and loc = 'HDC') set ename = lower(ename);
274. Create a view MY_VIEW that has EMPNO,ENAME, SAL,DNAME,LOC.
28ANSWER 4)
29 create view MY_VIEW as select empno,ename,sal,dname,loc from EMP_20017417 natural join DEPT_20017417;
305. Create synonym HR_INFO for the above view.
31ANSWER 5
32 create synonym HR_INFO for MY_VIEW;
33
346. Create a Package EMP_PK that has 4 procedures. INSERT_TR that takes 3 arguments EMPNO,ENAME,SAL and insert into the EMP_<EMPNO> table if empno does no exists else throw exception. UPDATE_TR that takes 3 arguments EMPNO,ENAME,SAL, if empno exists then update the ename and sal else throw exception. DELETE_TR that will take EMPNO as an argument and delete the record else throw exception. DISPLAY_REPORT, this procedure will display records as follows:
35RESEARCH
36ANTHONY
37JOHN
38
39SALES
40ANDERSON
41
42COMPUTERS
43KEVIN
44
45In the package create a local function CHK_BOOLEAN that will take nsal an argument and check if MAXSAL is greater than nsal then return true else return false. Call this function in INSERT_TR and UPDATE_TR
46
47ANSWER 6.
48
49create or replace package EMP_PK is
50procedure INSERT_TR(v_empno EMP_20017417.empno%type , v_ename EMP_20017417.ename%type, v_sal EMP_20017417.sal%type);
51procedure UPDATE_TR(v_empno EMP_20017417.empno%type , v_ename EMP_20017417.ename%type, v_sal EMP_20017417.sal%type);
52procedure DELETE_TR(v_empno EMP_20017417.empno%type);
53procedure DISPLAY_REPORT;
54function chk_boolean(nsal EMP_20017417.sal%type) return boolean;
55end;
56
57create or replace package body EMP_PK is
58v_count binary_integer;
59procedure INSERT_TR(v_empno EMP_20017417.empno%type , v_ename EMP_20017417.ename%type, v_sal EMP_20017417.sal%type) is
60begin
61 select count(*) into v_count from EMP_20017417 where empno = v_empno;
62 if v_count = 0 then
63 if chk_boolean(v_sal) then
64 insert into EMP_20017417(empno,ename,sal) values(v_empno,v_ename,v_sal);
65 else
66 raise_application_error(-20001, 'Cannot insert salary more than maximum salary');
67 end if;
68 else
69 raise_application_error(-20002, 'EMPNO already exists');
70 end if;
71end;
72procedure UPDATE_TR(v_empno EMP_20017417.empno%type , v_ename EMP_20017417.ename%type, v_sal EMP_20017417.sal%type) is
73begin
74 select count(*) into v_count from EMP_20017417 where empno = v_empno;
75 if v_count = 0 then
76 raise_application_error(-20001, 'EMPNO already exists');
77 else
78 if chk_boolean(v_sal) then
79 update EMP_20017417 set ename = v_ename, sal = v_sal where empno = v_empno;
80 else
81 raise_application_error(-20002, 'Cannot update salary more than the maximum salary');
82 end if;
83 end if;
84end;
85procedure DELETE_TR(v_empno EMP_20017417.empno%type) is
86begin
87delete from EMP_20017417 where empno = v_empno;
88if SQL%ROWCOUNT = 0 then
89 raise_application_error(-20001, 'EMPLOYEE does not exist');
90end if;
91end;
92procedure DISPLAY_REPORT is
93cursor v_c1 is select dname from DEPT_20017417;
94cursor v_c2(v_deptno DEPT_20017417.deptno%type) is select ename from EMP_20017417 where deptno = v_deptno;
95v_dname DEPT_20017417.dname%type;
96v_deptno DEPT_20017417.deptno%type;
97v_ename EMP_20017417.ename%type;
98begin
99 for v_dname in v_c1 loop
100 dbms_output.put_line(v_dname.dname);
101 dbms_output.put_line('-------------------------------------------------------------------------------');
102 select deptno into v_deptno from DEPT_20017417 where dname = v_dname.dname;
103 for v_ename in v_c2(v_deptno) loop
104 dbms_output.put_line(v_ename.ename);
105 end loop;
106 end loop;
107end;
108function chk_boolean(nsal EMP_20017417.sal%type) return boolean is
109v_bool boolean;
110maxsal EMP_20017417.sal%type;
111begin
112 select max(sal) into maxsal from EMP_20017417;
113 if nsal < maxsal then
114 return true;
115 else
116 return false;
117 end if;
118end;
119end;