Write a T-SQL script in the given space to solve each given problem.

Problem 1.

The following DML statement returns the sum of unit price of products that have more than 110units in stock. Using the original ClearWater database, the output should be $139.15. It's so simple and short a statement but also powerful.

SELECT SUM(UnitPrice) AS Sum_UnitPrice

FROM ClearWater.dbo.Products

WHERE UnitsInStock > 110;

Let's try to retrieve the same information but without using the built-in SUM()aggregate function. Instead, you are going to write a Transact-SQL script in the space below between lines of 'use' and 'go' to generate the same output value as the above DML statement. You may use variables, IF statement, WHILE statement, PRINT and even SELECT statements as long as the SUM() is not used. The only constants allowed in the script are 0, 1, and 110. No other values can appear or hard coded in the script. Let the output be printed in a single line like 'Sum_UnitPrice = .....'. To test your script, be sure you select all lines, including 'use Clearwater' and 'go' and execute them as one batch. Otherwise, it won't work. Several hints are provided below to help your script design, but, you don't have to use them, some or all, as long as your script delivers the desired output.

Hint 1: think and write down a step-by-step logical but manual procedure to simulate SUM() before coding your solution

Hint 2: retrieve one product a time (see Hint 4) and, if its UnitsInStock is above 110, add its UnitPrice to the sum.

Hint 3: use a loop by taking advantage of the continuous values of ProductID from 1. to 77 (i.e., use ProductID to control the loop index)

Hint 4: use a standard SELECT to get the total number of products

Hint 5: use a standard SELECT to retrieve each specific product inside a loop

Hint 6: not including the lines of 'use' and 'go', a sample solution is done with 12 lines to solve this problem, including lines like 'begin' and 'end'.

Your solution could be even shorter or longer.

*/use Clearwater;go/*

Problem 2.

Without using 'GROUP BY' clause in any SELECT statements, write a Transact-SQL script in the space below between lines of 'use' and 'go' to generate the same two-column output of 29 rows (one for each SupplierID in Products based on the original ClearWater database) as the DML statement below.

SELECT SupplierID, COUNT(*) AS TotalProducts FROM ClearWater.dbo.Products GROUP BY SupplierID;

Since data of the Products table can be changed any time, your script should not assume the table contains 29 SupplierIDs. That is, the number 29 cannot appear in your script. The only constant value allowed here is 1.

Hint 1: same as in Problem 1, think and write down a step-by-step logical but manual procedure to simulate GROUP BY before coding your solution

Hint 2: think and design a step-by-step logical procedure to simulate before coding

Hint 3: since the output contains rows and columns, you could use a table Variable or a temporary table, and use INSERT and UPDATE to manipulate it

Hint 4: use WHILE, IF-ELSE like in Problem 1

Hint 5: use one or two standard SELECT statements to get the total number of products and total number of different SupplierIDs in Products table.

Hint 6: this could possibly be solved by 14~16 lines, not including 'use' and 'go'.*/use Clearwater;go