simple SQL
Author: jason
Date: 2007-11-19 12:03:12
Category: Technical

simple SQL is a small Visual Basic application written with Visual Studio 2008 than enables users to query Access databases (mdb), Excel spreadsheets (xls) and MS SQL servers with SQL commands.

Download
sSQL v2.0.0
sSQL v1.0.6
sSQL v1.0.2

Requirements
.Net Framework 2.0 20MB
Windows Installer 3.0 2.5MB

Optional
Interop ADODB DLL (included in zip)
Interop ADOX DLL (included in zip)

Note: the above DLLs are used to dynamically read the table and sheet names from Access and Excel

Open sSQL.exe after extracting all the files. A message/status area is located under the query box and middle buttons. This is where messages or errors will be displayed. Sometimes the messages are blissfully cryptic. Other times they are helpful. There are more of the former than the later though. Select the sample database or spreadsheet by clicking Browse.




Only Access database (MDB) and Excel spreadsheets (XLS) can be used at this time. A SQL server option will be added in the future.




Clicking the Query button when the SQL expression window is empty will cause sSQL to attempt to retrieve table or sheet names from the selected file. When querying Excel files the brackets and dollar sign must be used to reference the sheet name.




A Favorites feature has been added. Common queries can be stored and reused without having to remember the exact command strings. To enable this feature click the Favorites button.




Select the favs.mdb database.




Favorite SQL queries can be stored, edited, and deleted. Entering and saving SQL commands here will populate the dropdown on the main sSQL window.




SQL command strings can be entered in the expression window, above the middle buttons. Note the brackets and dollar sign. SQL commands will also be loaded into the query window when selected in the favorites dropdown, next to the browse button.

SQL Code

select * from [employees$]





The number of rows returned is displayed in the message/status area. The window can be resized to fit the dimensions of the result grid. Alternating rows will shade gray for horizontal readability. The columns and rows can also be resized. Each column can be sorted by clicking on its header name. Multiple rows, columns, and cells can be selected.

Copy and paste functions are available by using the Control key plus the C or V. Ctrl+C is for copy, Ctrl+V is for paste.

Clicking the Clear Results button with clear the results grid and the message/status area, but will leave the last query in the expression window.




Example Excel Queries

Here are the Excel sheets we’ll be using for this example. Browse and select the sample Excel spreadsheet in sSQL.exe




Enter the below string and click Query.

SQL Code

select * from [employees$] where email like “%so.com%”


Here we are selecting everything from the employees sheet where the email fields have so.com characters, somewhere in the field. The percent sign is the wildcard character for SQL queries. The asterisk sign means all. Note the brackets and dollar sign around the sheet name.




Now let’s try a more complex query. Let’s grab the phone number from the employees sheet along with the name, mailing address, and mailing type from the address sheet. We will match the first and last name fields in the employees sheet to the name field in the address sheet.

SQL Code

select name, phone, mailing, type from [employees$], [addresses$] where [employees$].first_name + " " + [employees$].last_name like [addresses$].name





Example Access Queries

Here are the Access tables we’ll be using for this example. Browse and select the sample Access database in sSQL.exe.




Let’s display information from all three of the tables

Copy and paste the below command, then click Query.

SQL Code

select employees.first_name, employees.last_name, directory.title, directory.phone, address.mailing, address.type from employees, directory, address where employees.employee_id = directory.employee_id and employees.employee_id = address.employee_id


We are requesting the employees first name, last name, the title and phone from the directory, and the mailing address and type from the address table where all the employee ids match.




We can also look for information that is absent from the tables. Let’s find entries without an email address in the system. We need to know the person’s name that has the empty email address, so we’ll get his/her name from the employees table.

SQL Code

select email, first_name, last_name from directory, employees where directory.employee_id = employees.employee_id and directory.email is null


Is Null can be used to find blank records, also using equal or like “” works too. i.e. directory.email = “”




The last query we’ll do is count the number of records where the field name matches our criteria. Let’s count the number of addresses that are type “Home” in the address table.

SQL Code

select Count("type") as HomeMailingAddress from address where type = "Home"






jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009