DISEÑO Y PROGRAMACIÓN DE BASES DE DATOS

TEMA

TALLER SQL

DOCENTE:

ING. RUBÉN BAENA

INTEGRANTE:

JOJANN DE JESÚS DE VARGAS ÁLVAREZ

INGENIERÍA DE SISTEMAS V

UNIVERSIDAD DE CÓRDOBA
SEDE LORICA

30/05/2014
 EJERCICIOS DE SQL: SGBD MYSQL
Considerar las siguientes tablas:

EMPLEADO: ( DniEmp, NomEmp, SexEmp, FecNac, FecAlt, SalEmp, CodSuc, Dir )

SUCURSAL: ( CodSuc, DirSuc, TelSuc )
CUENTA: ( NumCta, FecApe, CodSuc, Saldo )
CLIENTE: ( DniCli, NomCli, DirCli, TelCli )
TITULAR: ( NumCta, DniCli, NumOrd )
CAJERO: ( CodCaj, TipCaj, CodSuc )
OPERACIÓN: ( CodCaj, FecOpe, HorOpe, TipOpe, DesOpe, ImpOpe, NumCta )

Realizar las siguientes consultas:

1. Número de todas las cuentas corrientes (NumCta) de la sucursal número 2

ordenadas por fecha de apertura (FecApe).

2. El número de todas las cuentas corrientes del cliente con DNI 444 y el orden de
titularidad.

3. El número de todas las cuentas corrientes del cliente que se llama Pedro del Valle y
el orden de titularidad.

4. El DNI y nombre de todos los clientes que tienen cuenta en la sucursal número 4,
ordenado por nombre.

* 5. El saldo de la cuenta número 6.

* 6. El número y saldo de todas las cuentas de la sucursal número 4.

* 7. Listado de clientes (DNI y nombre) con el saldo total (suma de saldos) de todas sus
cuentas corrientes, ordenado por nombre.

* 8. Todas las sucursales (número) y su saldo total (suma de los saldo de todas sus
cuentas) ordenado descendentemente por el saldo.

9. Todas las fechas que hay operaciones junto con el saldo global del banco acumulado
hasta esa fecha.

# 10. Todos los clientes (DNI y nombre) junto con el número de cuentas corrientes que
tienen (¿Cuántas tienen?), el saldo máximo y la suma de los saldos ordenado por el
número de cuentas que tienen descendentemente; y para todos aquellos que tienen
el mismo número de cuentas, ordenados por nombre ascendentemente.
 11. Todos los clientes (DNI) junto con el número de cuentas corrientes que tienen; pero
sólo aquellos clientes que tienen más de una cuenta corriente.

# 12. Todas las cuentas corrientes (número) cuyo saldo sea superior a la media de saldos
de la misma sucursal.

13. Extraer las cuentas en las que no han hecho ninguna operación en un cajero de la
sucursal de la cuenta.

14. Extraer un listado de todas las sucursales (número) junto con el número de
empleados de dicha sucursal; ordenado por el número de empleados

15. Extraer un listado de todas las sucursales (número) junto con el número de
empleados masculinos y el número de empleados femeninos.

16. Extraer un listado de clientes (DNI y nombre) junto con el nombre del director de la
sucursal de todas aquellas cuentas en las que el cliente es el primer titular.

Nota: * = Dos versiones; utilizando una vista y sin utilizarla; # = Sólo utilizando
vistas.
 Solución MySQL

1) SELECT NUMCTA FROM CUENTA WHERE CODSUC='2' ORDER BY

FECAPE;

2) SELECT NUMCTA FROM TITULAR WHERE DNICLI=' 44444444' ORDER BY

NUMORD;

3) SELECT NUMCTA FROM TITULAR AS T, CLIENTE AS C WHERE

NOMCLI='PEDRO DEL VALLE' AND T.DNICLI=C.DNICLI ORDER BY NUMORD;

4) SELECT C.DNICLI, C.NOMCLI FROM CLIENTE AS C, TITULAR AS T,

SUCURSAL AS S, CUENTA AS CU WHERE S.CODSUC='4' AND
S.CODSUC=CU.CODSUC AND CU.NUMCTA=T.NUMCTA AND
C.DNICLI=T.DNICLI;

5) SELECT SALDO FROM CUENTA WHERE NUMCTA='6';

5.1) CREATE VIEW SALDO_CTA6 AS SELECT SALDO FROM CUENTA WHERE

NUMCTA='6';

- SELECT *FROM SALDO_CTA6;

6) SELECT NUMCTA, SALDO FROM CUENTA AS C, SUCURSAL AS S WHERE
S.CODSUC='4' AND C.CODSUC=S.CODSUC;

CREATE VIEW VISTA6 AS SELECT NUMCTA, SALDO FROM CUENTA AS C,

SUCURSAL AS S WHERE S.CODSUC='4' AND C.CODSUC=S.CODSUC;

SELECT * FROM VISTA6;

