query for creating view

Question

This is my code:

create DATABASE assignment

CREATE TABLE Books (
    id INTEGER PRIMARY KEY,
    category TEXT,
    price FLOAT CHECK (price>0), 
    promoted bit DEFAULT 1
);

INSERT INTO Books (id, category, price) VALUES (1, 'Dictionary', 100); 
INSERT INTO Books (id, category, price) VALUES (2, 'Dictionary', 150); 
INSERT INTO Books (id, category, price) VALUES (3, 'Science', 120); 
INSERT INTO Books (id, category, price) VALUES (4, 'Science', 190); 
INSERT INTO Books  (id, category, price) VALUES (5, 'Science', 320);

CREATE VIEW PromotionSummary AS
SELECT category, MIN(price) AS minprice , MAX(price) AS maxprice 
FROM Books
WHERE promoted
GROUP BY category;

facing this error:

Msg 4145, Level 15, State 1, Procedure PromotionSummary, Line 5 An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.

I am using azure database right now.

Answer 1

The error message indicates that you are using SQL Server, not MySQL. If so, then you need to a value on the predicate on promoted (MySQL would allow that, but not SQL Server). Presumably, you want:

CREATE VIEW PromotionSummary AS 
SELECT category, MIN(price) AS minprice , MAX(price) AS maxprice 
FROM Books 
WHERE promoted = 1 
GROUP BY category;