The following example with the OpenAI provider creates an AI profile called OPENAI and sets the OPENAI profile for the current user session.

-- Create AI profile
--
SQL> BEGIN
  DBMS_CLOUD_AI.create_profile(
      'OPENAI',
      '{"provider": "openai",
        "credential_name": "OPENAI_CRED",
        "object_list": [{"owner": "SH", "name": "customers"},
                        {"owner": "SH", "name": "sales"},
                        {"owner": "SH", "name": "products"},
                        {"owner": "SH", "name": "countries"}]
       }');
END;
/
 
PL/SQL procedure successfully completed.
 
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.set_profile('OPENAI');
 
PL/SQL procedure successfully completed.

SQL> select ai how many customers exist;
 
CUSTOMER_COUNT
--------------
         55500
 
SQL> select ai showsql how many customers exist;
 
RESPONSE
----------------------------------------------------
SELECT COUNT(*) AS total_customers
FROM SH.CUSTOMERS
 
 
SQL> select ai narrate how many customers exist;
 
RESPONSE
------------------------------------------------------
There are a total of 55,500 customers in the database.
 
SQL> select ai chat how many customers exist;
 
RESPONSE
--------------------------------------------------------------------------------
It is impossible to determine the exact number of customers that exist as it con
stantly changes due to various factors such as population growth, new businesses
, and customer turnover. Additionally, the term "customer" can refer to individu
als, businesses, or organizations, making it difficult to provide a specific num
ber.


SQL> select ai explainsql how many customers in San Francisco are married;
 
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
 
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
  - 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
  - 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
 
Remember to adjust the table and column names based on your actual schema if they differ from the example.
 
Feel free to ask if you have more questions related to SQL or database in general.

-- Feedback on SQL Text
-- Negative feedback example:
SQL > select ai feedback for query "select ai showsql how many watch histories in total", please use sum instead of count;
-- Positive feedback example:
SQL > select ai feedback for query "select ai showsql how many watch histories in total", the sql query generated is correct;
 
-- Feedback on SQL ID
-- Negative feedback example:
SQL > select ai feedback please use sum instead of count for sql_id  1v1z68ra6r9zf;
-- Positive feedback example:
SQL > select ai feedback sql query result is correct for sql_id  1v1z68ra6r9zf;
 
-- If not specified, use default LASTAI SQL
-- To use default LASTAI sql, make sure that set server output off;
-- Negative feedback example:
SQL > select ai feedback please use ascending sorting for ranking;
-- Positive feedback example:
SQL > select ai feedback the result is correct;

SQL> SELECT AI SUMMARIZE
Like countless other people around the globe, I stream music, and like more
than six hundred million of them I mainly use Spotify. Streaming currently
accounts for about eighty per cent of the American recording industry’s
revenue, and in recent years Spotify’s health is often consulted as a measure
for the health of the music business over all. Last spring, the International
Federation of the Phonographic Industry reported global revenues of $28.6
billion, making for the ninth straight year of growth. All of this was
unimaginable in the two-thousands, when the major record labels appeared
poorly equipped to deal with piracy and the so-called death of physical media.
On the consumer side, the story looks even rosier. Adjusted for inflation, a
 
... (skipped 1000 rows in the middle)
 
Pelly writes of some artists, in search of viral fame, who surreptitiously use
social media to effectively beta test melodies and motifs, basically putting
together songs via crowdsourcing. Artists have always fretted about the
pressure to conform, but the data-driven, music-as-content era feels
different. “You are a Spotify employee at that point,” Daniel Lopatin, who
makes abstract electronic music as Oneohtrix Point Never, told Pelly. “If your
art practice is so ingrained in the brutal reality that Spotify has outlined
for all of us, then what is the music that you’re not making? What does the
music you’re not making sound like?” Listeners might wonder something similar.
What does the music we’re not hearing sound like?;
 
 
RESPONSE
------------------------------------------------------------------------------
The music streaming industry, led by Spotify, has revolutionized the way
people consume music, with streaming accounting for 80% of the American
recording industry's revenue. However, this shift has also complicated the
lives of artists, who struggle to survive in a hyper-abundant present where
music is often valued for its convenience rather than its artistic merit.
Spotify's algorithms prioritize popularity and profitability over artistic
diversity, leading to a homogenization of music and a devaluation of the
labor that goes into creating it. Meanwhile, the company's executives reap
enormous profits, with CEO Daniel Ek's net worth rivaling that of the
wealthiest musicians. As music critic Liz Pelly argues, the streaming economy
raises important questions about autonomy, creativity, and the value of art
in a world where everything is readily available and easily accessible.

--Grant EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER; 

--
-- Create Credential for AI provider
--
BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'AWS_CRED',
        username    => '<your_AWS_access_key>',
        password    => '<your_AWS_secret_key>'
      );
END;
/
 
PL/SQL procedure successfully completed.
 
 
--
-- Grant Network ACL for AWS
--
BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'bedrock-runtime.us-east-1.amazonaws.com',
        ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                    principal_name => 'ADB_USER',
                    principal_type => xs_acl.ptype_db)
       );
END;
/
 
PL/SQL procedure successfully completed.
 


--
-- Create AI profile 
--
BEGIN
      DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name =>'AWS',
        attributes   =>'{"provider": "aws",
          "credential_name": "AWS_CRED",
          "object_list": [{"owner": "SH", "name": "customers"},                
                        {"owner": "SH", "name": "countries"},                
                        {"owner": "SH", "name": "supplementary_demographics"},
                        {"owner": "SH", "name": "profits"},                  
                        {"owner": "SH", "name": "promotions"},               
                        {"owner": "SH", "name": "products"}],
           "model" : "anthropic.claude-v2",
           "conversation" : "true"
          }');
