Consider the following database schema of an e-commerce website: (Primary keys in bold and foreign keys in italic) item
Posted: Sun Jul 03, 2022 9:59 am
Consider the following database schema of an e-commerce website:(Primary keys in bold and foreign keys in italic)
item (item_id, name, description, price, quantity,category_id)
category (category_id, name)
customer (customer_id, name, gender, date_of_birth, phone,email)
address (address_id, building, country, city, location)
customer_address (customer_id, address_id, name)
order (order_id, date, total_price, customer_id, address_id)
order_details (order_id, item_id, nb_items, item_price,total_price)
NB: address_id of the table order holds the ID of the shippingaddress of the order.
Formulate SQL queries that:
1. Create the tables: order, and order_details with theirprimary and foreign keys.
2. Insert new customers with the following data (75, ‘Saleem’,‘M’, ‘1986-01-14’, ‘961 3 121213’, ‘[email protected]’)
(76, ‘Rima’, ‘F’, ‘1990-08-24’, ‘961 3 174223’,‘[email protected]’)
(77, ‘Maya’, ‘F’, ‘1991-05-01’, ‘961 3 187554’,‘[email protected]’)
3. List the items of the category ‘Electronics’ sorted byprice.
4. Give the total number of orders made during the year‘2015’.
5. Give the total amount of orders made during the year‘2015’.
6. List of orders made by the customer ‘Saleem’ shipped to hisaddress in ‘Beirut’ city, and which have a price > 20$.
7. The addresses of the customer ‘Saleem’.
8. The address details of the order having the ID ‘45139’.
9. The list of customers who made the highest number oforders.
10. The list of customers who didn’t made any order after‘2015-01-01’.
11. Increase by 10% the prices of items of the category ‘Mobileaccessories'.
item (item_id, name, description, price, quantity,category_id)
category (category_id, name)
customer (customer_id, name, gender, date_of_birth, phone,email)
address (address_id, building, country, city, location)
customer_address (customer_id, address_id, name)
order (order_id, date, total_price, customer_id, address_id)
order_details (order_id, item_id, nb_items, item_price,total_price)
NB: address_id of the table order holds the ID of the shippingaddress of the order.
Formulate SQL queries that:
1. Create the tables: order, and order_details with theirprimary and foreign keys.
2. Insert new customers with the following data (75, ‘Saleem’,‘M’, ‘1986-01-14’, ‘961 3 121213’, ‘[email protected]’)
(76, ‘Rima’, ‘F’, ‘1990-08-24’, ‘961 3 174223’,‘[email protected]’)
(77, ‘Maya’, ‘F’, ‘1991-05-01’, ‘961 3 187554’,‘[email protected]’)
3. List the items of the category ‘Electronics’ sorted byprice.
4. Give the total number of orders made during the year‘2015’.
5. Give the total amount of orders made during the year‘2015’.
6. List of orders made by the customer ‘Saleem’ shipped to hisaddress in ‘Beirut’ city, and which have a price > 20$.
7. The addresses of the customer ‘Saleem’.
8. The address details of the order having the ID ‘45139’.
9. The list of customers who made the highest number oforders.
10. The list of customers who didn’t made any order after‘2015-01-01’.
11. Increase by 10% the prices of items of the category ‘Mobileaccessories'.