This page is a basic overview of coding examples for the SQL programming language in my gitlab repository.
Various vendors of relational database management systems build upon the SQL standard, PostgreSQL being our preferred choice for most research applications. I have also included examples using SQLite3, MariaDB, and Oracle with some Python scripts to assist with initial research into creating APIs between platforms. Many of these scripts can later be modified to craft monitoring dashboards using your GUI of choice.
———————————-
Guide to the PostgreSQL examples.
Some of the examples can be directly loaded into a PostgreSQL database via an interactive shell, others use Python Jupyter notebooks to test and create scripts that can be scheduled to run automatically.
General concepts for building tables, select statements, joins and inserts are covered in notebooks that begin with two zeros.
Data sets are chosen for their cross platform compatibility, you should be able to load them into PostgreSQL, Oracle and SQLite3. I have included spreadsheets and a Python module called Faker to generate high quality testing data sets.
Links to SQL code sources:
————————————
| Learning PostgreSQL | https://www.packtpub.com/big-data-and-business-intelligence/learning-postgresql |
| SQL Pocket Guide by Jonathan Gennick | https://www.oreilly.com |
| Sam’s Teach Yourself SQL | https://www.oreilly.com/library/view/sams-teach-yourself/9780132603911 |
| Dave Berry : Pluralsight | https://www.pluralsight.com/authors/david-berry |
Guide to files in gitlab.com/sql repository.
————————————–
SQLite3 examples can be found in 00_mt_SQLite_builds.ipynb. The SQLite3 schema contain basic examples of how to handle data types, dates are stored a text columns, text to integer conversions etc. The cursor examples will work on a local host, multi-system transactions will require additional network configurations and additional steps to secure system level passwords.
Basic Oracle database code examples are included in 00_mt_SQL_Oracle_core_builds.ipynb, you should be able to paste these code blocks into SQL*Developer. Most of the topics here are focused on getting data either into or out of an Oracle system, core data structures and dynamic PL/SQL statements are also covered.
This notebook will create the output files prospective_students_import_oracle.sql, treasure_map_yorders.sql. You should be able to paste these into an IDE console or terminal shell.
PostgreSQL
I frequently choose the open source object-relational database system PostgreSQL for most research based applications.
PostgreSQL examples that cover the basics are covered in 00_mt_PostgreSQL_core_builds.ipynb. This notebook will create the output files mt_botanica_shema.sql, pg_test_conn.sh.
————————————-
Contact me if you would like to develop your own managed custom database.