Analisamos bancos de dados e a linguagem SQL. (Parte 6 - Verificando o trabalho final) - "Projeto Java de A a Z"

Um artigo de uma série sobre a criação de um projeto Java (links para outros materiais estão no final). Seu objetivo é analisar tecnologias-chave, o resultado é escrever um bot de telegrama. Esta parte contém uma análise da tarefa final no banco de dados. Saudações, queridos leitores. Hoje analisaremos a tarefa do último artigo do banco de dados. É interessante porque se destina a entrevistas de nível Médio. Ou seja, após esta tarefa você já pode ir para uma entrevista, e passará com sucesso em pelo menos parte do que se refere a bancos de dados relacionais. Sei o quanto este artigo pode ser necessário e, portanto, colocarei toda a minha experiência para torná-lo útil e interessante. E se você não adormecer no meio do artigo, significa que alcancei meu objetivo. Não vou repetir a tarefa inteira: vou citá-la antes de completar cada tarefa, sublinhando-a em itálico. Espero que todos que lerem este artigo executem todas as consultas em seu banco de dados e obtenham a mesma coisa. Isso trará o máximo benefício para o negócio. E ficarei um pouco mais feliz ao pensar que ajudei alguém em nossa difícil tarefa)

Exercício 1

Escreva um script SQL para criar a tabela 'Aluno' com os seguintes campos: id (chave primária), nome, sobrenome, e_mail (único). Já fizemos isso, então não deverá haver problemas. No script você precisa especificar a chave primária e um campo exclusivo diferente do primário. Primeiramente, vamos criar um novo banco de dados para esta tarefa: $ CREATE DATABASE final_task; E vamos usar esse banco de dados: $USE final_task; Assim que o ambiente estiver configurado e pronto para executar o trabalho, podemos escrever o seguinte script: $ CREATE TABLE aluno ( id INT AUTO_INCREMENT, nome VARCHAR(40), sobrenome VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNIQUE (e-mail)); Até agora, não há nada de novo em comparação com o que já passamos. Quaisquer comentários são desnecessários, vamos em frente.

Tarefa 2-3

Escreva um script SQL para criar a tabela 'Livro' com os seguintes campos: id, título (id + título = chave primária). Vincule 'Aluno' e 'Livro' com um relacionamento 'Livro' um-para-muitos de 'Aluno'. Vamos combinar duas tarefas em uma para torná-las mais rápidas e convenientes. Já discuti como adicionar uma chave estrangeira separada em artigos anteriores. Para acrescentar, precisamos lembrar como fazemos conexões e por meio de quê. O artigo anterior irá ajudá-lo, e aqui está o script: $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES student (id ) ); Desta forma simples, adicionamos uma chave composta para nossa tabela PRIMARY KEY (id, title) , agora a chave será exatamente o par. Isso significa que pode haver mais de um valor de campo id idêntico na tabela. E exatamente o mesmo para o título.

Tarefa 4

Escreva um script SQL para criar a tabela 'Professor' com os seguintes campos: id (chave primária), nome, sobrenome, e-mail (único), assunto. Continuamos preparando nosso banco de dados para consultas, criando uma tabela de professores: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id), UNIQUE (e-mail)); Até agora não é difícil, certo? Três tarefas já terminaram!

Tarefa 5

Vincule 'Aluno' e 'Professor' com um relacionamento 'Aluno' muitos para muitos Professor'. Agora isso é mais interessante! Acabamos de falar sobre isso da última vez. Deixe-me lembrá-lo do que é necessário para conseguir isso: você precisa criar uma tabela intermediária que armazene pares aluno-professor. Com sua ajuda, será possível criar um relacionamento muitos para muitos. Portanto, vamos criar uma tabela Students_x_techers . A abordagem de nomenclatura é aberta e também pode ser: student_teacher . $ CREATE TABLE estudantes_x_teachers (aluno_id INT NOT NULL, professor_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), CHAVE ESTRANGEIRA (student_id) REFERÊNCIAS aluno(id), CHAVE ESTRANGEIRA (teacher_id) REFERÊNCIAS professor(id) ); Como você pode ver, tudo é feito de forma clara e coerente. Temos uma chave composta para duas chaves estrangeiras: student_id e teacher_id. Por que também uma chave estrangeira? Para que possamos ter certeza de que existem registros nas tabelas de alunos e professores para os pares que estão sendo gravados.

