Background Reviewing some SQL and wanted to point out a tiny dissimilarity between database platforms. Data Our test table is Oracle's SCOTT.EMP Here is what the data looks like :- Substring/Substr The HIREDATE is the only well formatted fixed length string. Its format is DD-MMM-YY. Query Oracle SQL Output Explanation Oracle relies on Substr to … Continue reading ANSI Standards – Database – Substring
Database ( Technical )
PostgreSQL :- Datatype – bpchar
Background I went back and started reviewing the script uses for creating the customer table in the Northwind database. northwind pthom We are using the script furnished by pthom. It is published via GitHub and availed here. Here is the portion that creates the customer table What is bpchar? The customer_id column is defined as bpchar … Continue reading PostgreSQL :- Datatype – bpchar
SQL Beautifier
Background As a Database Administrator get so see a bit of SQL Statements. Avenues The SQL gets to the DBA through various avenues, inclusive :- Developers Submitted to review and execute against Production Database Monitoring Tools In quite a few cases, the first thing one has to do is space things out a bit. … Continue reading SQL Beautifier
dbfiddle – Constraints
Background A couple of posts ago spoke about db<>Fiddle. In this post we will start highlighting features that are not available due to security and feature set constraints in a shared environment. Legend db<>fiddle Initial Link Code Schema Create SQL Fails SQL Error SQL Works SQL Memory Optimized Table Create SQL Fails Error Delimiter/GO SQL … Continue reading dbfiddle – Constraints
dbfiddle
Background Reading through online SQL discussions and found out about db<>fiddle. Code Here is a sample code. db<>Fiddle URL dbFiddle is available here. Database Platform Supported As of 2019-07-07, here are some of the database platforms supported :- SQL Server SQL Server - v2017 Sample Code Our little sample code is available here. Image Explanation … Continue reading dbfiddle
“Entity Relationship” meets poetry
Background Researching an issue and wanted to see the best choice for a name. Googled on it and found good reasoning. Question The Question is :- Image Text Table: Color Columns: Id, ColorName, ColorCode Table: Shape Columns: Id, ShapeName, VertexList What should I call the table that maps color to shape? Table: ??? Columns: ColorId, … Continue reading “Entity Relationship” meets poetry
DBeaver – Table Relationships
Background Just wanting to make sure that I am properly tracking table relationships in MySQL. Code INFORMATION_SCHEMA INFORMATION_SCHEMA.KEY_COLUMN_USAGE Code Sample Output Tools DBeaver Outline Steps to follow to track visually through DBeaver Launch DBeaver Connect to MySQL Instance Navigate to Database Tables <Specific Table> Double Click on the selected table On the right … Continue reading DBeaver – Table Relationships
