Searching
(Soffritto from Westphalia’s Alps - Order it!)
SELECT fieldName1, fieldName2
FROM tableName1, tableName2
WHERE tableName1.PK = tableName2.FK
AND fieldName = data;
ORDER BY fieldName ASC OR DESC
Adding (insects intermittently vivacious)
INSERT INTO tableName (fieldName1, fieldName2)
VALUES (value1, value2);
Editing (United States of Where?)
UPDATE tableName
SET fieldName to updated value
WHERE criteria to be met;
Deleting (don’t forget whisky)
DELETE FROM tableName
WHERE criteria to be met;
Database End-User Requirements
The tasks that the user needs to carry out
Database Functional Requirements
The processes and activities that the database has to perform and the information that the system needs to store in order to perform these functions.
Should include any tables that will be used, a list of fields in each table, queries to be carried out, functions / calculations
Compound Key
Two or more attributes are used to uniquely identify each record in a table.
Each attribute from the compound key is a primary key in its own right.
Used when no single attribute in a table can be used as a primary key.
SQL Wildcards
Used with LIKE operator:
% - matches one or more character(s)
_ - Matches exactly one character
eg:
SELECT surname
FROM patient
WHERE forename LIKE ‘s_i%’;
Aggregate Functions
An aggregate function performs a calculation on a set of values and returns a single value.
SQL MAX / MIN
The MAX() function returns the largest value of the selected column.
The MIN() function returns the smallest value of the selected column.
SQL Sum
SUM() returns the sum of a numeric column.
SQL Average
The AVG() function returns the average value of a numeric column.
SQL Count
The COUNT() function returns the number of rows that match a specified criteria.
Computed Values
A computed value can be calculated by using arithmetic expressions in a SELECT statement.
Aliases
SQL aliases are used to give any column in a table a more readable name. Can be assigned to a column by using the AS keyword in the SELECT statement.
eg:
SELECT surname, COUNT(appointmentID) AS [Number of Appointments]
FROM doctor, appointment
GROUP BY
The GROUP BY statement arranges rows with the same values into groups, commonly used with aggregate functions.
Testing
To test a query, come up with a table of expected results then run the queries and check that the actual results match the expected results.
Fitness for Purpose
A database is fit for purpose if it meets the end-user requirements and the functional requirements.
Accuracy of output
queries are tested by comparing actual results against a table of expected results. The output is accurate if the actual results match the expected results.
Range check testing
If there are range checks (ie data must be entered within certain limits) you must check if the lower and upper limits are working
Restricted choice testing
When there is a restricted choice (ie select options from a dropdown) it must be tested to ensure that it has the correct choices and that a user cannot enter any new data
Presence Checks Testing
If there are any presence checks (ie fields that cannot be left blank) you must check what happens if no input is entered
Length Check Testing
It is not enough to just use the field length as this will only set a maximum number of characters. If a phone number has to be 11 characters then you must test happens if an invalid length of characters is entered (eg 10)
