What Exactly IS a Relational Database Management System RDBMS

IBM doesn’t just make hardware. They also have laboratories like mad scientists. It was in their San Jose Research Laboratory that Edgar Frank Codd developed the DBMS (database management system) relational model. RDBMSs still encase the backbone of most systems worldwide, even with the introduction of object-oriented database management systems or OODBMS …seriously not every computer term needs an acronym.

MySQL by Oracle Corporation is still deployed on 50% of database backed sites according to Gartner. Because it’s open source (free!) it is typically the introductory database most developers come across. The other big giants are Microsoft’s SQL Server and Oracle Database. Structured Query Language (SQL) is a specific language used to insert, query, update, and delete data inside the database.

Here’s the basic structure of an SQL Statement:
SQL example statement

Working with MongoDB

Advantages of MongoDB over RDBMS (relational database management system).

Schema less − MongoDB is a document database in which one collection holds different documents. Number of fields, content and size of the document can differ from one document to another.
Structure of a single object is clear.
No complex joins.
Deep query-ability. MongoDB supports dynamic queries on documents using a document-based query language that’s nearly as powerful as SQL.
Tuning.
Ease of scale-out − MongoDB is easy to scale.
Conversion/mapping of application objects to database objects not needed.
Uses internal memory for storing the (windowed) working set, enabling faster access of data.

Why Use MongoDB?

Document Oriented Storage − Data is stored in the form of JSON style documents.
Index on any attribute
Replication and high availability
Auto-sharding
Rich queries
Fast in-place updates
Professional support by MongoDB

Where to Use MongoDB?

Big Data
Content Management and Delivery
Mobile and Social Infrastructure
User Data Management
Data Hub

Here is an example of a MongoDB query. As you can see, you call out an item and then fields can be whatever you want them to. Unlike MySQL there is complete freedom over item values.


