Project 1

CREATE DATABASE project_1;
USE project_1;

CREATE TABLE Register(
RegisterId INT,
NAME CHAR(20),
PhoneNumber INT,
city CHAR(10),
country CHAR(10),
pincoode INT,
Email VARCHAR(50),
N0n_premium CHAR(5),
premium CHAR(5),
PRIMARY KEY (premium, N0n_premium,Email,PhoneNumber,RegisterId,city,country,pincoode)
);

SELECT * FROM Register;
INSERT INTO Register VALUES(1,'bala',85749758,'sklm','IND',53241,'bala58@gmail.com','YES','NO'),
(2,'khan',254725541,'VI','Srilanka',538451,'khan158@gmail.com','NO','YES'),
(3,'ram',1475955,'KM','AU',535241,'[email protected]','NO','YES'),
(4,'raju',24872535,'MD','ENG',532841,'[email protected]','NO','YES'),
(5,'sham',54614631,'CH','USSR',532541,'[email protected]','NO','YES'),
(6,'gana',36146316,'KL','USA',263241,'[email protected]','YES','NO');
CREATE TABLE customer(
RegisterId INT REFERENCES Register(RegisterId),
NAME CHAR(20) REFERENCES Register(NAME),
PhoneNumber INT REFERENCES Register(PhoneNumber),
city CHAR(10) REFERENCES Register(city),
country CHAR(10) REFERENCES Register(country),
pincoode INT REFERENCES Register(pincoode),
Email VARCHAR(50) REFERENCES Register(Email),
N0n_premium CHAR(5) REFERENCES Register(N0n_premium),
premium CHAR(5) REFERENCES Register(premium)
);

SELECT * FROM customer ORDER BY city ASC;
INSERT INTO customer VALUES(1,'bala',85749758,'sklm','IND',53241,'bala58@gmail.com','YES','NO'),
(2,'khan',254725541,'VI','Srilanka',538451,'khan158@gmail.com','NO','YES'),
(3,'ram',1475955,'KM','AU',535241,'[email protected]','NO','YES'),
(4,'raju',24872535,'MD','ENG',532841,'[email protected]','NO','Yes'),
(5,'sham',54614631,'CH','USSR',532541,'[email protected]','NO','YES'),
(6,'gana',36146316,'KL','USA',263241,'[email protected]','YES','NO');




CREATE TABLE discount(
id INT REFERENCES Register(RegisterId),
N0n_premium CHAR(5) REFERENCES Register(N0n_premium),
premium CHAR(5) REFERENCES Register(premium),
discountPremium INT,
discountNon_premium INT

);
SELECT * FROM discount;
INSERT INTO discount VALUES(1,'YES','NO',0,0),
(2,'NO','YES',500,0),
(3,'NO','YES',500,0),
(4,'NO','YES',500,0),
(5,'NO','YES',500,0),
(6,'YES','NO',0,0);

CREATE TABLE products(
NAME CHAR(50) REFERENCES seller(NAME),
product_id INT PRIMARY KEY,
collor CHAR(50) REFERENCES seller(collor),
seller_id INT REFERENCES seller(seller_id),
sizesAvailable CHAR(20)REFERENCES seller(sizesAvailable) ,
quantityAvailable CHAR(20) REFERENCES seller(quantityAvailable),
cost INT REFERENCES seller(cost),
currentdiscount DOUBLE

);
SELECT * FROM products;

INSERT INTO products VALUES('shirt',11,'black',12,'S','Good',1500,0.15),
( 'shoes',12,'white',123,'L','Good',2500,0.1),
( 'pens',13,'black',125,'Ball','Good',150,0),
( 'laptop',14,'i5core',1214,'i5','Good',15000,0.25),
( 'keyboard',15,'black',1245,'S','Good',1500,0.15),
( 'mouse',16,'black',124,'S','Good',500,0.1),
( 'pendrive',17,'sandisk',129,'S','Good',500,0.15);


CREATE TABLE Administrator(
Admin_id INT,
Admin_Name CHAR(20),
seller_id INT REFERENCES seller(seller_id)
);

INSERT INTO Administrator VALUES(121,'Ganesh',12),
(122,'manu',123),
(123,'Akash',125),
(124,'Bala',1214),
(125,'Gana',1245),
(126,'Gireesh',124),
(127,'Gowthami',129);


