Project 1

Task3:

1) SELECT * FROM customer ORDER BY city;

2)ALTER TABLE product

RENAME COLUMN id TO prod_id,

RENAME COLUMN name TO prod_name,

RENAME COLUMN category TO prod_category,

RENAME COLUMN brand TO prod_brand,

RENAME COLUMN colour TO prod_colour,

RENAME COLUMN description TO prod_description,

RENAME COLUMN review TO prod_review,

RENAME COLUMN seller_id TO prod_seller_id,

RENAME COLUMN price TO prod_price,

RENAME COLUMN availability TO prod_availability;

3)SELECT * FROM customer WHERE currentand prevorders IS NOT NULL;

4) SELECT * FROM customer WHERE currentand prevorders>1 AND name IS LIKE 'a%a';

5) SELECT * FROM orders o INNER JOIN product p ON o.product_id=p.prod_id WHERE o.status='delivered' AND p.cost>10000 ;

6)DELIMITER $$ CREATE PROCEDURE Ecommercetables

BEGIN

CREATE TABLE `brands` (
`brand_id` varchar(50) NOT NULL,
`brand_name` varchar(100) NOT NULL
) ;

INSERT INTO `brands` (`brand_id`, `brand_name`) VALUES
('9001', 'sony'),
('9002', 'LG'),
('9003', 'JBL'),
('9004', 'samsung'),
('9005', 'bosch');

CREATE TABLE `cart` (
`cart_id` varchar(10) NOT NULL,
`product_id` varchar(10) NOT NULL,
`delivery_adress` varchar(200) NOT NULL,
`payment_status` varchar(10) NOT NULL,
`cart_amount` int(10) NOT NULL
);

INSERT INTO `cart` (`cart_id`, `product_id`, `delivery_adress`, `payment_status`, `cart_amount`) VALUES
('3455', '2001', 'hyderabad', 'paid', 2000),
('3456', '2002', 'bangalore', 'cod', 1000),
('3457', '2003', 'chennai', 'paid', 1500),
('3458', '2004', 'mumbai', 'cod', 2500),
('3459', '2005', 'delhi', 'paid', 3000);

CREATE TABLE `categories` (
`category_id` varchar(50) NOT NULL,
`category_name` varchar(100) NOT NULL
);

INSERT INTO `categories` (`category_id`, `category_name`) VALUES
('3001', 'electronics'),
('3002', 'fashion'),
('3003', 'books'),
('3004', 'furniture'),
('3005', 'appliences');

CREATE TABLE `coupans` (
`coupen_code` varchar(20) NOT NULL,
`discount(%)` int(11) NOT NULL
);

INSERT INTO `coupans` (`coupen_code`, `discount(%)`) VALUES
('abc123', 10),
('abc124', 20),
('abc125', 30),
('abc126', 40),
('abc127', 50);

CREATE TABLE `customers` (
`customer_id` varchar(10) NOT NULL,
`password` varchar(20) NOT NULL,
`customer_name` varchar(100) NOT NULL,
`gender` varchar(10) NOT NULL,
`dob` varchar(20) NOT NULL,
`city` varchar(50) NOT NULL,
`mobile_no` int(10) NOT NULL,
`email_id` varchar(200) NOT NULL,
`adress` varchar(200) NOT NULL,
`whishlist_id` varchar(10) NOT NULL,
`cart_id` varchar(10) NOT NULL
);

INSERT INTO `customers` (`customer_id`, `password`, `customer_name`, `gender`, `dob`, `city`, `mobile_no`, `email_id`, `adress`, `whishlist_id`, `cart_id`) VALUES
('1001', 'rfftyy', 'root', 'male', '12-12-1992', 'london', 6755677, '[email protected]', 'uk', '1212', '3455'),
('1002', 'abc@123', 'alex', 'male', '', 'hyderabad', 4543553, '[email protected]', 'begumpet', '1213', '3456'),
('1003', 'rfftyy', 'smriti', 'female', '1-2-1992', 'delhi', 76786767, '[email protected]', 'india', '1214', '3457'),
('1004', 'rfftyy', 'stokes', 'male', '12-10-1995', 'london', 76687887, '[email protected]', 'uk', '1215', '3458'),
('1005', 'rfftyy', 'kaur', 'female', '2-1-1999', 'chennai', 98767676, '[email protected]', 'tamilnad', '1216', '3459');

