Project 1
CREATE DATABASE project1;
USE project1;
CREATE TABLE customers(
customer_id INT,
fname CHAR(30),
lname CHAR(30),
member CHAR(30),
city CHAR(30),
DOB DATE,
email CHAR(30),
delivery_address CHAR(60),
PRIMARY KEY(customer_id)
);
INSERT INTO customers VALUES(101,'Jay','Londhe','Premiam','Akola','1990-04-13','[email protected]','akot fail,railway colony'),
(102,'AJay','Kumar','Premiam','Pune','1995-06-17','[email protected]','Ranpisi Nagar 96H'),
(103,'Virat','Kohli','Normal','Delhi','1988-08-19','[email protected]','ashok nagar 87H');
CREATE TABLE products(
product_id INT,
produ_name CHAR(30),
describtion CHAR(50),
price FLOAT,
category_id INT,
customer_id INT,
seller_id INT,
productDetails_id INT,
PRIMARY KEY(product_id),
FOREIGN KEY(customer_id)REFERENCES customers(customer_id),
FOREIGN KEY(category_id)REFERENCES category(category_id),
FOREIGN KEY(seller_id)REFERENCES sellers(seller_id),
FOREIGN KEY(productDetails_id) REFERENCES productDetails(productDetails_id)
);
INSERT INTO products VALUES(10002,'dress','cotton dress', 12000,11,101,1001,5002),
(10003,'Tshirt','cotton dress', 1000,10,101,1001,5003)
CREATE TABLE productDetails(
productDetails_id INT,
color CHAR(30),
size INT,
rating FLOAT,
quantity INT (30),
PRIMARY KEY(productDetails_id)
);
DROP TABLE productdetails
DESCRIBE productdetails
INSERT INTO productDetails VALUES(5002,'red','M', 4.8, 3),
(5003,'blue','M', 4.8, 2);
SELECT * FROM productDetails;
CREATE TABLE Category(
category_id INT,
cat_name CHAR(30),
PRIMARY KEY(category_id)
);
INSERT INTO category VALUES(13,'Electronics'),
(11,'WOMEN'),
(12,'Children');
CREATE TABLE Sellers(
seller_id INT,
fname CHAR(30),
lname CHAR(30),
city CHAR(30),
DOB DATE,
email CHAR(30),
category_id INT,
PRIMARY KEY(seller_id),
FOREIGN KEY(category_id) REFERENCES category(category_id)
);
DROP TABLE Sellers;
DESCRIBE sellers
ALTER TABLE sellers ADD category_id INT ;
INSERT INTO sellers VALUES (1001,'Akshay','Kumar','Delhi','1967-02-11','[email protected]',10),
(1002,'Ramesh','jha','Hydrabad','1977-02-10','[email protected]',10),
(1003,'dinesh','Basu','pune','1987-06-11','[email protected]',11),
(1004,'Raja','Babu','banglore','1966-02-10','[email protected]',11),
(1005,'salman','khan','mumbai','1965-12-27','[email protected]',12);
CREATE TABLE Admin(
admin_id INT,
fname CHAR(30),
lname CHAR(30),
city CHAR(30),
DOB DATE,
email CHAR(30),
PRIMARY KEY(admin_id)
);
INSERT INTO admin VALUES(1,'Vijay', 'Londhe', 'Akola', '1985-04-27', '[email protected]');
CREATE TABLE orders(
order_id INT,
orderStatus CHAR(30),
cost FLOAT,
orderDate DATE,
email CHAR(30),
customer_id INT,
product_id INT,
FOREIGN KEY(customer_id) REFERENCES customers(customer_id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
PRIMARY KEY(order_id)
);
ALTER TABLE orders DROP COLUMN email;
INSERT INTO orders VALUES(600,'Delivered',35000,'2022-11-21',101,10001),
(601,'Delivered',36000,'2022-11-11',101,10002);
CREATE TABLE payment(
payment_id INT,
payment_mode CHAR(30),
order_id INT,
PRIMARY KEY(payment_id),
FOREIGN KEY(order_id) REFERENCES orders(order_id));
INSERT INTO payment VALUES(100001,'online',600),
(100002,'online',601);
TASK- 3
------------------------------------------------------------------------------
1.
SELECT * FROM customers ORDER BY city;
2.
ALTER TABLE products CHANGE produ_name prod_name CHAR(30),
CHANGE describtion prod_describtion CHAR(30),
CHANGE price prod_price FLOAT,
CHANGE category prod_category INT;
3.
SELECT c.*, COUNT(p.product_id) AS 'Total Product' FROM customers c LEFT JOIN products p
USING(customer_id) GROUP BY customer_id;
4.
SELECT c.*, COUNT(p.product_id) FROM customers c LEFT JOIN products p
USING(customer_id) WHERE fname LIKE 'a%' AND lname LIKE 'a%'
GROUP BY customer_id HAVING COUNT(p.product_id)>1;
5.
SELECT product_id,COUNT(customer_id)FROM orders WHERE cost>10000 AND
orderStatus='Delivered' GROUP BY product_id HAVING COUNT(customer_id)>1
6.
DELIMITER $$
CREATE PROCEDURE `project1`.`test`()
BEGIN
CREATE TABLE customers1(
customer_id INT,
fname CHAR(30),
lname CHAR(30),
member CHAR(30),
city CHAR(30),
DOB DATE,
email CHAR(30),
delivery_address CHAR(60),
PRIMARY KEY(customer_id)
);
INSERT INTO customers1 VALUES(101,'Jay','Londhe','Premiam','Akola','1990-04-13','[email protected]','akot fail,railway colony'),
(102,'AJay','Kumar','Premiam','Pune','1995-06-17','[email protected]','Ranpisi Nagar 96H'),
(103,'Virat','Kohli','Normal','Delhi','1988-08-19','[email protected]','ashok nagar 87H');
CREATE TABLE products1(
product_id INT,
produ_name CHAR(30),
describtion CHAR(50),
price FLOAT,
category_id INT,
customer_id INT,
seller_id INT,
productDetails_id INT,
PRIMARY KEY(product_id),
FOREIGN KEY(customer_id)REFERENCES customers1(customer_id),
FOREIGN KEY(category_id)REFERENCES category1(category_id),
FOREIGN KEY(seller_id)REFERENCES sellers1(seller_id),
FOREIGN KEY(productDetails_id) REFERENCES productDetails1(productDetails_id)
);
INSERT INTO products1 VALUES(10002,'dress','cotton dress', 12000,11,101,1001,5002),
(10003,'Tshirt','cotton dress', 1000,10,101,1001,5003)
CREATE TABLE productDetails1(
productDetails_id INT,
color CHAR(30),
size INT,
rating FLOAT,
quantity INT (30),
PRIMARY KEY(productDetails_id)
);
INSERT INTO productDetails1 VALUES(5002,'red','M', 4.8, 3),
(5003,'blue','M', 4.8, 2);
CREATE TABLE Category1(
category_id INT,
cat_name CHAR(30),
PRIMARY KEY(category_id)
);
INSERT INTO category1 VALUES(13,'Electronics'),
(11,'WOMEN'),
(12,'Children');
CREATE TABLE Sellers1(
seller_id INT,
fname CHAR(30),
lname CHAR(30),
city CHAR(30),
DOB DATE,
email CHAR(30),
category_id INT,
PRIMARY KEY(seller_id),
FOREIGN KEY(category_id) REFERENCES category1(category_id)
);
ALTER TABLE sellers1 ADD category_id INT ;
INSERT INTO sellers1 VALUES (1001,'Akshay','Kumar','Delhi','1967-02-11','[email protected]',10),
(1002,'Ramesh','jha','Hydrabad','1977-02-10','[email protected]',10),
(1003,'dinesh','Basu','pune','1987-06-11','[email protected]',11),
(1004,'Raja','Babu','banglore','1966-02-10','[email protected]',11),
(1005,'salman','khan','mumbai','1965-12-27','[email protected]',12);
CREATE TABLE Admin1(
admin_id INT,
fname CHAR(30),
lname CHAR(30),
city CHAR(30),
DOB DATE,
email CHAR(30),
PRIMARY KEY(admin_id)
);
INSERT INTO admin1 VALUES(1,'Vijay', 'Londhe', 'Akola', '1985-04-27', '[email protected]');
CREATE TABLE orders1(
order_id INT,
orderStatus CHAR(30),
cost FLOAT,
orderDate DATE,
email CHAR(30),
customer_id INT,
product_id INT,
FOREIGN KEY(customer_id) REFERENCES customers1(customer_id),
FOREIGN KEY(product_id) REFERENCES products1(product_id),
PRIMARY KEY(order_id)
);
ALTER TABLE orders1 DROP COLUMN email;
INSERT INTO orders1 VALUES(600,'Delivered',35000,'2022-11-21',101,10001),
(601,'Delivered',36000,'2022-11-11',101,10002);
CREATE TABLE payment1(
payment_id INT,
payment_mode CHAR(30),
order_id INT,
PRIMARY KEY(payment_id),
FOREIGN KEY(order_id) REFERENCES orders1(order_id));
INSERT INTO payment1 VALUES(100001,'online',600),
(100002,'online',601);
END$$
DELIMITER;