CREATE TABLE seller(
NAME CHAR(50),
collor CHAR(50),
seller_id INT ,
sizesAvailable CHAR(20),
quantityAvailable CHAR(20),
cost INT,
PRIMARY KEY(NAME,collor,seller_id,quantityAvailable,cost,sizesAvailable)
);
SELECT * FROM seller ;
INSERT INTO seller VALUES('shirt','black',12,'S','Good',1500),
( 'shoes','white',123,'L','Good',2500),
( 'pens','black',125,'Ball','Good',150),
( 'laptop','i5core',1214,'i5','Good',15000),
( 'keyboard','black',1245,'S','Good',1500),
( 'mouse','black',124,'S','Good',500),
( 'pendrive','sandisk',129,'S','Good',500);


CREATE TABLE categories_1(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
collor CHAR(50) REFERENCES seller(collor),
Brand CHAR(20),
Gender CHAR(20),
Age INT
);

INSERT INTO categories_1 VALUES(11,'shirt','black','Gucci','male',25),
(12,'shoes','white','Nike','male',22),
(13,'pens','Red','Elcos','male',30),
(14,'laptop','i5core','HP','male',18),
(15,'keyboard','black','Dell','male',19);

CREATE TABLE categories_2(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
collor CHAR(50) REFERENCES seller(collor),
Brand CHAR(20),
Gender CHAR(20),
Age INT
);

INSERT INTO categories_2 VALUES(11,'shirt','black','Gucci','Female',25),
(12,'shoes','white','Nike','Female',22),
(13,'pens','Red','Elcos','Female',30),
(14,'laptop','i5core','HP','Female',18),
(15,'keyboard','black','Dell','Female',19);

CREATE TABLE cart(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
collor CHAR(50) REFERENCES seller(collor),
Brand CHAR(20)
);

INSERT INTO wishlist VALUES(11,'shirt','black','Gucci'),
(12,'shoes','white','Nike'),
(13,'pens','Red','Elcos'),
(14,'laptop','i5core','HP'),
(15,'keyboard','black','Dell');

CREATE TABLE wishlist(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
collor CHAR(50) REFERENCES seller(collor),
Brand CHAR(20)
);
INSERT INTO wishlist VALUES(11,'shirt','black','Gucci'),
(12,'shoes','white','Nike'),
(13,'pens','Red','Elcos'),
(14,'laptop','i5core','HP'),
(15,'keyboard','black','Dell');

CREATE TABLE orders(
product_id INT REFERENCES products(product_id),
NAME CHAR(50) REFERENCES seller(NAME),
collor CHAR(50) REFERENCES seller(collor),
Brand CHAR(20),
cost INT REFERENCES seller(cost),
payment CHAR(20)

);

SELECT * FROM orders;

INSERT INTO orders VALUES(11,'shirt','black','Gucci',1500,'Yes'),
(11,'shirt','black','Gucci',1500,'Yes'),
(12,'shoes','white','Nike',2500,'Yes'),
(13,'pens','Red','Elcos',150,'Yes'),
(14,'laptop','i5core','HP',15000,'Yes'),
(14,'laptop','i5core','HP',15000,'Yes'),
(15,'keyboard','black','Dell',1500,'Yes'),
(15,'keyboard','black','Dell',1500,'Yes');





CREATE TABLE reviwers(
product_revid CHAR(20),
NAME CHAR(20) REFERENCES Register(NAME),
product_stars INT
);
INSERT INTO reviwers VALUES(301,'bala',5),
(302,'khan',4),
(303,'ram',3),
(304,'raju',0),
(305,'sham',5),
(306,'gana',3);







3.
SELECT COUNT(product_id),NAME,collor,Brand,cost,payment
FROM Orders
GROUP BY product_id
HAVING COUNT(product_id) > 1;
4.
SELECT * FROM orders
WHERE NAME LIKE 'k%' AND NAME LIKE'%d' ;
SELECT COUNT(product_id),NAME,collor,Brand,cost,payment
FROM Orders GROUP BY product_id HAVING product_id> 1;
5.
SELECT COUNT(product_id),NAME,collor,Brand,cost,payment
FROM Orders GROUP BY product_id HAVING cost>14000 AND product_id>1;
6.
First we have created Entity Realtion (ER) diagram and given realtions to it as per the input given by the .

In SQLyog we have created database and then created the required tables like Register, customer, seller, discounts, products,administartion,categories_1, categories_2, orders, wishtlist, reviews and cart.

For tables which already created as per our code requirement for few attributes we have considered the primary key and foreign key where ever it is required.
After that we have inserted the values for all the tables as per the fields input.
And finally we have write down the queries for given inputs.