USAR LENGUAJE TRANSACCIONAL SOBRE LA BASE DE DATOS

1. Construya los siguientes procedimientos Almacenados para inserción de registros:

• En MySQL; para las tablas Profesor, Curso, Estudiante, Estudiantexcurso.

Create procedure colegio() Select * from profesor, curso, estudiante, estudiantexcurso;

call colegio();

• En ORACLE; para las tablas Cliente, Articulo, Pedido.

Create or replace PROCEDURE negocios(unos out SYS_REFCURSOR)

as

BEGIN

OPEN unos FOR

SELECT *

FROM cliente, articulo, pedido;

END negocios;

• En el Motor de su preferencia (ORACLE O MySQL), para las tablas, Compañía, TiposAutomotores, Automotores, Aseguramientos, Incidentes.

Create procedure seguros() Select * from compañia, tiposautomotores, automotores, aseguramientos, incidentes;

call seguros();

2. En Oracle construya los procedimientos almacenados para realizar los siguientes procesos:

• Muestre los salarios de los profesores ordenados por categoría.

create or replace PROCEDURE salarioprofesor(uno out SYS_REFCURSOR)

as

BEGIN

OPEN uno FOR

SELECT sal_prof

FROM profesor ORDER BY cate_prof;

END salarioprofesor;

• Muestre los cursos cuyo valor sea mayor a $500.000.

Create or replace PROCEDURE valorcurso(uno out SYS_REFCURSOR)

as

BEGIN

OPEN uno FOR

SELECT nom_curs

FROM curso WHERE valor_cur>='500000';

END valorcurso;

• Visualizar el nombre, apellido y dirección de todos aquellos clientes que hayan realizado un pedido el día 25 /02/2012.

Create or replace PROCEDURE pedidos02252012(uno out SYS_REFCURSOR)

as

BEGIN

OPEN uno FOR

SELECT cliente.nom_cli, cliente.ape_cli, cliente.dir_cli

FROM cliente, pedido WHERE pedido.fec_ped='02/25/2012';

END pedidos02252012;

• Listar todos los pedidos realizados incluyendo el nombre del artículo.

Create or replace PROCEDURE todospedidos(uno out SYS_REFCURSOR)

as

BEGIN

OPEN uno FOR

SELECT pedido.id_ped, pedido.id_cli_ped, pedido.fec_ped, pedido.val_ped, articulo.tit_art

FROM pedido, articulo;

END todospedidos;

• Listar los todos datos de los automotores cuya póliza expira en octubre de 2013, este reporte debe visualizar la placa, el modelo, la marca, número de pasajeros, cilindraje nombre de automotor, el valor de la póliza y el valor asegurado.

Create or replace PROCEDURE polizaoctubre(uno out SYS_REFCURSOR)

as

BEGIN

OPEN uno FOR

SELECT automotores.autoplaca, automotores.automodelo, automotores.automarca, automotores.autonumpasajeros, automotores.autocilindraje, automotores.autotipo, aseguramientos.asecosto, aseguramientos.asevalorasegurado

FROM automotores, aseguramientos

WHERE aseguramientos.asefechaexpiracion>'09/30/2013' AND aseguramientos.asefechaexpiracion<'11/01/2013';

END polizaoctubre;

3. En MySQL construya los procedimientos almacenados para realizar los siguientes procesos:

• Muestre todos los campos de la tabla curso en orden ascendente según el valor.

CREATE PROCEDURE `cursoascendente`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER Select * from curso ORDER BY valor_cur ASC;

call cursoascendente();

• Mostrar los pedidos con los respectivos artículos (código, nombre, valor y cantidad pedida).

Create procedure pedidosyarticulos() Select pedido.id_ped,pedido.id_cli_ped, pedido.fec_ped, pedido.val_ped,articulo.id_art, articulo.tit_art, articulo.prec_art,articuloxpedido.can_art_artped from pedido, articulo,articuloxpedido

call pedidosyarticulos()

• Visualizar los datos de las empresas fundadas entre el año 1991 y 1998.

CREATE PROCEDURE empresafundada() SELECT * FROM compañia WHERE comañofun>=1991 AND comañofun<=1998

call empresafundada()

• Visualizar todos los clientes organizados por apellido.

Create procedure apellidosclientes() Select * from cliente ORDER BY ape_cli

call apellidosclientes()

