Click here to Skip to main content
15,169,085 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi how can i bring out the sales of all cakes

SQL
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
;

--deep fried
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');

--diabetic
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');

--cakes
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');

--crakers
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
Posted
Updated 17-Nov-21 7:31am

You're half way there with
SQL
SELECT NameProduct , CategoryName
FROM Confectionery, ConfectioneryCategory
WHERE CategoryName LIKE 'diabetic%'
AND Confectionery.Category=ConfectioneryCategory.IDConCat
change "diabetic" to "cakes" and then join to the sales table.

That is a very old-fashioned way to do table joins. You should use explicit join definitions instead. In the example below I'm also going to use Table Aliases[^] to avoid having to type out the long table names each time.
SQL
SELECT c.NameProduct , cc.CategoryName,s.*
FROM Confectionery c 
INNER JOIN ConfectioneryCategory cc on c.Category = cc.IDConCat
INNER JOIN Sales s on s.IDProduct = c.IDConfect
WHERE CategoryName LIKE 'cake%';
Because I am using INNER joins I only get results for Black Forest cake and Oreo cake - because those are the only ones that have been sold. But if I wanted to get all cakes, with any sales I would use
SQL
SELECT c.NameProduct , cc.CategoryName,s.*
FROM Confectionery c 
INNER JOIN ConfectioneryCategory cc on c.Category = cc.IDConCat
LEFT OUTER JOIN Sales s on s.IDProduct = c.IDConfect
WHERE CategoryName LIKE 'cake%'
That's not something you can do with the way you are currently joining tables.

Hint: The next exercise is probably going to be "include the name of the chef". Just follow the same pattern to join to the PastryChef table
   
If you mean a summary by category, have a look here: SQL GROUP BY Statement[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900