Project 1
Create a Entity Relationship diagram for your ecommerce website based on the given requirement specification.
---------FILE ATTACHED BELOW----------
Task 1: Submit the SQL files with your table structure and data which will contain all SQL queries for creating and inserting in your ecommerce database table.
Task 2: Your ecommerce database should store data for the following.
SHOW DATABASES
CREATE DATABASE Store
USE Store
DROP TABLE customer
CREATE TABLE customer(
cus_id INT,
cus_name CHAR(50),
PRIMARY KEY (cus_id),
PASSWORD CHAR(10),
gender CHAR(1),
dob DATE,
contact DOUBLE,
cus_address VARCHAR(250),
city CHAR(50),
email CHAR(50)
);
DESCRIBE customer
SELECT * FROM customer
TRUNCATE customer
INSERT INTO customer VALUES (111, 'Magesh', 'abcd', 'M', '2000-05-20', 1111111111, 'potheri', 'chengalpat', '[email protected]'),
(222, 'Gokul', 'abdsaf', 'M', '1999-08-23', 2222222222, 'guduvancheri', 'chengalpat', '[email protected]'),
(333, 'Bhavani', 'bhava@1232', 'F', '1998-11-23', 3333333333, 'Thirutheri', 'changalpet', '[email protected]'),
(444, 'adam', 'Adam@543', 'M', '2000-05-11', 4444444444, 'Bodi', 'Theni', '[email protected]')
DROP TABLE seller
CREATE TABLE seller(
seller_id INT,
PRIMARY KEY (seller_id),
seller_name CHAR(50),
seller_phn_no DOUBLE
)
DESCRIBE seller
SELECT * FROM seller
INSERT INTO seller VALUES (121, 'Ramesh', 5555555555),
(122, 'Prabha', 6666666666),
(123, "Thamizh", 7777777777)
DROP TABLE ADMIN
CREATE TABLE ADMIN(
admin_id INT,
PRIMARY KEY (admin_id),
admin_name CHAR(50),
contact DOUBLE)
DESCRIBE ADMIN
SELECT * FROM ADMIN
INSERT INTO ADMIN VALUES (000, 'Tamilmani', 9999999999),
(001, 'Aravinth', 8888888888)
DROP TABLE products
CREATE TABLE products(
product_id INT,
PRIMARY KEY (product_id),
product_name CHAR(50),
catagory CHAR(100),
brand CHAR(100),
color CHAR(50),
cost FLOAT(10,2),
discount INT,
size CHAR(10),
quantity_available INT,
seller_id INT,
FOREIGN KEY (seller_id) REFERENCES seller (seller_id),
avg_ratings INT,
total_ratings INT,
reviews_for_ratings CHAR(200)
)
SELECT * FROM products
INSERT INTO products VALUES (15001, 'soap', 'groceries', 'PEARS', 'brown', 50, 5, '100g', 10, 121, 50, 100, 'Good'),
(15002, 'Hair Oil', 'groceries', 'amway', 'black', 200, 6, '200g', 15, 121, 70, 100, 'Good'),
(16002, 'Formal shirt', 'Clothes', 'puma', 'blue', 600, 10, 'XL', 4, 122, 80, 100, 'Awesome'),
(16003, 'Formal pant', 'Clothes', 'puma', 'black', 1000, 10, '34', 2, 122, 30, 100, 'not good'),
(17001, 'pulb', 'electronics', 'chrompton', 'white', 110, 2, '12W', 15, 123, 40, 100, 'avg'),
(17002, 'table_fan', 'electronics', 'usha', 'brown', 1500, 10, 'medium', 3, 123, 60, 100, 'good')
DROP TABLE cart
CREATE TABLE cart(
product_id INT,
PRIMARY KEY(product_id),
FOREIGN KEY (product_id) REFERENCES products (product_id),
cus_id INT,
FOREIGN KEY (cus_id) REFERENCES customer (cus_id),
product_name CHAR(100),
catalog CHAR(100)
)
SELECT * FROM cart
TRUNCATE cart
INSERT INTO cart VALUES (15001, 111, 'soap', 'groceries'),
(16002, 222, 'Formal shirt', 'clothes'),
(16003, 222, 'Formal pant', 'clothes'),
(17002, 333, 'table_fan', 'electronics')
DROP TABLE orders
CREATE TABLE orders (
order_id INT,
PRIMARY KEY (order_id),
cus_id INT,
FOREIGN KEY (cus_id) REFERENCES customer(cus_id),
product_id INT,
FOREIGN KEY (product_id) REFERENCES cart (product_id),
cost FLOAT(10,2),
payment_status BOOLEAN
)
SELECT *FROM orders
INSERT INTO orders VALUES (98754379, 333, 17002, 1500, TRUE),
(23111998, 222, 16003, 1000, TRUE),
(12052000, 222, 16002, 600, TRUE)
-------------------ALSO ATTACHED FILE BELOW FOR YOUR REFERENCE----------------------
Task 3: Write the SQL Queries for the below scenarios:
1. WAQ to fetch the details of the customers in the order of their city.
SELECT customer.cus_id, customer.cus_name, customer.city, orders.order_id
FROM customer
INNER JOIN orders ON orders.cus_id = customer.cus_id
2. WAQ to alter the table product by renameing all th ecolumns by preceding them with 'prod_'
ALTER TABLE products
RENAME column product_id to prod_id
3. WAQ to fetch the details of customer who have purchased at least one product
SELECT customer.cus_id, customer.cus_name, customer.city, orders.order_id
FROM customer
INNER JOIN orders ON orders.cus_id = customer.cus_id
4. WAQ to fetch the details of the customer who have name starting from A and ending with A and has purchased more than one item.
SELECT customer.cus_id, customer.cus_name, customer.gender, customer.dob, customer.contact, customer.city
FROM customer where cus_name LIKE '%A'
5. WAQ to all the information about the orders which are already delivered and has cost more than 10000 and also been purchased by more than 1 customer.
SELECT orders.order_id, orders.product_id, orders.cus_id, orders.cost, orders.payment_status
FROM orders WHERE payment_status = TRUE AND cost > 10000
6. Write a simple procedure to run all the table creation statements, insert statements and alter statements at one.
Create Table:
The Create Table statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Insert Values
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO statement in two ways:
1. Specify both the columns names and the values to be insert
Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
2. Adding all the values for all the columns of the table.
Syntax
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Alter Table
The ALTER TABLE statement is used to add , delete or modify columns in an existing table.
1. ALTER TABLE - ADD Column
Syntax
ALTER TABLE table_name
ADD column_name datatype;
2. ALTER TABLE - DROP Column
Syntax
ALTER TABLE table_name
DROP COLUMN column_name;
3. ALTER TABLE - MODIFY Column
Syntax
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;