• Visualizar los datos de los incidentes que han tenido un(1) herido, este reporte debe visualizar la placa del automotor, con los respectivos datos de la póliza como son fecha de inicio, valor, estado y valor asegurado.

CREATE PROCEDURE incidentesunherido(uno int) SELECTincidentes.inciplaca, incidentes.incicantheridos,aseguramientos.asefechainicio, aseguramientos.asecosto,aseguramientos.aseestado, aseguramientos.asevaloraseguradoFROM incidentes, aseguramientos WHERE incicantheridos=uno

call incidentesunherido('1')

• Visualizar los incidentes del vehículo con placas " FLL420", este reporte debe visualizar la fecha, el lugar, la cantidad de heridos del incidente, la fecha de inicio la de expiración de la póliza y el valor asegurado.

CREATE PROCEDURE incidentesFLL420(fll varchar(6)) SELECTincidentes.incifecha,incidentes.incilugar,incidentes.incicantheridos,aseguramientos.asefechainicio,aseguramientos.asefechaexpiracion,aseguramientos.asevaloraseguradoFROM incidentes,aseguramientos WHERE inciplaca=fll

call incidentesFLL420('FLL420')

4. Realice las Siguientes funciones en MySQL:

• Cuente el número de estudiantes cuya edad sea mayor a 22.

CREATE FUNCTION `contarestudiantes`(`input` INT) RETURNS INT(11) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE cantidad int; SELECT COUNT(*) INTOcantidad FROM estudiante WHERE edad_est>'22'; RETURN cantidad; END

SELECT `contarestudiantes`(@p0) AS `contarestudiantes`

• Muestre el nombre y la edad del estudiante más joven.

CREATE FUNCTION `estudiantejoven`(`input` INT) RETURNS INT(11) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE joven int; SelectMIN(edad_est) INTO joven from estudiante; RETURN joven; END

SELECT `estudiantejoven`(@p0) AS `estudiantejoven`;

• Calcule el valor promedio de los cursos cuyas horas sean mayores a 40.

CREATE FUNCTION `valorpromedio`(`input` INT) RETURNS INT(11) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE promedio int; SELECT AVG(valor_cur) INTO promedioFROM curso WHERE horas_cur>='40'; RETURN promedio; END

SELECT `valorpromedio`(@p0) AS `valorpromedio`;

• Obtener el sueldo promedio de los profesores de la categoría 1.

CREATE FUNCTION `sueldopromedio`(`input` INT) RETURNS INT(11) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE promedio int; SELECT AVG(sal_prof) INTO promedioFROM profesor WHERE cate_prof='1'; RETURN promedio; END

SELECT `sueldopromedio`(@p0) AS `sueldopromedio`;

• Muestre el nombre del profesor con menor sueldo.

CREATE FUNCTION `menorsueldo`(`inout` INT) RETURNS VARCHAR(30) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE nombre varchar(30);SELECT nom_prof INTO nombre FROM profesor where sal_prof=(select min(sal_prof) from profesor); RETURN nombre; END

SELECT `menorsueldo`(@p0) AS `menorsueldo`;

5. Realice las Siguientes funciones en ORACLE:

• Visualizar el nombre del cliente, la fecha y el valor del pedido más costoso.

create or replace FUNCTION pedidomascostos RETURN NUMBER IS valor NUMBER;

BEGIN

SELECT MAX(val_ped) INTO valor FROM pedido;

RETURN valor;

END;

• Mostrar cuantos artículos se tienen de cada editorial.

create or replace FUNCTION contareditoriales RETURN NUMBER IS valor NUMBER;

BEGIN

SELECT COUNT(edi_art) INTO valor FROM articulo;

RETURN valor;

END;

• Visualizar todos los datos de la póliza más costosa.

create or replace FUNCTION polizacostosa RETURN NUMBER IS valor NUMBER;

BEGIN

SELECT asecodigo INTO valor FROM aseguramientos WHERE asecosto=(select min(asecosto) from aseguramientos );

RETURN valor;

END;

• Visualizar los incidentes con el mínimo número de autos involucrados, de este incidente visualizar el estado de la póliza y el valor asegurado.

create or replace FUNCTION menosautos RETURN NUMBER IS valor NUMBER;

BEGIN

SELECT incicodigo INTO valor FROM incidentes WHERE incicanautosinvolucrados=(select min(incicanautosinvolucrados) from incidentes);

