i. View the Customer Table:

How many Records are there?

Move to the beginning and end of the database

Move to the left and right hand side of the database

What are the names of the fields?

What type of information is held in each field?

Move up and down one screen

ii. Leave the Customer Table on the screen and view the Product and Orders Table. Use the F3 (Up Image) and F4 (Down Image) Keys. Note the field names and the type of information held in each field.

iii. Take the Customer Table off the screen (Use F3 or F4 to place the cursor on the Customer Table and F8 to clear the table from the screen).

iv. Clear the screen of all Tables (Alt F8).

v. View the Client Table; Move to record 9 and change to Form Mode (F7 - to toggle between Table and Form mode). Use the cursor keys to move around the form.

vi. Change back to Table mode and clear the screen of all tables.

i. View the Customer table. Edit record 28 (Press F9 to enter Edit mode), and enter the telephone number 0803 483479.

ii. For record 7 change the Credit Limit to 480.00

iii. In record 23 use Field View (Alt F5) to change the company name to Rose & Kitchener.

iv. Change the address for record 7 to 119 Burrows Road.

v. For Record 14, change the surname to Strickland.

vi. Change the telephone number for record 26 to 071 373 7788.

vii. Add the following record (go to the bottom of the table and arrow down to get blank line):

Customer No: 100

Company Name: Latterly Art Store

Contact: Ms Sonia Latterly

Address: 56, The High Street

Liverpool

Lancashire

L7 7KU

Tel. No: 051 374 4473

Credit: 1,300.00

viii. Save the above changes (F2) and note that the database re-orders itself automatically.

ix. Go back into Edit mode. Delete record 2, then using Ctrl U undo this last change. Leave Edit mode.

i. Create a Table

Create a table called PICTURE (F10 Create).

Enter the following structure:

FIELD NAME FIELD TYPE

Print A20

Name A15

Print Value $

No. in stock N

Save the structure using either F10, DO-IT! or the F2 (DO-IT!) key.

ii. Add Records

Add the following records to the PICTURE table you have just created (F10 Modify Data Entry).

Guernica Picasso 9.99 57

Water Lilies Monet 7.50 32

Rouen Cathedral Monet 8.50 50

Lady in Blue Matisse 8.50 45

The Dream Picasso 7.99 84

The Fiddler Matisse 6.00 62

Bullfight Picasso 12.99 18

iii. Restructure the Table

a) Alter the picture table (Modify Restructure) as follows:

change field name Print to Picture &

change field name Name to Painter.

b) Change the order of the fields so that painter is the first field.

iv. Print a Table

Print out the PICTURE table (Alt F7).

i. Clear all Images from the screen.

View the Product table and using Edit (F9) make the following alterations:

a) change the price of Canvas to 11.50.

b) change the price of a Smock to 14.99.

c) change the description of Easel to Wooden Easel.

d) change the description of Pallet to Artist's Palette.

e) add a new Record:

P09, Gilded Frame, 19.95.

f) save the changes.

ii. Use Modify DataEntry to add the following order to the Orders table:

027, 100, P09, 1.8.92, 17.

iii. Restructure the Customer table to change the field name firstname to forename.

iv. View the Customer, Product and Orders tables. Reduce the table size of each table to 5 records so all 3 tables can be seen together on the screen.

What item did the company "Fool's Gold" order on 15/1/92 and how many?

v. Create a new table called PAINTER with the following structure:

FIELD NAME FIELD TYPE

Forename A15

Painter A15

Date of Birth A4

Date of Death A4

Style of painting A15

vi. Add the following records:

Pablo Picasso 1881 1973 Cubist

Henri Matisse 1869 1954 Fauvist

Claude Monet 1840 1926 Impressionist

vii. Print out the PAINTER table (Alt F7).

Using Ask, Query each Table in turn for the following selection criteria. If the next Query you are performing is on a different Table, ensure that you clear the previous Query Form before asking for the next.

Save each Query and the Answer table. i.e. Save Question 1 Exercise 4 query as Q4-1 and the answer tables as Z4-1.

i. Using the Product Table find out how much a Wooden Easel is and the product code.

ii. Using the Customer Table find out which customers are based in London and what their telephone numbers are.

iii. Using the Picture Table find out which pictures Picasso painted, the print value and the number in stock.

iv. Using the Picture Table find out who painted the picture "Guernica".

Continue if time allows

v. Using the Painter Table find out the full details for Picasso.

vi. Using the Customer Table find out which customers have a credit limit of 500.

vii. Using the Customer Table find out which customers live in Finchley and their title, forename, surname and telephone no.

Using the relevant Tables, perform the following queries using the Range Operators: =, <, >, <=, >=. As for Exercise 4 save each Query and answer tables.

i. Picture - which pictures have a print value of more than 9.00?

ii. Customer - what clients are based in London and have a credit limit greater than or equal to 500.00?

iii. Product - what products have a value between 10 and 20?

iv. Orders - who placed orders in 1991?

v. Customer - Find all the customers whose company name starts with P.

vi. Customer - find all companies whose Postcode has been left blank.

vii. Picture - find all the pictures that were not painted by Picasso.

viii. Picture - find who painted the picture called something like Ruin Cthdrl.

ix. Customer - find all companies whose name does not contain the word Art or Artists in it.

i. Create the following mail merge letter for the Customer table (F10 Report Design, and choose Free Form).

Our Ref: 10020

Date: [Today's Date]

Customer Code: [Customer Code]

Customer Details:

[Company Name]

Attn: [Title] [Surname]

[Address]

[Town]

[County]

[Postcode]

Dear [Forename],

Due to expansion of Renaissance and an increased product range, we feel sure that you would appreciate your credit limit of [Credit Limit] being raised by 20% to the new limit of [Credit Limit] * 1.2.

Yours sincerely

Laura Stephens

ii. Create labels for the Customer table (F10 Report Create); similar to the example labels below.

Latterly Art Store Woolwich Way

Attn: Ms Latterly Attn: Mr Potter

56, The High Street 10, The Main Road

Liverpool Woolwich

Lancashire Herts

L7 7KU HY6 7UU

iii. Create a telephone list for all customers. (F10 Report Create), choose Tabular to create a report similar to the one below:

[Today's Date]

Company Name Title Forename Surname Telephone No

Anchorage Artists Ms Danielle Strickland 081 487 8594

Angel Art Ms Angel Higgins 085 3488 489

etc.

For each question save the sorted table into a new table (F10 Modify Sort).

i. Sort the picture table into painter then picture order.

ii. Sort the picture table into descending print value order.

iii. Sort the Customer table into ascending county order and descending company name.

iv. Sort the Customer table into ascending credit limit.