USE ClearWater;

/* Q1.

In the ClearWater database, each category has a different number of

products. Some have more and some have less. The two statements below

can find categories that have the largest and the second largest total

number of products. Their output returns category 3, 1, 2, and 8 because

category 3 has a total of 13 products and category 1, 2, and 8 each

has 12 products. All other categories have less than 12 products.

select *

from Categories

where [CategoryID] = ANY (select top 2 with ties CategoryID

from Products

group by CategoryID

order by count(*) desc);

--> Note (1) the above '= ANY' can be replaced with the 'IN' operator.

--> (2) 'with ties' must be included.

select g.*

from Categories g join Products p

on g.CategoryID = p.CategoryID

group by g.CategoryID, g.CategoryName, g.Description

having count(*) in (select top 2 count(*)

--'with ties' is not necessary because IN is used.

from Products

group by CategoryID

order by count(*) desc)

order by count(*) desc;

Both above statements contain a subquery but each returns a different

kind of data and the main query uses a different approach and condition

to reach the final answer.

After study these two examples, it's your turn to write a SELECT statement

with a subquery to find suppliers who supply only two different categories

of products. This statement will display their SupplierID, CompanyName,

CategoryID and CategoryName of the two categories. Do not include duplicate

or identical rows in your output.

Hint 1: use the similar pattern of the examples

Hint 2: find first each supplier and its total different categories

then add a condition to limit to two different categories

Hint 3: correct output should have four suppliers.

*/

/* Q2.

Make use of part of your statement of Q1 to write another SELECT

statement to get the total number of suppliers who supply only one

category of products. Display this number in an outut column called

Total_suppliers_with_single_category.

Hint: Make Q1 as a subquery in the FROM clause of the main query (i.e.,

derived table), or make it a subquery in the WHERE clause.

*/

/* Q3.

How many products whose UnitsInStock has a value greater than

the total UnitsInStock values of products whose ProductID is

between 26 and 34 and have a unit price above $40? Write a SELECT

statement with a subquery to answer this question.

*/

/* Q4.

Re-write the SELECT statement of Q3 so it returns the same but

contains no subqueries at all.

Hint: turn the subquery of Q3 into a derived table and do table join.

*/

/* Q5.

Based on the result of Q3 or Q4 of your selection and modify its

SELECT statement to find the supplier who supplies the product

counted by Q3 (or Q4). Display SupplierID, Country, and Phone of

each supplier found. Be sure no supplier is displayed twice.

Hint: Either Q3 or Q4 is modified, there are two approaches and you

may choose either one--one uses subquery and the other uses

join. If tables are extremely large, the latter would run faster.

*/

---------------------END---------------------