**I HAVE ADDED INFORMATION ABOUT THE TABLE STRUCTURE AT THE BOTTOM OF THIS POST**

I need to make two queries for the following questions:

5.By examining data of Products table without using an SQL query (whichwe will learn in a later chapter),

which suppliers supply the most different products? Write their ID and contact name. A correct answer

should contain two suppliers that meet the condition.

6. By examining data of Products table without using an SQL query (which

we will learn in a later chapter),

how many products whose unit price is below the average of all

products?

After studying summary query techniques of SQL, can you write one SELECT statement to solve Problem 5?

Standard solution of Problem 6 requires to use a subquery, which we will learn in Chapter 6. But, without a subquery, it can still be solved by using two SELECT statements, one to retrieve the average unit price and the other uses the returned average unit price to count the total number of products below the average.

Can you manage these two SELECT statements for Problem 6? (You may try to solve it again after subquerying techniques are learned in Chapter 6.)

*/

use ClearWater

--SELECT statement for Problem 5. (suppliers supply the most different

products)

--Correct output of query for Problem 5:

7 Ian Devling

12 Martin Bein

--two SELECT statements for Problem 6. (total products whose unit price is

below the average)

--Correct output of the second statement for Problem 6:

52

--The main concern of this two-SELECT approach is, if new products are

inserted or some are deleted or some

--products have their unit price updated, then the average unit price of

Products change and is no longer

--$28.8663. In these cases, the "hard coded" value of 28.8663 in the 2nd

SELECT must be changed manually.

--If a subquery is used (as will be learned in Chapter 6), we will not see

the average value hard coded

--in the statement, but a SELECT statement (which calculates the average

unit price) is embedded in another.

--So, whatever and however Products table contents are modified, the

subquery guarantees the accurate average

--is returned to the main query.

HERE ARE THE TABLES AND THEIR COLUMNS:

E dboCategories- Columns O CategorylD (PK, int, not null) CategoryName(nvarchar(15), not null) | Description (nvarchar(max), null) dboProducts- Columns ProductlD (PK, int, not null) ProductName(nvarchar(40), not null) os SupplierlD (FK, int, null) o CategorylD (FK, int, null) QuantityPerUnit (nvarchar(20), null) UnitPrice (money, null) | UnitsinStock (smallint, null) UnitsOnOrder (smallint, null) ReorderLevel (smallint, null) Discontinued (bit, not null) E EE dbo.Suppliers- Columns SupplierlD (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) E Region (nvarchar(15), null) | PostalCode (nvarchar(10), null) | Country (nvarchar(15), null) | | Phone (nvarchar(24), null) Fax (nvarchar(24), null) HomePage (nvarchar(max), null) E dboCategories- Columns O CategorylD (PK, int, not null) CategoryName(nvarchar(15), not null) | Description (nvarchar(max), null) dboProducts- Columns ProductlD (PK, int, not null) ProductName(nvarchar(40), not null) os SupplierlD (FK, int, null) o CategorylD (FK, int, null) QuantityPerUnit (nvarchar(20), null) UnitPrice (money, null) | UnitsinStock (smallint, null) UnitsOnOrder (smallint, null) ReorderLevel (smallint, null) Discontinued (bit, not null) E EE dbo.Suppliers- Columns SupplierlD (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) E Region (nvarchar(15), null) | PostalCode (nvarchar(10), null) | Country (nvarchar(15), null) | | Phone (nvarchar(24), null) Fax (nvarchar(24), null) HomePage (nvarchar(max), null)