Structured Query Language
SELECT Queries In Access
So far you have queried databases using Query by Example. When queries become more complex or you wish to create queries programmatically in an application linked to a database, SQL is a more powerful tool.
SQL is a data access language and not a programming language.
To query a Microsoft Access Database using SQL
- Select Queries from the database window.
- Open a new Query in Design View.
- Close the Add Table box without adding a table.
- Click on Query, SQL Specific, Data Definition.
For the purposes of the examination you are required to know how to use SELECT queries only. You can use SQL for all of the same types of query that you use through access. You may wish to learn more about SQL when you are using Access with Visual Basic.
For the examples below use the Access 2000 database, trek2000.mdb (approx 184kb). You may find it useful to peek at the tables before you begin. Copy the examples and save all of the queries that you write.
SELECT [Episode Number], [Episode Title], Series, Season
WHERE Series ="TOS";
This produces a table of episodes from The Original Series of Star Trek. There are 79 records for this series in the database. Square brackets are used where field names have spaces in their titles.
Use the format shown above to write queries that,
- produce a table of episodes from the DS9 series of Star Trek showing only the episode number and title of each record.
- produce a table of episodes from the TNG series of Star Trek showing only the episode number and title of each record.
- produce a table of episode titles and story authors where Gene Rodenberry is the sole author of the episode. (Gene is the daddy of Star Trek)
SELECT DISTINCT Series
You should now have a table showing 3 records from the tblSeries table. They should be TOS, TNG and DS9. Duplicate records have been removed with the DISTINCT statement.
Write SQL queries that,
- produce a table of episode titles and story authors showing only distinct records.
- produce a table of episode titles and story authors from the TNG series showing only distinct records.
WHERE [Teleplay Author] IS NULL;
You should now have a table showing 123 episodes for which there is no value in the Teleplay Author field.
WHERE [Episode Number] > 20 and Series ="TNG";
You should have 106 records.
Write SQL queries that,
- produce a table of episodes showing the first 3 episodes in each series.
- produce a table of series shown after 1987 (think about the date format and for Access include a # either side of the date).
Other Conditions that can be specified in the WHERE line are,
|LIKE||Similar to.||[Episode Title] LIKE "Q*"|
|BETWEEN...AND||Within a range of values including the two values which define the limits.||[Episode Number] BETWEEN 20 and 50|
|OR||Either condition must be true for the record to be displayed.||[Story Author] = "Gene Rodenberry" OR [Story Author] ="D C Fontana"|
Operators such as >, <>, >=, <= can be used in SQL and work as you would expect.
Modify the example query from Number 4 for each of the above examples.
SELECT tblEpisode.[Story Author], *
WHERE Series= "TNG"
ORDER BY [Episode Title];
This will produce a table of episodes from the TNG series, listing the episodes in alphabetical order by Title.
Write SQL queries that,
- produce a table of episodes from the TOS series, listing the episodes in alphabetical order by Episode Title.
- produce a table of episodes written by Gene Rodenberry, listing the episodes in numerical order by Episode Number.
SELECT Sum([Episode Number]) AS [Episode Nos Added],[Story Author]
Group by [Story Author];
This query does not make a lot of sense since the Episode Numbers are used to identify episodes and not for any arithmetic purpose. You should have produced a list of Story Authors and another field showing the value produced when the episode numbers of the episodes that they wrote are added together. More use in another database. Notice that you have 'created' a field here.
Write queries that,
- produce a table of episodes which include the word Time or Data in the Episode Title field. (The character Data is very popular as are episodes about Time).
- tell you which series of the three included in this database uses the word 'Space' most in episode titles.
Explain the nature and purpose of a database query language. (3)
The Trek database began with the following data structure (repeating fields not identified).
Episode(Episode Number, Episode Title, Series, Season, Story Author, Teleplay Author, Date First Aired)
The database has not been properly normalised and, although it is usable for the above tasks, would need to be improved if it were used in another context.
- Identify the problem field(s) that exist in this database, explaining why they would need to be modified if the database needed to be used in a professional context.
- Convert the data structure to 3rd Normal Form showing all of the stages involved.
- Draw an entity relationship model (ERM) for the data structure that you have specified.
Use the WWW to locate a more comprehensive introduction to using SQL queries in Access 2000. Concentrate on finding a resource that you will be able to refer to when completing your project. (SELECT queries are most useful).