CREATE TABLE ITEM (
    ITEM_iD CHAR(4) PRIMARY KEY,
    DESCRIPTION VARCHAR (50),
    ON_HAND INT (2),
    CATEGORY CHAR (3),
    LOCATION CHAR(1),
    PRICE DECIMAL (5,2)
);
INSERT INTO ITEM
    VALUES
    ('AD72', 'Dog Feeding Station', 12, 'DOG','B', 79.99),
    ('BC33', 'Feathers Bird Cage (12x24x18)', 10, 'BRD','B', 79.99),
    ('CA75', 'Enclosed Cat Litter Station', 15, 'CAT','C',39.99),
    ('DT12', 'Dog Toy Gift set', 27, 'DOG','B', 39.99),
    ('FM23', 'Fly Mask with Ears', 41, 'HOR','C', 24.95),
    ('FS39', 'Folding Saddle Stand', 12, 'HOR','C', 39.99),
    ('FS42', 'Aquarium (55 Gallon)', 5, 'FSH','A', 124.99),
    ('KH81', 'Wild Bird Food (25 lb)', 24, 'BRD','C', 19.99),
    ('LD14', 'Locking Small Dog Door', 14, 'DOG','A', 49.99),
    ('LP73', 'Large Pet Carrier', 23, 'DOG','B', 59.99),
    ('PF19', 'Pump & Filter Kit', 5, 'FSH','A', 74.99),
    ('QB92', 'Quilted Stable Blanket', 32, 'HOR','C', 119.99),
    ('SF91', 'Small Pet Carrier', 18, 'CAT','B', 39.99),
    ('UF39', 'Underground Fence System', 7, 'DOG','A', 199.99),
    ('WB49', 'Insulated Water Bucket', 34, 'HOR','C',79.99);

-- List the item ID, description, and on-hand value 
--of each item whose number of units on hand is more than 
--the average number of units on hand for all items. (Hint: Use a subquery.)

SELECT ITEM_iD, DESCRIPTION, (ON_HAND*PRICE) AS ON_HAND_VALUE FROM ITEM
WHERE ON_HAND>( SELECT AVG (ON_HAND) FROM ITEM);