17 Ejercicios corregidos consultas SQL

Estos ejercicios de consulta SQL corregidos son para principiantes y usuarios avanzados.

consultas SQL

Ejercicio de flashback sobre el modelo entidad-asociación

Una agencia inmobiliaria quiere gestionar su cartera inmobiliaria. Quiere almacenar todos los alojamientos de alquiler en su base.

Para ello asigna un identificador a cada alojamiento, su dirección, su superficie y el alquiler. Una vivienda está situada en un barrio al que se le asigna un número y una etiqueta. Cada alojamiento corresponde a un tipo de alojamiento con cargos fijos asociados al mismo. Además, la agencia también abastece a los inquilinos de su parque. Estos se identifican mediante un número, se caracterizan por sus apellidos, nombres, fecha de nacimiento y número de teléfono. A una vivienda pueden estar asociados varios inquilinos. Por otro lado, un individuo sólo puede ser referenciado en un alojamiento.

Propondrás el modelo entidad-asociación de estas especificaciones

Y deducirás el modelo lógico identificando las claves primaria y externa.

Ejercicio preliminar para entender SQL

Una pequeña empresa creó una base de datos relacional para almacenar información sobre sus empleados y los departamentos a los que pertenecen. La base de datos contiene las siguientes tablas (las claves principales están subrayadas):

EMPLEADO(ID de empleado, apellido, nombre, profesión, fecha_contratación, salario, comisión, id_departamento) contiene la lista de empleados, definida por un identificador interno del sistema, su apellido (único), su nombre, su profesión (ejecutivo, gerente, ingeniero, comercial, técnico y pasante), su fecha de contratación, su salario (entero positivo, un pasante no puede recibir pago), su comisión (un empleado no puede tener comisión) y el identificador del departamento al que pertenece el empleado.

DEPARTAMENTO(id_departamento, nombre, director, ciudad) contiene la lista de departamentos definidos por un identificador único, su nombre (ventas, producción, desarrollo y recursos humanos), el identificador del empleado director del departamento y la ciudad, ubicación del departamento.

Aquí está el esquema de la base de datos en forma gráfica:

entidad asociativa

La base de datos contiene los siguientes datos:

entidad asociativa

Ejercicio 1

Para cada una de las siguientes consultas, proporcione su significado en francés (tenga cuidado de no parafrasear el código SQL) e indique el resultado.

 

1) SELECCIONE DEPARTAMENTO.departamento_id, DEPARTAMENTO.nombre

DEL DEPARTAMENTO

DONDE DEPARTAMENTO.Id_departement NO ESTÁ (SELECCIONE EMPLEADO.Id_departement DEL EMPLEADO);

2) SELECCIONE E2.nombre COMO “Nombre del Director”, CUENTA(E1.nombre) COMO “Número de Empleados”

DEL EMPLEADO E1, EMPLEADO E2, DEPARTAMENTO D

DONDE E1.Id_departemento = D.Id_departemento

Y E2.Id_empleado = D.Director

GRUPO POR E2.nombre

ORDENAR POR 1;

3) SELECCIONE nombre, salario DEL EMPLEADO

DONDE salario > TODOS (SELECCIONAR salario

DEL EMPLEADO

WHERE profesión = 'Ingeniero');

4) SELECCIONE E1.name COMO “Nombre del empleado”, E1.hire_date, E2.name COMO “Nombre del director”, E2.hire_dateDEL EMPLEADO E1, EMPLEADO E2, DEPARTAMENTO DONDE E2.Id_employe = D.DirectorAND E1.Id_departement = D.Id_departmentAND E1.Fecha_contratación < E2.Fecha_contratación;

1) ¿Qué departamentos no tienen empleados?

No hay filas seleccionadas

2) ¿Cuál es el número de empleados por director?

Nombre Director Número de Empleados

——————————       ————————–

jackson 3

Lavergne 1

McDonald's 4

Valiente 2

3) ¿Cuál es el salario y el nombre de los empleados que ganan más que todos los ingenieros?

Nombre Salario

—————————-          ————–

Furnon 6000

Babin 7000

McDonald's 10000

4) ¿Cuál es el nombre y fecha de contratación de los empleados contratados antes que su jefe? También indique el nombre y fecha de contratación de su director.

Nombre Empleado Fecha_contratación Nombre Director Fecha_contratación

———————-                ————————-                          ————————                    ———————-

Sutton 04/01/1989 McDonald 05/06/2001

Furnon 11-06-1989 McDonald 06-05-2001

Babin 08-08-2000 McDonald 05-06-2001

Ejercicio 2

Para cada consulta indique si (Sí o No) da el resultado solicitado (varias consultas pueden dar el resultado correcto)

6) Indique el salario y el nombre de los empleados que ganan más que el salario más bajo de los ingenieros:.

6a SELECCIONE DISTINTO E1.nombre, E1.salario DEL EMPLEADO E1, EMPLEADO E2 DONDE E2.Profesión = 'Ingeniero'Y E1.salario > MIN(E2.salario);

6b SELECCIONE nombre, salario DE EMPLEADO DONDE salario > TODOS (SELECCIONE MIN(salario) DE EMPLEADO DONDE profesión = 'Ingeniero');

6c SELECCIONE nombre, salario DEL EMPLEADO DONDE salario < CUALQUIER (SELECCIONE MIN(salario) DEL EMPLEADO

WHERE profesión = 'Ingeniero');

  • Indique los nombres de los empleados con la misma profesión y el mismo director que 'Furnon':

7a SELECCIONE nombre, profesiónDEL EMPLEADODONDE nombre <> 'Furnon'Y Id_departement EN (SELECCIONE D2.Id_departementDEL EMPLEADO E, DEPARTAMENTO D1, DEPARTAMENTO D2DONDE E.name = 'Furnon'Y D1.Id_departement = E.Id_departementAND D1.Directeur = D2.Directeur );

