How do you think while formulating Sql Queries. Is it an experience or a concept?

Question

I have been working on sql server and front end coding and have usually faced problem formulating queries.

I do understand most of the concepts of sql that are needed in formulating queries but whenever some new functionality comes into the picture that can be dont using sql query, i do usually fails resolving them.

I am very comfortable with select queries using joins and all such things but when it comes to DML operation i usually fails

For every query that i never done before I usually finds uncomfortable with that while creating them. Whenever I goes for an interview I usually faces this problem.

Is it their some concept behind approaching on formulating sql queries.

Eg.

I need to create an sql query such that

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

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

Is it something like Practice Makes Man Perfect i.e. once you did it, next time you will be able to formulate or their is some logic or concept behind.

I could have get my answer of solving above problem simply by posting it on stackoverflow and i would have been with an answer within 5 to 10 minutes but I want to know the reason. How do you work on any new kind of query. Is it a major contribution of experience or some an implementation of concepts.

Whenever I learns some new thing in coding section I tries to utilize it wherever I can use it. But here scenario seems to be changed because might be i am lagging in some concepts.

EDIT

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

Answer 1

Typically, the first time you need to open a child proof bottle of pills, you have a hard time, but after that you are prepared for what it might/will entail.

So it is with programming (me thinks).

You find problems, research best practices, and beat your head against a couple of rocks, but in the process you will come to have a handy set of tools.

Also, reading what others tried/did, is a good way to avoid major obsticles.

All in all, with a lot of practice/coding, you will see patterns quicker, and learn to notice where to make use of what tool.

Answer 2

I have a somewhat methodical method of constructing queries in general, and it is something I use elsewhere with any problem solving I need to do.

The first step is ALWAYS listing out any bits of information I have in a request. Information is essentially anything that tells me something about something.

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

1. I have a table (I'll call it table1)
2. I have a column on table table1 (I'll call it col1)
3. I have duplicates in col1 on table table1
4. I need to remove duplicates.

The next step of my query construction is identifying the action I'll take from the information I have. I'll look for certain keywords (e.g. remove, create, edit, show, etc...) along with the standard insert, update, delete to determine the action. In the example this would be DELETE because of remove.

The next step is isolation.

Asnwer the question "the action determined above should only be valid for ______..?" This part is almost always the most difficult part of constructing any query because it's usually abstract. In the above example you're listing "duplicate records" as a piece of information, but that's really an abstract concept of something (anything where a specific value is not unique in usage). Isolation is also where I test my action using a SELECT statement. Every new query I run gets thrown through a select first!

The next step is execution, or essentially the "how do I get this done" part of a request.

A lot of times you'll figure the how out during the isolation step, but in some instances (yours included) how you isolate something, and how you fix it is not the same thing. Showing duplicated values is different than removing a specific duplicate.

The last step is implementation. This is just where I take everything and make the query...

Summing it all up... for me to construct a query I'll pick out all information that I have in the request. Using the information I'll figure out what I need to do (the action), and what I need to do it on (isolation). Once I know what I need to do with what I figure out the execution.

Every single time I'm starting a new "query" I'll run it through these general steps to get an idea for what I'm going to do at an abstract level. For specific implementations of an actual request you'll have to have some knowledge (or access to google) to go further than this.

Kris

Answer 3

I think in 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 in the way I know how.

Sometimes I will do something weird and find it tastes horrible, or that it is amazing.

Occasionally I will pick up new recipes from the internet or friends, then use parts of these in my own.

I also save my recipes in handy repositories, broken down into reusable chunks.

Answer 4

On the "Delete a duplicate" example, I'd come to the result by googling it. This scenario is so rare if the DB is designed properly that I wouldn't bother keeping this information in my head. Why bother, when there is a good resource is available for me to look it up when I need it?

For other queries, it really is practice makes perfect.

Over time, you get to remember frequently used patterns just because they ARE frequently used. Rare cases should be kept in a reference material. I've simply got too much other stuff to remember.