ub_m2/grocery_db_tables.sql

drop database if exists grocery_db;
create database grocery_db;
use grocery_db;

-- Users Table Schema
CREATE TABLE `users` (
  `UserID` int(11) NOT NULL AUTO_INCREMENT,
  `UserFirstName` varchar(50) DEFAULT NULL,
  `UserLastName` varchar(50) DEFAULT NULL,
  `UserEmail` varchar(50) DEFAULT NULL,
  `UserPassword` varchar(50) DEFAULT NULL,
  `UserCity` varchar(50) DEFAULT NULL,
  `UserState` varchar(20) DEFAULT NULL,
  `UserZip` varchar(12) DEFAULT NULL,
  `UserPhone` varchar(20) DEFAULT NULL,
  `UserAddress` varchar(100) DEFAULT NULL,
  `UserAddress2` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`UserID`)
);

ALTER TABLE users AUTO_INCREMENT = 100;

insert into users values(
100, "Rajesh", "Kumar",
"shawn@mail.com", "shawn@mail.com",
"Chennai", "Tamil Nadu", "600040",
"5000500050", "Block B, Abirami Apartments",
"Anna Nagar"
);
insert into users values(
101, "Vignesh", "Shivan",
"vignesh@mail.com", "vignesh@mail.com",
"Chennai", "Tamil Nadu", "600030",
"5000500060", "Block A, Harmony Melody",
"Shenoy Nagar"
);

