EJERCICIO RESUELTO EN SQL SERVER

1. modelado

2. crear tablas

create database planillas
use planillas
go
-- tabla TRABAJADORES
create table TRABAJADORES(
TRA_codigo int primary key identity(1,1),
TRA_nombres varchar(50) not null,
TRA_apellidoPaterno varchar(50) not null,
TRA_apellidoMaterno varchar(50) not null,
TRA_dni char(8) not null,
TRA_fechaContrato date default(getdate()),
TRA_cargo varchar(50) not null check(TRA_cargo='Obrero' or TRA_cargo='Operador' or TRA_cargo='Ejecutivo') default('Operador'),
TRA_sueldoMensual money not null,
TRA_descuentoAFP float check(TRA_descuentoAFP<1 and TRA_descuentoAFP>0 )
)



--tabla USUARIOS
create table USUARIOS(
USU_codigo varchar(200) primary key,
TRA_codigo int references TRABAJADORES(TRA_codigo),
TRA_contraseña varchar(200) not null
)
-- tabla REGISTROS
create table REGISTROS(
REG_codigo int primary key identity(1,1),
TRA_codigo int references TRABAJADORES(TRA_codigo),
REG_horaEntrada tinyint not null check(REG_horaEntrada<24),
REG_horaSalida tinyint not null check(REG_horaSalida<24),
REG_fecha date not null default(getdate())
)
-- tabla HORARIO
create table HORARIOS(
HOR_codigo int primary key identity(1,1),
TRA_codigo int references TRABAJADORES(TRA_codigo)
)
-- tabla TURNOS
create table TURNOS(
TUR_codigo int primary key identity(1,1),
TUR_turno varchar(50) not null check(TUR_turno='Mañana' or TUR_turno='Tarde' or TUR_turno='Noche') default('Mañana'),
TUR_horaInicio tinyint not null check(TUR_horaInicio<=24 and(TUR_horaInicio=8 or TUR_horaInicio=16 or TUR_horaInicio=24)),
TUR_horaFinal tinyint not null check(TUR_horaFinal<=24 and(TUR_horaFinal=16 or TUR_horaFinal=24 or TUR_horaFinal=8))

)

-- tabla DETALLE_HORARIOS
create table DETALLE_HORARIOS(
HOR_codigo int references HORARIOS(HOR_codigo),
TUR_codigo int references TURNOS(TUR_codigo),
DHO_dia varchar(50) not null
)
--tabla HORAS_EXTRAS
create table HORAS_EXTRAS(
HEX_codigo int primary key identity(1,1),
TRA_codigo int references TRABAJADORES(TRA_codigo),
HEX_fecha date not null default(getdate()),
HEX_horaEntrada tinyint not null,
HEX_horaSalida tinyint not null,
HEX_totalHoras tinyint not null
)
--tabla TAREAS
create table TAREAS(
TAR_codigo int primary key identity(1,1),
TAR_nombre varchar(200) not null,
TAR_descripcion text
)

-- tabla RESOLUCIONES
create table RESOLUCIONES(
RES_codigo int primary key identity(1,1),
HEX_codigo int references HORAS_EXTRAS(HEX_codigo),
TAR_codigo int references TAREAS(TAR_codigo)
)

3. trigger

use planillas
go
create trigger tgUsuarios on TRABAJADORES 
for  insert
as
begin
 declare @apellidoPaterno varchar(50),
 @apellidoMaterno varchar(50),
 @nombres varchar(50),
 @dni char(8),
 @codigoTrabajador int 
select
 @codigoTrabajador=i.TRA_codigo,
 @apellidoPaterno=i.TRA_apellidoPaterno,
 @apellidoMaterno=i.TRA_apellidoMaterno,
 @nombres=i.TRA_nombres,
 @dni=i.TRA_dni

from inserted i

 -- establecer codigo
  declare @codigoUsuario varchar(200)
  --apellido al revez
   declare @apellidoMaternoRevez varchar(200)
   set @apellidoMaternoRevez=reverse(@apellidoMaterno)


  --correlativo autogenerado
   declare @apMaterno varchar(200)
   declare @correlativo int
   set @correlativo=0
   declare recorrerTrabajadore cursor read_only for
   select
    t.TRA_apellidoMaterno
   from TRABAJADORES t
   open recorrerTrabajadore
   FETCH NEXT FROM recorrerTrabajadore  INTO  @apMaterno
   WHILE @@fetch_status = 0 
   begin
    if @apMaterno=@apellidoMaterno
     set @correlativo=@correlativo+1
    FETCH NEXT FROM recorrerTrabajadore  INTO @apMaterno
   end
   
   close recorrerTrabajadore
   deallocate recorrerTrabajadore
   if @correlativo<10
    set @codigoUsuario=@apellidoMaternoRevez+'0'+cast(@correlativo as varchar(10))+'@unitru.edu.pe'
   else
    set @codigoUsuario=@apellidoMaternoRevez+cast(@correlativo as varchar(10))+'@unitru.edu.pe'
 -- establecer contraseña
 declare @contraseñaUsuario varchar(200)
  -- # letras ApMaterno Excepto Vocales (de 2 dígitos)
  declare @cantidadLetras int
  declare @tamañoApellidoMaterno int
  declare @letra char(1)
  set @tamañoApellidoMaterno=len(@apellidoMaterno)
  set @cantidadLetras=0
  while @tamañoApellidoMaterno>0
  begin
   set @letra=LOWER(SUBSTRING(@apellidoMaterno,@tamañoApellidoMaterno,1))
   if @letra!='a' and @letra!='e' and @letra!='i' and @letra!='o' and @letra!='u'
       set @cantidadLetras=@cantidadLetras+1
   set @tamañoApellidoMaterno=@tamañoApellidoMaterno-1
  end
  -- # Vocales ApPaterno
  declare @tamañoApellidoPaterno int
  declare @cantidadVocales int
  set @cantidadVocales=0
  declare @vocal char(1)
  set @tamañoApellidoPaterno=len(@apellidoPaterno)
  while @tamañoApellidoPaterno>0
  begin
   set @vocal=LOWER(SUBSTRING(@apellidoPaterno,@tamañoApellidoPaterno,1))
   if @vocal='a' or @vocal='e' or @vocal='i' or @vocal='o' or @vocal='u' 
    set @cantidadVocales=@cantidadVocales+1
   set @tamañoApellidoPaterno=@tamañoApellidoPaterno-1
  end
 if @cantidadLetras<10
  set @contraseñaUsuario='0'+cast(@cantidadLetras as varchar(20))+'&'+cast(@cantidadVocales as varchar(20))+SUBSTRING(@dni,1,2)+SUBSTRING(@dni,7,2)
 else
  set @contraseñaUsuario=cast(@cantidadLetras as varchar(20))+'&'+cast(@cantidadVocales as varchar(20))+SUBSTRING(@dni,1,2)+SUBSTRING(@dni,7,2)

 insert into USUARIOS VALUES(@codigoUsuario,@codigoTrabajador,@contraseñaUsuario)
 

end

4. prueba

insert into TRABAJADORES values('Juan','Santos','Fernandez','27965149',getdate(),'Ejecutivo',3000,0.5)
select * from USUARIOS

Compartir

elson