Certamen Construccion de Base de Datos

INSTITUTO PROFESIONAL VIRGINIO GÓMEZ
ÁREA INFORMÁTICA

PAUTA CERTAMEN 1 / CONSTRUCCIÓN DE BD
IECI
(07-05-2014)

Pregunta 1:
Crear la siguiente tabla a través de SQL considerando restricciones con nombre y actualización a nulo cuando
sea necesario. Tanto el tipo de datos como el tamaño debe definirlos adecuadamente. Considere además que
el “Supervisor” solo puede aceptar el valor “Si” o el valor“No”, al igual que “Visitas”, que no puede ser un valor
negativo. El atributo Tipo solo podrá ser: “DES” o “SOP” o “RED”. (15)
Nombre de la tabla: Practicas
Codigo
Matricula (fk que
(pk)
referencia a alumno)
00001
00005

2014101555
2014602344

00010

2014103887

Empresa

Fec_Ini

CMPC Planta Laja

20/12/2013

BIO LECHE

08/08/2013

Salmonera Antártica

15/01/2014

Fec_Fin

Supervisor
Si

VisitasTipo
1
DES

10/10/2013

No

0

SOP

28/02/2014

No

2

RED

Create Table PRACTICAS(Codigo Number(5),
Matricula Number(10) Not Null,
Empresa VarChar2(20) Not Null,
Fec_Ini Date Not Null,
Fec_Fin Date,
Supervisor VarChar2(2) Not Null,
Visitas Number(2) Not Null,
Tipo VarChar2(3) Not Null,
Constraint PkPrac Primary Key(Codigo),
Constraint FkPrac Foreign Key(Matricula)
References Alumno(Matricula),Constraint ChkSupPrac Check(Supervisor in(‘Si’, ‘No’)),
Constraint ChkVisPrac Check(Visitas >= 0),
Constraint ChkTipPrac Check(Tipo in(‘DES’, ‘SOP’, ‘RED’)));

Pregunta 2:
Cree una secuencia llamada Sec_CodPractica, para utilizarlo en el campo Codigo de la tabla anterior. Iniciada
e incrementada de acuerdo a los valores del campo indicado (ver tabla), no cíclico y con un valor Máximo de
80000. (5)Create Sequence Sec_CodPractica
Start with 1
Increment by 5
Nocycle
Maxvalue 80000;

1

INSTITUTO PROFESIONAL VIRGINIO GÓMEZ
ÁREA INFORMÁTICA
Pregunta 3:
Indique la sentencia SQL para insertar los dos primeros registros de la tabla anterior, considerando que se
ejecutarán las dos inserciones al mismo tiempo. Para ello debe hacer uso de la secuencia creada en el punto
anterior. (10)
Insert IntoPracticas
Values(Sec_CodPractica.NextVal,2014101555,‘CMPC Planta Laja’,’20-DIC-2013’,
NULL,‘Si’, 1,‘DES’);
Insert Into Practicas
Values(Sec_CodPractica.NextVal,2014602344,‘BIO LECHE’,’08-AGO-2013’,
’10-OCT-2013’, ‘No’, 0,‘SOP’);

Pregunta 4:
Dada la tabla anterior obtenga, por medio de SQL, los siguientes informes o listados
a) Total de visitas realizadas por empresa. Debe mostrar el nombre de laempresa en una columna
llamada EMP y mostrar el total de visitas en una columna llamada TOTAL_VISITAS. Considere solo las
iniciadas en el año 2014 y que sean de soporte, además solo debe mostrar aquellas que tengan como
total más de 20 visitas. (15)
Select Empresa as EMP, Sum(Visitas) as TOTAL_VISITAS
From Practicas
Where Extract(Year From Fec_Ini) = 2014
And
Tipo = ‘SOP’
Group by Empresa
HavingSum(Visitas)>20
Otra solución:
Select Empresa, Sum(Visitas)
From Practicas
Where Fec_Ini Between ‘01-ENE-2014’ And ‘31-DIC-2014’
And
Tipo = ‘SOP’
Group by Empresa
Having Sum(Visitas)>20

b) Muestre el número total de prácticas realizadas correspondientes solo a Desarrollo. Debe mostrar una
columna llamada TOTAL_DES con el valor correspondiente. Suponga que usted no sabe si los valores
para elcampo “Tipo” fueron ingresados con mayúsculas o minúsculas, por lo tanto debe asegurarse de
que la consulta funcione en todos los casos. (10)
Select Count(*) as TOTAL_DES
From Practicas
Where Upper(Tipo) = ‘DES’

c) Genere un listado que muestre el código de Práctica unido a los primeros 4 números de la matrícula,
separado con el signo ”*”, en una columna llamada PRACTICANTE. En otra columna llamadaEMP
muestre la empresa asegurándose de mostrar el dato en mayúsculas. Considere solo las prácticas sin
fecha de término y que desde la fecha de inicio no hayan pasado más de 3 años. (10)
Select Concat(Concat(Codigo, ‘*’),SubStr(Matricula,1,4)) as PRACTICANTE,
Upper(Empresa) as EMP
From Practicas
Where Fec_Fin Is Null
And
2014 - Extract(Year From Fec_Ini) <= 3
Otra Solución...:
Select...