RETURN valor;

END;

• Visualizar los datos de la póliza cuyo valor asegurado es el más costoso, este reporte además de visualizar todos los datos de la póliza, debe presentar todos los datos del vehículo que tiene dicha póliza.

create or replace FUNCTION polizacostosa RETURN NUMBER IS valor NUMBER;

BEGIN

SELECT asecodigo INTO valor FROM aseguramientos WHERE asevalorasegurado=(select max(asevalorasegurado) from aseguramientos );

RETURN valor;

END;

6. Construya los siguiente disparadores para controlar la actualización y borrado de registros

• En MySQL; para las tablas Profesor, Curso, Estudiante.

Actualización:

ü  Profesor

CREATE TRIGGER `profesorupdate` BEFORE UPDATE ON `profesor` FOR EACH ROW INSERT INTO trigger_profesores (DocumentoAnterior, NombreAnterior,ApellidoAnterior, CategoriaAnterior, SalarioAnterior, DocumentoNuevo, NombreNuevo, ApellidoNuevo, CategoriaNuevo, SalarioNuevo) VALUES (OLD.doc_prof, OLD.nom_prof, OLD.ape_prof,OLD.cate_prof, OLD.sal_prof, NEW.doc_prof, NEW.nom_prof, NEW.ape_prof, NEW.cate_prof, NEW.sal_prof);

ü  Curso

CREATE TRIGGER `cursoupdate` BEFORE UPDATE ON `curso` FOR EACH ROW INSERT INTO trigger_cursos(CodigoAnterior, NombreAnterior, HorasAnterior, ValorAnterior, CodigooNuevo, NombreNuevo,HorasNuevo, ValorNuevo) VALUES (OLD.cod_curs, OLD.nom_curs, OLD.horas_cur, OLD.valor_cur, NEW.cod_curs, NEW.nom_curs, NEW.horas_cur,NEW.valor_cur);

ü  Estudiante

CREATE TRIGGER `estudianteupdate` BEFORE UPDATE ON `estudiante` FOR EACH ROW INSERT INTO trigger_estudiante (DocumentoAnterior, NombreAnterior, ApellidoAnterior, EdadAnterior,DocumentoNuevo, NombreNuevo, ApellidoNuevo, EdadNuevo) VALUES (OLD.doc_est, OLD.nom_est, OLD.ape_est, OLD.edad_est, NEW.doc_est, NEW.nom_est, NEW.ape_est, NEW.edad_est);

Borrado

ü  Profesor

CREATE TRIGGER `profesordelete` AFTER DELETE ON `profesor` FOR EACH ROW INSERT INTO trigger_profesores (DocumentoAnterior, NombreAnterior, ApellidoAnterior, CategoriaAnterior,SalarioAnterior, DocumentoNuevo, NombreNuevo, ApellidoNuevo, CategoriaNuevo, SalarioNuevo) VALUES (OLD.doc_prof, OLD.nom_prof, OLD.ape_prof, OLD.cate_prof, OLD.sal_prof);

ü  Curso

CREATE TRIGGER `cursodelete` AFTER DELETE ON `curso` FOR EACH ROW INSERT INTO trigger_cursos(CodigoAnterior, NombreAnterior, HorasAnterior, ValorAnterior, CodigooNuevo,NombreNuevo,HorasNuevo, ValorNuevo) VALUES (OLD.cod_curs, OLD.nom_curs, OLD.horas_cur, OLD.valor_cur);

ü  Estudiante

CREATE TRIGGER `estudiantedelete` AFTER DELETE ON `estudiante` FOR EACH ROW INSERT INTO trigger_estudiante (DocumentoAnterior,NombreAnterior, ApellidoAnterior, EdadAnterior, DocumentoNuevo, NombreNuevo, ApellidoNuevo, EdadNuevo) VALUES (OLD.doc_est, OLD.nom_est, OLD.ape_est, OLD.edad_est)

• En ORACLE; para las tablas Cliente, Articulo, Pedido.

Actualización:

ü  Cliente

create or replace trigger "CLIENTEUPDATE"

BEFORE

update on "CLIENTE"

for each row

begin

