comments

-- DB lab examination -- Point 1 USE d_breazu_store; SHOW TABLES; DROP TABLE IF EXISTS comments; CREATE TABLE IF NOT EXISTS comments( id_comm INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, comm TEXT, user_id INT UNSIGNED NOT NULL, product_ID INT UNSIGNED NOT NULL, DT DATETIME, CONSTRAINT fk_user_comm FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_product_comm FOREIGN KE8Y (product_ID) REFERENCES products(id) ); -- Point 2 DROP TRIGGER adding_comm; DELIMITER // CREATE TRIGGER adding_comm BEFORE INSERT ON comments FOR EACH ROW BEGIN IF (LENGTH(new.comm) <10) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'size less than ten!'; END IF; END // DELIMITER ; INSERT INTO comments (id_comm, comm, user_id, product_ID,DT) VALUES(NULL, 'This is overstated' ,1 , 1,now()); -- out of interval INSERT INTO comments (id_comm, comm, user_id, product_ID,DT) VALUES(NULL, 'Great price-quality report' ,1 , 1,now()); INSERT INTO comments (id_comm, comm, user_id, product_ID,DT) VALUES(NULL, 'Five stars!' ,2 , 1,now()); -- Point 3 DROP VIEW product_comments; CREATE VIEW product_comments AS SELECT CONCAT(u.first_name, ' ', u.last_name) as full_name, p.name AS product_name, c.comm AS comm, c.DT AS `TIME`FROM users AS u JOIN comments AS c ON u.id=c.id_comm JOIN products AS p on c.product_ID=p.id; SELECT * FROM product_comments; -- Point 4 SELECT product_ID ,p.name, COUNT(product_ID) as number_comm FROM comments AS c JOIN products AS p on c.product_ID=p.id;