END;
/
 
 
PL/SQL procedure successfully completed.


--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AWS');

PL/SQL procedure successfully completed.
 
--
-- Use AI
--

SELECT AI how many customers exist;
"RESPONSE"
"COUNT(*)"
55500


SELECT AI how many customers in San Francisco are married;
"RESPONSE"
"COUNT(*)"
46

SELECT AI showsql how many customers in San Francisco are married;
"RESPONSE"
"SELECT COUNT(*) AS "Number of Married Customers in San Francisco"
FROM "SH"."CUSTOMERS" C
WHERE UPPER(C."CUST_CITY") = UPPER('San Francisco')
AND UPPER(C."CUST_MARITAL_STATUS") = UPPER('Married')"

SELECT AI explainsql how many customers in San Francisco are married;

"RESPONSE""SELECT 
COUNT(*) AS "Number of Married Customers in San Francisco"
FROM "SH"."CUSTOMERS" C
WHERE C."CUST_CITY" = 'San Francisco'
AND C."CUST_MARITAL_STATUS" = 'Married'

Explanation:
- Used table alias C for CUSTOMERS table
- Used easy to read column names like CUST_CITY, CUST_MARITAL_STATUS
- Enclosed table name, schema name and column names in double quotes 
- Compared string values in WHERE clause without UPPER() since the values are not in double quotes
- Counted number of rows satisfying the condition and aliased the count as "Number of Married Customers in San Francisco""

SELECT AI narrate what are the top 3 customers in San Francisco;
"RESPONSE"
The top 3 customers in San Francisco ordered by credit limit in descending order are:

1. Bert Katz
2. Madallyn Ladd  
3. Henrietta Snodgrass

SELECT AI chat what is Autonomous AI Database;
"RESPONSE"
"An Autonomous AI Database is a cloud database service provided by Oracle Corporation. Some key features of Oracle Autonomous AI Database include:

- Fully automated and self-driving - The database automatically upgrades, patches, tunes, and backs itself up without any human intervention required.

- Self-securing - The database uses machine learning to detect threats and automatically apply security updates.

- Self-repairing - The database monitors itself and automatically recovers from failures and errors without downtime.

- Self-scaling - The database automatically scales compute and storage resources up and down as needed to meet workload demands. 

- Serverless - The database is accessed as a cloud service without having to manually provision any servers or infrastructure.

- High performance - The database uses Oracle's advanced automation and machine learning to continuously tune itself for high performance.

- Multiple workload support - Supports transaction processing, analytics, graph processing, etc in a single converged database.

- Fully managed - Oracle handles all the management and administration of the database. Users just load and access their data.

- Compatible - Supports common SQL and Oracle PL/SQL for easy migration from on-prem Oracle databases.

So in summary, an Oracle Autonomous AI Database is a fully automated, self-driving, self-securing, and self-repairing database provided as a simple cloud service. The automation provides high performance, elasticity, and availability with minimal human labor required."

--
--Clear the profile
--
BEGIN
   DBMS_CLOUD_AI.CLEAR_PROFILE;
END;
/
PL/SQL procedure successfully completed.

--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AWS');
 
PL/SQL procedure successfully completed.

Session-Based Conversations

Create your AI profile. Set the conversation attribute to true in the profile, this action includes content from prior interactions or prompts, potentially including schema metadata, and set your profile. Once the profile is enabled, you can begin having conversations with your data. Use natural language to ask questions and follow up as needed.

--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;

-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN  
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'api.openai.com',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'ADB_USER',
                             principal_type => xs_acl.ptype_db)
     );
    END;
    /
 
PL/SQL procedure successfully completed.
 
--
-- Create Credential for AI provider
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME   => 'OPENAI_CRED', 
username          =>  'OPENAI', 
password          =>  '<your_api_token>');
 
PL/SQL procedure successfully completed.
 
--
-- Create AI profile
--
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
  profile_name   => 'OPENAI',
  attributes     =>'{"provider": "openai",                                                                   
        "credential_name": "OPENAI_CRED",                                     
        "object_list": [{"owner": "SH", "name": "customers"},                
                        {"owner": "SH", "name": "countries"},                
                        {"owner": "SH", "name": "supplementary_demographics"},
                        {"owner": "SH", "name": "profits"},                  
                        {"owner": "SH", "name": "promotions"},               
                        {"owner": "SH", "name": "products"}],
        "conversation": "true"                
       }');                                                                  
     END;                                                                         
     / 
 
PL/SQL procedure successfully completed.
 
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
 
PL/SQL procedure successfully completed.
 
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
 
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
 
--
-- Use AI
--
what are the total number of customers;
 
CUSTOMER_COUNT
--------------
         55500
 
break out count of customers by country;   
 
RESPONSE
-----------------
COUNTRY_NAME 			CUSTOMER_COUNT
Italy 				7780
Brazil 				832
Japan 				624
United Kingdom 			7557
Germany 			8173
United States of America 	18520
France 				3833
Canada 				2010
Spain 				2039
China 				712
Singapore 			597
New Zealand 			244
Poland 	708
Australia 			831
Argentina 			403
Denmark 			383
South Africa 			88
Saudi Arabia 			75
Turkey 				91

  
what age group is most common;
 
RESPONSE
--------------------------------------------------------------------------------
AGE_GROUP 	CUSTOMER_COUNT
65+ 		28226
 
select ai keep the top 5 customers and their country by their purchases and include a rank in the result;
 
RESPONSE
--------------------------------------------------------------------------------
RANK 	CUSTOMER_NAME 		COUNTRY 	PURCHASES
1 	Abigail Ruddy 		Japan 		276
2 	Abigail Ruddy 		Italy 		168
3 	Abigail Ruddy 		Japan 		74
3 	Abner Robbinette 	Germany 	74
5 	Abner Everett 		France 		68
 
 
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
 
