Analizamos bases de datos y el lenguaje SQL. (Parte 6 - Verificación de la tarea final) - "Proyecto Java de la A a la Z"

Un artículo de una serie sobre la creación de un proyecto Java (los enlaces a otros materiales se encuentran al final). Su objetivo es analizar tecnologías clave y el resultado es escribir un bot de Telegram. Esta parte contiene un análisis de la tarea final en la base de datos. Saludos, queridos lectores. Hoy analizaremos la tarea del último artículo sobre la base de datos. Es interesante porque está destinado a entrevistas de nivel Medio. Es decir, después de esta tarea ya podrás acudir a una entrevista, y aprobarás con éxito al menos parte de lo relacionado con bases de datos relacionales. Sé lo necesario que puede ser este artículo, y por ello pondré toda mi experiencia en hacerlo útil e interesante. Y si no te quedas dormido en medio del artículo, significará que logré mi objetivo. No repetiré toda la tarea: la citaré antes de completar cada tarea, subrayándola en cursiva. Espero que todos los que lean este artículo ejecuten todas las consultas en su base de datos y obtengan lo mismo. Esto traerá el máximo beneficio al negocio. Y seré un poco más feliz al pensar que ayudé a alguien en nuestra difícil tarea)

Ejercicio 1

Escriba un script SQL para crear la tabla 'Estudiante' con los siguientes campos: id (clave principal), nombre, apellido, correo electrónico (único). Ya lo hemos hecho, así que no debería haber ningún problema. En el script debe especificar la clave principal y un campo único que difiera del principal. Primero, creemos una nueva base de datos para esta tarea: $ CREATE DATABASE final_task; Y usemos esta base de datos: $ USE final_task; Una vez que el entorno está configurado y listo para ejecutar el trabajo, podemos escribir el siguiente script: $ CREATE TABLE estudiante (id INT AUTO_INCREMENT, nombre VARCHAR(40), apellido VARCHAR(50), correo electrónico VARCHAR(100), CLAVE PRIMARIA ( id), ÚNICO (correo electrónico)); Hasta ahora no hay nada nuevo comparado con lo que ya hemos pasado. Cualquier comentario es innecesario, sigamos adelante.

Tarea 2-3

Escriba un script SQL para crear la tabla 'Libro' con los siguientes campos: id, título (id + título = clave principal). Vincule 'Estudiante' y 'Libro' con una relación de 'Libro' uno a muchos de 'Estudiante'. Combinemos dos tareas en una para hacerlo más rápido y conveniente. Ya hablé de cómo agregar una clave externa separada en artículos anteriores. Además, debemos recordar cómo establecemos conexiones y a través de qué. El artículo anterior le ayudará, y aquí está el script: $ CREATE TABLE book ( id INT, título VARCHAR(100), Student_id INT DEFAULT NULL, PRIMARY KEY (id, título), FOREIGN KEY (student_id) REFERENCIAS estudiante (id ) ); De esta forma sencilla, agregamos una clave compuesta para nuestra tabla PRIMARY KEY (id, title) , ahora la clave será exactamente el par. Esto significa que puede haber más de un valor de campo de identificación idéntico en la tabla. Y exactamente lo mismo para el título.

Tarea 4

Escriba un script SQL para crear la tabla 'Profesor' con los siguientes campos: id (clave principal), nombre, apellido, correo electrónico (único), asunto. Seguimos preparando nuestra base de datos para consultas, creamos una tabla de profesores: $ CREATE TABLE profesor( id INT AUTO_INCREMENT, nombre VARCHAR(30), apellido VARCHAR(30), correo electrónico VARCHAR(100), asunto VARCHAR(40), CLAVE PRIMARIA ( id), ÚNICO (correo electrónico)); Hasta ahora no es difícil, ¿verdad? ¡Ya han terminado tres tareas!

Tarea 5

Vincule 'Estudiante' y 'Profesor' con una relación de 'Estudiante' de muchos a muchos Profesor. ¡Ahora esto es más interesante! Hablamos de esto la última vez. Permítanme recordarles lo que se necesita para lograr esto: es necesario crear una tabla intermedia que almacene pares de estudiantes y maestros. Con su ayuda, será posible crear una relación de muchos a muchos. Por lo tanto, creemos una tabla Students_x_techers . El método de denominación es abierto y también podría ser: estudiante_profesor . $ CREAR TABLA estudiantes_x_profesores (id_estudiante INT NO NULO, id_profesor INT NO NULO, CLAVE PRIMARIA (id_estudiante, id_profesor), CLAVE EXTRANJERA (id_estudiante) REFERENCIAS estudiante(id), CLAVE EXTRANJERA (id_profesor) REFERENCIAS profesor(id)); Como puedes ver, todo se hace de forma clara y coherente. Tenemos una clave compuesta para dos claves foráneas: Student_id y Teacher_id. ¿Por qué también una clave externa? Para que podamos estar seguros de que existen registros en las tablas de estudiantes y maestros para los pares que se están registrando.