7) SELECT C.DNICLI,C.NOMCLI, SUM(CU.SALDO) AS SALDO_TOTAL FROM

CLIENTE AS C, CUENTA AS CU, TITULAR AS T WHERE C.DNICLI=T.DNICLI
AND T.NUMCTA=CU.NUMCTA GROUP BY C.DNICLI ORDER BY C.NOMCLI;

CREATE VIEW VISTA7 AS SELECT C.DNICLI,C.NOMCLI, SUM(CU.SALDO) AS

SALDO_TOTAL FROM CLIENTE AS C, CUENTA AS CU, TITULAR AS T WHERE
C.DNICLI=T.DNICLI AND T.NUMCTA=CU.NUMCTA GROUP BY C.DNICLI
ORDER BY C.NOMCLI;

SELECT * FROM VISTA7;

8) SELECT S.CODSUC, SUM(CU.SALDO) AS SUMA_CUENTAS FROM
SUCURSAL AS S, CUENTA AS CU WHERE S.CODSUC=CU.CODSUC GROUP
BY S.CODSUC ORDER BY SUMA_CUENTAS;

CREATE VIEW VISTA8 AS SELECT S.CODSUC, SUM(CU.SALDO) AS

SUMA_CUENTAS FROM SUCURSAL AS S, CUENTA AS CU WHERE
S.CODSUC=CU.CODSUC GROUP BY S.CODSUC ORDER BY
SUMA_CUENTAS;

SELECT * FROM VISTA8;

9) SELECT FECOPE, SUM((SELECT (SUM(SALDO)-SUM(DESOPE)-

SUM(IMPOPE))AS TOTAL FROM CUENTA JOIN OPERACION WHERE
CUENTA.NUMCTA=OPERACION.NUMCTA)

+(SELECT SUM(C.SALDO)AS SUMA_PARCIAL FROM CUENTA AS C WHERE

C.NUMCTA NOT IN(SELECT NUMCTA FROM OPERACION))) AS
SALDO_EN_BANCO FROM OPERACION GROUP BY NUMCTA ORDER BY
FECOPE
10) CREATE VIEW VISTA9 AS SELECT C.DNICLI, C.NOMCLI ,
COUNT(CU.NUMCTA) AS CANT_CUENTAS,MAX(CU.SALDO)AS
SALDO_MAYOR, SUM(CU.SALDO) AS SUMA_SALDO_INVIDUAL FROM
CUENTA AS CU, TITULAR AS T, CLIENTE AS C WHERE C.DNICLI=T.DNICLI
AND T.NUMCTA=CU.NUMCTA GROUP BY C.DNICLI ORDER BY C.NOMCLI
ASC ,COUNT(CU.NUMCTA) DESC

SELECT * FROM VISTA9;

11) CREATE VIEW VISTA10 AS SELECT CLIENTE.DNICLI, COUNT(*) AS

TOTAL FROM CLIENTE NATURAL JOIN CUENTA NATURAL JOIN TITULAR
GROUP BY DNICLI HAVING(TOTAL>1)

SELECT * FROM VISTA10;

12) CREATE VIEW VISTA11 AS SELECT NUMCTA, SALDO FROM CUENTA

NATURAL JOIN SUCURSAL HAVING(SALDO>AVG(SALDO))

SELECT * FROM VISTA11;

13) SELECT NUMCTA FROM CUENTA WHERE NUMCTA NOT IN(SELECT
NUMCTA FROM CUENTA NATURAL JOIN SUCURSAL NATURAL JOIN
OPERACION NATURAL JOIN CAJERO)

14) SELECT CODSUC, COUNT(*) AS CANT_EMPLEADOS FROM SUCURSAL

NATURAL RIGHT JOIN EMPLEADO GROUP BY CODSUC ORDER BY
CANT_EMPLEADOS

15) SELECT CODSUC ,SEXEMP,COUNT(EMPLEADO.SEXEMP) AS

CANT_EMPLEADOS FROM SUCURSAL NATURAL JOIN EMPLEADO WHERE
SEXEMP='M' GROUP BY CODSUC UNION SELECT
CODSUC ,SEXEMP,COUNT(EMPLEADO.SEXEMP) AS CANT_EMPLEADOS
FROM SUCURSAL NATURAL JOIN EMPLEADO WHERE SEXEMP='F' GROUP
BY CODSUC

16) SELECT DNICLI, NOMCLI, DIRSUC FROM SUCURSAL NATURAL JOIN

CUENTA NATURAL JOIN TITULAR NATURAL JOIN CLIENTE
EJERCICIOS SQL: SGBD POSTGRESQL

Para que un alumno ingrese en la Universidad, debe realizar una preinscripción,

especificando a qué estudios desea acceder en orden de preferencia.

Dicha información se guarda en una base de datos cuyo modelo de datos responde al
siguiente diagrama:

ALUMNO

N
Orden,
Admitid
Preinscripción o

ESTUDIO
M

Pasando el modelo de datos a relacional obtenemos:

ALUMNO: (DNI, Nombre, Nota, Isla)

