Consultas y Operaciones Avanzadas en SQL: Ejercicios Prácticos

Creación y Gestión de Tablas en SQL

A continuación, se presentan ejercicios sobre la creación y gestión de tablas en SQL, incluyendo la definición de claves primarias, foráneas y restricciones.

Ejercicio 1: Creación de Tablas

Se requiere crear las tablas BANCOS, PAGOS y ACTIVIDADES_USUARIOS. Es fundamental seguir un orden lógico para evitar errores de dependencias. El orden correcto es:

1. BANCOS (ya que no depende de otras tablas)
2. PAGOS (depende de USUARIOS, que no se muestra aquí pero se asume preexistente)
3. ACTIVIDADES_USUARIOS (depende de ACTIVIDADES y USUARIOS)

A continuación, se muestra el código SQL corregido y mejorado para cada tabla:

-- Tabla BANCOS
CREATE TABLE BANCOS (
    ENT_SUC     NUMBER(8) NOT NULL,
    NOMBRE      VARCHAR2(50),
    DIRECCION   VARCHAR2(50),
    LOCALIDAD   VARCHAR2(30),
    TELEFONOS   VARCHAR2(30),
    CONSTRAINT PK_BANCOS PRIMARY KEY (ENT_SUC)
);

-- Tabla PAGOS
-- Se asume que la tabla USUARIOS existe y tiene una columna NUM_SOCIO
CREATE TABLE PAGOS (
    CODIGO_USU  VARCHAR2(7) NOT NULL,
    NUMERO_MES  NUMBER(2) NOT NULL,
    CUOTA       NUMBER(7),
    OBSERVACIONES VARCHAR2(500),
    CONSTRAINT PK_PAGOS PRIMARY KEY (CODIGO_USU, NUMERO_MES),
    CONSTRAINT FK_PAGOS_USUARIOS FOREIGN KEY (CODIGO_USU)
        REFERENCES USUARIOS (NUM_SOCIO) ON DELETE CASCADE
);

-- Tabla ACTIVIDADES_USUARIOS
-- Se asume que la tabla ACTIVIDADES existe y tiene una columna CODIGO_ACT
CREATE TABLE ACTIVIDADES_USUARIOS (
    CODIGO_ACT  VARCHAR2(7) NOT NULL,
    CODIGO_USU  VARCHAR2(7) NOT NULL,
    FECHA_ALTA  DATE,
    FECHA_BAJA  DATE,
    CONSTRAINT PK_ACT_USU PRIMARY KEY (CODIGO_ACT, CODIGO_USU),
    CONSTRAINT FK_ACT_USU_ACTIVIDADES FOREIGN KEY (CODIGO_ACT)
        REFERENCES ACTIVIDADES (CODIGO_ACT) ON DELETE CASCADE,
    CONSTRAINT FK_ACT_USU_USUARIOS FOREIGN KEY (CODIGO_USU)
        REFERENCES USUARIOS (NUM_SOCIO) ON DELETE CASCADE
);

-- Tabla ALMACEN (ejemplo adicional con errores corregidos)
CREATE TABLE ALMACEN (
    REFERENCIA   NUMBER(15) NOT NULL,
    DENOMINACION VARCHAR2(50),
    PVP          NUMBER(7),
    EXISTENCIA   NUMBER(7),
    PROVEEDOR    NUMBER(7),
    CONSTRAINT PK_ALMACEN PRIMARY KEY (REFERENCIA),
    CONSTRAINT FK_ALMACEN_PROVEEDORES FOREIGN KEY (PROVEEDOR)
        REFERENCES PROVEEDORES (CODIGO) ON DELETE CASCADE
);

Consultas SQL Avanzadas

A continuación, se presentan diversas consultas SQL, optimizadas y corregidas:

Ejercicio 2: Visualización de Bancos y Usuarios

SELECT TRIM(BANCOS.NOMBRE), USUARIOS.NOMBRE
FROM BANCOS, USUARIOS
WHERE BANCOS.ENT_SUC = USUARIOS.CODIGO_BANCO
ORDER BY BANCOS.NOMBRE;

Ejercicio 3: Actividades con Cuota Inferior a la Media

