Ejercicios DDL

Tema Título Versión
04 SQL I (EJERCICIOS DDL) v 1.0

TEMA 4 (I): Ejercicios DDL

Ejercicio 1: Base de datos de Colegios

Descripción de la estructura:

  1. Tabla: Colegios

    • ID colegio: Identificador único del colegio (clave primaria).
    • Nombre: Nombre del colegio.
    • Dirección: Ubicación física del colegio.
    • Tipo: Público o privado.

  2. Tabla: Estudiantes

    • ID estudiante: Identificador único del estudiante (clave primaria).
    • Nombre: Nombre del estudiante.
    • Edad: Edad del estudiante.
    • ID colegio: Identificador del colegio al que pertenece (clave foránea).

  3. Tabla: Profesores

    • ID profesor: Identificador único del profesor (clave primaria).
    • Nombre: Nombre del profesor.
    • Asignatura: Materia que enseña.
    • ID colegio: Identificador del colegio donde trabaja (clave foránea).

  4. Tabla: Clases

    • ID clase: Identificador único de la clase (clave primaria).
    • Nombre: Nombre de la clase o curso.
    • ID profesor: Identificador del profesor que la imparte (clave foránea).
    • ID colegio: Identificador del colegio al que pertenece (clave foránea).

Colegios

Ejercicio 2: Base de datos de Facturas

Descripción de la estructura:

  1. Tabla: Clientes

    • ID cliente: Identificador único del cliente (clave primaria).
    • Nombre: Nombre completo del cliente.
    • Teléfono: Número de contacto del cliente.
    • Correo electrónico: Dirección de correo electrónico.

  2. Tabla: Facturas

    • ID factura: Identificador único de la factura (clave primaria).
    • Fecha: Fecha de emisión de la factura.
    • ID cliente: Identificador del cliente asociado (clave foránea).
    • Total: Monto total de la factura.

  3. Tabla: Productos

    • ID producto: Identificador único del producto (clave primaria).
    • Nombre: Nombre del producto.
    • Precio: Precio unitario del producto.

  4. Tabla: DetallesFactura

    • ID detalle: Identificador único del detalle (clave primaria).
    • ID factura: Identificador de la factura asociada (clave foránea).
    • ID producto: Identificador del producto asociado (clave foránea).
    • Cantidad: Número de unidades compradas.

Facturas

Ejercicio 3: Base de datos de Vuelos

Descripción de la estructura:

  1. Tabla: Aeropuertos

    • ID aeropuerto: Identificador único del aeropuerto (clave primaria).
    • Nombre: Nombre del aeropuerto.
    • Ciudad: Ciudad donde se encuentra.
    • País: País donde se encuentra.

  2. Tabla: Vuelos

    • ID vuelo: Identificador único del vuelo (clave primaria).
    • ID aeropuerto origen: Identificador del aeropuerto de origen (clave foránea).
    • ID aeropuerto destino: Identificador del aeropuerto de destino (clave foránea).
    • Duración: Duración del vuelo en horas.
    • Capacidad: Número total de asientos.

  3. Tabla: Pasajeros

    • ID pasajero: Identificador único del pasajero (clave primaria).
    • Nombre: Nombre del pasajero.
    • Pasaporte: Número de pasaporte del pasajero.

  4. Tabla: Reservas

    • ID reserva: Identificador único de la reserva (clave primaria).
    • ID vuelo: Identificador del vuelo reservado (clave foránea).
    • ID pasajero: Identificador del pasajero que hizo la reserva (clave foránea).
    • Asiento: Número de asiento asignado.

Vuelos

Ejercicio 4: Base de datos de Juegos y Usuarios

