Learning SQL with Toad Data Point

Learning SQL can be a challenge, but one that’s made much easier by using tools that make finding, preparing, extracting and reporting on data simple. By accessing relational data directly using the SQL language, or simply by tweaking the code snippets and SQL produced by the Toad Data Point Query Builder, you can better perform your role. 

• What is SQL? 
• Toad Data Point Setup 
• Code Snippets 
• SQL Recall 

What is SQL? 

SQL stands for Structured Query Language and has been the standard query language for all relational databases since the mid-1980s.

The better you know SQL, the more refined your SQL can become to select the desired data from data stores using tools such as Toad Data Point.  

The SQL you will learn in this series can be used to tweak SQL generated by Toad Data Point's Query Builder and can be used in any application where SQL is exposed. 

Toad Data Point Setup 

Toad Data Point has an internal database called 'Local Storage,' a MySQL database, that will be used throughout this course. 

The Call to Action below has the links to the EMP and DEPT spreadsheet data that will be used throughout this learning series. 

First, start traditional Toad Data Point. 

Second, connect to the Local Storage connection then double click on the Local Storage Connection. 

The Local Storage connection highlights when it is connected. This is your 'focus' for Toad Data Point as the SQL will be applied using this connection.

Third, create a database called EMP_DATA using the button in the lower left tab on the Navigation Manager as pictured.

You can now view these databases as folders or containers for your table data.

Fourth, start the Import Wizard by clicking the Export/Import wizard button on the menu ribbon.

Select 'File' on the left side(the right side should show just the data source Table and already be connected to Local Storage). Click 'Next.'

Make sure you open the EMP spreadsheet first and change some of the HIREDATE dates to the current month and quarter.

Then find the EMP spreadsheet data. After that, you’ll be able to see some data. Click 'Next.' No changes to the columns are required, so click 'Next' on the following panel as well.

On this panel, select 'A single new table.' The Table name is EMP and the Schema should be already filled in to the previously created EMP_DATA database name. Click on 'Next' two more times to walk through the panels, and be sure to Import the Data at this point.

If all goes well, you will see the display of the image above. You can now review the data from here. Make sure you have 14 rows loaded for the EMP table and four rows loaded for the DEPT table.

Now, do this same import function for the DEPT spreadsheet, creating a DEPT table in the EMP_DATA database. Four rows should be imported.

Code Snippets and SQL Recall

Toad for Oracle and Toad Data Point both have Code Snippets and SQL Recall that can be useful in your day-to-day use of SQL.

Your Toad Data Point Navigation Manager should look something like this. In the Object Explorer, click on the Tables tab. You should see both the EMP and DEPT tables that you just created. Click on the table and you will see the columns and other useful data.

Double click on the table and you’ll get a Table Viewer Display. Note that you can see a variety of information, including data. If you have permissions to change the data, you can do so here.

Now, click 'Build' on the ribbon menu or 'SQL Editor' from the start tab and you will get an SQL area to build and manipulate SQL.

Drag and drop the EMP table from the Navigation Manager to the SQL area then select 'Select Statement' from the pop-up menu.

The drop-down menu from the 'Execute' area (upper left corner of the SQL Editor panel) has several options. If you need to put more than one SQL statement per SQL Editor session, you can stack your SQL into one SQL. These buttons also appear at the bottom of the SQL Editor panel.

Execute the SQL. You can now make some simple changes to the SQL, such as deleting columns so that you’re left with only the columns you need. Note that the column data will come back in the order listed in the SQL statement.

Code Snippets

Code Snippets are a drag and drop library of SQL code snippets that will play a larger role as you get into math and null value processing, date functionalities and most data formatting you would like to do with SQL. To access Code Snippets, use the menu item 'View > Code Snippets' and the Code Snippet panel will appear. You can undock this panel (move it around the screen) and the little push pin icon allows you to autohide the Code Snippets (or any panel with this push pin icon).

There are code snippets for nearly every database, including Local Storage.

SQL Recall

Another feature is SQL Recall. All SQL that is executed is saved the default is 100 SQL statements, but it can be adjusted in Toad Data Point Options). To access priorly-executed SQL, use the menu bar 'View à SQL Recall' to bring up the panel of prior executed SQL pictured below.

Double click on the SQL and it will appear in your SQL Editor panel.

These topics are the groundwork of SQL basics. From experienced DBAs to those just starting on their SQL journey, everyone is encouraged to follow this series to learn SQL with the help of Toad Data Point.

Related Links

How to use Quest® Toad® Data Point to cleanse, transform, save and retrieve data

Access and query data with a repeatable process using Quest® Toad® Data Point

How to use Quest® Toad® Data Point to Securely Save and Share Data Sets