SELECT DESCRIPCION
FROM ACTIVIDADES
WHERE CUOTA < (
    SELECT AVG(CUOTA_SOCIO)
    FROM USUARIOS
    WHERE CUOTA_FAMILIAR IS NOT NULL
);

Ejercicio 4: Cuotas Familiares con Valores Nulos

SELECT NVL(TO_CHAR(CUOTA_FAMILIAR), 'ES NULA') AS CUOTA_FAMILIAR
FROM USUARIOS;

Ejercicio 5: Usuarios con Apellidos que Empiezan por 'A'

SELECT NOMBRE, APELLIDOS
FROM USUARIOS
WHERE APELLIDOS LIKE 'A% A%';

Ejercicio 6: Información de Usuarios, Actividades y Bancos

SELECT U.NOMBRE, U.APELLIDOS, B.NOMBRE AS NOMBRE_BANCO, A.DESCRIPCION
FROM BANCOS B
JOIN USUARIOS U ON B.ENT_SUC = U.CODIGO_BANCO
JOIN ACTIVIDADES_USUARIOS AU ON U.NUM_SOCIO = AU.CODIGO_USU
JOIN ACTIVIDADES A ON AU.CODIGO_ACT = A.CODIGO_ACT;

Ejercicio 7: Usuarios que Realizan la Misma Actividad que Inés Pérez Díaz

SELECT U.NOMBRE, U.APELLIDOS
FROM USUARIOS U
JOIN ACTIVIDADES_USUARIOS AU ON U.NUM_SOCIO = AU.CODIGO_USU
WHERE AU.CODIGO_ACT IN (
    SELECT CODIGO_ACT
    FROM ACTIVIDADES_USUARIOS
    WHERE CODIGO_USU = (
        SELECT NUM_SOCIO
        FROM USUARIOS
        WHERE NOMBRE = 'INES' AND APELLIDOS = 'PEREZ DIAZ'
    )
);

Ejercicio 8: Formateo de Salida con Datos de Usuario

SELECT 'El socio con número ' || NUM_SOCIO || ' se llama ' || NOMBRE || ' ' || APELLIDOS AS INFORMACION
FROM USUARIOS;

Ejercicio 9: Formateo de Salida con Nombres de Bancos y Localidades

SELECT TRIM(NOMBRE) || ' está en ' || LOCALIDAD AS UBICACION
FROM BANCOS;

Ejercicio 10: Número de Usuarios por Banco

SELECT COUNT(NUM_SOCIO)
FROM USUARIOS
WHERE CODIGO_BANCO = (
    SELECT ENT_SUC
    FROM BANCOS
    WHERE NOMBRE = 'ATLANTICO'
);

Ejercicio 11: Cálculo de Años de Alta de Usuarios

SELECT
    NOMBRE,
    APELLIDOS,
    ROUND((SYSDATE - FECHA_ALTA) / 365.25) AS ANIOS_DESDE_ALTA
FROM
    USUARIOS;

Ejercicio 12: Formateo de Fechas de Nacimiento

SELECT NOMBRE, TO_CHAR(FECHA_NACIMIENTO, '"nació el día" dd "de" Month "de" yyyy') AS FECHA_NACIMIENTO_FORMATO
FROM USUARIOS;

* * *

Administración de Bases de Datos en SQL

Ejercicio 13: Creación de Tabla con Datos de Otras Tablas

CREATE TABLE FACTURASCONDETALLES AS
SELECT F.NUM_FACTURA, F.FECHA, F.CLIENTE, F.DESCUENTO, DF.NUM_LINEA, DF.REFERENCIA, DF.CANTIDAD
FROM FACTURAS F
JOIN DETALLE_FACTURAS DF ON F.NUM_FACTURA = DF.FACTURA;

Ejercicio 14: Restricción de Localidad en Tabla CLIENTES

ALTER TABLE CLIENTES
ADD CONSTRAINT CK_LOCALIDAD CHECK (LOCALIDAD IN ('BILBAO', 'DONOSTIA', 'GASTEIZ'));

Ejercicio 15: Creación de Vista VCLIPROV

