CREATE OR REPLACE TABLE `USER` (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
username CHAR(30) NOT NULL,
password CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
CREATE OR REPLACE TABLE `PRODUCT` (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
description TEXT NOT NULL,
price DECIMAL(6,2),
currency CHAR(3),
PRIMARY KEY (id)
);
CREATE OR REPLACE TABLE `CART` (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
product_id MEDIUMINT,
user_id MEDIUMINT,
PRIMARY KEY (id)
);
INSERT INTO USER (username, password) VALUES ("betty_97", "betty_surfer_2024");
INSERT INTO PRODUCT (
name,
description,
price,
currency
) VALUES
(
"Surfboard",
"Amzaing blue surfboard",
55.00,
"EUR"
),
(
"Skateboard",
"Amzaing red skateboard",
20.00,
"EUR"
),
(
"Parasoll",
"Giant beach 5arasoll",
15.00,
"EUR"
);
INSERT INTO CART (
user_id,
product_id
) VALUES
(1,1),
(1,1),
(1,2);
CREATE OR REPLACE VIEW `SHOPPING_CART_V` AS
SELECT
p.id,
p.name,
p.description,
p.price,
SUM(p.price) AS totalprice
FROM USER u
LEFT JOIN CART c
ON u.id = c.user_id
LEFT JOIN PRODUCT p
ON c.product_id = p.id
GROUP BY p.id, p.currency;
select * from SHOPPING_CART_V;