AP6-AA2-Ev1-Usar AP6-AA2-Ev1-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 from profesor, profesor, curso, estudiante, estudiante xcurso; 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 pr preferencia eferencia (ORACLE O MySQL), para las tablas, Compañía, TiposAutomotores, Automotores, Aseguramientos, Incidentes. Create procedure seguros() Select * from from compañia, compañia, tiposautomotores, automot ores, 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 W HERE pedido.fec_ped='02/25/2012'; pedido.fec_ped='02/25/2012'; END pedidos02252012; 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 CONTAIN S SQL SQL SECURITY DEFINER Select * from curso ORDER BY valor_cur A SC; 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, p edido.fec_ped, pedido.val_ped,articulo.id_art, articulo.tit_art, articulo.prec_art,ar ticuloxpedido.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 WHER E 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.inciplac a, incidentes.incicantheridos,aseguramientos.asefechainicio, aseguramientos.a
secosto,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.inc ifecha,incidentes.incilugar ,incidentes.incicantheridos,aseguramientos.asefechai nicio,aseguramientos.asefechaexpiracion,aseguramientos.asevaloraseguradoF ROM 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 DECLA RE 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 D ETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLAR E 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 DE TERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLARE promedio int; SELECT AVG(valor_cur) INTO promedioFROM curso WHERE ho ras_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 D ETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DECLAR E promedio int; SELECT AVG(sal_prof) INTO promedioFROM profesor WHER E 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) N OT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN DEC LARE nombre varchar (30);SELECT nom_prof INTO nombre FROM profesor wh ere 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 E ACH ROW INSERT INTO trigger_profesores (DocumentoAnterior, NombreAnte rior,ApellidoAnterior, CategoriaAnterior, SalarioAnterior, DocumentoNuevo, No mbreNuevo, ApellidoNuevo, CategoriaNuevo, SalarioNuevo) VALUES (OLD.do c_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, HorasAnt
erior, ValorAnterior, CodigooNuevo, NombreNuevo,HorasNuevo, ValorNuevo) VALUES (OLD.cod_curs, OLD.nom_curs, OLD.horas_cur , OLD.valor_cur , NE W.cod_curs, NEW.nom_curs, NEW.horas_cur ,NEW.valor_cur ); Estudiante CREATE TRIGGER `estudianteupdate` BEFORE UPDATE ON `estudiante` FO R EACH ROW INSERT INTO trigger_estudiante (DocumentoAnterior, NombreA nterior, ApellidoAnterior, EdadAnterior,DocumentoNuevo, NombreNuevo, Apelli doNuevo, EdadNuevo) VALUES (OLD.doc_est, OLD.nom_est, OLD.ape_est, O LD.edad_est, NEW.doc_est, NEW.nom_est, NEW.ape_est, NEW.edad_est); Borrado Profesor CREATE TRIGGER `profesordelete` AFTER DELETE ON `profesor` FOR EAC H ROW INSERT INTO trigger_profesores (DocumentoAnterior, NombreAnterior , ApellidoAnterior, CategoriaAnterior,SalarioAnterior, DocumentoNuevo, Nombr eNuevo, ApellidoNuevo, CategoriaNuevo, SalarioNuevo) VALUES (OLD.doc_pr of , OLD.nom_prof , OLD.ape_prof , OLD.cate_prof , OLD.sal_prof ); Curso CREATE TRIGGER `cursodelete` AFTER DELETE ON `curso` FOR EACH RO W INSERT INTO trigger_cursos(CodigoAnterior, NombreAnterior, HorasAnterio r, ValorAnterior, CodigooNuevo,NombreNuevo,HorasNuevo, ValorNuevo) VAL UES (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,NombreAnt erior, ApellidoAnterior, EdadAnterior, DocumentoNuevo, NombreNuevo, Apellid oNuevo, EdadNuevo) VALUES (OLD.doc_est, OLD.nom_est, OLD.ape_est, OL D.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) :OLD.fec_ped, :OLD.val_ped); end;
VALUES
(:OLD.id_ped,
:OLD.id_cli_ped,
• 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, MarcaA nterior, TipoAnterior, ModeloAnterior,NumeroPasajerosAnterior, CilindrajeAnteri or, NumeroChasisAnterior, PlacaNuevo, MarcaNuevo, TipoNuevo, ModeloNuev o, NumeroPasajerosNuevo, CilindrajeNuevo, NumeroChasisNuevo)VALUES (O LD.autoplaca, OLD.automarca, OLD.autotipo, OLD.automodelo, OLD.autonump asajeros, OLD.autocilindraje, OLD.autonumchasis, NEW.autoplaca, NEW.auto marca, NEW.autotipo,NEW.automodelo, NEW.autonumpasajeros, NEW.autocili ndraje, NEW.autonumchasis) Aseguramientos CREATE TRIGGER `aseguramientosupdate` BEFORE UPDATE ON `asegura mientos` FOR EACH ROW INSERT INTO trigger_aseguramientos(CodigoAnter ior, FechaInicioAnterior,FechaExpiracionAnterior, ValorAseguradoAnterior, Esta doAnterior, CostoAnterior, PlacaAnterior, CodigoNuevo, FechaInicioNuevo, Fec haExpiracionNuevo, ValorAseguradoNuevo,EstadoNuevo, CostoNuevo, PlacaN uevo) VALUES (OLD.asecodigo, OLD.asefechainicio, OLD.asefechaexpiracion, OLD.asevalorasegurado, OLD.aseestado, OLD.asecosto, OLD.aseplaca,NEW. asecodigo, NEW.asefechainicio, NEW.asefechaexpiracion, NEW.asevaloraseg urado, NEW.aseestado, NEW.asecosto, NEW.aseplaca) Incidentes CREATE TRIGGER `incidentesupdate` BEFORE UPDATE ON `incidentes` FO R EACH ROW INSERT INTO trigger_incidentes(CodigoAnterior, FechaAnterior, PlacaAnterior, LugarAnterior,CantidadHeridosAnterior, CantidadFatalidadesAnt erior, CantidadAutosInvolucradosAnterior, CodigoNuevo, FechaNuevo, PlacaN uevo, LugarNuevo, CantidadHeridosNuevo,CantidadFatalidadesNuevo, Cantida dAutosInvolucradosNuevo) VALUES (OLD.incicodigo, OLD.incifecha, OLD.incip laca, OLD.incilugar , OLD.incicantheridos, OLD.incicanfatalidades,OLD.incicana utosinvolucrados, NEW.incicodigo, NEW.incifecha, NEW.inciplaca, NEW.incilug ar , NEW.incicantheridos, NEW.incicanfatalidades, NEW.incicanautosinvolucrad os) Borrado Automotores CREATE TRIGGER `automotoresdelete` AFTER DELETE ON `automotores` F OR EACH ROW INSERT INTO trigger_automotores (PlacaAnterior, MarcaAnte rior, TipoAnterior, ModeloAnterior,NumeroPasajerosAnterior, CilindrajeAnterior, NumeroChasisAnterior, PlacaNuevo, MarcaNuevo, TipoNuevo, ModeloNuevo, NumeroPasajerosNuevo, CilindrajeNuevo, NumeroChasisNuevo)VALUES (OL D.autoplaca, OLD.automarca, OLD.autotipo, OLD.automodelo, OLD.autonumpa sajeros, OLD.autocilindraje, OLD.autonumchasis) Aseguramientos CREATE TRIGGER `aseguramientosdelete` AFTER DELETE ON `aseguramie ntos` FOR EACH ROW INSERT INTO trigger_aseguramientos(CodigoAnterior,
FechaInicioAnterior,FechaExpiracionAnterior, ValorAseguradoAnterior, EstadoA nterior, CostoAnterior, PlacaAnterior, CodigoNuevo, FechaInicioNuevo, FechaE xpiracionNuevo, ValorAseguradoNuevo,EstadoNuevo, CostoNuevo, PlacaNuev o) VALUES (OLD.asecodigo, OLD.asefechainicio, OLD.asefechaexpiracion, OL D.asevalorasegurado, OLD.aseestado, OLD.asecosto, OLD.aseplaca) Incidentes CREATE TRIGGER `incidentesdelete` AFTER DELETE ON `incidentes` FOR E ACH ROW INSERT INTO trigger_incidentes(CodigoAnterior, FechaAnterior, Pl acaAnterior, LugarAnterior,CantidadHeridosAnterior, CantidadFatalidadesAnteri or, CantidadAutosInvolucradosAnterior, CodigoNuevo, FechaNuevo, PlacaNuev o, LugarNuevo, CantidadHeridosNuevo,CantidadFatalidadesNuevo, CantidadA utosInvolucradosNuevo) VALUES (OLD.incicodigo, OLD.incifecha, OLD.incipla ca, OLD.incilugar , OLD.incicantheridos, OLD.incicanfatalidades,OLD.incicanaut osinvolucrados)
