Jean Policia

create database policial go use policial go create table entidad ( num_entidad char(10) not null, domicilio char(20) primary key (num_entidad) ) go create table sucursal ( num_sucursal char(10) not null, domicilio char (20)not null, num_empleado char (10), num_enti char (10) primary key (num_sucursal), foreign key (num_enti)references entidad ) go create table vigilante ( num_vigilante char(10) not null, nombre_vig char(15), edad_vig char(2), sueldo char(4), domicilio char(25), telefono char(10) primary key (num_vigilante) ) go create table banda ( num_banda char(5) not null, nom_banda char(15) primary key(num_banda) ) go create table juez ( num_juez char(10) not null, nom_juez char(15), apellido_juez char(15), edad_juez char(2) primary key (num_juez) ) go create table contrata ( num_sucur char (10) not null, num_vigi char(10), fecha char(10), arma char(15) foreign key(num_sucur)references sucursal, foreign key(num_vigi)references vigilante ) go

create table atracador ( num_atracador char (10) not null, nombre_atraca char(15), apellido_atraca char(15), num_band char(5) primary key(num_atracador), foreign key(num_band)references banda ) go create table atraca ( num_sucur char(10), num_atracad char(10), numero_juez char(10), fecha char(10), condena char(10), tiempo char(10) foreign key(num_sucur)references sucursal, foreign key(num_atracad)references atracador, foreign key(numero_juez)references juez ) go insert into entidad values ('001','sauces 5') insert into entidad values ('002','la 24 y la J') insert into entidad values ('003','la Garzota') insert into entidad values ('004','los vergeles') insert into entidad values ('005','samanes') insert insert insert insert insert

into into into into into

sucursal sucursal sucursal sucursal sucursal

values values values values values

('123','la floresta','14','001') ('124','petrillo','25','002') ('132','daule','36','003') ('147','Duran','48','004') ('178','los ceibos','13','005')

insert into vigilante values insert into vigilante values insert into vigilante values insert into vigilante values perimetral','2663706') insert into vigilante values trinitaria','2478960') insert insert insert insert insert

into into into into into

banda banda banda banda banda

insert insert insert insert insert

into into into into into

juez juez juez juez juez

values values values values values values values values values values

('024','Mauricio','33','340','cdla avas','2678467') ('036','Pedro','27','287','guasmo central','2457983') ('044','Richard','30','278','mapasingue','2413604') ('055','Miguel','62','1250','la ('066','Roberto','47','654','la

('E047','Sacapinta') ('A048','Latin key') ('B050','los piratas') ('C052','Aventureros') ('D054','los �etas') ('j011','Armando','Lopez','57') ('j081','Jorge','Velez','63') ('j084','Daniel','Alcivar','43') ('j085','Angel','Zambrano','72') ('j087','Roberto','Coronel','50')

insert into contrata values ('123','024','10-enero','metralladora') insert into contrata values ('124','036','22-febrero','calibre 24') insert into contrata values ('132','044','1-marzo','calibre-16')

insert into contrata values ('147','055','8-abril','calibre-4') insert into contrata values ('178','066','6-junio','metra') insert insert insert insert insert

into into into into into

atracador atracador atracador atracador atracador

insert insert insert insert insert

into into into into into

atraca atraca atraca atraca atraca

values values values values values

values values values values values

('L202','fabian','Sanchez','E047') ('L204','jhon','Ramirez','A048') ('L208','alejandro','Cardenas','B050') ('L210','fernando','Barahona','C052') ('L215','alexis','Mercado','D054')

('123','L202','j011','1-julio','violacion','3-a�os') ('124','L204','j081','3-agost','robo','1-a�o') ('132','L208','j084','7-sept','secuestro','4-a�os') ('147','L210','j085','5-Octub','asesinato','6-a�os') ('178','L215','j087','3-nov','rob_viol','10-a�os')

select v.nombre_vig, s.num_sucursal,v.sueldo from vigilante v,contrata c,sucursal s where v.num_vigilante=num_vigi and c.num_sucur=s.num_sucursal select a.nombre_atraca,r.num_sucur,s.domicilio,j.nom_juez,r.tiempo,b.nom_banda from atracador a,atraca r,sucursal s, juez j,banda b where a.num_atracador=r.num_atracad and r.numero_juez=j.num_juez and b.num_banda=a.num_band select b.nom_banda,a.nombre_atraca from banda b,atracador a where a.num_band=b.num_banda select a.nombre_atraca from atracador a where not exists (select * from banda b where b.num_banda = a.num_band and b.nom_banda like 'Latin key' )