Challenge

Project 1

PPrachi Nagpurkar21 Nov 2022

CREATE DATABASE Ecommerce;
USE Ecommerce;

CREATE TABLE products(
NAME CHAR(250),
color CHAR(50),
seller_name CHAR(200),
Size_available VARCHAR(150),
quantity_available INT,
cost INT,
current_discount DECIMAL(2,2),
place_of_origin CHAR(150),
calculation INT,
isFeatured CHAR(50),
PopularityIndex INT,
avgrating INT,
totalrating INT,
review_for_rating VARCHAR(250),
review_country CHAR(150),
isOnsale CHAR(120),
categories CHAR(150),
brands CHAR(150),
images LONGBLOB,
product CHAR(150),
description CHAR(255),
tags CHAR(50),
related_products_ids INT
);

DESCRIBE products;

CREATE TABLE customer(
cust_gender CHAR(50),
cust_dob DATE,
city CHAR(50),
pw VARCHAR(50),
NAME CHAR(150),
email VARCHAR(30),
delivery_adress CHAR(255),
contacts INT,
current_ord INT,
previous_ord INT,
pay_info_saved INT
);

DESCRIBE customer;

CREATE TABLE orders(
STATUS CHAR(50),
deliverydate DATE,
orderdate DATE,
invoices INT
);
DESCRIBE orders;

CREATE TABLE seller(
NAME CHAR(50),
mob_no INT,
PASSWORD VARCHAR(30),
Categories_provided CHAR(255)
);
DESCRIBE seller;

Task 3:

1.WAQ to fetch the deatails of the customers in the order of their city.

INSERT INTO customer VALUES('Male','1998-09-07','Nagpur','ABC1','Sandeep','[email protected]',"Mahal nagpur",44567874,1,2,3);
INSERT INTO customer VALUES('Male','1990-10-19','mumbai','123f','vicky','[email protected]',"West mumbai",5669854,2,2,4);
INSERT INTO customer VALUES('Female','2000-07-14','Mumbai','7hgf','Rashmi','[email protected]',"South mumbai",9764357,1,3,3);
INSERT INTO customer VALUES('Female','1984-06-07','Akola','hdr7','Lily','[email protected]',"Akola",8765442,0,1,3);
INSERT INTO customer VALUES('Male','2003-03-30','Delhi','983f','rohan','[email protected]',"South delhi",747578085,1,2,3);
DROP TABLE customer;
SELECT * FROM customer;
SELECT * FROM customer ORDER BY city;

2.WAQ to alter the table product by remaining all the columns by preceding them with 'prod_'.

ALTER TABLE products RENAME COLUMNS NAME TO prod_name,color TO prod_name,Size_available TO prod_sizeavl,quantity_available TO prod_quantity,
cost TO prod_cost,current_discount TO prod_cur_dis,place_to_origin TO prod_plcto_origin,calculation TO prod_calc,isFeatured TO prod_featured,
PopularityIndex TO prod_popindex,avgrating TO prod_avgrating,totalrating TO prod_totalrating,review_for_rating TO prod_revrating,
review_country TO prod_revcountry,isOnsale TO prod_onsale,categories TO prod_categories,brands TO prod_brands,images TO prod_image,
description TO prod_description,tags TO prod_tags,related_products_ids TO prod_relprodids;

3.WAQ to fetch the details of customer who have purchased at least one product.

SELECT previous_ord FROM customer HAVING (previous_ord)>=1;

4.WAQ to fetch the deatails of the customer who have name starting from A and ending from A and has purchased more than one item.

SELECT * FROM customer WHERE NAME LIKE '_a_' HAVING (previous_ord)>=1;

5.WAQ to all the information about the orders which are already delivered and has cost more than 10000 and also has been purchased by more than 1 customer.

SELECT previous_ord FROM customer HAVING previous_ord>=1 NOT IN
(SELECT * FROM orders WHERE STATUS='delivered' HAVING invoices<=10000);

6.Write a simple procedure to run all the tables creation statements,insert statments and alter statesments at once.

DELIMITER$$
CREATE PROCEDURE `ecommerce`.`Shoppinglink`()
BEGIN
CREATE TABLE products(
NAME CHAR(250),
color CHAR(50),
seller_name CHAR(200),
Size_available VARCHAR(150),
quantity_available INT,
cost INT,
current_discount DECIMAL(2,2),
place_of_origin CHAR(150),
calculation INT,
isFeatured CHAR(50),
PopularityIndex INT,
avgrating INT,
totalrating INT,
review_for_rating VARCHAR(250),
review_country CHAR(150),
isOnsale CHAR(120),
categories CHAR(150),
brands CHAR(150),
images LONGBLOB,
product CHAR(150),
description CHAR(255),
tags CHAR(50),
related_products_ids INT
);

DESCRIBE products;

DESCRIBE customer;

CREATE TABLE orders(
STATUS CHAR(50),
deliverydate DATE,
orderdate DATE,
invoices INT
);
DESCRIBE orders;

CREATE TABLE seller(
NAME CHAR(50),
mob_no INT,
PASSWORD VARCHAR(30),
Categories_provided CHAR(255)
);
DESCRIBE seller;

ALTER TABLE products RENAME COLUMN color TO prod_color;

SELECT previous_ord FROM customer HAVING (previous_ord)>=1;

SELECT * FROM customer WHERE NAME LIKE '_a_' HAVING (previous_ord)>=1;

INSERT INTO orders VALUES('Shipped','2022-11-20','2022-11-12',8000);
INSERT INTO orders VALUES('Cancelled','2022-10-9','2022-10-12',4000);
INSERT INTO orders VALUES('Shipped','2022-11-24','2022-11-12',8450);
INSERT INTO orders VALUES('Completed','2022-10-20','2022-10-12',15000);
INSERT INTO orders VALUES('Packed','2022-11-30','2022-11-19',450);
INSERT INTO orders VALUES('Shipped','2022-11-29','2022-11-21',780);
INSERT INTO orders VALUES('delivered','2022-09-20','2022-09-23',10000);
INSERT INTO orders VALUES('delivered','2022-10-07','2022-10-05',12500);
INSERT INTO orders VALUES('Packed','2022-11-14','2022-11-11',5600);
SELECT * FROM orders;

SELECT previous_ord FROM customer HAVING previous_ord>=1 NOT IN
(SELECT * FROM orders WHERE STATUS='delivered' HAVING invoices<=10000);

END$$
DELIMITER;

CALL Shoppinglink()

Project 1

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.