Juan Guillermo Forero Neme

LABORATORIO 12
Creacion de WorkSpace laboratorioSQL
WORKSPACE: LABORATORIOSQL
PASWORD: 12345
Creacion de tablas
1 - Profesor
Create table PROFESOR
(doc_prof varchar(11) not null,
nom_prof varchar(30) not null,
ape_prof varchar(30) not null,
cate_prof int,
sal_prof int,
primary key (doc_prof))
2

Curso

Create table CURSO

(cod_curs int not null,
nom_curs varchar(100) not null,
horas_cur int,
valor_cur int,
primary key (cod_curs))
3

Estudiante

Create table ESTUDIANTE

(doc_est varchar(11) not null,
nom_est varchar(30) not null,
ape_est varchar(30) not null,
edad_est int,
primary key (doc_est))
4

Estudiante por curso

Create table ESTUDIANTEXCURSO

(cod_curso_estcur int not null,
doc_est_estcur varchar(11) not null,
fec_ini_estcur date)
Claves Foraneas
alter table ESTUDIANTEXCURSO
add constraint fkdoc_est
foreign key(doc_est_estcur) references estudiante(doc_est)

alter table ESTUDIANTEXCURSO

add constraint fkcod_est
foreign key(COD_CUR_ESTCUR) references curso(COD_CURS)
Cargue de datos

INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO

PROFESOR
PROFESOR
PROFESOR
PROFESOR

VALUES
VALUES
VALUES
VALUES

INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO

CURSO
CURSO
CURSO
CURSO

INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO

ESTUDIANTE
ESTUDIANTE
ESTUDIANTE
ESTUDIANTE

INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO

Estudiantexcurso
Estudiantexcurso
Estudiantexcurso
Estudiantexcurso

VALUES
VALUES
VALUES
VALUES

([Link], 13.826.789, 91.216.904, 63.502.720)

('63502720','MARTHA','ROJAS', 2, 690000)
('91216904','CARLOS','PEREZ',3,950000)
('13826789','MARITZA','ANGARITA',1,550000)

('149842','FANDAMENTOS DE BASES DE DATOS', 40, 500000)

('250067','FUNDAMENTOS DE SQL', 20, 700000)
('289011','FUNDAMENTOS DE Mysql', 45, 550000)
('345671','FUNDAMENTALS OF ORACLE', 60, 3000000)

VALUES
VALUES
VALUES
VALUES

