Hi how can i bring out the sales of all cakes
CREATE DATABASE Workshop;
USE Workshop;
CREATE TABLE Confectionery (
IDConfect INT IDENTITY NOT NULL PRIMARY KEY,
NameProduct NVARCHAR(50) NOT NULL,
Category INT NOT NULL,
Quantity SMALLINT NOT NULL,
Price DECIMAL(20)
);
CREATE TABLE ConfectioneryCategory (
IDConCat INT IDENTITY NOT NULL PRIMARY KEY,
CategoryName NVARCHAR(50) NOT NULL,
);
CREATE TABLE Sales (
SaleID INT IDENTITY NOT NULL,
Quantity INT NOT NULL,
DateOfSale DATE,
IDPastryChef INT NOT NULL,
IDProduct INT NOT NULL,
PRIMARY KEY(SaleID, IDProduct )
);
CREATE TABLE PastryChef (
IDPC INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
ManifacturedProduct NVARCHAR(50)
);
ALTER TABLE Sales
ADD CONSTRAINT FK_Confectionery_Sales FOREIGN KEY (IDProduct)
REFERENCES Confectionery (IDConfect)
ON DELETE CASCADE
ON UPDATE CASCADE
;
ALTER TABLE Confectionery
ADD CONSTRAINT FK_Confectionery_ConfectioneryCategory FOREIGN KEY (Category)
REFERENCES ConfectioneryCategory (IDConCat)
ON DELETE CASCADE
ON UPDATE CASCADE
;
ALTER TABLE Sales
ADD CONSTRAINT FK_Sales_PastryChef FOREIGN KEY (IDPastryChef)
REFERENCES PastryChef (IDPC)
ON DELETE CASCADE
ON UPDATE CASCADE
;
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Donuts', 1, 50,'0.99');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Luqaimat', 1, 30 ,'4.12');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Tulumba', 1, 28 ,'1.25');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Julbia', 1, 15 ,'1.15');
INSERT INTO ConfectioneryCategory(CategoryName)
VALUES('deep fried'),('diabetic'),('cakes'),('crakers');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES(' Peanut Butter Cookies', 2, 80,'2.99');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Chocolate Keto Fat Bombs', 2, 100 ,'3.20');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES(' Yogurt Ice Cream', 2, 7 ,'2.50');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Pumpkin and raspberry muffins', 2, 9 ,'2.50');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Garash cake', 3, 5 ,'15.00');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Black Forest cake', 3, 4 ,'20.00');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Red velvet', 3, 12, '1.60');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Oreo cake', 3, 12, '1.80');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Creem Crakers', 4, 35 ,'1.00');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Salts', 4, 64 ,'0.30');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Brecel', 4, 14 ,'1.35');
INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Olive crakers', 4 , 12 ,'0.99');
INSERT INTO PastryChef(FirstName,LastName,ManifacturedProduct)
VALUES('Kiril', 'Petrov', 'Brecel');
INSERT INTO PastryChef(FirstName,LastName,ManifacturedProduct)
VALUES('Alexander', 'Dimitrov', 'Red velvet');
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(15, '2021-11-16', 1, 3);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(24, '2021-11-16', 1,6);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(4, '2021-11-12', 2,8);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(0, '2021-11-12', 2,10);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(10, '2021-10-05', 2,12);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(16, '2021-10-05', 1,13);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(4, '2021-11-27', 2,16);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(9, '2021-11-27', 1,18);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(6, '2021-09-14', 1,17);
INSERT INTO Sales(Quantity,DateOfSale,IDPastryChef,IDProduct)
VALUES(12, '2021-09-14', 2,19);
SELECT NameProduct , CategoryName
FROM Confectionery, ConfectioneryCategory
WHERE CategoryName LIKE 'diabetic%'
AND Confectionery.Category=ConfectioneryCategory.IDConCat
SELECT CategoryName, Quantity
FROM ConfectioneryCategory, Sales
WHERE IDConCat = 3
AND ConfectioneryCategory.IDConCat=Sales.SaleID
What I have tried:
SELECT CategoryName, Quantity
FROM ConfectioneryCategory, Sales
WHERE IDConCat = 3
AND ConfectioneryCategory.IDConCat=Sales.SaleID