SET NOCOUNT ON
USE SqlCredit19
GO
-- You're judging the annual Giant Pumpkin/Squash/Watermelon contest, and you give prizes by weight.
-- Note: I got these real results from http://www.backyardgardener.com/wcgp/oconomowoc.html but changed the third-place pumpkin entry's weight to cause a tie.
IF EXISTS (SELECT * FROM sys.tables WHERE [name] = 'ContestResults')
    DROP TABLE dbo.ContestResults

CREATE TABLE dbo.ContestResults (
    ColID       int             NOT NULL    IDENTITY,
    Category    varchar(10)     NOT NULL,
    Weight      int             NOT NULL,
    Entrant     nvarchar(20)    NOT NULL,

    CONSTRAINT PK_ContestResults PRIMARY KEY CLUSTERED (ColID)
)

INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    716, 'Chad Johnson')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    679, 'George Kopsell')
--INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    486, 'Dan Gardner')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    679, 'Dan Gardner')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    481, 'John Suydam')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    452, 'Mark Bardin')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    442, 'Bill Kallas')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    428, 'Theresa Helmer')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    426, 'Terry Helmer')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    346, 'Gary Spiel')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    331, 'Kevin Rabell')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    289, 'Jan Spiel')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    247, 'Harvey Zale')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Pumpkin',    229, 'Harvey Zale')

INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Squash',     462, 'Dan Gardner')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Squash',     462, 'Harvey Zale')          -- I made up this entry
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Squash',     435, 'Terry Helmer')         -- I made up this entry
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Squash',     405, 'Gary Spiel')           -- I made up this entry

INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 146, 'Mark Bardin')
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 139, 'Christine Daaé')       -- I made up this entry
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 139, 'Carlotta Giudicelli')  -- I made up this entry
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 132, 'Ubaldo Piangi')        -- I made up this entry
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 132, 'Meg Giry')             -- I made up this entry
INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 129, 'Joseph Buquet')        -- I made up this entry
--INSERT INTO dbo.ContestResults (Category, Weight, Entrant) VALUES ('Watermelon', 129, 'Don Attilio')          -- I made up this entry

--CREATE NONCLUSTERED INDEX IX_ContestResults_Category_Weight ON dbo.ContestResults (
--	Category,
--	Weight
--)

SELECT Category, COUNT(*) AS Ct
FROM dbo.ContestResults
GROUP BY Category
ORDER BY Category

SELECT *
FROM dbo.ContestResults
ORDER BY ColID
GO