7b SELECCIONE E.nomDEL EMPLEADO E, DEPARTAMENTO DONDE E.nombre <> 'Furnon' AND E.Id_departement = D.Id_departementAND (profesión, director) = (SELECCIONE profesión, D1.directorDEL EMPLEADO E1, DEPARTAMENTO D1, DEPARTAMENTO D2DONDE E1.nombre = 'Furnon'Y E1.Id_departement = D1.Id_departementY D1.Directeur = D2.Directeur);

8) Indique los nombres de los empleados del departamento 'Comercial' contratados el mismo día que un empleado del departamento 'Producción':

8a SELECCIONE DISTINTO E1.nombre DEL EMPLEADO E1, DEPARTAMENTO D1, EMPLEADO E2, DEPARTAMENTO D2 DONDE E1.Id_departement = D1.Id_departementY E2.Id_departement = D2. Id_departement AND D1.name = 'Comercial'AND D2.name = 'Producción'AND E1.Date_hire = E2.Date_hire; 

 8b SELECCIONE E.nombreDEL EMPLEADO E, DEPARTAMENTO DDONDE E.Id_departamento = D.Id_departamentoY D.nombre = 'Comercial'Y E.Fecha_contratación EN (SELECCIONE E1.Fecha_contrataciónDEL EMPLEADO E1, DEPARTAMENTO D1DONDE E1. Id_departemento = D1. Id_departementoY D1.nombre = 'Producción');

8c SELECCIONE E.nombreDEL EMPLEADO E, DEPARTAMENTO DDONDE E.Id_departemento = D.Id_departementoY D.nombre = 'Comercial'Y E.Fecha_contratación EXISTE (SELECCIONE E1.Fecha_contrataciónDEL EMPLEADO E1, DEPARTAMENTO D1DONDE E1. Id_departemento = D1. Id_departementoY D1.nombre = 'Producción');

9) Indique los nombres de los empleados con salario máximo en cada departamento:

9a SELECCIONE nombre DEL EMPLEADO DONDE (Id_departamento, salario) EN (SELECCIONE Id_departamento, MAX (salario) DE EMPLEADO GRUPO POR Id_departamento); 

 9b SELECCIONE nombre DEL EMPLEADO EN DONDE salario = (SELECCIONE MAX (salario) DEL EMPLEADO E1 DONDE E1. Id_departement = E. Id_departement);

9c SELECCIONE E1.nombre, E1.departamento_id, MAX(E1.salario)

                DEL EMPLEADO E1, EMPLEADO E2

                DONDE E1. Id_departamento = E2. id_departamento

                GRUPO POR E1.nombre, E1.id_departamento;

6a NO

6b SÍ

6c Sí

7a NO

7b SÍ

8a SÍ

 8bOinterfaz de usuario

8c NO

9a SÍ

9b SÍ

9c Sí

Ejercicio 3

11) Proporcione los comandos SQL para mostrar la lista de todos los departamentos con el apellido, nombre, salario e identificador de los empleados asignados a ellos. Tenga en cuenta: un departamento no puede tener ningún empleado.

12) Proporcione los comandos SQL para mostrar la lista de trabajos con el salario promedio más bajo; Indique también su salario medio.

13) Proporcione los comandos SQL para mostrar los empleados del departamento de 'Producción' cuyo salario es inferior a 5000 $ y los empleados del departamento 'Comercial' cuyo salario excede el salario promedio.

11)

SELECCIONE D.apellido, E.employee_id, E.apellido, E.nombre, E.salario

DEL EMPLEADO E, DEPARTAMENTO D

DONDE D.Id_departemento = E.Id_departemento(+)

ORDENAR POR D.apellido, E.apellido, E.nombre;

12) 

 SELECCIONE profesión, PROMEDIO(salario) DE EMPLEADOGRUPO POR profesiónTENIENDO PROMEDIO(salario) = (SELECCIONE MIN(PROMEDIO(salario)) DE EMPLEADO

GRUPO POR profesión);

13)    

SELECCIONE E1.Id.Empleado, E1.Nombre, E1.Salario

DEL EMPLEADO E1, DEPARTAMENTO D1

DONDE D1.Id_departemento = E1.Id_departemento

Y D1.Nombre = 'producción'

Y E1.Salario < 5000

UNIÓN

SELECCIONE E2.Id.Empleado, E2.Nombre, E2.Salario

DEL EMPLEADO E2, DEPARTAMENTO D2

DONDE D2.Id_departemento = E2.Id_departemento

Y D2.Nombre = 'comercial'

Y E2.Salario > (SELECCIONE PROMEDIO(E3.Salario) DEL EMPLEADO E3)

ORDENAR POR 1, 2;

Ejercicio 4

Proporcionará los comandos SQL para crear las tablas de la base de datos, incluidas todas las restricciones establecidas que se pueden especificar en el comando de creación de tablas. Tenga en cuenta que al eliminar un departamento se deben eliminar todos sus empleados.

CREAR TABLA DE DEPARTAMENTOS (

Id_department NUMÉRICO (3) CLAVE PRIMARIA,

Nombre VARCHAR2 (30) CHECK (Nombre IN ('Comercial', 'Desarrollo', 'Producción', 'Recursos Humanos')),

Director NUMÉRICO (6) NO NULO ÚNICO,

Ciudad VARCHAR2 (20) NO NULO

);

CREAR TABLA DE EMPLEADOS (

Id_employee CLAVE PRIMARIA NUMÉRICA (6),

Nombre VARCHAR2 (30) NO NULO ÚNICO,

Nombre VARCHAR2 (30) NO NULO,

Profesión VARCHAR2 (30) NOT NULL CHECK (Profesión IN ('Ejecutivo', 'Gerente', 'Ingeniero', 'Vendedor', 'Técnico', 'Becario')),

Hire_date FECHA NO NULA,

Salario INT NO NULL CHECK (Salario >= 0),

Comisión INT DEFAULT 0,

Id_departement RESTRICCIÓN NUMÉRICA (3) fk_employe_departement DEPARTAMENTO DE REFERENCIAS (Id_departamento) AL ELIMINAR CASCADA

);

