cZ15HrTj

1-------ratings-------
2
3DROP TABLE IF EXISTS ratings;
4CREATE TABLE IF NOT EXISTS ratings (
5id_rating INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
6rating FLOAT UNSIGNED NOT NULL,
7user_id INT UNSIGNED NOT NULL,
8product_ID INT UNSIGNED NOT NULL,
9CONSTRAINT fk_user_rating FOREIGN KEY (user_id) REFERENCES users (id),
10CONSTRAINT fk_product_rating FOREIGN KEY (product_ID) REFERENCES products (id)
11);
12
13DROP TRIGGER adding_new_rating;
14DELIMIT //
15CREATE TRIGGER adding_new_rating
16BEFORE INSERT ON ratings FOR EACH ROW
17BEGIN
18DECLARE existing_rating INT ;
19SELECT ratings.id_rating INTO existing_rating
20FROM ratings WHERE ratings.user_id = new.user_id AND ratings.product_ID = new.product_ID;
21IF (existing_rating IS NOT NULL) THEN
22SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Rating already exists' ;
23END IF ;
24IF (new.rating> 5 OR new.rating < 1 ) THEN
25SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Rating out of interval 1-5!' ;
26END IF ;
27END //
28DELIMIT;
29
30INSERT INTO ratings (id_rating, rating, user_id, product_ID) VALUES (NULL, 2 , 1 , 1 );
31INSERT INTO ratings (id_rating, rating, user_id, product_ID) VALUES (NULL, 3 , 1 , 2 );
32INSERT INTO ratings (id_rating, rating, user_id, product_ID) VALUES (NULL, 5 , 2 , 2 );
33INSERT INTO ratings (id_rating, rating, user_id, product_ID) VALUES (NULL, 5 , 3 , 2 );
34
35TRUNCATE ratings;
36SELECT * FROM users;
37SELECT * FROM products;
38SELECT * FROM ratings;
39
40
41DROP VIEW user_ratings;
42CREATE VIEW user_ratings AS SELECT
43CONCAT (u.first_name, '' , u.last_name) as full_name,
44u.email AS email,
45p.name AS product_name,
46p.id AS product_id,
47r.rating AS rating
48FROM users AS u JOIN ratings AS r ON u.id = r.id_rating
49JOIN products AS p on r.product_ID = p.id;
50
51
52SELECT * FROM user_ratings;
53
54
55SELECT AVG (r.rating) AS avg_rating, p.name AS product_name
56FROM ratings AS r JOIN products AS p ON r.product_ID = p.id
57GROUP BY r.product_ID ORDER BY avg_rating DESC ;
58----------ratings end----------------------
59
60------comments-------------
61DROP TABLE IF EXISTS comments;
62CREATE TABLE IF NOT EXISTS comments (
63id_comm INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
64comm TEXT ,
65user_id INT UNSIGNED NOT NULL,
66product_ID INT UNSIGNED NOT NULL,
67DT DATETIME ,
68CONSTRAINT fk_user_comm FOREIGN KEY (user_id) REFERENCES users (id),
69CONSTRAINT fk_product_comm FOREIGN KE8Y (product_ID) REFERENCES products (id)
70);
71
72DROP TRIGGER adding_comm;
73DELIMIT //
74CREATE TRIGGER adding_comm
75BEFORE INSERT ON comments FOR EACH ROW
76BEGIN
77IF (LENGTH ( new.comm ) < 10 ) THEN
78SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'size less than ten!' ;
79END IF ;
80END //
81DELIMIT;
82
83INSERT INTO comments (id_comm, comm, user_id, product_ID, DT) VALUES (NULL, 'fuck you ali abboud' , 1 , 1 , now ());
84INSERT INTO comments (id_comm, comm, user_id, product_ID, DT) VALUES (NULL, 'fuckupetre' , 1 , 1 , now ());
85DROP VIEW product_comments;
86CREATE VIEW product_comments AS SELECT
87CONCAT (u.first_name, '' , u.last_name) as full_name,
88p.name AS product_name,
89c.comm AS comm,
90c.DT AS `TIME` FROM users AS u JOIN comments AS c ON u.id = c.id_comm
91JOIN products AS p on c.product_ID = p.id;
92
93SELECT * FROM product_comments;
94
95SELECT product_ID, p.name, COUNT (product_ID) as no_comm FROM comments AS c JOIN products
96AS p on c.product_ID = p.id;
97
98-----comments end-------
99-----price hitory------
100DROP TABLE IF EXISTS price_history ; CREATE TABLE price_history (
101id_pri INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT ,
102ID_product INT UNSIGNED NOT NULL ,
103DT DATETIME ,
104old_pri DECIMAL UNSIGNED NOT NULL , CONSTRAINT fk_product_price FOREIGN KEY ( ID_product ) REFERENCES products ( id ) ) ; DESCRIBE products ;
105
106DROP TRIGGER up_date ;
107DELIMIT / /
108CREATE TRIGGER up_date
109BEFORE UPDATE ON products FOR EACH ROW BEGIN
110DECLARE old_price DECIMAL UNSIGNED ;
111SELECT price INTO old_price FROM products WHERE new . id = products . id ;
112IF ( NEW . Price ! = Old_price ) THEN
113INSERT INTO price_history ( ID_product , old_pri , DT ) VALUES ( new . Id , old_price , NOW ( ) ) ;
114END IF ; END / /
115DELIMIT ;
116
117SHOW TABLES ; SELECT * FROM products ; UPDATE products SET price = 19 WHERE products . id = 2 ; SELECT * FROM price_history ;
118
119DROP procedure changes ;
120DELIMIT / /
121CREATE PROCEDURE changes ( IN prices DECIMAL UNSIGNED , IN prodid INT UNSIGNED ) BEGIN
122DECLARE var INT UNSIGNED ;
123SELECT id INTO var FROM products WHERE prodid = products . id ;
124IF ( var IS NULL ) THEN
125SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'PRODUCT not found' ;
126ELSE
127UPDATE products SET price = prices WHERE products . id = prodid ;
128END IF ;
129END / /
130DELIMIT ;
131
132CALL changes ( 10 , 8 ) ;
133
134SELECT ` name ` , price , MIN ( price ) , MAX ( price ) FROM products , price_history WHERE id = id_product ;
135----price end---------