HINT - 1st 3 ques prob use subqueries and the last 1 uses set operator

1. Display product Id, name, and list Price for products that were purchased in orders handled by salesman Marshall and with list price greater than all average list prices per each category. Sort the output by Id ascending.

ProdId Product Name LPrice

---------- -------------------- --------

2 Intel Xeon E5-2697 V 2554.99

45 Intel Xeon E5-2685 V 2501.69

2. Display customer Id, name, and total number of orders, for orders handled by salesman Marshall, but only if customer name begins on General or ends on Electric. Exclude customers who placed a single order, but include customers without orders as well. Sort the result based on the total number of orders descending and then by name ascending.

Do not use LIKE operator and do not join 3 tables.

CUSTOMER_ID Customer Name # of Orders

----------- -------------------- ------------

47 General Mills 3

9 Emerson Electric 2

185 General Dynamics 0

3. Display product Id, name, and list Price for products that their list price is more than any highest product standard cost per warehouse outside Americas regions.

(You need to find the highest standard cost for each warehouse that is located outside the Americas regions. Then you need to return all products that their list price is higher than any highest standard cost of those warehouses.)

Sort the result according to list price.

Product ID Product Name List Price

---------- -------------------- ------------

50 Intel SSDPECME040T40 $8,867.99

4. Display product Id, name, and list Price for the most expensive product, then the cheapest product and also for the product with the price closest to the average product price (rounded to the nearer ten). For the third row exclude products with name that starts on Intel. Here is the exact output.

Product ID Product Name Price

---------- -------------------- -----------

94 Western Digital WD25 $15.55

50 Intel SSDPECME040T40 $8,867.99

183 Asus GTX780TI-3GD5 $899.99

Tables (Fitered) CONTACTS COUNTRIES CUSTOMERS CUSTOMER ID NAME ADDRESS WEBSITE CREDIT LIMIT EMPLOYEES INVENTORIES PRODUCT_ID WAREHOUSE ID QUANTITY LOCATIONS LOCATION JD 1 ADDRESS POSTAL_CODE CITY STATE COUNTRY_ID ORDER ITEMS ORDER_ID ITEMID PRODUCT_ID 1 QUANTITY UNIT_PRICE ORDERS ORDER_ID 1 CUSTOMER_ID STATUS SALESMAN_D ORDER DATE DE PRODUCT_CATEGORIES CATEGORY_JD CATEGORY_NAME PRODUCTS PRODUCT_ID PRODUCT_NAME DESCRIPTION STANDARD_COST LIST_PRICE CATEGORY_ID REGIONS WAREHOUSES WAREHOUSE_JD 1 WAREHOUSE_NAME LOCATION_JD Tables (Fitered) CONTACTS COUNTRIES CUSTOMERS CUSTOMER ID NAME ADDRESS WEBSITE CREDIT LIMIT EMPLOYEES INVENTORIES PRODUCT_ID WAREHOUSE ID QUANTITY LOCATIONS LOCATION JD 1 ADDRESS POSTAL_CODE CITY STATE COUNTRY_ID ORDER ITEMS ORDER_ID ITEMID PRODUCT_ID 1 QUANTITY UNIT_PRICE ORDERS ORDER_ID 1 CUSTOMER_ID STATUS SALESMAN_D ORDER DATE DE PRODUCT_CATEGORIES CATEGORY_JD CATEGORY_NAME PRODUCTS PRODUCT_ID PRODUCT_NAME DESCRIPTION STANDARD_COST LIST_PRICE CATEGORY_ID REGIONS WAREHOUSES WAREHOUSE_JD 1 WAREHOUSE_NAME LOCATION_JD