Problem

At Bird Company, an order is recorded in two separate tables as follows. The table titled...

At Bird Company, an order is recorded in two separate tables as follows. The table titled orders contains a single record of header information for each order in the following fields: order number (numeric data), customer number (character data), order type (character data), subtotal (numeric data), and shipping charges (numeric data). The items ordered and related amounts for each order (a “many-to-one” relationship) are recorded in the table titled order details. Each entry in the order detailstable contains the fields order number (numeric data), the vendor product number of the item (character data), quantity of the item (numeric data), and price of the item (numeric data). Sample records are illustrated below.

Orders (Sample Records)

Order Number (numeric)

Customer Number (character)

Order Type (character)

Subtotal (numeric)

Shipping Charges (numeric)

101

16822

Phone

145.00

12.25

102

10023

Email

224.00

15.66

103

12476

Fax

98.54

8.48

104

16822

Mail

210.44

24.00

105

14562

Fax

500.00

45.65

Order Details (Sample Records)

Order Number (numeric)

Vendor Product (character data)

Quantity (numeric)

Price (numeric)

101

16,822

2

22.50

101

10,023

5

10.00

101

12,476

2

25.00

102

15,822

2

112.00

103

10,023

5

10.00

103

11,476

2

20.00

103

10,099

1

8.54

104

15,822

3

168.00

104

15,444

2

21.22

105

12,476

20

25.00

Required

a. Write an SQL query that retrieves all of the fields for each record and all of the records from the orderstable.


b. Write an SQL query that retrieves order number and vendor product number for all of the records from the order detailstable.


c. Write an SQL query that retrieves order number and order type for all of the records from the orderstable. The query should sort the data ascending by order number.


d. Write an SQL query that retrieves order number and vendor product number for records from the order detailstable, selecting only records that have a price that is greater than 20.00.


e. Write an SQL query that retrieves order number and customer number for records from the orderstable, selecting only records that have “fax” as the order type. The query should sort the data descending by customer number.


f. Write an SQL query that displays the total number of records in the order detailstable.


g. Write an SQL query that displays the average value of shipping charges for all of the records in the orderstable.


h. Write an SQL query that displays the order number and the total amount of the subtotal and shipping charges for each record for all of the records in the orders table.


i. Write an SQL query that displays the order number and the customer number from the orders table and the corresponding vendor product number from the order details table for all of the orders in the orderstable.


j. Write an SQL query that displays the order number and the shipping charges from the orders table and the corresponding vendor product number and quantity from the order detailstable for all of the orders that have an order type of either “fax” or “email.”

Step-by-Step Solution

Request Professional Solution

Request Solution!

We need at least 10 more requests to produce the solution.

0 / 10 have requested this problem solution

The more requests, the faster the answer.

Request! (Login Required)


All students who have requested the solution will be notified once they are available.
Add your Solution
Textbook Solutions and Answers Search