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;
Public Last updated: 2020-01-12 07:09:26 PM