Google Gemini - Generative AI Tool for SQL Professionals

By: Rick Dobson   |   Updated: 2024-04-15   |   Comments   |   Related: More > Artificial Intelligence

Problem

Please present a collection of generative AI demonstrations illustrating how Google's generative AI software can enhance my productivity as a T-SQL developer. Also, discuss the availability and usefulness for T-SQL developers of generative AI in the Google Chrome browser.

Solution

Generative AI refers to software database applications that generate content in response to a prompt. This technology is evolving rapidly, and it may be getting to the point where any technology professional should have at least a passing acquaintance with generative AI software operations and capabilities. For example, Google made available two major generative AI initiatives (Bard and Gemini) within the 12 months preceding the preparation of this tip.

Gemini is an outgrowth of Bard. Both of these AI initiatives function as chatbox applications. Wikipedia defines a chatbox application as software or a web interface that mimics human conversation through voice and text. In the context of this tip, you can prompt Gemini for sample code or an explanation of some database development features. Then, Gemini can respond with sample code and/or commentary in response to the prompt. By rephrasing a question or asking a series of questions on a topic, you can have a "conversation" with Gemini or other generative AI applications.

There are many generative AI applications. ZDNET provides an overview of some of the most prominent generative AI applications. Among those examined in the overview are ChatGPT, Microsoft Copilot, Bard, and Gemini.

This tip demonstrates the Gemini Pro release as a standalone application for a desktop or laptop personal computer. This release does not require payment to use. This tip also explains the operation of the release of Gemini accessible through the Google Chrome browser (Version 122.0.6261.70 (Official Build) (64-bit)). The Gemini demonstrations in this tip are focused on obtaining code samples and commentary about T-SQL development topics.

A Quick Introduction to Using Gemini

One way to start using Google generative AI is to sign up at https://bard.google.com and agree to the terms for using Bard. Then, you can start a new browser tab and navigate to the Gemini Pro release by entering https://gemini.google.com/app into the address box on a new tab.

Below is a view of what Gemini will look like right after you initially open it and before you type any prompts into the chatbox. Notice the Gemini welcome page offers some sample prompts to try. While I did not observe a suggested prompt for getting more information about T-SQL coding, I was able to retrieve code samples and commentary on T-SQL programming techniques by entering custom prompts.

A Couple of Sample Prompts to Show T-SQL Code Generated by Gemini

Here is a sample prompt to Gemini for an example of T-SQL code. This prompt is very open-ended. Therefore, the sample prompt shows Gemini's ability to respond to very general prompts. The answer starts by posing a couple of more specific prompts:

1. It suggests a general topic (Finding Top Customers by Order Total).
2. It further refines the prompt to a search for the top 10 customers by order value in the past year.

Next, Gemini presents T-SQL code to address the narrowed scope. After the code sample for the narrowed scope, there is an explanation of how the code should work, given the existence of Customers and Orders tables in the schema within which the query runs.

The code sample in the following screenshot demonstrates the use of these key phrases: select, from, inner join, where, group by, and order by. It also shows applications of sum, dateadd, and getdate functions.

It is strongly recommended that you test the operation of all Gemini-generated code before assuming they return valid results or even run at all. In the case of the following code sample, you can do this by making sample Customers and Orders tables in a schema. Then, you can run the code in SQL Server Management Studio. This step is critical because it will allow you to track errors in Gemini-generated code. For example, the select statement's third list item in the following code sample is a sum function with an alias of TotalOrderValue. While the alias assignment is valid, the reference to the alias in the order by clause is not valid. You can fix this error by replacing the alias with sum(o.OrderTotal).

With Gemini, it is possible to ask follow-up prompts to an initial prompt. The following screenshot presents another prompt that is as open-ended as the initial prompt (show me another example of Gemini generating T-SQL code). Again, Gemini responds by narrowing the scope of the prompt, presenting code for the narrowed scope, and explaining the code.

The reply to this second simple prompt shows that Gemini keeps track of its answers to present a fresh code sample. The second code sample in the following screenshot illustrates the use of select, from, inner join, where, group by, and having key phrases, as well as avg, dateadd and getdate functions.

Notice that in the following code sample the having clause does not reference the AverageRating alias term in its having clause. Instead, the clause references the underlying function (AVG(r.Rating)) to which the alias applies. By avoiding using an alias in the argument for the having clause, this code sample avoids the error in the preceding code sample for the order by clause.

