Understanding databases and SQL. (Part 6 - Checking the final task) - "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. In this part - the analysis of the final task on the database.Greetings, dear readers. Today we will sort through the task from the last article on the database. It is interesting because it is intended for an interview at the Middle level. That is, after this task, you can already go for an interview, and at least part of what relates to relational databases, you will successfully pass. 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 do not fall asleep in the middle of the article, then this will mean that I have achieved my goal. I will not repeat the task in full: I will quote it before each task, underlining it in italics. I expect that everyone who reads this article will go through all the queries in their database and get the same. This will bring maximum benefit to the business. And I'll be a little happier at the thought

Exercise 1

Write SQL script to create table 'Student' with fields: id (primary key), name, last_name, e_mail (unique). We've done this before, so it shouldn't be a problem. 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 case: $ CREATE DATABASE final_task; Also we will use this DB: $ USE final_task; With the environment 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 superfluous, let's move on.

Task 2-3

Write SQL script to create table 'Book' with fields: id, title (id + title = primary key). Associate '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. How to add a separate foreign key, I have already said in previous articles. To add, you need to remember how we make connections and due to what. The previous article helped you, and then here's 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 pair will be the key. This means that there can be more than one identical value of the id field in the table. And the same goes for the title.

Task 4

Write SQL script to create table 'Teacher' with fields: id (primary key), name, last_name, e_mail (unique), subject. We continue to prepare our database for queries, creating 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 so easy, right? Three missions are over!

Task 5

Associate 'Student' and 'Teacher' with 'Student' many-to-many Teacher' relationship. Now that's more interesting! We just talked about it last time. Let me remind you that it is necessary 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. So let's create a students_x_techers table . 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: for 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 recorded pairs.

Task 6

Select 'Student' who have 'oro' in their last name, eg 'Sidorov', 'Voronovsky'. To make it interesting and visual for us, I suggest adding a few 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 in the query result: $ 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 be included: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (name, last_name, email) VALUES('Kostya', Let's check the result, look at the list of data in the student table: $ SELECT * FROM student; and we get: There are four entries in total, two of them should fit, and two should not. Having prepared all the data for the query, we can make a query for the task itself: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; As a result, Ivan and Nikolai went down the list.

Task 7

The next task, we read: Select from the table 'Student' all last names ('last_name') and the number of their repetitions. Assume 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 the relationship ;) I will not invent mail, we will simply exclude it in new entries. Let's execute the following command 12 times: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); 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'); Now the data is ready. To get such data, you need to make a grouping, to make 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,quantity , and here we 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 16))

Task 8

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

name	quantity
Alexander	27
Sergey	10
Peter	7

Oh, for this case we already have Ivans, Petras and Sidors. So there is no need to add them. We already know how to sort. The only thing we haven't talked about today is how to select a certain number of records. It flashed already in the previous solutions of tasks on a DB. Who has not read - read. The rest, straight to the point: $ SELECT name, COUNT(*) as quantity FROM student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; As you can see from the query, if you know the order of operators in the SELECT query, there will be no problems with the execution of such a query. And this task is still up to us. And the knowledge that was presented before is quite enough to solve this problem.

Task 9

Quest condition: Select the 'Student' who has the most '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 already clearly more difficult than the previous one, right? No wonder: it smells like a join here... and not even one) First, we need to figure out what to do... It can be seen that Book's quantity requires grouping. But what? And why group? Three tables, grouping, sorting appear in request. Judging by the fact that records where there are no books are not shown, it means that you need to take exactly INNER JOIN. For LEFT JOIN, we will also make a query so that there are no problems with this. And there are several options. The first thing we do is join the three tables into one record. Next, we group by student and append the teacher's name to it. What will we choose? Name of teacher, student and number of books. Let's add data for the request:

• three teachers;
• ten books;
• associate 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 1 and 2 students. I will link 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, let's add to the students_x_teachers 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);

Implementing 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 records yet, but we can see that we have successfully joined three tables. Now we add book grouping, sorting, and the fields 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 response: 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 -to-many 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 such piece as View SQL. The idea is: we create a separate view, which is a new table, already with the grouping we need. And already to this table we will add the necessary names of teachers. But we take into account the fact that there can be more than one teacher, so the entries will be repeated. We 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; Further, we work with this view as with a simple table that has three fields: student last_name, student_id and books count. Using the student ID, we can also add teachers 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: the student with id=1 has six books and two teachers, and the student with id=2 has four books and one teacher.

Task 10

Condition: Select the 'Teacher' who has the highest number of 'Books' from all of his 'Student's. 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 query from the previous task. What do we need to change in it? We already have this data, we just need to add one more 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 the teacher Vasilenko has 10 books, and Shevchenko has 6 ...)

Task 11

Condition: Select a 'Teacher' whose number of 'Books' for all his 'Student's 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 over aggregate 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 highlighted the part I added. And, in fact, the expected result: Only Vasilenko passed this tour))

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 for this there is a UNION. In other words, we will take records from students and from teachers and output 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 teachers and students. It would seem that everything is simple, but this is when we are already looking at the result. And so you need to guess two things.

Task 13

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

Task 14

Condition: This item is not mandatory, but it will be a plus. Write a function that will iterate through all 'Book' and output all 'title' separated by commas. Here you just need to issue a string as a result of the query, which will contain all the titles of the books. Here again it was necessary 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 all ...)) All 14 tasks are ready.

conclusions

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

• SQL VIEW
• GROUP_CONCAT
• UNION

and so on. Thanks 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)

List of all materials in the series at the beginning of this article.