The important things I would like to emphasize here are: Return only customers that have more than the average number of calls of all customers. Write a query that returns customer id and name and the number of calls related to that customer. SQL Practice #2 – Combining Subquery & Aggregate Function You can see the result returned in the picture below. Still, including a primary key from the dictionary, in cases similar to this one, is more than desired This will work OK if the name is defined as UNIQUE. In theory (and most cases), grouping by name should be enough. The last important thing is that we’ve used GROUP BY country.id, untry_name_eng instead of using only GROUP BY untry_name_eng. If we count the city.id, we’ll get the number of related cities We must use COUNT(city.id) AS number_of_cities and not only COUNT(*) AS number_of_cities because COUNT(*) would count if there is a row in the result (LEFT JOIN creates a row no matter if there is related data in other table or not). We’ve used LEFT JOIN ( LEFT JOIN city ON country.id = untry_id) because we need to include all countries, even those without any related city Order the result by country name ascending. You need to show all countries as well as give a reasonable name to the aggregate column. SQL Practice #1 – Aggregating & LEFT JOINĬreate a report that returns a list of all country names (in English), together with the number of related cities we have in the database. For each query, we’ll describe the result we need, take a look at the query, analyze what is important for that query, and take a look at the result. If you feel ready, let’s take a look at the first 2 queries (we’ll have some more in upcoming articles).
How to use textedit for sql how to#
Before you continue, feel free to refresh your knowledge on INNER JOIN and LEFT JOIN, how to join multiple tables, SQL aggregate functions, and the approach to how to write complex queries. The focus shall be on understanding what is required and what is the learning goal behind such a question. Other places where this might help you are college assignments or completing tasks related to online courses. The goal of this SQL practice is to analyze some typical assignments you could run into at the interview. This won’t probably happen at the interview but could happen in the real-life, e.g., when you continue working on an existing project. E.g., you should run a SELECT statement on each table and conclude what is where and how the tables are related. The worst option is that you have to check all the tables first. If not, then you’ll have the description of the database (tables and data types + additional description of what is stored where and how the tables are related). You can expect that in real-life situations (e.g., interview), you’ll have a data model at your disposal. Let’s take a quick look at the model we’ll use in this practice.
![how to use textedit for sql how to use textedit for sql](https://s33046.pcdn.co/wp-content/uploads/2018/10/word-image-247.png)
Now it’s time to discuss some interesting SQL queries. In this series, so far, we’ve covered most important SQL commands ( CREATE DATABASE & CREATE TABLE, INSERT, SELECT) and some concepts ( primary key, foreign key) and theory ( stored procedures, user-defined functions, views).