Databases Reference
In-Depth Information
14.
When sorting data on more than one field in an Access query, which field is the major sort key? Which field is
the minor sort key? What effect do these keys have on the order in which the rows are displayed?
66
15.
How do you join tables in an Access query?
16.
When do you use an update query?
17.
When do you use a delete query?
18.
When do you use a make-table query?
19.
What is relational algebra?
20.
Describe the purpose of the SELECT command in relational algebra.
21.
Describe the purpose of the PROJECT command in relational algebra.
22.
Describe the purpose of the JOIN command in relational algebra.
23.
Describe the purpose of the UNION command in relational algebra.
24.
Are there any restrictions on the tables when using the UNION command? If so, what are these restrictions?
25.
Describe the purpose of the INTERSECT command in relational algebra.
26.
Describe the purpose of the SUBTRACT command in relational algebra.
27.
Describe the purpose of the product process in relational algebra.
28.
Describe the results of the division process in relational algebra.
In the following exercises, you will use the data in the Premiere Products database shown in Figure 2-1. (If you use
a computer to complete these exercises, use a copy of the Premiere Products database so you will still have the origi-
nal data when you complete Chapter 3.) In each step, use QBE to obtain the desired results. You can use the query
feature in a DBMS to complete the exercises using a computer, or you can simply write a description of how you
would complete the task. Check with your instructor if you are uncertain about which approach to take.
1.
List the number and name of all customers.
2.
List the complete Part table.
3.
List the number and name of all customers represented by sales rep 35.
4.
List the number and name of all customers that are represented by sales rep 35 and that have a credit limit of
$10,000.
5.
List the number and name of all customers that are represented by sales rep 35 or that have a credit limit of
$10,000.
6.
For each order, list the order number, order date, number of the customer that placed the order, and name of
the customer that placed the order.
7.
List the number and name of all customers represented by Juan Perez.
8.
How many customers have a credit limit of $10,000?
9.
Find the total of the balances for all customers represented by sales rep 35.
10.
Give the part number, description, and on-hand value (OnHand * Price) for each part in item class HW.
11.
List all columns and all records in the Part table. Sort the results by part description.
12.
List all columns and all records in the Part table. Sort the results by part number within item class.
13.
List the item class and the sum of the value of parts on hand. Group the results by item class.
14.
Create a new table named SportingGoods to contain the columns PartNum, Description, OnHand,
Warehouse, and Price for all rows in which the item class is SG.
15.
In the SportingGoods table, change the description of part BV06 to “Fitness Gym.”
16.
In the SportingGoods table, delete every row in which the price is greater than $1,000.
