Last active
December 14, 2022 05:03
-
-
Save adriasir123/0896abce2b81714557d3c1e2a038c3cb to your computer and use it in GitHub Desktop.
Creación de tablas y carga de datos del proyecto "Gran Hipódromo" para Oracle
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table caballosCarreras | |
( | |
codigoCaballo number(3), | |
peso number(6,2), | |
nacionalidad varchar2(30), | |
constraint pk_caballosCarreras primary key(codigoCaballo) | |
); | |
create table instalacionesCuadras | |
( | |
codigoCuadra number(3), | |
aforo number(5), | |
tam number(7), | |
tipo varchar2(100), | |
constraint pk_instalacionesCuadras primary key(codigoCuadra) | |
); | |
create table pistasEntrenamiento | |
( | |
codigoCuadra number(3), | |
superficie varchar2(15), | |
longitud number(4), | |
constraint pk_pistas primary key(codigoCuadra), | |
constraint superficieOK check (lower(superficie) in ('hierba', 'arena batida', 'tierra')) | |
); | |
create table propietarios | |
( | |
dni varchar2(10), | |
nombre varchar2(15), | |
apellido1 varchar2(15), | |
apellido2 varchar2(15), | |
cuotaMensual number(6,2), | |
constraint pk_propietarios primary key(dni), | |
constraint dniPropietarioOK check(regexp_like(lower(dni), '[0-9]{8}[a-z]{1}$') or regexp_like(lower(dni), '[k,l,m,x,y,z]{1}[0-9]{7}[a-z]{1}$')) | |
); | |
create table caballos | |
( | |
codigoCaballo number(3), | |
dniPropietario varchar2(10), | |
nombre varchar2(30), | |
fechaNac date, | |
raza varchar2(30), | |
constraint pk_caballos primary key(codigoCaballo), | |
constraint fk_propietarios foreign key(dniPropietario) references propietarios(dni) | |
); | |
create table carrerasProfesionales | |
( | |
codigoCarrera number(3), | |
fechaHora date, | |
importePremio number(7,2), | |
limiteApuesta number(5,2), | |
fechaNacMin date, | |
fechaNacMac date, | |
constraint pk_carrerasProf primary key(codigoCarrera), | |
constraint ck_carrerasProf unique(fechaHora), | |
constraint fechaOK check(to_char(fechaHora, 'MM') in ('11', '12', '01', '02') or (to_char(fechaHora, 'MM') = '10' and to_char(fechaHora, 'DD')>= '20') or ( to_char(fechaHora, 'MM') = '03' and to_char(fechaHora, 'DD')<= '02')), | |
constraint horaOK check(to_number(to_char(fechaHora, 'HH24')) >= 09 and to_number(to_char(fechaHora, 'HH24')) <= 14), | |
constraint domingoOk check( lower(to_char(fechaHora, 'DAY', 'NLS_DATE_LANGUAGE=SPANISH')) like '%domingo%') | |
); | |
create table jockeys | |
( | |
dni varchar2(10), | |
nombre varchar2(30), | |
peso number(4,2), | |
altura number(3,2), | |
telefono varchar2(9), | |
constraint pk_jockeys primary key(dni), | |
constraint dniJockeyOk check(regexp_like(lower(dni), '[0-9]{8}[a-z]{1}$') or regexp_like(lower(dni), '[k,l,m,x,y,z]{1}[0-9]{7}[a-z]{1}$')) | |
); | |
create table apuestas | |
( | |
dniCliente varchar2(10), | |
codigoCarrera number(3), | |
codigoCaballo number(3), | |
importeApostado number(6,2), | |
tantoAUno number(5,2), | |
constraint pk_apuestas primary key(dniCliente, codigoCarrera, codigoCaballo), | |
constraint maxApuestaOK check(importeApostado < 9000.00), | |
constraint importeApuestaOk check (importeApostado is not null), | |
constraint tantoOk check (tantoAUno is not null) | |
); | |
create table cuadrante | |
( | |
codigoCaballo number(3), | |
codigoCuadra number(3), | |
fechaHora date, | |
constraint pk_cuadrante primary key(codigoCaballo, codigoCuadra), | |
constraint fk_instalacionesCuadras foreign key(codigoCuadra) references instalacionesCuadras(codigoCuadra), | |
constraint notLunesOk check( lower(to_char(fechaHora, 'DAY', 'NLS_DATE_LANGUAGE=SPANISH')) not like '%lunes%') | |
); | |
create table participaciones | |
( | |
codigoCarrera number(3), | |
codigoCaballo number(3), | |
dniJockey varchar2(10), | |
dorsal number(2), | |
posicionFinal number(2), | |
constraint pk_participaciones primary key(codigoCarrera, codigoCaballo), | |
constraint fk_jockey foreign key(dniJockey) references jockeys(dni) | |
); | |
create table clientes | |
( | |
dni varchar2(10), | |
nombre varchar2(30), | |
apellido1 varchar2(30), | |
apellido2 varchar2(30), | |
direccion varchar2(60), | |
localidad varchar2(30), | |
provincia varchar2(15), | |
telefono varchar2(9), | |
constraint pk_clientes primary key(dni), | |
constraint localidadOK check (localidad = initcap(localidad)), | |
constraint dniClienteOk check(regexp_like(lower(dni), '[0-9]{8}[a-z]{1}$') or regexp_like(lower(dni), '[k,l,m,x,y,z]{1}[0-9]{7}[a-z]{1}$')) | |
); | |
insert into pistasEntrenamiento values (1, 'hierba', 3000); | |
insert into pistasEntrenamiento values (2, 'arena batida', 1886); | |
insert into pistasEntrenamiento values (3, 'tierra', 1609); | |
insert into pistasEntrenamiento values (4, 'hierba', 1400); | |
insert into propietarios values ('21913124n', 'Horacio','Arreguy', 'Fazzio', 250); | |
insert into propietarios values ('Z7782152S', 'Robert', 'Chase', ' ',300); | |
insert into propietarios values ('83069279H', 'Lucas', 'Martinez', 'Munoz', 200.70); | |
insert into propietarios values ('X58056225B', 'Gustavo', 'Scarpello', ' ',100); | |
insert into instalacionesCuadras values(1, 1000, 72232, 'galope'); | |
insert into instalacionesCuadras values(2, 500, 32935, 'galope'); | |
insert into instalacionesCuadras values(3, 600, 34816, 'trote'); | |
insert into instalacionesCuadras values(4, 740, 56619, 'obstaculos steeple chase'); | |
insert into instalacionesCuadras values(5, 90, 30500, 'boxes'); | |
insert into instalacionesCuadras values(6, 5, 4000, 'centro veterinario'); | |
insert into instalacionesCuadras values(7, 10, 3000, 'duchas de caballos'); | |
insert into instalacionesCuadras values(8, 7, 500, 'almacen grano'); | |
insert into caballosCarreras values (1, 1490.5, 'Americana'); | |
insert into caballosCarreras values (2, 1500, 'Espanola'); | |
insert into caballosCarreras values (3, 1440,'Arabe'); | |
insert into caballosCarreras values (4, 1600, 'Espanola'); | |
insert into caballosCarreras values (5, 1560.6, 'Britanica'); | |
insert into caballosCarreras values (6, 1450,'Arabe'); | |
insert into caballosCarreras values (7, 1550, 'Espanola'); | |
insert into caballosCarreras values (8, 1500, 'Americana'); | |
insert into caballosCarreras values (9, 1490, 'Espanola'); | |
insert into caballosCarreras values (10, 1570.9, 'Arabe'); | |
insert into clientes values ('28841115N', 'Maria', 'Romero', 'Angulo', 'Calle Paris, 18, 1D', 'Dos Hermanas', 'Sevilla', '606088324'); | |
insert into clientes values ('Z4128090D', 'John', 'Smith', ' ' , 'Avenida de La paz, 71', 'Sevilla', 'Sevilla','954125995'); | |
insert into clientes values ('41500351W', 'Jose Luis', 'Torres', 'Andrades', 'Calle Turin, 7', 'Dos Hermanas', 'Sevilla','955126745'); | |
insert into clientes values ('X5339679E', 'Desa', 'Connif',' ', 'Calle Fernan Caballero, 47', 'Huevar Del Aljarafe', 'Sevilla','955341267'); | |
insert into clientes values ('18498310P', 'Mayumi', 'Ozaki',' ' , 'Calle Argentina', 'Las Rozas De Madrid', 'Madrid','917573498'); | |
insert into clientes values ('02411561B', 'Paco', 'Jover', 'Cobos', 'Calle Real, 12', 'Cordoba', 'Cordoba','678123467'); | |
insert into caballos values (1,'21913124n', 'Wad Vison', to_date('11-11-2011', 'DD-MM-YYYY'), 'Quarter Horse' ); | |
insert into caballos values (2,'Z7782152S', 'Dagoberto', to_date('10-10-2010', 'DD-MM-YYYY'), 'Purasangre espanol' ); | |
insert into caballos values (3,'83069279H', 'Atreus', to_date('24-12-2011', 'DD-MM-YYYY'), 'Shagya Arabe' ); | |
insert into caballos values (4,'X58056225B', 'Mayo', to_date('15-03-2010', 'DD-MM-YYYY'), 'Purasangre espanol' ); | |
insert into caballos values (5,'21913124n', 'Argos', to_date('04-04-2012', 'DD-MM-YYYY'), 'Purasangre ingles' ); | |
insert into caballos values (6,'Z7782152S', 'Daniela', to_date('06-06-2011', 'DD-MM-YYYY'), 'Shagya Arabe' ); | |
insert into caballos values (7,'83069279H', 'Kayak', to_date('14-02-2010', 'DD-MM-YYYY'), 'Purasangre espanol' ); | |
insert into caballos values (8,'X58056225B', 'Charming Star', to_date('23-09-2011', 'DD-MM-YYYY'), 'Quarter Horse' ); | |
insert into caballos values (9,'21913124n', 'Perdigon', to_date('24-06-2012', 'DD-MM-YYYY'), 'Purasangre espanol' ); | |
insert into caballos values (10,'Z7782152S', 'Innuendo', to_date('31-01-2011', 'DD-MM-YYYY'), 'Darley Arabian' ); | |
insert into jockeys values('00015258D', 'B.Fayos', 58.5, 1.60, '600031480'); | |
insert into jockeys values('55466243H', 'R.Ramos', 58.0, 1.65, '605309531'); | |
insert into jockeys values('09849927Q', 'R.Sousa', 57.0, 1.63, '606088322'); | |
insert into jockeys values('X6785562X' ,'M.Gomes', 56.5, 1.66, '670894325'); | |
insert into jockeys values('Y6857984L', 'N. de Julian', 58.0, 1.65, '652978612'); | |
insert into jockeys values('35647376K' ,'C.A.Loaiza', 55.0, 1.69, '607239176'); | |
insert into jockeys values('X6951336T' ,'D.Ferreira', 58.0, 1.68, '658902365'); | |
insert into jockeys values('85108890N' ,'J.Gelabert', 55.5, 1.58, '623459158'); | |
insert into carrerasProfesionales values (1, to_date('08-01-2017 09:30', 'DD-MM-YYYY HH24:MI'), 8750, 650, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (2, to_date('08-01-2017 10:00', 'DD-MM-YYYY HH24:MI'), 11156, 800, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (3, to_date('15-01-2017 11:00' , 'DD-MM-YYYY HH24:MI'), 6125, 450, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (4, to_date('15-01-2017 12:00' , 'DD-MM-YYYY HH24:MI'), 7857, 520, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (5, to_date('22-01-2017 13:00' , 'DD-MM-YYYY HH24:MI'), 7000, 470, to_date('01-06-2009', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (6, to_date('22-01-2017 14:00' , 'DD-MM-YYYY HH24:MI') , 6125, 450, to_date('01-06-2009', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (7, to_date('06-11-2016 11:00' , 'DD-MM-YYYY HH24:MI'), 8750, 650, to_date('01-06-2009', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (8, to_date('06-11-2016 12:00' , 'DD-MM-YYYY HH24:MI'),11167, 800, to_date('01-06-2009', 'DD-MM-YYYY'), to_date('31-12-2012', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (9, to_date('06-11-2016 13:00', 'DD-MM-YYYY HH24:MI'), 7000, 470, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('01-06-2013', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (10, to_date('13-11-2016 10:00' , 'DD-MM-YYYY HH24:MI'), 8750, 650, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('01-06-2013', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (11, to_date('13-11-2016 10:30' , 'DD-MM-YYYY HH24:MI'), 7000, 470, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('01-06-2013', 'DD-MM-YYYY')); | |
insert into carrerasProfesionales values (12, to_date('13-11-2016 12:00' , 'DD-MM-YYYY HH24:MI'), 6125, 450, to_date('01-01-2010', 'DD-MM-YYYY'), to_date('01-06-2013', 'DD-MM-YYYY')); | |
insert into apuestas values ('28441115n',12 ,2 ,300 ,3.10); | |
insert into apuestas values ('41500351W',2 ,4 ,700 ,5.10); | |
insert into apuestas values ('18498310P', 7, 5 ,20 ,1.8 ); | |
insert into apuestas values ('X5339679E',5 , 2, 450,1.6); | |
insert into apuestas values ('Z4128090D', 6,1 ,400 ,10.30); | |
insert into apuestas values ('41500351W', 12,7 ,345 ,103.02 ); | |
insert into apuestas values ('28441115n', 4, 4,50 ,0.00 ); | |
insert into apuestas values ('28441115n', 8,8 ,600 ,6.9); | |
insert into apuestas values ('18498310P', 9, 3, 100, 11.4 ); | |
insert into apuestas values ('Z4128090D', 10, 9, 90, 25.2 ); | |
insert into apuestas values ('41500351W', 11, 1, 235.55,9.7 ); | |
insert into apuestas values ('X5339679E', 3, 4,123.6 ,15.7 ); | |
insert into apuestas values ('28441115n', 11, 10, 334, 8.7 ); | |
insert into apuestas values ('02411561B', 2, 3, 730,16.7 ); | |
insert into apuestas values ('18498310P', 11, 10, 400, 6.4 ); | |
insert into apuestas values ('02411561B', 1,5 ,250 ,3.4 ); | |
insert into apuestas values ('X5339679E', 1, 6,280 ,6.8 ); | |
insert into apuestas values ('02411561B', 5,7 ,290 ,21.7 ); | |
insert into apuestas values ('41500351W', 3, 1, 125, 27.2 ); | |
insert into apuestas values ('02411561B', 12, 8, 275, 43.5 ); | |
insert into apuestas values ('Z4128090D', 8,9 ,650 ,3.5 ); | |
insert into cuadrante values(1,1, to_date('16-02-2017 16:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(2,2, to_date('16-02-2017 17:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(3,3, to_date('16-02-2017 16:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(4,4, to_date('16-02-2017 17:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(5,5, to_date('16-02-2017 19:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(6,6, to_date('16-02-2017 12:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(7,7, to_date('21-02-2017 18:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(8,8, to_date('14-02-2017 15:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(9,8, to_date('17-01-2017 16:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(10,7, to_date('16-02-2017 17:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(1,6, to_date('17-01-2017 16:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(2,5, to_date('17-01-2017 17:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(2,4, to_date('16-02-2017 19:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(3,2, to_date('16-02-2017 12:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(4,2, to_date('21-02-2017 18:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(4,3, to_date('14-02-2017 15:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(5,7, to_date('07-02-2017 19:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(6,1, to_date('16-02-2017 12:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(8,4, to_date('25-02-2017 18:00', 'DD-MM-YYYY HH24:MI')); | |
insert into cuadrante values(9,7, to_date('14-02-2017 15:00', 'DD-MM-YYYY HH24:MI')); | |
insert into participaciones values(1, 2, 'Y6857984L', 1, 1); | |
insert into participaciones values(1, 4, '85108890N', 3, 4); | |
insert into participaciones values(1, 6, '00015258D', 5, 7); | |
insert into participaciones values(2, 8, '09849927Q', 5, 4); | |
insert into participaciones values(2, 3, 'X6785562X', 6, 3); | |
insert into participaciones values(3, 7, 'X6785562X', 8, 7); | |
insert into participaciones values(3, 5, '55466243H', 7, 6); | |
insert into participaciones values(3, 2, 'Y6857984L', 9, 5); | |
insert into participaciones values(3, 8, '09849927Q', 2, 2); | |
insert into participaciones values(4, 9, '35647376K', 5, 2); | |
insert into participaciones values(4, 2, 'Y6857984L', 3, 8); | |
insert into participaciones values(4, 5, '55466243H', 4, 1); | |
insert into participaciones values(5, 7, 'X6951336T', 3, 4); | |
insert into participaciones values(5, 3, 'X6785562X', 1, 1); | |
insert into participaciones values(6, 9, '35647376K', 5, 2); | |
insert into participaciones values(6, 6, '00015258D', 3, 5); | |
insert into participaciones values(6, 4, '85108890N', 1, 7); | |
insert into participaciones values(7, 1, '00015258D', 2, 1); | |
insert into participaciones values(7, 2, 'Y6857984L', 4, 2); | |
insert into participaciones values(8, 10, '09849927Q', 7, 4); | |
insert into participaciones values(8, 1, '00015258D', 6, 3); | |
insert into participaciones values(9, 1, '00015258D', 1, 5); | |
insert into participaciones values(9, 5, '55466243H', 5, 7); | |
insert into participaciones values(9, 2, 'Y6857984L', 6, 6); | |
insert into participaciones values(10, 3, 'X6785562X', 2, 2); | |
insert into participaciones values(10, 10, '09849927Q', 3, 4); | |
insert into participaciones values(11, 10, '09849927Q', 7, 6); | |
insert into participaciones values(11, 4, '85108890N', 5, 1); | |
insert into participaciones values(12, 7, 'X6785562X', 1, 2); | |
insert into participaciones values(12, 8, '09849927Q', 2, 5); | |
commit; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment