Project 1

ER Diagram

Task 1:

CREATE TABLE Orders(order_id INT, seller_id INT, customer_id INT, delivery_id INT, order_date DATE, freight INT);

create table Subcategories(Category_name char(50), description char(50));

create table Billing_Info(billing_id int, billing_address char(50), billing_date date, payment_method char(50));

create table Cart(Product_id int, total_price int, no_of_products int);

create table Customer(first_name char(20), last_name char(20), contact_no char(20), customer_id int);

create table Delivery_Info(company_name char(50), delivery_id int, del_agent_phone int);

create table Order_Details(order_id int, product_id int, discount int, quantity int, pro_price int, total_price int, status char(20));

create table Personal_info(customer_id int, Phone int, Email_id char(50), city char(20), address char(50), pin_code int, Country char(20));

create table Seller(seller_id int, seller_fname char(20), seller_lname char(20), seller_address char(50));

create table Subcategories(Category_name char(50), description char(50));

create table Admin(admin_id int, admin_fname char(20), admin_lname char(20), admin_password int);

Task 2:

INSERT INTO orders VALUES
(501, 701, 301, 401, '2022-11-26',100),
(502, 702, 302, 402, '2022-11-25', 150),
(503, 703, 303, 403, '2022-11-24', 150),
(504, 701, 301, 404, '2022-11-25', 100)
;

insert into subcategories values( 'Education', 'Description');

insert into billing_info values(201, 'anywhere', 2022-11-26, 'cash');

insert into cart values(101, 2500, 5);

INSERT INTO customer VALUES
('Nick', 'Rimando', 986574256, 302),
('Brad', 'Davis', 4586258965, 303),
('Graham', 'Zusi', 145823658, 304),
('Julian', 'Green', 1452365894, 305),
('Fabian', 'Johnson', 1536478592, 306),
('Geoff', 'Cameron', 4586957856, 307),
('Jozy','Altidor', 1458796583, 308);

insert into delivery_info ('Skill Lync', 401, 620650545);

INSERT INTO order_details VALUES
(502, 101, 250, 10, 1200, 12000, 'shipped'),
(503, 102, 200, 3, 3000, 9000, 'packed'),
(504, 103, 500, 6, 2000, 12000, 'cancelled')
;

INSERT INTO personal_info VALUES
(302, 986574256, '[email protected]', 'Banglore', 'address1', 828131, 'India'),
(303, 458625896, '[email protected]', 'chennai', 'address2', 828132, 'India'),
(304, 145823658, '[email protected]', 'pune', 'address3', 828133, 'India'),
(305, 145236589, '[email protected]', 'chennai', 'address4', 828134, 'India'),
(306, 145823658, '[email protected]', 'pune', 'address5', 828135, 'India'),
(307, 458695785, '[email protected]', 'ranchi', 'address6',828136, 'India'),
(308, 145823658, '[email protected]', 'pune', 'address3', 828133, 'India')
;

insert into seller values(701, 'Skill', 'Lync', 'Banglore');

insert into admin values(001, 'Md Irfan', 'Ansari', 256347);

insert into product values(101, 'Some Product', 500);

Task 3:

1. select customer.customer_id, customer.first_name, customer.last_name,customer.contact_no, personal_info.Email_id, personal_info.city, personal_info.address, personal_info.pin_code, personal_info.country from personal_info inner join customer on personal_info.customer_id=customer.customer_id order by personal_info.city;

2. alter table product rename column product_id to prod_id, rename column product_name to prod_name, rename column product_price to prod_price;

3. select customer.customer_id, customer.first_name, customer.last_name, customer.contact_no, personal_info.Email_id, personal_info.city, personal_info.address, personal_info.pin_code, personal_info.Country from customer inner join personal_info on customer.customer_id = personal_info.customer_id inner join orders on customer.customer_id = orders.customer_id where orders.order_id > 0 ;

4. select customer.customer_id, customer.first_name, customer.last_name, customer.contact_no, personal_info.Email_id, personal_info.city, personal_info.address, personal_info.pin_code, personal_info.Country from customer inner join personal_info on customer.customer_id = personal_info.customer_id inner join orders on customer.customer_id = orders.customer_id where customer.first_name like 'A%' and customer.last_name like 'A%' having count(orders.order_id) > 1;

5. select orders.order_id, orders.seller_id, orders.customer_id, orders.delivery_id, orders.order_date, orders.freight, order_details.product_id, order_details.pro_price, order_details.quantity, order_details.total_price, order_details.discount, order_details.status from orders inner join order_details on orders.order_id = order_details.order_id where order_details.status = 'delivered' and order_details.total_price > 10000;

6. First we created Entity Relation (ER) diagram and gave relations to it as per the input provided in the project.

In MySQL, we created the database and then created the required tables like admin, customer, seller, products, orders, categories, subcategories, order_details, billing_info, delivery_info and cart.

After that, we inserted the values for all the tables as per the fields input.
And finally, we have written down the queries for the given inputs.