Hi guys,
I am starting a new series around sql interview prep which will have sql interview like questions and how to approach solving them.
People have different approaches to solving problems so whatever approach l provide is just one of the ways of solving a problem and there could be other approaches that may be even be better and more optimized.
Some interviewers will request you to provide a more optimized solution if the approach you gave is not the best so you should be able to reason differently to solve problems and that comes with practice and creativity. So lets try to see how we can break down the above question and provide a solution.
First, we are to provide print out of the English Alphabets without explicitly writing the letters. Second, we know there are 26 letters in the English Alphabet ABCDEFGHIJKLMNOPQRSTUVWXYZ. If you don’t know this then this problem will be really hard to solve 🙂 . Third, the alphabets have ASCII codes and if you do not know this fact then you won’t be able to solve this problem. Letter A’s ascii code is 65 and the rest of the alphabets are just counted + 1 from there till Z’s value of 90.
Now that we have the above information then all we have to do is convert it into code. One approach can be a loop from 65 to 90 that converts the numbers back into the letter equivalent. To convert a number into a character we use the sql command char.
So lets go ahead and see first implementation.
This approach is very simple. We defined two variables start and end. The start variable is assigned 65 and end is assigned 90 to represent 65 = A to 90 = Z.
Then we loop from 65 and increase the start value by one in each step and print its value. We continued till we reached 90 and the loop ends.
In the above approach we had to loop to get each number, what if there is already a way we directly retrieve the numbers and just convert them? That will definitely be a faster or more optimized than the loop.
If you are familiar with SQL server, there is a table that holds numbers in the master database that you can use. This table is called spt_values. see it below
This table has number codes for so many things in sql server and has so many uses. If you are interested in getting just the regular numbering then set name to null as shown below.
So now we can simply get our alphabets from this table from 65 to 90 as shown below.
To make this code reusable, you could create a view or a function that gives the information. To create a view in SQL, add create view statement as shown below.
CREATE VIEW dbo.vwAlphabets
AS
SELECT CHAR(number) AS Letter
FROM MASTER.dbo.spt_values
WHERE name IS NULL AND Number BETWEEN 65 AND 90
For those who likes functions you can also provide a table-valued function to return these results. Note that this can not be a scalar or aggregate function since it returns multiple values.
How to create a table-valued function in sql? Lets use our query above to create one.
CREATE FUNCTION fnAlphabets()
RETURNS TABLE
AS
RETURN
(
SELECT CHAR(number) AS Letter
FROM MASTER.dbo.spt_values
WHERE name IS NULL AND Number BETWEEN 65 AND 90
)
GO
Creating a table-valued function is quit simple as in the first part we used the create function and provide the name of the function, and provide the brackets that can include list of parameters for example if we adjust our code here so that we pass a parameter ‘case’ like upper or lower so the function returns the lower or upper case of the letters. If the function requires no parameters then you just provide empty brackets after that you provide the key word Returns table. Note also there are inline table-valued functions and multiple table-valued functions with the difference being the inline table-valued functions is a single select statement to return the data and the multiple table-valued functions will have multiple steps performed to generate the final output. To keep things simple, our current function is an inline table valued function so after the returns table we simply provide sql statement to the RETURNS keyword.
Here is an update to this post based on the question what if l don’t know alphabets have ascii codes or l can’t remember the ascii codes, what can l do to still generate the same results?
So here is an approach using string functions in sql to get the letters to print.
First we know our alphabets are ABCDEFGHIJKLMNOPQRSTUVWXYZ and there are 26 of them. So we could using string functions to print each of the alphabets from the alphabet string.
See below
In this approach we assign the alphabets to a string called alphabets then using the loop and the substring function, we are able to print each letter in the alphabet string.
So which of the implementations above do you prefer?
The interviewer is likely looking to see the use of ascii code and convert to the letters as that approach has been widely used. To further rest the theory of using the ascii codes, l asked ChatGPT the question and see the response it gave me confirming he understands the concept.
S what is a use case to retrieve alphabets someone might ask.
Let’s get practical use for it in data pipeline design.
Assume you have a webservice or api that provides clients names or some services and it requires you to either give the name of service/clients but it also supports wild cards or regex so if you give it something like A*, it will give you all clients or services that starts with that letter. Then all you have to do in your loop is generate your letter, get the results and store it, then go to next letter. This allows you to automatically pull any service available from the webservice without knowing their names ahead of time.
