Solución repaso entera del año pasado

EJERCICIO 1

1) A los trabajadores que han estado asignados a edificios de oficinas, disminuirles su TARIFA EN 2 euros.

update trabajador

set tarifa=tarifa-2

 where id_t in (select   t.id_t

from trabajador t, asignacion a, edificio e

where e.ID_E=a.ID_E and

      t.ID_T=a.ID_T and

     UPPER(e.TIPO)='OFICINA')

2)  A cada supervisor que supervisa a más de un trabajador, actualizar su TARIFA   la mayor TARIFA de los que dependen de él..

UPDATE TRABAJADOR T0

SET T0.TARIFA = (SELECT MAX(TARIFA) FROM TRABAJADOR WHERE TRABAJADOR.ID_SUPV=T0.ID_T)

WHERE ID_T IN

( SELECT S.ID_T

 FROM TRABAJADOR T, TRABAJADOR  S

 WHERE T.ID_SUPV=S.ID_T

 GROUP BY S.ID_T

 having  count(t.ID_T) > 1)

       

3) Incrementar un 5% la tarifa de todos los trabajadores que tienen una tarifa por hora menor que la del promedio de todos los trabajadores que dependen del mismo supervisor que él.

UPDATE TRABAJADOR

SET TRABAJADOR.TARIFA=TRABAJADOR.TARIFA*1.05

WHERE TRABAJADOR.TARIFA < ( SELECT AVG(T2.TARIFA) FROM TRABAJADOR T2

                                       WHERE TRABAJADOR.ID_SUPV=T2.ID_SUPV)

4) Insertar un nuevo trabajador , su ID_T será 4000, su nombre será G.ALVAREZ,

   su supervisor  nadie, el oficio será el mismo de los supervisados por C.COULOMB y su tarifa la media de las tarifas de los empleados supervisados por C. COULOMB.

---una forma, usando subconsulta en la cláusula FROM

INSERT INTO TRABAJADOR

SELECT  DISTINCT 4000,'G_ALVAREZ',

 ( SELECT AVG(TARIFA) FROM TRABAJADOR

 WHERE ID_SUPV=

         (SELECT ID_T FROM TRABAJADOR WHERE

         NOMBRE LIKE '%COULOMB%') ),

 OFICIO,NULL FROM TRABAJADOR

 WHERE ID_SUPV =

 (SELECT ID_T FROM TRABAJADOR WHERE

 NOMBRE LIKE '%COULOMB%')

-----otra forma (Con GROUB BY)

INSERT INTO TRABAJADOR(ID_T,NOMBRE, TARIFA,OFICIO)

(SELECT 4000,'F.ALVAREZ',AVG(TARIFA),OFICIO FROM TRABAJADOR WHERE

ID_T IN

(SELECT ID_T FROM TRABAJADOR WHERE ID_SUPV =

(SELECT ID_T FROM TRABAJADOR WHERE NOMBRE = 'C.COULOMB')

)

GROUP BY OFICIO);

5) a) Crea una vista  que contenga los nombres de los electricistas, la dirección de los edificios a los que están asignados y cuántas veces han estado asignados a esos edificios.

CREATE OR REPLACE VIEW (TRABAJADOR,DIRECCION,NUM_DESTINOS) AS

SELECT T.NOMBRE,E.DIR,COUNT(A.ID_T)

FROM TRABAJADOR T, EDIFICIO E, ASIGNACION A

WHERE T.ID_T=A.ID_T AND E.ID_E=A.ID_E AND UPPER(T.OFICIO) LIKE 'ELECTRI%'

GROUP BY T.NOMBRE,E.DIR

b)Crea una secuencia de nombre SEQ_EXAMEN, con valor inicial 4500 y valor final 4600 e incremento de 1 en 1. Usa esa secuencia para la columna ID_T de la tabla TRABAJADOR y añade una fila en dicha tabla con los valores que quieras en las demás columnas.

c) Usa dicha secuencia para añadir una fila en la vista VISTA4 comentando lo que ocurre y por qué

 6) Se quiere borrar toda la información que se tiene del trabajador P.MASON. Indica las instrucciones que debes ejecutar para ello.

