SQL, or Structured Query Language, was devised by IBM to make it easier to perform operations on a relational database. The implementation of a database system is probably easier under a graphical user environment (GUI,, such as Windows or Mac), where the logic of computer operations is more exposed and accessible. Before graphical environments were devised all interaction between user and computer was done via a command line. The user had to learn the commands, type them in and wait for a text response from the computer.
In this situation anything which would make it easier to communicate with a computer was to be welcomed and SQL was considered a great advance in its day. Just as important, SQL was based on the relational database model and implemented its standard features. In modern database applications like Access the user does not need to worry about SQL if he does not want to but it is there nevertheless, under the surface, and it can prove very useful as database applications become more advanced (for example, in issuing commands to databases stored on a web server and in products like SQL Server and Oracle which service large commercial databases).
A knowledge of SQL becomes useful and important when we step outside the confines of a 'desktop' system such as MS Access into a system such as an SQL database, and also databases accessed under the MS ODBC system. ODBC stands for 'Open Database Connectivity' and provides a series of protocols and methods for accessing one database system from another. A common example of this today is the use of databases in the web environment, where data is stored on servers and the ODBC protocols and methods are used to access this data from client web browsers. We shall look at this in more detail in the next section, first we need some knowledge of SQL.
As you will see, having a system like Access around makes it easy to create SQL commands because it does it for you. If you need a piece of SQL code you can write it out yourself from first principles or you can frame the query and operations you need in QBE and then copy the SQL from the code generated within Access. You could, of course, do the reverse and write some SQL and then paste it into the QBE window.
The examples in this section are based on a database with two tables, one for football clubs and the other for football players. You should create this database and recognise that the relationship between them will be on the primary key of the clubs table (name) and a foreign key in the players table (club).
The query techniques we have looked at so far are based on 'Query be Example' or QBE and they implemented in a graphical environment. We can easily display the SQL which underlies and processes the QBE. To begin looking at SQL create a simple query on the Clubs table:
Now select View/SQL View and you will see the following code in a dialogue box:
SELECT Clubs.[Club Name], Clubs.[Year Founded]
FROM Clubs
WHERE (((Clubs.[Year Founded])<1892));
This shows three basic SQL commands:
SELECT - pick the fields required from their respective tables e.g. Clubs.[Club Name]
FROM - gives the names of the tables from which the fields are drawn
WHERE - specifies the field criteria on which records will be selected
You could, if you so wished, switch to the SQL pane before entering any fields or criteria into the QBE grid and create the query directly in SQL - you may be able to do this later.
There are a number of additional SQL statements which are used in the creation and management of databases but these are not needed in our situation because Access carries out the operations for us. One group of additional SQL commands allows the creation and deletion of database items (CREATE and DROP) - this is known as data definition language or DDL.
To create a table:
CREATE TABLE name (
first_field_name data type
PRIMARY KEY,
last_field_name data type
NOT NULL);
To remove a table:
DROP TABLE name ;
If we want to add a field to a table we use:
ALTER TABLE name
ADD COLUMN name data type;
If we want to delete a field from a table we use:
ALTER TABLE name
DROP COLUMN name data type;
Data types in SQL include CHARACTER(), NUMERIC, INTEGER, DATE. If a data item must not be empty then we add NOT NULL to the field definition. If a field must be unique then the UNIQUE constraint is used. A field that is NOT NULL and UNIQUE is a candidate to be a PRIMARY KEY.
If a field is to be the primary key for a record then the constraint PRIMARY KEY is added to the field definition. If the primary key is composite (contains more than one field) the following is used:
CONSTRAINT primary_key_name PRIMARY KEY (field_name, field_name);
If a field in a record is a foreign key (a primary key in a related table) then the following is used:
CONSTRAINT FOREIGN_KEY_NAME FOREIGN KEY (field_name) REFERENCES TABLE_NAME (field_name);
To add data to the fields of a record use:
INSERT INTO table (field1, field2, field3)
VALUES ('data', 'data', 'data');
To change existing data:
UPDATE table
SET field1='data', field2='data', field3='data' ;
Another deals with access permissions (GRANT, REVOKE and DELETE), while a third deals with data manipulation (DELETE, INSERT, SELECT, UPDATE). The main SQL command we need for web databases is SELECT, along with its clauses.
Here is another simple query:
SELECT clubs.[Club Name], clubs.[Year Founded], clubs.Ground, clubs.Manager
FROM clubs
WHERE (((clubs.[Club Name])="Liverpool"));
This query picks four fields from the Clubs table (note how field names with two or more separate words are placed in [ ] markers). The WHERE condition is that the Club Name should be equal to "Liverpool". We could change the WHERE condition to:
WHERE (((clubs.[Club Name]) Like "Liver*"));
This uses the Like keyword and the '*' wild card character to catch all names beginning with 'Liver' - a similar search with 'Like "Man*"' would produce Manchester United, Manchester City and even Mansfield Town.
Here is a query taken from the Players table:
SELECT Name, [Club Name], Nationality
FROM players
WHERE ([Club Name]) Like "Man*"))
ORDER BY [Club Name], Nationality DESC;
This introduces the ORDER BY clause, which is used to sort the records selected on the specified field.
The next query introduces the SQL keyword for joining two tables by their primary and foreign keys.
SELECT clubs.[Club Name], players.Name
FROM clubs INNER JOIN players ON (clubs.[Club Name] = players.[Club Name]) AND (clubs.[Club Name] = players.[Club Name])
WHERE (((players.[Club Name]) Like "Man*"))
ORDER BY clubs.[Club Name];
The first line SELECTs the Club's name from the Clubs table and the Player's name from the Players table.
The second line tells the query to JOIN the two tables ON the Club Name field, which is common to both, where the values are equal. The JOIN operation links two tables to form a single recordset, based on the condition specified. The two tables here are clubs and players - 'clubs INNER JOIN players'. The third line repeats the condition in the JOIN clause.
If a club has no players then it will not appear in the output because the condition is not met, it has no players. The situation where a player has no club should not arise because referential integrity requires that a record on the many side of a relation should not exist if there is no parent on the '1' side. You cannot enter a player for a club not in the database and if you delete a club its players will, if the Cascade Delete option was ticked, be deleted as well.
The third line adds the same condition, that the query find only those records where the Club Name is the same in the two fields. This query list all clubs and all the players of each one.
There are two variations of the JOIN command, LEFT JOIN and RIGHT JOIN.
A LEFT JOIN produces a result set which contains all records from the left table with either data from the right table where there is a match between the fields, or NULL values where the fields do not match.
RIGHT JOIN - produces a result set which contains all records from the right table with either data from the left table where there is a match between the fields, or NULL values where the fields do not match.
The next query shows the goals scored by players in the matches played.
SELECT players.Name, goals.Date, players.[Club Name], matches.[Home Club Name], matches.[Away Club Name], goals.Type
FROM players INNER JOIN (matches INNER JOIN goals ON matches.[Match ID] = goals.[Match ID]) ON players.[Player ID] = goals.[Player ID]
ORDER BY players.Name;
The SELECT clause takes fields from three tables.
The FROM clause first joins the record sets Matches and Goals where the Match ID is the same; It then joins this product of two tables to a third table, Players, where the Player ID in the Players table is the same as that in the Goals table. Fields for display are those in the SELECT clause.
The following SQL is from the Players-Goals query, which displays just two fields, Player Name and a Count of goals scored so far. The first line SELECTs the fields from the Players table. The first field is simple while the second is listed as 'Count(goals.Date) AS CountOfDate', which performs a Count operation on the Date field and renames it CountOfDate.
SELECT players.Name, Count(goals.Date) AS CountOfDate
FROM players INNER JOIN goals ON players.[Player ID] = goals.[Player ID]
GROUP BY players.Name;
Remember that 'group by' appeared in the QBE method as well, where it allows you to add a statistical function such as COUNT or SUM. The expression here for counting the number of goals is:
Count(goals.Date) AS CountOfDate
Here the goals.Date field is to be counted and grouped by players, thus providing a count of the number of goals scored by each player.
The GROUP BY command in SQL consolidates a record set, removing all but one occurrence of the grouped data. The same principle applies in Reports where you can specify grouped output, for example by Club Name or Player Name.
Note that GROUP BY is different to ORDER BY, which does not consolidate records, it outputs them all but in a particular order. ORDER BY should always be the last clause in a query. IF GROUP BY and ORDER BY appear in the same query GROUP BY will operate first in arranging the data by its group fields and ORDER BY will then sort the records within each grouped category.