('63502720','MARIA','PEREZ', 23)
('91245678','CARLOS JOSE','LOPEZ',25)
('1098098097','JONATAN','ARDILA',17)
('1098765678','CARLOS','MARTINEZ,19)
VALUES (289011,1098765678,'01/02/2011')
VALUES(250067,63502720,'01/03/2011')
VALUES(289011,1098098097,'01/02/2011')
VALUES(345671,63502720,'01/04/2011')

Consultas
Muestre los salarios de los profesores ordenados por categora.
Select CATE_PROF from PROFESOR order by (CATE_PROF) asc
Muestre los cursos cuyo valor sea mayor a $500.000.
Select NOM_CURS from CURSO where VALOR_CUR > 500000
Cuente el nmero de estudiantes cuya edad sea mayor a 22.
Select count(*) from estudiante where EDAD_EST > 22
Muestre el nombre y la edad del estudiante ms joven.
select NOM_EST || ' ' || APE_EST as Nombres , EDAD_EST as edad
from ESTUDIANTE
where EDAD_EST = (select min(EDAD_EST) from ESTUDIANTE)
Calcule el valor promedio de los cursos cuyas horas sean mayores a 40.
Select avg(HORAS_CUR) from curso where HORAS_CUR > 40
Obtener el sueldo promedio de los profesores de la categora 1.
Select avg(SAL_PROF) from PROFESOR where CATE_PROF = 1
Muestre todos los campos de la tabla curso en orden ascendente segn el valor.
Select * from CURSO order by (VALOR_CUR) asc
Muestre el nombre del profesor con menor sueldo.
select nom_prof || ' ' || ape_prof as Nombres
from profesor
where sal_prof = (select min(sal_prof) from profesor)
Visualizar todos los estudiantes (cdigo y nombre) que iniciaron cursos el 01/02/2

011, del curso debe mostrarse el nombre, las horas y el valor.

Select NOM_EST,APE_EST, FEC_INI_ESTCUR, COD_CURS, NOM_CURS, HORAS_CUR, VALOR_CUR
from (estudiante inner join estudiantexcurso on estudiante.DOC_EST = estudiante
xcurso.DOC_EST_ESTCUR)
inner join curso on estudiantexcurso.COD_CUR_ESTCUR = curso.COD_CURS
where FEC_INI_ESTCUR = '01/02/2011'
Visualice los profesores cuyo sueldo este entre $500.000 y $700.000.
Select NOM_PROF, APE_PROF, SAL_PROF
from profesor
where sal_prof > 500000
and sal_prof < 700000
Tabla 2
Cliente
Create table CLIENTE
(Id_cli varchar(11) not null,
Nom_cli varchar(30) not null,
Ape_cli varchar(30) not null,
Dir_cli varchar(100) not null,
Dep_cli varchar(20) not null,
Mes_cum_cli varchar(10) not null,
Primary key (Id_cli))
ARTICULO

Create table ARTICULO

(ID_ART Int,
Tit_art varchar(100),
aut_art varchar(100),
edi_art varchar(300),
prec_art int,
primary key (id_art))
PEDIDO
create table PEDIDO
(id_ped int,
id_cli_ped varchar (11),
fec_ped date,
val_ped int,
primary key(id_ped))
CLAVE FORANEA
alter table PEDIDO
add constraint FKID_CLI
foreign key(ID_CLI_PED) references CLIENTE(ID_CLI)
Articulo Por Pedido

create table articuloxpedido

(Id_ped_artped int,
id_art_artped int,
can_art_artped int,
val_ven_art_artped int)
LLAVES FORANEAS
alter table articuloxpedido
add constraint fkped_artped
foreign key (id_ped_artped) references pedido(id_ped)
alter table articuloxpedido
add constraint fkart_artped
foreign key (id_art_artped) references articulo(id_art)
Cargue de datos
INSERT INTO CLIENTE VALUES ('63502718','MARITZA', ROJAS , 'CALLE 34 14 45', 'SANTAN
DER', 'ABIL')
INSERT INTO CLIENTE VALUES ('13890234','ROGER', 'ARIZA', 'CARRERA 30 13 45', 'AN
TIOQUIA','JUNIO )
INSERT INTO CLIENTE VALUES ('77191956','JUAN CARLOS','ARENAS', 'DIAGONAL 23 12 3
4 APARTAMENTO 101','VALLE','MARZ0')
INSERT INTO CLIENTE VALUES ('1098765789','CATALINA','ZAPATA','AVENIDA EL LIBERT
ADOR 30 14', 'CAUCA','MARZO')
INSERT INTO ARTICULO VALUES (1,'REDES CISCO', 'ERNESTO ARIGASELLO','ALFAOMEGA-RA
MA',60000)
INSERT INTO ARTICULO VALUES (2,'FACEBOOK Y TWITTER PARA ADULTOS','VELOSO CLAUDIO
','ALFAOMEGA',52000)
INSERT INTO ARTICULO VALUES (3,'CREACION DE UN PORTAL CON PHP Y MySQL', 'JACOBO
PAVON PUERTA', 'ALFAOMEGA-RAMA', 40000)
INSERT INTO ARTICULO VALUES (4,'ADMINISTRACION DE SISTEMAS OPERATIVOS','JULIO GO
MEZ LOPEZ', 'ALFAOMEGA-RAMA',55000)
INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO

PEDIDO
PEDIDO
PEDIDO
PEDIDO

VALUES
VALUES
VALUES
VALUES

(1,'63502718','02/25/2012',120000)
(2,'77191956','04/30/2012',55000)
(3,'63502718','12/10/2011',260000)
(4,'1098765789','02/25/2012',1800000)

INSERT
INSERT
INSERT
INSERT
INSERT
INSERT

INTO
INTO
INTO
INTO
INTO
INTO

ARTICULOXPEDIDO
ARTICULOXPEDIDO
ARTICULOXPEDIDO
ARTICULOXPEDIDO
ARTICULOXPEDIDO
ARTICULOXPEDIDO

VALUES
VALUES
VALUES
VALUES
VALUES
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)

Visualizar el nombre, apellido y direccin de todos aquellos clientes que hayan re

alizado un pedido el da 25 /02/2012.
select NOM_CLI, APE_CLI, DIR_CLI
from cliente inner join pedido on cliente.id_cli = pedido.id_cli_ped
where fec_ped = '02/25/2012'
Listar todos los pedidos realizados incluyendo el nombre del artculo.
select ID_PED, ID_CLI_PED, FEC_PED, TIT_ART
from (pedido inner join articuloxpedido on pedido.Id_ped = articuloxpedido.id_pe
d_artped)

inner join articulo on articuloxpedido.id_art_artped = articulo.id_art

Visualizar los clientes que cumplen aos en marzo.
Select *
from cliente
where MES_CUM_CLI = 'MARZO'
Visualizar los datos del pedido 1, incluyendo el nombre del cliente, la direccin
del mismo, el nombre y el valor de los artculos que tiene dicho pedido.
Select Nom_cli, ape_cli,ID_PED, ID_CLI_PED, FEC_PED, VAL_PED, dir_cli
from (cliente inner join pedido on cliente.id_cli = pedido.id_cli_ped)
inner join articuloxpedido on pedido.id_ped = articuloxpedido.id_ped_artped
where id_ped = 1
Visualizar el nombre del cliente, la fecha y el valor del pedido ms costoso.
Select NOM_CLI || ' ' || APE_CLI as "Cliente", Fec_Ped as "Fecha Pedido", Val_Pe
d as "Valor"
from (cliente inner join pedido on cliente.Id_cli = pedido.Id_cli_ped)
where val_ped = (select max(val_ped) from pedido)
Mostrar cuantos artculos se tienen de cada editorial.
Select edi_art, count(EDI_ART)
from Articulo
group by edi_art

Mostrar los pedidos con los respectivos artculos (cdigo, nombre, valor y cantidad
pedida).
Select id_ped, id_cli_ped, fec_ped, val_ped, id_art, tit_art, aut_art, edi_art,
prec_art
from (articulo inner join articuloxpedido on articulo.id_art = articuloxpedido.i
d_art_artped)
inner join pedido on articuloxpedido.id_art_artped = pedido.id_ped
Visualizar todos los clientes organizados por apellido.
Select nom_cli, ape_cli
from cliente
order by (ape_cli)asc
Visualizar todos los artculos organizados por autor.
Select *
from articulo
order by (aut_art)asc
Visualizar los pedidos que se han realizado para el articulo con id 2, el listad
o debe mostrar el nombre y direccin del cliente, el respectivo nmero de pedido y l
a cantidad solicitada
Select ID_PED, Nom_cli, ape_cli, dir_cli, CAN_ART_ARTPED
from (Cliente inner join pedido on cliente.id_cli = pedido.id_cli_ped)
inner join articuloxpedido on pedido.id_ped = articuloxpedido.id_ped_artped
where Id_ped = 2

Base de Datos 3
Compaia

create table compaia

(comnit varchar(11),
comnombre varchar(30),
comaofun int,
comreplegal varchar(100),
primary key (comnit))
Tipo automotores
create table tiposautomotores
(auttipo int,
Autnombre varchar(30)
primary key (auttipo))
automotores

create table automotores

(autoplaca varchar(6),
automarca varchar(30),
Autotipo int,
automodelo int,
autonumpasajeros int,
autocilindraje int,
autonumchasis varchar(20),
primary key (autoplaca))
llave fornea
alter table automotores
add constraint fkautotipo
foreign key (Autotipo) references TiposAutomotores(auttipo)
Aseguramiento
create table aseguramientos
(asecodigo int,
Asefechainicio date,
asefechaexpiracion Date,
asevalorasegurado int,
Aseestado varchar(30),
Asecosto int,
Aseplaca varchar(20),
primary key(asecodigo))
llave fornea
Alter table aseguramientos
add constraint fkaseplaca
foreign key (Aseplaca) references automotores(autoplaca)
Incidente

Create table incidente

(incicodigo int,
incifecha date,
inciplaca varchar(6),
incilugar varchar(40),
incicantheridos int,
incicanfatalidades int,
incicanautosinvolucrados int,
primary key(incicodigo))
llave fornea
alter table incidente
add constraint fkinciplaca
foreign key (inciplaca) references automotores(autoplaca)
CARGUE DE DATOS
INSERT INTO COMPAIA VALUES ('800890890-2','SEGUROS ATLANTIDA','1998','CARLOS LOPE
Z')
INSERT INTO COMPAIA VALUES ('899999999-1','ASEGURADORA ROJAS','1991','LUIS FERNAN
DO ROJAS')
INSERT INTO COMPAIA VALUES ('899999999-5','SEGUROS DEL ESTADO','2001','MARIA ARGA
RITA PEREZ')
INSERT INTO TIPOSAUTOMOTORES VALUES (1,'AUTOMOVILES')
INSERT INTO TIPOSAUTOMOTORES VALUES (2,'CAMPEROS')
INSERT INTO TIPOSAUTOMOTORES VALUES (3,'CAMIONES')
INSERT INTO AUTOMOTORES VALUES ('FLL420','CHEVROLET CORSA',1,'2003',5,1400,'wywz
zz167kk009d25')
INSERT INTO AUTOMOTORES VALUES ('DKZ820','RENAULT STEPWAY',1,'2008',5,1600,'wyww
zz157kk009d45')
INSERT INTO AUTOMOTORES VALUES ('KJQ920','KIA SPORTAGE',2,'2009',7,2000,'wywzzz1
57kk009d25')
INSERT INTO ASEGURAMIENTOS VALUES (1,'09/30/2012','09/30/2013',30000000,'VIGENTE
',500000,'FLL420')
INSERT INTO ASEGURAMIENTOS VALUES (2,'09/27/2012','09/27/2013',35000000,'VIGENTE
',600000,'DKZ820')
INSERT INTO ASEGURAMIENTOS VALUES (3,'09/28/2011','09/28/2012',50000000,'VENCIDO
',800000,'KJQ920')
INSERT INTO INCIDENTE VALUES (1,'09/30/2012','DKZ820','BUCARAMANGA',0,0,2)
INSERT INTO INCIDENTE VALUES (2,'09/27/2012','FLL420','GIRON',1,0,2)
INSERT INTO INCIDENTE VALUES (3,'09/28/2011','FLL420','BUCARAMANGA',1,0,2)
Consultas
Visualizar los datos de las empresas fundadas entre el ao 1991 y 1998.
select *
from compaia
where COMAOFUN >= '1991'
and COMAOFUN <= '1998'
Listar los todos datos de los automotores cuya pliza expira en octubre de 2013, e
ste reporte debe visualizar la placa, el modelo, la marca, nmero de pasajeros, ci

lindraje nombre de automotor, el valor de la pliza y el valor asegurado.

Ningn vehculo le vence la poliza en octubre de 2013
Select autoplaca, automodelo, automarca, autonumpasajeros, autocilindraje, aseco
sto, asevalorasegurado, autnombre
from (aseguramientos inner join automotores on [Link] = automot
[Link])
inner join tiposautomotores on [Link] = [Link]
where ASEFECHAEXPIRACION >='10/31/2013'
Se cambia la fecha de vencimiento a septiembre de 2013
select autoplaca, automodelo, automarca, autonumpasajeros, autocilindraje, aseco
sto, asevalorasegurado, autnombre
from (aseguramientos inner join automotores on [Link] = automot
[Link])
inner join tiposautomotores on [Link] = [Link]
where ASEFECHAEXPIRACION >='09/30/2013'
Visualizar los datos de los incidentes ocurridos el 30 de septiembre de 2012, co
n su respectivo nmero de pliza, fecha de inicio de la pliza, valor asegurado y valo
r de la pliza.
Select asecodigo, asefechainicio, asevalorasegurado, asecosto
from (incidente inner join aseguramientos on [Link] = aseguramiento
[Link])
where incifecha = '09/30/2012'
Visualizar los datos de los incidentes que han tenido un (1) herido, este report
e debe visualizar la placa del automotor, con los respectivos datos de la pliza c
omo son fecha de inicio, valor, estado y valor asegurado.
select inciplaca, asefechainicio, asecosto, aseestado, asevalorasegurado
from (incidente inner join aseguramientos on [Link] = aseguramiento
[Link])
where INCICANTHERIDOS = 1
Visualizar todos los datos de la pliza ms costosa.
select *
from aseguramientos
where asecosto = (select max(asecosto) from aseguramientos)

Visualizar los incidentes con el mnimo nmero de autos involucrados, de este incid
nte visualizar el estado de la pliza y el valor asegurado.
Select INCICANAUTOSINVOLUCRADOS, ASEESTADO, ASEVALORASEGURADO
From (incidente inner join aseguramientos on [Link] = aseguramiento
[Link])
where INCICANAUTOSINVOLUCRADOS = (select min(INCICANAUTOSINVOLUCRADOS) from inci
dente)
Visualizar los incidentes del vehculo con placas " FLL420", este reporte debe vis
ualizar la fecha, el lugar, la cantidad de heridos del incidente, la fecha de in
icio la de expiracin de la pliza y el valor asegurado.
Select INCIFECHA, INCILUGAR, INCICANTHERIDOS, asefechaexpiracion+1, asevaloraseg
urado

from (incidente inner join aseguramientos on [Link] = aseguramiento

[Link])
where inciplaca = 'FLL420'
Visualizar los datos de la empresa con nit 899999999-5.
select *
from compaia
where COMNIT = '899999999-5'

Visualizar los datos de la pliza cuyo valor asegurado es el ms costoso, este repo
te adems de visualizar todos los datos de la pliza, debe presentar todos los datos
del vehculo que tiene dicha pliza.
select ASECODIGO, ASEFECHAINICIO, ASEFECHAEXPIRACION, ASEVALORASEGURADO, ASEESTA
DO, ASECOSTO, AUTOPLACA, AUTOMARCA, AUTOTIPO, AUTOMODELO, AUTONUMPASAJEROS, AUTO
CILINDRAJE, AUTONUMCHASIS
from (aseguramientos inner join automotores on [Link] = automot
[Link])
where asecosto = (select max(asecosto) from aseguramientos)
Visualizar los datos de las plizas de los automotores tipo 1, este reporte debe i
ncluir placa, marca, modelo, cilindraje del vehculo junto con la fecha de inicio,
de finalizacin y estado de la pliza..
Select AUTOPLACA, AUTOMARCA, AUTOMODELO, AUTOCILINDRAJE, ASEFECHAINICIO, ASEFECH
AEXPIRACION, ASEESTADO
from ( automotores inner join aseguramientos on [Link] = aseguram
[Link])
where AUTOTIPO = 1