We analyze databases and the SQL language. (Part 6 - Checking the final assignment) - "Java project from A to Z"

An article from a series about creating a Java project (links to other materials are at the end). Its goal is to analyze key technologies, the result is to write a telegram bot. This part contains an analysis of the final task on the database. Greetings, dear readers. Today we will analyze the task from the last article on the database. It is interesting because it is intended for interviews at the Middle level. That is, after this task you can already go for an interview, and you will successfully pass at least part of what relates to relational databases. I know how necessary this article can be, and therefore I will put all my experience into making it useful and interesting. And if you don’t fall asleep in the middle of the article, it will mean that I achieved my goal. I will not repeat the entire task: I will quote it before completing each task, underlining it in italics. I expect that everyone who reads this article will run all the queries in their database and get the same thing. This will bring maximum benefit to the business. And I will be a little happier from the thought that I helped someone in our difficult task)

Exercise 1

Write an SQL script to create the 'Student' table with the following fields: id (primary key), name, last_name, e_mail (unique). We've already done this, so there shouldn't be any problems. In the script you need to specify the primary key and a unique field that differs from the primary one. First, let's create a new database for this task: $ CREATE DATABASE final_task; And let's use this database: $ USE final_task; Once the environment is set up and ready to run the job, we can write the following script: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY (id), UNIQUE (email ) ); So far, there is nothing new compared to what we have already gone through. Any comments are unnecessary, let's move on.

Task 2-3

Write an SQL script to create the 'Book' table with the following fields: id, title (id + title = primary key). Link 'Student' and 'Book' with a 'Student' one-to-many 'Book' relationship. Let's combine two tasks into one to make it faster and more convenient. I have already discussed how to add a separate foreign key in previous articles. To add, we need to remember how we make connections and through what. The previous article will help you, and then here is the 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) ); In this simple way, we added a composite key for our table PRIMARY KEY (id, title) , now the key will be exactly the pair. This means that there can be more than one identical id field value in the table. And exactly the same for title.

Task 4

Write an SQL script to create the 'Teacher' table with the following fields: id (primary key), name, last_name, e_mail (unique), subject. We continue to prepare our database for queries, create a teacher table: $ CREATE TABLE teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY (id), UNIQUE (email) ); So far it's not difficult, right? Three tasks are already over!

Task 5

Link 'Student' and 'Teacher' with a 'Student' many-to-many Teacher' relationship. Now this is more interesting! We just talked about this last time. Let me remind you what is needed to achieve this: you need to create an intermediate table that would store student-teacher pairs. With its help, it will be possible to create a many-to-many relationship. Therefore, let's create a table students_x_techers . The naming approach is open and could also be: student_teacher . $ CREATE TABLE students_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (teacher_id) REFERENCES teacher(id) ); As you can see, everything is done clearly and coherently. We have a composite key for two foreign keys: student_id and teacher_id. Why also a foreign key? In order for us to be sure that records in the student and teacher tables exist for the pairs being recorded.

Task 6

Select 'Student' who have 'oro' in their last name, for example 'Sidorov', 'Voronovsky'. To make it interesting and visual for us, I suggest adding several students first, so that some of them are suitable for this request, and some are not. Therefore, let's write down those who should be included as a result of the request: $ INSERT INTO student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); And those who should not get in: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Let's check the result, look at the list of data in the students table: $ SELECT * FROM student; and we get: There are four records in total, two of them should fit, and two should not. Having prepared all the data for the request, we can make a request for the task itself: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; As a result, Ivan and Nikolai went through the list.

Task 7

The next task, we read: Select from the 'Student' table all last names ('last_name') and the number of their repetitions. Consider that there are namesakes in the database. Sort by quantity in descending order. It should look like this:

last_name	quantity
Petrov	15
Ivanov	12
Sidorov	3

For clarity, you need to add more data. Without further ado, let's add the Petrovs, Ivanovs and Sidorovs, who do not know their kinship ;) I won't invent an email address, I'll just exclude it from the new entries. Let's run the following command 12 times: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Let's add 15 Petrovs: $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); And two Sidorovs (we already have one))): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); The data is now ready. To get such data, you need to do a grouping; to do a grouping, you need to use the Group By operator, and you need to do this by the last_name field. You can also notice that the number of repetitions is designated as quantity , and here you also need to remember how to make aliases in SQL: $ SELECT last_name, COUNT(*) as quantity FROM student GROUP BY last_name ORDER BY COUNT(*) DESC; So I overdid it with the Petrovs - it turned out to be 16))

Task 8

Condition: Select the top 3 most repeated names from 'Student'. Sort by quantity in descending order. It should look like this:

name	quantity
Alexander	27
Sergey	10
Peter	7

Oh, for this purpose we already have Ivans, Peters and Sidors. Therefore there is no need to add them. We already know how to sort. The only thing we didn't talk about today is how to select a certain number of records. This has already appeared in previous solutions to database problems. For those who haven't read it, read it. For the rest, let's get straight to the point: $ SELECT name, COUNT(*) as quantity FROM student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; As can be seen from the query, if you know the order of operators in a SELECT query, there will be no problems with executing such a query. And this task is still up to us. And the knowledge that was presented earlier is quite sufficient to solve this problem.

Task 9

Task condition: Select the 'Student' who has the largest number of 'Book' and associated 'Teacher'. Sort by quantity in descending order. It should look like this:

Teacher's last_name	Student's last_name	Book's quantity
Petrov	Sidorov	7
Ivanov	Smith	5
Petrov	Kankava	2

