Showing entries 1 to 7
Displaying posts with tag: SQL Developer (reset)
MySQL Column Aliases using the AS keyword

Be it running reports or displaying data in some other visualization, SQL SELECT column expressions should be meaningful and understandable. To provide those valuable query results, SQL Developers, use a multitude of available functions, adjacent columns, or other means not readily apparent to end-users. All that being said, the column names often suffer the most as far as readability is concerned, taking on long function call names or other combined expressions. But, as luck would be on our side, there is an easy fix and that is aliasing columns using the AS keyword. Although AS is optional – in this particular context – I err on the side of readability and use it when aliasing SELECT column expressions.

Image by …

[Read more]
MySQL Count Weekday occurrences

Each SQL dialect is different in some way, shape, form, or fashion from the next flavor. Some dialects have this function, while others have that function. In this post, I cover porting over Oracle SQL to MySQL in order to count the number of occurrences of a specific weekday found in the current given month (at the time of writing) purely as a learning exercise focused on MySQL DATE functions and the WITH clause…

Image by tigerlily713 from Pixabay

[Read more]
MySQL’s AUTO_INCREMENT attribute

Most developers use some form of auto-incrementing integer counter for a given database table, ensuring uniqueness among the rows. Several of the popular SQL dialects implement this facility. For instance, MySQL’s AUTO_INCREMENT attribute is used to provide a unique identity for a table row. What exactly is the behavior of AUTO_INCREMENT? Can you explicitly use a value of your choosing for it if you need to? How does it count? Continue reading and know the answers to these questions and more…

Image by Gerd Altmann from …

[Read more]
Top n Window Function queries in MySQL

Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…

Image by …

[Read more]
3 MySQL Commands Developers should know.

If you are a developer working in a MySQL environment, this blog post is for you. I share 3 MySQL commands or statements that you should know. That is a bold statement, I know. Turns out, once you do know (of) these commands, you will use them all the time. They minimize guesswork which leads to better productivity in other facets of your programming and querying workflow. I use them myself almost daily and am sure you will too once you see how simple they are to use. So why should you know them? Continue reading and find out…

Photo by hannah joshua on Unsplash

OS, Software, and DB used:

  • OpenSuse Leap 15.1
[Read more]
Making Existing SQLPLUS Scripts 12c and Container DB (PDB) Compatible

Oracle 12c introduces new catalog features including CDB_ dictionary views (which include a CON_ID column) superseding the DBA_ views that most DBA sqlplus scripts are based upon.

However, existing DBA sqlplus scripts can easily be modified using just a few simple sqlplus techniques to be compatible with 11g, as well as all types of 12c databases including legacy and container databases.

The following simple SQL and sqlplus techniques can be used to make a “universal script” that is compatible with all versions.

Illustrating the Issue

Let’s say for sake of example that we have a simple 10g/11g monitoring script that’s checking the amount of freespace in each tablespace by querying the DBA_TABLESPACE_USAGE_METRICS view.

On our 10g or 11g database the following query gives the necessary information:

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

SQL> select …
[Read more]
SQL Developer – Fedora

This is the continuation of my efforts to stage an awesome Fedora developer’s instance. It shows you how to install Java 1.8 software development kit, which is nice to have. Though you can’t use Java 1.8 officially with Oracle SQL Developer 4.0.3 it is required for Oracle SQL Developer 4.1. Fortunately, the Oracle Product Manager, Jeff Smith has advised us that you can use Java 1.8 JDK with Oracle SQL Developer 4.0.3, and he’s written a comment to the blog post that it runs better with the Java 1.8 SDK.

After you install Oracle SQL Developer 4.0.3 or Oracle SQL Developer 4.1, you can watch Jeff Smith’s YouTube Video on SQL Developer 3.1 to learn how to use the basics of SQL Developer. I couldn’t find an updated version of the video for SQL Developer 4 but I didn’t try too hard.

You …

[Read more]
Showing entries 1 to 7