SQL

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?

--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.

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)