Structured Query Language (SQL) - selecting data
Using SELECT - FROM
Write an SQL query using the following commands:
SELECT Name
FROM tblDogs;
Run the query (in Access, by pressing the exclamation mark) and you should see a list of the names of the dogs. To go back to SQL view, simply right-click on the results of the query and then select SQL view.
The SELECT command is used to select columns to display in a table. The FROM command is used to pick the table(s) you want to get the columns from. You can select more than one column from a table and more than one table. Try running this:
SELECT Name, Type
FROM tblDogs;
You should see a list of names of dogs and their types. Try this:
SELECT Name, Type, [Date of Birth]
FROM tblDogs;
You should get a list of names of dogs, their type and their data of birth. Note the use of square brackets, used because the field name has spaces in it. You can display all of the fields in any particular table easily by using the wildcard symbol, *. Try this:
SELECT *
FROM tblDogs;
You will see all of the fields in all of the records in the dog’s table displayed. You can also display fields from more than one table in the same query. Try this:
SELECT tblDogs.Name, tblDogs.Type, tblOwners.Title, tblOwners.Surname
FROM tblOwners, tblDogs;
Notice the syntax in the SELECT statement. For example tblOwners.Surname means ‘go to the table called tblOwners and get the column called Surname’.
INNER JOIN
You can experiment in Access easily by designing queries in QBE (Query By Example). QBE is the graphical way of designing queries in Access. You can check that they work and then switch to SQL view to the SQL code (use the ‘design view’ icon under the FILE menu). You can also write queries in SQL and then see the equivalent in QBE. For example, if you design a query in QBE to select the names of dogs and their types and who owns them. When you convert this to SQL, you will get the following:
SELECT tblDogs.Name, tblDogs.Type, tblOwners.Title, tblOwners.Surname
FROM tblOwners INNER JOIN tblDogs ON tblOwners.[Owner ID] = tblDogs.[Owner ID];
This SQL code is different from the code we successfully used in the previous example. The relationship has been defined in the SQL code. It is important to get into the habit of defining the links between tables. Take note of this example and use it in any future SQL queries where you need data from more than one table. If you don’t, you may get strange results!
Using SELECT - FROM - WHERE
You can search your tables according to some criteria specified in the WHERE statement. There are lots of criteria you can use.
Note that dates in Access must be surround by hash symbols. NULL does not mean zero. It means ‘no data’. There is a big difference! There are other criteria! Try out the following SQL queries. Remember to predict the results before you run the query, then run the query and then compare the prediction to the results.
SELECT [Name], [Type], [Date of Birth]
FROM tblDogs
WHERE Type="Poodle" OR Type="Spaniel"
SELECT Name, Type, [Date of Birth]
FROM tblDogs
WHERE [Competition wins] IS NULL;
SELECT tblDogs.Name, tblDogs.Type, tblOwners.Title, tblOwners.Surname
FROM tblOwners INNER JOIN tblDogs ON tblOwners.[Owner ID] = tblDogs.[Owner ID]
WHERE Type="Poodle" OR Type="Spaniel";
Don’t forget that if you are getting data from more than one table then you should link them properly in the FROM statement.
Using SELECT - FROM - WHERE - ORDER BY
You can sort the results easily, either in ascending or descending order by any field. For example:
SELECT tblDogs.Name, tblDogs.Type, tblOwners.Title, tblOwners.Surname
FROM tblOwners INNER JOIN tblDogs ON tblOwners.[Owner ID] = tblDogs.[Owner ID]
WHERE Type="Poodle" OR Type="Spaniel"
ORDER BY tblOwners.Surname;
This will order the results in ascending order by the owner’s surname. The reverse order can be obtained like this:
ORDER BY tblOwners.Surname DESC;
Using SELECT - SUM - AS - FROM - GROUP BY
This command can be used to find the totals for each unique entry in columns in tables. For example, suppose you wanted to know how many wins each unique owner in the dog club had in total. You would do it like this:
SELECT tblDogs.[Owner ID], Sum(tblDogs.[Competition wins]) AS [Total number of wins]
FROM tblDogs
GROUP BY tblDogs.[Owner ID];
This SQL command adds up the competition wins for each owner in the tblDogs table. It then displays the total number of wins for each owner under the heading ‘Total number of wins’.