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.

Blog-TW-SQLBasics-Im-JY-73146

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

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-3-1-2

 

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.

1 simple sql

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.

2 Toad insites popup

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.

3 Toad insites popup multi column select

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.

4 Toad insites results

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.

5 SQL basics - Equality Where Clause

6 Equality where clause using IN

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

7 Equality where clause issue

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

8 Equality where clause using OR

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.

AND OR Data Issues

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

AND OR Where CLause

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!

Parenthesis Matching and the SQL Editor

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.

Try Toad Data Point for free

Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.

Get started with our free 30-day trial.

Already in a trial? If Toad Data Point is helping you connect, query and prepare data for faster business insights, buy it now or contact a sales representative.

 

 

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!

About the Author

Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Start the discussion at forums.toadworld.com