select * from users;
-- Product-Categories Table Schema
CREATE TABLE IF NOT EXISTS `productcategories` (
  `CategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `CategoryName` varchar(50) NOT NULL,
  PRIMARY KEY (`CategoryID`)
);

INSERT INTO `productcategories` (`CategoryID`, `CategoryName`) VALUES
(1, 'Snacks'),
(2, 'Vegetables'),
(3, 'Fruits'),
(4, 'Beverages'),
(5, 'Bakery Products'),
(6, 'Fist-Meat');

select * from productcategories;


-- Product Table Schema
create table Product(
  `ProductID` int(12) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(100) NOT NULL,
  `ProductPrice` float NOT NULL,
  `ProductWeight` float NOT NULL,
  `ProductShortDesc` varchar(1000) NOT NULL,
  `ProductLongDesc` text NOT NULL,
  `ProductImage` varchar(100) NOT NULL,
  `ProductCategoryID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ProductID`),
  FOREIGN KEY (`ProductCategoryID`) REFERENCES productcategories(`CategoryID`)
);

ALTER TABLE Product AUTO_INCREMENT = 1000;

-- Vegetables
insert into product values (1000, "Carrot", 55, 0.5, 
"Fresh Carrot - Ooty",
"A popular sweet-tasting root vegetable, Carrots provide the highest content of vitamin A of all the vegetables.",
"https://www.bigbasket.com/media/uploads/p/l/10000271_13-fresho-carrot-ooty.jpg", 2);
insert into product values (1001, "Potato", 30, 1, 
"Fresh Potato - Organically Grown",
"Potatoes are nutrient-dense, non-fattening and have reasonable amount of calories. Consumption of potatoes helps to maintain the blood glucose level and keeps the brain alert and active.",
"https://www.bigbasket.com/media/uploads/p/l/10000159_25-fresho-potato.jpg", 2);
insert into product values (1002, "Cauliflower", 60, 0.45, 
"Fresh Cauliflower",
"Cauliflower is made up of tightly bound clusters of soft, crumbly, sweet cauliflower florets that form a dense head.",
"https://www.bigbasket.com/media/uploads/p/l/10000074_19-fresho-cauliflower.jpg", 2);
-- Fruits
insert into product values (1003, "Orange", 65, 1, 
"Fresh Orange",
"Navel oranges are very sugary and juicy and considered to be the world's finest orange for fresh consumption because they are very sweet, naturally juice, seedless and peels and segments very easily.",
"https://www.bigbasket.com/media/uploads/p/l/20000910_12-fresho-orange-imported.jpg", 3);
insert into product values (1004, "Banana - Yelakki", 70, 1, 
"Fresh Banana - Yelakki",
"Yelakki bananas are small size, they are naturally flavoured, aromatic and sweeter compared to regular bananas.",
"https://www.bigbasket.com/media/uploads/p/l/10000031_21-fresho-banana-yelakki.jpg", 3);
-- Snacks
insert into product values (1005, "Britannia Good Day Cashew Cookies", 90, 0.120, 
"Britannia Good Day Cashew Cookies",
"Britannia Good Day Cashew Cookies are delicious crunchy cookies made with rich cashews.",
"https://www.bigbasket.com/media/uploads/p/l/40083744_5-britannia-good-day-cashew-cookies.jpg", 1);
insert into product values (1006, "Haldirams Namkeen - Bhujia Sev", 200, 1, 
"Haldirams Namkeen - Bhujia Sev",
"Haldirams Bhujia Sev is a authentic namkeen. This classic snack is made with chickpea flour and some spices.",
"https://www.bigbasket.com/media/uploads/p/l/100022552_1-haldirams-namkeen-bhujia-sev.jpg", 1);
-- Fist-Meat
insert into product values (1007, "Eggs - Regular (12)", 76, 0.150, 
"Fresh Eggs - Regular, 2x6 pcs Multipack ",
"Fresh Table eggs are hygienically processed and securely filled for keeping these free from any contamination and impurity.",
"https://www.bigbasket.com/media/uploads/p/l/40083744_5-britannia-good-day-cashew-cookies.jpg", 6);

select * from product;


-- Orders Table Schema
CREATE TABLE IF NOT EXISTS `orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderUserID` int(11) NOT NULL,
  `OrderAmount` float NOT NULL,
  `OrderShipAddress` varchar(100) NOT NULL,
  `OrderShipAddress2` varchar(100) NOT NULL,
  `OrderCity` varchar(50) NOT NULL,
  `OrderState` varchar(50) NOT NULL,
  `OrderZip` varchar(20) NOT NULL,
  `OrderCountry` varchar(50) NOT NULL,
  `OrderPhone` varchar(20) NOT NULL,
  `OrderShippingCost` float NOT NULL,
  `OrderTax` float NOT NULL,
  `OrderEmail` varchar(100) NOT NULL,
  `OrderDate` date not null,
  `OrderShipped` varchar(100) not null,
  `OrderTrackingNumber` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`OrderID`)
);

ALTER TABLE orders AUTO_INCREMENT = 1000;

insert into orders values(1000,1,12.5,'indra nagar','panchshill nagar','bhilai','c.g.','490025','india','7896545210',25.3,56.2,'g@gmail.com','2021-09-10','done','456');
insert into orders values(1001,1,12.5,'indra nagar','panchshill nagar','bhilai','c.g.','490025','india','7896545210',25.3,56.2,'g@gmail.com','2021-09-10','done','457');

SELECT * FROM orders;

-- Members Table Schema
create table `members`(
	  `UserID` int(11) NOT NULL,
	  `MemberID` int(11) not null AUTO_INCREMENT,
    `MemberName` varchar(20) not null,
    `MemberDoorNumber` int(4) not null,
    `TotalItems` int(3) not null default 0,
    `MemberPhone` varchar(15) not null,
    `MemberTotalAmount` float not null default 0,
    PRIMARY KEY (`MemberID`),
    FOREIGN KEY (`UserID`) REFERENCES users(`UserID`) 
);

<<<<<<< HEAD
INSERT INTO members VALUES(100, 1, "Abhishek", 501,0,"1234512345", 0);
INSERT INTO members VALUES(100, 2, "Meera", 502,5,"12345234567", 1200);
INSERT INTO members VALUES(100, 3, "Shyam", 503,10,"1234598745", 800);
=======
INSERT INTO members VALUES(100, 1, "Abhishek", 501, 0,"1234512345", 0);
INSERT INTO members VALUES(100, 2, "Meera", 502, 5,"12345234567", 1200);
INSERT INTO members VALUES(100, 3, "Shyam", 503, 10,"1234598745", 800);
>>>>>>> 62466d63e25d717c4d1930484f1053faf246ad32

select * from members;

-- Cart Table Schema
CREATE TABLE IF NOT EXISTS `cart` (
	 --  `CartID` int NOT NULL AUTO_INCREMENT,
--     `UserID` int NOT NULL,
--     `OrderID` int NOT NULL,
--     `ProductName` varchar(50) NOT NULL,
--     `ProductPrice` int(5) NOT NULL,
--     `Quantity` int NOT NULL,
--     PRIMARY KEY (`CartID`),
--     FOREIGN KEY (`UserID`) REFERENCES users(`UserID`) 
	`ProductID` int(12) NOT NULL AUTO_INCREMENT,
	`UserID` int NOT NULL,
  `ProductName` varchar(100) NOT NULL,
  `ProductPrice` float NOT NULL,
  `ProductCategoryID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ProductID`),
  FOREIGN KEY (`ProductCategoryID`) REFERENCES productcategories(`CategoryID`),
  FOREIGN KEY (`UserID`) REFERENCES users(`UserID`)
);
insert into cart values (1001,100,"Potato", 30,2);
 select * from cart;
ALTER TABLE cart AUTO_INCREMENT = 1000;

-----------------------------------------------------------------------------------------

drop user if exists 'testuser';


CREATE USER 'testuser' IDENTIFIED BY 'Password123';
grant all privileges on grocery_db.* to 'testuser'@'%' with grant option;

FLUSH PRIVILEGES;