A Pair of Prompts for Outer Joins

The preceding section discusses two queries that illustrate the implementation of inner joins with T-SQL. This section switches the focus to outer joins.

The initial prompt seeks information about how an outer join differs from an inner join. Because of the prompt's design, there is no code in the reply. Instead, Gemini responds with three key sections:

1. Briefly describes inner joins.
2. Short summary of the three types of outer joins followed by a table contrasting inner joins, left joins, right joins, and full joins on three key features.
3. Guidance about when to use each type of join.

The following screenshot shows the prompt to and the reply from Gemini.

The second prompt within this section asks for T-SQL code samples contrasting inner joins, left outer joins, right outer joins, and full outer joins. In the reply, a separate code sample is presented for each join type, followed by a very brief explanation of the code. If each explanation is sufficient, then you can end the conversation. Otherwise, if you need additional clarification about any of the brief code explanations, you can pose new prompts for each explanation that requires clarification.

Is Gemini in Google Chrome?

Yes, Gemini became available in Google Chrome around the time Gemini was released by Google (February 2024). Many features in generative AI are relatively new and/or under development, which is true for Gemini in Google Chrome and other Google applications.

Here is the initial segment of the Google Chrome reply for the prompt that appears in the search box. The screenshot below confirms that Gemini is integrated into the Chrome browser and other Google products, such as Gmail, Drive, Sheets, and Docs. The layout of this content has the look and feel of Gemini content.

Here are excerpts from two additional page sections returned by the prompt in the preceding screenshot. These page sections have the look and feel of content returned by Google Chrome.

This new combined output from Gemini and Google Chrome represents an upgrade of the output from either application by itself. For example, the output in the preceding screen contains the same easy-to-read writing style characteristic of Gemini. On the other hand, the following two screenshots show references to multiple sources familiar with output from Google Chrome.

Google Chrome Output for a Prompt about T-SQL

Here is an excerpt from Google Chrome's reply to a question about a T-SQL topic. The prompt appears in the Google Chrome search box. It is about the syntax for an example of a case statement with when clauses, which is sometimes referred to as a case when statement.

The excerpt includes a framework example for invoking a case when statement, along with another example illustrating how to adapt the framework for adding a column named price_category to a query from a table named orders.

Also, notice that off to the right of the commentary and examples for the case when statement, there is an image link to a blog with additional content on case when statements.

Here's an excerpt for additional content returned by the same prompt in the preceding screenshot. This content references three authoritative web sources (Microsoft Learn, W3Schools, and MSSQLTips.com) with more in-depth coverage of T-SQL case statements and related topics.

Next Steps

This tip is meant to empower you to start using the Pro version of Google's generative AI application, Gemini. This version of Gemini is free to use, and the tip shows step-by-step instructions on how to get a personal account with Gemini Pro. The primary objective of the Gemini demonstrations is to show T-SQL developers how to generate code and/or commentary about SQL Server programming with T-SQL.

After reading this tip, the first step is to go to the Bard site and agree to the terms for installing and permitting you to use the Gemini chatbox on your workstation. After equipping your workstation with a Gemini workstation, you can start using Gemini as described in this tip. Alternatively, you can issue prompts to Gemini about topics you need help with regarding your current projects.

Another approach to using Gemini is using the Google Chrome browser. This tip demonstrates how to include prompts in a browser search box. If your Google browser is not a recent version, it is recommended that you update it to ensure that the browser has built-in extensions for working with Gemini. The Google Chrome browser used in this tip is version 122.0.6261.70 (Official Build) (64-bit). Using Gemini from a Google browser gives you Gemini's easy-to-read text, precise focus to answer prompts entered into a browser search box, and an extended set of web references for the words in the search box.

Test the browser for all prompts on which you seek a reply. However, do not expect the answers to be correct in every case. Generative AI is a work in progress: it is ok now, but it will likely get much better over successive iterations. Also, you may find that generative AI applications work better for some prompts than others. This is likely to result from the training text or even the underlying AI model. Despite occasional errors, sometimes referred to as hallucinations, I find generative AI valuable for many coding and research projects. I hope this tip helps you to enjoy the same experience.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips