Understanding databases and SQL. (Part 4 - checking homework) - "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. Previous articles and analysis of homework on databases: 1 , 2 , 3 . To everyone who had the patience and endurance, to everyone who is with me for the fourth article - well done. As they say, the road will be mastered by the walking one. This week will be the final article on Databases, in which we will talk about types of relationships and joins (joins). But before we deal with the new information, let's check the homework ... I felt like a teacher. Don't get mad at me: I don't have a pedagogical education, maybe. Since last week a detailed check of the remote sensing took up the lion's share of the material, I decided to split the analysis of the homework and the review of the new material into two parts.

Actually, homework analysis

I'm definitely glad that there are people who do DZ and talk about it. This is cool! I am as sure as possible that just reading without consolidating knowledge is a road to nowhere. Therefore, everyone who did or tried to do is respect. Let me remind you the conditions of the tasks:

1. Understand the HAVING operator and write an example query for the tables from our example. If you need to add some fields or more values ​​​​to make it clearer - add. Who wants to - write your example solution in the comments - so I can also check it if I have time.
2. Install MySQL Workbench to work with the database through the UI. I think we've had enough practice working from the console. Connect to DB. If you use something else to work with the database, feel free to skip this task. Hereinafter, I will only use MySQL Workbench.
3. Write requests for receiving according to our data:
   1. the smallest/largest numerical country;
   2. the average number of inhabitants in the country;
   3. the average number of inhabitants in countries whose names end in “a”;
   4. the number of countries with a population of more than four million;
   5. sort countries by decreasing number of inhabitants;
   6. sort countries by name in natural order.

Let's talk about HAVING

Knowing the Having operator can help you pass more than one interview, where there will be tasks in SQL. Therefore, it is extremely important to understand it. It just so happens that you cannot use conditions for aggregation functions (SUM, MIN, MAX, AVG). In addition, HAVING is used for fields that are grouped. What does it mean? For example, if we want to get countries where the average number of inhabitants in cities is more than 50,000 inhabitants, we cannot do without using HAVING. As I understand it, this is done because the aggregation occurs after the WHERE clause is executed and you cannot add aggregation values ​​to it, which will be calculated later. Even if so far my judgments do not add much understanding, you can just accept it as a fact and go with it. In programming, it often happens that if at one moment something is not clear, it could very well mean that the brain just hasn't digested it yet. Sleep with this thought, and the next day everything will become clearer.

Installing MySQL Workbench

Hereinafter, I will use Workbench for queries. I'll show you what you need to set up and create a connection to the database. This is a product from Oracle, so you just need to go to their website and select the right version and operating system. To do this, follow this link : Here you can choose exactly the operating system that you need. We press Download , but instead of downloading we will see this window: Don't get lost, just look for a button called No thanks, just start my download , and the download will begin. Why are they doing that? Probably, that more people register with them, it doesn't matter to us. After a successful download, run the installation file. On MacOS it looks like this:Just transfer the icon - and that's it, the installation is complete. Not as difficult as installing MySQL itself, right? Or have you just got used to it and become more experienced ;) The second part of this task is to establish a connection with our database. What is needed for this? Click the plus sign next to MySQL Connections: In the window that appears, enter the required data:

• Connection Name - the name of our connection. Write the most understandable names so that later there are no problems with identification. I name this connection JRTB_DB ;
• Hostname - will already be set as local 127.0.0.1 (aka localhost). In our case, you don’t need to change anything, since the database is installed on the computer, but if the database is somewhere else, then the host (ip of the machine on which the database is running) needs to be changed accordingly;
• Username - you can also set your user if necessary. If you haven't added anything to this key, leave it unchanged;
• Password - click Store in Keychain and set exactly the password that you set for yourself. I left everything simple - root .

To check if there will be a connection, click Test Connection : Well, if everything was done correctly, the result will not keep you waiting: Now we have a saved connection in the database, and there will be no need to create a connection every time, fill in the name and password. And this happiness will look like this: We go into the newly created connection and see the window for requests. To be sure that everything is correct, let's check the list of databases, go to ours and get all the data about the cities: And here we get carte blanche for what we need. The first section is responsible for the script that we enter. Next, in the Result Grid , we see the result of the last operation in the script. And in Action Outputshows a list of operations and their result. A very useful thing, I want to tell you: with its help, you can monitor the speed of execution of certain scripts. Why is it important? One of the most common problems in the speed of performing tasks in an application is the speed of executing queries in the database. Here they can be quickly and conveniently checked by hand.

We write the necessary requests

We only have 7 requests to complete, let's go!

1. Get the most populous country. Here you can go tricky and several routes:

• According to country table

Then we just need to sort our query by population and take only one record. To do this, you need to add a LIMIT statement at the end of the script and specify the required amount: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;

• According to city table

Everything is more interesting here, because the query will be more difficult, but also more interesting. Since we don't know about joins yet, we can only get the country ID: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1; Here we did a cool thing - we collected the sum of the population of all known cities for each country, sorted by this sum and took the first element. Well, is it great? I'm delighted :D After this, you immediately feel like a query guru ... (not for long, of course))

2. Get the smallest country. Here you can go tricky and several routes

In this case, everything will be exactly the same. The only difference is that it will sort backwards - that's all. So I just write requests:

• According to city table

$ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) LIMIT 1;

• According to country table

$ SELECT * FROM country ORDER BY population LIMIT 1; And see the result for yourself!

3. Average number of inhabitants in the country

Here again, the TK is somehow inaccurate, as if the manager wrote it ... Why did I decide so? Because it's not clear which table to work with. But this is normal: tasks in which everything will be immediately clear and understandable simply do not happen. Therefore, you need to carefully read the tasks, and if you have questions, ask them right away! That's right, remark. Taking into account the data that we have in the database, we will search according to data from cities. To do this, we write the following query: $ SELECT country_id, AVG(population) FROM city GROUP BY country_id; It's simple here: we use the AVG function and group our city records by country.

4. Average number of people in countries whose names end with “a”

There will be a slight change here upon request. We need to add name filtering before we do the grouping. I do my homework, like all students, before publishing this article, and I understand that this task cannot be solved without joins. Why? Because in addition to the country ID, we also need to get its name. And this cannot be done without joining two tables into one record. So I will do this task, of course, but this is my cant ...))) I wanted to come up with a problem using LIKE in the query ...) $ SELECT ci.country_id, AVG(ci.population) FROM city ci INNER JOIN country co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id;What happened here? First, we joined records from the city and country tables by the country_id foreign key, filtered by country names so that they end in “a”, and only then grouped by country_id.

5. Number of countries with a population of more than four million

Here we just need to use the COUNT function and add population filtering: $ SELECT COUNT(*) from country WHERE population > 4000000; As a result, we learn that there are 3 such countries. Is this correct? Yes, only Moldova does not pass this milestone.

6. Sort countries by decreasing population

To do this, we need to use the ORDER BY operator we already know. But keep in mind that sorting is in natural order by default. For numbers, this means that it is sorted in ascending order, for strings, it means that it is sorted starting from the first characters. If we want a descending sort, we need the reverse of natural: $ SELECT * FROM country ORDER BY population DESC;

7. Sort countries by name in natural order

This is where the knowledge of what the natural order is will come in handy. Since it's the default, it's as easy as shelling pears for us: $ SELECT * FROM country ORDER BY name;

Instead of output

It just so happened that the size of the homework solution will turn out to be very large, so we will make an exception: I publish this article with a check, and on Friday I will publish a new material with connections and joins. Thank you all for reading. Till Friday!

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