Descripción de la estructura:

  1. Tabla: Usuarios

    • ID usuario: Identificador único del usuario (clave primaria).
    • Nombre: Nombre del usuario.
    • Correo electrónico: Dirección de correo electrónico del usuario.
    • Edad: Edad del usuario.

  2. Tabla: Juegos

    • ID juego: Identificador único del juego (clave primaria).
    • Nombre: Título del juego.
    • Categoría: Género del juego (por ejemplo, aventura, acción, etc.).

  3. Tabla: Compras

    • ID compra: Identificador único de la compra (clave primaria).
    • ID usuario: Identificador del usuario que hizo la compra (clave foránea).
    • ID juego: Identificador del juego comprado (clave foránea).
    • Fecha de compra: Fecha en que se realizó la compra.

  4. Tabla: Progreso

    • ID progreso: Identificador único del progreso (clave primaria).
    • ID usuario: Identificador del usuario (clave foránea).
    • ID juego: Identificador del juego (clave foránea).
    • Nivel alcanzado: Último nivel alcanzado por el usuario en el juego`.

Juegos

Ejercicio 5: Modificaciones con ALTER

Ejercicio 5.1: Colegios

  1. Cambiar el nombre del campo "direccion" de la tabla Colegios a "ubicacion".
  2. Añadir un nuevo campo "fecha_fundacion" en la tabla Colegios para almacenar la fecha de creación del colegio.
  3. Modificar el tipo de dato del campo "edad" de la tabla Estudiantes para que acepte valores decimales.

Ejercicio 5.2: Facturas

  1. Cambiar el nombre del campo "telefono" de la tabla Clientes a "numero_contacto".
  2. Añadir un nuevo campo "direccion_envio" en la tabla Facturas para incluir la dirección de entrega.
  3. Modificar el tipo de dato del campo "precio" de la tabla Productos para que acepte hasta tres decimales.

Ejercicio 5.3: Vuelos

  1. Cambiar el nombre del campo "duracion" de la tabla Vuelos a "tiempo_vuelo".
  2. Añadir un nuevo campo "clase" en la tabla Reservas para indicar la clase del asiento (económica, ejecutiva, etc.).
  3. Modificar el tipo de dato del campo "capacidad" de la tabla Vuelos para que acepte valores mayores.

Ejercicio 5.4: Juegos y Usuarios

  1. Cambiar el nombre del campo "categoria" de la tabla Juegos a "genero".
  2. Añadir un nuevo campo "fecha_ultimo_login" en la tabla Usuarios para registrar la última conexión del usuario.
  3. Modificar el tipo de dato del campo "nivel_alcanzado" de la tabla Progreso para que acepte valores decimales.

Ejercicio 6: Modificaciones avanzadas

Ejercicio 6.1: Modificaciones avanzadas

  1. En la tabla Estudiantes de la base de datos Colegios, cuando cambie la clave de Colegiosque también cambie la de Estudiantes.
  2. En la tabla DetallesFactura de la base de datos Facturas, cuando cambie la clave de Facturas que tmabién cambie la de DetallesFactura.
  3. En la tabla Reservas de la base de datos Vuelos, que cuando se elimine un vuelo se eliminen también todas sus reservas.

Ejercicio 6.2: Uso de claves foráneas

  1. En la tabla Usuarios de la base de datos Juegos y Usuarios, añadir una clave foránea que haga referencia a la tabla Compras.
  2. En la tabla Progreso de la base de datos Juegos y Usuarios, añadir una clave foránea que haga referencia a la tabla Juegos.
  3. En la tabla Compras de la base de datos Juegos y Usuarios, añadir una clave foránea que haga referencia a la tabla Usuarios.

Ejercicio 6.3: Uso de índices y constraints

  1. Crear un índice único en el campo correo_electronico de la tabla Usuarios en la base de datos Juegos y Usuarios.
  2. Añadir una restricción CHECK en la tabla Productos de la base de datos Facturas para que el precio sea mayor que 0.
  3. Eliminar el índice creado en el campo correo_electronico de la tabla Usuarios en la base de datos Juegos y Usuarios.

Soluciones crear bases de datos y tablas:

Ejercicio 1: Colegios 
CREATE DATABASE Colegios;
USE Colegios;

CREATE TABLE Colegios (
    id_colegio INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    direccion VARCHAR(255),
    tipo ENUM('Público', 'Privado') NOT NULL
);

CREATE TABLE Estudiantes (
    id_estudiante INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    edad INT NOT NULL,
    id_colegio INT,
    FOREIGN KEY (id_colegio) REFERENCES Colegios(id_colegio)
);

CREATE TABLE Profesores (
    id_profesor INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    asignatura VARCHAR(100),
    id_colegio INT,
    FOREIGN KEY (id_colegio) REFERENCES Colegios(id_colegio)
);

CREATE TABLE Clases (
    id_clase INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    id_profesor INT,
    id_colegio INT,
    FOREIGN KEY (id_profesor) REFERENCES Profesores(id_profesor),
    FOREIGN KEY (id_colegio) REFERENCES Colegios(id_colegio)
);
Ejercicio 2: Facturas 
CREATE DATABASE Facturas;
USE Facturas;

CREATE TABLE Clientes (
    id_cliente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    telefono VARCHAR(20),
    correo_electronico VARCHAR(100)
);

CREATE TABLE Facturas (
    id_factura INT AUTO_INCREMENT PRIMARY KEY,
    fecha DATE NOT NULL,
    id_cliente INT,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente)
);

CREATE TABLE Productos (
    id_producto INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10, 2) NOT NULL
);

CREATE TABLE DetallesFactura (
    id_detalle INT AUTO_INCREMENT PRIMARY KEY,
    id_factura INT,
    id_producto INT,
    cantidad INT NOT NULL,
    FOREIGN KEY (id_factura) REFERENCES Facturas(id_factura),
    FOREIGN KEY (id_producto) REFERENCES Productos(id_producto)
);
Ejercicio 3: Vuelos 
CREATE DATABASE Vuelos;
USE Vuelos;

CREATE TABLE Aeropuertos (
    id_aeropuerto INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    ciudad VARCHAR(100),
    pais VARCHAR(100)
);

CREATE TABLE Vuelos (
    id_vuelo INT AUTO_INCREMENT PRIMARY KEY,
    id_aeropuerto_origen INT,
    id_aeropuerto_destino INT,
    duracion DECIMAL(5, 2),
    capacidad INT NOT NULL,
    FOREIGN KEY (id_aeropuerto_origen) REFERENCES Aeropuertos(id_aeropuerto),
    FOREIGN KEY (id_aeropuerto_destino) REFERENCES Aeropuertos(id_aeropuerto)
);

CREATE TABLE Pasajeros (
    id_pasajero INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    pasaporte VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Reservas (
    id_reserva INT AUTO_INCREMENT PRIMARY KEY,
    id_vuelo INT,
    id_pasajero INT,
    asiento VARCHAR(10),
    FOREIGN KEY (id_vuelo) REFERENCES Vuelos(id_vuelo),
    FOREIGN KEY (id_pasajero) REFERENCES Pasajeros(id_pasajero)
);
Ejercicio 4: Juegos y Usuarios 
CREATE DATABASE JuegosUsuarios;
USE JuegosUsuarios;

CREATE TABLE Usuarios (
    id_usuario INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    correo_electronico VARCHAR(100) UNIQUE NOT NULL,
    edad INT NOT NULL
);

CREATE TABLE Juegos (
    id_juego INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    categoria VARCHAR(50)
);

CREATE TABLE Compras (
    id_compra INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    id_juego INT,
    fecha_compra DATE NOT NULL,
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario),
    FOREIGN KEY (id_juego) REFERENCES Juegos(id_juego)
);

CREATE TABLE Progreso (
    id_progreso INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    id_juego INT,
    nivel_alcanzado INT,
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario),
    FOREIGN KEY (id_juego) REFERENCES Juegos(id_juego)
);

Soluciones modificaciones (ALTER)

Solución 5.1: Colegios 
-- Cambiar el nombre del campo "direccion" a "ubicacion"
ALTER TABLE Colegios RENAME COLUMN direccion TO ubicacion;

-- Añadir un nuevo campo "fecha_fundacion"
ALTER TABLE Colegios ADD COLUMN fecha_fundacion DATE;

-- Modificar el tipo de dato del campo "edad" para aceptar decimales
ALTER TABLE Estudiantes MODIFY COLUMN edad DECIMAL(5,2);
Solución 5.2: Facturas 
-- Cambiar el nombre del campo "telefono" a "numero_contacto"
ALTER TABLE Clientes RENAME COLUMN telefono TO numero_contacto;

-- Añadir un nuevo campo "direccion_envio"
ALTER TABLE Facturas ADD COLUMN direccion_envio VARCHAR(255);

-- Modificar el tipo de dato del campo "precio" para aceptar tres decimales
ALTER TABLE Productos MODIFY COLUMN precio DECIMAL(10,3);
Solución 5.3: Vuelos 
-- Cambiar el nombre del campo "duracion" a "tiempo_vuelo"
ALTER TABLE Vuelos RENAME COLUMN duracion TO tiempo_vuelo;

-- Añadir un nuevo campo "clase"
ALTER TABLE Reservas ADD COLUMN clase ENUM('Económica', 'Ejecutiva', 'Primera') NOT NULL;

-- Modificar el tipo de dato del campo "capacidad" para aceptar valores mayores
ALTER TABLE Vuelos MODIFY COLUMN capacidad BIGINT;
Solución 5.4: Juegos y Usuarios 
-- Cambiar el nombre del campo "categoria" a "genero"
ALTER TABLE Juegos RENAME COLUMN categoria TO genero;

-- Añadir un nuevo campo "fecha_ultimo_login"
ALTER TABLE Usuarios ADD COLUMN fecha_ultimo_login DATETIME;

-- Modificar el tipo de dato del campo "nivel_alcanzado" para aceptar valores decimales
ALTER TABLE Progreso MODIFY COLUMN nivel_alcanzado DECIMAL(5,2);

Soluciones 6: Modificaciones avanzadas

Ejemplo de salida: SHOW INDEX FROM Estudiantes

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
Estudiantes 0 PRIMARY 1 id_estudiante A 10 NULL NULL BTREE
Estudiantes 1 fk_colegio 1 id_colegio A 10 NULL NULL YES BTREE
Solución 6.1: UModificaciones avabzadas 
-- Modificar claves foráneas con ON UPDATE CASCADE
ALTER TABLE Estudiantes DROP FOREIGN KEY fk_estudiantes_colegios;
ALTER TABLE Estudiantes ADD CONSTRAINT fk_estudiantes_colegios FOREIGN KEY (id_colegio) REFERENCES Colegios(id_colegio) ON UPDATE CASCADE;

ALTER TABLE DetallesFactura DROP FOREIGN KEY fk_detallesfactura_productos;
ALTER TABLE DetallesFactura ADD CONSTRAINT fk_detallesfactura_productos FOREIGN KEY (id_producto) REFERENCES Productos(id_producto) ON UPDATE CASCADE;

ALTER TABLE Reservas DROP FOREIGN KEY fk_reservas_vuelos;
ALTER TABLE Reservas ADD CONSTRAINT fk_reservas_vuelos FOREIGN KEY (id_vuelo) REFERENCES Vuelos(id_vuelo) ON UPDATE CASCADE;
Solución 6.3: Uso de índices y constraints 
-- Uso de índices y constraints
CREATE UNIQUE INDEX idx_usuarios_correo ON Usuarios(correo_electronico);
ALTER TABLE Productos ADD CONSTRAINT chk_precio CHECK (precio > 0);
DROP INDEX idx_usuarios_correo ON Usuarios;