project0.sql (new)

DROP TABLE IF EXISTS userInfo;

DROP TABLE IF EXISTS saleProductOffers;

DROP TABLE IF EXISTS exchangeProductOffers;

CREATE TABLE userInfo
(
userNumber INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
userName VARCHAR(50) UNIQUE NOT NULL,
passcode VARCHAR(100) NOT NULL,
firstName VARCHAR(25) NOT NULL,
lastName VARCHAR(25) NOT NULL,
phoneNumber VARCHAR(15) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE,
placeWithoutDivision VARCHAR(25) NOT NULL,
division VARCHAR(25) NOT NULL,
profilePicture VARCHAR(255),
PRIMARY KEY (userName)
);

CREATE TABLE saleProductOffers
(
offerNumber INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
productImage VARCHAR(255),
productName VARCHAR(100) NOT NULL,
productDescription VARCHAR(255) NOT NULL,
productPrice INT UNSIGNED NOT NULL,
productUploadDate DATE NOT NULL,
productUploadTime TIME NOT NULL,
productCategory VARCHAR(50) NOT NULL,
productSubCategory VARCHAR(50),
userName VARCHAR(50),
PRIMARY KEY (offerNumber),
FOREIGN KEY (userName) REFERENCES userInfo(userName)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

CREATE TABLE exchangeProductOffers
(
exchangeNumber INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
sendImage VARCHAR(255),
sendName VARCHAR(100) NOT NULL,
sendDescription VARCHAR(255) NOT NULL,
sendUploadDate DATE NOT NULL,
sendUploadTime TIME NOT NULL,
sendCategory VARCHAR(50) NOT NULL,
sendSubCategory VARCHAR(50),
receiveName VARCHAR(100) NOT NULL,
receiveDescription VARCHAR(255) NOT NULL,
userName VARCHAR(50),
PRIMARY KEY (exchangeNumber),
FOREIGN KEY (userName) REFERENCES userInfo(userName)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

INSERT INTO userInfo (userNumber, userName, passcode, firstName, lastName, phoneNumber, email, placeWithoutDivision, division, profilePicture)
VALUES (NULL,'abdullah','1','hasnat','abdullah','01761900888','hasnat.abdullah@gmail.com','shantinagar','dhaka','abdullah.jpg');

INSERT INTO userInfo (userNumber, userName, passcode, firstName, lastName, phoneNumber, email, placeWithoutDivision, division, profilePicture)
VALUES (NULL,'joy','1','joy','rahman','01761900999','joy.rahman@gmail.com','uttara','dhaka','joy.jpg');


INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'a4techKeyboard.jpg','A4TECH keyboard','Brand : A4TECH \n Used : 1 year',100,CURRENT_DATE,CURRENT_TIME,'electronics','keyboard','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'a4techKeyboardAndMouse.jpg','A4TECH keyboard and mouse','Brand : A4TECH \n Used : 1 year',200,CURRENT_DATE,CURRENT_TIME,'electronics','mouse and keyboard','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'a4techMouse.jpg','A4TECH mouse','Brand : A4TECH \n Used : 1 year',100,CURRENT_DATE,CURRENT_TIME,'electronics','mouse','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'adjustable2PinAdapter.jpg','Adjustable 2 pin adapter','Brand : Many \n Used : 1 year',100,CURRENT_DATE,CURRENT_TIME,'electronics','adapter','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'bench.jpg','Bench','Homemade \n Used : 1 year \n Color : Black \n Quantity : 1 piece',1000,CURRENT_DATE,CURRENT_TIME,'furniture','bench','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'blackDuster.jpg','Duster','Brand : Faber Castle \n Color : Black \n Used : 1 month',100,CURRENT_DATE,CURRENT_TIME,'education','duster','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'chairs.jpg','Chair','Homemade \n Quantity : 2 pieces \n Used : 1 year',2000,CURRENT_DATE,CURRENT_TIME,'furniture','chair','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'coolineAC.jpg','Air conditioner','Brand : Cooline \n Type : 1.5 ton \n Used : 1 year',100000,CURRENT_DATE,CURRENT_TIME,'electronics','airconditionter','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'hitachiProjector.jpg','Projector','Brand : Hitachi \n Used : 1 year',10000,CURRENT_DATE,CURRENT_TIME,'electronics','projector','abdullah');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'hpProbook450G1Laptop.jpg','HP Probook 450 G1 Laptop','Brand : A4TECH \n Used : 1 year',100000,CURRENT_DATE,CURRENT_TIME,'electronics','laptop','joy');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'logitechMouse.jpg','Logitech mouse','Brand : Logitech \n Type : Bluetooth mouse \n Used : 1 year',2500,CURRENT_DATE,CURRENT_TIME,'electronics','mouse','joy');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'manyMultiPlug.jpg','Multiplug','Brand : Many \n Used : 1 year',3000,CURRENT_DATE,CURRENT_TIME,'electronics','multiplug','joy');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'transcend1TBExternalHarddisk.jpg','Transcend Harddisk','Brand : Transcend \n Memory : 1TB Type: External \n Used : 1 year',3000,CURRENT_DATE,CURRENT_TIME,'electronics','harddisk','joy');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'transcend4GBPendrive.jpg','Transcend 4GB Pendrive','Brand : Transcend \n Memory : 4GB \n Used : 2 year',1000,CURRENT_DATE,CURRENT_TIME,'electronics','pendrive','joy');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'transend64GBPendrive.jpg','Transcend 64GB Pendrive','Brand : Transcend \n Memory : 64GB \n Used : 2 year',10000,CURRENT_DATE,CURRENT_TIME,'electronics','pendrive','joy');

