LIS 558: Database Management Systems

1
LIS 558 Database Management Systems

• Queries
• Margaret Kipp
• mkipp_at_uwo.ca

Faculty of Information Studies, University of
Western Ontario
2
Replication

• Replication allows you to make a copy of the
  database or parts of the database
• It is also possible to merge this replica back
  into the database if changes have been made to it
  and not to the original database.
• Autonumber ID Field Consideration If you will
  be routinely adding a lot of records to a
  replicated database change Autonumber to
  Replication ID for the data type

3
Sorting

• sorts fields alphabetically or numerically
• Records -- Sort
• Options A-Z or Z-A

4
Filtering

• Records - Filter - Filter By Form
• allows you to select criteria for filtering
• Records - Filter - Filter By Selection
• allows you to filter your table by selecting
  item(s) from a field
• Records - Filter - Filter Excluding Selection
• select items to exclude from the filter
• Records - Apply Filter (or Remove Filter to
  remove)

5
Key Components of a Relational Database

• Tables (wk1)
• Records (wk1)
• Fields (wk1)
• Data Types (wk2)
• Data Validation /Constraints (wk2)
• Indexes (wk2)
• Transactions (wk7)
• Querying

• Keys (Primary and Foreign) (wk1)
• Relationships (wk1)
• Forms and Reports (wk2)
• Import/Export Routines (wk1)
• Replication/Backups (wk2)
• Security (wk2)

6
Queries

• Queries are a way to locate information in the
  database
• Structured Query Language is the standard for
  queries but some DBMS offer other methods
• Access permits several query methods
• QBE (query by example)
• QBF (query by form)
• SQL (structured query language)
• Types of queries
• Select
• Action

7
Types of Queries

• SELECT
• Search listed tables and find data matching
  listed criteria
• SELECT Query
• Crosstab Query (Access Term)
• e.g. want to list all employees, items, computers
  by year

8
Types of Queries (cont.)

• Action
• Search listed tables and make changes to the data
• INSERT Query
• APPEND Query (Access term, INSERT SELECT in SQL)
• UPDATE Query
• DELETE Query
• Make Table Query (Access term, combination of
  SELECT and CREATE table in SQL)

9
Querying in Access Three Methods

• QBE (Query By Example)
• Select and Action Queries
• Query Criteria (Where Clauses, Ordering)
• Aggregate Functions (Count(), Sum)
• Parameter Queries
• QBF (Query By Form)
• Forms and VBA Scripting

10
Querying in Access Three Methods (cont.)

• SQL (Structured Query Language)
• Select Queries
• Action Queries
• Create/Alter Table and Field Properties (wk 6,7)

11
In Class Exercise Advanced Querying in Access

• Get the articles database from the course web
  site
• http//publish.uwo.ca/mkipp/teaching/558/558test.
  mdb
• this database consists of material extracted from
  Reference Manager, a citation management tool,
  this is real data and therefore may have
  imperfections
• the data covers books, journal articles and other
  printed materials

12
Query by Example in Access

• queries can be quite simple or extremely
  complicated from listing all the items in a
  table to listing the name and title of employees
  at the main branch
• three ways to define QBE queries
• Wizard
• Design View
• SQL

13
QBE SELECT Queries

• a select query locates and displays information,
  no changes are made to the underlying data
• a select query may be very simple
• e.g. select all items from the table
• a select query may also limit selections
• e.g. select all employees from the main branch
• a select query can also order information
  (sorting) or group it (summarising)
• e.g. select all items from the employees table
  order by employee name

14
Criteria Where Clauses

• criteria allow you to filter the query results by
  specifying specific requirements the data has to
  meet (in SQL this is called a WHERE Clause)
• a criterion may match text, numbers or dates
• example criteria include
• similar last name
• same area code
• salary greater than 40000
• branch is main branch

15
Criteria Key Comparison Operators

• Numeric Criteria
• , ltgt (not equal), gt (greater than), lt (less
  than), gt, lt
• Text Criteria
• , Like (for similar text)
• Like can be used with wildcards ( for any
  character, ? for one character)
• Date/Time Criteria
• same as Numeric

16
Criteria Duplicates and Blanks

