Retrieving information from a relational database

1
Retrieving information from a relational database

• Using queries

2
Outcomes

• At the end of the session you will understand
• the characteristics of Query-by-Example (QBE
• the purpose and importance of the Structured
  Query Language (SQL)
• how to retrieve data from a database using the
  SELECT command

3
Query-By-Example (QBE)

• QBE represents a visual approach for accessing
  data in a databse through the use of query
  templates (Zloof, 1977, quoted p 199 Connolly and
  Begg, 2005)
• You can use QBE to ask questions about the data
  in one or more tables.
• When you use Access wizards to create a query,
  access constructs the equivalent Structured Query
  Language (SQL) statement.

4
What is SQL? (see Chapter 5 Database Systems,
Connolly and Begg, 2005).

• SQL is an example of a transform-oriented
  language designed to use relations to transform
  inputs into required outputs.
• As a language, the ISO SQL standard (ISO 1992)
  has 2 components
• A Data definition Language (DDL) defines the
  database structure and control of access to data
• A Data Manipulation language (DML) - retrieving
  and updates data

5
Using SQL in Access

• When you use the query wizard, the SQL statements
  are automatically written in the correct syntax
  (the code can be seen in the SQLview).
• You will be mostly designing queries using the
  wizard tool but you will also learn how to
  directly type in SQL commands to retrieve or
  update data.

6
Syntax

• Unlike some Microsoft programming languages such
  as VBA and VB.NET
• SQL is case sensitive.
• Upper case is used for reserved words and must be
  spelt exactly.
• Lower-case letters are used for user-defined
  words

7
Data manipulation SQL DML statements

• SELECT
• INSERT
• UPDATE
• DELETE

8
Types of queries used in Microsoft Access

• Select Query select and retrieve information
• Parameter query prompts you for the criteria
  each time you execute the query
• Total query performs calculations on a group of
  records using Sum, Count, Max and Min.
• Update Query update existing field values
• Delete Query delete selected field values
• Make Table Query create a new database table
  based on selected field values
• Append Query add records to a database table
  based on selected values
• Crosstab Query present collated values of one
  field within a table based on two other fields

9
SELECT queries

• These are the most common form of query and by
  default that is the form of query you are
  presented with when you have selected your tables
  or queries. If you need to change the query to a
  different type then select it from the Query
  menu. Probably the best way to explain the use of
  a Select query is to look at an example.
• First we need to decide on the information
  needed in this example we are going to retrieve
  data on who owns which books.

10
Creating a new query using the query wizard

• In order to create a new query, from the database
  window select the Query tab and then click on
  New.
• You will be offered a selection of methods-we
  shall use the wizard now.
• You will then be asked to add Tables, Queries or
  Both.
• You need to select the initial data that is to
  act as input to the query. Note that a query can
  also be based on another query, so if you want
  some fairly complex organisation of data that is
  not possible in a single query then they can be
  sequenced.
• When a query is based on another query the first
  query is run first and its output then becomes
  the input to the next query.

11
Click on Queries and select New, then Simple
Query Wizrd
12
Selecting the source of data
You can choose a table or query as the data
source for this query we need the table Book
Transfer the required fields across to the
Selected fields screen one by one (gt) or all (gtgt)
13
Output when the who owns what query is run
Save the query as Who owns what and run it to
obtain the output shown below
We can make this query more useful by adding a a
parameter to narrow the search
14
Parameters

• Queries are often static, that is you define
  precisely what the query does in advance and it
  runs the same way every time.
• However, you can also allow the user to specify
  characteristics of the query at run-time through
  the use of parameters.
• When creating an information system, rather than
  a database, one of the things you want to protect
  the user from is having to understand the
  database that stores the data.
• They should be able to respond with the system in
  a high-level and meaningful way. This means that
  you are responsible for creating the system that
  does the work for the user.

15
Specifying criteria for a parameter query

• A parameter query prompts you for the criteria
  each time you run the query. There is a
  difference between a select query and a parameter
  query in the way the criteria are specified. A
  select query contains the actual criteria whilst
  a parameter query prompts you for the query at
  runtime.

16
Example
We can make the query prompt the user to type in
a single name to search for that persons books
If you type your question to prompt an answer,
put in . brackets
17
For example, run the who owns what query and type
in Sarah at runtime
18
Output from running the parameter query Who owns
what with Sarah entered into the message box
a list of books for Sarah (just 1 in this case).
The output of this query is called a dynaset
19
Dynasets

• The output from running the query produces a set
  of data
• When you run a query by clicking the output is
  displayed as though it was a table of data. Even
  if a query points at a single table, what is
  displayed by a query is termed a Dynaset.
• A dynaset displays a set of data values that are
  dynamically linked to the underlying tables.
• This means that often changes to the dynaset will
  be reflected in the table.

20
Tutorial

• Work through Tutorial Exercise 8.1 (SELECT
  queries)