Tarefa 6

Selecione 'Aluno' que tenha 'oro' no sobrenome, por exemplo 'Sidorov', 'Voronovsky'. Para torná-lo interessante e visual para nós, sugiro adicionar vários alunos primeiro, para que alguns deles sejam adequados para esta solicitação e outros não. Portanto, vamos anotar aqueles que devem ser incluídos em decorrência da solicitação: $ INSERT INTO aluno (nome, sobrenome, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO aluno (nome, sobrenome, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); E quem não deve entrar: $ INSERT INTO aluno (nome, sobrenome, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO aluno (nome, sobrenome, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Vamos verificar o resultado, veja a lista de dados da tabela de alunos: $ SELECT * FROM aluno; e obtemos: Há quatro registros no total, dois deles deveriam caber e dois não deveriam. Tendo preparado todos os dados para a solicitação, podemos fazer uma solicitação para a tarefa em si: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Como resultado, Ivan e Nikolai percorreram a lista.

Tarefa 7

A próxima tarefa, lemos: Selecione na tabela 'Aluno' todos os sobrenomes ('sobrenome') e o número de suas repetições. Considere que existem homônimos no banco de dados. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

sobrenome	quantidade
Petrov	15
Ivanov	12
Sidorov	3

Para maior clareza, você precisa adicionar mais dados. Sem mais delongas, vamos adicionar os Petrovs, Ivanovs e Sidorovs, que não conhecem o seu parentesco ;) Não vou inventar um endereço de e-mail, apenas excluí-lo das novas entradas. Vamos executar o seguinte comando 12 vezes: $ INSERT INTO aluno (nome, sobrenome) VALUES ('Ivan', 'Ivanov'); Vamos adicionar 15 Petrovs: $ INSERT INTO aluno (nome, sobrenome) VALUES ('Petr', 'Petrov'); E dois Sidorovs (já temos um))): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); Agora os dados estão prontos. Para obter esses dados, você precisa fazer um agrupamento; para fazer um agrupamento, você precisa usar o operador Group By, e você precisa fazer isso pelo campo last_name. Você também pode notar que o número de repetições é designado como quantidade , e aqui você também precisa lembrar como fazer aliases em SQL: $ SELECT last_name, COUNT(*) as amount FROM student GROUP BY last_name ORDER BY COUNT(*) DESC ; Então exagerei com os Petrovs - acabou sendo 16))

Tarefa 8

Condição: Selecione os 3 nomes mais repetidos em 'Aluno'. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

nome	quantidade
Alexandre	27
Sergei	10
Peter	7

Ah, para isso já temos Ivans, Peters e Sidors. Portanto não há necessidade de adicioná-los. Já sabemos como classificar. A única coisa que não falamos hoje é como selecionar um determinado número de registros. Isso já apareceu em soluções anteriores para problemas de banco de dados. Para quem ainda não leu, leia. De resto, vamos direto ao assunto: $ SELECT nome, COUNT(*) as quantidade FROM aluno GROUP BY nome ORDER BY COUNT(*) DESC LIMIT 3; Como pode ser visto na consulta, se você souber a ordem dos operadores em uma consulta SELECT, não haverá problemas ao executar tal consulta. E essa tarefa ainda cabe a nós. E o conhecimento apresentado anteriormente é suficiente para resolver este problema.

Tarefa 9

Condição da tarefa: Selecione 'Aluno' que possui o maior número de 'Livro' e 'Professor' associado. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

Sobrenome do professor	Sobrenome do aluno	Quantidade do livro
Petrov	Sidorov	7
Ivanov	Smith	5
Petrov	Kankava	2