Tarea 6

Seleccione 'Estudiante' que tenga 'oro' en su apellido, por ejemplo 'Sidorov', 'Voronovsky'. Para que sea interesante y visual para nosotros, sugiero agregar varios estudiantes primero, de modo que algunos de ellos sean adecuados para esta solicitud y otros no. Por lo tanto, anotemos aquellos que deben incluirse como resultado de la solicitud: $ INSERT INTO estudiante (nombre, apellido, correo electrónico) VALORES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERTAR EN estudiante (nombre, apellido, correo electrónico) VALORES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Y los que no deberían entrar: $ INSERT INTO estudiante (nombre, apellido, correo electrónico) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO estudiante (nombre, apellido, correo electrónico) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Comprobemos el resultado, miremos la lista de datos en la tabla de estudiantes: $ SELECT * FROM Student; y obtenemos: Hay cuatro registros en total, dos de ellos deberían encajar y dos no. Habiendo preparado todos los datos para la solicitud, podemos realizar una solicitud para la tarea en sí: $ SELECT * FROM Student WHERE last_name LIKE '%oro%'; Como resultado, Iván y Nikolai repasaron la lista.

Tarea 7

La siguiente tarea, leemos: Seleccione de la tabla 'Estudiante' todos los apellidos ('apellido') y el número de sus repeticiones. Considere que hay homónimos en la base de datos. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

apellido	cantidad
petrov	15
Ivánov	12
Sidorov	3

Para mayor claridad, es necesario agregar más datos. Sin más, agreguemos a los Petrov, Ivanov y Sidorov, que no conocen su parentesco ;) No inventaré una dirección de correo electrónico, simplemente la excluiré de las nuevas entradas. Ejecutemos el siguiente comando 12 veces: $ INSERT INTO estudiante (nombre, apellido) VALUES ('Ivan', 'Ivanov'); Agreguemos 15 Petrov: $ INSERT INTO estudiante (nombre, apellido) VALUES ('Petr', 'Petrov'); Y dos Sidorov (ya tenemos uno))): $ INSERT INTO estudiante (nombre, apellido) VALUES ('Sidor', 'Sidorov'); Los datos ya están listos. Para obtener dichos datos, necesita hacer una agrupación; para hacer una agrupación, necesita usar el operador Agrupar por, y debe hacerlo mediante el campo apellido. También puede notar que el número de repeticiones se designa como cantidad , y aquí también debe recordar cómo crear alias en SQL: $ SELECT last_name, COUNT(*) como cantidad FROM Student GROUP BY last_name ORDER BY COUNT(*) DESC ; Así que me excedí con los Petrov: resultaron ser 16))

Tarea 8

Condición: Seleccione los 3 nombres más repetidos de 'Estudiante'. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

nombre	cantidad
Alejandro	27
serguéi	10
Pedro	7

Ah, para ello ya contamos con Ivans, Peters y Sidors. Por lo tanto no es necesario agregarlos. Ya sabemos cómo ordenar. Lo único de lo que no hablamos hoy es de cómo seleccionar una cierta cantidad de registros. Esto ya apareció en soluciones anteriores a problemas de bases de datos. Para aquellos que no lo han leído, léanlo. Por lo demás, vayamos directo al grano: $ SELECT nombre, COUNT(*) como cantidad FROM Student GROUP BY nombre ORDER BY COUNT(*) DESC LIMIT 3; Como puede verse en la consulta, si conoce el orden de los operadores en una consulta SELECT, no habrá problemas para ejecutar dicha consulta. Y esta tarea todavía depende de nosotros. Y el conocimiento presentado anteriormente es suficiente para resolver este problema.

Tarea 9

Condición de la tarea: seleccione el 'Estudiante' que tenga la mayor cantidad de 'Libro' y 'Profesor' asociado. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

Apellido del profesor	Apellido del estudiante	cantidad del libro
petrov	Sidorov	7
Ivánov	Herrero	5
petrov	kankava	2

