ejer cici os transact sq l

Ejercicios Transact-SQL

Vistas

Un club dicta cursos de distintos deportes. Almacena la información en varias tablas.

El director no quiere que los empleados de administración conozcan la estructura de las tablas ni

algunos datos de los profesores y socios, por ello se crean vistas a las cuales tendrán acceso.

1.- Crear una base de datos llamada “db_Ejercicios”

2.- Correr el siguiente Script, para crear las tabla e insertarle los datos.

create table socios(

documento char(8) not null,

nombre varchar(40),

domicilio varchar(30),

constraint PK_socios_documento

primary key (documento)

)

Go

create table profesores(


nombre varchar(40),


constraint PK_profesores_documento

primary key (documento)

)

Go

create table cursos(

numero tinyint identity,

deporte varchar(20),

dia varchar(15),

constraint CK_inscriptos_dia check (dia

in('lunes','martes','miercoles','jueves','viernes','sabado')),

documentoprofesor char(8),

constraint PK_cursos_numero

primary key (numero),

)

Go

create table inscriptos(

documentosocio char(8) not null,

numero tinyint not null,

matricula char(1),

constraint CK_inscriptos_matricula check (matricula in('s','n')),

constraint PK_inscriptos_documento_numero

primary key (documentosocio,numero)

)

Go

--Ingrese algunos registros para todas las tablas:

insert into socios values('30000000','Fabian Fuentes','Caseros 987')Go

insert into socios values('31111111','Gaston Garcia','Guemes 65')Go

insert into socios values('32222222','Hector Huerta','Sucre 534')Go

insert into socios values('33333333','Ines Irala','Bulnes 345')Go

insert into profesores values('22222222','Ana Acosta','Avellaneda 231')Go

insert into profesores values('23333333','Carlos Caseres','Colon 245')Go

insert into profesores values('24444444','Daniel Duarte','Sarmiento 987')Go

insert into profesores values('25555555','Esteban Lopez','Sucre 1204')Go

insert into cursos values('tenis','lunes','22222222')Go

insert into cursos values('tenis','martes','22222222')Go

insert into cursos values('natacion','miercoles','22222222')Go

insert into cursos values('natacion','jueves','23333333')Go

insert into cursos values('natacion','viernes','23333333')Go

insert into cursos values('futbol','sabado','24444444')Go

insert into cursos values('futbol','lunes','24444444')Go

insert into cursos values('basquet','martes','24444444')Go

insert into inscriptos values('30000000',1,'s')Go

insert into inscriptos values('30000000',3,'n')Go

insert into inscriptos values('30000000',6,null)Go




1- Cree una vista en la que aparezca el nombre y documento del socio, el deporte, el día y el

nombre del profesor.

2- Realice una consulta a la vista donde muestre la cantidad de socios inscriptos en cada

deporte

3- ordenados por cantidad.

4- Muestre (consultando la vista) los cursos (deporte y día) para los cuales no hay inscriptos.

5- Muestre los nombres de los socios que no se han inscripto en ningún curso (consultando la

vista)

6- Cree una vista para que muestre la cantidad de inscriptos por curso, incluyendo el número

del curso, el nombre del deporte y el día.

7- Cree la vista "vis_deudores" que muestre el documento y nombre del socio, el deporte, el

día y la matrícula, de todas las inscripciones no pagas colocando "with check option".

8- Modifique la vista "vis_deudores" para que muestre el domicilio, coloque la opción de

encriptación y omita with check option".

Procedimientos almacenados

Una empresa almacena los datos de sus empleados en una tabla llamada "empleados".

1.- En la misma base de datos llamada “db_Ejercicios” creada en los ejercicios de vista

2.- Correr el siguiente Script, para crear las tablas e insertarle los datos.

create table empleados(

documento char(8),

nombre varchar(20),

apellido varchar(20),

sueldo decimal(6,2),

cantidadhijos tinyint,

seccion varchar(20),

primary key(documento)

)Go

-- Ingrese algunos registros:

insert into empleados values('22222222','Juan','Perez',300,2,'Contaduria')Go

insert into empleados values('22333333','Luis','Lopez',300,0,'Contaduria')Go

insert into empleados values ('22444444','Marta','Perez',500,1,'Sistemas')Go

insert into empleados values('22555555','Susana','Garcia',400,2,'Secretaria')Go

insert into empleados values('22666666','Jose Maria','Morales',400,3,'Secretaria')Go

insert into empleados values('22777777','Andres','Perez',3,'Sistemas')Go

insert into empleados values('22888888','Laura','Garcia',3,'Secretaria')

1. Cree un procedimiento almacenado llamado "pa_emp_sueldo" que seleccione los

nombres, apellidos y sueldos de los empleados.

2. Cree un procedimiento almacenado llamado "pa_emp_hijos" que seleccione los nombres,

apellidos y cantidad de hijos de los empleados con hijos.