• criteria can be used to remove blank results in
  text fields from a query by stating that the
  field should not be blank (use Not '' as the
  criteria '' represents a blank or use Is Null)
• removing duplicates is not possible from the QBE
  wizard or Design View, instead the SQL term
  DISTINCT may be added in SQL View between the
  SELECT keyword and the field names

17
SELECT Queries Examples

• select all items from the table
• use wizard or design view to select all fields
• select all items from the table published in
  Ottawa
• user wizard or design view to select all fields,
  set criteria for City to "Ottawa"
• select titles from the table published after 2000
• select the title field and date field, set the
  date field criteria to gt 2000
• Note that in design view you don't need to list
  the field name in the criteria box

18
Exercise Select Queries

• Write the following queries using the wizard or
  design view
• Select titles, authors, publisher name, date and
  abstract from the table
• Select all publisher names and cities from the
  table, remove duplicates and blanks
• Select all authors from the table
• Select all dates from the table where the date is
  after 1999 (two ways to do this)

19
Exercise Select Queries (cont.)

• Select all abstracts where the abstract starts
  with "This"
• Select all types from the table, remove
  duplicates
• Select the author names for all items published
  in Ottawa with a URL
• Select the titles and URLS for all items
  published in Ottawa dated 1996

20
Ordering and Grouping Clauses

• Ordering clauses allow you to sort your results
  alphabetically or numerically
• in Access this is the Sort field in Design View,
  select Sort and pick ascending or descending
• in SQL this is called an Order By clause
• Grouping clauses allow you to sort your results
• in Access these are referred to as Aggregate
  Functions
• in SQL this is called a Group By Clause
• this includes count, sum, average, max, and min

21
Grouping Aggregate Functions in Access

• Start a Select Query in Design View
• Select the Totals icon (S) to add the totals
  field to design view, this allows you to add
  aggregate functions to your query
• Select the publisher and title fields
• Leave the total set to Group By for publisher,
  set to Count for title
• Run the query. You should get a count of the
  number of items in the database per publisher

22
Exercise Select Queries with Ordering and
Grouping

• Write queries for the following
• select all publishers from the table, sorted
  alphabetically
• select all titles and authors, sorted by title
• select all titles sorted by date
• get a count of all titles grouped by date
• get a count of all items grouped by type

23
Parameter Queries

• parameter queries allow you to prompt the
  database user for criteria for the query
• most queries can be converted by adding the field
  name followed by parentheses () to the criteria
  clause
• e.g. Last Name() or Publisher()
• Exercise Try this out on one of the select
  queries

24
QBE Make Table Queries

• query selects data from a table (or group of
  tables) and stores it in a newly created table
• similar to the append query except it creates a
  new table
• used to move existing data into a new location or
  to join existing data in a new way in a more
  permanent fashion (ordinary queries form
  temporary tables, this creates a permanent table)
• can also be used when normalising a database

25
Make Table Queries (cont.)

• e.g. you want to store a list of all publishers
  in a separate publisher table
• Exercise 1. Create a publisher table with a
  unique ID, publisher name and city.
• Select Create Query in Design View
• Show the main table
• For the first Field, select Publisher with
  Criteria set to Is Not Null. Is Not Null means
  that the query will only select items from the
  Publisher field which have a value.

26
Make Table Query (cont.)

• For the second Field, select City. The criteria
  on the first field will filter out null
  publishers so we don't need it here.
• Go to the Query menu.
• Select Make Table Query
• Enter a name for the table "Publisher" is a good
  choice.
• Switch to SQL View
• Type "DISTINCT" after SELECT but before the list
  of field names. (this filters out duplicates)

27
Make Table Query (cont.)

• Run the Query click exclamation point
• Add a unique ID (primary key)
• Open table in Design View
• Add a column (Publisher ID)
• Change type to AutoNumber
• Save
• Now the table should have a unique ID added for
  every entry, this is a shortcut for adding a
  unique ID to tables that do not have one

28
Make Table Query (cont.) Exercise

• 2. Create a series table with a unique ID and
  series name.
• Steps
• Create a query to select the series title from
  the original table
• Set the criteria to eliminate duplicate entries
  and blank entries
