Project 1
TABLE CREATION
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,'Santosh',6786768678,'abc@123','winter'),
(2,'Ramesh',9999768678,'ac@123','summer'),
(3,'Suresh',8886768678,'bc@123','ethnic');
INSERT INTO product VALUES(1,'red shirt',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,'dwight','schrute','scranton',786876),
(2,'Jim','helpert',453636),
(3,'kevin','malone',646464);
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,'mark'),(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 ();