Design a shopping cart database

Опубликовано: 31 Октябрь 2024
на канале: Improve Your Programming skills

5,687

1. Design a shopping cart database diagram using MySQL Workbench 8.0 CE
2. Generate the diagram to database
3. Insert data to database using SQL commands
4. Testing the correctness of that database design by making some SQL commands
about statistics
Get the list of carts of all users
select username, cart.id as cartID, CAST(delivery_date AS date) as delivery_date
from cart, user
where cart.user_id = user.id
Get the list of cart of a user
select username, cart.id as cartID, CAST(delivery_date AS date) as delivery_date
from cart, user
where cart.user_id = user.id
and username='johnlennon'

get the list of products of a cart
select username, delivery_date, product.name, orders.quantity
from product, orders, cart, user
where cart.id=orders.cart_id
and product.id = orders.product_id
and orders.cart_id=1
and user.id=cart.user_id

Get the total amount of a cart
select username, CAST(delivery_date as Date) as delivery_date, sum(orders.quantity*product.price) as total
from product, orders, cart, user
where orders.cart_id=1
and product.id = orders.product_id
and cart.id = orders.cart_id
and user.id=cart.user_id
Get the list of carts of all users including the total amount
select username, CAST(delivery_date as Date) as delivery_date, sum(orders.quantity*product.price) as total
from product, orders, cart, user
where user.id=cart.user_id
and cart.id = orders.cart_id
and product.id = orders.product_id
group by cart.id