Transcript Basics of relational databases Exercises with Northwind

Basics of relational databases
Exercises with Northwind sample database
1. Answer to the questions based on the information in the Northwind database.
a) Find all German suppliers. How many are there ?
b) What products does Karkki Oy supply ?
c) Which order has the biggest subtotal ? What is its order ID ?
d) How many products are packed in cans, tins or jars ?
e) How many different products are on order at the moment ?
f) How many customers don´t have a fax number ?
g) What field(s) is (are) common for the tables Customers and Orders ?
h) How many products can belong to one product category ? HINT ! See the relationship window.
2. a) Search for the customer record of Romero y Tomillo (Form). What are the fields of this record ?
b) Which customer record does contain the name ”Pipps” (or a part of it) ? Use Find command !
3. Sort the products in an ascending order by the unit price (Table). What´s the highest unit price ?
What´s the lowest one ?
4. Filter products by the quantity per unit. The filterred products should be packed in boxes. How many
that kind of products are there ?
5. Open Order details table. Choose Design view.
a) What is the data type of Quantity field and what are the other properties of this field ?
b) What is the data type in Discount field and what are the other properties ?
6. Select Orders form.
a) Find orders of Simons bistro by filtering. How many are there ?
b) Sort all the orders by Order date in a descending sequence. What are the latest date and the first date ?
7. Select the query called Product Sales for 1997.
a) Choose Design view. How criterium ”In Year 1997” is marked ?
b) Which product gives most money ?
8. Select function Help.
Search information about a topic ”Adding a New Chart in Report”.
9. Insert a new record
a) in Customers table.
KOTKU Kotkan kulinaria Pekka Kokkonen Owner Laivurinkatu 44 Kotka 48100 Finland
05-220 8374 05-220 8301
b) in Customers form.
VIRHE Virolahden herkku Paula Pekkola Owner Vaalimaantie 93 Virolahti 49900 Finland
05-3544 201 05-3544 221
c) Update the street address of a customer in the Customers table (or form) .