We analyze databases and the SQL language. (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 has been going through this fourth article with me, well done. As they say, the one who walks can master the road. This week the final article about Databases will be released, in which we will talk about types of relationships and joins . But before we deal with the new information, let’s check our homework... I really felt like a teacher. Don’t be angry with me: I don’t have a pedagogical education, that’s for sure. Since last week a detailed check of the remote control took up the lion's share of the material, I decided to split the analysis of homework and the review of new material into two parts.

Actually, homework analysis

I'm definitely glad that there are people who do PD and talk about it. This is cool! I am as sure as possible that simply reading without consolidating knowledge is a road to nowhere. Therefore, everyone who did or tried to do - 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 more clear, add them. If anyone 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 already had enough practice working from the console. Connect to the database. If you use something else to work with the database, feel free to skip this task. Here and further I will use only MySQL Workbench.
3. Write requests for receipt using our data:
   1. the smallest/most populous country;
   2. average number of inhabitants in the country;
   3. 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 SQL problems. Therefore, understanding it is extremely important. It just so happens that you cannot use conditions for aggregating 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 aggregation occurs after the WHERE statement has been executed and it is impossible to add aggregation values ​​to it that will be calculated later. Even if so far my opinions do not add much to the understanding, you can simply accept this as a fact and go with it. In programming, it often happens that if something is unclear at one moment, it may well mean that the brain simply has not yet digested it. Sleep with this thought, and the next day everything will become clearer.

Installing MySQL Workbench

Here and further I will use Workbench for queries. I'll show you what you need to install 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 version and operating system you want. To do this, follow this link : Here you can select exactly the operating system that you need. Click Download , but instead of downloading we will see this window: Don’t get lost, just look for the button called No thanks, just start my download , and the download will begin. Why are they doing that? Probably, so that more people register with them, this is not important to us. After successful downloading, run the installation file. On MacOS it looks like this: Just drag the icon and that’s it, the installation is complete. Not as difficult as installing MySQL itself, right? Or we’ve just gotten used to it and become more experienced ;) The second part of this task is to establish a connection to our database. What is needed for this? Click the plus sign next to MySQL Connections: In the window that appears, enter the necessary data:

• Connection Name - the name of our connection. Write names that are as clear as possible so that there are no problems with identification later. I give this connection the name JRTB_DB ;
• Hostname - will already be set as local 127.0.0.1 (aka localhost). In our case, nothing needs to be changed, 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 specify your user if necessary. If you haven't added anything in 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 whether the connection will work, click Test Connection : Well, if everything was done correctly, the result will not be long in coming: Now we have a saved connection in the database, and we will not need to create a connection and fill in the name and password every time. And this happiness will look like this: We go into the newly created connection and see the request window. 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 Action Output shows a list of operations and its result. A very useful thing, I want to tell you: with its help you can monitor the execution speed of certain scripts. Why is it important? One of the most common problems in the speed of task execution in an application is the speed of query execution in the database. Here you can quickly and conveniently check them 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 cunningly and take several routes:

• According to the 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 operator at the end of the script and specify the required quantity: $ SELECT * FROM country ORDER BY population DESC LIMIT 1;

• According to the city table

Everything is more interesting here, because the request will be more complex, but also more interesting. Since we still have no idea about joins, 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 in 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 cunningly and take several routes

In this case, everything will be exactly the same. The only difference is that the sorting will be reversed - that’s all. Therefore, I simply write requests:

• According to the city table

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

• According to the 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 technical specification is somehow not accurate, as if the manager had written it... Why did I decide this? Because it is not clear which table to work in. But this is normal: there are simply no tasks in which everything will be immediately clear and understandable. Therefore, you need to carefully read the tasks, and if you have questions, ask them right away! That's right, a remark. Taking into account the data that we have in the database, we will search using data from cities. To do this, 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 inhabitants in countries whose names end with “a”

There will be a slight change here upon request. We need to add filtering by names before we do grouping. I do my homework, like all students, before publishing this article, and I understand that this problem cannot be solved without joins. Why? Because in addition to the country ID, we also need to get his name. And this cannot be done without joining two tables into one record. Therefore, I will do this task, of course, but this is my jamb...))) I wanted to come up with a task using LIKE in the request...) $ 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 using the foreign key country_id, filtered by country names so that they ended in “a,” and only then grouped by country_id.

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

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

6. Sort countries by decreasing number of inhabitants

To do this, we need to use the ORDER BY operator we already know. But keep in mind that by default the sorting is in natural order. 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 need a descending sort, we need the inverse of the natural one: $ SELECT * FROM country ORDER BY population DESC;

7. Sort countries by name in natural order

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

Instead of output

It just so happens that the size of the homework solution will be very large, so we will make an exception: I am publishing this article with a check, and on Friday I will publish new material with connections and joins. Thanks everyone for reading. Till Friday!

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