PL/SQL procedure successfully completed.

Customizable Conversations

• Use DBMS_CLOUD_AI.CREATE_CONVERSATION function and then set the conversation using DBMS_CLOUD_AI.SET_CONVERSATION_ID.
• Call the DBMS_CLOUD_AI.CREATE_CONVERSATION procedure directly to create and set the conversation in one step.

The following example demonstrates how to create a conversation using DBMS_CLOUD_AI.CREATE_CONVERSATION function and set it using the DBMS_CLOUD_AI.SET_CONVERSATION_ID procedure.

SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;  -- in 19c, run SELECT DBMS_CLOUD_AI.create_conversation FROM dual;
  
 
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92
 
 
EXEC DBMS_CLOUD_AI.SET_CONVERSATION_ID('30C9DB6E-EA4D-AFBA-E063-9C6D46644B92');
 
PL/SQL procedure successfully completed

The following example demonstrates running the DBMS_CLOUD_AI.CREATE_CONVERSATION procedure to create and set the conversation_id directly.

EXEC DBMS_CLOUD_AI.create_conversation;
 
PL/SQL procedure successfully completed.

You can also customize the conversation attributes such as title, description, retention_days, and conversation_length attributes.

SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION(
            attributes => '{"title":"My first conversation", 
			"description":"this is my first conversation", 
			"retention_days":5, 
			"conversation_length":5}');
  
 
CREATE_CONVERSATION
------------------------------------
38F8B874-7687-2A3F-E063-9C6D4664EC3A

You can view if a certain conversation exists by querying DBA/USER_CLOUD_AI_CONVERSATIONS view.

-- Verify the setup
SELECT conversation_id, conversation_title, description, retention_days, 
conversation_length FROM DBA_CLOUD_AI_CONVERSATIONS WHERE 
conversation_id = '38F8B874-7687-2A3F-E063-9C6D4664EC3A';
 
CONVERSATION_ID                      	CONVERSATION_TITLE                              DESCRIPTION                        RETENTION_DAYS                 CONVERSATION_LENGTH
------------------------------------ 	----------------------------------------------- ---------------------------------- ------------------------------ -------------------
38F8B874-7687-2A3F-E063-9C6D4664EC3A 	My first conversation                           this is my first conversation     +00005 00:00:00.000000         5

You can also verify if a conversation is set by calling the DBMS_CLOUD_AI.GET_CONVERSATION_ID function.

SELECT DBMS_CLOUD_AI.GET_CONVERSATION_ID;
 
 
--------------------------------------------------------------------------------
30C9DB6E-EA4F-AFBA-E063-9C6D46644B92

After you create and set the conversation and enable your AI profile, you can start interacting with your data. Use natural language to ask questions and follow up as needed.

Use SELECT AI <ACTION> <PROMPT>.

SELECT AI CHAT What is the difference in weather between Seattle and San Francisco?;
 
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco are both located on the West Coast of the United State
s, but they have distinct weather patterns due to their unique geography and cli
mate conditions. Here are the main differences:
 
1. **Rainfall**: Seattle is known for its rainy reputation, with an average annu
al rainfall of around 37 inches (94 cm). San Francisco, on the other hand, recei
ves significantly less rainfall, with an average of around 20 inches (51 cm) per
 year.
2. **Cloud Cover**: Seattle is often cloudy, with an average of 226 cloudy days
per year. San Francisco is also cloudy, but to a lesser extent, with an average
of 165 cloudy days per year.
 
......
 
 
SELECT AI CHAT Explain the difference again in one paragraph only.;
 
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco have different weather patterns despite both experienc
ing a mild oceanic climate. San Francisco tends to be slightly warmer, with aver
age temperatures ranging from 45?F to 67?F, and receives less rainfall, around 2
0 inches per year, mostly during winter. In contrast, Seattle is cooler, with te
mperatures ranging from 38?F to 64?F, and rainier, with around 37 inches of rain
fall per year, distributed throughout the year. San Francisco is also known for
its fog, particularly during summer, and receives more sunshine, around 160 sunn
y days per year, although it's often filtered through the fog. Overall, San Fran
cisco's weather is warmer and sunnier, with more pronounced seasonal variations,
 while Seattle's is cooler and rainier, with more consistent temperatures throug
hout the year.

The following example show how two conversations are used interchangeably to ask questions and verify accurate responses. Each conversation begins with a different question focused on comparison. Later, when you ask the same follow-up question in both conversations, each returns a different answer based on its prior context.

-- First conversation
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;
 
 
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92



-- Second conversation
SELECT DBMS_CLOUD_AI.CREATE_CONVERSATION;
 
 
CREATE_CONVERSATION
------------------------------------
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92


-- Call generate using the first conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
        prompt       =>  'What is the difference in weather between Seattle and San Francisco?',
        profile_name =>  'GENAI',
        action       =>  'CHAT',
        params       =>  '{"conversation_id":"30C9DB6E-EA4D-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
 
 
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco, both located in the Pacific Northwest and Northern Ca
lifornia respectively, experience a mild oceanic climate. However, there are som
e notable differences in their weather patterns:
 
1. **Temperature**: San Francisco tends to be slightly warmer than Seattle, espe
cially during the summer months. San Francisco's average temperature ranges from
 45?F (7?C) in winter to 67?F (19?C) in summer, while Seattle's average temperat
ure ranges from 38?F (3?C) in winter to 64?F (18?C) in summer.
 
