In the last chapter when making the form look nicer you saw the row source that looked like:
SELECT [Vehicle].[ID], [Vehicle].[Number Plate], [Vehicle].[Manufacturer], [Vehicle].[Model] FROM [Vehicle] ORDER BY [Manufacturer], [Model], [Number Plate];These SELECT, FROM and ORDER BY keywords are SQL. SQL stands for Structured Query Language.
SQL is designed to query data stored in a relational database.
SQL implementations are similar but not 100% compatible. The syntax you use for MS Access will not be identical to what you would need for MySQL for example.
Constraints
Constraints specify rules for the data in a table. The following are common constraints.
| CONSTRAINT | MEANING |
| NOT NULL | Entry cannot be left empty |
| UNIQUE | Entry must be difference from any other in the column |
| PRIMARY KEY | Combination of NOT NULL and UNIQUE |
| FOREIGN KEY | Prevents data being added if this value does not appear in the other table |
| DEFAULT | Sets a value if none given |
| CHECK | Specify conditions that need to be matched to be valid |
Simple constraints can be added after the column definition in a CREATE or ALTER statement e.g.
CREATE TABLE Test (ID int PRIMARY KEY, ...Alternatively, or with more complex ones dealing with multiple columns, you add a CONSTRAINT line to the end of the query.
...
Age int,
CONSTRAINT CHK_Age (Age >= 18 AND Age <= 100)...Data types
Up until now you have dealt with either a high level design data type such as Number where Number is any numeric value, or in Access where Number is an small integer by default. In SQL you state the exact data type when creating or altering a table. The most common ones are:
| DATA TYPE | DESCRIPTION |
| SHORT or INT | Whole number between -32768 and 32767 |
| LONG | Whole number between -2147483648 and 2147483647 |
| SINGLE | Single precision floating point number |
| DOUBLE | Double precision floating point number |
| VARCHAR(n) | Variable width character string, where n is the max length. n cannot be more than 8000 max characters |
| TINYINT(1) or BIT | 0, 1 or NULL used for boolean |
| DATETIME | Store a date and time in ithe format YYYY-MM-DD hh:mm:ss |
Reserved words
Just like in programming, some words are reserved and must be escaped before they can be used. Each SQL platform is different so search the documentation if you encounter one.
For example, in MySQL you escape a table or column name by enclosing it in backticks, the ` character to the left of the 1 key. Whereas in Access you use the square brackets.
Creating a database
There are normally two parts to creating a database:
- Create the database
- Grant access to one or more users
In simplest terms, granting one user the same permissions to all tables, for MySQL you would use syntax like:
CREATE DATABASE databasename DEFAULT CHARSET charset;
GRANT permissions ON databasename.* TO 'username'@'hostname' IDENTIFIED BY 'password';Where databasename, charset, permissions, username, hostname and password are all replaced by relevant values e.g.
CREATE DATABASE db_hotel_guests DEFAULT CHARSET utf8;
GRANT ALL ON db_hotel_guests.* TO 'hotel_admin'@'localhost' IDENTIFIED BY 'j)ks89KIos0Kle,31d';Creating a table
The CREATE TABLE query gives the table a name and then lists the column names, their data type and any constraints e.g.
CREATE TABLE TestLink (
`ID` INT NOT NULL PRIMARY KEY,
`Name` VARCHAR(100) NOT NULL,
`DriverID` INT,
CONSTRAINT FK_DriverTestLink FOREIGN KEY (`DriverID`) REFERENCES Driver(`ID`)
);Inserting data
To add a new row to a table you use the INSERT INTO command following the format:
INSERT INTO TableName VALUES (Value1, Value2…);
For example:
INSERT INTO Driver VALUES (0000000001, 'John', 'Doe', '123 Main Street', 01234567890, 'jdoe@example.com');Some RDBMS allow you to list which columns you want to supply data for. But you would need to include any required field unless they are a field automatically filled by the database such as when using the DEFAULT constraint e.g.
INSERT INTO Driver (Forename, Surname, Address) VALUES ('John', 'Doe', '123 Main Street');Selecting data
The SELECT … FROM statement is used to select data from a database.
To select all the records from a table you use an asterisk (*) instead of a column name. For example, to get all the drivers in the Driver table you could use the following SQL:
SELECT * FROM Driver;To only view certain columns in the results you replace the * with the column names e.g.
SELECT Forename, Surname FROM Driver;SELECT … FROM … WHERE Queries
By adding a WHERE clause to the query you can limit the results to only those that match. For example:
SELECT * FROM Driver WHERE Surname = 'Smith';Will return all the columns in the Driver table but only for the rows that have a value of ‘Smith’ in the Surname column.
Operators
WHERE queries can be refined using operators. In the previous slide we used = to return matching values. But you can also use > and < for checking greater than and less than comparisons of numeric data e.g.
SELECT ID FROM Invoice WHERE Amount > 500;Queries can be further refined using the AND and OR operators:
- AND will only return a row where both sides of the AND operator are true
- OR will only return a row where either side of the OR operator is true
Ordering the results
The ORDER BY clause is used to order the results by the given column in the given order. The order being either ascending (ASC) or descending (DESC) e.g.
SELECT Forename, Surname FROM Driver ORDER BY Forename ASC;You can also order by multiple columns e.g.
SELECT Forename, Surname FROM Driver ORDER BY Forename ASC, Surname ASC;Wildcards
Wildcard characters are used with the SQL operator LIKE when you want to match only part of a string or text.
- % denotes a set of characters
- _ denotes a single character
For MS Access use:
- * instead of %
- ? instead of _
For a=example, to find all the rows of the Driver table where the Surname field begins with Mac you could use a query like:
SELECT * FROM Driver WHERE Surname LIKE 'Mac%';Updating entries
To edit the data that already exists in a database you use the UPDATE query. This follows the format:
UPDATE TableName SET ColumnName = Value;But you will almost always use a WHERE clause to limit the number of rows being changed.
UPDATE TableName SET ColumnName = NewValue WHERE ColumnToMatch = ValueToMatch;For example:
UPDATE Driver SET Address = 'Flat 1, 123 Main Street' WHERE ID = 0000000001;This will only change the record with that ID even if there is another driver called John Doe living at 123 Main Street.
Deleting entries
To remove data from a table you use the DELETE query in the format:
DELETE FROM TableName;You should always use a WHERE clause to limit the rows affected:
DELETE FROM TableName WHERE ColumnToMatch = ValueToMatch;Joining tables
Joins are used to combine the columns from multiple tables into the results of a single query. The most common is the inner join.
An INNER JOIN clause is used to select data from more than one table.
SELECT Driver.Forename, Driver.Surname, Invoice.CreatedDate, Invoice.Amount
FROM Driver
INNER JOIN Invoice on Driver.ID = Invoice.DriverID
WHERE Invoice.Paid = false;You can still use WHERE and ORDER BY clauses with inner joins. The join is simply to combine multiple tables into one temporary table for the purpose of the query.
Functions
Most RDBMS come with built in functions that can be used in a SELECT statement. Some common functions are:
| FUNCTION NAME | MEANING |
| AVG() | Returns the average value of a numeric column or expression |
| COUNT() | Returns the number of rows that match the criteria in the WHERE clause |
| MAX() | Returns the largest value of the selected column or expression |
| MIN() | Returns the smallest value of the selected column or expression |
| SUM() | Returns the total sum of a numeric column or expression |
| FORMAT() | Format a value to a given pattern, such as setting the number of decimal places |
Common rdbms functions
Use self learning to find how to use these functions with the database product that you are using. While most are similar, there might be differences between products. Check to be sure.
Because the function returns a single value for the whole column you cannot mix it with a regular select column that would return multiple rows.
SELECT ID, MAX(Amount) FROM Invoice;You can use it in a select statement that contains multiple functions as they each only return one vale.
SELECT COUNT(ID), MAX(Amount) FROM Invoice;Aliases
Aliases allow you to use a different name in your queries from the given table or column name. This is useful if you are dealing with really long names, ambiguous names, or computed column names formed when creating a column from a mathematical equation of other columns.
Long name example:
SELECT DriverID As ['dID'], VehicleID As ['vID'] FROM DriverVehicleLink As['DVl'];Ambiguous name example:
SELECT * FROM Table3458687687 As ['FeedbackJune21'];Computed name example:
SELECT StartingBalance - Costs As ['CurrentBalance'] FROM PettyCash;Grouping results
The GROUP BY clause groups rows of records together that are identical based on the criteria given. This is useful because you run functions on these groups rather than the entire column e.g.
SELECT COUNT(Make), Make FROM Vehicle GROUP BY Make;+-------------------------------+
| COUNT(Make) | Make |
+-------------------------------+
| 3| Ford |
| 5| Renault |
| 1| Tesla |
+-------------------------------+You can use ORDER BY and GROUP BY clauses in the same query but the GROUP BY must come before the ORDER BY.
How to see the SQL in Access
Click the Create Menu and choose the Query Wizard option. Then choose the “Simple Query Wizard” in the popup.
Use the arrows on the next page to add all the columns to the right-hand box then click Next.
Choose a name for the query and choose the Modify option before clicking finish.
On the far left of the Design Ribbon is an option for View. Click this the in the dropdown choose “SQL View”.
You can now create SQL statements. To run the query, click the Run option in the Design ribbon.
If the table that you are adding data to is open, you will need to close and reopen it to see the new data.
Syntax errors
As mentioned above, some words are reserved and must be escaped before they can be used. In Access you should surround the word in square brackets [] or rename that column or table.
If you used a column called Note in the previous slide you will have encountered a syntax error when you tried to run the query and Access will have highlighted the word causing the issue. Try escaping it in square brackets [Note].