INSERT INTO trigger_cliente (CodigoAnterior, NombreAnterior, ApellidoAnterior, DireccionAnterior,DepartamentoAnterior, MesCumpleAnterior, CodigoNuevo, NombreNuevo, ApellidoNuevo, DireccionNuevo, DepartamentoNuevo, MesCumpleNuevo) VALUES (:OLD.id_cli, :OLD.nom_cli, :OLD.ape_cli, :OLD.dir_cli, :OLD.dep_cli, :OLD.mes_cum_cli, :NEW.id_cli, :NEW.nom_cli,  :NEW.ape_cli, :NEW.dir_cli, :NEW.dep_cli, :NEW.mes_cum_cli);

end;​

ü  Articulo

create or replace trigger "ARTICULOUPDATE"

BEFORE

update on "ARTICULO"

for each row

begin

INSERT INTO trigger_articulo (CodigoAnterior, NombreAnterior, AutorAnterior, EditorialAnterior, PrecioAnterior, CodigoNuevo, NombreNuevo, AutorNuevo, EditorialNuevo, PrecioNuevo) VALUES (:OLD.id_art, :OLD.tit_art, :OLD.aut_art, :OLD.edi_art, :OLD.prec_art, :NEW.id_art, :NEW.tit_art,  :NEW.aut_art, :NEW.edi_art, :NEW.prec_art);

end;​

ü  Pedido

create or replace trigger "PEDIDOUPDATE"

BEFORE

update on "PEDIDO"

for each row

begin

INSERT INTO trigger_pedido (CodigoAnterior, DocumentoClienteAnterior, FechaAnterior, ValorAnterior, CodigoNuevo, DocumentoClienteNuevo, FechaNuevo, ValorNuevo) VALUES (:OLD.id_ped, :OLD.id_cli_ped, :OLD.fec_ped, :OLD.val_ped, :NEW.id_ped, :NEW.id_cli_ped, :NEW.fec_ped, :NEW.val_ped);

end;

Borrado

ü  Cliente

create or replace trigger "CLIENTEDELETE"

AFTER

delete on "CLIENTE"

for each row

begin

INSERT INTO trigger_cliente (CodigoAnterior, NombreAnterior, ApellidoAnterior, DireccionAnterior,DepartamentoAnterior, MesCumpleAnterior, CodigoNuevo, NombreNuevo, ApellidoNuevo, DireccionNuevo, DepartamentoNuevo, MesCumpleNuevo) VALUES (:OLD.id_cli, :OLD.nom_cli, :OLD.ape_cli, :OLD.dir_cli, :OLD.dep_cli, :OLD.mes_cum_cli);

end;​

ü  Articulo

create or replace trigger "ARTICULODELETE"

AFTER

delete on "ARTICULO"

for each row

begin

INSERT INTO trigger_articulo (CodigoAnterior, NombreAnterior, AutorAnterior, EditorialAnterior, PrecioAnterior, CodigoNuevo, NombreNuevo, AutorNuevo, EditorialNuevo, PrecioNuevo) VALUES (:OLD.id_art, :OLD.tit_art, :OLD.aut_art, :OLD.edi_art, :OLD.prec_art);

end;​

ü  Pedido

create or replace trigger "PEDIDODELETE"

AFTER

delete on "PEDIDO"

for each row

begin

INSERT INTO trigger_pedido (CodigoAnterior, DocumentoClienteAnterior, FechaAnterior, ValorAnterior, CodigoNuevo, DocumentoClienteNuevo, FechaNuevo, ValorNuevo) VALUES (:OLD.id_ped, :OLD.id_cli_ped, :OLD.fec_ped, :OLD.val_ped);

end;

• En el Motor de su preferencia (ORACLE O MySQL), para las tablas, Automotores, Aseguramientos, Incidentes.

Actualización:

ü  Automotores

CREATE TRIGGER `automotoresupdate` BEFORE UPDATE ON `automotores` FOR EACH ROW INSERT INTO trigger_automotores (PlacaAnterior, MarcaAnterior, TipoAnterior, ModeloAnterior,NumeroPasajerosAnterior, CilindrajeAnterior, NumeroChasisAnterior, PlacaNuevo, MarcaNuevo, TipoNuevo, ModeloNuevo, NumeroPasajerosNuevo, CilindrajeNuevo, NumeroChasisNuevo)VALUES (OLD.autoplaca, OLD.automarca, OLD.autotipo, OLD.automodelo, OLD.autonumpasajeros, OLD.autocilindraje, OLD.autonumchasis, NEW.autoplaca, NEW.automarca, NEW.autotipo,NEW.automodelo, NEW.autonumpasajeros, NEW.autocilindraje, NEW.autonumchasis)

