Crear consultas para base de datos Mysql

[gnzsoloyo]

Bueno, recién ahora la cosa se pone interesante. SIn códigos es todo muy aburrido.... Lo siente.

Para poder entender como se resuelven las cosas en BBDD y SQL, tienes que razonar de modo algo diferente a lo que haces en programación. Eso tenlo presente porque es una de las primeras reglas que te enseñan en la asignatura, cuando la cursas en la universidad. Y es cierto.
No empieces tratando de resolver todo el problema, como si se tratase de un proceso que hay que resolver. No existe tal.
Empieza siempre por separar los bloques de información que se indican, de modo que queden los conceptos simples. Esto quiere decir que primero debes ver que bloques de datos que intervienen en la consulta pueden ser obtenidos en consultas de menor complejidad.

Vamos a los problemas y las soluciones que planteas, para que se entienda la idea.

El primero dice así:

Ciudades entre las que se haya programado el viaje de mayor duración junto con el vehículo y dni del conductor que lo hizo,

En este caso te piden tres cosas claras: a) Ciudades relacionadas con viajes; b) viaje más largos de todos, y c) conductores con sus DNI.

No pienses la consulta completa. Piensa cada parte de eso.

Voy a comenzar creando las tablas a partir de tu descripción pero las voy a normalizar un poco por necesidades de simpleza de consultas.

Código MySQL:

CREATE TABLE CONDUCTOR(
    DNI INT UNSIGNED NOT NULL PRIMARY KEY, 
    NOMBRE VARCHAR (30) NOT NULL, 
    DIREC VARCHAR (150) NOT NULL,
    PROV_ID INT UNSIGNED NOT NULL);
CREATE TABLE VEHICULO(
    MATRICULA VARCHAR(10) NOT NULL PRIMARY KEY, 
    CARGAMAX MEDIUMINT UNSIGNED NOT NULL, 
    FECHACOMPRA DATE NOT NULL);
CREATE TABLE RUTA(
    RUTA_ID INT UNSIGNED NOT NULL PRIMARY KEY, 
    CIUDADSAL_ID MEDIUMINT UNSIGNED NOT NULL,
    CIUDADLLEG_ID MEDIUMINT UNSIGNED NOT NULL, 
    KM DECIMAL (14,2) NOT NULL);
CREATE TABLE VIAJE_PROGRAMADO(
    VIAJE_ID INT UNSIGNED NOT NULL PRIMARY KEY, 
    RUTA_ID INT UNSIGNED NOT NULL, 
    FECHASAL DATE NOT NULL, 
    HORASAL TIME NOT NULL, 
    FECHALLEG DATE NOT NULL, 
    HORALLEG TIME NOT NULL);
CREATE TABLE VIAJE_ASIGNADO(
    VIAJE_ID INT UNSIGNED NOT NULL, 
    DNI INT UNSIGNED NOT NULL, 
    MATRICULA VARCHAR(10),
    PRIMARY KEY(VIAJE_ID, DNI, MATRICULA));
 
# Tablas de uso recomendado.
CREATE TABLE CIUDAD(
    CIUDAD_ID INT UNSIGNED NOT NULL PRIMARY KEY, 
    PROV_ID INT UNSIGNED NOT NULL,
    NOMBRE_CIUDAD VARCHAR(150) NOT NULL);
 
CREATE TABLE PROVINCIA(
    PROV_ID INT UNSIGNED NOT NULL PRIMARY KEY, 
    NOMBRE_CIUDAD VARCHAR(150) NOT NULL);
 

No uses "#" para los nombres de objetos, es el caracter de remark o comentario y puede causar problemas en algun momento.

Caso 1) Ciudades entre las que se haya programado el viaje de mayor duración junto con el vehículo y dni del conductor que lo hizo,

Veamos lo simple: viajes de mayor duración:

Código MySQL:

SELECT viaje_id
FROM (
    SELECT 
        viaje_id, 
        (TIMESTAMPDIFF(MINUTE, adddate(fechasal, horasal), adddate(fechalleg, horalleg))) duracion
    FROM VIAJE_ASIGNADO) viajes
HAVING duracion = MAX(duracion);
 

En principio el having debería funcionar bien en mi experiencia, pero hay otras formas de escribir esa consulta, aunque algo más complejas.
Luego vamos a las egunda parte: Viaje asignado, con su chofer y ciudad.

Código MySQL:

SELECT VP.viaje_id, CO.dni DocConductor, CO.nombre NombreConductor, C1.nombre Origen, C2.nombre Destino
FROM viaje_asignado VA
    INNER JOIN viaje_programado VP ON VA.viaje_id = VP.viaje_id
    INNER JOIN ruta R ON VP.ruta_id = R.ruta_id
    INNER JOIN ciudad C1 ON R.ciudad_sal = C1.ciudad_id
    INNER JOIN ciudad C2 ON R.ciudad_lleg = C2.ciudad_id
    INNER JOIN conductor CO ON VA.dni = CO.dni;
 

Es una consulta algo compleja como primer ejercicio de SQL, eso te lo concedo. Quien te lo dio así está un poco alterado de las neuronas.

A esto hay que agregarle el filtrado anterior:

Código MySQL:

WHERE VP.iaje_id IN (SELECT viaje_id
                    FROM (
                    SELECT 
                            viaje_id, 
                            (TIMESTAMPDIFF(MINUTE, adddate(fechasal, horasal), adddate(fechalleg, horalleg))) duracion
                        FROM VIAJE_ASIGNADO) viajes
                    HAVING duracion = MAX(duracion));
 

Con lo que al final podría funcionar así:

Código MySQL:

SELECT VP.viaje_id, CO.dni DocConductor, CO.nombre NombreConductor, C1.nombre Origen, C2.nombre Destino
FROM viaje_asignado VA
    INNER JOIN viaje_programado VP ON VA.viaje_id = VP.viaje_id
    INNER JOIN ruta R ON VP.ruta_id = R.ruta_id
    INNER JOIN ciudad C1 ON R.ciudad_sal = C1.ciudad_id
    INNER JOIN ciudad C2 ON R.ciudad_lleg = C2.ciudad_id
    INNER JOIN conductor CO ON VA.dni = CO.dni
WHERE VP.iaje_id IN (SELECT viaje_id
                    FROM (
                    SELECT 
                            viaje_id, 
                            (TIMESTAMPDIFF(MINUTE, adddate(fechasal, horasal), adddate(fechalleg, horalleg))) duracion
                        FROM VIAJE_ASIGNADO) viajes
                    HAVING duracion = MAX(duracion));
 

Para mi es un ejercicio algo complicado para iniciarse...

Luego vemos el resto.