The company wants to see the total sale amount per sales person (salesman) for all orders. Assume that some online orders do not have any sales person involved. Display the salesman full name and the total sale amount for that employee. 

Include also the Total Sale amount for all Orders without involvement of a salesman.

Exclude sales persons whose first name end on y (like Lily or Daisy).

Include only those sales persons who were responsible for more than 4 million dollars in sale.

Sort the result so that bigger sales are shown first.

Note; Do NOT use LIKE operator.

Here is shown the exact Output with the first TWO rows. Your code must accomplish the Exact look of the Output.

Emp Name                  Total Sale

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

,                           $18,245,463.50

Gomez, Freya                 $8,081,332.30

 

1. Display number of orders for customers with name starting on B or L, but only if their total number of orders is less than 2.  Include the customers with no orders in your report as well.

The report should show only customers who contain l in their name (if their name starts on B) and also customers who contain b in their name (if their name starts on L). Exclude Banks from your report (like Bank of New York Mellon).

Sort the result by the value of total orders ascending, followed by name ascending.

Note; Do NOT use LIKE operator.

Here is shown the exact Output with the first TWO rows. Your code must accomplish the Exact look of the Output.

 

        CustId  Name                           Total Orders

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

       116 Baxter International                      0

       289 BlackRock                                 0

 

1.  
   1. Display customer number, customer name, and the order id and the order date of all orders placed by contact persons from Area Phone Code 319 (Eastern Iowa in US). Be careful to avoid phone numbers having these digits somewhere else (like +1 212 319 4567). Show also the total number of items ordered, the total amount of each customer's order and last name of the contact person.
   2. Exclude Orders with the Total Amount exceeding 800 thousand dollars
   3. Exclude orders placed by Contact person Norris.
   4. Sort the result from the highest to lowest total order amount.

Note: use Multiple Tables JOIN method with USING clause

Here is shown the exact Output with the FIRST row only. Your code must accomplish the Exact look of the Output.

 

Cust# Customer Name    Order# Order Date Total# Total Amount  LName       

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

   42 Becton Dickinson     25 24-AUG-16    700 $484,279.39   Jarvis

       

1. Display Warehouse Id, warehouse name, product category Id, product category name, and the lowest product list price for this combination.

• In your result, include the rows that the lowest list price is less then $50.
• Also, include the rows that the lowest cost is more than $200.
• Observe only warehouses that are located in Countries that start on C
• Sort the output according to Warehouse Id and then product category Id

Here is shown the exact Output with the first TWO rows. Your code must accomplish the Exact look of the Output.

Wrhs# Warehouse Name Category# Category Name  Lowest Price

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

    5 Toronto                 1 CPU              $554.99

    5 Toronto                 2 Video Card       $739.99

1. Display product Id, category Id, product Name, and list Price for products that were purchased in orders handled by salesmen whose last name begins on E or F, but only for product category that starts on Video. Sort the output by product Id ascending.

Note;  You must NOT use JOIN method, just use SubQueries. Also you must use LIKE operator (not SUBSTR or INSTR).

Here is shown the exact Output with the first THREE rows. Your code must accomplish the Exact look of the Output.

      ProdId   Category#  Product Name            Lprice

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

         3           2 Corsair CB-9060011-W     799.99

         5           2 PNY VCQK6000-PB         2290.79

         6           2 Zotac ZT-P10810A-10P     849.99

1. Variation of Q7

Display product Id, product Name, and list Price for products that were purchased in orders handled by salesmen whose last name begins on E or F and who were hired in December of any year, but only for product category that starts on Video. Include only products with list Price smaller than Any Average Price per product Category. Sort the output by product Id ascending.

Note;  You must NOT use JOIN method, just SubQueries.  Also,

 you must NOT use LIKE operator.

Here is shown the exact Output with the first TWO rows. Your code must accomplish the Exact look of the Output.

 

    ProdId Product name             Lprice

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

        48 AMD FirePro S7000        1218.5

        89 PNY VCGGTX780T3XPB-O     749.99

       

1. Display product Id, product Name, and list Price for products that their list price is less than Any lowest product standard cost per warehouse inside regions of Europe or Asia.

(You need to find the lowest standard cost for each warehouse that is located inside these two regions. Then you need to return all products that their list price is less than any lowest standard cost of those warehouses.). 

Include only products with Quantity greater than Any highest quantity per warehouse (anywhere).

Sort the result according to list price descending.

Note;  You should use both methods here, Subquery first, then followed by JOIN/USING method.

Here is shown the exact Output with the first THREE rows. Your code must accomplish the Exact look of the Output.

 

   Product ID Product Name         List Price

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

       227 Gigabyte GA-X99-UD5       $305.00

       260 Crucial CT1050MX300S      $267.99

       286 Samsung MZ-V6E500         $234.00

  

0 Tables (Filtered) CONTACTS CONTACT_ID FIRST_NAME LAST_NAME EMAIL PHONE CUSTOMER_ID COUNTRIES COUNTRY_ID COUNTRY_NAME REGION_ID COUNTRY COUNTRY_ID COUNTRY_NAME REGION_ID CUSTOMERS CUSTOMER_ID NAME ADDRESS WEBSITE CREDIT_LIMIT DEPARTMENT DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID EMPLOYEE EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID