SQL Interpreter

Graph Studio provides a SQL interpreter which allows you to run SQL statements in a notebook paragraph.

To use the SQL interpreter, you must specify %sql at the beginning of the notebook paragraph and then input the SQL statement. You can run only one SQL statement in a single paragraph.

Tip:

You can hover over the bottom part of a notebook paragraph and click the Add SQL Paragraph icon to open a SQL paragraph instantly in the notebook.

The database connection is established for the currently logged in user. For example, the following SQL statement retrieves the name of the user logged on to the database.

%sql
-- Get Current user
SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL;

The following examples describe a few scenarios using the SQL interpreter.

Example: Visualization Using Charts

You can visualize any tabular output from a SQL query using charts in a notebook paragraph. For example, the following SQL query to determine the products bought by a specific customer, is visualized using a Bar Chart:

%sql
SELECT p.prod_name, count(p.prod_name) AS sold
FROM sh.products p, sh.sales s, sh.customers c
WHERE p.prod_id = s.prod_id AND s.cust_id = c.cust_id AND c.cust_id= 3221
GROUP BY p.prod_name;

Description of the illustration sql_viz.png

Example: Creating, Querying, and Deleting SQL Property Graphs

Using the SQL interpreter, you can work with SQL property graphs in Oracle Database 23ai, currently available with Always Free Autonomous Database. Therefore, if you are using an Always Free Autonomous Database instance with Oracle Database 23ai, then you can create and query SQL property graphs.

The following code uses the CREATE PROPERTY GRAPH DDL statements for creating a SQL property graph in a notebook paragraph:

%sql
CREATE PROPERTY GRAPH bank_sql_pg
  VERTEX TABLES (
    bank_accounts
      KEY (id)
      LABEL account
      PROPERTIES ALL COLUMNS
  )
  EDGE TABLES (
    bank_txns
      KEY (txn_id)
      SOURCE KEY (from_acct_id) REFERENCES bank_accounts (id)
      DESTINATION KEY (to_acct_id) REFERENCES bank_accounts (id)
      LABEL transfer
      PROPERTIES ALL COLUMNS
  );

You can query the SQL property graph using SQL graph queries.

%sql
SELECT * FROM GRAPH_TABLE (bank_sql_pg
  MATCH
  (a IS account WHERE a.id = 816) -[e IS transfer]-> (b IS account)
  COLUMNS (a.id AS acc_a, e.amount AS amount, b.id AS acc_b)
);

The preceding query produces the following output:

ACC_A	AMOUNT	ACC_B
816	8781	287
816	6381	590
816	9011	934
816	6890	289
816	4443	812

Finally, you can delete the SQL property graph using the DROP PROPERTY GRAPH DDL statement as shown:

%sql
DROP PROPERTY GRAPH bank_sql_pg;

See Also:

• SQL DDL Statements for Property Graphs in Oracle Database Graph Developer's Guide for Property Graph
• SQL Graph Queries in Oracle Database Graph Developer's Guide for Property Graph

Example: Creating and Using Custom Database Views for PGQL Property Graphs

Another example scenario is to create custom database views using the SQL interpreter, which are then used to create a property graph. Note that this example scenario applies only for PGQL property graphs.

As shown in the following sequence of SQL paragraphs, database views are created on the SALES and CUSTOMERS tables in SH schema. Also, the primary key and foreign key constraints are defined for the views.

%sql
CREATE VIEW sh_customers 
AS SELECT cust_id, cust_first_name, cust_last_name, country_id, cust_city, cust_state_province 
FROM sh.customers;

%sql
ALTER VIEW sh_customers
ADD CONSTRAINT shcustomers_id PRIMARY KEY (cust_id) 
DISABLE NOVALIDATE;

%sql
CREATE VIEW sh_sales 
AS SELECT rownum sale_id, cust_id, prod_id, channel_id, promo_id, quantity_sold, amount_sold 
FROM sh.sales;

%sql
ALTER VIEW sh_sales 
ADD CONSTRAINT shsales_id PRIMARY KEY (sale_id) 
DISABLE NOVALIDATE;

%sql
ALTER VIEW sh_sales 
ADD CONSTRAINT shsale_cust_fk FOREIGN KEY (cust_id) 
REFERENCES sh_customers DISABLE NOVALIDATE;

You can then create a PGQL Property Graph graph using these database views (see Create a Property Graph from Existing Relational Tables) and then perform graph visualizations in a PGQL (PGX) paragraph as shown:

Description of the illustration sales_graph_viz.png