I asked ChatGPT five Programmer Interview Questions...

Note: parts of this article were generated using AI (obviously 😀)

The threat (or not?) of AI

Many programmers and others in the tech industry are are worried that AI is going to take their jobs.

Want my 2 cents on that? I say no. Not anytime soon, anyway. It's nowhere near ready. It is awesome technology, and we're definitely on the cusp of a new technological era, but there are still too many issues, such as (ease of misuse), (growing pains - Bing, looking at you...) and (cases of blatant plagarism, (something I intended to check for during my experiment)), all which need to be sorted out before it will enter its primetime.

Besides, new technologies usually end up causing a shift in the job market, not outright loss of jobs.

As an interesting experiment, I decided to pose some interview questions to ChatGPT, to see how it would do at actually getting a job. Read on to see what I found.

Question 1

Which is more generally appropriate in a dynamic workplace environment; adherence to ANSI/ISO standard SQL, or tailoring queries and other logical constructs to a specific database architecture using proprietary, vendor-specific constructs?

ChatGPT's Response:

In a dynamic workplace environment, it is generally more appropriate to adhere to ANSI/ISO standard SQL rather than tailoring queries and other logical constructs to a specific database architecture using proprietary, vendor-specific constructs.

Adhering to the ANSI/ISO standard SQL provides a standardized way of working with relational databases that is widely recognized and understood. This allows for greater portability and interoperability between different database systems and tools, which can be particularly valuable in a dynamic workplace environment where changes to the technology stack are frequent.

Using proprietary, vendor-specific constructs may provide some advantages in terms of performance or functionality, but it can also lead to vendor lock-in and greater complexity in terms of maintenance and support. It can also limit the ability to integrate with other tools and systems that do not support the same proprietary constructs.

Overall, it is generally better to prioritize standardization and interoperability over proprietary optimizations in a dynamic workplace environment, as it can help ensure flexibility and scalability over the long term.

