SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE IF NOT EXISTS Establishment ( id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL, name VARCHAR(100) NOT NULL, is_chain BOOLEAN default false, chain_id BIGINT, FOREIGN KEY (chain_id) REFERENCES Establishment (id) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE IF NOT EXISTS Review ( id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL, establishment_id BIGINT NOT NULL, FOREIGN KEY (establishment_id) REFERENCES Establishment (id) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE IF NOT EXISTS Rate ( id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL, rate DOUBLE, type VARCHAR(100) NOT NULL, review_id BIGINT NOT NULL, FOREIGN KEY (review_id) REFERENCES Review (id) ON DELETE NO ACTION ON UPDATE NO ACTION ); INSERT INTO Establishment(name, is_chain, chain_id) VALUES ('Starbucks', true, null), ('Starbucks New York1', false, 1), ('Starbucks Philadelphia', false, 1), ('Starbucks New York2', false, 1), ('Starbucks Los Angeles1', false, 1), ('Starbucks Los Angeles2', false, 1), ('Masa', false, null), ('Beard Papas', false, null), ('Joanne', false, null), ('Brooklyn Fare', false, null), ('KFC', true, null), ('KFC Los Angeles', false, 11), ('KFC New York', false, 11), ('KFC Philadelphia', false, 11), ('McDonald''s', true, null), ('McDonald''s Philadelphia', false, 15), ('McDonald''s Los Angeles', false, 15), ('McDonald''s New York', false, 15); INSERT INTO Review(establishment_id) VALUES (2), (2), (3), (3), (4), (5), (7), (8), (8), (8), (9), (10), (12), (13), (14), (16), (16), (16), (17), (17), (18), (18), (18), (18); INSERT INTO Rate(rate,type,review_id) VALUES (9.2,'FOOD',1), (9.1,'SERVICE',1), (7.1,'PRICES',1), (10,'INTERIOR',1), (7.8,'AMBIENCE',1), (3.9,'FOOD',2), (5.1,'SERVICE',2), (3.5,'PRICES',2), (4.9,'INTERIOR',2), (6.9,'AMBIENCE',2), (7,'FOOD',3), (8.5,'SERVICE',3), (5.4,'PRICES',3), (6.2,'INTERIOR',3), (5.4,'AMBIENCE',3), (7.6,'FOOD',4), (6.1,'SERVICE',4), (4.3,'PRICES',4), (7.8,'INTERIOR',4), (4.6,'AMBIENCE',4), (9.6,'FOOD',5), (9,'SERVICE',5), (7.4,'PRICES',5), (8.7,'INTERIOR',5), (8.3,'AMBIENCE',5), (0,'FOOD',6), (0,'SERVICE',6), (0,'PRICES',6), (0,'INTERIOR',6), (10,'AMBIENCE',6), (2,'FOOD',7), (9.1,'SERVICE',7), (8.8,'PRICES',7), (9,'INTERIOR',7), (8.9,'AMBIENCE',7), (9.1,'FOOD',8), (9.1,'SERVICE',8), (7.3,'PRICES',8), (9,'INTERIOR',8), (8.1,'AMBIENCE',8), (8.2,'FOOD',9), (8.1,'SERVICE',9), (7.5,'PRICES',9), (5.7,'INTERIOR',9), (8.4,'AMBIENCE',9), (4.9,'FOOD',10), (5.2,'SERVICE',10), (0,'PRICES',10), (8.2,'INTERIOR',10), (3.3,'AMBIENCE',10), (6.6,'FOOD',11), (5.1,'SERVICE',11), (5.3,'PRICES',11), (5.8,'INTERIOR',11), (6.4,'AMBIENCE',11), (9.4,'FOOD',12), (1.6,'SERVICE',12), (2.4,'PRICES',12), (2.3,'INTERIOR',12), (1.7,'AMBIENCE',12), (8.7,'FOOD',13), (8.4,'SERVICE',13), (7.1,'PRICES',13), (7.8,'INTERIOR',13), (6.8,'AMBIENCE',13), (5.3,'FOOD',14), (5.3,'SERVICE',14), (3.9,'PRICES',14), (7.4,'INTERIOR',14), (6.2,'AMBIENCE',14), (6.1,'FOOD',15), (5.6,'SERVICE',15), (6.3,'PRICES',15), (4,'INTERIOR',15), (5.3,'AMBIENCE',15), (10,'FOOD',16), (6,'SERVICE',16), (6.8,'PRICES',16), (9.7,'INTERIOR',16), (5,'AMBIENCE',16), (9.1,'FOOD',17), (5.1,'SERVICE',17), (8.2,'PRICES',17), (10,'INTERIOR',17), (8.2,'AMBIENCE',17), (8.8,'FOOD',18), (8.3,'SERVICE',18), (7.7,'PRICES',18), (7.8,'INTERIOR',18), (6.5,'AMBIENCE',18), (10,'FOOD',19), (10,'SERVICE',19), (10,'PRICES',19), (10,'INTERIOR',19), (10,'AMBIENCE',19), (9.4,'FOOD',20), (10,'SERVICE',20), (6.3,'PRICES',20), (7.8,'INTERIOR',20), (9.3,'AMBIENCE',20), (6.9,'FOOD',21), (6.2,'SERVICE',21), (4.2,'PRICES',21), (4.8,'INTERIOR',21), (9,'AMBIENCE',21), (0,'FOOD',22), (0,'SERVICE',22), (0,'PRICES',22), (0,'INTERIOR',22), (0,'AMBIENCE',22), (9.3,'FOOD',23), (7.9,'SERVICE',23), (6.6,'PRICES',23), (6.9,'INTERIOR',23), (6.4,'AMBIENCE',23), (4.4,'FOOD',24), (4.9,'SERVICE',24), (7.1,'PRICES',24), (3.4,'INTERIOR',24), (3.5,'AMBIENCE',24); SELECT name, ROUND(AVG(establishment_avg_rate), 2) avg_rate FROM ( SELECT chains.name, ROUND(AVG(COALESCE(rate, 0)), 2) establishment_avg_rate FROM Establishment LEFT JOIN Review ON Review.establishment_id = Establishment.id LEFT JOIN Rate ON Rate.review_id = Review.id JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id) WHERE AND not Establishment.is_chain GROUP BY chains.name, Establishment.name ) establishment_avg GROUP BY name ORDER BY avg_rate DESC; SELECT chains.name, Establishment.name, ROUND(AVG(COALESCE(rate, 0)), 2) establishment_avg_rate FROM Establishment LEFT JOIN Review ON Review.establishment_id = Establishment.id LEFT JOIN Rate ON Rate.review_id = Review.id JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id) WHERE chains.name = 'McDonald\'s' -- 'Starbucks' AND not Establishment.is_chain -- , chain_id GROUP BY chains.name, Establishment.name;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear