1
$\begingroup$

Mathematica has some excellent data query and analysis methods, but can also perform as a front-end to an SQL database.

Having worked as an SQL DBA in the past I would like to investigate further what Mathematica can do, for that purpose I am looking for some ( any ) online avalaible SQL database I can connect to and perform queries on.

I have been told that there are tons of online SQL practice databases. My bad is that I wasn't able to find any. From that my thoughts went to Google Trends, although it doesn't seem to have a connection to query front-ends other than it's own. It doesn't have to be a Google Trends database.

Basically any ( relatively large ) database will do.

Can anyone suggest a way to go further with this?

Thanks in advance.

$\endgroup$
4
  • $\begingroup$ learnsql.com/blog/free-online-datasets-to-practice-sql $\endgroup$
    – Syed
    Commented Jan 17, 2022 at 14:52
  • $\begingroup$ I have been there, thank you Syed. I need an SQL database connection string : user / password / networkaddress:port ; did you find one on this blog ? $\endgroup$ Commented Jan 17, 2022 at 14:55
  • 1
    $\begingroup$ I think that a service (such as querying someone's database) would not come for free except for the most limited usage; but you can download datasets to practice on your own computer. Another good source for data: databank.worldbank.org/home $\endgroup$
    – Syed
    Commented Jan 17, 2022 at 15:16
  • 1
    $\begingroup$ There is a more modern (than DatabaseLink) framework shipped with Mathematica starting from version 12, which uses entities and EntityFramework as a front end and generates SQL for a specific backend, for you. It is however limited to reading from databases, at least in terms of documented functionality. You can look at this Q/A for a shorter description, and here is a longer, more detailed tutorial $\endgroup$ Commented Jan 19, 2022 at 22:43

1 Answer 1

2
$\begingroup$

You can experiment with the RNACentral public PostgreSQL database.

Needs["DatabaseLink`"]

conn = OpenSQLConnection[
  JDBC["PostgreSQL", "hh-pgsql-public.ebi.ac.uk/pfmegrnargs"],
  "Username" -> "reader", "Password" -> "NWDMCE5xdipIjRrp"
  ]

List of tables

tables = SQLTableNames[conn]

Columns in the xref table

SQLColumns[conn, "xref"]

Extract just the column names

SQLColumns[conn, "xref"] // 
 Cases[#, SQLColumn[{"xref", column_}, ___] :> column] &

Execute a query using an SQL string.

SQLExecute[conn, "select * from xref where ac = 'OTTHUMT00000106564.1'"]

Same query using WL SQLSelect expression queries

SQLSelect[conn, "xref", SQLColumn["ac"] == "OTTHUMT00000106564.1"]

Close the connection when you are done

CloseSQLConnection[conn]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.