Retrieving information: Part II

1
Retrieving information Part II

• DELETE and UPDATE QUERIES

2
More queries

• Outcomesafter this lecture you will understand
  how to
• Design a query using the design view
• Create a DELETE query

3
We will use the Rent database to illustrate an
APPEND query
4
The Rent Database

• Most rental agreements with students finish after
  1 year. The landlord wants to delete any rental
  agreements and associated students before
  1/7/2005 as new students have taken over the
  property P01. You could do this either by typing
  the SQL directly but because we are going to
  delete from more than one table it will be easier
  to use the query wizard and adapt the QBE
  interface.

5
Entity Relation Diagram (ERD) for rent database
6

• First create a new query to select the students
  you want to
• delete to make sure that you have selected the
  correct
• items for the query.
• To do this, use the wizard to create a
  SELECT query and select RentalNo and End_date
  from the rental table and all the data from the
  Student table. Name the query Rental query.
• You need to add a parameter lt01/07/2004
  to the End_date criteria section (Figure 11)
• When you type lt1/7/2004 into the criteria section
  Access automatically adds the at either end

7
Since we want to delete all the old student
details stored before this date, drag the from
Student into the field
Drag the end_date from Rental into a field
Set the criteria to before 1/7/05
8
DELETE query

• For the rent database we want to delete any
  rental agreements and associated students before
  1/7/2005
• Start the query in design view and check that the
  4 records of rentals before 1/7/2005 are
  returned.

9
Check that this query returns the correct 4
records
10
Change the SELECT query to a DELETE query

• First run the query again
• Change to design view
• Go to Query/ Delete
• The template will change to show delete in one of
  the row headings.
• Save and run the query
• You will be asked if you are sure you want to
  delete the records
• You will also be prompted that 4 records are
  about to be deleted.

11
Delete
Parameter for choosing records to delete
12

• Now if you run the query you will get 2 warnings
  again before you proceed and the 4 records will
  be deleted to leave the 10 students registered
  for 2004/2005.
• BE VERY CAREFUL when using the DELETE query as
  you may delete records you do not want to lose
  and you may have to spend time retyping in the
  data.

13
Wildcards for criteria

• The following table gives a range of wildcards
  and operators (used in mathematical operations
• These are very useful for customizing queries to
  suit your clients business needs.

14
Wildcards for criteria
Activity 1 Some tables will contain records with
no values in a particular field (e.g. a student
may not have a mobile phone no). Which of the
above would you use and where? Activity 2 How
would you search for properties with a deposit of
greater than 800?
15
Wildcards versus prompts

• You could search for records for a particular
  person by hard-coding the name into the parameter
  such as the example Jimmy or Jane.
• This is not as flexible as using a prompt at
  runtime as you would have to create different
  queries for each name for whom you wanted data.

16
UPDATE queries

• Design a query using the design view
• Create an UPDATE query

17

• 7.4 UPDATE query
• This is used to update data globally in a table
  or tables. For example if the landlord wants to
  raise the rent by 10 this could be done using an
  UPDATE query. Your rent database does not have
  rents but what if the landlord decides after
  refurbishing the flats that all the deposits
  should be set at 1500 per flat.
• Do not forget to add the semicolon at the end.
• Save the query as Updatedeposit and run it.
• Check in the Rental table that all the deposits
  now read 1500.

18
Writing SQL commands directly to create an UPDATE
query

• Start a new query and open in design view.
• Close the tables box and go to SQL view.

19

• Delete SELECT to clear the SQL view screen
• In the blank screen type
• UPDATE Rental
• SET Deposit 1500
• Remember that commands are in uppercase

20
Note that the Deposits have all been updated to
1500 in the rental table.
21
Writing more complex SQL syntax

• You can learn about writing SQL by viewing the
  SQL view every time use create a query using a
  wizard.
• This is how query by example (QBE) can be used to
  show more complex examples
• The following example, using parameters and
  joins, is from the Flatmates database

22
Demonstration of code behind a SELECT query

• SELECT Owner.OwnerID, Owner.Name, Book.Title,
  Book.Author, Book.Type_of_ book, Book.BookID
• FROM Owner INNER JOIN Book ON Owner.OwnerID
  Book.OwnerID
• WHERE (((Owner.Name)which name))

• OwnerID and Name are selected from the table
  Owner Title, Author, Type_of_book and BookID are
  from the Book table
• This specifies that the tables are joined by the
  OwnerID primary key from Owner to the OwnerID
  primary key from Book
• A parameter has been set in the criteria box for
  the name field prompting a message which name
  to be displayed at runtime.