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 | 224.00 | 15.66 | |
103 | 12476 | Fax | 98.54 | 8.48 |
104 | 16822 | 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.”
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.