BDD Coches.txt

La base de datos utilizada modela una actividad de alquiler de coches entre particulares: coches

La información guardada en la base de datos es información sobre los coches, los propietarios de estos coches, los clientes y los alquileres realizados.

Se toma el siguiente diagrama relacional:

CARRO (immat, modelo, marca, categoría, color, lugares, compraA, contador, precioJ, #codeP )

DUEÑO (códigoP, apodo, correo electrónico, ciudad, añoI)

CLIENTE (códigoC, apellido, nombre, edad, licencia, dirección, ciudad)

ALQUILER (#CodeC, #immat, año, mes,numLoc, km, duración, ciudadD, ciudadA, fechaD, fechaF)

Las claves principales están en negrita/subrayadas; las claves foráneas están precedidas por un#

Ejercicio 1

  1. ¿Qué marca es el vehículo modelo T550?
  2. ¿Cuáles son los apodos de los dueños de París?
  3. ¿En qué marcas se encuentran los vehículos de la categoría 'lujo'? ¿Qué debo utilizar para evitar duplicados?
  4. ¿Cuáles son las matriculaciones de descapotables de 4 plazas?
  5. ¿Cuántas categorías diferentes de automóviles hay?
  6. ¿Cuáles son las diferentes categorías de automóviles?
  7. ¿Qué modelos de Peugeot están disponibles para alquilar?
  8. Lista (pseudo) de propietarios con una dirección de correo electrónico nula 
  9. ¿Cuál es el kilometraje del vehículo `75AZ92′?
  10. ¿Qué clientes (sólo el nombre) viven en "Nantes"?
  11. ¿Usar DISTINCT fue una buena opción? Y por qué ?

seleccione la marca del automóvil donde modelo = 'T550'; —Ferrari
seleccione el apodo del propietario donde ciudad = 'París'; - (4 filas devueltas)
seleccione la marca del automóvil donde categoría = 'Lujo'; - (3 filas devueltas, 1 con distinta)
seleccione immat del automóvil donde categoría = 'convertible' y plazas = 4; - (1 fila devuelta)
seleccione el recuento (categoría distinta) del automóvil; — (7)
seleccione una categoría distinta del automóvil; - (7 filas devueltas)
seleccione el modelo del automóvil donde marca = 'Peugeot'; - (5 filas devueltas)
seleccione el apodo del propietario donde el correo electrónico es nulo; - (2 filas devueltas)
seleccione el contador del automóvil donde immat =’75AZ92′; — 17560
seleccione el nombre del cliente donde ciudad ='Nantes';
Advertencia DISTINCT borra homónimos

Ejercicio 2

  1. Listado de vehículos (immat) por año de compra (del más nuevo al más antiguo).
  2. Verifique agregando el año de compra a la lista
  3. Listado de marcas y modelos ordenados por marca y modelo.
  4. Listado de coches con al menos 4 plazas (marca, modelo, matrícula, plazas) en orden decreciente de plazas.
  5. Listado de ciudades de propietarios (sin duplicados y en orden alfabético).
  6. Listado de alquileres (lista de matriculaciones de vehículos) clasificados en orden descendente de km recorridos

seleccione immat del pedido del automóvil mediante compraA; - (20 filas devueltas)
seleccione immat, compreA del pedido del automóvil por compraA; - (20 filas devueltas)

seleccione una marca y modelo distintos del automóvil, ordene por marca y modelo; - (15 filas devueltas)
seleccione marca, modelo, piso, plazas del coche donde plazas >= 4 ordenar por plazas DESC; - (10 filas devueltas)
seleccione una ciudad distinta del orden del propietario por ciudad ASC; - (se devolvieron 6 filas)
seleccione immat, km desde la ubicación ordenar por km DESC; - (133 filas devueltas)

Ejercicio 3

1) ¿Lista de nombres de clientes en Lyon?

lo mismo sin los duplicados.

¿Cuántos clientes de Lyon hay? (recuento(*), recuento(nombre), recuento(nombre distinto))

¿Observa los resultados y explica las diferencias entre los 3 resultados obtenidos?

2) Mismas preguntas pero con nombres.

¿Cuántas parejas diferentes (apellido, nombre) hay en la base de datos (todas las ciudades juntas?

— 1
seleccione el nombre del cliente donde ciudad ='Lyon';
seleccione un nombre distinto del cliente donde ciudad ='Lyon';
seleccione recuento (nombre) del cliente donde ciudad = 'Lyon';
seleccione recuento (*) del cliente donde ciudad ='Lyon';
seleccione recuento (nombre distinto) del cliente donde ciudad = 'Lyon';
— 2
seleccione apellido, nombre del cliente donde ciudad = 'Lyon';
seleccione recuento (*) del cliente donde ciudad ='Lyon';
seleccione recuento (nombre) del cliente donde ciudad = 'Lyon';
seleccione recuento (nombre distinto) del cliente donde ciudad = 'Lyon';
seleccione apellido, nombre del cliente donde el nombre es NULL y ciudad ='Lyon';

Ejercicio 4

  1. ¿Cuántos convertibles rojos o azules hay?
  2. ¿Cuántos coches hay en la categoría (familiar con menos de 50.000 km, o utilitario), blancos o negros?
  3. Listado (immat, marca, modelo) de los modelos de las marcas Peugeot y Citroen mostrados por marca y modelo
  4. Lista de clientes de París mayores de 50 años por orden alfabético
  5. Lista (immat) de vehículos blancos excepto descapotables 
  6. ¿Cuántos vehículos de las categorías de lujo y premium hay antes de 2012 (2012 incluido)?
  7. Lista de alquileres (número de alquiler, con ciudad, año, mes) que comenzaron y terminaron en la misma ciudad en orden cronológico (el más reciente primero)
  8. Lista de automóviles que datan de 2010 a 2012 (2010 y 2012 inclusive)
  9. Listado de coches de las marcas Renault, Peugeot o Citroen (uso IN).
  10. ¿Cuántos propietarios no viven ni en París, ni en Lyon, ni en Nantes? 
  11. Lista de clientes que alquilaron sin licencia

                1 Listado de clientes (códigoC) que han realizado alquileres.

                2 Listado de clientes (códigoC) que tienen número de permiso 

                3 Listado de clientes (códigoC) que no tienen número de licencia 

                4 Listado de clientes (codeC) que alquilaron sin número de licencia

12 Listado (nombre y apellido) de clientes que viven en la misma ciudad que un propietario 

13 Lista (immat) de coches que se han alquilado (al menos una vez)

14 Lista (immat) de coches que nunca han sido alquilados

— 1
seleccione recuento (*) del automóvil donde categoría = “convertible” y (color = 'rojo' o color = 'azul'); — 1
— 2
seleccione una categoría distinta del automóvil; - (7 filas devueltas)
seleccione el recuento (*) del automóvil donde ((categoría ='familia' y contador <= 50000) o (categoría ='utilidad')) y (color = 'negro' o color ='blanco'); — 4
— 3
seleccionar una marca distinta del automóvil ordenar por marca; – (se devolvieron 6 filas)
seleccione marca, modelo, immat del automóvil donde marca = 'Peugeot' o 'Citroen' ordene por marca, modelo; - (8 filas devueltas)
— 4
seleccione apellido, nombre, edad del cliente donde ciudad ='París' y edad > 50 ordenar por apellido; - (1 filas devueltas)
— 5
seleccione immat del automóvil donde color = 'blanco' y categoría <> 'convertible'; - (8 filas devueltas)
— 6
seleccione recuento (*) del automóvil donde (categoría = 'lujo' o categoría = 'premium') y compra A <= 2012; - (se devolvieron 6 filas)
— 7
seleccione numLoc, ciudadD, año, mes desde la ubicación donde ciudadD = ciudadA ordene por año DESC, mes DESC;– (se devolvieron 67 filas)
— 8-9-10
seleccione immat del automóvil donde compró A entre 2010 y 2012; - (8 filas devueltas)
seleccione immat del automóvil donde marca ('Renault', 'Peugeot', 'Citroen'); - (16 filas devueltas)
seleccione el apodo del propietario en la ciudad que no esté ('París', 'Lyon', 'Nantes'); - (5 filas devueltas)
— 11
seleccione codeC desde la ubicación;
seleccione codeC del cliente donde el permiso no es nulo;
seleccione codeC del cliente donde el permiso es nulo;
seleccione codeC distinto, permitido por el cliente 
donde está el código C (seleccione el código C de la ubicación)
y el permiso es nulo; - (2 filas devueltas)
— otra versión
 
seleccione codeC distinto, permitido por el cliente 
donde existe (seleccione el código C de la ubicación)
y el permiso es nulo; 
— otra versión pero sin el número de licencia
seleccione un código distinto de la ubicación 
donde está codeC (seleccione codeC del cliente donde el permiso es nulo); 
 
— 12
seleccione apellido, nombre, ciudad del cliente 
dónde está la ciudad (seleccione la ciudad del propietario); - (18 filas devueltas)
— 13
seleccionar distintos immat de la ubicación; - (20 filas devueltas)
 
— 14
seleccionar distintos immat del coche 
donde immat no está (seleccione immat distinto 
desde la ubicación); - (1 filas devueltas)
 
 seleccione un immat distinto del automóvil donde no existe (
seleccione * desde la ubicación 
donde ubicación.immat = coche.immat); - (1 filas devueltas)

Ejercicio 5

  1. ¿Cuál es el apodo del propietario del coche '56AA46′?
  2. ¿Cuántos alquileres hay para un Peugeot 205?
  3. Listado (marca, modelo, matrícula, número de alquiler) de coches alquilados en 4/2015.
  4. Listado (marca, modelo, matrícula, número de alquiler) de los coches alquilados en el año de su compra. 
  5. Lista de pegatinas de vehículos Peugeot
  6. Lista de immats de vehículos Peugeot e información de alquiler correspondiente por año, mes e immat 
  7. ¿La lista de clientes que han alquilado un vehículo Ferrari?
  8. ¿Qué modelos de Peugeot se han alquilado?
— 1
seleccione apodo 
del propietario p, coche v — Jules
donde immat = '56AA46′ 
y p.codeP = v.codeP;
— 2
seleccionar recuento(*) 
desde la ubicación l, coche v — 11
donde l.immat = v.immat
y v.marque= 'Peugeot' 
y v.model = '205';
— 3
seleccione v.brand, v.model, v.immat, l.numLoc– (se devolvieron 6 filas)
del coche v, alquiler l 
donde v.immat=l.immat
y l.año = '2015' 
y l.mes = '4';
— 4
seleccione v.brand, v.model, v.immat, l.numLoc– (se devolvieron 11 filas)
del coche v, alquiler l 
donde v.immat = l.immat
y l.año = v.compraA;
— 5
seleccionar immat distinto - (se devolvieron 5 filas)
desde el coche 
donde marca ='Peugeot';
— 6
seleccione l.* — (se devuelven 30 filas)
del coche v, alquiler l
donde v.immat = l.immat
y marca = 'Peugeot'
ordenar por l.año, l.mes, l.immat;
— 7
Seleccione c.apellido, c.nombre, v.marca, v.modelo (se devolvieron 13 filas)
desde la ubicación l, coche v, cliente c  
donde c.códigoC = l.códigoC
y l.immat = v.immat
y v.marca = 'Ferrari';

Ejercicio 6

  1. Listado de marcas con más de 3 coches disponibles para alquiler
  2. Relación de immat de vehículos que han recorrido más de 800 km en alquiler
  3. Lista de modelos de coches que se han alquilado al menos 3 veces

— 1: lista de marcas con más de 3 (>) coches disponibles para alquiler
marca de selección - (se devuelven 2 filas)
desde el coche
grupo por marca
tener recuento(*) > 3;

— 2: lista de immats de coches que han recorrido más de 800 km en alquiler
seleccione immat - (se devolvieron 15 filas)
desde la ubicación
grupo por immat
teniendo suma(km) > 800;

— 3: modelos de coche que se han alquilado al menos 3 veces (con número de alquileres)
— mostrado en orden alfabético por modelo
seleccione v.model, count(l.numloc) - (se devolvieron 15 filas)
desde la ubicación l ÚNETE al coche v ON l.immat = v.immat
grupo por v.modele
teniendo recuento (l.numloc) >= 3
ordenar por v.modelo;

Ejercicio 7

  1. Kms recorridos en alquiler por Alain Delon, coche (immat) por coche
  2. Marcas y km medios recorridos en alquiler (por marca)
  3. ¿Cuál es el coche más antiguo? (con MÍN)
  4. ¿Qué coche tiene más kilómetros en el cuentakilómetros que todos los Peugeot? (con toda)
  5. ¿Qué coche/s tienen un precio diario más alto que un Ferrari?
  6. Muestra la lista de automóviles con un kilometraje superior al kilometraje promedio general.
  7. ¿Lista de automóviles con un odómetro de kilómetros superior al odómetro de kilómetros promedio de su modelo?
— 1: KM realizados en régimen de alquiler con el coche de Alain Delon (immat) por coche
seleccione immat, suma (km) - (se devolvieron 4 filas)
desde el cliente c ÚNETE a la ubicación l en c.codeC = l.codeC 
donde nombre = 'Delon'
grupo por immat; 
 
— 2a: marcas y km medios recorridos en alquiler por marca
seleccione v.marque, avg(l.km) — (se devolvieron 6 filas)
desde la ubicación l, coche v 
donde l.immat = v.immat 
grupo por v.marque;
 
— 2b: lo mismo en orden descendente de promedios
— uso de AS
seleccione v.marque, avg(l.km) como promedio (se devolvieron 6 filas)
desde la ubicación l, coche v 
donde l.immat = v.immat 
grupo por v.marque 
ordenar por DESC promedio;
 
— 3: ¿cuál es el coche más antiguo?
seleccione immat - (se devolvieron 3 filas)
desde el coche 
donde compraA = (seleccione min(compraA) 
desde el coche);
 
— 4: ¿Qué coche/s tienen más kilómetros en el cuentakilómetros que todos los Peugeot (TODOS)
seleccione immat - (se devolvieron 7 filas)
desde el coche
donde contador >= TODOS (seleccione contador
desde el coche
donde marca = 'Peugeot'); 
 
— 5: ¿Qué coches tienen un precio diario más alto que un Ferrari?
seleccione immat - (se devolvieron 20 filas)
desde el coche
donde precioJ >= CUALQUIER (seleccione precioJ
desde el coche 
donde marca ='Ferrari');
 
— 6: listado de coches con un kilometraje superior al kilometraje medio general
seleccione immat– (se devolvieron 6 filas)
desde el coche
donde contador > (seleccione promedio(contador) 
desde el coche);
 
— 7: lista de coches con un kilometraje superior al kilometraje medio de su modelo
seleccione v1.immat - (se devolvieron 5 filas)
desde el coche v1
donde v1.counter > (seleccione avg(v2.counter) 
desde el coche v2 
donde v2.modelo = v1.modelo);

Ejercicio 8

  1. ¿Lista de modelos de automóviles alquilados al menos 3 veces (con número de alquileres)?
  2. ¿Qué coche ha recorrido más km en alquiler?
  3. ¿Qué cliente(s) realizó más alquileres?
  4. ¿Qué cliente ha alquilado más vehículos diferentes?
  5. ¿Cuál es el total de kilómetros de alquiler realizados para el propietario del P75?
  6. ¿Cuál es el total de kilómetros de alquiler realizados por el propietario del coche P75 por coche?

                Lo mismo pero teniendo en cuenta que los alquileres de más de 300 km. 

                lo mismo excepto para los vehículos que hayan recorrido un total de más de 300 km en alquiler. 

Para verificación, listado de alquileres realizados por vehículos P75. 

— 1: modelos de coche que se han alquilado al menos 3 veces (con número de alquileres)
— mostrado en orden alfabético por modelo
seleccione v.model, count(l.numloc) - (se devolvieron 15 filas)
desde la ubicación l, coche v
donde l.immat = v.immat
grupo por v.modele
teniendo recuento (l.numloc) >= 3
ordenar por v.modelo; 
 
— 2: qué coche ha recorrido más kilómetros en alquiler
seleccione immat, suma(km) — 11RS75, 3697km
desde la ubicación 
grupo por immat 
teniendo suma(km) >= TODOS(seleccione suma(km) 
desde la ubicación 
grupo por immat);
 
— 3: el cliente que más alquileres ha realizado
seleccione C.apellido, C.nombre - (se devolvieron 1 filas)
desde el cliente c ÚNETE a la ubicación l ON c.codeC = l.codeC
agrupar por C.nom, C.prenom  
teniendo conteo(*)
>= TODOS (seleccione recuento (*)) 
desde la ubicación baja 
agrupar por lo.codeC);
 
— 4: el cliente que ha alquilado más vehículos diferentes
seleccione C.apellido, C.nombre - (se devuelven 2 filas)
del cliente c, ubicación l 
donde c.códigoC = l.códigoC
agrupar por C.nom, C.prenom
tener conteo (immat distinto)  
>= TODOS (seleccione recuento (immat distinto) 
desde la ubicación l2 
agrupar por l2.codeC);
 
— ————————
 
— 5: total de kilómetros de alquiler realizados para vehículos de propietario P75
seleccione suma(l.km) — 7192
desde la ubicación l, coche v
donde l.immat = v.immat y v.codeP =’P75′;
 
— 6a: total de kilómetros de alquiler realizados para el propietario P75 coche por coche
seleccione l.immat, suma(l.km) — (se devuelven 4 filas)  
desde la ubicación l, coche v
donde l.immat = v.immat y v.codeP =’P75′
grupo por l.immat;
 
— 6b: igual que (b) pero teniendo en cuenta únicamente los alquileres de más de 300 km
seleccione l.immat, sum(l.km)– (se devuelven 3 filas) 
desde la ubicación l, coche v
donde l.immat = v.immat y v.codeP =’P75′
y l.km > 300
grupo por l.immat;
 
— 6c Igual que en la letra b), pero solo para los vehículos que hayan recorrido un total de más de 300 km en alquiler.
seleccione l.immat, suma(l.km) — (se devuelven 4 filas) 
desde la ubicación l, coche v
donde l.immat = v.immat y v.codeP =’P75′
grupo por l.immat
teniendo suma(l.km) > 300;
 
— 6d: para verificación, lista de alquileres realizados por los vehículos de P75 
seleccione l.immat, l.km - (se devolvieron 25 filas)
desde la ubicación l, coche v
donde l.immat = v.immat y v.codeP =’P75′
ordenar por l.immat, l.km DESC;

Ejercicio 9

 La consulta en cuestión es: ¿qué clientes (nombres) utilizan el mismo número de licencia?

Para guiarte una serie de consultas progresivas.

  1. Para cada número de permiso, muestre el número de veces que se utiliza.
  2. ¿Lista de números de licencia que se han utilizado más de una vez?
  3. Lista de nombres de clientes con un número de licencia utilizado más de una vez. 
  4. Lista de nombres de clientes cuyo número de licencia es igual al número de licencia de otro cliente (con IN)
  5. Escritura alternativa para (4) usando igualdades de unión explícitas (SQL1). 
  6. Si quedan duplicados, ¿cómo los elimino?
— 1: número de veces que se ha utilizado un número de permiso
seleccione c1.permis, count(*) — (se devuelven 8 filas)
del cliente c1 
agrupar por c1.permis
ordenar por c1.permiso;
 
— 2: número de permisos utilizados más de una vez 
seleccione c1.permis - (se devolvieron 5 filas)
del cliente c1 
agrupar por c1.permis
teniendo recuento (*)> 1
ordenar por c1.permiso;
 
— 3: clientes con un número de licencia (también muestra el número de licencia) utilizado más de una vez
seleccione c2.name - (se devolvieron 14 filas)
del cliente c2
donde c2.permit EN (seleccione c1.permit
del cliente c1 
agrupar por c1.permis
teniendo recuento(*)> 1 ) 
ordenar por c2.nombre;
 
— 4: lista de nombres de clientes que utilizan el número de licencia (mostrar número de licencia) de otro cliente 
seleccione c1.name, c1.permit - (se devolvieron 14 filas)
del cliente c1
donde c1.permit EN (seleccione c2.permit
del cliente c2 
donde c2.permiso = c1.permiso
y c2.nombre != c1.nombre)
ordenar por c1.nombre;
 
- 5: otros escritos con igualdades de unión explícitas 
— (la autounión ahora es mejor visible)
seleccione c1.name, c2.name, c1.permit - (se devolvieron 38 filas)
del cliente c1, cliente c2
donde c2.permit = c1.permit y c2.name != c1.name 
ordenar por c1.nombre;
 
— 6: otros escritos con igualdades de unión explícitas 
— (la autounión ahora es más visible)
seleccione c1.name, c2.name, c1.permit - (se devolvieron 19 filas)
del cliente c1, cliente c2
donde c2.permit = c1.permit y c2.name > c1.name 
ordenar por c1.nombre;

Ejercicio 10

 La consulta dirigida es: ¿qué clientes (los nombres) alquilaron todos los coches?

a) Lista de automóviles que no han sido alquilados; escribir usando NOT EXISTS. (reformulación: vehículos para los que no existe ningún alquiler con este número de matrícula).

(b) Lista de nombres de clientes para quienes existen vehículos que él (el cliente) no ha alquilado.

(c) Relación de nombres de clientes que han alquilado todos los vehículos de la flota.

 (reformulación: lista de nombres de clientes para quienes no hay ningún vehículo que no hayan alquilado.

— La consulta en cuestión es: ¿qué clientes (los nombres) alquilaron todos los coches?
— a) lista de vehículos que no han sido alquilados; escribir usando NOT EXISTS.

SELECCIONAR car.immat - 1 fila
desde el coche
donde no existe (seleccione * de la ubicación donde ubicación.immat=car.immat);

— b) Relación de nombres de clientes para quienes existen vehículos que él (el cliente) no ha alquilado.
SELECCIONE DISTINTO apellido, nombre
del cliente, coche
donde no existe (seleccione codeC de la ubicación donde location.immat=voiture.immat y client.codeC=location.codeC);

— c) Relación de nombres de clientes que han alquilado todos los vehículos de la flota.
— (nueva redacción: lista de nombres de clientes para quienes no hay ningún vehículo que no hayan alquilado.

seleccione nombre.cliente
desde la ubicación, cliente
donde ubicación.codeC= cliente.codeC
grupo por client.codeC
tener count(distinct immat)>= (seleccione (count(distinct immat)) del automóvil);

BDD Vins.txt

Queremos utilizar el siguiente diagrama relacional para gestionar una bodega. La base de datos que representa esta bodega tiene el siguiente esquema relacional: vinos

VINO (númV, añada, año, título)

PRODUCTOR ( númeroP, apellido, nombre, región)

COSECHA ( nprod#, nvin#, cantidad)

Y los siguientes csv: vino, cosecha, productor.

Usando la siguiente declaración, traduce cada línea del archivo .csv en una tupla en la tabla correspondiente

CARGUE EL ARCHIVO DE DATOS 'ruta absoluta/XXX.csv' EN LOS CAMPOS DE LA TABLA XXX TERMINADOS POR ';' LÍNEAS TERMINADAS EN '\r\n';

 

CARGUE EL ARCHIVO DE DATOS 'C:/XXX.csv' EN LOS CAMPOS DE LA TABLA XXX TERMINADOS POR ';' LÍNEAS TERMINADAS EN '\r\n'; si el archivo XXX.csv está bajo la raíz

Un vino se caracteriza por un número entero, una añada, un año de elaboración y una titulación. Todos los vinos están representados por la relación VIN. La clave para la relación VIN es el atributo numV.

Un productor se caracteriza por un número entero, un apellido, un nombre y una región. Todos los productores están representados por la relación PRODUCTOR. La clave de la relación PRODUCTOR es el atributo numP. Un productor produce uno o más vinos. Por el contrario, un vino es elaborado por uno o varios productores (¡posiblemente ninguno!).

Toda la producción está representada por la relación COSECHA. Una tupla de la relación RECOLTE representa una producción particular de un vino de número nvin por parte de un productor de número nprod en una determinada cantidad. La clave de la relación COSECHA es el grupo de atributos (nvin, nprod).

Ejercicio 1

1- Cree la base de datos a partir de los archivos proporcionados.

De forma predeterminada, los archivos solo se pueden leer desde el directorio especificado en la variable Secure_file_priv.

MOSTRAR VARIABLES COMO “secure_file_priv”;

Coloque sus archivos en este directorio y cárguelos para completar sus tablas.

CARGAR ARCHIVO DE DATOS 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/producer.csv'

    EN LA MESA productor

    CAMPOS

        TERMINADO POR ';'

    LÍNEAS

        COMENZANDO POR »  

        TERMINADO POR '\r\n';

2- Visualizar la información contenida en la relación VIN.

3- Dar la lista de productores que no tienen nombre.

4- Dé la lista de las distintas regiones de producción de vino.

5- Da la lista de vinos del año 1980.

6- Indique la lista de nombres y apellidos de los productores de vino que no pertenecen a las siguientes regiones: Córcega, Beaujolais, Borgoña y Ródano.

7- ¿Cuál es la lista de añadas cosechadas en 1979? Muestra la añada, el número de productor y la cantidad producida.

8- ¿Cuáles son los nombres de los productores de Cru Etoile, sus regiones y la cantidad de vinos cosechados?

9- ¿Cuál es el número de cosechas?

10-¿Cuántos productores de vino hay en la región de Saboya y Jura?

11-¿Cuántos productores de vino hay que hayan cosechado al menos un vino en la región de Saboya y Jura?

12-Cuáles son las cantidades de vino producidas por región. Dé la lista ordenada por cantidad decreciente.

13- ¿Cuál es la cantidad de vino producida de grado >12 por número de vino?

14- ¿Cuál es el mayor grado de añada o añadas?

15- Dar la lista ordenada de vinos.

16- Dar la lista ordenada de vinos cosechados.

17- Entonces ¿cuál es la materia prima no cosechada?

18- ¿Dar la lista ordenada de añadas y la cantidad por añada?

19- ¿Cuál es la graduación media de los vinos?

20- ¿Cuáles son las añadas (ordenadas por grado y año) de mayor grado que el grado medio de las añadas?

seleccione * de vino; # 101 filas
seleccione * del productor; # 200 filas
seleccione * de la cosecha; # 200 filas

#Pregunta 2 – 7
seleccione un nombre distinto del productor donde el nombre es nulo; # 0 filas
seleccione un nombre distinto del productor donde nombre = » o nombre es nulo; # 12 filas

seleccionar una región distinta del productor; # 11 filas
seleccionar grado, añada del vino donde año=1980 ordenar por grado desc; # 18 filas
seleccione nombre, nombre, región del productor donde la región no está en ('Córcega', 'Beaujolais', 'Borgoña', 'Ródano') ordenar por nombre asc; # 164 filas
seleccione nprod, añada, cantidad de vino v, cosecha r donde año=1979 y v.numV=r.nprod ordenar por nprod; # 5 filas

#Pregunta 8- 13
seleccione cru,nombre,región,cantidad FROM WINE v,PRODUCER p,COSECHA r WHERE cru='Star' AND p.numP=v.numV AND p.numP=r.nprod; #1 fila
seleccione CONTAR(*) DE COSECHA; # –> 665
seleccione CONTAR(*) DEL PRODUCTOR DONDE región en ('Savoie','Jura'); # –> 57
seleccione CONTAR(*), cantidad DEL PRODUCTOR p,COSECHA r DONDE región en('Savoie','Jura') AND p.numP=r.nprod AND cantidad>0 GRUPO POR cantidad; # 22 filas
seleccione SUMA(cantidad) DE COSECHA R, PRODUCTOR P DONDE R.nprod=P.numP GRUPO POR región ORDEN POR cantidad DESC; # 10 filas
seleccione SUMA(cantidad) de la cosecha r, vino v donde v.numV=r.nvin y v.degre>12 agrupar por nvin; # 14 filas

# Pregunta 14-20
seleccione raw DESDE VIN DONDE grado en (seleccione MAX (grado) DESDE VIN); # –> Amor Santo
seleccione cru DEL VINO PEDIR POR cru ASC; #101 filas
seleccione cru distintos de vin v, coseche r donde v.numV=r.nvin ordene por cru; # 61 filas
seleccione cru de vin v donde v.cru no esté (seleccione cru de vin v, coseche r donde v.numV=r.nvin); # –> Saint Veran
seleccione cru, SUMA (cantidad) de vin v, cosecha r donde v.numV = r.nvin agrupar por cru ordenar por cru; # 61 filas
seleccione promedio (grado) de vin v; # –> 11.35
seleccione cru,grado,año de vin donde grado > (seleccione promedio (grado) de vin v) ordene por cru,grado,año; # 48 filas

Ejercicio 2

1 – Regala todos los vinos cuya añada comience con la letra 'S'

2 – Indicar todos los vinos distintos cuya fecha de elaboración sea entre 1980 y 1981

3 – Selecciona todas las producciones del productor número 606

4 – Indique por número de productor, la cantidad total de vino producido. Consulta con la pregunta 2

5 – Indique por número de productor, la cantidad total de vino producido si es superior a 400 y todo ordenado en orden ascendente de las cantidades calculadas utilizando un alias sobre la suma

6 – Busque la cosecha de Pommard más antigua. Publícalo con el comentario: “el que prefiero”

7 – ¿Cuáles son los números de los productores que cosecharon más vinos diferentes?

8 – Agregue una columna a la tabla del Productor correspondiente a su fecha de nacimiento,

Modificar los siguientes productores existentes en la base de datos asignándoles su fecha de nacimiento

fecha de nacimiento = '1960-12-11′ nombre = 'Seis';

fecha de nacimiento = '1962-12-11′ nombre = 'Lasnier';

fecha de nacimiento = '1970-12-11′ nombre='Moniot';

fecha de nacimiento = '1970-12-11′ nombre='Boxler';

fecha de nacimiento = '1980-12-11′ nombre = 'Jayer';

fecha de nacimiento = '1961-05-10′ nombre='Grivot';

fecha de nacimiento = '1980-12-11′ nombre='Tortochot';

9 – Seleccionar los productores cuya fecha de nacimiento no sea cero

10 – Calcula la edad de Grivot

11 – Calcular el número de productores por edad

1- seleccione cru de vin donde cru como 's%';

2- seleccionar cru distintos, añejados de vin donde añejados entre 1980 y 1981;

3- seleccione * de la cosecha donde nprod = 606;

4- seleccione nprod, sum(qte) del grupo de cosecha por
nprod;

5- seleccione nprod, sum(qte) como suma del grupo de cosecha por
nprod con suma(qte) > 400 orden por suma;

6- seleccione cru, annee de vin donde cru='Pommard';

seleccione concat (cru, 'el que prefiero'), año
de vin donde cru = 'Pommard' y annee >= (seleccione max(annee) de vin donde cru='Pommard');

7- seleccione nprod, nombre de cosecha, productor donde cosecha.nprod = productor.num
grupo por nprod
teniendo recuento (nvin distinto) >= Todo
(seleccione contar (nvin distinto) como nbvindiferente del grupo de recolección por nprod
);


seleccione nprod, nombre de cosecha, productor donde cosecha.nprod = productor.num
grupo por nprod
teniendo recuento (nvin distinto) >=
(seleccione max(nbvindiferente) de
(seleccione contar (nvin distinto) como nbvindiferente del grupo de recolección por nprod) P);


seleccione max(nbvindiferente) de
(seleccione recuento (nvin distinto) como nbvindiferente del grupo de recolección por nprod) P;

seleccione nprod, cuente (nvin distinto) como nbvindiferente
del grupo de cosecha por orden de nprod por nbvindiferente descripción;

seleccione * de la cosecha donde nprod=355;


8- modificar el productor de la tabla agregar la columna fecha y hora de nacimiento;

9- seleccione * del productor donde num <5;
actualizar productor establecer nacimiento = '1990/10/10' donde num = 1;

10- SELECCIONE el nombre, Año (fecha_actual()) – AÑO (productor.nacimiento) como EDAD DEL productor
donde el nacimiento no es nulo;

11- seleccione contar(*) como número, Año(fecha_actual()) – año(productor.nacimiento)como edad
del productor donde el nacimiento no es nulo grupo por edad;

Ejercicio 3

Reanude la base de datos de VIN y responda las siguientes preguntas:

1 – ¿Cuáles son los nombres de los productores del vino N° 5;

2 – ¿Quiénes son los productores que elaboraron el vino 'Pommard'?

3 – ¿Cuáles son las añadas que se cosechan?

7- ¿Cuál es la lista de añadas cosechadas en 1979? Muestra la añada, el número de productor y la cantidad producida.

5- ¿Cuáles son los nombres de los productores de Cru Etoile, sus regiones y la cantidad de

vinos cosechados?

6 – ¿Cuál es el número de productores de añadas recolectadas en Saboya y el Jura?

7 – ¿Cuál es entonces la materia prima no cosechada?

8 – ¿Dar la lista ordenada de añadas y la cantidad por añada?

9 – Indique los nombres de los productores que cosecharon los vinos más diferentes.

10 – Dar la lista de nombres de productores que no cosecharon

11 – ¿Cuáles son los nombres de las personas con el mismo nombre?

1- seleccione el nombre del productor, cosecha donde productor.num = cosecha.nprod y nvin=5;
 
2- seleccione apellido, nombre del productor, cosecha, vino donde productor.num = cosecha.nprod y cosecha.nvin=vin.num y cru = 'Pommard';
 
3- seleccione un cru distinto de la cosecha, vin donde cosecha.nvin= vin.num;
 
4- seleccionar 
    p.num, p.nom, crudo, qte
de
    vino v,
    cosechar,
    productor p
dónde
    fecha = 1979 y v.num = r.nvin
        y p.num = r.nprod
ordenar por nprod;
 
5-seleccionar 
    nombre, vintage, región, cantidad
de
    cosechar,
    productor p,
    vino v
dónde
    p.num = r.nprod y v.num = r.nvin
        y v.cru = 'Estrella';
 
6- seleccionar 
    contar(*)
de
    productor p,
    cosechar
dónde
    región en ('Saboya', 'Jura')
        y p.num = r.nprod;
 
seleccionar 
    contar(*)
de
    productor p,
    cosechar
dónde
     p.num = r.nprod y (p.region='Jura' o p.region='Saboya');
 
7- seleccionar 
    creyó
de
    vino
dónde
    vin.num no está en (seleccione 
            cosecha.nvin
        de
            cosecha);
 
8- seleccionar 
     crudo, suma (cantidad) 
de
    cosecha,
    vino
dónde
   vin.num = cosecha.nvin
grupo por nvin;
 
 
seleccionar 
    crudo, suma (cantidad)
de
    vino v
        IZQUIERDA COMBINACIÓN EXTERNA
    cosecha r ON v.num = r.nvin
grupo por cru
ordenar por crudo;
 
/*solicitudes 9 y 10 ver arriba*/
 
11- seleccione P1.nombre, P2.nombre del Productor P1, Productor P2
donde P1.nombre = P2.nombre;
Compartir, repartir