2. **Rainfall**: Seattle is known for its rainy reputation, with an average annu
al rainfall of around 37 inches (94 cm). San Francisco receives less rainfall, w
ith an average of around 20 inches (51 cm) per year. However, San Francisco's ra
infall is more concentrated during the winter months, while Seattle's rainfall i
s more evenly distributed throughout the year.
 
......

-- Call generate using the second conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
        prompt       =>  'How does the cost of living compare between New York and Los Angeles?',
        profile_name =>  'GENAI',
        action       =>  'CHAT',
        params       =>  '{"conversation_id":"30C9DB6E-EA4E-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
 
 
RESPONSE
--------------------------------------------------------------------------------
The cost of living in New York and Los Angeles is relatively high compared to ot
her cities in the United States. However, there are some differences in the cost
 of living between the two cities. Here's a comparison of the cost of living in
New York and Los Angeles:
 
1. Housing: The cost of housing is significantly higher in New York than in Los
Angeles. The median home price in New York is around $999,000, while in Los Ange
les it's around $849,000. Rent is also higher in New York, with the average rent
 for a one-bedroom apartment being around $3,000 per month, compared to around $
2,400 per month in Los Angeles.
 
2. Food: The cost of food is relatively similar in both cities, with some variat
ion in the cost of certain types of cuisine. However, eating out in New York can
 be more expensive, with the average cost of a meal at a mid-range restaurant be
ing around $15-20 per person, compared to around $12-18 per person in Los Angele
s.
 
......


-- Call generate using the first conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
        prompt       =>  'Explain the difference again in one paragraph only.',
        profile_name =>  'GENAI',
        action       =>  'CHAT',
        params       =>  '{"conversation_id":"30C9DB6E-EA4D-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
 
 
RESPONSE
--------------------------------------------------------------------------------
Seattle and San Francisco have different weather patterns despite both experienc
ing a mild oceanic climate. San Francisco tends to be slightly warmer, with aver
age temperatures ranging from 45?F to 67?F, and receives less rainfall, around 2
0 inches per year, mostly during winter. In contrast, Seattle is cooler, with te
mperatures ranging from 38?F to 64?F, and rainier, with around 37 inches of rain
fall per year, distributed throughout the year. San Francisco is also known for
its fog, particularly during summer, and receives more sunshine, around 160 sunn
y days per year, although it's often filtered through the fog. Overall, San Fran
cisco's weather is warmer and sunnier, with more pronounced seasonal variations,
 while Seattle's is cooler and rainier, with more consistent temperatures throug
hout the year.


-- Call generate using the second conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
        prompt       =>  'Explain the difference again in one paragraph only.',
        profile_name =>  'GENAI',
        action       =>  'CHAT',
        params       =>  '{"conversation_id":"30C9DB6E-EA4E-AFBA-E063-9C6D46644B92"}') AS RESPONSE;
 
 
RESPONSE
--------------------------------------------------------------------------------
The cost of living in New York is approximately 20-30% higher than in Los Angele
s, mainly due to the higher cost of housing and transportation. New York has a m
edian home price of around $999,000 and average rent of $3,000 per month for a o
ne-bedroom apartment, compared to Los Angeles' median home price of $849,000 and
 average rent of $2,400 per month. While the cost of food and utilities is relat
ively similar in both cities, the cost of transportation is higher in Los Angele
s due to its car-centric culture, but the cost of public transportation is highe
r in New York. Overall, the total monthly expenses for a single person in New Yo
rk can range from $4,600, compared to around $4,050 in Los Angeles, making New Y
ork the more expensive city to live in.

You may call the DBMS_CLOUD_AI.GENERATE function without specifying a conversation; however, in such cases, a meaningful response should not be expected.

-- Ask SELECT AI using the second conversation.
SELECT DBMS_CLOUD_AI.GENERATE(
        prompt       =>  'Explain the difference again in one paragraph only.',
        profile_name =>  'GENAI',
        action       =>  'CHAT') AS RESPONSE;
 
 
RESPONSE
--------------------------------------------------------------------------------
There is no previous explanation to draw from, as this is the beginning of our c
onversation. If you would like to ask a question or provide a topic, I would be
happy to explain the differences related to it in one paragraph.

You can query the DBMS_CLOUD_AI conversation views to review conversation and prompt details. See for more details.

SELECT conversation_id, conversation_title, description FROM dba_cloud_ai_conversations;
 
CONVERSATION_ID
------------------------------------
CONVERSATION_TITLE
----------------------------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92
Seattle vs San Francisco Weather
The conversation discusses the comparison of weather patterns between Seattle an
d San Francisco, focusing on the differences in temperature, rainfall, fog, suns
hine, and seasonal variation between the two cities.
 
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92
NY vs LA Cost Comparison
The conversation discusses and compares the cost of living in New York and Los A
ngeles, covering housing, food, transportation, utilities, and taxes to provide
an overall view of the expenses in both cities.

SELECT conversation_id, count(*) FROM dba_cloud_ai_conversation_prompts
     GROUP BY conversation_id;
 
CONVERSATION_ID                COUNT(*)
------------------------------------ ----------
30C9DB6E-EA4D-AFBA-E063-9C6D46644B92          2
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92          2

You can update the title, description, and retention_days of a conversation using the DBMS_CLOUD_AI.UPDATE_CONVERSATION procedure. You can verify the update by querying the DBMS_CLOUD_AI conversation view.

-- Update the second conversation's title, description and retention_days
SQL> EXEC DBMS_CLOUD_AI.update_conversation(conversation_id => '30C9DB6E-EA4E-AFBA-E063-9C6D46644B92', 
											attributes => '{"retention_days":20, 
														"description":"This a description", 
														"title":"a title", 
														"conversation_length":20}');
 