INSERT INTO saleproductoffers(offerNumber,productImage,productName,productDescription,productPrice,productUploadDate,productUploadTime,productCategory,productSubCategory,username)
VALUES (NULL,'whiteBoard.jpg','Whiteboard','Homemade \n Quantity : 1 piece \n Used : 2 year',100,CURRENT_DATE,CURRENT_TIME,'education','whiteBoard','joy');


INSERT INTO exchangeProductOffers(exchangeNumber,sendImage,sendName,sendDescription,sendUploadDate,sendUploadTime,sendCategory,sendSubCategory,receiveName,receiveDescription,userName)
VALUES (NULL,'a4techMouse.jpg','A4TECH keyboard','Brand : A4TECH \n Used : 1 year',CURRENT_DATE,CURRENT_TIME,'electronics','keyboard','A4TECH mouse','Color : Any \n Used : Any','abdullah');

INSERT INTO exchangeProductOffers(exchangeNumber,sendImage,sendName,sendDescription,sendUploadDate,sendUploadTime,sendCategory,sendSubCategory,receiveName,receiveDescription,userName)
VALUES (NULL,'blackDuster.jpg','Duster','Brand : Faber Castle \n Color : Black \n Used : 1 month',CURRENT_DATE,CURRENT_TIME,'education','duster','Adjustable 2 pin adapter','Brand : Any \n Used : Any','abdullah');

INSERT INTO exchangeProductOffers(exchangeNumber,sendImage,sendName,sendDescription,sendUploadDate,sendUploadTime,sendCategory,sendSubCategory,receiveName,receiveDescription,userName)
VALUES (NULL,'transcend1TBExternalHarddisk.jpg','Transcend Harddisk','Brand : Transcend \n Memory : 1TB Type: External \n Used : 1 year',CURRENT_DATE,CURRENT_TIME,'electronics','harddisk','Transcend 64GB Pendrive','Brand : Transcend \n Used : Any','joy');

INSERT INTO exchangeProductOffers(exchangeNumber,sendImage,sendName,sendDescription,sendUploadDate,sendUploadTime,sendCategory,sendSubCategory,receiveName,receiveDescription,userName)
VALUES (NULL,'transcend4GBPendrive.jpg','Transcend 4GB Pendrive','Brand : Transcend \n Memory : 4GB \n Used : 2 year',CURRENT_DATE,CURRENT_TIME,'electronics','pendrive','Whiteboard','Size : 12*4 sq.inch \n Used : Any','joy');