CREATE OR REPLACE VIEW VCLIPROV (NOMBRECLIENTE, NOMBREPROVEEDOR, CIFPROVEEDOR) AS
SELECT C.NOMBRE, P.NOMBRE, P.CIF
FROM PROVEEDORES P
JOIN ALMACEN A ON P.CODIGO = A.PROVEEDOR
JOIN DETALLE_FACTURAS DF ON A.REFERENCIA = DF.REFERENCIA
JOIN FACTURAS F ON DF.FACTURA = F.NUM_FACTURA
JOIN CLIENTES C ON F.CLIENTE = C.CODIGO;

Ejercicio 16: Inserción de Datos en PROVEEDORES desde Tabla EMPLE

INSERT INTO PROVEEDORES (CODIGO, NOMBRE, SALDO)
SELECT EMP_NO, APELLIDO, COMISION
FROM EMPLE
WHERE DEPT_NO = (
    SELECT DEPT_NO
    FROM EMPLE
    GROUP BY DEPT_NO
    HAVING COUNT(*) = (
        SELECT MAX(COUNT(*))
        FROM EMPLE
        GROUP BY DEPT_NO
    )
);

Ejercicio 17: Actualización de Saldo de Proveedores en Bilbao

UPDATE PROVEEDORES
SET SALDO = SALDO * 2
WHERE LOCALIDAD = 'BILBAO';

Ejercicio 18: Consulta con Agregación y Subconsulta

SELECT C.NOMBRE, C.LOCALIDAD, A.DENOMINACION, DF1.CANTIDAD
FROM CLIENTES C
JOIN FACTURAS F ON C.CODIGO = F.CLIENTE
JOIN DETALLE_FACTURAS DF1 ON F.NUM_FACTURA = DF1.FACTURA
JOIN ALMACEN A ON DF1.REFERENCIA = A.REFERENCIA
WHERE DF1.CANTIDAD = (
    SELECT MAX(DF2.CANTIDAD)
    FROM DETALLE_FACTURAS DF2
    WHERE DF2.FACTURA = DF1.FACTURA
);

Ejercicio 19: Creación de Tablespace

CREATE TABLESPACE TEXAMEN DATAFILE 'EXA1.ORA' SIZE 5M;

Ejercicio 20: Ampliación de Tablespace

ALTER TABLESPACE TEXAMEN
ADD DATAFILE 'EXA2.ORA' SIZE 5M
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED;

Ejercicio 21: Creación de Usuario

CREATE USER EXAMEN
IDENTIFIED BY EXAMEN
DEFAULT TABLESPACE TEXAMEN
QUOTA 500K ON TEXAMEN
TEMPORARY TABLESPACE TEXAMEN;

Ejercicio 22: Borrado de Usuario

DROP USER EXAMEN CASCADE;

Ejercicio 23: Concesión de Privilegios

GRANT CREATE SESSION TO EXAMEN;
GRANT SELECT ON EMPLE TO EXAMEN;
GRANT INSERT ON DEPART TO EXAMEN;
GRANT UPDATE (COMISION) ON EMPLE TO EXAMEN;

Ejercicio 24: Otorgar Privilegios con Opción de Administración

Para que EXAMEN pueda otorgar los mismos privilegios a otros usuarios, se deben usar las cláusulas WITH ADMIN OPTION (para privilegios de sistema) y WITH GRANT OPTION (para privilegios sobre objetos).

--Ejemplo
GRANT CREATE SESSION TO EXAMEN WITH ADMIN OPTION;
GRANT SELECT ON EMPLE TO EXAMEN WITH GRANT OPTION;

Ejercicio 25: Revocar Privilegio de Inserción

REVOKE INSERT ON DEPART FROM EXAMEN;

Ejercicio 26: Creación de Rol

CREATE ROLE ROLEXAMEN;
GRANT INSERT ON DEPART TO ROLEXAMEN;

Ejercicio 27: Asignación de Rol a Usuario

GRANT ROLEXAMEN TO EXAMEN;

Ejercicio 28: Creación de Perfil

CREATE PROFILE PEXAMEN
LIMIT SESSIONS_PER_USER 2
CONNECT_TIME 5;

Ejercicio 29: Consulta con Condiciones en Existencias y Localidad

SELECT A.REFERENCIA, A.DENOMINACION
FROM ALMACEN A
JOIN PROVEEDORES P ON A.PROVEEDOR = P.CODIGO
WHERE A.EXISTENCIA BETWEEN 0 AND 50
AND UPPER(P.LOCALIDAD) NOT LIKE '%E%';