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

Project 1

DDipanjan Ghosh28 Nov 2022

Task 3.

1. SELECT * FROM `customer` ORDER BY c_city;

2. ALTER TABLE `products` CHANGE `id` `prod_id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE `products` CHANGE `sub_category_id` `prod_sub_category_id` INT NULL DEFAULT NULL;

ALTER TABLE `products` CHANGE `product_name` `prod_product_name` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `brand` `prod_brand` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `color` `prod_color` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `seller_id` `prod_seller_id` INT NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `sizes_available` `prod_sizes_available` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `quantity_available` `prod_quantity_available` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `price` `prod_price` FLOAT NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `discount` `prod_discount` FLOAT NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `price_afterDiscount` `prod_price_afterDiscount` FLOAT NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `product_desc` `prod_desc` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `place_of_origin` `prod_place_of_origin` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `isFeatured` `prod_isFeatured` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `popularityIndex` `prod_popularityIndex` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `ratings` `prod_ratings` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `image` `prod_image` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL;
ALTER TABLE `products` CHANGE `is_onSale` `prod_is_onSale` INT NULL DEFAULT '0';
3.
SELECT * FROM `customer` c JOIN `orders` o ON c.id= o.customer_id;
4.
SELECT * FROM customer c JOIN (SELECT customer_id,COUNT(id) as items_ordered FROM orders GROUP BY customer_id) a ON c.id=a.customer_id WHERE a.items_ordered > 1 and c.c_name LIKE 'A%A';
5.
SELECT * FROM orders WHERE is_confirmed = 1 and amount > 10000;
6.

DELIMITER $$
CREATE PROCEDURE ecom.ecom_all()
BEGIN
CREATE TABLE `cart` (
`id` int NOT NULL,
`customer_id` int DEFAULT NULL,
`product_id` int DEFAULT NULL,
`cart_desc` varchar(1000) DEFAULT NULL,
`image` varchar(250) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE `category` (
`id` int NOT NULL,
`category_name` varchar(250) DEFAULT NULL,
`category_desc` varchar(250) DEFAULT NULL,
`trending` varchar(10) DEFAULT NULL,
`discount` float DEFAULT NULL
) ;
CREATE TABLE `customer` (
`id` int NOT NULL,
`c_name` varchar(250) DEFAULT NULL,
`c_mobile_no` varchar(250) DEFAULT NULL,
`c_email` varchar(250) DEFAULT NULL,
`c_country` varchar(250) DEFAULT NULL,
`c_city` varchar(250) DEFAULT NULL,
`c_address` varchar(1000) DEFAULT NULL,
`type` int DEFAULT '3',
`password` varchar(250) DEFAULT NULL,
`gender` enum('M','F','other') DEFAULT NULL,
`payment_info` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ;
CREATE TABLE `orders` (
`id` int NOT NULL,
`customer_id` int DEFAULT NULL,
`prod_id` int DEFAULT NULL,
`payment_method` enum('COD/POD','DEBIT/CREDIT card','upi','e-wallet','pay later') DEFAULT NULL,
`payment_id` varchar(250) DEFAULT NULL,
`amount` float DEFAULT NULL,
`delivery_address` varchar(250) DEFAULT NULL,
`seller_id` int DEFAULT NULL,
`customer_mobile` varchar(250) DEFAULT NULL,
`customer_email` varchar(250) DEFAULT NULL,
`is_confirmed` int DEFAULT '0',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ;
CREATE TABLE `products` (
`prod_id` int NOT NULL,
`prod_sub_category_id` int DEFAULT NULL,
`prod_product_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_brand` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_color` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_seller_id` int DEFAULT NULL,
`prod_sizes_available` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_quantity_available` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_price` float DEFAULT NULL,
`prod_discount` float DEFAULT NULL,
`prod_price_afterDiscount` float DEFAULT NULL,
`prod_desc` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_place_of_origin` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_isFeatured` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_popularityIndex` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_ratings` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_image` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`prod_is_onSale` int DEFAULT '0'
) ;
CREATE TABLE `review` (
`id` int NOT NULL,
`reviewer_name` varchar(250) DEFAULT NULL,
`is_validCustomer` int DEFAULT '0',
`product_id` int DEFAULT NULL,
`review_desc` varchar(1000) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ;
CREATE TABLE `seller` (
`id` int NOT NULL,
`s_name` varchar(250) DEFAULT NULL,
`s_mobile` varchar(250) DEFAULT NULL,
`s_email` varchar(250) DEFAULT NULL,
`s_password` varchar(250) DEFAULT NULL,
`s_country` varchar(250) DEFAULT NULL,
`s_city` varchar(250) DEFAULT NULL,
`s_address` varchar(1000) DEFAULT NULL,
`type` int DEFAULT '2',
`category_id` int DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ;
CREATE TABLE `subCategory` (
`id` int NOT NULL,
`category_id` int DEFAULT NULL,
`sub_category_name` varchar(250) DEFAULT NULL,
`sub_category_desc` varchar(250) DEFAULT NULL,
`trending` int DEFAULT NULL,
`discount` float DEFAULT NULL
) ;
CREATE TABLE `user` (
`id` int NOT NULL,
`user_type` varchar(100) DEFAULT NULL
) ;
INSERT INTO `category` (`id`, `category_name`, `category_desc`, `trending`, `discount`) VALUES
(1, 'electronics', 'All type of electronics', NULL, NULL),
(2, 'clothings', 'All type of clothes', NULL, NULL),
(3, 'office collections', 'All type of office accessories', NULL, NULL),
(4, 'groceries', 'All type of office groceries', NULL, NULL);
INSERT INTO `customer` (`id`, `c_name`, `c_mobile_no`, `c_email`, `c_country`, `c_city`, `c_address`, `type`, `password`, `gender`, `payment_info`, `created_at`) VALUES
(1, 'john saha', '+917003552389', '[email protected]', 'india', 'bangalore', '123,bikashnagar,bangalore', 3, '1234', 'M', '', '2022-11-27 10:06:27'),
(2, 'subham roy', '+917003552382', '[email protected]', 'india', 'bangalore', '123,prakashganz,bangalore', 3, 'ab123wert', 'M', '', '2022-11-27 10:08:05'),
(3, 'dip ravi', '+919003552382', '[email protected]', 'india', 'pune', '121,sector23,pune', 3, 'we341aswe', 'M', '', '2022-11-27 10:09:14');
INSERT INTO `orders` (`id`, `customer_id`, `prod_id`, `payment_method`, `payment_id`, `amount`, `delivery_address`, `seller_id`, `customer_mobile`, `customer_email`, `is_confirmed`, `created_at`) VALUES
(1, 1, 2, 'upi', 'qwe3452134ww', 2900, 'lorem ipsam', 2, '9876543210', '[email protected]', 1, '2022-11-27 18:15:57'),
(2, 1, 1, 'pay later', '1245rtewq3', 12390, 'lorem ipsum', 1, '9876543210', '[email protected]', 1, '2022-11-26 18:30:00'),
(3, 2, 3, 'upi', 'qwe3345665r', 13409, 'wewewee', 3, '8907653421', '[email protected]', 1, '2022-11-27 18:26:35'),
(4, 3, 3, 'DEBIT/CREDIT card', 'ereew34eerd', 13409, 'gfgfgf', 3, '7898564380', '[email protected]', 1, '2022-11-27 18:54:03');

INSERT INTO `seller` (`id`, `s_name`, `s_mobile`, `s_email`, `s_password`, `s_country`, `s_city`, `s_address`, `type`, `category_id`, `created_at`) VALUES
(1, 'cristine ', '+919876543210', '[email protected]', 'cr234sdfg', 'india', 'kolkata', 'kolkata,west bengal', 2, NULL, '2022-11-27 10:19:01'),
(2, 'caroline', '+919876543211', '[email protected]', 'caror234sdfg', 'india', 'pune', 'pune,maharastra', 2, NULL, '2022-11-27 10:20:03'),
(3, 'subhas', '+919476543211', '[email protected]', 'csubh34sdfg', 'india', 'pune', 'pune,maharastra', 2, NULL, '2022-11-27 10:21:00');
INSERT INTO `user` (`id`, `user_type`) VALUES
(1, 'admin'),
(2, 'seller'),
(3, 'customer');
ALTER TABLE `cart`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `category`
--
ALTER TABLE `category`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`prod_id`);

--
-- Indexes for table `review`
--
ALTER TABLE `review`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `seller`
--
ALTER TABLE `seller`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `subCategory`
--
ALTER TABLE `subCategory`
ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cart`
--
ALTER TABLE `cart`
MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `customer`
--
ALTER TABLE `customer`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `prod_id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `review`
--
ALTER TABLE `review`
MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seller`
--
ALTER TABLE `seller`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `subCategory`
--
ALTER TABLE `subCategory`
MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
END$$

DELIMITER;

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.