PL/SQL procedure successfully completed.
 
 
-- Verify the information for the second conversation
SQL> SELECT conversation_title, description, retention_days
FROM dba_cloud_ai_conversations
WHERE conversation_id = '30C9DB6E-EA4E-AFBA-E063-9C6D46644B92';
 
CONVERSATION_TITLE         DESCRIPTION                          RETENTION_DAYS         LENGTH
-------------------------- ------------------------------------ -------------- --------------
a title                    This a description                   20                         20

You can delete an individual prompt from your conversations and verify the modification by querying the DBMS_CLOUD_AI conversation view.

-- Find the latest prompt for first conversation
SELECT conversation_prompt_id FROM dba_cloud_ai_conversation_prompts
     WHERE conversation_id = '30C9DB6E-EA4D-AFBA-E063-9C6D46644B92'
     ORDER BY created DESC
     FETCH FIRST ROW ONLY;
 
CONVERSATION_PROMPT_ID
------------------------------------
30C9DB6E-EA61-AFBA-E063-9C6D46644B92
 
 
-- Delete the prompt
EXEC DBMS_CLOUD_AI.DELETE_CONVERSATION_PROMPT('30C9DB6E-EA61-AFBA-E063-9C6D46644B92');
 
PL/SQL procedure successfully completed.
 
 
-- Verify if the prompt is deleted
SELECT conversation_prompt_id FROM dba_cloud_ai_conversation_prompts
WHERE conversation_id = '30C9DB6E-EA4D-AFBA-E063-9C6D46644B92';
 
-- Only one prompt now
CONVERSATION_PROMPT_ID
------------------------------------
30C9DB6E-EA5A-AFBA-E063-9C6D46644B92

You can delete the entire conversation, which also removes all prompts associated with it.

-- Delete the first conversation
EXEC DBMS_CLOUD_AI.DROP_CONVERSATION('30C9DB6E-EA4D-AFBA-E063-9C6D46644B92');
 
PL/SQL procedure successfully completed.
 
 
-- Verify if the conversation and its prompts are removed
SELECT conversation_id FROM dba_cloud_ai_conversations;
 
-- We only have the second conversation now
CONVERSATION_ID
------------------------------------
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92
 
 
SELECT conversation_id, count(*) FROM dba_cloud_ai_conversation_prompts GROUP BY conversation_id;
 
-- We only have prompts in the second conversation
CONVERSATION_ID                COUNT(*)
------------------------------------ ----------
30C9DB6E-EA4E-AFBA-E063-9C6D46644B92          2

SQL> select ai showsql how many movies;
 
RESPONSE
------------------------------------------------------------------------
SELECT COUNT(m."MOVIE_ID") AS "Number of Movies" FROM "ADB_USER"."MOVIES" m
 
SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1', sql_text=> 'select ai showsql how many movies', feedback_type=> 'negative', response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"');
 
PL/SQL procedure successfully completed.
SQL> select CONTENT, ATTRIBUTES from OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB where JSON_VALUE(attributes, '$.sql_text') = 'select ai showsql how many movies';
 
CONTENT                                                
----------------------------------------------------------------------------------------------------
how many movies                                             
ATTRIBUTES
----------------------------------------------------------------------------------------------------
 
{"response":"SELECT SUM(1) FROM \"ADB_USER\".\"MOVIES\"","feedback_type":"negative","sql_id":null,"sql_text":"select ai showsql how many movies","feedback_content":null}

The following example demonstrates providing your approval that you agree and confirm the generated SQL (positive feedback) using feedback_type as positive.

In this example, the query retrieves the sql_id from the v$mapped_sql view for the given prompt. See V_MAPPED_SQL for more information.

You add your feedback to the AI profile named OCI_FEEDBACK1 by calling the DBMS_CLOUD_AI.FEEDBACK procedure with the sql_id parameter. Then, you retrieve the content and attributes columns from the <profile_name>_FEEDBACK_VECINDEX$VECTAB table, which is linked to that specific SQL query. Select AI automatically creates this vector table when you first use the feedback feature. See for more information.

SQL> select ai showsql how many distinct movie genres?;
 
RESPONSE
-----------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT g."GENRE_NAME") AS "Number of Movie Genres" FROM "ADB_USER"."GENRES" g
 
SQL> SELECT sql_id FROM v$mapped_sql WHERE sql_text = 'select ai showsql how many distinct movie genres?';
 
SQL_ID
-------------
852w8u83gktc1
 
SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1', sql_id=> '852w8u83gktc1', feedback_type=>'positive', operation=>'add');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT content, attributes FROM OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB WHERE JSON_VALUE(attributes, '$.sql_id') ='852w8u83gktc1';
 
 CONTENT                                               
---------------------------------------------------------------------------------------------------- 
how many distinct movie genres?
ATTRIBUTES
----------------------------------------------------------------------------------------------------
{"response":"SELECT COUNT(DISTINCT g.\"GENRE_NAME\") AS \"Number of Movie Genres\" FROM \"ADB_USER\".\"GENRES\" g","feedback_type":"positive","sql_id":"852w8u83gktc1","sql_text":"select ai showsql how many distinct movie genres?","feedback_content":null}

You may provide feedback for SQL prompts even if the prompt has not been used previously. For example:

BEGIN
  DBMS_CLOUD_AI.FEEDBACK(
    profile_name=>'AI_PROFILE',
    sql_text=>'select ai runsql how many products named PAD', -- Prior usage not required
    feedback_type=>'negative',
    response=>'SELECT COUNT(*) AS "Num" FROM "PRODUCTS"."CATG" o WHERE UPPER(o."NAME") LIKE ''%PAD%''',
    feedback_content=>'Use LIKE instead of ='
  );
END;
/

