Databases: SQL

From Wikibooks, open books for an open world
Jump to navigation Jump to search

UNIT 3 - ⇑ Databases ⇑

← Normalisation SQL SELECT →

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

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

Write an SQL statement that selects the names and dates of birth of male crooks with less than 3 scars.


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