Ejercicios SQL Aplicados a Bases de Datos de Telefonía

Cuestiones SQL para Base de Datos de Empresa de Telefonía

1. Creación de la tabla `TELEFONOS`

Crea la siguiente tabla en la base de datos: TELEFONOS (IMEI, MARCA, MODELO, FECHA, PRECIO).

CREATE TABLE TELEFONOS (
    IMEI VARCHAR(20) PRIMARY KEY,
    MARCA VARCHAR(30) NOT NULL,
    MODELO VARCHAR(40) NOT NULL,
    FECHA DATE NOT NULL,
    PRECIO DECIMAL(8,2) NOT NULL
);

2. Añadir el campo `COLOR` a la tabla `TELEFONOS`

Añade a la tabla anterior el campo COLOR para indicar el color del aparato.

ALTER TABLE TELEFONOS
ADD COLOR VARCHAR(15) NULL;

3. Inserción de registros en la tabla `TELEFONOS`

Inserta dos registros en la tabla TELEFONOS.

INSERT INTO TELEFONOS (IMEI, MARCA, MODELO, FECHA, PRECIO, COLOR)
VALUES ('1234567890', 'SAMSUNG', 'GALAXY 21', '2024-03-06', 455.60, 'ROJO');

INSERT INTO TELEFONOS (IMEI, MARCA, MODELO, FECHA, PRECIO, COLOR)
VALUES ('1234567891', 'SAMSUNG', 'GALAXY 22', '2024-03-06', 605.60, NULL);

4. Borrar llamadas de números de Prepago

Borra las llamadas que realizaron los números de Prepago el último trimestre del año pasado.

Nota: Aplicar SET AUTOCOMMIT=0; antes y SET AUTOCOMMIT=1; después de la operación.

-- SET AUTOCOMMIT=0;
DELETE FROM LLAMADAS
WHERE NUMERO IN (SELECT NUMERO FROM NUMEROS WHERE TIPO = 'PREPAGO')
  AND INSTANTE BETWEEN '2023-10-01' AND '2023-12-31';
-- SET AUTOCOMMIT=1;

5. Incrementar saldo de teléfonos de Prepago de Ana Torrente Ortega

Incrementa en 10 euros el saldo de los teléfonos de Prepago de Ana Torrente Ortega.

UPDATE NUMEROS
SET SALDO = SALDO + 10
WHERE TIPO = 'PREPAGO'
  AND CLIENTE = (SELECT DNICLIENTE FROM CLIENTES WHERE APELLIDOS = 'TORRENTE ORTEGA' AND NOMBRE = 'ANA');

6. Clientes de Vera y Pulpí dados de alta el año pasado

Obtén la relación de clientes de Vera y Pulpí dados de alta el año pasado.

SELECT *
FROM CLIENTES
WHERE YEAR(FECHAALTA) = 2023
  AND CIUDAD IN ('VERA', 'PULPÍ');

7. Números y fecha de alta de teléfonos de Emilio Blanco Pérez

Obtén el número y fecha de alta de los números de teléfono del cliente Emilio Blanco Pérez.

SELECT NUMERO, FECHAALTA
FROM NUMEROS
WHERE CLIENTE IN (SELECT DNICLIENTE FROM CLIENTES WHERE NOMBRE = 'EMILIO' AND APELLIDOS = 'BLANCO PÉREZ');

8. Detalles de tarifa de teléfonos de Emilio Blanco Pérez

Muestra el número de teléfono, nombre de la tarifa y precio por minuto de esta, para los teléfonos del cliente Emilio Blanco Pérez.

SELECT N.NUMERO, T.NOMBRE AS TARIFA, T.PRECIO_MINUTO
FROM NUMEROS N
INNER JOIN TARIFAS T ON N.TARIFAS = T.CODTARIFA 
WHERE N.CLIENTE IN (SELECT DNICLIENTE FROM CLIENTES WHERE NOMBRE = 'EMILIO' AND APELLIDOS = 'BLANCO PÉREZ');

9. Número total de teléfonos por cliente

Muestra el número total de teléfonos que tiene cada cliente, indicando el DNI, Nombre y Apellidos del cliente.

SELECT C.DNICLIENTE, C.NOMBRE, C.APELLIDOS, COUNT(N.NUMERO) AS TOTAL_TELEFONOS
FROM CLIENTES C
INNER JOIN NUMEROS N ON C.DNICLIENTE = N.CLIENTE
GROUP BY C.DNICLIENTE, C.NOMBRE, C.APELLIDOS;

10. Clientes con más de un número de teléfono Prepago

Obtén la relación de clientes que tienen más de un número de teléfono en la modalidad Prepago.

SELECT C.DNICLIENTE, C.APELLIDOS, C.NOMBRE, COUNT(N.NUMERO) AS TOTAL_PREPAGO
FROM CLIENTES C
INNER JOIN NUMEROS N ON C.DNICLIENTE = N.CLIENTE
WHERE N.TIPO = 'PREPAGO'
GROUP BY C.DNICLIENTE, C.APELLIDOS, C.NOMBRE
HAVING COUNT(N.NUMERO) > 1;

Nota: Las siguientes sentencias INSERT y el comentario parecen ser para pruebas y configuración de datos para esta consulta:

-- Datos de ejemplo y prueba
INSERT INTO CLIENTES (DNICLIENTE, NOMBRE, APELLIDOS, DIRECCION, CIUDAD, SEXO, FECHAALTA) 
VALUES ('666666666', 'Ana', 'Torrente Ortega', 'LA QUE SEA', 'GARRUCHA', 'M', CURRENT_DATE());

INSERT INTO NUMEROS (NUMERO, CLIENTE, TARIFA, TIPO, FECHAALTA, SALDO) 
VALUES ('606999999', '666666666', 3, 'CONTRATO', CURRENT_DATE(), NULL);

-- (CORREGIR PARÁMETROS PARA COMPROBAR POSTERIORMENTE QUITANDO EL DNI CLIENTES) -- Comentario original

INSERT INTO NUMEROS (NUMERO, CLIENTE, TARIFA, TIPO, FECHAALTA, SALDO) 
VALUES ('606777777', '333333333', 1, 'PREPAGO', CURRENT_DATE(), 20);