SQL and Queries

Página 1

SQL and Queries

What is SQL and what

is a query?

• A

Query is a little piece of code within a database that can be used to extract

useful information

– E.g.

we could use a query in our top trumps database to find any characters with a

size rating of 5.

• To

write a query we use a special query language called SQL

• SQL

Stands for Structured Query Language

Creating SQL Queries

• Click

Create à Query Design

• When

you see the Show Table dialogue box, click OK.

You don’t need to select anything else.

• Now,

in the top left corner, click the view button and choose SQL view

Writing SQL

• We

need to use 3 key words:

SELECT

FROM

WHERE

E.g:

SELECT name, size FROM tblTrumps WHERE size=5;

This will show you the name and size of all the top

trumps where the size is 5

Viewing our Results

• Viewing

the results of our query is easy. We

just need to click the Run button. It

looks like a red exclamation mark (!)

• To

select all fields in a table, you can use an asterisk.

• SELECT

* FROM tblTrumps;

• To

check for values more than a number, use >

• SELECT

* FROM tblTrumps WHERE size>5;

•

You can also use < for less than

•

You can use <= for less than or equal to

•

You can use >= for more than or equal to

•

You can use <> for not equal to

• You

can use AND and OR to select multiple criteria

• SELECT

* FROM tblTrumps WHERE size > 5 AND agility > 8 ;

•

This will find large, agile characters

•

You can use OR instead of AND to find either characters

that are large OR agile

• Sometimes,

you want to find a text field that has something in the name. E.g. you might want to find all characters

whose name starts with the letter R. We

will need to use the asterisk again for this.

• SELECT

* FROM tblTrumps WHERE name like “R*”;

•

We could have looked for anything ending

in R by searching for “*R”

•

Note – because we are looking for

text, we need to put it in quotation marks;

•

Note 2 – When using the * wildcard, we

use like instead of =

• Sometimes

it is nice to sort your results in a particular order. We can use the key words ORDER BY for

this. To sort these characters by size

we would write:

• SELECT

* FROM tblTrumps WHERE name like “R*” ORDER BY size ASC;

•

To sort in descending order of size, we could

write:

•

SELECT * FROM tblTrumps WHERE name like “R*”

ORDER BY size DESC;