Reference no: EM132182790
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