DELETE ASIGNACION WHERE ASIGNACION.ID_T =

(SELECT TRABAJADOR.ID_T FROM TRABAJADOR WHERE

 TRABAJADOR.NOMBRE ='P.MASON');

UPDATE TRABAJADOR

SET TRABAJADOR.ID_SUPV=NULL

WHERE ID_SUPV =

(SELECT TRABAJADOR.ID_T FROM TRABAJADOR WHERE

 TRABAJADOR.NOMBRE ='P.MASON');

DELETE  TRABAJADOR WHERE

 TRABAJADOR.NOMBRE ='P.MASON';

7) a) A  partir de tu situación actual, conectado como EJER1, enumera los pasos necesarios para que el usuario usuarioxx  pueda realizar las operaciones siguientes:

- Pueda ver los datos a través de la vista creada en el ejercicio 5 a)  y además pueda otorgar este derecho.

- Pueda actualizar las columnas TARIFA de TRABAJADOR y la columna NUM_DIAS de ASIGNACION.

- Pueda insertar filas nuevas en las tabla ASIGNACION.

SQL> GRANT SELECT ON  TO USUARIO00 WITH GRANT OPTION;

Concesión terminada correctamente.

SQL> GRANT UPDATE(TARIFA) ON TRABAJADOR TO USUARIO00;

Concesión terminada correctamente.

SQL> GRANT UPDATE(NUM_DIAS) ON ASIGNACION TO USUARIO00;

Concesión terminada correctamente.

SQL> GRANT INSERT ON ASIGNACION TO USUARIO00;

Concesión terminada correctamente.

b)      Entra como USUARIOXX y comprueba los privilegios adquiridos.

SQL> CONN USUARIO00/USUARIO00;

Conectado.

SQL> SELECT * FROM EJER1.VISTA5;

TRABAJADOR           DIRECCION       NUM_DESTINOS

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

C.COULOMB            456 MAPLE                  1

C.COULOMB            1415 BEACH                 1

M.FARADAY            123 ELM                    2

M.FARADAY            789 OAK                    1

SQL> UPDATE EJER1.TRABAJADOR

  2  SET TARIFA=TARIFA+1;

6 filas actualizadas.

SQL> UPDATE EJER1.ASIGNACION

  2  SET NUM_DIAS=NUM_DIAS+1;

19 filas actualizadas.

SQL> ED

Escrito archivo afiedt.buf

  1  INSERT INTO EJER1.ASIGNACION

  2* VALUES (1235,312,SYSDATE,3)

SQL> /

1 fila creada.

EJERCICIO 2

1)

a)      Modifica la tabla profesores añadiéndole una columna NUM_CURSOS NUMBER(3).

b)      Esta columna está destinada a guardar el nº de cursos impartido por cada profesor.  Escribe la sentencia

UPDATE que lleve a cabo esta misión.

rem a)

alter table profesores

add (num_cursos number(3));

rem b)

update profesores

            set num_cursos=(select count(cursos.dni_profesor)  from cursos

      where dni_profesor=profesores.dni);

2) Hay que dar de alta una fila en la tabla TIPOS_CURSO, el COD_CURSO es SGBDR012, el  título Oracle Forms 10g   y la duración de 10 horas menos que la máxima duración de los cursos que no sean ni electricidad ni soldadura.

insert into tipos_curso

select 'SGBDR012','Oracle Forms 10g',max(duracion-10) from tipos_curso

where upper(titulo) NOT IN ('SOLDADURA','ELECTRICIDAD')

3) Se ha asignado al profesor de apellido “Algete” que imparta el curso 534 , de “Introducción a Oracle”. Haz el correspondiente UPDATE  en la tabla CURSOS.

update cursos set

 cod_curso =(select cod_curso  from tipos_curso

                      where upper(titulo) like '%ORACLE%'),

                   DNI_PROFESOR=(select dni from profesores

                         where upper(apellido1)='ALGETE')

WHERE N_CURSO=534;