So, this task is clearly more difficult than the previous one, right? No wonder: it smells like join... and more than one) First, we need to understand what to do... It is clear that Book's quantity requires grouping. But what? And why should we group them? The query involves three tables, grouping, and sorting. Judging by the fact that records where there are no books are not shown, it means that you need to take INNER JOIN. We will also make a request for LEFT JOIN so that there are no problems with this. And there are several options. The first thing we do is join three tables into one record. Next, we group by student and add the teacher’s name to it. What will we choose? The name of the teacher, student and number of books. Let's add data for the request:

• three teachers;
• ten books;
• connect two students with three teachers.

Three teachers

$ INSERT INTO teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO teacher(last_name) VALUES ('Shevchenko'); $ INSERT INTO teacher(last_name) VALUES ('Vasilenko');

10 books

I’ll take the IDs of 1st and 2nd students. I will attach books to them. Since AUTO_INCREMENT was not set, in order not to write a new ID each time, you need to do the following: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Next, add books for the first student: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO book (title, student_id) VALUES('book2', 1); $ INSERT INTO book (title, student_id) VALUES('book3', 1); $ INSERT INTO book (title, student_id) VALUES('book4', 1); $ INSERT INTO book (title, student_id) VALUES('book5', 1); $ INSERT INTO book (title, student_id) VALUES('book6', 1); And books for the second student: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO book (title, student_id) VALUES('book8', 2); $ INSERT INTO book (title, student_id) VALUES('book9', 2); $ INSERT INTO book (title, student_id) VALUES('book10', 2);

Teacher-student connections

To do this, add students_x_teachers to the table: $ INSERT INTO students_x_teachers VALUES (1,1); $INSERT INTO students_x_teachers VALUES(1,2); $INSERT INTO students_x_teachers VALUES(2,3);

Let's implement the request

We do the first stage - we link three tables into one record: $ 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; Of course, we don’t have any records yet, however, we can see that we have successfully connected three tables. Now we add book grouping, sorting and the fields that we need: $ 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 books DESC; But we get an error in SQL and the following answer: Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'final_task.tch.last_name' which is not functionally dependent on columns in GROUP BY clause It doesn’t work take these elements because there is a many-to-many relationship between teacher and student. And it’s true: we can’t get just one teacher per student. So let's go the other way. Let's use something called View SQL. What is the idea: we create a separate view, which is a new table, already with the grouping we need. And to this table we will add the necessary names of teachers. But we take into account the fact that there may be more than one teacher, so the entries will be repeated. Create a view: $ 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; Next, we work with this view as a simple table that has three fields: student last_name, student_id and books count. According to the student's ID, we can also add a teacher through two joins: $ 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 teacher tch ON tch.id = stch.teacher_id; And now the result will be: Huh! This is a request, right?) It turned out as expected: a student with id=1 has six books and two teachers, and a student with id=2 has four books and one teacher.

Task 10

Condition: Select the 'Teacher' who has the largest number of 'Books' among all his 'Students'. Sort by quantity in descending order. It should look like this:

Teacher's last_name	Book's quantity
Petrov	9
Ivanov	5

Here we can use a ready-made request from the previous task. What do we need to change about it? We already have this data, we just need to add another grouping and remove the student’s name from the output data. But first, let’s add one more student to the teacher to make the result more interesting. To do this, we write: $ INSERT INTO students_x_teachers VALUES (2, 1); And the query itself: $ 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. teacher_id GROUP BY tch.id; As a result, we get: that teacher Vasilenko has 10 books, and Shevchenko has 6...)

Task 11

Condition: Select a 'Teacher' whose number of 'Books' for all of his 'Students' is between 7 and 11. Sort by quantity in descending order. It should look like this:

Teacher's last_name	Book's quantity
Petrov	eleven
Sidorov	9
Ivanov	7

This is where we will use HAVING. We talked about him. The request will be exactly the same as before, only you need to add the condition that the number of books will be in a certain range. And as I said in previous articles, when we need to do filtering during grouping and/or on aggregation functions, we need to use HAVING : $ 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.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; I've highlighted the part I added. And, in fact, the expected result: Only Vasilenko passed this round))

Task 12

Condition: Print all 'last_name' and 'name' of all 'Teacher' and 'Student' with field 'type' (student or teacher). Sort alphabetically by 'last_name'. It should look like this:

last_name	type
Ivanov	student
Kankava	teacher
Smith	student
Sidorov	teacher
Petrov	teacher

That is, we need to combine two outputs, and this is exactly what UNION is for. In other words, we will take records from students and from teachers and print together: $ SELECT last_name, 'teacher' as type from teacher UNION ALL select last_name, 'student' as type from student ORDER BY last_name; And there will be both teachers and students. It would seem that everything is simple, but this is when we already look at the result. And so you need to guess two things.

Task 13

Condition: Add a column 'rate' to the existing 'Student' table, which will store the course the student is currently in (numeric value from 1 to 6). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (rate > 0 AND rate < 7); Here we are adding a field through ALTER TABLE and CHECK to set the limit on this field from 1 to 6.

Task 14

Condition: This item is not required, but will be a plus. Write a function that will go through all 'Books' and output all 'titles' separated by commas. Here you just need to return a line as a result of the request, which will contain all the titles of the books. Here again I had to google. There is such a function - GROUP_CONCAT , with which this is done very simply: $ SELECT GROUP_CONCAT(title) from book; And that’s it...)) All 14 tasks are ready.

conclusions

Uuhhh... It wasn't easy. It was interesting. The tasks were worth it, I'm more than sure. While we were doing these tasks, we went over many things that were not previously known:

• SQL VIEW
• GROUP_CONCAT
• UNION

and so on. Thank you to everyone who had the strength to read and repeat what I did. Who knows how to make requests better - write in the comments, I will definitely read them)

A list of all materials in the series is at the beginning of this article.