3. Cree un procedimiento almacenado llamado "pa_emp_sueldo" que seleccione los

nombres, apellidos y sueldos de los empleados que tengan un sueldo superior o igual al

enviado como parámetro.

4. Cree un procedimiento almacenado llamado "pa_emp_actualizar_sueldo" que actualice

los sueldos iguales al enviado como primer parámetro con el valor enviado como segundo

parámetro.

a. Ejecute el procedimiento creado anteriormente y verifique si se ha ejecutado

correctamente: exec pa_emp_actualizar_sueldo 300,350

b. Ejecute el procedimiento almacenado "pa_emp_actualizar_sueldo" enviando en

primer lugar el parámetro @sueldonuevo y en segundo lugar @sueldoanterior

(parámetros por nombre).

5. Cree un procedimiento llamado "pa_suetotal" que reciba el documento de un empleado y

muestre su nombre, apellido y el sueldo total (resultado de la suma del sueldo y salario

por hijo, que es de $200 si el sueldo es menor a $500 y $100, si el sueldo es mayor o igual

a $500). Coloque como valor por defecto para el parámetro el patrón "%".

a. Ejecute el procedimiento anterior enviando diferentes valores:

exec pa_suetotal '22333333'

6. Cree un procedimiento que muestre todos los empleados de una sección determinada que

se ingresa como parámetro. Si no se ingresa un valor, o se ingresa "null", se muestra un

mensaje y se sale del procedimiento.

7. Cree un procedimiento almacenado que permita modificar la cantidad de hijos ingresando

el documento de un empleado y la cantidad de hijos nueva. Ambos parámetros DEBEN

ingresarse con un valor distinto de "null". El procedimiento retorna "1" si la actualización

se realiza (si se ingresan valores para ambos parámetros) y "0", en caso que uno o ambos

parámetros no se ingresen o sean nulos.

Un profesor guarda en una tabla llamada "alumnos" el nombre de los alumnos y su nota.

create table alumnos(

documento char(8),

nombre varchar(40),

nota decimal(4,2),

primary key(documento)

)Go

-- Ingrese algunos registros:

insert into alumnos values ('22222222','Pedro Lopez',5)Go

insert into alumnos values ('23333333','Ana Lopez',4)Go

insert into alumnos values ('24444444','Maria Juarez',8)Go

insert into alumnos values ('25555555','Juan Garcia',5.6)Go

insert into alumnos values ('26666666','Karina Torres',2)Go

insert into alumnos values ('27777777','Nora Torres',7.5)Go

insert into alumnos values ('28888888','Mariano Herrero',3.5)

Go

create table aprobados(

documento char(8),

nombre varchar(40),

nota decimal(4,2)

)

Go

create table desaprobados(

documento char(8),

nombre varchar(40)

)

8. Cree el procedimiento para que seleccione todos los datos de los alumnos cuya nota es

igual o superior a 4. Y los Ingrese en la tabla "aprobados". Los alumnos cuya nota es

menor a 4. Los Ingrese en la tabla "desaprobados".

Una librería almacena los datos de los libros en una tabla denominada "libros" y en una tabla

"ventas" las ventas de los mismos.

create table libros(

codigo int identity,

titulo varchar(40),

autor varchar(30),

editorial varchar(20),

precio decimal(6,2),

cantidad int,

primary key (codigo)

)

Go

create table ventas(

numero int identity,

codigo int not null,

preciounitario decimal(6,2),

cantidad int,

constraint PK_ventas primary key (numero),

constraint FK_ventas_codigolibro

foreign key (codigo)

references libros(codigo)

on update cascade

) Go

--Ingrese algunos registros para ambas:

insert into libros values('Uno','Richard Bach','Planeta',15,100)Go

insert into libros values('Ilusiones','Richard Bach','Planeta',18,150)Go

insert into libros values('El aleph','Borges','Emece',25,200)Go

insert into libros values('Matematica estas ahi','Paenza','Nuevo siglo',20,300)Go

insert into libros values('Aprenda PHP','Mario Molina','Nuevo siglo',45,200)Go

insert into ventas values(1,15,1)Go




9. Cree un procedimiento que muestre los datos de las ventas (número, título, autor y

editorial del libro vendido, precio unitario, cantidad vendida, total por item).

10. Cree un procedimiento que permita ingresar una venta en "ventas" con los siguientes

datos: código del libro y cantidad que se vende. El procedimiento debe controlar que haya

libros disponibles (es decir, que la cantidad que se vende sea mayor o igual a la cantidad

existente del libro) y luego restar la cantidad vendida de la tabla "libros": Este

procedimiento recibe parámetros, declara variables locales y modifica 2 tablas, en una de

ellas realiza una inserción y en la otra una actualización.

Funciones

Una clínica almacena los turnos para los distintos médicos en una tabla llamada "consultas" y en

