M2 - Bases de dades / Exercicis conceptes fundamentals UF3: PL-SQL

Esquema HR

1.- Crea una taula PRESSUPOST (CREATE TABLE) que contingui els següent camps: Nom_Department i Total_Salaris.

CREATE TABLE PRESSUPOST(
Nom_Department VARCHAR2(30) PRIMARY KEY, 
Total_Salaris NUMBER(5));

a) Crea la funció Total_Salari_Dept que a partir d'un número de departament ens retorni el total de salaris pressupostats per a aquell departament, tenint en compte els següents augments:

3%: AD_ASST, SA_REP, SH_CLERK, PU_CLERK, ST_CLERK
5%: AC_ACCOUNT, FI_ACCOUNT, HR_REP, PR_REP, IT_PROG, MK_REP
7%: MK_MAN, AC_MGR, FI_MGR, PU_MAN, SA_MAN, ST_MANAGER
9%: AD_PRES, AD_VP 

b) Fes un bloc PL/SQL per provar la funció. Mostra el total dels salaris revisats dels departaments 10, 50 i 75.

Total Salari Departament 10: 4532
Total Salari Departament 50: 123600
Total Salari Departament 75: 0

c) Crea la funció Total_Salari_Dept2 per tal que en cas de que no existeixi el número de departament utilitzi el procediment RAISE_APPLICATION_ERROR per generar una excepció amb el codi: -20215 i missatge: 'No existeix aquest departament!!'.

b) Fes un bloc PL/SQL per provar el funcionament correcta de l'excepció de la funció. c) En un bloc PL/SQL crea un cursor que recuperi tots els departaments que tenen empleats (manager_id not null) ordenats per departament i utilitza la funció de l'apartat a) per omplir la taula PRESSUPOST convenientment.

2.- Fes un bloc PL/SQL que crei una taula indexada per job_title i crea un cursor que recuperi tots els empleats ordenats per departament i recorre'l per omplir la taula amb la següent informació: número d'empleats que tenen aquest ofici i el número de departaments que tenen aquest ofici. Mostra el resultat.

3.- Utilitzant dos cursors, un que recuperi els departaments i un segon parametritzat que a partir d'un department_id recupera els empleats, fes el següent informe:

                     - EMPLEATS PER DEPARTAMENT -

Departament: <nom del departament>

Cap: <nom i cognom del cap del departament>

Empleats 

<nom i cognom dels empleats>

- - - - - - - - - - - - - - - - - - - - 

Departament: <nom del departament>

Cap: <nom i cognom del cap del departament>

Empleats 

<nom i cognom dels empleats>

Esquema SCOTT ampliat