BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 1

DDivya Parihar5 Feb 2023

Project 1 ER Diagram

TASK- 1 Table Structure

TASK 2 e-commerce database Queries

CREATE DATABASE ecommercewebsite;

CREATE TABLE `ecommercewebsite`.`customer` (
`cust_id` INT NOT NULL,
`email` VARCHAR(45) NOT NULL,
`password` TEXT(10) NOT NULL,
`first_name` VARCHAR(45) NOT NULL,
`middle_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`dob` DATE NOT NULL,
`city` VARCHAR(45) NOT NULL,
`current_order` VARCHAR(45) NOT NULL,
`prev_order` VARCHAR(45) NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
`deleted_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`cust_id`));

CREATE TABLE `ecommercewebsite`.`cust_address` (
`cust_id` INT(11) NOT NULL,
`address_line1` VARCHAR(45) NULL,
`address_line2` VARCHAR(45) NULL,
`city` VARCHAR(45) NULL,
`postal_code` INT NULL,
`country` VARCHAR(45) NULL,
`mobile_no` INT NULL,
PRIMARY KEY (`cust_id`),
CONSTRAINT `cust_id`
FOREIGN KEY (`cust_id`)
REFERENCES `ecommercewebsite`.`customer` (`cust_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

INSERT INTO cust_address VALUES(1, "Vinoba Nagar", "Shimla Colony", "Ratlam", 457001, "India", 1236547898);

CREATE TABLE `ecommercewebsite`.`cust_payment` (
`cust_id` INT(11) NOT NULL,
`payment_type` VARCHAR(45) NOT NULL,
`provider` VARCHAR(45) NOT NULL,
`acc_no` INT NOT NULL,
`expiry_date` DATE NOT NULL,
PRIMARY KEY (`cust_id`),
FOREIGN KEY (`cust_id`)
REFERENCES `ecommercewebsite`.`customer` (`cust_id`)
);

CREATE TABLE `ecommercewebsite`.`seller` (
`seller_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`mob_no` INT NOT NULL,
`password` VARCHAR(45) NOT NULL,
`product_categories` VARCHAR(45) NOT NULL,
PRIMARY KEY (`seller_id`));

CREATE TABLE `ecommercewebsite`.`category` (
`category_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`parent_id` INT NOT NULL,
`desc` TEXT(50) NOT NULL,
`title` VARCHAR(45) NOT NULL,
`meta_title` VARCHAR(45) NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
`deleted_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`category_id`));

CREATE TABLE `ecommercewebsite`.`sub_categories` (
`id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`category_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `category_id_idx` (`category_id` ASC),
CONSTRAINT `category_id`
FOREIGN KEY (`category_id`)
REFERENCES `ecommercewebsite`.`category` (`category_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`brands` (
`brand_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`brand_id`));

INSERT INTO brands VALUES(1, "Biba");

