A-level Computing 2009/AQA/Problem Solving, Programming, Operating Systems, Databases and Networking/Databases/SELECT





The SELECT statement allows you to ask the database a question (Query it), and specify what data it returns. We might want to ask something like Tell me the name and ages of all the crooks. Of course this wouldn't work, so we need to put this into a language that a computer can understand: Structured Query Language or SQL for short: This would return the following:

But suppose we wanted to filter these results, for instance: Tell me the ID, name and ages of all the crooks who are male and come from Snape. We need to use another statement, the WHERE clause, allowing us to give the query some criteria (or options): This would return the following:

Say the police knew that a crime had been committed by a heavily scarred woman (4+ scars), they want a list of all the scarred women: This would return: However, the police want to quickly sort through and see who is the most heavily scarred. We are going to use an ORDER command: ORDER BY numScars sorts your returned data into DESCending (big to small) or ASCending (small to big) order

You can build even more complex SQL WHERE statements by combining ANDs and ORs. Maybe the police should have said that a criminal is either a woman, OR a man AND from Newcastle.

INNER JOIN
We spoke earlier about how databases are great because they allow you to link tables together and perform complex searches on linked data. So far we have only looked at searching one table.

When you use a social network such as Facebook you can often see a list of all your friends in the side bar as well as details from your record such as your name and place of work. How did they find this data? They would have searched for all the relationships that involve your ID, returning the names of people involved AND returned values such as job title from your personal record. This looks like using two queries: --return relationship information—return personal record information It would be possible to do this, but it's far easier to use one query for both things.

Take a look at this example. The police want to know the name and town of a criminal (ID = 45) along with all the descriptions of crimes they have performed: