solution

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 details table 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.

a. Write an SQL query that retrieves all of the fields foreach record and all of the records from the orders table.

b. Write an SQL query that retrieves order number andvendor product number for all of the records from theorder details table.

c. Write an SQL query that retrieves order number andorder type for all of the records from the orders table. Thequery should sort the data ascending by order number.

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

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

f. Write an SQL query that displays the total number ofrecords in the order details table.

g. Write an SQL query that displays the average value ofshipping charges for all of the records in the orders table.

 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 orders table.

 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 details table for all of the orders that have an order type of either “fax” or “email.”