In this case, feedback for the prompt select ai runsql how many products named PAD is submitted without needing to use the prompt beforehand.

See for more details on the parameters.

EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
                                   sql_id=> '852w8u83gktc1',
                                   feedback_type=>'positive',
                                   operation=>'add');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
                                   sql_text=> 'select ai showsql how many movies',
                                   feedback_type=> 'negative',
                                   response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"',
                                   feedback_content=>'Use SUM instead of COUNT');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
                                   sql_id=> '852w8u83gktc1',
                                   operation=>'delete');

This example demonstrates using feedback action to improve the generated SQL by suggesting the modifications using natural language.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name=>'OCI_FEEDBACK1',
    attributes=>'{"provider": "oci",                                                                 
      "credential_name": "GENAI_CRED",
      "oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
      "object_list": [{"owner": "ADB_USER", "name": "users"},
                      {"owner": "ADB_USER", "name": "movies"},
                      {"owner": "ADB_USER", "name": "genres"},
                      {"owner": "ADB_USER", "name": "watch_history"},
                      {"owner": "ADB_USER", "name": "movie_genres"},
                      {"owner": "ADB_USER", "name": "employees1"},
                      {"owner": "ADB_USER", "name": "employees2"}
                        ]
      }');
END;
/
 
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_FEEDBACK1');

 
PL/SQL procedure successfully completed.
 

select ai showsql rank movie duration;
 
RESPONSE
-------------------------------------------------------------------------------
SELECT "DURATION" AS "Movie Duration" FROM "ADB_USER"."MOVIES" ORDER BY "DURATION"
 
select ai feedback use ascending sorting;
 
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "rank movie duration" is successfully refined. The refined SQL query as following:
SELECT m."DURATION" AS "Movie Duration" FROM "ADB_USER."MOVIES" m ORDER BY m."DURATION" ASC
 
 
select ai showsql rank the movie duration;
 
RESPONSE
-----------------------------------------------------------------------------------------
SELECT m."DURATION" AS "Movie Duration" FROM "ADB_USER."MOVIES" m ORDER BY m."DURATION" ASC

This example demonstrates using the feedback action to accept the generated SQL using natural language.

--Positive feedback

select ai showsql which movies are comedy?;
 
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT m."TITLE" AS "Movie Title" FROM "ADB_USER"."MOVIES" m INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID" INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID" WHERE g."GENRE_NAME" = 'comedy'
 
select ai feedback this is correct;
 
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thank you for your positive feedback. The SQL query for prompt "which movies are comedy?" is correctly implemented and delivering the expected results. It will be referenced for future optimizations and improvements.


Select AI Feedback Action Referring SQL_ID

This example demonstrates using SQL_ID with the feedback action to provide feedback for a particular generated SQL query. You can obtain the SQL_ID by querying the v$MAPPED_SQL table.

-- Query mentioned with SQL_ID

select ai showsql how many movies are in each genre;
 
RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT g."GENRE_NAME" AS "Genre Name", COUNT(m."MOVIE_ID") AS "Number of Movies" FROM "ADB_USER"."MOVIES" m INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID" INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID" GROUP BY g."GENRE_NAME"
 
select sql_id from v$cloud_ai_sql where sql_text = 'select ai showsql how many movies are in each genre';
 
SQL_ID
-------------
8azkwc0hr87ga
 
select ai feedback for query with sql_id = '8azkwc0hr87ga', rank in descending sorting;
 
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "how many movies are in each genre" is successfully refined. The refined SQL query as following:
SELECT g."GENRE_NAME" AS "Genre Name", COUNT(m."MOVIE_ID") AS "Number of Movies"
FROM "ADB_USER"."MOVIES" m
INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID"
INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID"
GROUP BY g."GENRE_NAME"
ORDER BY COUNT(m."MOVIE_ID") DESC

This example shows the feedback action for a specific Select AI query by including the Select AI prompt in quotes followed by your feedback.

-Query mentioned with SQL_TEXT

select ai showsql how many watch history in total;
 
RESPONSE
----------------------------------------------------------------------------------
SELECT COUNT(w."WATCH_ID") AS "Total Watch History" FROM "ADB_USER"."WATCH_HISTORY" w
 
select ai feedback for query "select ai showsql how many watch history in total", name the column as total_watch;
 
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "how many watch history in total" is successfully refined. The refined SQL query as following:
SELECT COUNT(w."WATCH_ID") AS "total_watch" FROM "ADB_USER"."WATCH_HISTORY" w

This example shows how to define multiple property graphs in your AI profile, including a sample query and its output.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
      profile_name =>'OPENAI',
      attributes   =>'{"provider": "openai",
        "model": "gpt-4o",
        "credential_name": "OPENAI_CRED",
        "object_list": [{"owner": "ADB_USER", "name": "LDBC_GRAPH"},
                        {"owner": "ADB_USER", "name": "G"}]
       }');                                                                  
END;                                                                         
/

SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE(profile_name => 'OPENAI');

PL/SQL procedure successfully completed.

SQL> select ai who bought a dress;

CUSTOMER_N
----------
Kate

1 row selected.

The following example creates sample tables and a property graph.

CREATE TABLE Person
(
    id         NUMBER PRIMARY KEY,
    firstName  VARCHAR2(20 CHAR),
    lastName   VARCHAR2(20 CHAR),
    age        NUMBER,
    jsonProp   VARCHAR2(40 CHAR)
);

CREATE TABLE Post
(
    id         NUMBER PRIMARY KEY,
    content    VARCHAR2(20 CHAR)
);

CREATE TABLE personLikesPost
(
    idPerson NUMBER REFERENCES Person (id),
    idPost   NUMBER REFERENCES Post (id)
);

