*SQL Queries*

This is what I have for Query 1, which displays SupplierID and Avg UnitPrice where Avg_UnitPrice is the average unit price of products of a supplier. Output is sorted by SupplierID:

SELECT distinct SupplierID, AVG(UnitPrice) FROM Products GROUP BY SupplierID ORDER BY SupplierID;

I have verified that my above query executes and populates the correct values. The problem I have with the following parts is that SQL doesn't like when you use an aggregate or alias in the WHERE clause. For instance, two queries I tried for Query 2 are as follows:

SELECT distinct SupplierID, AVG(UnitPrice) average FROM Products WHERE AVG(UnitPrice) BETWEEN 15 and 22 order by SupplierId;

and

SELECT distinct SupplierID, AVG(UnitPrice) as Av_UnitPrice FROM Products WHERE Av_UnitPrice BETWEEN 15 and 22 order by SupplierId;

Neither solutions work for Query 2 for the reasons I mentioned above. Please help!

--Query 2. Copy the statement of Query 1 and modify it to display SupplierID and Avg_UnitPrice of suppliers whose average unit price is between $15 and $22. Sort output by SupplierID. Hint: 8 suppliers

--Query 3. Copy the statement of Query 2 and modify it to display SupplierID and Avg_UnitPrice of suppliers whose average unit price is ranked #1 and #2 (i.e., the highest two averages). Hint: 3 suppliers

--Query 4. Copy the statement of Query 3 and modify it to display the same output plus two more columns: CompanyName and Phone of each included supplier. Hint: 3 suppliers, join Suppliers table

Snapshot of table/column relationships:

O dbo.Categories o Columns CategoryID (PK, int, not null) CategoryName (nvarchar(15), not null) Description (nvarchar(max), null) Keys Constraints # Triggers Indexes Statistics dbo.Products Columns ProductID (PK, int, not null) ProductName (nvarchar(40), not null) - SupplierlD (FK, int, null) - CategoryID (FK, int, null) QuantityPerUnit (nvarchar(20), null) UnitPrice (money, null) UnitsInStock (smallint, null) UnitsOnOrder (smallint, null) ReorderLevel (smallint, null) Discontinued (bit, not null) Keys + Constraints # Triggers Indexes Statistics o dbo.Suppliers o Columns Supplier D (PK, int, not null) CompanyName (nvarchar(40), not null) ContactName (nvarchar(30), null) ContactTitle (nvarchar(30), null) Address (nvarchar(60), null) City (nvarchar(15), null) Region (nvarchar(15), null) Postal Code (nvarchar(10), null) Country (nvarchar(15), null) Phone (nvarchar(24), null) Fax (nvarchar(24), null) HomePage (nvarchar(max), null)