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

CREAR UN TIPO DE DATO EN SQL SERVER

En vista que SQL Server nos proporciona una serie de tipos de dato a veces necesitaremos algún tipo especial. Como el caso del DNI que cuenta con 8 caracteres de tipo CHAR o el número telefónico de 15 caracteres.

CURSORES EN SQL SERVER

Los cursores permiten tener el control fila por fila de un conjunto de registros.
¿en que caso usamos cursores?, puesto que si ya tenemos acceso alas filas de una tabla uno a uno entonces podremos obtener dichos valores y controlarlos desde variables locales y así poder acumular valores, contarlos, aplicar quiebres, etc.

ejemplo en sql server(triggers, cursores)

 1) modelado de base de datos




2) script: crear las tablas

create database BDMatricula;
use BDMatricula;
go

create table alumno(
idAlumno int primary key identity(1,1),
nombres varchar(50) not null,
apellidos varchar(50) not null
)

create table curso(
idCurso int primary key identity(1,1),
nombre varchar(50) not null,
creditos smallint not null default(3),
)

create table preRequisito(
idCurso int references curso(idCurso),
curso int references curso(idCurso)
)
create table seccion(
idSeccion int primary key identity(1,1),
nombre varchar(50) not null,
)
create table horario(
idSeccion int references seccion(idSeccion),
idCurso int references curso(idCurso),
vacantes int not null default(0),
dia varchar(10) not null,
horaInico int not null,
horaFinal int not null
)
create table nota(
idCurso int references curso(idCurso),
idAlumno int references alumno(idAlumno),
nota1 int  default(0),
nota2 int  default(0),
nota3 int  default(0),
nota4 int  default(0)
)

create table cronograma(
idCronograma int primary key identity(1,1),
inicioMatricula date not null default getdate(),
finMatricula date not null
)

create table matricula(
idMatricula int primary key identity(1,1),
idAlumno int references alumno(idAlumno),
fecha date default getdate()
)
create table detalleMatricula(
idMatricula int references matricula(idMatricula),
idCurso int references curso(idCurso),
idSeccion int references seccion(idSeccion)
)
create table registrarMatricula(
idMatricula int references matricula(idMatricula),
idCronograma int references cronograma(idCronograma),
recibo varchar(50) not null,
totalCreditos int not null
constraint creditos check( totalCreditos>=12 and totalCreditos<=22)
)

3)trigger para detalle matrícula

create trigger tgDetalleMatricula on detalleMatricula for insert, update
as
begin
 declare @idAlumno int
 declare @idSeccion int
 declare @idMatricula int
 declare @idCurso int
 select
  @idMatricula=i.idMatricula,
  @idCurso=i.idCurso,
  @idSeccion=i.idSeccion,
  @idAlumno=m.idAlumno
 from inserted i
 inner join matricula m on i.idMatricula=m.idMatricula
 -- b.4 Debe haber vacante en la sección donde desea matricularse
 declare @vacantes int
 select
  @vacantes=vacantes
 from horario
 where idSeccion=@idSeccion and idCurso=@idCurso
 if @vacantes=0
 begin
  raiserror ('no hay vacantes disponibles', 16, 2)
     rollback transaction 
 end
end
go

4) trigger para registrar matrícula

