Estos ejercicios corregidos de SQL para principiantes están dirigidos, como su nombre indica, a personas que no están iniciadas en el lenguaje SQL. Otros ejercicios cubrirán las bases de datos con más profundidad.
Estados
La base de datos utilizada modela una actividad de alquiler de vehículos entre particulares. 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. Esta es la base de datos utilizada como ejemplo durante el curso (en su versión completa… se han agregado algunos atributos respecto a la versión del curso)
El diagrama relacional es el siguiente:
CARRO (immat :VARCHAR, modelo: VARCHAR, marca: VARCHAR, categoría: VARCHAR, color: VARCHAR, lugares: INTEGER, compraA: INTEGER, contador: INTEGER, precioJ: DECIMAL, #codeP: INTEGER)
DUEÑO (códigoP:VARCHAR(4), apodo: VARCHAR, correo electrónico: VARCHAR, ciudad: VARCHAR, año: INTEGER)
CLIENTE (Código:VARCHAR(4), apellido: VARCHAR, nombre: VARCHAR, edad: INTEGER, licencia: VARCHAR, dirección: VARCHAR, ciudad: VARCHAR)
ALQUILER (#codeC:VARCHAR(4), #immat:VARCHAR, año:INTEGER, mes:INTEGER, locnum:INTEGER, km:INTEGER, duración:INTEGER, ciudadD:VARCHAR, ciudadA:VARCHAR, fechaD:FECHA, fechaF:FECHA)
Las claves principales están en negrita/subrayadas; las claves foráneas están precedidas por un#
Un cliente se identifica por su código de cliente (Ccode) y la información almacenada es su apellido, nombre, edad, calle, nombre de la ciudad y su número de licencia.
Un propietario se identifica por su (código de propietario) y la información almacenada es su nombre, ciudad y correo electrónico.
Un coche se identifica por su (matrícula) y la información almacenada es la marca, modelo, color, categoría, número de plazas, año de compra, precio por día y kilómetros cuentakilómetros que se actualiza tras cada nuevo alquiler.
El color puede tomar sus valores de los siguientes: blanco, rojo, verde, negro, morado y azul.
El número de plazas en los coches no puede exceder de 6 (6 incluidos)
Un alquiler se identifica mediante el (código de cliente, matrícula del vehículo y una fecha desglosada en mes y año).
Además, para cada alquiler se guarda un número de alquiler, el kilometraje recorrido, la duración, las ciudades de salida y llegada, y las fechas de inicio y finalización.
Ejercicio 1
Cree el diagrama Entidad-Asociación correspondiente a las especificaciones y que obviamente se adapte al diagrama relacional proporcionado.
Está todo en el comunicado...
Ejercicio 2
Desde el esquema relacional proporcionado anteriormente, cree su base de datos en MySQL respetando los nombres.
Para la restricción de color: color ENUM('blanco','rojo','verde','negro','púrpura','azul'),
Para la restricción de lugares: lugares INT NULL CHECK (lugares <= 6),
Tenga en cuenta que el esquema de la base de datos no se actualiza automáticamente. Necesitas actualizar la ventana.
El archivo mesas de pie permite la instalación de un conjunto de datos en tablas.
Utilice el comando del menú MySQL: Archivo / Abrir script SQL para insertar las tuplas de la tabla.
Ejercicio 3
- Mostrar el listado (modelo, color, matrícula) de vehículos Peugeot.
- Ver especificaciones del vehículo 56AA46. (usar *)
- ¿Dónde vive Depardieu?
- ¿Qué clientes tienen 'Jean' como nombre?
- Lista de clientes (apellido, nombre) con "jean" como nombre, en orden alfabético.
- Lista de clientes con un nombre compuesto que comienza con jean (jean-pierre, jean-jacques, etc.)
- ¿Quién (apellido, nombre) vive en una calle cuyo nombre contiene la palabra "oso"?
- ¿Qué son los descapotables de 4 plazas?
- Lista (números de matrícula) de coches de color azul.
- Lista (números de matrícula) de coches de color blanco.
- Listado (*) de vehículos matriculados en 62.
- 1 (5 filas devueltas)
SELECCIONE modelo, color, immat DESDE el automóvil DONDE marca = 'Peugeot';
- 2 (1 filas devueltas)
SELECCIONE * DESDE el automóvil DONDE immat = '56AA46′;
- 3 (3 filas devueltas)
SELECCIONE nombre, dirección, ciudad DEL cliente DONDE apellido = 'Depardieu';
- 4 (2 filas devueltas)
SELECCIONE el nombre DEL cliente DONDE nombre = 'John';
- 5 (2 filas devueltas)
SELECCIONE apellido, nombre DEL cliente DONDE nombre = 'Jean' ordene por apellido;
- 6 (5 filas devueltas)
SELECCIONE el apellido, el nombre DEL cliente DONDE el nombre como 'Jean%' ordene por apellido;
- 7 (2 filas devueltas)
SELECCIONE apellido, nombre, dirección DEL cliente DONDE dirección como '%ours%' ordene por apellido;
- 8 (3 filas devueltas)
SELECCIONE * DEL automóvil DONDE categoría = 'convertible' y asientos = 2;
— 9 (0 filas devueltas)
SELECCIONE immat, marca, modelo DESDE el automóvil DONDE color = 'azul';
- 10 (8 filas devueltas)
SELECCIONE immat, marca, modelo DESDE el automóvil DONDE color = 'blanco';
- 11 (4 filas devueltas)
SELECCIONE * DEL automóvil DONDE soy como '';
Ejercicio 4
4-1 Estudiar la tabla Coche
- ¿Cuántos vehículos hay en el cómic?
- ¿Cuántas marcas? (SELECCIONAR recuento (marcas))
- ¿Cuántas inscripciones? (SELECCIONAR recuento (immat))
- Haz las mismas consultas añadiendo distintas y explica la diferencia.
4-2. Insertar tuplas en una tabla.
coche (10AK37, Peugeot, 205, descapotable, violeta, 2 plazas, 1980, metros: 65.000, 40 €/día, propietario: P99)
coche (11BF37, Peugeot, 206, descapotable, morado, 2 plazas, 1980, metros: 21.000, 32 €/día, propietario: P99)
coche (15AK37, Peugeot, 205, descapotable, violeta, 2 plazas, 1985, metros: 27.000, 30 €/día, propietario: P99)
- Consulta la presencia de estos coches en la tabla correspondiente.
coche (63GH94, Megane, Renault, sedán, rosa, 4 plazas, 2012, metros: 750, 40€/día, propietario: P99)
coche (87AZ92, Clio, Renault, city car, verde, 4 asientos, 1999, contador: 61200, 40 €/día, propietario: P99)
- Si problema => identificar el problema, ¿podemos corregir y rehacer la inserción?
4-3. Diverso
- ¿Cuántos vehículos hay en el cómic?
- ¿Cuántos vehículos morados hay?
- Cambiar color de matrícula del vehículo 11BF37 a blanco
- Quita los vehículos morados, ¿cuántos vehículos quedan en el cómic?
- Elimina el coche cuyo código P='P89', explica
4.4 Insertar columnas en una clase
- Se añaden dos nuevas columnas a la tabla de alquileres destinadas a recibir las opiniones de los usuarios sobre el alquiler realizado:
- calificación (de 0 a 5)
- agradecimiento (texto limitado a 120 caracteres) y
Verifique la tabla de ubicaciones con el comando DESC
— 1
SELECCIONE el recuento (modelo) DEL automóvil;
SELECCIONE el recuento (modelo distinto) DEL automóvil;
SELECCIONE el recuento (immat) DEL automóvil;
SELECCIONE el recuento (immat distinto) DEL automóvil;
— 2
insertar en el coche (immat, modelo, marca, categoría, color, asientos, compraA, contador, precioJ, códigoP)
valores('10AK37′, '205','Peugeot', 'cabriolet', 'púrpura',2,1980,65000,40,'P99′);
insertar en los valores del automóvil ('11BF37′, '206', 'Peugeot', 'cabriolet', 'purple',2,1980,21000,27,'P99′);
insertar en los valores del automóvil ('15AK37′, '205', 'Peugeot', 'cabriolet', 'purple',2,1980,27000,32,'P99′);
SELECCIONE el recuento (*) DEL automóvil;
insertar en el coche (immat, modelo, marca, categoría, color, asientos, compraA, contador, precioJ, códigoP)
valores ('63GH94′, 'megane','Renault','sedan','rouge',4,'2012′,750,40,'P99′);
insertar en el coche (immat, modelo, marca, categoría, color, asientos, compraA, contador, precioJ, códigoP)
valores('87AZ93′, 'Clio','Renault', 'citadine','vert',4,'1999′,61200,30,'P99′);
— ———————
— 3
SELECCIONE el recuento (*) DEL automóvil;
SELECCIONE * DESDE el automóvil DONDE color = 'púrpura';
SELECCIONE * DESDE el automóvil DONDE immat = '11BF37′;
actualizar el color del conjunto de automóviles = 'blanco' WHERE immat = '11BF37′;
SELECCIONE el recuento (*) DEL automóvil DONDE color = 'púrpura';
SELECCIONE el recuento (*) DEL automóvil;
eliminar DEL coche DONDE color = 'púrpura';
SELECCIONE el recuento (*) DEL automóvil;
SELECCIONE el recuento (*) DEL automóvil DONDE códigoP = 'P89';
eliminar DEL coche DONDE codeP = 'P89';
SELECCIONE el recuento (*) DEL automóvil DONDE códigoP = 'P89';
— no se puede eliminar. Se hace referencia a P89 como clave externa en otra tupla
— —————————————-
— para modificar el parámetro del modo seguro (si hay un problema)
— —————————————-
— mostrar variables como 'sql\_safe\_updates';
— establecer sql\safe\updates = 0;
— —————————————-
— 4
alterar la ubicación de la tabla agregar nota de columna int;
alterar la ubicación de la tabla agregar apreciación de columna varchar(20);
ubicación de descripción;
Ejercicio 5
- ¿Cuántos vehículos hay en la base de datos?
- ¿Cuántos alquileres hay en el bd?
- Eliminar el vehículo matriculado `11TR62′
- ¿Qué código de error y por qué?
- ¿En qué tabla se debe activar la eliminación en cascada?
Se requerirán dos ALTER TABLE:
uno para SOLTAR LLAVE EXTRANJERA
luego uno para AGREGAR RESTRICCIÓN... CLAVE EXTRANJERA
Atención : el nombre elegido para designar la restricción de clave externa debe ser único, así que use nombres como... NombreVariableNombreTabla
- Reintentar la eliminación del vehículo registrado `11TR62′
- ¿Cuántos vehículos hay en la base de datos (compárelo con el resultado indicado anteriormente)?
- ¿Cuántos alquileres hay en la base de datos (compárelo con el resultado indicado anteriormente)?
- Tabla vacía (eliminar todas las tuplas) de la tabla del coche
- Suelte la tabla de clientes (tupla y estructura de tabla) usando DROP TABLE
- ¿Por qué esto no funciona?
- Elimine la tabla de ubicación (tupla y estructura de tabla) con DROP TABLE Luego elimine la tabla de clientes. ¿Por qué es posible eliminar el alquiler entonces para el cliente y no para el cliente antes del alquiler?
- Eliminar en el orden correcto por DROP TABLE, tablas de automóvil, mantenimiento y propietario
- Finalmente, regenere toda la base de datos al estado inicial para el resto del tutorial.
Vuelva a crear las tablas ejecutando los scripts de creación de tablas;
Vuelva a ejecutar el script de llenado de tablas
Luego realice una inserción masiva de un archivo de datos en la base de datos (más precisamente en la tabla de ubicación)
CARGAR DATOS ARCHIVO LOCAL '/temp/locationComplements.csv' 2
EN LA TABLA `ubicación`
CAMPOS TERMINADOS POR ';'
LÍNEAS TERMINADAS POR '\n';
para modificar la variable del sistema correspondiente:
MOSTRAR VARIABLE GLOBAL COMO 'local_infile';
ESTABLECER GLOBAL LOCAL_INFILE = 'ACTIVADO'
— 1,2,3
SELECCIONE el recuento (*) DEL automóvil;
SELECCIONE el recuento (*) DESDE la ubicación;
eliminar DEL coche DONDE immat = '11TR62′;
— 4
#–Código de error: 1451. No se puede eliminar ni actualizar una fila principal: falla una restricción de clave externa (`loueur`.`location`, CONSTRAINT `immat` CLAVE EXTRANJERA (`immat`) REFERENCIAS `car` (`immat`) ON BORRAR SIN ACCIÓN AL ACTUALIZAR SIN ACCIÓN)
— 5
alterar la ubicación de la tabla soltar la clave externa immatLocation;
alterar la ubicación de la tabla agregar CONSTRAINT immatLocation
LLAVE EXTRANJERA (immat) REFERENCIAS coche (immat)
AL ELIMINAR CASCADA AL ACTUALIZAR NO HAY ACCIÓN;
— 6,7,8
eliminar DEL coche DONDE immat = '11TR62′;
SELECCIONE el recuento (*) DEL automóvil;
SELECCIONE el recuento (*) DESDE la ubicación;
— 9,10,11,12
eliminar DEL coche;
soltar la mesa de clientes;
#: Código de error: 1217. No se puede eliminar ni actualizar una fila principal: falla una restricción de clave externa
alquiler de mesa abatible;
soltar la mesa de clientes;
— 13
mantenimiento de la mesa abatible;
carro con mesa abatible;
propietario de la mesa desplegable;
— ———————
# CARGAR ARCHIVO DE DATOS
cargar datos en el archivo local '/temp/locationComplements.csv'
en la ubicación de la mesa
campos terminados en ';'
líneas terminadas en '\n';
— ———————
— —————————————-
— para permitir la carga masiva de datos
— —————————————-
— mostrar variables globales como 'local\infile';
— establecer global local\infile = 'ON';
— —————————————-
Ejercicio 6
Crear un usuario y asignar derechos de acceso a la base de datos.
- Crear un usuario con nombre 'superbozo' (contraseña 'usuario'): CREAR USUARIO 'superbozo'@'localhost' IDENTIFICADO POR '123';
- Dale todos los derechos del cómic GRANT ALL ON renter.* TO 'superbozo'@'localhost';
- Crear un usuario con nombre 'bozo' (contraseña 'usuario')
- Dale solo acceso de lectura a la tabla de ubicación (SELECCIONAR)
- Vuelva a conectarse con el nombre bozo e intente insertar un nuevo propietario (pseudo bozo, código P01, correo electrónico bozo@gmail.com, ciudad París, año 2017).
- Inicie sesión nuevamente como root
- Revocar al superbozo los derechos que le permiten eliminar tuplas (DELETE): REVOCAR.
- Ver los derechos de `bozo', 'superbozo' y el usuario actual
- Eliminar usuarios bozo y superbozo: DROP USER…
- Ver la lista de usuarios (de la tabla mysql.user)
crear usuario 'superbozo'@'localhost' identificado por '123';
otorgar todo en loueur.* a 'superbozo'@'localhost';
—
crear usuario 'bozo'@'localhost' identificado por 'usuario';
conceda SELECT en loueur.location a 'bozo'@'localhost';
—
revocar la eliminación en loueur.location FROM 'superbozo'@'localhost';
mostrar subvenciones para 'superbozo'@'localhost';
mostrar subvenciones para 'bozo'@'localhost';
mostrar subvenciones para usuario_actual;
subvenciones para espectáculos;
—
soltar usuario 'bozo'@'localhost';
soltar usuario 'superbozo'@'localhost';
SELECCIONE * DE mysql.user;
SELECCIONE usuario, host DESDE mysql.user orden por usuario;
ES 
FR 
EN