ESTUDIO: (Código, Nombre, Nota de corte)

PREINSCRIPCION: (DNI, Código, Orden, Admitido)

Por otro lado, su implementación en SQL corresponde a:

CREATE TABLE ALUMNO

( DNI Varchar2( 9 ),

NOM Varchar2( 40 ),

ILLA Varchar2( 3 )
CHECK ( ILLA IN (‘MA’, ‘ME’, ‘IB’, ‘FO’) ),
NOTA Number
);
CREATE TABLE PREINSCRIPCION
( DNI Varchar2( 9 ),
 COD Varchar2( 4 ),
ORD Number( 2 ),
ADM Varchar2( 1 ) CHECK ( ADM IN ( ‘S’, ‘N’) )
);
CREATE TABLE ESTUDIO
( COD Varchar2( 4 ),
NOM Varchar2( 20 ),
NOTA Number
);
Nota: El atributo ‘Nota de corte’ de la entidad ESTUDIO indica la nota mínima para
acceder a estos estudios. Sin embargo hay alumnos que tienen acceso directo por otras
causas (por ejemplo por aprobar las pruebas de mayores de 25 años); por tanto se
considera que un alumno está admitido en un estudio si el atributo ‘Admitido’ de la
relación ‘Preinscripción’ es igual a ‘S’.

1) Extraer el DNI y nombre de todos los alumnos junto con el nombre del estudio
donde han sido admitidos ordenados por estudios.

2) Extraer el DNI y nombre de todos los alumnos que no han sido admitidos en ningún
estudio ordenados por DNI.
3) Extraer todos los estudios junto con el número de alumnos que lo han solicitado en
primera preferencia, ordenado descendentemente por el número de alumnos.
4) Extraer todos los alumnos (DNI y nombre) cuya nota sea igual o superior a la nota
media de todos los alumnos que viven en la misma isla.
5) Enunciar las instrucciones SQL necesarias para crear las claves primarias, externas y
los índices que creas convenientes del ejemplo.
6) Suponer que al crear la clave primaria de la tabla de alumnos da error porque
existen DNI’s duplicados. Dar la sentencia SQL para listar los DNI’s duplicados (es
decir, que existen dos o más registros diferentes en a tabla de alumnos con el mismo
DNI). Evidentemente, al listar los DNI’s no deben salir duplicados.

Solución PostgSQL
1) SELECT A.DNI, A.NOM, E.NOM FROM ALUMNO AS A, PREINSCRIPCION AS
P, ESTUDIO AS E WHERE A.DNI=P.DNI AND E.COD = P.COD AND P.ADM='S'
ORDER BY E.NOM;
2) SELECT A.DNI, A.NOM FROM ALUMNO AS A, PREINSCRIPCION AS P,
ESTUDIO AS E WHERE A.DNI=P.DNI AND E.COD=P.COD AND P.ADM='N'
ORDER BY A.DNI;

3) SELECT E.NOM, COUNT(A.DNI) AS N_ALUMNOS FROM ESTUDIO AS E,

ALUMNO AS A, PREINSCRIPCION AS P WHERE A.DNI=P.DNI AND
E.COD=P.COD GROUP BY E.NOM;

4) SELECT DNI, NOM FROM ALUMNO NATURAL JOIN(SELECT ISLA,

AVG(NOTA) AS MEDIA_ALUM FROM ALUMNO GROUP BY ISLA) AS
INFO_ALUM WHERE ALUMNO.NOTA >=INFO_ALUM.MEDIA_ALUM;
5)

CREATE TABLE ALUMNO(

DNI VARCHAR(9) NOT NULL PRIMARY KEY,
NOM VARCHAR(40) NOT NULL,
ISLA VARCHAR(2) NOT NULL CHECK ( ISLA IN ('MA', 'ME', 'IB', 'FO')),
NOTA FLOAT NOT NULL,
CONSTRAINT PK_PRIMARY1 PRIMARY KEY ("DNI")
);
CREATE TABLE ESTUDIO(
COD VARCHAR(4) NOT NULL PRIMARY KEY,
NOM VARCHAR(30) NOT NULL,
NOTA FLOAT NOT NULL,
CONSTRAINT PK_PRIMARY2 PRIMARY KEY ("COD")
);

CREATE TABLE PREINSCRIPCION(

DNI VARCHAR(9) NOT NULL primary key,

COD VARCHAR(4) NOT NULL,

ORD INT NOT NULL,

ADM VARCHAR(1) NOT NULL CHECK ( ADM IN ( 'S', 'N')),

CONSTRAINT PK_1 PRIMARY KEY ("DNI", "COD"), CONSTRAINT FK_1

FOREIGN KEY ("DNI") REFERENCES ALUMNO ("DNI") MATCH SIMPLE ON
UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT FK_2 FOREIGN KEY ("COD") REFERENCES ESTUDIO ("COD")

MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE

);

6) SELECT DNI FROM ALUMNO GROUP BY DNI HAVING COUNT(*) >= 2;