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 (101, 'Ruth', 90005, 'House_no_25 nehru nagar', 'Delhi' , 110001, "India", 490000)
ALTER TABLE customers
CHANGE customerName Cust Name VARCHAR (50) ;
END$$
DELIMITER;
CALL GetCustomersNew ();