Entonces, esta tarea es claramente más difícil que la anterior, ¿verdad? No es de extrañar: huele a unión... y más de uno) Primero, debemos entender qué hacer... Está claro que la cantidad del Libro requiere agrupación. ¿Pero que? ¿Y por qué deberíamos agruparlos? La consulta implica tres tablas, agrupación y clasificación. A juzgar por el hecho de que no se muestran los registros donde no hay libros, significa que es necesario realizar INNER JOIN. También haremos una solicitud de LEFT JOIN para que no haya problemas con esto. Y hay varias opciones. Lo primero que hacemos es unir tres tablas en un solo registro. A continuación, agrupamos por alumno y le agregamos el nombre del profesor. ¿Qué elegiremos? El nombre del profesor, alumno y número de libros. Agreguemos datos para la solicitud:

• tres profesores;
• diez libros;
• conectar dos estudiantes con tres profesores.

tres profesores

$ INSERTAR EN profesor(apellido) VALORES ('Matvienko'); $ INSERTAR EN profesor(apellido) VALORES ('Shevchenko'); $ INSERTAR EN profesor(apellido) VALORES ('Vasilenko');

10 libros

Tomaré las identificaciones de los estudiantes de 1º y 2º. Les adjuntaré libros. Como no se configuró AUTO_INCREMENT, para no escribir una nueva ID cada vez, debe hacer lo siguiente: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; A continuación, agregue libros para el primer estudiante: $ INSERT INTO libro (título, estudiante_id) VALUES('libro1', 1); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro2', 1); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro3', 1); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro4', 1); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro5', 1); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro6', 1); Y libros para el segundo estudiante: $ INSERT INTO libro (título, estudiante_id) VALUES('libro7', 2); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro8', 2); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro9', 2); $ INSERTAR EN libro (título, id_estudiante) VALORES('libro10', 2);

Conexiones profesor-alumno

Para hacer esto, agregue estudiantes_x_profesores a la tabla: $ INSERT INTO estudiantes_x_profesores VALUES (1,1); $INSERT INTO estudiantes_x_profesores VALORES(1,2); $INSERT INTO estudiantes_x_profesores VALORES(2,3);

Implementemos la solicitud.

Hacemos la primera etapa: vinculamos tres tablas en un registro: $ SELECT * FROM teacher tch INNER JOIN Students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN estudiante st ON st_x_tch.student_id = st.id INNER JOIN libro b ON st .id = b.student_id; Por supuesto, todavía no tenemos ningún registro, sin embargo, podemos ver que hemos conectado correctamente tres tablas. Ahora agregamos agrupación de libros, clasificación y los campos que necesitamos: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM Student st INNER JOIN book b ON st.id = b.student_id INNER ÚNASE a estudiantes_x_teachers st_x_tch ON st.id = st_x_tch.student_id UNIRSE INTERIOR al maestro tch ON tch.id = st_x_tch.teacher_id AGRUPAR POR st.id ORDENAR POR libros DESC; Pero obtenemos un error en SQL y la siguiente respuesta: Código de error: 1055. La expresión n.° 1 de la lista SELECT no está en la cláusula GROUP BY y contiene la columna no agregada 'final_task.tch.last_name' que no depende funcionalmente de las columnas en GROUP BY. cláusula No funciona tomar estos elementos porque hay una relación de muchos a muchos entre profesor y alumno. Y es cierto: no podemos tener un solo profesor por alumno. Así que vayamos por el otro lado. Usemos algo llamado Ver SQL. Cuál es la idea: creamos una vista separada, que es una tabla nueva, ya con la agrupación que necesitamos. Y a esta tabla agregaremos los nombres necesarios de los profesores. Pero tenemos en cuenta que puede haber más de un profesor, por lo que las entradas se repetirán. Cree una vista: $ CREAR VER libros de estudiantes como SELECCIONAR st.last_name,st.id,COUNT(*) como libros DESDE el estudiante st INNER JOIN libro b ON st.id=b.student_id GRUPO POR st.id ORDENAR POR libros DESC; A continuación, trabajamos con esta vista como una tabla simple que tiene tres campos: apellido del estudiante, ID del estudiante y recuento de libros. Según el ID del estudiante, también podemos agregar un maestro a través de dos combinaciones: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from Studentbook sbw INNER JOIN Students_x_teachers sttch ON sbw. id = stch.student_id UNIÓN INTERNA maestro tch ON tch.id = stch.teacher_id; Y ahora el resultado será: ¡Eh! Esta es una solicitud, ¿verdad?) Resultó como se esperaba: un estudiante con id=1 tiene seis libros y dos maestros, y un estudiante con id=2 tiene cuatro libros y un maestro.

Tarea 10

Condición: Seleccione el 'Profesor' que tenga la mayor cantidad de 'Libros' entre todos sus 'Estudiantes'. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