Então, essa tarefa é claramente mais difícil que a anterior, certo? Não é à toa: cheira a juntar... e mais de um) Primeiro, precisamos entender o que fazer... É claro que a quantidade do Livro exige agrupamento. Mas o que? E por que deveríamos agrupá-los? A consulta envolve três tabelas, agrupamento e classificação. A julgar pelo fato de não serem mostrados registros onde não há livros, isso significa que você precisa fazer INNER JOIN. Também faremos uma solicitação de LEFT JOIN para que não haja problemas com isso. E existem várias opções. A primeira coisa que fazemos é juntar três tabelas em um registro. A seguir, agrupamos por aluno e adicionamos o nome do professor. O que escolheremos? O nome do professor, aluno e número de livros. Vamos adicionar dados para a solicitação:

• três professores;
• dez livros;
• conecte dois alunos com três professores.

Três professores

$ INSERT INTO professor (sobrenome) VALUES ('Matvienko'); $ INSERT INTO professor (sobrenome) VALUES ('Shevchenko'); $ INSERT INTO professor (sobrenome) VALUES ('Vasilenko');

10 livros

Vou levar as identificações do 1º e 2º alunos. Vou anexar livros a eles. Como AUTO_INCREMENT não foi definido, para não escrever um novo ID a cada vez, você precisa fazer o seguinte: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Em seguida, adicione livros para o primeiro aluno: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO livro (título, id_aluno) VALUES('livro2', 1); $ INSERT INTO livro (título, id_aluno) VALUES('livro3', 1); $ INSERT INTO livro (título, id_aluno) VALUES('livro4', 1); $ INSERT INTO livro (título, id_aluno) VALUES('livro5', 1); $ INSERT INTO livro (título, id_aluno) VALUES('livro6', 1); E livros para o segundo aluno: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO livro (título, id_aluno) VALUES('livro8', 2); $ INSERT INTO livro (título, id_aluno) VALUES('livro9', 2); $ INSERT INTO livro (título, id_aluno) VALUES('livro10', 2);

Conexões professor-aluno

Para fazer isso, adicione Students_x_teachers à tabela: $ INSERT INTO Students_x_teachers VALUES (1,1); $INSERT INTO alunos_x_professores VALUES(1,2); $INSERT INTO alunos_x_professores VALUES(2,3);

Vamos implementar a solicitação

Fazemos a primeira etapa - vinculamos três tabelas em um registro: $ SELECT * FROM teacher tch INNER JOIN Students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; Claro, ainda não temos registros, mas podemos ver que conectamos três tabelas com sucesso. Agora adicionamos agrupamento de livros, classificação e os campos que precisamos: $ 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 JOIN Students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN teacher tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY livros DESC; Mas obtemos um erro no SQL e a seguinte resposta: Código de erro: 1055. A expressão nº 1 da lista SELECT não está na cláusula GROUP BY e contém a coluna não agregada 'final_task.tch.last_name' que não depende funcionalmente das colunas em GROUP BY cláusula Não funciona pegar esses elementos porque existe um relacionamento muitos-para-muitos entre professor e aluno. E é verdade: não podemos ter apenas um professor por aluno. Então vamos por outro caminho. Vamos usar algo chamado View SQL. Qual é a ideia: criamos uma view separada, que é uma tabela nova, já com o agrupamento que precisamos. E a esta tabela adicionaremos os nomes necessários dos professores. Mas levamos em consideração o fato de que poderá haver mais de um professor, portanto as inscrições serão repetidas. Crie uma visualização: $ CREATE VIEW studentBooks as SELECT st.last_name,st.id,COUNT(*) as books FROM student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; A seguir, trabalhamos com esta visão como uma tabela simples que possui três campos: sobrenome_aluno, id_aluno e contagem de livros. De acordo com o ID do aluno, também podemos adicionar um professor através de duas junções: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from studentbook sbw INNER JOIN Students_x_teachers stch ON sbw. id = stch.student_id INNER JOIN professor tch ON tch.id = stch.teacher_id; E agora o resultado será: Huh! Isso é um pedido, certo?) Aconteceu conforme o esperado: um aluno com id=1 tem seis livros e dois professores, e um aluno com id=2 tem quatro livros e um professor.

Tarefa 10

Condição: Selecione o 'Professor' que possui o maior número de 'Livros' entre todos os seus 'Alunos'. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

