1. In the original Clear Water database, each category has a total number of products. Some categories have more products while some have less. Which categories have one of the two largest total number of products? By writing and executing a SELECT statement with a subquery, you should be able to get four categories and their categoryID are 3, 1,__________, and ___________. (each is an integer)

2. In the original Clear Water database, each product has a UnitsInStock value. How many products whose UnitsInStock value is greater than the total UnitsInStock values of products with ProductID from 7 to 11? You can write a SELECT statement with a subquery to answer this query and the total should be ____. (an integer, no partial credit)

3. In solving the problem of Question 2, the SELECT clause of the subquery should contain one item that must be an aggregate on a column. This item in the SELECT clause should be _____.

4. 

In solving the problem of Question 2, the entire statement can be re-written such that the subquery becomes a "derived table" and joined with Products table as shown below.

select count(*)
from Products p join (select sum(UnitsInStock) as                                                              sum_UnitsInStock
                               from Products
                               where ProductID between 7 and 11) as tmp
      on ________;

Enter an expression in the blank so the query can be executed for desired output.

5. 

The following statement returns suppliers of ClearWater database that are located in the same country as a supplier whose company name is Nord-Ost-Fisch Handelsgesellschaft mbH.

select sp.*from Suppliers spwhere EXISTS (select * from Suppliers                        where CompanyName = 'Nord-Ost-Fisch Handelsgesellschaft mbH'                             and SupplierID <> sp.SupplierID                             and Country = sp.Country);

If we re-write the above statement with a non-correlated subquery but uses no EXISTS, it would become the following:

select *from Supplierswhere CompanyName <> 'Nord-Ost-Fisch Handelsgesellschaft mbH'      and           
 = (select           
                 from Suppliers                 where CompanyName = 'Nord-Ost-Fisch Handelsgesellschaft mbH');

6.  In Question 5, both statements use a subquery. Re-write either one such that the new statement returns the same output but uses a join and no subqueries.