Let's Play SQL

This page will walk you through some interesting SQL applications. You need to have Nwind.mdb ready. This is a Microsoft ACCESS sample database file which comes with ACCESS. You also need to have DataDynamica Data Explorer running, so that you can copy the syntax to the SQL window in the Data Explorer application. To test the SQLs, highlight the text of each example and drag it to the SQL window. Or you can press Ctrl-C to copy the highlighted text to the clipboard and then Ctrl-P to paste.

The simplest SQL syntax

SELECT  *  FROM  Employees       
^-- copy this syntax to SQL window and click 'Run' button

The basic SQL syntax is 'SELECT  what field(s)  FROM what table WHERE what condition'. 'SELECT * ' means select all fields from the table. If the table name contains space in between, you have to put square brackets to close the name. For example, [Order Details].

WHERE clause

SELECT  *  FROM  Employees WHERE Country='USA'
^-- copy this syntax to SQL window and click 'Run' button

Add criteria or filter to the syntax. Now you select all the employees whose country is USA. Put a pair of single quote to enclose the string. If the data type is number, no need to quote. For example, WHERE ID = 68.

Create calculated column - Calculate Age from Birthday

SELECT [FirstName] & " " & [LastName] as Name, Format(Now()- [BirthDate] ,"yy") as Age
FROM [Employees]
^-- copy this syntax to SQL window and click 'Run' button

Combine two fields, FirstName and LastName, into one new field named 'Name'. Now() is an ACCESS function. Format the time difference between now and birthday of each employee into year - 'yy'.

Join two tables to extend more information

SELECT DISTINCTROW Categories.CategoryName, Categories.Description, Categories.Picture, Products.ProductID, Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
^-- copy this syntax to SQL window and click 'Run' button

Join two tables need a connection clause: TableA INNER JOIN TableB ON TableA.FieldA=TableB.FieldB. So, instead of displaying CategoryID, you get more informative CategoryName associated with each Product.

************

I will add more examples to this site. You are welcome to submit your examples and post here by sending email to (SQL@DataDynamica.com).Please use the same database NWind.mdb. I will give you credit and mention your name. Thank you in advance.