Interactive SQL

Virtuoso Universal Server SQL Data Management
1

Hello @hwilliams,
I would like to know if Virtuoso supports the functionality to get all tables in a database, delete tables, and also join tables using SQL query, if yes, please kindly share sample queries. Thanks

2

In what database(s) are the tables you are seeking to get/delete/join located ?

3

I have a virtuoso instance I am interacting with and all the databases and tables are stored in this format DB.[database_name].[table_name]

4

Virtuoso is a standard SQL compliant database supporting standard SQL commands and queries as detailed in the Virtuoso SQL Reference Guide, which would provide all the details on DML & DDL operations that can be performed.

1 Like
5

Thanks, @hwilliams. this really helps

6

Virtuoso is a full blown DBMS, so it has the requested capability.

Note a SQL Cheatsheet I recently generated for Virtuoso using ChatGPT.

Fundamentally, Virtuoso’s SQL engine is based on the ANSI SQL 99 standard.

1 Like
7

I created some tables in this format
DB.Demo.Table1
DB.Demo.Table2
DB.Demo.Table3

I can run SQL query to delete DB.Demo.Table. (i.e. DELETE TABLE DB.Demo.Table1, and it works fine)

But what if I want to delete all Demo, I have tried running drop database DB.Demo, so I can delete all records linked to DB.Demo but I am getting an error, how can I run a query to delete database DB.Demo, just like executing DROP DATABASE database_name in SQL query

Virtuoso

Also using SQL server I can run a sample query as this

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = ‘BASE TABLE’

and this will list out all the tables I have in the database I am using, does virtuoso supports this type of functionality, let’s say I want to return all table name
DB.Demo.Table1
DB.Demo.Table2
DB.Demo.Table3
without their records, is this possible?

8

Virtuoso does not have a create database ... command and thus does not have a corresponding drop database ... command.

Virtuoso supports multiple namespaces for tables and procedures. A table or procedure is uniquely identified by a three part name consisting of qualifier, owner and name separated by dots, as detailed in the Qualifiers and Owners documentation, with the qualifier, owner and acting as a container for tables within ie DB.Demo.. A table can dynamically be created with a qualifier and owner which is automatically created if it does not already exist. Tables have to be explicitly dropped. The use statement can be used to set the default qualifier for a connected session in the database.

The query select KEY_TABLE from SYS_KEYS where KEY_TABLE like 'QUALIFIER.OWNER.%' and KEY_IS_MAIN = 1 can be run to list tables in a given QUALIFIER.OWNER in the database. The SYS_KEYS table being one of the Core System Tables containing details of tables in the database.

1 Like
9

A database is a document comprising sets of entity relationships grouped by predicates. In the case of SQL oriented entity relationships, a table is the predicate (basis) for the various sets, i.e., what identifies a particular set in the database.

The database itself uses identifiers (which may be quoted or unquoted) to qualify (partition) these sets, which breaks down as follows in the virtuoso database objects 3-part naming scheme:

  1. Database
  2. Schema
  3. Table, View, or Stored Procedure

Operations on these database objects are qualified based on the above in the database.schema.table form, i.e., that’s what you target when performing all operations, including DROP.

1 Like
10

Thanks @hwilliams and @kidehen for the responses, it really helps to clarify lots of things and also helped with what I was trying to achieve.