uery Name

Description

Query

1

Retrieve client information for Jill Klein. List the client ID, client name, st

ate and zip

code for the clientname

of

"

Jill K

lein

"

. (Note: you need to enclose

"

Jill K

lein

"

in

quotations for queries like this that involve text strings).

Query

2

Get a list of our highly priced products. Display the product ID, product name and

price for all products priced over $200.

Query

3

Find out

which notes and comments have been entered for

Clien

t

ID =

267368

.

Display the client ID and all comments entered for

this

ID

.

Query

4

Show all the transactions (meaning lines) where a computer was purchased

(

"

comp

"

). In this query show: Order#, ProdID,

Qty.

Query

5

(2 tables)

Same as Q

uery

4 but this time make more elegant by accessing full descriptions.

Show these fields: order#, prodID, product description, price, quantity.

Query6

(2 tables)

Find out how much money your computer orders are making. List all order

numbers, line items, product names, quantities ordered, unit price (Price), and total

price (Qty*Price) for the ProductID =

"

comp

"

(enclose comp in quotations here too).

Note: you can

use not only single fields in a query column, but also full expressions

like Qty*Price. When you do this, MS Access will assign an ugly name for the

corresponding column (e.g., expr1). However, you can give this column a more

meaningful name using the

"

AS

"

clause (e.g., Qty*Price AS TotalPrice). Tip: you need

to join two tables for this query (Products and LineItems) because part of the

information is available the Products table only (e.g., Price) and other information is

available in the LineItems table o

nly (e.g., Qty).

Query

7

(2 tables)

Run a similar query to query 3 above, but this time let

'

s also list the Client Name

(i.e., if you need to use this query for a management report you need to include

client names, not just client IDs). So, please display

the client ID, client name and

comments entered for the client with an ID =

267368

. Tip: the difference between

this query and item 3 above is that you need to join the ClientComm table with the

Clients table (so that you can get the client name from the C

lients table).

Query

8

(2 tables)

Since we keep track of orders and the line items for these orders in separate tables,

let

'

s build a query that gives us a complete view of all orders and their respective

line items. List all orders, client ID, line item n

umbers, product ID and quantities

ordered in all line items in all orders. Tip: you need to join the Orders and the

LineItems tables

Clients

This

table

contains

1

record

(row)

for

each

client

.

It

contains

the

following

fields:

ClientID,

ClientName,

Address,

City,

State,

ZipCode

and

Telephone

.

The

ClientID

field

contains

a

client

ID

that

uniquely

identifies

each

client

(i.e.,

each

record

;

the

field(s)

that

uniquely

identify

a

record

in

a

table

is

called

the

"

primary

key

"

).

ClientID

ClientName

Address

City

State

ZipCode

Telephone

267368

Milliways

15525 Warwick Blvd

Norfolk

VA

36008

(757) 887

-

2151

268686

Keedsler Motors

15408 NE 45th St

Vancouver

WA

28682

(360) 256

-

3730

281501

Roxxon

60 E Eighth St

New York

NY

20003

(212) 529

-

6637

283757

General Services

4015 Eliza

beth

Ave

Bellingham

WA

28226

(360) 676

-

6458

296832

Jill Klein

2034 Old Lancaster Pike

Reading

PA

29608

(610) 775

-

4423

Orders

This table contains one record

(row) for every order placed by your customers. It contains the following

fields

:

OrderNo, ClientID, OrderDate, OrderDescription, OrderStatus

and

DeliveryDueDate

.

The OrderNo

field is a sequential number that uniquely identifies an order (have you ever ca

lled a company to check

the status of an order you placed? they probably asked you to give them an order

number). The ClientID

is the ID of the client who placed an order. A client ID listed in the orders table must exist in the Clients

table

(

w

hen

a

field

in one table needs to exist in another table, we call it a

"

foreign key

"

).

Note that an order may be placed to purchase more than one item (e.g., when you order a computer

from Dell you may also have included in the same order a flat monitor, a router, a

scanner and a

printer). So, we keep the corresponding line item details in a separate table below.

ITEC

-

200 Database Queries

Lab

5

OrderNo

ClientID

OrderDate

OrderDescription

OrderStatus

DeliveryDueDate

990001

267368

12/

7

/2017

Computer System

Top Priority

12/

10

/2017

990002

268686

12/

7

/2017

Another System

Medium Priority

12/

11

/2017

990004

281501

12/

7

/2017

Only CPUs

Medium Priority

12/

11

/2017

990005

283757

12/

7

/2017

Monitors and Keyboards

Top Priority

12/

7

/2017

990003

296832

12/

7

/2017

More Systems

Low Priority

12/

24

/2017

Products

This table contains one record (row) for each product the company sells

and

contains data about

product

s

recorded in fields:

ProdID, ProdName, ProdDescription,

and

Price

.

ProdID contains a product

code that uniquely identifies each product. The data in the

remaining fields are self

-

explanatory.

ProdID

ProdName

ProdDescription

Price

comp

Computer

Computer Box

1999.98

keyb

Keyboard

Ergonomic Keyboard

119.98

monit

Monitor

Computer Display

374.85

mou

Mouse

3 Button Mouse

44.85

prt

Printer

Laser Printer

443.98

LineItems

This table contains 1 record (row) for every line item of every order placed. Each line item specifies a

product and quantity ordered. The same product cannot appear in more than 1 line item in the same

order (i.e., if y

ou buy 2 identical computers you will only see one line item with a quantity of 2). The

table contains the following fields:

OrderNo, LineItem, ProdID

,

and

Qty

.

The OrderNo field contains the

order number in which the line item belongs. The LineItem field

is a sequential number starting with 1

for the first line item in the order, 2 for the second, and so on

.

The

OrderNo

and

LineItem

fields jointly

and uniquely identify each line item recorded in every

order

OrderNo

LineItem

ProdID

Qty

990001

1

comp

10

990001

2

monit

10

990001

3

keyb

12

990001

4

mou

12

990002

1

comp

20

990002

2

monit

22

990003

1

comp

15

990003

2

keyb

16

990004

1

comp

50

990005

1

monit

12

990005

2

keyb

12

ITEC

-

200 Database Queries

Lab

6

ClientComm

(Client Communication)

This table keeps track of the history of client contact. It contains a record in which salespeople record

notes and comments about every contact so the clie

nt contact history is available

. It contains the

following fields:

ClientID, Date

and

Comment

.

Note

that because clients can call

more than once

, there

may be

multiple

entries for a given client. Therefore, the ClientID and Date (

which

includes a time

stamp) fields jointly and uniquely identify each comment entered about every client

(i.e., a dual primar

y

key).

ClientID

Date

Comments

267368

12/

7

/2017

Client called to inquire about when order will ship.

267368

12/

8

/2017

Client called to inquire a second time about when order will ship.

281501

12/

10

/2017

Client called about warranty service.

283757

12/

8

/2017

Client inquired about return policy.

296832

12/

7

/2017

Called to get specs on product.

Technical