A-level Computing/AQA/Problem Solving, Programming, Operating Systems, Databases and Networking/Databases/SQL
Structured Query Language (SQL) is used to perform function on a database. There are four main functions that you should be familiar with: SELECT, INSERT, UPDATE, DELETE
 |
Built in SQL commands are normally written in capital letters, making your statements easier to read. However, you can get away without them. |
To help us understand how these things work we are going to use a test data set. Databases are used in all areas of the computer industry, but for the moment we are going to use a dataset that keeps track of crooks in England, noting, names, gender, date of birth, towns and numbers of scars. Take a look at the crooks data table below:
| ID | name | gender | DoB | town | numScars |
|---|---|---|---|---|---|
| 1 | Geoff | male | 12/05/1982 | Hull | 0 |
| 2 | Jane | female | 05/08/1956 | York | 1 |
| 3 | Keith | male | 07/02/1999 | Snape | 6 |
| 4 | Oliver | male | 22/08/1976 | Blaxhall | 2 |
| 5 | Kelly | female | 11/11/1911 | East Ham | 10 |
| 6 | Marea | female | 14/07/1940 | Wythenshawe | 6 |
To select all the items from this table we can use:
SELECT * FROM crooks
This would display all the results. But what if we just want to display the names and number of scars of the female crooks?
SELECT name, numScars FROM crooks
WHERE gender = 'female'
The result of this query would be:
| name | numScars |
|---|---|
| Jane | 1 |
| Kelly | 10 |
| Marea | 6 |
|
Questions Write an SQL statement that selects the names and dates of birth of male crooks with less than 3 scars. Answer:
SELECT name, DoB FROM crooks
WHERE gender = 'male'
AND numScars < 3
|
|
Extension: Practice SQL You can practice your SQL skills using Access, MySQL (as part of XAMPP), MSSQL, SQLite etc. If you don't have this software easily available you might want to take a look at the SQLZoo site, which allows you to hone your skills in an online environment where you don't have to worry about accidentally deleting all your records! For the exam and probably for your projects you need to be very good at SQL, so get practicing. |
|
AQA Teacher Resources: Database querying a database using SQL |