create trigger tgRegistraMatricula on registrarMatricula
for insert, update
as
begin
 declare @idMatricula int
 declare @idAlumno int
 declare @idCronograma int

 select
  @idMatricula=idMatricula,
  @idCronograma=idCronograma
 from inserted
 select @idAlumno=idAlumno
 from matricula
 -- a.1El registro de la matrícula solamente se puede efectuar
 -- en las fechas establecidas en el cronograma;
 declare @inicioMatricula date
 declare @finMatricula date
 declare @fechaMatricula date
 select
  @inicioMatricula=inicioMatricula,
  @finMatricula=finMatricula
 from cronograma
 where idCronograma=@idCronograma;
 select
  @fechaMatricula=fecha
 from matricula
 where idMatricula=@idMatricula;
 
 if @fechaMatricula<@inicioMatricula or @fechaMatricula>@finMatricula
 begin
  raiserror ('las matrícula se realizó fuera de fecha', 16, 2)
     rollback transaction
 end
 -- a.2 se debe verificar el pago de matrícula registrando
 -- el número de recibo respectivo
 -- solución: registrarMatricula.recibo es not null por lo tanto
 -- si o si tiene que ingresarse el campo recibo

 -- b.1 La nota del alumno, en el curso pre requisito
 --  del Plan de estudios actual, debe estar aprobado
 declare @nota1 int,@nota2 int,@nota3 int, @nota4 int
 declare @desaprobado int
 set @desaprobado=0
 declare infoNotasPreRequisitos cursor read_only for
 select
  n.nota1,
  n.nota2,
  n.nota3,
  n.nota4
 from matricula m
 join alumno a on m.idAlumno=m.idAlumno
 join detalleMatricula dm on dm.idMatricula=m.idMatricula
 join curso c on dm.idCurso=c.idCurso
 join preRequisito pr on pr.idCurso=c.idCurso
 join nota n on n.idCurso=pr.curso
 where m.idMatricula=@idMatricula;
 open infoNotas
 FETCH NEXT FROM infoNotasPreRequisitos INTO @nota1,@nota2,@nota3,@nota4
 WHILE @@fetch_status = 0
 begin
  if @nota1<=10 and @nota2<=10 and @nota3<=10 and @nota4<=10
   set @desaprobado=1
  FETCH NEXT FROM infoNotasPreRequisitos INTO @nota1,@nota2,@nota3,@nota4
 end
 close infoNotasPreRequisitos
 if @desaprobado=1
 begin
  raiserror ('cursos pre requisitos no aprobados', 16, 2)
     rollback transaction  
 end

 --  El total de créditos no debe ser menor de 12, ni mayor a 22 créditos.
 declare @totalCreditos int
 declare @creditos int
 set @totalCreditos=0
 declare cTotalCreditos cursor read_only for
 select c.creditos
 from matricula m
 inner join detalleMatricula dm on dm.idMatricula=m.idMatricula
 inner join curso c on dm.idCurso=c.idCurso
 where m.idMatricula=1
 open cTotalCreditos
 FETCH NEXT FROM cTotalCreditos  INTO @creditos
 WHILE @@fetch_status = 0
 BEGIN
  set @totalCreditos=@totalCreditos+@creditos
  FETCH NEXT FROM cTotalCreditos  INTO @creditos
 END
 close cTotalCreditos
 if @totalCreditos<12 or @totalCreditos>22
 begin
  raiserror ('total de créditos no válidos', 16, 2)
     rollback transaction 
 end
-- b.3 El alumno no puede tener una asignatura con más de 3 notas desaprobadas
 set @desaprobado=0
 declare notasAlumno cursor read_only for
 select
 nota1,nota2,nota3,nota4
 from nota
 where idAlumno=@idAlumno
 open notasAlumno
 FETCH NEXT FROM notasAlumno  INTO @nota1,@nota2,@nota3,@nota4
 WHILE @@fetch_status = 0
 BEGIN
  if @nota1<=10 and @nota2<=10 and @nota3<=10 and @nota4<=10
   set @desaprobado=1
  FETCH NEXT FROM notasAlumno  INTO @nota1,@nota2,@nota3,@nota4
 END
 close notasAlumno
 if @desaprobado=1
 begin
  raiserror ('Más de tres notas desaprobatorias', 16, 2)
     rollback transaction 
 end 

 --c1. Considerar que al confirmarse el registro de la matricula del alumno;
 --se actualizará la vacante del curso
 
 
 declare cambiarVacantes cursor read_only for
 select
  idCurso,
  idSeccion
 from detalleMatricula dm
 where dm.idMatricula=@idMatricula
 open cambairVacantes
  FETCH NEXT FROM cambairVacantes  INTO @idCurso,@idSeccion
 WHILE @@fetch_status = 0
 BEGIN
  update horario
  set vacantes=vacantes-1
  where idCurso=@idCurso and idSeccion=@idSeccion
  FETCH NEXT FROM cambairVacantes  INTO @idCurso,@idSeccion
 END
 close cambairVacantes

end

MANEJO DE ESQUEMAS EN SQL SERVER

El esquema de una base de dato describe la estructura de la misma ya que todos los objetos que se puede crear o administra en SQL server se encuentra dentro de un esquema estándar .
El manejo de esquemas responde a temas como agrupación de objetos y seguridad de las mismas. Cuando se crea un objeto de base de datos o se especifica una entidad de seguridad de dominio válida(usuario o grupo) como la propietaria del objeto, o la entidad de seguridad de dominio se agregará a la base de datos como esquema. Esa entidad  de seguridad  de dominio será la propietaria del nuevo esquema

sintaxis de creación:

CREATE SCHEMA nombreEsquema AUTHORIZATION nombrePropietario

sintaxis de eliminación de esquemas:
para eliminar un esquema debe tener en cuenta que no tenga objetos asociado a él. Por lo tanto, la sitaxis reflejará los pasos a seguir:
DROP TABLE
GO
DROP nombreEsquema SCHEMA AUTHORIZATION nombrePropietario
GO

ejemplo:

-- implemente los esquemas PAGO dentro de la base de datos BDMatricula

use BDMatricula
go
create schema pago authorization dbo
go

select * from sys.schemas where principal_id=1

Tomar en cuenta que hay varios esquemas dentro de una base de datos, pero la identificación asociada al propietario DBO es uno por ese motivo la consulta se condiciona a ese valor por la columna PRINCIPAL_ID.