Project 1
CREATE TABLE product(product_id INT PRIMARY KEY, Name varchar(50),
cost float(10), size CHAR(50), available_products VARCHAR(255),
current_discount float(10), avg_ratings float(10), popularity_index INT);
INSERT INTO product VALUES(101, 'Ring', 9000, '60 mm',8,20.5,8000,100),
(102, 'vivo', 15000, '60 mm',6,12,9500,80),
(103, 'Moto', 12000,'57 mm',9,13,8200.5,92),
(104, 'Redmi',2000, '50 mm',10,8.8,8300.50,80),
(105, 'Titan', 9000, '55 mm',8,25,8110,82),
(106, 'Rolex',11000, '62 mm',10,11.5,8250.75,85),
(107,'Lenovo',200000,'9 inch',9,15,8994.25,99),
(108, 'Sony',25000, '7 inch',4,11,8772,90),
(109, 'Dell',16000, '22 inch',10,14,9772,93);
CREATE TABLE customer(customer_id INT PRIMARY KEY,
product_id INT,
Name VARCHAR(255),
gender CHAR(1),
DOB DATE,
customer_city CHAR(50),
Email VARCHAR(255),
contact_no CHAR(10),
customer_types CHAR(50),
previous_order VARCHAR(255),
current_order VARCHAR(255),
delivery_address VARCHAR(255),
payment_info VARCHAR(255),
FOREIGN KEY(product_id) REFERENCES product(product_id));
INSERT INTO customer VALUES(201,103, 'Sonia',"F",'1998-05-31', 'Newyork', '[email protected]',9539712852,
'Premium', 'Mobile', 'Rolex', '22,Melbournstreet', 'Paid'),
(202,104, 'siddu', 'M','1998-03-06', 'Delhi', '[email protected]',
8945123654, "Normal", "Titan","redmi",'235,Adayar,chennai','Unpaid'),
(204,106, 'jyothi', 'F', '2007-04-08',"London",'[email protected]',
9685659852,'Premium', 'Redmi', 'Moto','17,Wellingtonstreet,US','Paid'),
(205,106,'yogi', 'M', '2001-05-04','Chennai','[email protected]',
9953652212, "Normal", 'Rolex','Moto','19,T-Nagar,chennai','paid'),
(210,107,'satish', 'M','1999-11-05','Hyderabad' ,'[email protected]',
9856565487, 'premium', 'Titan', 'Lenovo', '20,Rajmundrystreet,Hyderabad', 'paid'),
(211,107, 'Asha', 'F', '1998-08-31', 'Bangalore', '[email protected]',
9856543227,' Normal', 'Vivo', 'Lenovo','122,Rajivstreet,Bangalore','paid');
Create table Seller(seller_id int,seller_name char(50),mobile_no char(50),password char(8),product_id int,add_product char(50));
insert into seller values(301,'Sagar','9876465623','klm568',198,'Smart Mobile');
insert into seller values(302,'sony','9876466544','gnf258',181,'Smart TV');
insert into seller values(301,'parthik','8952215648','thw654',178,'Machine');
create table categories(product_id int ,categories varchar(255),subcategories varchar(255),
product_description varchar(255),foreign key (product_id) references product(product_id));
insert into categories values(101,'Mobiles','IOS','Apple,35mm,Black,8GB RAM&256GB ROM'),
(103,'Mobile','Android','Redmi,65mm,Gray,16GB RAM & 512GB ROM'),
(106,'Electronics','Smart TV','Sony,655mm,Black,2GB RAM & 8GB ROM');
create table Rating(customer_id int,product_id int,total_ratings decimal(10,2),reviews varchar(255),reviewer_country varchar(255),
foreign key (customer_id) references customer(customer_id),foreign key (product_id) references product(product_id));
insert into Rating values(201,103,8425.25,'Looks Amazing','U.S.A'),
(204,106,9865,'it is working fine for a long time','Dellmark'),
(205,104,7896,'More efficient and working smoothly','India'),
(211,102,9225,'working smoother without any lag','India');
create table cart(product_id int,product_name char(50),
booking_status char(50),foreign key (product_id) references product(product_id));
insert into cart values(103,'Rolex','Booked'),(104,'Sony','Booked'),(106,'Moto','Cancelled');
CREATE TABLE Orders (customer_id INT,
product_id INT,
order_no INT primary key,
ord_status CHAR(50),
order_date DATE,
delivery_date DATE,
delivery_status CHAR(50),
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
FOREIGN KEY(product_id) REFERENCES product(product_id));
INSERT INTO orders VALUES(201,103,400, 'Ordered sucessfully', '2021-06-25', '2021-08-11', 'delivered'),
(201,105,402, 'ordered sucessfully', '2021-11-28', '2021-12-05', 'delivered'),
(202,103,405, 'ordered sucessfully', '2022-07-28', '2022-08-07','delivered'),
(201,104,407,'Ordered sucessfully', '2022-11-08', '2022-11-18', 'delivered'),
(204,106,403,'No response', '2023-04-28', '2023-05-07','cancelled'),
(205,104,404,'Waiting for confirmation', '2022-05-15', "2022-05-25","Packed"),
(210,107,408,'ordered sucessfully', '2020-06-14','2020-06-26', 'delivered'),
(211,107,409, 'ordered sucessfully', '2021-11-09', '2021-11-14', 'delivered'),
(211,109,411,'ordered sucessfully', '2022-04-09', '2022-04-15', 'delivered'),
(210,106,413,'ordered sucessfully', '2021-02-19', '2021-02-26', 'delivered'),
(204,109,414,'ordered sucessfully', '2021-02-19', '2021-02-26', 'delivered'),
(211,104,416,'No response', '2023-04-16', '2023-04-26', 'cancelled');
CREATE TABLE payment(customer_id INT,
bill_no INT,
order_no INT,
coupon_code INT,
coupon_percentage DECIMAL(10,2),
product_id INT,
amount DECIMAL(10,2),
payment_date DATE,
payment_status VARCHAR(255),
FOREIGN KEY(order_no) REFERENCES orders(order_no),
FOREIGN KEY(product_id) REFERENCES product(product_id),
FOREIGN KEY(customer_id) REFERENCES customer(customer_id));
INSERT INTO payment VALUES (202,7001,400,11001,10,103,12000, '2020-04-18','Paid successfully'),
(204,7002,402,11002,12.5,106,11000, '2020-06-28','Paid successfully'),
(205,7005,403,11008,10.5,104,2000, '2020-06-28','Payment unsuccessful');
CREATE TABLE ADMIN(admin_id INT,
admin_name CHAR(50),
PASSWORD CHAR(8),
product_id INT,
add_product VARCHAR(255));
INSERT INTO ADMIN VALUES(501,'Rahul', 'abcd7890',110, 'shirts'),
(502,'John','xyz0b21c',112, 'Mobile');
1A.
select * from customer order by customer_city;
2A.
alter table product rename column product_id to prod_id,rename column name to prod_name,rename column cost to prod_cost,
rename column size to prod_size,rename column current_discount to prod_discount,rename column avg_ratings to prod_ratings,
rename column popularity_index to prod_index;
3A.
Select c.customer_id,c.product_id,c.name AS "Customer Name",c.customer_city,c.Email,c.contact_no,
c.customer_types,c.current_order,p.name FROM customer c INNER JOIN product p ON c.product_id=p.product_id;
4A.
select c.customer_id,c.product_id,c.name AS "CustomerName",c.customer_city,c.Email,c.contact_no,customer_types,
o.order_no,o.ord_status FROM customer c LEFT JOIN orders o ON c.product_id=o.product_id WHERE c.name LIKE 'A%a'
AND o.delivery_status="delivered";
5A.
select o.customer_id,o.order_no,o.prod_id,o.ord_status,o.order_date,
o.delivery_status,p.cost FROM orders o LEFT JOIN product p ON o.prod_id=p.prod_id WHERE p.cost>10000;
6A.
call RunAllStatements();