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,'veena',8574975856,'sklm','IND',53241,'[email protected]','YES','NO'),
(2,'ammu',254725571,'IND','Srilanka',538451,'[email protected]','NO','YES'),
(3,'ramu',147594586,'KM','IND',535241,'[email protected]','NO','YES'),
(4,'vidya',2487254674,'MD','IND',532841,'[email protected]','NO','YES'),
(5,'shalini',5461463641,'CH','USSR',532541,'[email protected]','NO','YES'),
(6,'guna',36146316,'KL','USA',263241,'[email protected]','YES','NO');
CREATE TABLE Administrator(
Admin_id INT,
Admin_Name CHAR(20),
seller_id INT REFERENCES seller(seller_id)
);
INSERT INTO Administrator VALUES(121,'ammu',12),
(122,'manu',123),
(123,'Akash',125),
(124,'chaya',1214),
(125,'javashree',1245),
(126,'anesh',124),
(127,'kumar',129);
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(Non_premium),
premium CHAR(5) REFERENCES Register(premium)
);
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
);
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 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,'veena',4),
(303,'ammu',3),
(304,'chaya',0),
(305,'shalini',5),
(306,'guna',3);
1SELECT * FROM customers ORDER BY city HASSAN;
INSERT INTO customer VALUES(1,'veena',8574975856,'sklm','IND',53241,'[email protected]','YES','NO'),
(2,'ammu',254725571,'IND','Srilanka',538451,'[email protected]','NO','YES'),
(3,'ramu',147594586,'KM','IND',535241,'[email protected]','NO','YES'),
(4,'vidya',2487254674,'MD','IND',532841,'[email protected]','NO','YES'),
(5,'shalini',5461463641,'CH','USSR',532541,'[email protected]','NO','YES'),
(6,'guna',36146316,'KL','USA',263241,'[email protected]','YES','NO');
2 ALTER TABLE producuts ADD prod_;
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
CREATE TABLE Register();
SELECT * FROM Register;
INSERT INTO Register VALUES();
CREATE TABLE Administrator();
SELECT *FROM Administration;
INSERT INTO Administrator VALUES();
CREATE TABLE categories1();
SELECT * FROM categories1;
INSERT INTO categories1 VALUES();
CREATE TABLE categories2();
SELECT * FROM categories2;
INSERT INTO categories2 VALUES();
CREATE TABLE cart();
SELECT * FROM cart;
INSERT INTO cart VALUES();
CREATE TABLE order();
SELECT * FROM order;
INSERT INTO order VALUES();
CREATE TABLE wishlist();
SELECT * FROM wishlist;
INSERT INTO wishlist VALUES();
CREATE TABLE reviwers();
SELECT * FROM reviwers;
INSERT INTO reviwers VALUES();