CREATE TABLE `orders` (
`order_id` varchar(10) NOT NULL,
`custoer_id` varchar(10) NOT NULL,
`producct_id` varchar(10) NOT NULL,
`order_amount` int(10) NOT NULL
);

INSERT INTO `orders` (`order_id`, `custoer_id`, `producct_id`, `order_amount`) VALUES
('4001', '1001', '2001', 2000),
('4002', '1002', '2002', 1000),
('4003', '1003', '2003', 1500),
('4004', '1004', '2004', 2500),
('4005', '1005', '2005', 3000);

CREATE TABLE `products` (
`product_id` varchar(10) NOT NULL,
`product_name` varchar(100) NOT NULL,
`product_category` varchar(50) NOT NULL,
`product_brand` varchar(50) NOT NULL,
`product_colour` varchar(50) NOT NULL,
`Product_description` varchar(200) NOT NULL,
`product_review` varchar(50) NOT NULL,
`seller_id` varchar(50) NOT NULL,
`product_price` int(10) NOT NULL,
`product_availability` varchar(100) NOT NULL
);

INSERT INTO `products` (`product_id`, `product_name`, `product_category`, `product_brand`, `product_colour`, `Product_description`, `product_review`, `seller_id`, `product_price`, `product_availability`) VALUES
('2001', 'laptop', '3001', '9001', 'grey', 'hghjg', 'good', '5001', 2000, 'avialable'),
('2002', 'laptop', '3002', '9002', 'black', 'ahhgh', 'good', '5002', 1000, 'avialable'),
('2003', 'laptop', '3003', '9003', 'red', 'jghgh', 'good', '5003', 1500, 'avialable'),
('2004', 'laptop', '3004', '9004', 'biue', 'ytygg', 'good', '5004', 2500, 'avialable'),
('2005', 'laptop', '3005', '9005', 'white', 'kjkf', 'good', '5005', 3000, 'avialable');

CREATE TABLE `sellers` (
`seller_id` varchar(10) NOT NULL,
`seller_name` varchar(100) NOT NULL,
`password` varchar(20) NOT NULL,
`categories_provided` varchar(10) NOT NULL,
`seller_mobile` int(10) NOT NULL,
`seller_adress` varchar(200) NOT NULL
);

INSERT INTO `sellers` (`seller_id`, `seller_name`, `password`, `categories_provided`, `seller_mobile`, `seller_adress`) VALUES
('5001', 'sachin', 'gfhdjj', '3001', 74557674, 'chandigarh'),
('5002', 'dhoni', 'gfhdsj', '3002', 74555674, 'calicut'),
('5003', 'kohli', 'gfhfdg', '3003', 45557674, 'california'),
('5004', 'yadav', 'djhfjj', '3004', 74557656, 'Raipur'),
('5005', 'sourav', 'hjkhj', '3005', 89857674, 'kolkatta');

CREATE TABLE `wishlist` (
`wishlist_id` varchar(10) NOT NULL,
`product_id` varchar(10) NOT NULL
);

INSERT INTO `wishlist` (`wishlist_id`, `product_id`) VALUES
('1212', '2001'),
('1213', '2002'),
('1214', '2003'),
('1215', '2004'),
('1216', '2005');

ALTER TABLE `brands`
ADD PRIMARY KEY (`brand_id`);

ALTER TABLE `cart`
ADD PRIMARY KEY (`cart_id`);

ALTER TABLE `categories`
ADD PRIMARY KEY (`category_id`);

ALTER TABLE `customers`
ADD PRIMARY KEY (`customer_id`);

ALTER TABLE `orders`
ADD PRIMARY KEY (`order_id`);

ALTER TABLE `products`
ADD PRIMARY KEY (`product_id`);

ALTER TABLE `sellers`
ADD PRIMARY KEY (`seller_id`);

ALTER TABLE `wishlist`
ADD PRIMARY KEY (`wishlist_id`);

END $$

DELIMITER ;