ü  Aseguramientos

CREATE TRIGGER `aseguramientosupdate` BEFORE UPDATE ON `aseguramientos` FOR EACH ROW INSERT INTO trigger_aseguramientos(CodigoAnterior, FechaInicioAnterior,FechaExpiracionAnterior, ValorAseguradoAnterior, EstadoAnterior, CostoAnterior, PlacaAnterior, CodigoNuevo, FechaInicioNuevo, FechaExpiracionNuevo, ValorAseguradoNuevo,EstadoNuevo, CostoNuevo, PlacaNuevo) VALUES (OLD.asecodigo, OLD.asefechainicio, OLD.asefechaexpiracion, OLD.asevalorasegurado, OLD.aseestado, OLD.asecosto, OLD.aseplaca,NEW.asecodigo, NEW.asefechainicio, NEW.asefechaexpiracion, NEW.asevalorasegurado, NEW.aseestado, NEW.asecosto, NEW.aseplaca)

ü  Incidentes

CREATE TRIGGER `incidentesupdate` BEFORE UPDATE ON `incidentes` FOR EACH ROW INSERT INTO trigger_incidentes(CodigoAnterior, FechaAnterior, PlacaAnterior, LugarAnterior,CantidadHeridosAnterior, CantidadFatalidadesAnterior, CantidadAutosInvolucradosAnterior, CodigoNuevo, FechaNuevo, PlacaNuevo, LugarNuevo, CantidadHeridosNuevo,CantidadFatalidadesNuevo, CantidadAutosInvolucradosNuevo) VALUES (OLD.incicodigo, OLD.incifecha, OLD.inciplaca, OLD.incilugar, OLD.incicantheridos, OLD.incicanfatalidades,OLD.incicanautosinvolucrados, NEW.incicodigo, NEW.incifecha, NEW.inciplaca, NEW.incilugar, NEW.incicantheridos, NEW.incicanfatalidades, NEW.incicanautosinvolucrados)

Borrado

ü  Automotores

CREATE TRIGGER `automotoresdelete` AFTER DELETE ON `automotores` FOR EACH ROW INSERT INTO trigger_automotores (PlacaAnterior, MarcaAnterior, TipoAnterior, ModeloAnterior,NumeroPasajerosAnterior, CilindrajeAnterior, NumeroChasisAnterior, PlacaNuevo, MarcaNuevo, TipoNuevo, ModeloNuevo, NumeroPasajerosNuevo, CilindrajeNuevo, NumeroChasisNuevo)VALUES (OLD.autoplaca, OLD.automarca, OLD.autotipo, OLD.automodelo, OLD.autonumpasajeros, OLD.autocilindraje, OLD.autonumchasis)

ü  Aseguramientos

CREATE TRIGGER `aseguramientosdelete` AFTER DELETE ON `aseguramientos` FOR EACH ROW INSERT INTO trigger_aseguramientos(CodigoAnterior, FechaInicioAnterior,FechaExpiracionAnterior, ValorAseguradoAnterior, EstadoAnterior, CostoAnterior, PlacaAnterior, CodigoNuevo, FechaInicioNuevo, FechaExpiracionNuevo, ValorAseguradoNuevo,EstadoNuevo, CostoNuevo, PlacaNuevo) VALUES (OLD.asecodigo, OLD.asefechainicio, OLD.asefechaexpiracion, OLD.asevalorasegurado, OLD.aseestado, OLD.asecosto, OLD.aseplaca)

ü  Incidentes

CREATE TRIGGER `incidentesdelete` AFTER DELETE ON `incidentes` FOR EACH ROW INSERT INTO trigger_incidentes(CodigoAnterior, FechaAnterior, PlacaAnterior, LugarAnterior,CantidadHeridosAnterior, CantidadFatalidadesAnterior, CantidadAutosInvolucradosAnterior, CodigoNuevo, FechaNuevo, PlacaNuevo, LugarNuevo, CantidadHeridosNuevo,CantidadFatalidadesNuevo, CantidadAutosInvolucradosNuevo) VALUES (OLD.incicodigo, OLD.incifecha, OLD.inciplaca, OLD.incilugar, OLD.incicantheridos, OLD.incicanfatalidades,OLD.incicanautosinvolucrados)