• Set to Make Table Query and Run
• Insert a unique ID column

29
QBE Append Queries

• Append queries allow data to be added to existing
  tables (the closest analogue to this in SQL is
  the INSERT SELECT query)
• data for the append query comes from another
  table in the database

30
Append Queries Exercises

• Enter two more items into the main table
• Write a query to Select the publisher name and
  city from these last two lines
• Change the type of the query to Append Query and
  select the Publisher table
• Set the Criteria to the publisher names you just
  entered, use OR to separate them
• Run the query. The new publisher names and cities
  should be in the publisher table.

31
QBE Update Queries

• Update queries modify existing table data
• a query can be used to locate items that match
  particular criteria for update
• update queries require two things the values to
  go into the table and the values they are
  replacing
• e.g., you could update a branch record in our old
  libraryemployees database to change the address
  after a branch move or change the address or name
  of a publisher after a merger

32
UPDATE Queries Exercises

• Select Query - Update Query
• Notice the new field in design view Update To,
  this is for the new value
• Select the URL field
• Enter "http//example.org/" in the Update To
  field
• Use Criteria Is Null
• Run the query. This should have filled in all the
  empty URL fields with http//example.org/

33
UPDATE Queries Exercises (cont.)

• Change the city associated with one publisher in
  the publisher table we just created using an
  update query. Run this query a second time
  against the original large table.
• Change the blank dates to 1900.
• Change all references to type BOOK to type
  Monograph
• Change all references to type COMP to CDROM where
  date is 1998

34
QBE Delete Queries

• a DELETE query deletes material from the table
  based on various criteria
• the delete query will delete an entire record
  based on identifying particular fields within the
  record
• to delete exactly one record, use the unique
  primary key
• you can delete all of a particular type of record
  quickly with one query

35
Delete Queries Exercises

• Start a new Query in Design View
• Select Query -- Delete Query
• Select a field to use to identify the records to
  delete Type
• Enter a criteria "VIDEO"
• Run the query
• All the items of type VIDEO should now be deleted.

36
Delete Queries Exercises (cont.)

• delete items with publication date 1974
• delete any items with publication city Ottawa and
  date 1998

37
Query By Form

• query by form or QBF allows queries to be run
  from a form
• criteria are entered into the form's boxes to
  specify limits to the query
• fields that are left blank are ignored by the
  query
• QBF forms look like data entry forms

38
Query By Form High Level Overview

• A Query By Form query consists of a set of
  interconnecting objects
• a query (a parameter query)
• a form
• And a macro to tie them together
• Each object will refer to the others by name
• E.g. the macro will name the query that it calls
• The query will specify the names of the text
  boxes on the form as its parameters

39
QBF

• Create a QBF form for searching for items in the
  database by title, item type or date.
• 1. Create a new form which is not based on any
  table. (Create new form in design view)
• 2. Add text boxes for type and date from the
  toolbox
• 3. Select each text box in turn, right click,
  choose properties and set the Name to
• WhatType and WhatDate respectively
• Add one command button and close the properties
  wizard that pops up

40
QBF (cont.)

• Modify the button's properties (right click)
• Set the caption to Search
• Under the Event tab, set OnClick to QBF_Macro and
  run the Macro Builder
• Create a new macro (inside the Macro Builder)
• set Action OpenQuery (select from list)
• Set Name QBF_Query
• Set View Datasheet
• Set Data Mode Edit

41
QBF (cont.)

• Save the Macro as QBF_Macro and the Form as
  QBF_Form, close both of them
• Now create a new query in design view
• select type and date to be fields in the query
• set the criteria for both(Note the criteria must
  all be on one line)
• Forms!QBF_Form!WhatType this refers to the
  form you created (QBF_Form) and the textboxes
  (WhatType and WhatDate)
• Type
• Forms!QBF_Form!WhatType Or Forms!QBF_Form!W
  hatType Is Null
• Date
• Forms!QBF_Form!WhatDate Or Forms!QBF_Form!W
  hatDate Is Null

42
QBF(cont.)

• Select a few other fields to be displayed (e.g.
  authors, publisher)
• Save the Query as QBF_Query
• Now, open the QBF_Form
• Try entering values for type or date