CREATE TABLE personKnowsPerson
(
    idPerson1 NUMBER REFERENCES Person (id),
    idPerson2 NUMBER REFERENCES Person (id)
);

CREATE PROPERTY GRAPH person_graph
  VERTEX TABLES (
    Person KEY (id) LABEL Person
      PROPERTIES (firstName, lastName, age, jsonProp),
    Post KEY (id) LABEL Post
      PROPERTIES(content)
  )
  EDGE TABLES (
    personLikesPost
      KEY(idPerson, idPost)
      SOURCE KEY (idPerson) REFERENCES Person (id)
      DESTINATION KEY (idPost) REFERENCES POST (id)
      LABEL Likes NO PROPERTIES,
    personKnowsPerson
      KEY(idPerson1, idPerson2)
      SOURCE KEY (idPerson1) REFERENCES Person (id)
      DESTINATION KEY (idPerson2) REFERENCES Person (id)
      LABEL Knows NO PROPERTIES
  );

insert into Person values (1, 'John', 'Doe',23, '{"key1":"value1","key2":"value2"}');
insert into Person values (2, 'Scott', 'Tiger', 25, '{"key1":"value3","key2":"value4"}');
insert into Person values (3, 'Max', 'Power', 27, '{"key1":"value5","key2":"value6"}');
insert into Person values (4, 'Jane', 'Doe', 22, '{"key1":"value7","key2":"value8"}');
insert into Person (id, Firstname, age) values (5, 'Hans', 23);
insert into Person (id, Firstname, age) values (6, 'Franz', 24);
 
INSERT INTO Post VALUES (10, 'Lorem ipsum...');
INSERT INTO Post VALUES (11, 'Nulla facilisi...');
INSERT INTO Post VALUES (12, 'Vestibulum eget ..');
INSERT INTO Post VALUES (13, 'Sed fermentum...');
INSERT INTO Post VALUES (14, 'Fusce at ...');
INSERT INTO Post VALUES (15, 'Pellentesque sit ...');
INSERT INTO Post VALUES (16, 'Integer...');
INSERT INTO Post VALUES (17, 'Curabitur luctus ...');
INSERT INTO Post VALUES (18, 'Nam in ...');
INSERT INTO Post VALUES (19, 'Etiam ac ...');
 
insert into personKnowsPerson values (1, 2);
insert into personKnowsPerson values (2, 3);
insert into personKnowsPerson values (3, 4);
insert into personKnowsPerson values (4, 5);
insert into personKnowsPerson values (5, 6);
insert into personKnowsPerson values (6, 2);
insert into personKnowsPerson values (5, 3);
 
INSERT INTO personLikesPost VALUES (1, 10);
INSERT INTO personLikesPost VALUES (1, 11);
INSERT INTO personLikesPost VALUES (1, 12);
INSERT INTO personLikesPost VALUES (2, 10);
INSERT INTO personLikesPost VALUES (2, 13);
INSERT INTO personLikesPost VALUES (2, 14);
INSERT INTO personLikesPost VALUES (3, 11);
INSERT INTO personLikesPost VALUES (3, 15);
INSERT INTO personLikesPost VALUES (3, 16);
INSERT INTO personLikesPost VALUES (4, 12);
INSERT INTO personLikesPost VALUES (4, 17);
INSERT INTO personLikesPost VALUES (4, 18);
INSERT INTO personLikesPost VALUES (5, 13);
INSERT INTO personLikesPost VALUES (5, 14);
INSERT INTO personLikesPost VALUES (5, 19);
INSERT INTO personLikesPost VALUES (6, 15);
INSERT INTO personLikesPost VALUES (6, 16);
INSERT INTO personLikesPost VALUES (6, 17);
INSERT INTO personLikesPost VALUES (1, 18);
INSERT INTO personLikesPost VALUES (2, 19);

commit;

Prompt: Find all the people IDs

SELECT person_id
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (v IS "PERSON")
    COLUMNS (VERTEX_ID(v) AS person_id))

Prompt: Find all the edge IDs

SELECT edge_id
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (v1) -[e]-> (v2)
    COLUMNS (EDGE_ID(e) AS edge_id))

Prompt: Find all the knows relationship IDs

SELECT knows_id
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p1 IS "PERSON") -[e IS "KNOWS"]-> (p2 IS "PERSON")
    COLUMNS (EDGE_ID(e) AS knows_id))

Prompt: List all people who know someone who liked the post 'Sed fermentum...'

SELECT person_id, person_firstname, person_lastname
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p1 IS "PERSON") -[e1 IS "KNOWS"]-> (p2 IS "PERSON") -[e2 IS "LIKES"]-> (post IS "POST")
    WHERE UPPER(post."CONTENT") = UPPER('Sed fermentum...')
    COLUMNS (VERTEX_ID(p1) AS person_id, p1."FIRSTNAME" AS person_firstname, p1."LASTNAME" AS person_lastname))

Prompt: Find first name and last name of all people

SELECT first_name, last_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON")
    COLUMNS (p."FIRSTNAME" AS first_name, p."LASTNAME" AS last_name))

Prompt: Find post contents liked by John Doe

SELECT post_content
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON") -[e IS "LIKES"]-> (post IS "POST")
    WHERE UPPER(p."FIRSTNAME") = UPPER('John') AND UPPER(p."LASTNAME") = UPPER('Doe')
    COLUMNS (post."CONTENT" AS post_content))

Prompt: Show all people (full name) and display their key1 value from jsonProp property

SELECT person_fullname, json_key1
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON")
    COLUMNS (
	(p."FIRSTNAME" || ' ' || p."LASTNAME") AS person_fullname,
	JSON_QUERY(p."JSONPROP", '$.key1') AS json_key1
    )
)

