ratings

-- DB lab examination -- Point 1 USE d_breazu_store; SHOW TABLES; DROP TABLE IF EXISTS ratings; CREATE TABLE IF NOT EXISTS ratings( id_rating INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, rating FLOAT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, CONSTRAINT fk_user_rating FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_product_rating FOREIGN KEY (product_id) REFERENCES products(id) ); -- Point 2 DELIMITER // CREATE TRIGGER adding_new_rating BEFORE INSERT ON ratings FOR EACH ROW BEGIN DECLARE existing_rating INT; SELECT ratings.id_rating INTO existing_rating FROM ratings WHERE ratings.user_id = new.user_id AND ratings.product_id=new.product_id; IF (existing_rating IS NOT NULL) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Rating already exists!'; END IF; IF (new.rating > 5 OR new.rating < 1) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Rating out of interval 1-5 !'; END IF; END // DELIMITER ; INSERT INTO ratings (id_rating, rating, user_id, product_id) VALUES(NULL, 5.5 ,1 , 1); -- out of interval INSERT INTO ratings (id_rating, rating, user_id, product_id) VALUES(NULL, 2 ,1 , 1); INSERT INTO ratings (id_rating, rating, user_id, product_id) VALUES(NULL, 3 ,2 , 2); INSERT INTO ratings (id_rating, rating, user_id, product_id) VALUES(NULL, 4 ,2, 2); INSERT INTO ratings (id_rating, rating, user_id, product_id) VALUES(NULL, 5 ,3 , 2); TRUNCATE ratings; SELECT * FROM users; SELECT * FROM products; SELECT * FROM ratings; -- Point 3 DROP VIEW user_ratings; CREATE VIEW user_ratings AS SELECT CONCAT(u.first_name, ' ', u.last_name) as full_name, u.email AS email, p.name AS product_name, p.id AS product_id, r.rating AS rating FROM users AS u JOIN ratings AS r ON u.id=r.id_rating JOIN products AS p on r.product_id=p.id; SELECT * FROM user_ratings; -- Point 4 SELECT AVG(r.rating) AS average_rating , p.name AS product_name FROM ratings AS r JOIN products AS p ON r.product_id=p.id GROUP BY r.product_id ORDER BY average_rating DESC;