CREATE TABLE `ecommercewebsite`.`brand_categories_pivot` (
`id` INT NOT NULL,
`brand_id` INT NOT NULL,
`category_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `brand_id_idx` (`brand_id` ASC),
INDEX `category_id_idx` (`category_id` ASC),
FOREIGN KEY (`brand_id`)
REFERENCES `ecommercewebsite`.`brands` (`brand_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (`category_id`)
REFERENCES `ecommercewebsite`.`category` (`category_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`brand_subcategories_pivot` (
`id` INT NOT NULL,
`brand_id` INT NOT NULL,
`subcategories_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `brand_id_idx` (`brand_id` ASC),
INDEX `subcategories_id_idx` (`subcategories_id` ASC),
CONSTRAINT `brand_id`
FOREIGN KEY (`brand_id`)
REFERENCES `ecommercewebsite`.`brands` (`brand_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `subcategories_id`
FOREIGN KEY (`subcategories_id`)
REFERENCES `ecommercewebsite`.`sub_categories` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`product` (
`product_id` INT NOT NULL,
`cust_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`desc` TEXT(100) NOT NULL,
`SKU` VARCHAR(45) NOT NULL,
`color` VARCHAR(45) NOT NULL,
`seller` VARCHAR(45) NOT NULL,
`sizes_available` INT NOT NULL,
`quantity_available` INT NOT NULL,
`cost` INT NOT NULL,
`current_discount` DECIMAL(5) NOT NULL,
`origin_place` VARCHAR(45) NOT NULL,
`isFeatured` TINYINT NOT NULL,
`popularity_index` INT NOT NULL,
`avg_ratings` INT NOT NULL,
`total_ratings` INT NOT NULL,
`rating_review` VARCHAR(45) NOT NULL,
`country_review` VARCHAR(45) NOT NULL,
`isOnSale` TINYINT NOT NULL,
`categories` VARCHAR(45) NOT NULL,
`category_id` INT NOT NULL,
`inventory_id` INT NOT NULL,
`brands` VARCHAR(45) NOT NULL,
`product_QA` VARCHAR(100) NULL,
`desc_QA` VARCHAR(100) NULL,
`tags_QA` VARCHAR(100) NULL,
`relatedtoproductid_QA` VARCHAR(100) NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
`deleted_id` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`product_id`));

CREATE TABLE `ecommercewebsite`.`product_inventory` (
`inventory_id` INT NOT NULL,
`quantity` INT NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
`deleted_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`inventory_id`),
CONSTRAINT `inventory_id`
FOREIGN KEY (`inventory_id`)
REFERENCES `ecommercewebsite`.`product` (`inventory_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`product_review` (
`product_id` INT NOT NULL,
`parent_id` INT NOT NULL,
`title` VARCHAR(45) NOT NULL,
`rating` INT NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
`deleted_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`product_id`),
CONSTRAINT `product_id`
FOREIGN KEY (`product_id`)
REFERENCES `ecommercewebsite`.`product` (`product_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`order_details` (
`order_id` INT NOT NULL,
`cust_id` INT NOT NULL,
`total` DECIMAL(10) NOT NULL,
`promo` VARCHAR(45) NOT NULL,
`discount` DECIMAL(10) NOT NULL,
`grand_total` DECIMAL(10) NOT NULL,
`payment_id` INT NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`order_id`),
INDEX `cust_id_idx` (`cust_id` ASC),
CONSTRAINT `order_id`
FOREIGN KEY (`order_id`)
REFERENCES `ecommercewebsite`.`order_items` (`order_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY (`cust_id`)
REFERENCES `ecommercewebsite`.`customer` (`cust_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`cart_item` (
`cart_id` INT NOT NULL,
`cust_id` INT NOT NULL,
`product_id` INT NOT NULL,
`session_id` INT NOT NULL,
`token` VARCHAR(45) NOT NULL,
`SKU` VARCHAR(45) NOT NULL,
`price` DECIMAL(10) NOT NULL,
`discount` DECIMAL(10) NOT NULL,
`quantity` INT NOT NULL,
`status` VARCHAR(45) NOT NULL,
`active` TINYINT NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`updated_at` TIMESTAMP(6) NOT NULL,
`content` VARCHAR(45) NOT NULL,
PRIMARY KEY (`cart_id`),
INDEX `product_id_idx` (`product_id` ASC),
INDEX `cust_id_idx` (`cust_id` ASC),
FOREIGN KEY (`product_id`)
REFERENCES `ecommercewebsite`.`product` (`product_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,FOREIGN KEY (`cust_id`)
REFERENCES `ecommercewebsite`.`customer` (`cust_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`shopping_session` (
`shopping_id` INT NOT NULL,
`cust_id` INT NOT NULL,
`total` DECIMAL(10) NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`shopping_id`),
INDEX `cust_id_idx` (`cust_id` ASC),
FOREIGN KEY (`cust_id`)
REFERENCES `ecommercewebsite`.`customer` (`cust_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

CREATE TABLE `ecommercewebsite`.`payment_details` (
`payment_id` INT NOT NULL,
`order_id` INT NOT NULL,
`cust_id` INT NOT NULL,
`amount` DECIMAL(10) NOT NULL,
`code` INT NOT NULL,
`provider` VARCHAR(45) NOT NULL,
`type` VARCHAR(45) NOT NULL,
`mode` VARCHAR(45) NOT NULL,
`status` VARCHAR(45) NOT NULL,
`created_at` TIMESTAMP(6) NOT NULL,
`modified_at` TIMESTAMP(6) NOT NULL,
`updated_at` TIMESTAMP(6) NOT NULL,
PRIMARY KEY (`payment_id`),
INDEX `order_id_idx` (`order_id` ASC),
FOREIGN KEY (`order_id`)
REFERENCES `ecommercewebsite`.`order_details` (`order_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

TASK 3 SQL Queries

Question 1

SELECT order_items.order_id, customer.city
FROM order_items, customer
WHERE order_items.cust_id = customer.cust_id;

Question 2

AlTER TABLE product
RENAME COLUMN product_id TO prod_id

Question 3

SELECT *
FROM customer
WHERE (current_order AND prev_order) >= 1;

Question 4

SELECT first_name
FROM customer
WHERE LEFT(first_name, 1) IN ('A')
AND RIGHT(first_name, 1) IN ('A');

Question 5

SELECT *
FROM order_details
WHERE grand_total > 10000;

Question 6

CREATE TABLE `ecommercewebsite`.`brands` (
`brand_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`brand_id`));

INSERT INTO brands VALUES(1, "Biba");

ALTER TABLE brands RENAME brands TO product_brand;

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.