/************************************************/
/*		AYUDANTIA Nº 1 DE BASE DE DATOS		    */
/*	      EQUIPOS DEPORTIVOS  20 de Agosto.     */ 
/*			  © Alejandro Sepúlveda Sotomayor   */
/************************************************/

-- Me conecto con el usuario: "system" y paasswd: "manager"
connect system/manager;  -- cnx Local

------ > Me conecto con el usuario: "scott  y passwd: "tiger" usando conexion remota con la cadena de cnx "db2007"
-------     connect scott/tiger@db2007;
-----------------------------------------------------------------
-- Borro las tablas inicialmente
DROP TABLE afs1_equipos_socios; 
DROP TABLE afs1_jugador;
DROP TABLE afs1_socios;
DROP TABLE afs1_equipos;

-----------------------------------------------------------------
---creacion de las tablas---

CREATE TABLE afs1_jugador (
	rut 	 varchar2(10) NOT NULL ,
	nombre 	 varchar2(25) ,
	IDequipo varchar2(20) ,
  constraint pk_afs1_jugador primary key (rut)
);

CREATE TABLE afs1_equipos (
	IDequipo varchar2(20) NOT NULL ,
	nombre 	 varchar2(20) ,
	ranking	 number(5)   ,
  constraint pk_afs1_equipos primary key (IDequipo)
);

CREATE TABLE afs1_equipos_socios ( 
	IDequipo varchar2(20) NOT NULL ,
	rut 	 varchar2(10) NOT NULL ,
  constraint pk_afs1_equipos_socios primary key (IDequipo,rut)
);

CREATE TABLE afs1_socios (
	rut 	 		varchar2(10) NOT NULL ,
	nombre 	 		varchar2(25) ,
	nacionalidad	varchar2(25) ,
	constraint pk_afs1_socios primary key (rut)
);

-----------------------------------------------------------------
-- Claves Foraneas

ALTER TABLE afs1_jugador 
  ADD CONSTRAINT fk_afs1_jugador 
  FOREIGN KEY(IDequipo) 
  REFERENCES afs1_equipos(IDequipo)
ON DELETE CASCADE;

ALTER TABLE afs1_equipos_socios 
  ADD CONSTRAINT fk_afs1_equipos_socios1  
  FOREIGN KEY (idequipo) 
  REFERENCES afs1_equipos(IDequipo) 
ON DELETE CASCADE;

ALTER TABLE afs1_equipos_socios 
  ADD CONSTRAINT fk_afs1_equipos_socios2  
  FOREIGN KEY (rut) 
  REFERENCES afs1_socios(rut) 
ON DELETE CASCADE;


-----------------------------------------------------------------
--               Modificaciones en las Tabllas

/* Agregar en la tabla Socios la cuota de cada socio */

ALTER TABLE afs1_socios 
  ADD cuota	 number(10);

/* Eliminar en la tabla Socios la nacionalidad  */

ALTER TABLE afs1_socios 
  DROP COLUMN nacionalidad;

/* Cambiar el ancho de la columna nombre de 20  a 25 */
ALTER TABLE afs1_equipos 
  MODIFY nombre varchar2(25);

------------------------------------------------------------------
--  Restriicciones de las Tablas

/* La cuota de los socios no puede ser negativa */
ALTER TABLE afs1_socios ADD 
  CONSTRAINT ck_afs1_socios 
  CHECK (cuota > 0);

/* Que el codigo de los equipos  "IDequipo" pueda ser solo "E01" o "E02".*/
ALTER TABLE afs1_equipos ADD
  CONSTRAINT ck_afs1_equipos 
  CHECK (IDequipo IN ('E01', 'E02'));   

-----------------------------------------------------------------
-- Inserciones
/* Se borra el contenido de las tablas */
DELETE FROM afs1_equipos_socios; 
DELETE FROM afs1_jugador;
DELETE FROM afs1_socios;
DELETE FROM afs1_equipos;
 
/* Se inserta */
INSERT INTO afs1_socios 
  VALUES ('11111111-1','Socio1',1);
INSERT INTO afs1_socios 
  VALUES ('22222222-2','Socio2',2);
INSERT INTO afs1_socios 
  VALUES ('33333333-3','Socio3',3);
INSERT INTO afs1_socios 
  VALUES ('44444444-4','Socio4',4);
INSERT INTO afs1_socios 
  VALUES ('55555555-5','Socio5',5);
INSERT INTO afs1_socios 
  VALUES ('66666666-6','Socio6',6);
INSERT INTO afs1_socios 
  VALUES ('77777777-7','Socio7',7);
INSERT INTO afs1_socios 
  VALUES ('88888888-8','Socio8',8);
INSERT INTO afs1_socios 
  VALUES ('99999999-9','Socio9',9);

INSERT INTO afs1_equipos
  VALUES ('E01','Colo-Colo',1);
INSERT INTO afs1_equipos 
  VALUES ('E02','Audax Italiano',2);

INSERT INTO afs1_jugador 
  VALUES ('01111111-1','jugador1','E01');
INSERT INTO afs1_jugador 
  VALUES ('02222222-2','jugador2','E01');
INSERT INTO afs1_jugador 
  VALUES ('03333333-3','jugador3','E01');
INSERT INTO afs1_jugador 
  VALUES ('04444444-4','jugador4','E01');
INSERT INTO afs1_jugador 
  VALUES ('05555555-5','jugador5','E01');
INSERT INTO afs1_jugador 
  VALUES ('06666666-6','jugador6','E02');
INSERT INTO afs1_jugador 
  VALUES ('07777777-7','jugador7','E02');
INSERT INTO afs1_jugador 
  VALUES ('08888888-8','jugador8','E02');
INSERT INTO afs1_jugador 
  VALUES ('09999999-9','jugador9','E02');  
  
INSERT INTO afs1_equipos_socios 
  VALUES ('E01','11111111-1');
INSERT INTO afs1_equipos_socios 
  VALUES ('E01','22222222-2');
INSERT INTO afs1_equipos_socios 
  VALUES ('E01','33333333-3');
INSERT INTO afs1_equipos_socios 
  VALUES ('E01','44444444-4');
INSERT INTO afs1_equipos_socios 
  VALUES ('E01','55555555-5');
INSERT INTO afs1_equipos_socios 
  VALUES ('E02','11111111-1');
INSERT INTO afs1_equipos_socios 
  VALUES ('E02','66666666-6');
INSERT INTO afs1_equipos_socios 
  VALUES ('E02','77777777-7');
INSERT INTO afs1_equipos_socios 
  VALUES ('E02','88888888-8');
INSERT INTO afs1_equipos_socios 
  VALUES ('E02','99999999-9');

/* Guardo los cambios en la Bdd */
COMMIT;

-----------------------------------------------------------------
-- Select Anidados

CREATE VIEW VistaAyu1 AS 
  SELECT E.nombre As NombreEquipo, J.nombre As NombreJugador 
  FROM afs1_equipos E, afs1_jugador J 
  WHERE E.ranking > 1 
      AND E.IDequipo = J.IDequipo
/
SELECT E.IDequipo, AVG(S.cuota)
  FROM afs1_equipos E , afs1_equipos_socios ES , afs1_socios S
  WHERE E.IDequipo = ES.IDequipo
      AND ES.rut = S.rut
  GROUP BY E.IDequipo
/