Project 1

CREATE TABLE seller(
seller_id int PRIMARY KEY,
name char(20),
mobile_no int,
password char(20),
cat_prod char(20)
);

CREATE TABLE product(
id int PRIMARY KEY,
name char(20),
price int,
status char(20)
);

CREATE TABLE cart(
prod_id int,
quantity int,
size char(1),
FOREIGN KEY(prod_id) REFERENCES product(prod_id)
);

CREATE TABLE customer(
cust_id int PRIMARY KEY,
f_name char(20),
l_name char(20),
cust_city char(20),
cont_no int,
);

CREATE TABLE order(
order_no int PRIMARY KEY,
cust_id int,
order_date date,
status char(20);
order_amt int,
FOREIGN KEY(cust_id) REFERENCES customer(cust_id);
);

CREATE TABLE payment(
payment_id int PRIMARY KEY,
pay_date date,
amount int
);

CREATE TABLE admin(
admin_id int PRIMARY KEY,
name char(20)
);
CREATE TABLE category(
cat_id PRIMARY KEY,
cat_name char(20)
);

CREATE TABLE tracking(
tracking_no int PRIMARY KEY,
courier_name char(20)
);

INSERTING DATA INTO THE TABLES

INSERT INTO seller VALUES(1,'abd',9633316088,'[email protected]','winter'),
(2,'sachin',9995678544,'sachu@548','summer'),
(3,'kavya',9996768678,'[email protected]','ethnic');

INSERT INTO product VALUES(1,'pant',250,'available'),
(2,'jeans',500,'available'),
(3,'sweat shirt',1000,'out of stock');

INSERT INTO cart VALUES(1,5,'L'),
(2,1,'S'),
(2,2,'XL');

INSERT INTO customer VALUES(1,'randy','schrute','pkd',546876),
(2,'kane','william','chennai',456874),
(3,'john','cena','banglore',678572);

CREATE TABLE order(
order_no int PRIMARY KEY,
cust_id int,
order_date date,
status char(20);
order_amt int,
FOREIGN KEY(cust_id) REFERENCES customer(cust_id);
);

INSERT INTO order VALUES(1,1,'2022-12-1','delivered',345),
(2,2,'2022-11-29','pending',500),
(3,5,'2022-12-25','refunded',600);

INSERT INTO payment VALUES(1,'2022-12-1',345),
(2342,'2022-11-29',500),
(334,'2022-12-25',600);

INSERT INTO admin VALUES(1,'dude'),(2,'jeff'),(3,'bill');

INSERT INTO admin VALUES(1,'winter'),(2,'summer'),(3,'ethnic');

INSERT INTO admin VALUES(342,'Xpressbees'),(2312,'blue dart'),(3242,'india post');

QUERIES

1]SELECT * FROM customer ORDER BY cust_city;

2]ALTER TABLE product
RENAME COLUMN id to prod_id,
RENAME COLUMN name to prod_name,
RENAME COLUMN price to prod_price,
RENAME COLUMN status to prod_status;

3] SELECT customer.f_name,customer.l_name FROM customer
INNER JOIN order
ON customer.cust_id=order.cust_id;

4]SELECT customer.f_name,customer.l_name,customer.cust_city,customer.cont_no FROM customer
INNER JOIN order
ON customer.cust_id=order.cust_id
WHERE customer.f_name LIKE 'A%A';

5]select * from order group by cust_id having count(cust_id)>1 where order_amt>10000 and status='delivered' ;

6]
DELIMITER $$
CREATE PROCEDURE GetCustomersNew ()
BEGIN
CREATE TABLE customers (
Customer_Id INT,
CustomerName VARCHAR (20),
phone INT,
addressLine VARCHAR (100),
city VARCHAR (20),
state VARCHAR (20),
postalCode INT (10),
country VARCHAR (20),
creditlimit INT
);
INSERT INTO customers VALUES (105, 'Ram' , 90005, 'house _no_50 ram naghar', 'delhi', 'delhi', 110001, "India", 50000)
ALTER TABLE customers
CHANGE customerName Cust Name VARCHAR (50) ;
END$$
DELIMITER;
CALL GetCustomersNew ();