SQLTeam.com | Weblogs | Forums

How Do Professionals Learn How to Write Code SQL Code From Scratch

#1

Hello Community,

I'm finding it hard to translate questions / requirements into SQL Query. I'm getting better at understanding SQL Code, but I still struggle translating requirements into code.

For example, if I gave you the following question would you know straight off the bat how to write sql code to get the answer:

Investigating trends and patterns in data often means looking at the data that makes up a specific area of data. Finding attributes of least (and most) profitable sales is an example of this kind of analysis. As a specific example, the sales director wants you to find all colors for cars sold for the least profitable 5 percent of sales.

The data / tables used to generate the code is as follows:
CREATE TABLE PrestigeCars.Data.SalesDetails (
SalesDetailsID INT IDENTITY
,SalesID INT NULL
,LineItemNumber TINYINT NULL
,StockID NVARCHAR(50) NULL
,SalePrice NUMERIC(18, 2) NULL
,LineItemDiscount NUMERIC(18, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE PrestigeCars.Data.Stock (
  StockCode NVARCHAR(50) NULL CONSTRAINT DF_Stock_StockCode DEFAULT (NEWID())
 ,ModelID SMALLINT NULL
 ,Cost MONEY NULL
 ,RepairsCost MONEY NULL
 ,PartsCost MONEY NULL
 ,TransportInCost MONEY NULL
 ,IsRHD BIT NULL
 ,Color NVARCHAR(50) NULL
 ,BuyerComments NVARCHAR(4000) NULL
 ,DateBought DATE NULL
 ,TimeBought TIME NULL
) ON [PRIMARY]
GO

So, would the above be enough information for you generate the appropriate SQL script?

If so, how do you learn to interpret code straight off the bat like that.

I know the answer, but even when I look at the question again, I still struggle as to how to write the code for it?

Any guidance on how to obtain the expertise is greatly appreciated.

#2

Before getting into query writing you need to have a clear understanding of your data model.
which table has what type of data and how are the tables linked to each other.Once you have this information,then it is just using SQL to pull the data that you want.Depending on your requirement you will be using different functions available.

#3

Look at what is being asked for and then build up the query. How do you get the profit on each car? What are the least profitable 5 percent. From those, how do you get the colors?

Of course, then you have to ask the questions which aren't really covered. Over what time period? Does the customer want monthly figures, annual figures, just over all time etc?

This is something that comes with time and experience. The more you write SQL, the better you get at translating human requirements into queries and then the better you get at spotting the subtleties that the original requester will overlook because they tend to assume you know what they are after. Practicing is by far the best way to improve your skills.

#4

AndyC,

This is a very thought-provking response. I have been using the book 'Aspin, Adam; Aspin, Karine. Query Answers with SQL Server: Volume II: In–Depth Querying' to assist me in writing SQL Queries. I can answer all the questions in the book, but I'm still struggling actually translating the questions in the book into SQL queries without actually looking at the answer.

My question, then to you is, 'With your experience, could you use the tables I posted to write the SQL query to answer the question "find all colors for cars sold for the least profitable 5 percent of sales"'?

Cheers

#5

Knowing that it's a book example and therefore really only looking for exactly what it says, yes. In the real world there would definitely be additional questions because there is a certain amount of ambiguity in the question.

Part of this is realizing there are two parts to it. Being able to translate a human description into a more technical specification is an analyst role, it's where being able to understand and spot the subtlety of human speech and asking the right questions to get the level of detail you really need. Taking that technical specification and turning it into SQL queries is the developer part and, if the specification is well written, is usually easier than trying to decipher English language descriptions of problems.

Both of these are skills that you will get better at with practice. Don't worry initially about getting the same answer as the book, or even the "right" answer. Look at what you've done and re-read the description to see if you can pick up what the author probably thought was obvious that differs from your interpretation.

1 Like
#6

Hi AndyC,

Really appreciate the clarification. You have confirmed to me in my head that I'm the developer in this scenario.

I think the advice[quote="AndyC, post:5, topic:15648"]
Look at what you've done and re-read the description to see if you can pick up what the author probably thought was obvious that differs from your interpretation.
[/quote]
Is perfect, and is exactly what I intend to do going forward

Thanks mate

1 Like