4) Tras repasar los datos nos hemos dado cuenta que se cometió un error al introducir el DNI del alumno Pedro García García, se tecleo 43734348 cuando en realidad su DNI  es 43734384. Escribe las instrucciones que creas necesarias para arreglar dicho error en las tablas ALUMNOS y ASISTIR.

-- METO LA FILA EN ALUMNOS

INSERT INTO ALUMNOS  SELECT 43734384,ALUMNOS.NOMBRE,ALUMNOS.APELLIDO1,ALUMNOS.APELLIDO2,  ALUMNOS.DIRECCION,  ALUMNOS.TELEFONO,ALUMNOS.EDAD,ALUMNOS.CIF

FROM ALUMNOS WHERE DNI=43734348

-- METO LAS FILAS EN ASISTIR

insert into asistir select 43734384, asistir.N_CURSO, asistir.NOTA from asistir WHERE dni=43734348

 -- borro en asistir

delete from asistir WHERE dni=43734348

--borro en alumnos

delete from alumnos WHERE dni=43734348

5) Crea la vista ASISTENCIA_CURSOS  que tendrá todas las columnas de la tabla ASISTIR, todas las de la tabla TIPOS_CURSO, y las columnas FECHA_INICIO, FECHA_FIN y   DNI_PROFESOR. de la tabla CURSOS. Joins por columnas comunes.

CREATE OR REPLACE VIEW ASISTENCIA_CURSOS

(DNI, N_CURSO, NOTA, COD_CURSO, TITULO,

 DURACION, FECHA_INICIO, FECHA_FIN, DNI_PROFESOR)

AS

select asistir.*,tipos_curso.*,cursos.fecha_inicio,cursos.fecha_fin,cursos.dni_profesor

from asistir,cursos,tipos_curso

where asistir.n_curso=cursos.n_curso and

cursos.cod_curso=tipos_curso.cod_curso;

Intenta hacer un UPDATE en ASISTENCIA-CURSOS  actualizando N_CURSO  a 522  en aquellas filas en las que N_CURSO es igual a  521. Comenta lo que ocurre y por qué.

6) Crea una secuencia denominada SEQ_EXAMEN  que comienza por 600, incrementando de 1 en 1, y con valor máximo 700. Utiliza esa secuencia con la columna  N_CURSOS de la tabla CURSOS para insertar 2 filas con los valores que quieras para las demás columnas.

CREATE SEQUENCE SEQ_EXAMEN

START WITH 10020

MAXVALUE 11000

INCREMENT BY 1

NOCYCLE

NOCACHE;

INSERT INTO ESTUDIANTES VALUES (SEQ_EXAMEN.NEXTVAL,'PEPE','GÓMEZ','Historia',0);

INSERT INTO ESTUDIANTES VALUES (SEQ_EXAMEN.NEXTVAL,'ana','pérez','Económicas',0);

EJERCICIO 3

1)      Eliminar los módulos que no tengan estudiantes registrados.

 delete from modulos

where (departamento,curso) NOT IN

(SELECT departamento,curso FROM estudiantes_registrados)

2)      Actualizar la columna NUM_ESTUDIANTES de la tabla MÓDULOS. Para ello debe usarse la tabla ESTUDIANTES_REGISTRADOS  donde tenemos la información de los módulos en los que está matriculado cada estudiante.

update modulos

set NUM_ESTUDIANTES=

(SELECT COUNT(*) FROM ESTUDIANTES_REGISTRADOS R

WHERE MODULOS.CURSO=R.CURSO AND MODULOS.DEPARTAMENTO=R.DEPARTAMENTO)

3)Actualizar la columna CREDITOS_ACTUALES de la tabla ESTUDIANTES. Para ello deben usarse la tabla ESTUDIANTES_REGISTRADOS  donde tenemos la información de los módulos en los que está matriculado cada estudiante y la tabla MODULOS donde tenemos cuantos créditos le corresponden a cada módulo.

UPDATE estudiantes

SET creditos_actuales=

(SELECT SUM(M.num_creditos) FROM modulos M,estudiantes_registrados E

WHERE M.departamento=E.departamento AND M.curso=E.curso

GROUP BY e.estudiante_id

HAVING estudiantes.id=e.estudiante_id)