using sql for mysql workbench

1. List products for Motorcycle, Ship, and Train product lines that have quantity in stock.

(Hint: You can do this with a combination of AND/OR constructs. If you do, be sure to use parenthesis to group the operations. Otherwise you can do this with a single AND and an IN comparison operator. You can look up the IN operator in the MySQL reference manual.)

2. List product line, product code, product name, and total ordered quantity sorted by total ordered quantity in decreasing order. Include products with no orders (return 0 total quantity ordered for products with no orders).

3. List the names of customers and how many distinct products they have ordered where that customer has ordered 40 or more distinct products.

(Hint: Make sure you get only distinct products.)

4. List the names of all customers who have placed an order and the total dollar amount ordered by that customer.

(Hint: To get the dollar amount for an order, you will need to multiply the order quantity by the price each in the order detail table.)

5. List the names and customer numbers of all customers and the total dollar amount paid by that customer. If the customer hasnt made any payments, list 0 for the total amount paid for that customer.

Extra Credit

6. List the names of all customers who have placed an order, the total dollar amount ordered for all orders for that customer, the dollar amount paid for all orders for that customer and the outstanding balance (amount that has not yet been paid) for each customer.

(Hint: If you try to mix the inner and outer joins for this query in the same FROM clause, you will probably get a meaningless result. Instead, use the queries from questions 9 and 10. Embed one of them as a subquery in the FROM clause of the other while adding the extra items being computed in the embedded query in the appropriate places in the primary query.)

(Note: Make sure your entire query shows in the query pane and at least two rows of the results appear in the results grid. You may need to resize panes to accomplish this.)

off eCode VARCHAR(10) city: VARCHARS0) phone: VARCHAR(S0) addinessline l: VARCHAR(SO) orderlumber INTEGER orderNumber: INTEGER productCode: VARCHARC5) 9 orderOute DATETIME MALLINT comments: TEXT customer% mber INTEGER country: VARCHARSO) postalCode VARCHARIS) sales Repltmplyeeumber: INTEGER productcode: VARCHAR(25) productName: VARCHAR70 VARCHAR(10) 9 extension: VARCHARO) produenendor. VARCHAR(50) ARCHAR(SO) quastylaStock INTEGER 0 buy Price: DOUBLE off eCode VARCHAR(10) city: VARCHARS0) phone: VARCHAR(S0) addinessline l: VARCHAR(SO) orderlumber INTEGER orderNumber: INTEGER productCode: VARCHARC5) 9 orderOute DATETIME MALLINT comments: TEXT customer% mber INTEGER country: VARCHARSO) postalCode VARCHARIS) sales Repltmplyeeumber: INTEGER productcode: VARCHAR(25) productName: VARCHAR70 VARCHAR(10) 9 extension: VARCHARO) produenendor. VARCHAR(50) ARCHAR(SO) quastylaStock INTEGER 0 buy Price: DOUBLE