I would start with this part “where average call duration is greater than the average call duration of all calls”. Well, check how the query works when certain parts are added or executed. Simpler “blocks” but also, you’ll test your query along the way because you’ll be checking parts of it at a time as It will help you to build a complex query from We have already written part of the query and that’s a good practice.
#Psequel query writer how to#
How to Write a Complex SELECT Query – Write Parts of the Query at the Time But still, we have to answer on “How to write a complex SELECT query?”.
By testing these parts you’ll get the idea of what is happening in the background, and could assume what the final result should be.
While they won’t be displayed in the final results, they will be used in the background. IĪlways advise that you test parts of your queries. I won’t post the picture of the whole result because it simply has too many columns. Thinking about the final query, we now know it will contain this part: If we want to use them properly, we need to JOIN these tables using foreign keys. Tables between them (in our case that would be – how to get from the country table to theĪfter this analysis we know we must use the following tables: country, city,Ĭustomer, and call. If the tables from the previous bullet point are not directly related, you’ll also need to include all the In our case, the tables in question areĬountry (we need country_name) and call (we need start_time and end_time to Use all tables containing data you need to display in your result. In the data model, I’ve added colors to the tables we need to use.Īnd how to determine which tables should be? The answer has two parts: The first thing we’ll do is to determine which tables we’ll be using in the process. Result display only countries where average call duration is greater than the average call duration of all Return all countries together with the number of related calls and their average duration in seconds. I’ll tell you what was the desired result of the query (assignment given to us). How to Write a Complex SELECT Query & Where is the Data? Without any comments, we can’t easily say what does this query does and how it works. And let’s start with a complex query.Īs you can see, we have a complex query and 2 rows in the result. In case I spent too many words so far, let’s remind ourselves of the original question – “How to write a complex Spare you a lot of time in the long-run, so invest some time when you’re starting. I mean, you can try it, but…Īll these tips can be used regardless of what you are doing with your database. Wouldn’t recommend you driving a car where I’ve repaired brakes. You can always do it without the documentation, but you should be pretty sure you know what you’re doing. This takes time, but is really very useful, especially if you jump in the middle Understanding the business logic behind the dataĬreate documentation yourself. If you don’t have these two on disposal, you have 3 options:Īsk somebody who created the model for the documentation (if that person is available). Also, you should understand the nature of relations between these tables. Before you start to write (complex) queries you should understand what is where – which tables stored what data. Therefore, in this article, I’ll try to cover the “magic” behind writing complex SELECT statements.Īs always, I’ll start with the data model we’ll be using. Writing complex SQL wouldn’t be the only thing required to do that. While I would like to think of myself as of a brilliant mind or genius or add something like “query magician” to my social network profiles, well, How you’ve learned to write such complex queries?”. In my career, I’ve heard many times, things like “How to write a complex SELECT query?”, “Where to start from?” or
0 kommentar(er)
