ANSI Standards – Database – Substring

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 – Buffer Pool – Clearing

Background During Database performance stressing, data is ingested into memory and written to disk. As one tries out different database table structures and Data Manipulation Languages ( DML ) queries, one often finds it useful to clean the slate in between.   Granularity Like any other operation one can target processing against the server, database, … Continue reading Database – Buffer Pool – Clearing

MySQL – Basic User Information

Background Once one is connected to MySQL, it is good to review basic user information. Functions Here are some available functions for reviewing one's own information. Outline username user() current_user Review Permissions show grants Username There are a few functions that basically do the same thing. They are all return the current user's name. The … Continue reading MySQL – Basic User Information

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

MySQL :- Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column”

Background As I tested out the code from our last blog, discovered a nice MySQL safety net. BTW, that blog is here.   Error Error Text The error reads:   Error Image Troubleshooting Review code and identify DML operations ( Update, Delete ) where the key column is not being filtered on. Here are possible … Continue reading MySQL :- Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column”

Transact SQL – STRING_AGG

Background In our post on MySQL - Information_schema.statistics we spoke glowingly of the GROUP_CONCAT Function. I really could not find a way around using it in MySQL. I ran into some difficulties using it and as with problems one just googles for workarounds. BTW, the MySQL post is here. SQL Server Introduction It seems that … Continue reading Transact SQL – STRING_AGG