Prompt: Find friends of Scott Tiger ordered by their last name

SELECT friend_firstname, friend_lastname
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON") -[e IS "KNOWS"]-> (f IS "PERSON")
    WHERE UPPER(p."FIRSTNAME") = UPPER('Scott') AND UPPER(p."LASTNAME") = UPPER('Tiger')
    COLUMNS (f."FIRSTNAME" AS friend_firstname, f."LASTNAME" AS friend_lastname)
)
ORDER BY friend_lastname

Prompt: Find all people ordered by first name. Skip one result and return 2 results only

SELECT person_firstname
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p IS "PERSON")
    COLUMNS (p."FIRSTNAME" AS person_firstname))
ORDER BY person_firstname
OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY

Unsupported Queries

For the following queries certain LLMs generate valid NL2SQL, but the resulting SQL uses features that are not yet supported in Oracle AI Database 26ai.

Prompt: Find people that do not know Scott.

EXISTS subquery is not supported.

SELECT person_id, first_name, last_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (p1 IS "PERSON")
    WHERE NOT EXISTS (
	SELECT 1
	FROM GRAPH_TABLE("ADB_USER"."PERSONGRAPH"
	    MATCH (p2 IS "PERSON") -[e IS "PERSONKNOWSPERSON"]-> (p3 IS "PERSON"
)
	    WHERE p2."ID" = p1."ID" AND UPPER(p3."FIRSTNAME") = UPPER('Scott')
	    COLUMNS (1 AS dummy_value))
    )
    COLUMNS (p1."ID" AS person_id, p1."FIRSTNAME" AS first_name, p1."LASTNAME" A
S last_name))

Prompt: Show all people and how many posts they have liked (show people even if they have not liked a post).

OPTIONAL match is not supported.

SELECT person_id, person_firstname, person_lastname, liked_post_ids
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
MATCH (p is "PERSON") OPTIONAL MATCH (p) -[l is "PERSONLIKESPOST"]-> (post is "POST")
COLUMNS(
    VERTEX_ID(p) as person_id,
    p."FIRSTNAME" as person_firstname,
    p."LASTNAME" as person_lastname,
    JSON_ARRAYAGG(VERTEX_ID(post)) as liked_post_ids
))

Prompt: Find all people that Scott can reach.

Queries that use unbounded quantifiers are not supported.

SELECT person_id, person_firstname, person_lastname
FROM GRAPH_TABLE("ADB_USER"."PERSONGRAPH"
    MATCH (src IS "PERSON") -[e IS "PERSONKNOWSPERSON"]->* (dst IS "PERSON")
    WHERE src."FIRSTNAME" = 'Scott'
    COLUMNS (
	VERTEX_ID(dst) AS person_id,
	dst."FIRSTNAME" AS person_firstname,
	dst."LASTNAME" AS person_lastname
    )
)

Intermittent Queries

LLMs have been shown to struggle when translating queries that require more than one GRAPH_TABLE operator. The following are such examples:

Prompt: Show people who have liked all the same posts as Hans

SELECT person_id, person_name
FROM GRAPH_TABLE("PERSON_GRAPH"
  MATCH (hans is "PERSON") -[likes_hans is "PERSONLIKESPOST"]-> (post is "POST"),
	(other_person is "PERSON") -[likes_other is "PERSONLIKESPOST"]-> (post)
  WHERE hans."FIRSTNAME" = 'Hans'
  COLUMNS (VERTEX_ID(other_person) as person_id, other_person."FIRSTNAME" AS person_name)
)
WHERE NOT EXISTS (
  SELECT 1
  FROM GRAPH_TABLE("PERSONGRAPH"
    MATCH (hans is "PERSON") -[likes_hans is "PERSONLIKESPOST"]-> (post is "POST")
    WHERE hans."FIRSTNAME" = 'Hans'
    COLUMNS (VERTEX_ID(post) as post_id)
  ) hans_posts
  LEFT JOIN GRAPH_TABLE("PERSONGRAPH"
    MATCH (other_person is "PERSON") -[likes_other is "PERSONLIKESPOST"]-> (post
 is "POST")
    COLUMNS (VERTEX_ID(post) as post_id)
  ) other_posts
  ON hans_posts.post_id = other_posts.post_id
  WHERE other_posts.post_id IS NULL
)

Prompt: Find all names of the people that can be reached in a 1 to 3 edge path

SELECT person_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (start_person IS "PERSON") -[e IS "KNOWS"]->{1,3} (end_person IS "PERSON")
    COLUMNS (end_person."FIRSTNAME" AS person_name))

Prompt: Find all names of the people that can be reached in a 1 to 3 edge path where each person is younger than the next one

SELECT person_name
FROM GRAPH_TABLE("ADB_USER"."PERSON_GRAPH"
    MATCH (start_person IS "PERSON") ((v1 IS "PERSON") -[e IS "KNOWS"]-> (v2 IS"PERSON") WHERE v1."AGE" < v2."AGE"){1,3} (end_person IS "PERSON")
    COLUMNS (end_person."FIRSTNAME" AS person_name))

LLMs often struggle with translating queries that require grouping and aggregation. A common mistake is placing aggregations in the COLUMNS clause instead of the SELECT clause.

Prompt: Find the average number of posts liked by all the users

SELECT AVG(COUNT(post)) AS average_liked_count
FROM GRAPH_TABLE("PERSON_GRAPH"
MATCH (p IS "PERSON") -[e IS "PERSONLIKESPOST"]-> (post IS "POST")
COLUMNS (VERTEX_ID(p) AS person, VERTEX_ID(post) AS post))
GROUP BY person;