Apellido del profesor	cantidad del libro
petrov	9
Ivánov	5

Aquí podemos utilizar una solicitud preparada de la tarea anterior. ¿Qué necesitamos cambiar al respecto? Ya tenemos estos datos, solo necesitamos agregar otra agrupación y eliminar el nombre del estudiante de los datos de salida. Pero primero, agreguemos un alumno más al profesor para que el resultado sea más interesante. Para ello escribimos: $ INSERT INTO estudiantes_x_profesores VALORES (2, 1); Y la consulta en sí: $ SELECT tch.last_name as 'Profesor', SUM(sbw.books) as 'Books' from Studentbook sbw INNER JOIN Students_x_teachers sttch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch .profesor_id GRUPO POR tch.id; Como resultado, obtenemos: el profesor Vasilenko tiene 10 libros y Shevchenko tiene 6...)

Tarea 11

Condición: seleccione un 'Profesor' cuyo número de 'Libros' para todos sus 'Estudiantes' esté entre 7 y 11. Ordenar por cantidad en orden descendente. Debe tener un aspecto como este:

Apellido del profesor	cantidad del libro
petrov	once
Sidorov	9
Ivánov	7

Aquí es donde usaremos TENER. Hablamos de él. La solicitud será exactamente la misma que antes, solo que deberá agregar la condición de que la cantidad de libros estará en un rango determinado. Y como dije en artículos anteriores, cuando necesitamos filtrar durante las funciones de agrupación y/o agregación, necesitamos usar HAVING : $ SELECT tch.last_name como 'Profesor', SUM(sbw.books) como 'Libros' de Studentbook sbw INNER JOIN estudiantes_x_teachers stch ON sbw.id = stch.student_id INNER JOIN profesor tch ON tch.id = stch.teacher_id GRUPO POR tch.id TENER SUMA(sbw.books) > 6 Y SUMA(sbw.books) < 12; He resaltado la parte que agregué. Y, de hecho, el resultado esperado: solo Vasilenko pasó esta ronda))

Tarea 12

Condición: Imprima todos los 'apellidos' y 'nombres' de todos los 'Profesores' y 'Estudiantes' con el campo 'tipo' (estudiante o profesor). Ordenar alfabéticamente por 'apellido'. Debe tener un aspecto como este:

apellido	tipo
Ivánov	alumno
kankava	maestro
Herrero	alumno
Sidorov	maestro
petrov	maestro

Es decir, necesitamos combinar dos resultados, y esto es exactamente para lo que sirve UNION. En otras palabras, tomaremos registros de estudiantes y maestros e imprimiremos juntos: $ SELECCIONAR apellido, 'maestro' como tipo de maestro UNION ALL seleccione apellido, 'estudiante' como tipo de estudiante ORDENAR POR apellido; Y habrá tanto profesores como estudiantes. Parecería que todo es sencillo, pero aquí es cuando ya nos fijamos en el resultado. Y entonces necesitas adivinar dos cosas.

Tarea 13

Condición: agregue una columna 'tasa' a la tabla 'Estudiante' existente, que almacenará el curso en el que se encuentra actualmente el estudiante (valor numérico del 1 al 6). ALTER TABLA estudiante AGREGAR RESTRICCIÓN check_rate VERIFICAR (tasa> 0 Y tasa <7); Aquí estamos agregando un campo a través de ALTER TABLE y CHECK para establecer el límite en este campo de 1 a 6.

Tarea 14

Condición: Este artículo no es obligatorio, pero será una ventaja. Escriba una función que revise todos los 'Libros' y genere todos los 'títulos' separados por comas. Aquí solo necesita devolver una línea como resultado de la solicitud, que contendrá todos los títulos de los libros. Aquí nuevamente tuve que buscar en Google. Existe una función de este tipo: GROUP_CONCAT , con la que esto se hace de forma muy sencilla: $ SELECT GROUP_CONCAT(título) del libro; Y eso es todo...)) Las 14 tareas están listas.

conclusiones

Uuhhh... No fue fácil. Fue interesante. Las tareas valieron la pena, estoy más que seguro. Mientras realizábamos estas tareas, repasamos muchas cosas que no se sabían anteriormente:

• VISTA SQL
• GRUPO_CONCAT
• UNIÓN

etcétera. Gracias a todos los que tuvieron la fuerza de leer y repetir lo que hice. ¿Quién sabe cómo hacer solicitudes mejor? Escriba en los comentarios, definitivamente los leeré)

Al principio de este artículo encontrará una lista de todos los materiales de la serie.