Transcript 4_Querying

Chapter 3
Querying and Sorting
IT Key Ideas, Dynamic Database Systems, 2002
page 15
Interrogating the Database
The real power of database software is the
ability to query the database in order to
find answers to specific questions, or in
other words to turn data into information.
IT Key Ideas, Dynamic Database Systems, 2002
The result of a query is a set of records that
match the conditions of the query.
This set of records is a table in appearance,
although it is not a real table of the
database system in use. Often it is referred
to as a temporary or resultant table.
IT Key Ideas, Dynamic Database Systems, 2002
Queries provide the following advantages
• specific fields can be chosen to display particular
•
•
•
•
•
columns of data
specific records can be searched for that match a
criteria
records can be viewed in a specific order
calculations can be performed on the data in a
table
data from multiple tables may be matched
specific data can be obtained for use in forms,
reports or graphs.
The interrogating of the database to find
records that match a criteria is an
essential component of any database
system. The result of the interrogation is
referred to as an outcome of the system.
An outcome should present a sub-set of
the data, rather than show all of the
records of the database.
IT Key Ideas, Dynamic Database Systems, 2002
When one asks for help in a library, the
librarian will ask a question like
what topic are you researching
(eg. database systems) ,
or what author are you looking for
(eg. Potter)?
IT Key Ideas, Dynamic Database Systems, 2002
When a person walks into a car yard to
purchase a car the salesperson will ask a
question like
what brand of car are you interested in
(eg. Commodore), or
what size car (eg. 6 cylinder) or
what type of vehicle (eg. wagon)?
Queries
page 16
Practical 3.1
IT Key Ideas, Dynamic Database Systems, 2002
Practical 3.1
2. Display a list of all surnames and given names, in that
order.
3. Display a list of given names followed by surnames, where
the list is alphabetically sorted by surname.
4. Display a list of the surnames of females and then males,
where each group is alphabetically sorted.
5. Display a list of the people who have red hair, in
alphabetical order?
6. How many males are there? Hint: look at the data in the
table before executing the query.
7. Display a list of people who do not have blonde hair.
8. How many people are 15 years of age?
IT Key Ideas, Dynamic Database Systems, 2002
Database file for querying
Data File
Class Details
IT Key Ideas, Dynamic Database Systems, 2002
Querying a Database Table
IT Key Ideas, Dynamic Database Systems, 2002
Show/Add a table to the query
IT Key Ideas, Dynamic Database Systems, 2002
page 17
click and
drag fields to
the Field line
QBE Grid
or click the pop-down list arrow and choose
IT Key Ideas, Dynamic Database Systems, 2002
To display all fields
page 17
drag the
asterisk to
the field
line
Toolbars
IT Key Ideas, Dynamic Database Systems, 2002
Advice
If the line Table line is showing within
QBE grid turn it off, View Table Names
command; as the current database only
has one table.
IT Key Ideas, Dynamic Database Systems, 2002
Displaying particular fields
drag
Given
Names
and
Surname
to the
field line
click the
Datasheet
View or Run
button in
order to view
the recordset
Changing the order of the Fields
highlight a
column(s)
drag the
column(s)
across by
using the
Field Selector
(it’s a very
thin grey line)
IT Key Ideas, Dynamic Database Systems, 2002
Field
Selector
Ordering Records
click in the
required
column on
the sort line
and choose
ascending
or
descending
IT Key Ideas, Dynamic Database Systems, 2002
Sorting on more than one field
and controlling the display
Given Name
Kate
Zena
Catherine
Kate
Patience
Georgia
Kate
Winifred
Jacinta
Clark
Benson
Joseph
Alan
Matt
Matthew
Surname
Abbott
Abram
Albany
Ashby
Baldwin
Barnes
Barnes
Barr
Barrachina
Abbey
Aitchison
Badcock
Badcow
Bazocca
Clarke
Sex
F
F
F
F
F
F
F
F
F
M
M
M
M
M
M
how is the
table
organised?
IT Key Ideas, Dynamic Database Systems, 2002
Hint: in order to achieve the required
output, it is possible to insert the
same field more than once.
IT Key Ideas, Dynamic Database Systems, 2002
Given Names
Surname
Sex
Ascending
Surname
Ascending
IT Key Ideas, Dynamic Database Systems, 2002
Conditional Retrieval
Setting a condition, by using criteria in the QBE,
can retrieve particular records of a database
table. Remember that a record is a row of data.
The database software processes the data in the
database by finding records that match the
criteria and produces a subset of data, from the
whole system.
IT Key Ideas, Dynamic Database Systems, 2002
Alphanumeric Criteria
To find people with red hair, enter the value
red as the criterion under the Hair Colour field.
Hair Colour
red
IT Key Ideas, Dynamic Database Systems, 2002
SQL equivalent
SELECT [Given Names], Surname
FROM Details
WHERE [Hair Colour] =”red”
ORDER BY Surname;
IT Key Ideas, Dynamic Database Systems, 2002
SQL
SELECT
FROM
specifies the fields in the query
specifies the table the data is
stored in
WHERE
specifies the criteria that
records have to match, and
ORDER BY specifies the order in which the
records will be sorted.
IT Key Ideas, Dynamic Database Systems, 2002
How many males?
Sex
m
IT Key Ideas, Dynamic Database Systems, 2002
Not blondes
Hair Colour
not blonde
not is referred to as a logical operator
IT Key Ideas, Dynamic Database Systems, 2002
Calculation Fields
In order to query the database for ages,
eg. 15 year olds, it is necessary to calculate
people’s ages, as each person has their
Date of Birth stored.
Date of Birth is stored so that when
queries are executed at later times correct
outputs apply irregardless of when the list
is produced.
IT Key Ideas, Dynamic Database Systems, 2002
The fact that different outputs will be
produced depending on when the query is
executed is referred to as dynamic.
It is this concept of dynamic outputs that
distinguishes IT apart from other fields of
study.
IT Key Ideas, Dynamic Database Systems, 2002
In order to calculate people’s ages it is
necessary to develop an algorithm to
complete the calculation.
An algorithm is a series of steps to solve a
problem.
IT Key Ideas, Dynamic Database Systems, 2002
Calculating Age
Consider your age.
What does your age depend on?
IT Key Ideas, Dynamic Database Systems, 2002
Calculating Age
Your date of birth and today’s date
IT Key Ideas, Dynamic Database Systems, 2002
Today’s date
Today’s date can
be displayed by
using the function
Date().
Type this on the
Field line.
NB: The Now()
function could be
used alternatively.
IT Key Ideas, Dynamic Database Systems, 2002
Today’s date
The Expr1: can be replaced by your
own field name, eg. Current – followed
by a colon :
IT Key Ideas, Dynamic Database Systems, 2002
Age
A person’s age is calculated by
Today’s Date minus their Date of Birth
IT Key Ideas, Dynamic Database Systems, 2002
Age
Functions and fields
can be selected using a
point and click method
by using the
Expression Builder.
IT Key Ideas, Dynamic Database Systems, 2002
Empty the field
line and click on
the Build button.
Select
Functions
Built-in Functions
Date/Time
and double-click
Date
IT Key Ideas, Dynamic Database Systems, 2002
Type or click on a
minus sign
then select
Tables
Details
and double click
Date of Birth
Select OK
IT Key Ideas, Dynamic Database Systems, 2002
Also place the Date of Birth field in this
query as a separate column.
Run the query.
IT Key Ideas, Dynamic Database Systems, 2002
This calculation produces a person’s
age in days, as computers store dates in
days - from a starting point, such as
1/1/1900.
IT Key Ideas, Dynamic Database Systems, 2002
To determine a person’s age in years,
divide the result of the subtraction by
365.2425
NB: The decimal component is necessary to
allow for leap years every 400 and every 4
years, but not every 100 years.
IT Key Ideas, Dynamic Database Systems, 2002
Age
Re-enter the Expression Builder and enter
the division. Hint: don’t forget the extra
brackets. Change the name of the
calculated field to Age.
Run the query.
IT Key Ideas, Dynamic Database Systems, 2002
Age, as a whole number
Ages now appear, except they include a
decimal component.
To remove the decimal component, as one
is 15 at 15 years and 1 day through to 364
days, another function, namely Int, is
utilised.
IT Key Ideas, Dynamic Database Systems, 2002
Int(number)
Int is a function in both database and spreadsheet software.
Example Int(Cost)
Item
Deodorant
Magazine
Washing Powder
Detergent
Nails
Cost
6.3
8.85
5.95
4.85
7.74
Int(Cost)
6
8
5
4
7
IT Key Ideas, Dynamic Database Systems, 2002
Age, as a whole number
Place the previous expression inside the
brackets of Int function.
Save this query for re-use many times.
IT Key Ideas, Dynamic Database Systems, 2002
15 year olds
Age:
15
Age:Int((Date()-[Date of Birth])/365.2425)
IT Key Ideas, Dynamic Database Systems, 2002
At least 15 years of age
Age:Int((Date()-[D
>=15
IT Key Ideas, Dynamic Database Systems, 2002
Full Names
People’s full names consist of their
given name(s) and their surname. To
join strings of text together use the &
operator. This can be constructed by
pointing and clicking on the field names
in the Expression Builder.
IT Key Ideas, Dynamic Database Systems, 2002
IT Key Ideas, Dynamic Database Systems, 2002
Full names of females
Full Name:[Given Names]&” “&[Surname]
Sex
F
Save this query!
Both with the Sex field and without – in order
to re-use the Full Name calculation in future
questions!
IT Key Ideas, Dynamic Database Systems, 2002
Between 14 and 17 years of ages
Age:Int((Date()-[Date of Birth]
between 14 and 17
IT Key Ideas, Dynamic Database Systems, 2002
Born in the second of 1986
Age:Int((Date()-[Date of Birth]
Ascending
between 1/7/86 and 31/12/86
IT Key Ideas, Dynamic Database Systems, 2002
12, 14 or 17 years of age
Age:Int((Date()-[Da
in (12, 14, 17)
IT Key Ideas, Dynamic Database Systems, 2002
Brownish hair
Hair Colour
*brown
IT Key Ideas, Dynamic Database Systems, 2002
Complex Criteria
A query with a criterion (one single
criteria) is referred to as a simple
query.
A query with two or more criteria is
referred to as a complex query.
IT Key Ideas, Dynamic Database Systems, 2002
Complex Criteria
As a complex query involves two or
more criteria the conditions are joined
through the use of the and or or logical
operators.
eg.
blue eyes and blonde hair
16 years of age or male
IT Key Ideas, Dynamic Database Systems, 2002
Blue eyes and blonde hair
SQL
Eye Colour
Hair Colour
blue
blonde
SELECT Surname, Eye Colour, Hair Colour
FROM Details
WHERE [Eye Colour] = blue
AND [Hair Colour] = blonde;
IT Key Ideas, Dynamic Database Systems, 2002
Blue or hazel eyes
Eye Colour
blue
hazel
IT Key Ideas, Dynamic Database Systems, 2002
Blue eyes or red hair
Eye Colour
Hair Colour
blue
red
IT Key Ideas, Dynamic Database Systems, 2002
Blue eyes and blonde hair and 15
Eye Colour
Hair Colour
Age:Int((Dat
blue
not blonde
15
IT Key Ideas, Dynamic Database Systems, 2002
Blue eyes and not blonde hair
Eye Colour
Hair Colour
blue
not blonde
IT Key Ideas, Dynamic Database Systems, 2002
Matching hair and eyes
Eye Colour
Hair Colour
blue
brown
blonde
*brown
blue eyes and blonde hair
brown eyes and brownish hair
IT Key Ideas, Dynamic Database Systems, 2002
15 who have either brown or blonde hair
Age:
Hair Colour
15
brown or blonde
IT Key Ideas, Dynamic Database Systems, 2002
Brown hair or blonde haired 15 year olds
Hair Colour
Age:
brown
blonde
15
IT Key Ideas, Dynamic Database Systems, 2002
Same hair colour and eye colour
Hair Colour
[Eye Colour]
SQL
SELECT Surname, Hair Colour
FROM Details
WHERE [Hair Colour] = [Eye Colour];
IT Key Ideas, Dynamic Database Systems, 2002
page 33
Data File
Exercise 3.2 - Extension
People
IT Key Ideas, Dynamic Database Systems, 2002
Homework/Study
page 35-36
Exercise 3.3
table of data page 34
IT Key Ideas, Dynamic Database Systems, 2002
Chapter 3
********************************