The better you know SQL basics, the better you can perform your job by being able to access relational data using the SQL language, or by tweaking the SQL produced by Query Builder.

This series will utilize Toad Data Point and its Local Storage. I will make reference to specifics for the Oracle and SQL Server, RDBMS, but the focus will be the SQL language.

This blog will focus on the following areas of the SQL query language using Traditional Toad Data Point:

• SQL Basics
• SQL Where Clause Topics

SQL Basics 

The basic SQL syntax are the SELECT and FROM clauses. SELECT identifies the columns to be included, and the FROM clause is the data table or another SQL statement that returns data.

The 'SELECT *' selects all the columns from the table identified in the FROM clause. The FROM lists the single table. Since there is no WHERE clause, this SQL will return all of the table data from the named table. The columns will be listed in the order that they appear in the database table structure. The data will be returned (roughly) in the order in which it was added to the table.

Please note that unless there is an ORDER BY clause (covered in the next part of this series), there is no guarantee that the rows will be returned in any particular order at all.

SELECT * or column name [alias] [, column name [alias] [, …][function][arithmetic expression]

FROM table name [, table_name [, …] or [sub-query]

[WHERE [conditional statement] or [sub-query]]

This is the rough railroad syntax for a SQL SELECT statement. This learning series will cover all of these topics.

This part will cover more of the SELECT and WHERE clauses for a single table access.

SQL Where Clause Topics

I'd like to illustrate another cool feature of TDP: Toad Insites. Insites allows for automatic popups of useful table data to appear.

Type in SELECT and the FROM clause. IF you include the FROM clause, the insite pick list will include just columns from that table, as shown below. You can see in this illustration that useful functions also appear in the pick list.

This is how I use insites. I hold down the <crtl> key and click on multiple columns. I hit the 'Enter' key to get these out, and see below, Toad Data Point includes the columns in the order that you clicked on them, and includes the commas between each.

This SQL will work. You didn't have to type in long column names or anything else. Perhaps just as easy as the table drag and drop out of the Navigation Manager.

Insites also works in the WHERE clause. It works anywhere in the SQL Editor window and is signaled by the space bar.

Notice my SQL always ends with a semi-colon. This is good practice, especially if you are going to put more than one SQL statement into a single SQL Editor session. A semi-colon denotes the end of a SQL statement.

Where Clauses allow for selectivity of the data being returned.

Where Clause Conditions

Notice my SQL always ends with a semi-colon. This is good practice, especially if you are going to put more than one SQL statement into a single SQL Editor session. A semi-colon denotes the end of a SQL statement.

Where Clauses allow for selectivity of the data being returned.

• Equality =
• Not equal   !=   <>
• Greater than >
• Less than <
• Greater than/equal to >=
• Less than/equal to <=
• IN
• Between Like %

Listed above are the main Where Clause conditions.

Let's try some of them out.

This SQL selects more than 1 DEPTNO. The IN Clause is far more efficient than using the 'OR' clause in this instance.

What is wrong here? Notice I asked where DEPTNO = 10 or 20 but I have some DEPTNO 30s in this list.

Hint: wrong syntax! I’m not sure why this works, but it does; this is the correct syntax. I like the 'IN' clause when selecting different data from the same column.

Ok, here we want just the clerks and analysts in dept 10. However, we have a lot of other department data.

Use parenthesis to define exactly what you want, to both SQL and to others, that might be using this code in the future.

Another cool feature of the SQL Editor: parenthesis matching! This works with braces and brackets too.

Notice in the above illustration that I have the cursor on the line, and by the closing parenthesis, both parenthesis turned gray!

A slightly different example to highlight the parenthesis matching.

In this article, we laid out some SQL basics, then launched into a variety of WHERE clause examples that allows for selectivity of the data.

These SQL basics offer a solid foundation for data analysts to understand the SQL language and utilize data gleaned for organizational decisions and learning.

Related Links

Toad Data Point Tutorials: Data setup, code snippets and SQL Recall

Toad Data Point Tutorials: SQL Where Clause Null Values

SQL Pattern Matching in Toad Data Point

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

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!