Fundamentals of 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:
SELECT name, DoB --what to return
FROM crooks --where are you returning it from
This would return the following:
name | DoB |
---|---|
Geoff | 12/05/1982 |
Jane | 05/08/1956 |
Keith | 07/02/1999 |
Oliver | 22/08/1976 |
Kelly | 11/11/1911 |
Marea | 14/07/1940 |
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):
SELECT ID, name, DoB
FROM crooks
WHERE town = 'Snape' AND gender = 'male' --Criteria
This would return the following:
ID | name | DoB |
---|---|---|
3 | Keith | 07/02/1999 |
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:
SELECT name, town, scars
FROM crooks
WHERE numScars >= 4 AND gender = 'female' --Criteria
This would return:
name | town | numScars |
---|---|---|
Kelly | East Ham | 10 |
Marea | Wythenshawe | 6 |
However, the police want to quickly sort through and see who is the most heavily scarred. We are going to use an ORDER command:
SELECT name, town
FROM crooks
WHERE numScars >= 4 AND gender = 'female' --Criteria
ORDER BY numScars DESC --sorts the numScars values in big to small order
ORDER BY numScars sorts your returned data into DESCending (big to small) or ASCending (small to big) order
Extension: | ||||||||||||||
WARNING: in the exam they will likely ask you to return specific fields so using a * (wildcard character) will lose you marks! Sometimes you would like to return all the details regarding a record, this is a little cumbersome if you have to list them: SELECT name, DoB, gender, town ....
Instead we can use the *. This means return all fields from a selected record: SELECT * --return all fields from crooks
FROM crooks
WHERE name = 'Jane'
This would return: |
|
Exercise: SELECT statements Using the criminals data set, write SQL to find the name and location of women born before 1993 Answer: SELECT name, town
FROM crooks
WHERE DoB < 1993 AND gender = 'female'
Using the above data set, write a Query to find out the town and gender for all persons called Noel: Answer: SELECT town, gender FROM crooks --you don't have to have a separate line for each SQL statement
WHERE name = 'Noel'
The police are pretty sure that a criminal is either a woman OR a person from Newcastle. Find their names and DoB Answer: SELECT name, DoB FROM crooks
WHERE gender = 'female' OR town = 'Newcastle'
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. SELECT name, DoB FROM crooks
WHERE gender = 'female' OR (gender = 'male' AND town = 'Newcastle')
Using the above data set, write a Query to find the ages of crooks from youngest to oldest Answer: SELECT name, town, DoB FROM crooks
ORDER BY DoB DESC
|
INNER JOIN
editWe 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:
SELECT name, town, description --select things to return (from different tables)
FROM crooks, crime --name tables that data comes from
WHERE crook.Id = crime.crimId --specify the link dot.notation means table.field. The Ids are the same
AND crook.Id = 45 --specify which crook you are looking at
ORDER BY date ASC --order the results by the oldest first
Operators used in the WHERE clauses
editOperator | Meaning of the operator | Example |
---|---|---|
= | Checks if they're equivalent | Id1 = 123 |
> and < | Checks if a field is greater than or less than |
|
<> | Checks if it's not equal to | Id1 <> 123 |
>= and <= | Similar to "> and <", but also checks for equality |
|
OR | Will be accepted if either the left, right or both are true | Id1 = 123 OR Id2 <> 444 |
AND | Only accepted if both the left part and the right part are true | Id1 = 123 AND Id2 <> 444 |
NOT | Inverts the boolean value of the statement | NOT Id1 = 123 |
IS NULL | Checks if it's a null value contained within the variable | Id1 IS NULL |
... BETWEEN ... AND ... | Checks if something is within a range | Id1 BETWEEN 2.8 AND 3.14159265 |