Sobrenome do professor	Quantidade do livro
Petrov	9
Ivanov	5

Aqui podemos usar uma solicitação pronta da tarefa anterior. O que precisamos mudar nisso? Já temos esses dados, só precisamos adicionar outro agrupamento e retirar o nome do aluno dos dados de saída. Mas primeiro, vamos adicionar mais um aluno ao professor para tornar o resultado mais interessante. Para fazer isso, escrevemos: $ INSERT INTO Students_x_teachers VALUES (2, 1); E a consulta em si: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch professor_id GRUPO POR tch.id; Como resultado, obtemos: aquele professor Vasilenko tem 10 livros e Shevchenko tem 6...)

Tarefa 11

Condição: Selecione um 'Professor' cujo número de 'Livros' para todos os seus 'Alunos' esteja entre 7 e 11. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

Sobrenome do professor	Quantidade do livro
Petrov	onze
Sidorov	9
Ivanov	7

É aqui que usaremos HAVING. Nós conversamos sobre ele. A solicitação será exatamente a mesma de antes, bastando adicionar a condição de que a quantidade de livros esteja dentro de um determinado intervalo. E como falei em artigos anteriores, quando precisarmos fazer filtragem durante agrupamento e/ou em funções de agregação, precisamos usar HAVING : $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from livro do aluno sbw INNER JOIN estudantes_x_teachers stch ON sbw.id = stch.student_id INNER JOIN professor tch ON tch.id = stch.teacher_id GRUPO POR tch.id TENDO SOMA(sbw.books) > 6 E SOMA(sbw.books) < 12; Eu destaquei a parte que adicionei. E, de fato, o resultado esperado: Apenas Vasilenko passou nesta rodada))

Tarefa 12

Condição: Imprima todos os 'sobrenome' e 'nome' de todos os 'Professor' e 'Aluno' com o campo 'tipo' (aluno ou professor). Classifique em ordem alfabética por 'sobrenome'. Deveria ficar assim:

sobrenome	tipo
Ivanov	estudante
Kankava	professor
Smith	estudante
Sidorov	professor
Petrov	professor

Ou seja, precisamos combinar dois resultados, e é exatamente para isso que serve o UNION. Em outras palavras, pegaremos os registros dos alunos e dos professores e imprimiremos juntos: $ SELECT last_name, 'teacher' as type from teacher UNION ALL select last_name, 'student' as type from student ORDER BY last_name; E haverá professores e alunos. Parece que tudo é simples, mas é aí que já olhamos o resultado. E então você precisa adivinhar duas coisas.

Tarefa 13

Condição: Adicione uma coluna 'taxa' à tabela 'Aluno' existente, que armazenará o curso em que o aluno está atualmente (valor numérico de 1 a 6). ALTER TABLE aluno ADD CONSTRAINT check_rate CHECK (taxa > 0 AND taxa < 7); Aqui estamos adicionando um campo através de ALTER TABLE e CHECK para definir o limite deste campo de 1 a 6.

Tarefa 14

Condição: Este item não é obrigatório, mas será uma vantagem. Escreva uma função que percorrerá todos os 'Livros' e produzirá todos os 'títulos' separados por vírgulas. Aqui basta retornar uma linha como resultado da solicitação, que conterá todos os títulos dos livros. Aqui novamente eu tive que pesquisar no Google. Existe uma função - GROUP_CONCAT , com a qual isso é feito de forma muito simples: $ SELECT GROUP_CONCAT(título) do livro; E é isso...)) Todas as 14 tarefas estão prontas.

conclusões

Uuhhh... Não foi fácil. Foi interessante. As tarefas valeram a pena, tenho mais que certeza. Enquanto realizávamos essas tarefas, repassamos muitas coisas que não eram conhecidas anteriormente:

• VISUALIZAÇÃO SQL
• GRUPO_CONCAT
• UNIÃO

e assim por diante. Obrigado a todos que tiveram forças para ler e repetir o que fiz. Quem sabe fazer melhor os pedidos - escreva nos comentários, com certeza irei lê-los)

Uma lista de todos os materiais da série está no início deste artigo.