db.inventory.insertMany([
   { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
   { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
   { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
   { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
   { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
]);

Write to MySQL Database Using Java

Probably one of the most common tasks you’ll ever utilize is writing to a database. Java INSERT is a very basic code snippet. This code inserts a first and last name into the table.

create table users (
  id int unsigned auto_increment not null,
  first_name varchar(32) not null,
  last_name varchar(32) not null,
  date_created timestamp default now(),
  is_admin boolean,
  num_points int,
  primary key (id)
);

Just for your reference here is another way to write to a MySQL database using JavaScript. Note: I’ve got the database host set to “localhost” since we are executing the script on the same server. The username is set to “root”.

This script will be inserting a first name, last name, and date created etc. I created a Prepared Statement to capture all the values before executing the write.

import java.sql.*;
import java.util.Calendar;

public class JavaMysqlPreparedStatementInsertExample
{

  public static void main(String[] args)
  {
    try
    {
      // create a mysql database connection
      String myDriver = "org.gjt.mm.mysql.Driver";
      String myUrl = "jdbc:mysql://localhost/test";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "root", "");
    
      // create a sql date object so we can use it in our INSERT statement
      Calendar calendar = Calendar.getInstance();
      java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

      // the mysql insert statement
      String query = " insert into users (first_name, last_name, date_created, is_admin, num_points)"
        + " values (?, ?, ?, ?, ?)";

      // create the mysql insert preparedstatement
      PreparedStatement preparedStmt = conn.prepareStatement(query);
      preparedStmt.setString (1, "Barney");
      preparedStmt.setString (2, "Rubble");
      preparedStmt.setDate   (3, startDate);
      preparedStmt.setBoolean(4, false);
      preparedStmt.setInt    (5, 5000);

      // execute the preparedstatement
      preparedStmt.execute();
      
      conn.close();
    }
    catch (Exception e)
    {
      System.err.println("Got an exception!");
      System.err.println(e.getMessage());
    }
  }
}

Now that you’ve written to the database you can query it using the following script:

select * from users;

The output should resemble this:


+----+------------+-----------+---------------------+----------+------------+
| id | first_name | last_name | date_created        | is_admin | num_points |
+----+------------+-----------+---------------------+----------+------------+
|  1 | Barney     | Rubble    | 2010-06-23 14:02:00 |        0 |       5000 | 
+----+------------+-----------+---------------------+----------+------------+
1 row in set (0.00 sec)

How Data Analysts Use Toad for Writing SQL

Where to begin:

  • Every statement you execute gets recorded.
  • By default we remember the last 100 executed.
  • SHIFT + F8 opens the SQL Statement Recall panel (also available under the View menu).
  • ALT + PgUp / Dn will cycle through the most/least recently executed statements
2. Code Snippets
 
In Toad, when making a call to a database provided function I suggest you start using CTRL+SPACEBAR which activates Toad’s Code Snippets.

Toad will allow you to progressively drill down into a category of database function, complete with documentation on how to use them!

Using Favorites:
We can take a SQL query we’ve stored to the SQL Recall and convert it to a “Favorite” code snippet. Or, you can choose to hand code your collection of frequently used snippets of Code. Just use CTRL+N to pull up your list of favorite snippets of code.

3. Code Insight (Dot Lookups)
 

When you want to write a SQL query or make a PL/SQL call, you probably spend a bit of time manually looking up the column names or command arguments required in the Database Browser. Stop doing this right now! From now on, let Toad figure this out for you automatically in the editor.

The basics:

  • schema. – this will pop up a list of all objects in the current schema you may want to type
  • schema.table. – this will pop up a list of columns for a given table. You don’t need to prefix the schema name if the table is in your current schema.
  • schema[A-Z]+CTRL. – If you start typing your object name and hit CTRL. (CTRL key with a period), Toad will pop up a list of objects matching that pattern.

  • Toad waits 0.5 seconds after seeing the ‘.’ or ‘(‘ character before popping up the list of items available for that object. This is configurable down to the millisecond for optimal usage. You can also configure Toad to list the column names in the order they appear in the table, or alphabetize them

  • You can use aliases for your view/table names and the code insight feature will automatically include the alias when bringing back a list of columns. Toad will show you the column comments so you know exactly what you’re looking at.

4. F4 DESC

DESC, or Describe, is a Oracle command that will display the table or view structure, e.g. a list of columns and their datatypes with size and precision. Toad for Data Analysis has its own DESC command available for all of the supported database vendors (Oracle, SQL Server, MySQL, DB2, and for v2.0 – Sybase!) You can invoke it immediately for ANY object in the database by typing that object in the editor, putting you cursor on it, and hitting ‘F4’. You can type away in the editor with the DESC dialogs available to you. Ever find yourself working on a query only to find out you need to consult the existing columns or indexes? Or maybe you just need to see the existing data or even update a row to get your report query to work properly? Don’t go to the Database Browser anymore, just use ‘F4’ for instant gratification.



Get instant access to any database object with the power of the
Database Browser immediately available.

5. Make/Strip Code

If a developer gives you a jumbled mess of Java of Perl scripts that contains the SQL  you need just let Toad for Data Analysis do this work for you. Take any embedded SQL statement and strip away the non-SQL syntax for instant execution in the Editor. This is one of the most over-looked features in Toad. No more REGEX search and replace to get your well-crafted SQL statement ready for your Eclipse or Visual Studio application.

To activate the feature, just use the ‘Editor’ toolbar menu:

Your SQL is now ready to be put into your custom report or spreadsheet.

Configure Toad to use the programming language of your choice

6. Reverse Engineer your SQL to a Query Builder Model
 
This feature has been available in Toad for Oracle for awhile now, and has been frequently requested as a feature in Toad for Data Analysis. Toad for Data Analysis users will be able to take advantage of this feature in v2.0 of the product which is due in a few weeks.
Conclusion
 
Writing SQL by hand can be difficult, whether you’re using a text editor or a visual query tool application like Toad for Data Analysis. I’m planning a blog topic on how to develop your SQL more efficiently using the visual Query Builder. There are other SQL query tools out there but I recommend Toad for any enterprise Data Analyst.