AP6-AA1-Ev1- LABORATORIO 12 CONSTRUCCIÓN DE MODELOS DE BASE DE DATOS APLICANDO SENTENCIAS SQL
Realizar la práctica expuesta en
el laboratorio No.12 “Construir sentencias SQL para la definición y
manipulación del modelo de base de datos”. Con el desarrollo de este
laboratorio usted podrá aplicar los conceptos vistos para la construcción de la
base de datos propuesta, las tablas y relaciones entre tablas, según la
información suministrada en el laboratorio.
Esta actividad la puede hacer
utilizando el SMBD que prefiera (Oracle o MySQL), el laboratorio suministra un
grupo de video tutoriales que les indican los procedimientos particulares a
seguir en estos dos SMBD. Una vez construida la estructura planteada en el
laboratorio y validada contra el diseño presentado, ingresar los datos
sugeridos y realizar las consultas propuestas.
Las sentencias utilizadas para la
construcción y manipulación de la base de datos deben ser almacenados en un
archivo con extensión .txt.
Ejercicios:
Utilizando instrucciones
SQL, realice las siguientes actividades:
1. Cree
una base de datos llamada laboratorioSQL:
Create database LabotarorioSQL;
2. Ubicado
en la base de datos que acabó de crear, construya las siguientes tablas con los
respectivos campos y tipos de datos.
Create Table profesor (
docprof varchar (11) not null,
nomprof varchar (30) not null,
apeprof varchar (30) not null,
cateprof int not null,
salprof int not null,
primary key
(docprof)
);
Create table curso (
Codcurs int auto_increment,
nomcurs varchar (100) not null,
horascur int not null,
valorcur int not null,
primary key
(Codcurs)
);
Create table estudiante (
docest varchar (11) not null,
nomest varchar (30) not null,
apeest varchar (30) not null,
edadest int not null,
primary key (docest)
);
codcur_estcur int
(11),
docest_estcur varchar (11),
fecini_estcur Date not null,
foreign key (codcur_estcur) references curso(Codcurs),
foreign key (docest_estcur) references
estudiante(docest),
primary key (codcur_estcur,docest_estcur)
);
create table cliente (
idcli varchar (11) not null,
nomcli varchar
(30) not null,
apecli varchar (30) not null,
dircli varchar (100) not null,
depcli varchar (20) not null,
mescumpli varchar (10) not null,
primary key (idcli)
);
create table Articulo (
idart int not null auto_increment,
titart varchar (100) not null,
autart varchar (100) not null,
ediart varchar (300) not null,
precart int not null,
primary key (idart)
);
create table pedido (
idped int not null auto_increment,
idcliped varchar (11) not null,
fecped Date not null,
valped int not null,
primary key (idped),
foreign key (idcliped) references cliente (idcli)
);
create table ArticuloxPedido (
idped_artped int not null,
idart_artped int not null,
canart_artped int not null,
valven_artped int not null,
foreign key (idped_artped) references Articulo
(idart),
primary key
(idped_artped)
);
create table compañia (
comnit varchar (11) not null,
comnombre varchar (30) not null,
comañofun int not null,
comreplegal varchar (100) not null,
primary key (comnit)
);
create table TiposAutomotores (
auttipo int not null,
autnombre varchar
(30),
primary key
(auttipo)
);
create table Automotores (
autoplaca varchar (6) not null,
automarca varchar (30) not null,
autotipo int not null,
automodelo int not null,
autonumpasajeros int not null,
autocilindraje int not null,
autonumchasis varchar (20) not null,
primary key
(autoplaca)
);
create table
Aseguramientos (
asecodigo int
auto_increment,
asefechainicio date not null,
asefechaexpiracion date not null,
asevalorasegurado
int not null,
aseestado varchar (30),
asecosto int not null,
aseplaca varchar (20),
primary key
(asecodigo)
);
create table Incidentes (
incicodigo int not null auto_increment,
incifecha date not null,
inciplaca varchar (6) not null,
incilugar varchar (40) not null,
incicantheridos int not null,
incicanfatalidades int not null,
incicanautosinvolucrados int not null,
primary key
(incicodigo)
);
3. Inserte los
siguientes registros según las tablas que se presentan a continuación:
Utilizando la tecla apostrofe.
Insert
into profesor
(docprof,
nomprof, apeprof, cateprof, salprof)
Values ('63502720','Martha','Rojas',2,690000),('91216904','Carlos','Perez',3,950000),
('13826789','Maritza','Angarita',1,550000),('1098765789','Alejandra','Torres',4,1100000);
Select * from
profesor;
Insert into Curso
(Codcurs, nomcurs, horascur, valorcur) Values
('149842','Fundamentos de Base de
Datos',40,500000),
('250067','Fundamentos de
SQL',20,700000),
('289011','Manejo de Mysql',45,550000),
('345671','Fundamentals
of Oracle',60,3000000);
Select * from curso;
Insert
into Estudiante
(docest, nomest, apeest, edadest) values
('63502720','Maria','Perez',23),
('91245678','Carlos
Jose','Lopez',25),
('1098098097','jonatan','Ardila',17),
('1098765678','Carlos','Martinez',19);
Select * from
estudiante;
Insert into Estudiantexcurso
(codcur_estcur,docest_estcur,
fecini_estcur) values
('289811','1098765678','2011-02-01'),
('250067','63502720','2011-03-01'),
('289011','1098098097','2011-02-01'),
('345671','63502720','2011-04-01');
Select * from Estudiantexcurso;
Insert into Cliente
(idcli,
nomcli,apecli,dircli,depcli,mescumpli) values
('63502718','Maritza','Rojas','calle 34
No 14-45','Santander','Abril'),
('13890234','Roger','Ariza','Carrera 30
No 13-45','Antioquia','Junio'),
('77191956','Juan
Carlos','Arenas','Diagonal 23 No 12-34 Apto 101','Valle','Marzo'),
('1098765789','Catalina','Zapata','Av el
Libertado No 30-14','Cauca','Marzo');
Select * from cliente;
Insert into articulo
(idart,titart,autart,ediart,precart) values
(1,'redes cisco','Ernesto Arigasello','Alfaomega-Rama',60000),
(2,'Facebook y Twitter para adultos','veloso Claudio','Alfaomega',52000),
(3,'Creacion de un portal con php y mysql','Jacobo Pavon Puertas','Alfaomega-Rama',40000),
(4,'Administracion de sistemas operativos','Julio Gomez Lopez','Alfaomega-Rama',55000);
Select * from articulo;
Insert
into pedido
(idped,idcliped,fecped,valped) values
(1,'63502718','2012-02-25',120000),
(2,'77191956','2012-04-30',55000),
(3,'63502718','2011-12-10',260000),
(4,'1098765789','2012-02-25',1800000);
Select * from pedido;
Insert
into ArticuloxPedido
(idped_artped,idart_artped,canart_artped,valven_artped)
values
(1,3,5,'40000'),
(1,4,12,'55000'),
(2,1,5,'65000'),
(3,2,10,'55000'),
(3,3,12,'45000'),
(4,1,20,'65000');
Select * from
ArticuloxPedido;
Insert into compañia
(comnit,comnombre,comañofun,comreplegal)
values
('800890890
2','Seguros Atlantida',1998,'Carlos Lopez'),
('899999999
1','Aseguradora Rojas',1991,'Luis Fernando Rojas'),
('899999999
5','Seguros de Estadio',2001,'Maria Margarita Perez');
Select * from compañia;
Insert into TiposAutomotores(auttipo,autnombre) values
(1,'Automotores'),
(2,'Campero'),
(3,'Camiones');
Select * from
TiposAutomotores;
Insert into Automotores
(autoplaca,automarca,autotipo,automodelo,autonumpasajeros,autocilindraje,autonumchasis)
values
('FLL420','Chevrolet
Corsa',1,2003,5,1400,'wywzzz167kk009d25'),
('DKZ820','Renault
Stepway',1,2008,5,1600,'wywwzz157kk009d45'),
('KJQ920','kia sportage',2,2009,7,2000,'wywzzz157kk009d25');
Select * from Automotores;
Insert into aseguramientos
(asecodigo,asefechainicio,asefechaexpiracion,asevalorasegurado,aseestado,asecosto,aseplaca)
values
(1,'2012-09-30','2013-09-30',30000000,'vigente',500000,'FLL420'),
(2,'2012-09-27','2013-09-27',35000000,'vigente',600000,'DKZ820'),
(3,'2011-09-28','2012-09-28',50000000,'vencido',800000,'KJQ920');
Select * from
aseguramientos;
Insert into Incidentes
(incicodigo,incifecha,inciplaca,incilugar,incicantheridos,incicanfatalidades,incicanautosinvolucrados)
values
(1,'2012-09-30','DKZ820','Bucaramanga',0,0,2),
(2,'2012-09-27','FLL420','Giron',1,0,1),
(3,'2011-09-28','FLL420','Bucaramanga',1,0,2);
Select * from Incidentes;
4. 1.
Realice las siguientes consultas:
ü Muestre los salarios de los profesores ordenados por categoría.
Select * from profesor order by cateprof;
ü Muestre los cursos cuyo valor sea mayor a $500.000.
Select * from curso where valorcur > 500000;
ü Cuente el número de estudiantes cuya edad sea mayor a 22.
Select count(edadest) from estudiante where
edadest > 22;
ü Muestre el nombre y la edad del estudiante más joven.
Select nomest, min(edadest) from estudiante;
ü Calcule el valor promedio de los cursos cuyas horas sean mayores a 40.
Select avg(valorcur) from curso where harascur>40;
ü Obtener el sueldo promedio de los profesores de la categoría 1.
Select avg(salprof) from profesor where cateprof
=1;
ü Muestre todos los campos de la tabla curso en orden ascendente según el
valor.
Select * from curso order by valorcur;
ü Muestre el nombre del profesor con menor sueldo.
Select nomprof from profesor where salprof in(Select
min(salprof) from profesor);
ü
Visualizar todos los estudiantes (código y nombre) que
iniciaron cursos el 01/02/2011, del curso debe mostrarse el nombre, las horas y
el valor.
Select docest, nomest, nomcurs, horascur, valorcur
From estudiante,
curso, estudiantexcurso
Where Codcurs =
codcur_estcur and docest = docest_estcur and fecini_estcur = 20110201;
ü Visualice los profesores cuyo sueldo este entre $500.000 y $700.000.
Select * from profesor where salprof > 500000 and salprof <
700000;
ü Visualizar el nombre, apellido y dirección de todos aquellos clientes que
hayan realizado un pedido el día 25 /02/2012.
Select nomcli, apecli,
dircli from cliente, pedido where fecped = 20120225;
ü Listar todos los pedidos realizados incluyendo el nombre del artículo.
Select idped, idcliped, fecped, titart
From pedido, articuloxpedido, articulo
Where
idart_artped= idart;
ü Visualizar los clientes que cumplen años en marzo.
Select nomcli from cliente where mescumpli = 'Marzo';
ü Visualizar los datos del pedido 1, incluyendo el nombre del cliente, la
dirección del mismo, el nombre y el valor de los artículos que tiene dicho
pedido.
Select idped, valped, dircli, nomcli from pedido, cliente where
idcli=idcliped and idped=1;
ü Visualizar el nombre del cliente, la fecha y el valor del pedido más
costoso.
Select nomcli, fecped, valped from cliente,pedido
Where idcli=idcliped and valped in(Select max(valped) from pedido);
ü Mostrar cuantos artículos se tienen de cada editorial.
Select * from articulo order by autart;
ü Mostrar los pedidos con los respectivos artículos (código, nombre, valor y
cantidad pedida).
Select idped, titart, valped, canart_artped from articulo, pedido,
articuloxpedido
Where idart = idart_artped and idped = idped_artped;
ü Visualizar todos los clientes organizados por apellido.
Select nomcli, apecli from cliente order by apecli;
Select
* from articulo order by autart;
ü Visualizar
los pedidos que se han realizado para el articulo con id 2, el listado debe
mostrar el nombre y dirección del cliente, el respectivo número de pedido y la
cantidad solicitada.
Select nomcli,
dircli, idped, canart_artped
From
cliente,pedido,articuloxpedido
Where
idped_artped=idped and idcliped=idcli and idart_artped = 2;
ü
Visualizar los datos de las empresas fundadas entre el
año 1991 y 1998.
Select * from compañia where
comañofun >=1991 and comañofun <=1998;
ü 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.
Select autoplaca,
automodelo, automarca, autonumpasajeros, autocilindraje, autnombre, asecosto,
asevalorasegurado
From automotores,aseguramientos,tiposautomotores
Where autoplaca =
aseplaca and auttipo = auttipo
And
asefechaexpiracion <= 20130831 and asefechaexpiracion >= 20130801;
ü Visualizar
los datos de los incidentes ocurridos el 30 de septiembre de 2012, con su respectivo
número de póliza, fecha de inicio de la póliza, valor asegurado y valor de la
póliza.
Select inciplaca, incifecha, incilugar, incicodigo,
asefechainicio, asecosto, aseestado, asevalorasegurado
From aseguramientos,incidentes
Where inciplaca = aseplaca and incifecha = 20120930;
ü 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.
Select incicantheridos, autoplaca,
incilugar, incifecha, asefechainicio, aseestado, asecosto, asevalorasegurado
From incidentes, aseguramientos,
automotores
Where inciplaca = aseplaca and autoplaca
= inciplaca and incicantheridos = 1;
ü Visualizar todos los datos de la póliza más costosa.
Select * from
aseguramientos
Where asecosto in(Select
max(asecosto) from aseguramientos);
ü 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.
Select *, max(asecosto)
from aseguramientos;
ü 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.
Select
inciplaca, incifecha, incilugar, incicantheridos, asefechainicio,
asefechaexpiracion, asevalorasegurado
From
incidentes, aseguramientos
Where
inciplaca=aseplaca and inciplaca = 'FLL420';
ü Visualizar los datos de la empresa con nit 899999999-5.
Select * from compañia where comnit = '899999999
5';
ü 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.
Select
* from aseguramientos,automotores
Where
aseplaca = autoplaca and asevalorasegurado in(Select max(asevalorasegurado)
from aseguramientos);
ü Visualizar los datos de las pólizas de los automotores tipo 1, este
reporte debe incluir placa, marca, modelo, cilindraje del vehículo junto con la
fecha de inicio, de finalización y estado de la póliza.
Select autoplaca, automarca, automodelo,
autocilindraje, asefechainicio, asefechaexpiracion, aseestado
From automotores,aseguramientos
Where autoplaca = aseplaca and autotipo =1;
5. Genere el script de la base de datos
en un archivo bloc de notas con extensión txt cuyo nombre debe ser Solución_Laboratorio_SQL.txt.
