/* 6. By examining data of Products table without writing and executing an SQL query (which we will learn in a later chapter), how many products with a unit price below the average (of all products)? Hint: The average unit price is the sum of unit price of all products divided by the total number of products.

Now, after studying summary query techniques of SQL in Chapter 6, can you write one SELECT statement to solve Problem 6 printed above? */

Standard solution of SQl for Problem 6 requires to use a subquery, which we will learn in Chapter 6. But, without using a subquery, you can still solve it by using two SELECT statements, one to retrieve the average unit price and the other simply hard codes the average unit price in the statement to count the total number of products below that average.

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

--Your two SELECT statements for Problem 6 (i.e., total number of products with a unit price below average)

--statement 1: (find the the average unit price of all products)

--statement 2: (place the average unit price found above somewhere in this statement)

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

52

o o dbo.Categories o Columns CategoryID (PK, int, not null) CategoryName (nvarchar(15), not null) Description (nvarchar(max), null) Keys PK_Categories # Constraints # Triggers + Indexes # Statistics dbo.Products o Columns ProductID (PK, int, not null) ProductName (nvarchar(40), not null) O 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) o Keys PK_Products Om FK_Products Categories - FK_Products Suppliers # Constraints # Triggers + Indexes Statistics dbo.Suppliers o Columns SupplierlD (PK, int, not null) CompanyName (nvarchar(40), not null) ContactName (nvarchar(30), null) Contact Title (nvarchar(30), null) Address (nvarchar(60), null) City (nvarchar(15), null) Region (nvarchar(15), null) PostalCode (nvarchar(10), null) Country (nvarchar(15), null) Phone (nvarchar(24), null) Fax (nvarchar(24), null) HomePage (nvarchar(max), null) Keys PK_Suppliers O