otra tabla "medicos" los datos de los médicos.

create table medicos (


nombre varchar(30),

constraint PK_medicos

primary key clustered (documento)

)Go

create table consultas(

fecha datetime,

medico char(8) not null,

paciente varchar(30),

constraint PK_consultas

primary key (fecha,medico),

constraint FK_consultas_medico

foreign key (medico)

references medicos(documento)

on update cascade

on delete cascade

)Go

3- Ingrese algunos registros:

insert into medicos values('22222222','Alfredo Acosta')Go

insert into medicos values('23333333','Pedro Perez')Go

insert into medicos values('24444444','Marcela Morales')Go

insert into consultas values('2007/03/26 8:00','22222222','Juan Juarez')Go

insert into consultas values('2007/03/26 8:00','23333333','Gaston Gomez')Go

insert into consultas values('2007/03/26 8:30','22222222','Nora Norte')Go

insert into consultas values('2007/03/28 9:00','22222222','Juan Juarez')Go

insert into consultas values('2007/03/29 8:00','24444444','Nora Norte')Go

insert into consultas values('2007/03/24 8:30','22222222','Hector Huerta')Go

insert into consultas values('2007/03/24 9:30','23333333','Hector Huerta')Go

11. Cree la función "f_nombreDia" que recibe una fecha (tipo string) y nos retorne el nombre

del día en español.

12. Cree la función "f_horario" que recibe una fecha (tipo string) y nos retorne la hora y

minutos.

13. Muestre todas las consultas del médico llamado 'Alfredo Acosta', incluyendo el día

(emplee la función "f_nombreDia", el horario (emplee la función "f_horario") y el nombre

del paciente.

Disparador (trigger)

Una empresa almacena los datos de sus empleados en una tabla denominada "empleados2" y en

otra tabla llamada "secciones2", el código de la sección y el sueldo máximo de cada una de ellas.

create table secciones2(


nombre varchar(30),

sueldomaximo decimal(8,2),

constraint PK_secciones primary key(codigo)

)

Go

create table empleados2(


nombre varchar(30) not null,


codigoseccion int not null,

sueldo decimal(8,2),

constraint PK_empleados primary key(documento),

constraint FK_empelados_seccion

foreign key (codigoseccion) references secciones(codigo)

)Go

--Ingrese algunos registros en ambas tablas:

insert into secciones2 values('Administracion',1500)Go

insert into secciones2 values('Sistemas',2000)Go

insert into secciones2 values('Secretaria',1000)Go

insert into empleados2 values('22222222','Ana Acosta','Avellaneda 88',1,1100)Go

insert into empleados2 values('23333333','Bernardo Bustos','Bulnes 345',1,1200)Go

insert into empleados2 values('24444444','Carlos Caseres','Colon 674',2,1800)Go

insert into empleados2 values('25555555','Diana Duarte','Colon 873',3,1000)Go

14. Cree un disparador para que se ejecute cada vez que una instrucción "insert" ingrese

datos en "empleados2"Go el mismo debe verificar que el sueldo del empleado no sea

mayor al sueldo máximo establecido para la sección, si lo es, debe mostrar un mensaje

indicando tal situación y deshacer la transacción.

15. Cree un disparador para controlar que no se elimine un empleado si su sueldo es mayor

que 0. El disparador se activará cada vez que se ejecuta un "delete" sobre " empleados2",

si se está eliminando un empleado cuyo sueldo sea mayor a 0, el disparador debe retornar

un mensaje de error y deshacer la transacción.

Una empresa almacena los datos de sus empleados en una tabla denominada "empleados" y los

datos de las distintas sucursales en una tabla "sucursales".

create table sucursales(



constraint PK_sucursales primary key (codigo)

)Go

create table empleados3(


nombre varchar(30),


sucursal int not null,

constraint PK_empleados primary key (documento),

constraint FK_empleados_sucursal foreign key(sucursal)

references sucursales(codigo)

)Go

-- Ingrese algunos registros en las dos tablas:

insert into sucursales values ('Colon 123')Go

insert into sucursales values ('Sucre 234')Go

insert into sucursales values ('Rivadavia 345')Go

insert into empleados3 values ('22222222','Ana Acosta','Avellaneda 1258',1)Go

insert into empleados3 values ('23333333','Betina Bustos','Bulnes 345',2)Go

insert into empleados3 values ('24444444','Carlos Caseres','Caseros 948',3)Go

insert into empleados3 values ('25555555','Fabian Fuentes','Francia 845',1)Go

insert into empleados3 values ('26666666','Gustavo Garcia','Guemes 587',2)Go

insert into empleados3 values ('27777777','Maria Morales','Maipu 643',3)Go

16. Cree un disparador de inserción, eliminación y actualización que no permita

modificaciones en la tabla "empleados" si tales modificaciones afectan a empleados de la

sucursal de 1

ejer cici os transact sq l

Documents