/* @ej1_Banco */
SET echo ON

/* listar las cuentas abiertas */
SELECT * FROM apercuenta;

/* nº de cuentas de cada sucursal */

   SELECT ciudad,agencia,count(distinct numero)
   FROM apercuenta
   GROUP BY ciudad,agencia;

/* nº máximo de cuentas de una sucursal */

SELECT max(count(distinct numero)) "nº max. suc."
FROM apercuenta
GROUP BY ciudad,agencia;

/* sucursales que tienen 3 cuentas */

SELECT ciudad,agencia
FROM apercuenta
WHERE 3 = (SELECT count(distinct numero)
           FROM apercuenta ap
			        WHERE apercuenta.ciudad=ap.ciudad and
			              apercuenta.agencia=ap.agencia)
GROUP BY ciudad,agencia;

/* agencias cuyo nº es una unidad menor que el mayor nº de cuentas de una agencia */

SELECT ciudad,agencia
FROM apercuenta
WHERE agencia+1 = (SELECT max(count(distinct numero))
                   FROM apercuenta
                   GROUP BY ciudad,agencia);

SET pagesize 64;
SET linesize 100;
column  TABLE_NAME format a30;
column comments format a68;

/* visualización del diccionario del SGBD */
SELECT table_name, comments FROM DICT;

/* visualización de las tablas, vistas, etc. accesibles al usuario */
SELECT * FROM USER_CATALOG;

/* adicion de un cliente con un campo nulo (la calle) */
INSERT INTO cliente VALUES ('Pedro',179,'','Zaragoza');

SELECT * FROM cliente;

/* obtención del número de clientes con calle conocida */
SELECT count (calle) FROM cliente;

/* obtención del número de clientes */
SELECT count(*) FROM cliente;

/* visualizar los clientes con calle desconocida */
SELECT * FROM cliente WHERE calle is NULL;

/* añadir un cliente que vive donde 'José López' */
INSERT INTO cliente
VALUES ('Angel Sanchez', 17171718, 'Mayor 17', 'Zaragoza');


/* listado de clientes que viven en la misma ciudad y calle */
/* que el cliente con dni = 15444345                        */
SELECT nombre,dni,ciudad,calle
FROM cliente
WHERE (ciudad,calle) = (SELECT ciudad,calle
                        FROM cliente
                        WHERE dni=15444345);

/* otro modo alternativo sería */
SELECT X.nombre,X.dni,X.ciudad,X.calle
FROM cliente X, cliente Y
WHERE X.calle=Y.calle and X.ciudad=Y.ciudad and Y.dni=15444345;

/* listado del número de clientes que hay en cada ciudad */
SELECT ciudad,count(*)
FROM cliente
GROUP BY ciudad;

/* listado de las ciudades con más de 2 clientes */
SELECT ciudad,count(*)
FROM cliente
GROUP BY ciudad
HAVING count(*) > 2;

/* cambio del importe del prestamo número 17 */
UPDATE prestamo
SET importe=110000
WHERE numprestamo=17;

/* listado de préstamos por importe mayor que la media de los importes */
/* de los prestamos que tienen igual interes, agrupados por interés    */
SELECT numprestamo,importe,interes
FROM prestamo X
WHERE importe > (SELECT avg(importe)
                 FROM prestamo
                 WHERE X.interes=interes)
ORDER BY interes;

/* informe de dichos préstamos para ilustrar el uso del group by */
SELECT interes,avg(importe),max(importe),count(*)
FROM prestamo X
WHERE importe > (SELECT avg(importe) FROM prestamo WHERE X.interes=interes)
GROUP BY interes;

/****************************************************************************/
/* Simplificación del esquema eliminando prestformalizado */
/****************************************************************************/

/* adición de la cuenta ligada al prestamo a la tabla prestamo */
/* paso 1: creación de la columna numCuenta */
ALTER TABLE prestamo add (numCuenta number(10));

/* listado de la información de los prestamos (para comprobar) */
SELECT X.numprestamo,importe,interes,numero
FROM prestamo X, prestformalizado Y
WHERE X.numprestamo = Y.numprestamo;

/* paso 2: asignación de valores a la columna numCuenta */
UPDATE prestamo X
SET numCuenta=(SELECT numero
               FROM prestformalizado Y
               WHERE X.numprestamo=Y.numprestamo);
DROP TABLE prestformalizado;


/****************************************************************************/
/* Simplificación del esquema eliminando invformalizada   */
/****************************************************************************/

/* adición de la cuenta ligada a la inversion a la tabla inversion */
/* paso 1: creación de la columna numCuenta */
ALTER TABLE inversion add (numCuenta number(10));

/* listado de la información de las inversiones (para comprobar) */
SELECT X.numinversion,importe,interes,numero
FROM inversion X, invformalizada Y
WHERE X.numinversion = Y.numinversion;

/* paso 2: asignación de valores a la columna numCuenta */
UPDATE inversion X
SET numCuenta=(SELECT numero
               FROM invformalizada Y
               WHERE X.numinversion=Y.numinversion);
DROP TABLE invformalizada;


/* comprobación del resultado de la operacion */
/* visualización de las tablas, vistas, etc. accesibles al usuario */
SELECT * FROM CAT;
SELECT * FROM prestamo;
SELECT * FROM inversion;