Geek Answers Handbook

What do you think when formulating Sql queries. Is it an experience or a concept?

I worked on sql server and front end encoding and usually ran into the problem of formulating queries.

I really understand most of the sql concepts that are needed when formulating queries, but whenever a new function appears on the picture that cannot use the sql query, I usually don't solve them.

I really like the choice of queries using joins and all such things, but when it comes to DML operation, I usually fail

For every request that I have never made before I usually feel uncomfortable creating them. When I go for an interview, I usually come across this problem.

Is it their concept that fits into the formulation of sql queries.

Eg.

I need to create a sql query so that

 A table contain single column having duplicate record. I need to remove duplicate records.

I know that I can find a solution to this query very easily on Google, but I want to know how everyone comes to the desired result.

Whether this is something like Practice Makes Man Perfect , that is, as soon as you do this, the next time you can formulate or , it's a bit of logic or concept .

I could get the answer to the solution to the problem above by simply posting it on stackoverflow, and I would respond with the answer within 5-10 minutes, but I want to know the reason. How do you work on any new type of request. Is this an important contribution of experience or some implementation of concepts.

Whenever I learn something new in the coding section, I try to use it wherever I can use it. But here the script seems to have changed, because maybe I'm a little behind some of the concepts.

EDIT

How can I test my knowledge and concepts in Sql and related sql queries?

+6
sql sql-server
source share
9 answers

I have a somewhat methodical method for constructing queries in general, and this is what I use elsewhere with any solution to the problem that I need to make.

At the first stage, ALWAYS lists any bits of information that I have in the request. Information is, in fact, something that tells me something.

The table contains one column having a duplicate record. I need to remove duplicate

  • I have a table (I will call it table1)
  • I have a table column table1 (I will call it col1)
  • I have duplicates in col1 in table table1
  • I need to remove duplicates.

The next step in my query design is to identify the action that I will take from the information . I will search for specific keywords (for example, delete, create, edit, show, etc.) Together with the standard insert, update, delete to determine the action. In the example, this will be DELETE due to deletion.

The next step is isolation .

In response to the question "should the action above be valid only for ______ ..?" This part is almost always the most difficult part of constructing any query, since it is usually abstract. In the above example, you specify “duplicate entries” as part of the information, but this is really an abstract concept of something (something where a particular value is not unique to use). Isolation is also where I test my action with the SELECT statement. Every new request that I run is triggered with a first choice!

The next step will be to execute, or essentially a part of the “how to do it” request.

Many times you define how at the isolation stage, but in some cases (including yours), how you isolate something and how you correct it is not the same thing. Displaying duplicate values ​​is different from deleting a specific duplicate.

The final step is implementation . This is exactly where I take everything and make a request ...

To summarize ... for me, to build a query, I will select all the information that I have in the query. Using the information, I will find out what I need to do (action ), and what I need to do ( isolate ). As soon as I find out what I need to do with the fact that I figured out the execution .

Each time I start a new “query”, I start it using these general steps to get an idea of ​​what I'm going to do at an abstract level. For specific implementations of the actual request, you will need to have some knowledge (or google access) to go beyond this.

Kris

+2
source share

As a rule, the first time you need to open a bottle of medicines for children, it is not easy for you, but after that you are ready for what it can / entails.

So it is with programming (I think).

You will find problems, research best practices and hit your head on a couple of stones, but in the process you will get a convenient set of tools.

Also, reading what others have tried to do is a good way to avoid serious obstacles.

In general, with a lot of practice / coding, you will see patterns faster and learn to notice where to use which tool.

+5
source share

Using the example "Delete duplicate" I came to the result by clicking on the link. This scenario is so rare if the database is designed properly that I would not keep this information in my head. Why bother when there is a good resource available for me to search when I need it?

For other requests, this really makes practice perfect.

Over time, you often remember frequently used patterns just because they are often used. Rare cases should be stored in the reference material. I just have too many other things to remember.

+2
source share

I think the same way I cook dinner. I have some ingredients (tables, columns, etc.), some cooking methods (SELECT, UPDATE, INSERT, GROUP BY, etc.), then I put them together the way I know.

Sometimes I will do something strange and find that it tastes terrible, or that it is awesome.

Sometimes I collect new recipes from the Internet or friends, and then use parts of them on my own.

I also save my recipes in convenient repositories, broken into multiple fragments.

+2
source share

  • Find good documentation for your software. I use Mysql a lot and Mysql has a great documentation site with a decent search function, so you get a lot of answers just by reading the docs. If you do NOT get your answer, at least you are learning something.

  • Then I create a sample database (or use the one I work on) and gradually create my SQL. I try to divide the problem into small parts and solve it step by step - it is very successful if you create queries, including many JOINS - it is best to start with a specific case and "publicize" your SQL with many conditions, such as WHEN id = "123", which you take out when you work on your solution.

  • The best and fastest way to learn good SQL is to work with someone else, preferably with someone who knows more than you, but this is not necessarily a condition. It can be replaced by learning mature code written by others.

+1
source share

Your example is a test of how well you understand the DISTINCT keyword and the GROUP BY , which are SQL ways to duplicate data.

0
source share

Examples and experience. You look at other people's examples, and you create your own code, and as soon as it looks in, you no longer need to think about it.

0
source share

From viewing your answers, you have two options.

  • Have a copy of the specification for whatever you work (SQL specification and documentation for implementing SQL (SQLite, SQL Server, etc.)
  • Use Google, SO, Books, etc. as a resource for finding answers.

You cannot formulate an answer to a problem without performing one of the above actions. The first option is to study well the possibilities of what you are working on.

The second option allows you to find answers that you can’t even fully find out how to ask. You, for example, are quite simplified, so if you read the specification / implementation documentation, you will immediately know the answer. But there are times when even if you read the specification / documentation, you do not know the answer. You only know that this is possible, just not how to do it.

Remember that as workplaces and supervisors work, it is important to solve the problem, but the faster you can do it, the better that you can often do with option 2.

0
source share

More articles:


All Articles