While some parts of this were clearly just parroted back to me, the gist of it is spot on (I've highlighted points of particular significance), and it is exactly what I would want to hear from an interviewee for a low-level database programming job.

Question 2

Can you write an Oracle PL/SQL function that accepts a 16-digit numeric input, and performs a luhn algorithm against it, returning the modulus 10 as an integer?

Given its performance on the first question, I thought I'd bring out the big guns, and ask it for actual code. Now, OpenAI and ChatGPT itself will be the first to tell you that it doesn't write code. Why OpenAI keep saying this isn't obvious, because it clearly does write code when you ask it to, at least in theory. Again, I will be fact-checking and checking for plagarism as far as I can.

How would ChatGPT perform at writing code to perform a relatively well-known identification number validation scheme, namely the Luhn algorithm, or Modulo 10 test. This is a simple calculation used by government and banking systems as a front-line check for mis-keyed ID and/or account numbers.

Here is one that I wrote waaaaaaaaay back in 2009 or so… It returns 0 if the account number passes the test. Here's how it does that:

Loop through all digits beginning with last digit
Double every other digit by using a bitand function, which returns 0 for an odd-numbered place, 1 for even, and adding 1 to that to get the multiplier.
Add the results of step 2 together, and finally:
return the modulus 10 of the resulting total - i.e. the remainder left over after dividing the total by 10.

If the function returns a 0, we know we passed the test, with any other number representing a fail.

CREATE OR REPLACE FUNCTION F_LUHN_MOD10
(
	acctNum IN Number Default 1
)
RETURN int
AS
    subTotal int := 0;
    sumTotal int := 0;
BEGIN
    For step1 In Reverse 1 .. Length(acctNum) Loop    
            subTotal := (to_number(Substr(acctNum,step1,1))) * (BitAnd(step1,1) + 1);
            For step2 In 1 .. Length(subTotal) Loop
                sumTotal := sumTotal + Substr(subTotal,step2,1);                
            End Loop;
    End Loop;  
    RETURN sumTotal Mod 10;
END;
/

Simple Oracle PL/SQL implementation of a Luhn algorithm test

Here is ChatGPT's response:

Can you write an Oracle PL/SQL function that accepts a 16-digit numeric input, performs a luhn algorithm test for validity, and returns the modulus 10 as an integer

Yes, I can write an Oracle PL/SQL function that performs a Luhn algorithm test on a 16-digit numeric input and returns the modulus 10 as an integer. Here's an example implementation:

CREATE OR REPLACE FUNCTION luhn_mod10(p_number IN NUMBER)
RETURN NUMBER
AS
l_checksum NUMBER := 0;
l_digit NUMBER;
l_weight NUMBER := 1;
BEGIN
FOR i IN REVERSE 1..16 LOOP
l_digit := TRUNC(p_number / POWER(10, i-1)) MOD 10;
l_checksum := l_checksum + (l_digit * l_weight);
l_weight := CASE WHEN l_weight = 1 THEN 2 ELSE 1 END;
l_checksum := l_checksum + CASE WHEN l_digit * 2 >= 10 THEN (l_digit * 2) - 9 ELSE l_digit * 2 END;
END LOOP;

IF l_checksum MOD 10 = 0 THEN
RETURN 0;
ELSE
RETURN 10 - (l_checksum MOD 10);
END IF;
END;

This function takes a 16-digit numeric input p_number, performs the Luhn algorithm test to compute the checksum, and returns the modulus 10 of the checksum. The Luhn algorithm is a formula used to validate credit card numbers, IMEI numbers, and other numeric codes.

The function works by iterating through the 16 digits of the input number in reverse order. For each digit, it computes a weight factor (either 1 or 2) based on its position, and then computes the checksum as the sum of the weighted digits. If a digit is multiplied by 2, its resulting product is added to the checksum after subtracting 9 if the product is greater than or equal to 10.

Finally, the function checks whether the checksum is divisible by 10. If it is, then the input number is valid and the function returns 0. Otherwise, it computes the difference between 10 and the modulus 10 of the checksum and returns that value as the result.

While it's not very concise, it actually is really well written and would get the job done.

Question 3

Do you comment code that you write, and why/why not?

Despite knowing that this is particularly vague and directed at an individual, I thought I would ask anyway, just to see what I get…

Response:

As an AI language model, I don't write code, but I can provide a general answer to your question.

Yes, it is generally a good practice to comment code that you write. Comments are textual annotations within the code that explain what the code is doing or why it is being done in a particular way. There are several reasons why you should comment your code:

Improved Readability: Comments make it easier for other developers (including yourself) to understand the code, especially when working on large, complex codebases.
Easier Debugging: Comments can help identify and explain problems in the code, which makes debugging easier and faster.
Future Maintenance: Comments can also help future maintainers of the codebase understand what the code does and why it is structured in a particular way.
Team Collaboration: Comments can help facilitate collaboration among team members by clarifying what each section of code does, making it easier to share and discuss code.

However, it's important to use comments judiciously. Over-commenting can make code harder to read and follow, and can also lead to confusion if comments are out of date or don't accurately reflect what the code is doing. In general, it's best to keep comments concise, clear, and up-to-date with any changes to the code.

Despite the modest (and oddly incorrect, as it does indeed write code…) beginning, the 4 points that it makes are spot on. Perfect answer.

Question 4

Describe a technique for finding and identifying duplication within a set of data, using SQL.

Response:

Sure, here is a technique for finding and identifying duplication within a set of data using SQL:

Identify the criteria for identifying duplicate records: Before writing SQL queries to find duplicates, it's important to identify the criteria that define a duplicate record in your dataset. For example, you may consider two records to be duplicates if they have the same value in a specific field, or if they have the same values in multiple fields.

Write a SELECT statement to identify potential duplicates: Once you have identified the criteria for identifying duplicates, you can use a SELECT statement to identify potential duplicates in your dataset. For example, if you consider two records to be duplicates if they have the same value in a specific field, you can use the following SQL query:

SELECT field_name, COUNT(*)
FROM table_name
GROUP BY field_name
HAVING COUNT(*) > 1;

This query will group records by the specified field and count the number of records that have the same value in that field. The HAVING clause will filter the results to only include groups that have more than one record, indicating that there are potential duplicates in the dataset.

Modify the SELECT statement to display duplicate records: Once you have identified potential duplicates, you can modify the SELECT statement to display the duplicate records. For example, if you want to display all records in the dataset that have the same value in the specified field, you can use the following SQL query:

SELECT *
FROM table_name
WHERE field_name IN (
SELECT field_name
FROM table_name
GROUP BY field_name
HAVING COUNT(*) > 1
);

This query will first identify the values in the specified field that have more than one record, and then display all records in the dataset that have one of those values in the specified field.

Note that this technique is just one way to find and identify duplicates using SQL, and there may be other approaches that are more appropriate for your specific dataset and requirements.

Again, this is exactly the correct answer, at least it is the way that I investigate duplication (it's nice to have your methods validated, even if it is by a bot!)

Question 5

What is the most complex database query you have ever had to perform, and how did you handle it?

Again, this is a very specific question directed toward individual experience, and so I expected a general or theoretical answer, which is exactly what I got:

As an AI language model, I don't have personal experiences, but I can provide a general answer to your question.

Complex database queries can vary greatly depending on the specific use case and data being queried, so it's difficult to provide a definitive answer. However, here are some examples of complex database queries that a developer might encounter, along with some general strategies for handling them:

Recursive Queries: A recursive query involves querying a table that references itself, which can be challenging to handle. In these cases, it's often necessary to use Common Table Expressions (CTEs) or recursive functions to iterate over the table and build up the desired results.
Multi-Join Queries: A query that involves multiple joins between tables can be complex to write and optimize. In these cases, it's important to carefully consider the relationships between the tables and the query conditions to ensure that the query is efficient and returns accurate results.
Subqueries: A subquery is a query that is nested inside another query, and can be used to retrieve data that is dependent on the results of another query. Subqueries can be challenging to optimize and can have a significant impact on query performance, so it's important to carefully evaluate their use and consider alternative approaches when possible.

To handle complex database queries, it's important to have a good understanding of database design principles, query optimization techniques, and SQL syntax. It's also helpful to break the problem down into smaller, more manageable sub-problems, and to test and refine the query as you work through each sub-problem. Additionally, it's important to document the query and its performance characteristics, so that it can be easily maintained and optimized in the future.

The challenges it lists are indeed very good and common introducers of complexity into a query. I would also have expected from an interviewee more real-world scenarios, such as having to work around bad database design, but the descriptions of the scenarios that it does provide, along with the overall strategies for solving them, are spot on.

Finally, I thought I'd close out our interview: “Thank you, ChatGPT! It's been a pleasure interviewing you, and we'll definitely give you a call once we've made a decision:"

You're welcome! It was my pleasure to assist you. If you have any further questions or need additional assistance in the future, don't hesitate to reach out. Thank you for considering me for the role. Have a great day!

Thanks for reading. Bye!