Project 1

CREATE DATABASE project;
USE project;
CREATE TABLE product (product_id int primary key,name varchar(255) not null,color char(50) not null,seller varchar(255) not null,sizes int not null,quatity int default,cost int not null,discount int default, place of orgin varchar(255) not null,total int,foreign key (product_Id) references prouduct(product_Id) );

CREATE TABLE Customer( customerId int primary key, firstName varchar(255), lastName varchar(255), email varchar(255), password varchar(255), dateOfBirth date, shippingAddress varchar(255), contact no int(10), payment info saved char(255) ),gender char(5));

CREATE TABLE Seller( sellerId int primary key, firstName varchar(255), lastName varchar(255), email varchar(255), password varchar(255), dateOfBirth date, shippingAddress varchar(255), billingAddress varchar(255) );

CREATE TABLE Order( orderId int primary key, customerId int, shippingAddress varchar(255), billingAddress varchar(255), foreign key (customerId) references Customer(customerId) );

INSERT INTO product VALUES(1,pratap,red,yes/no,1-10,0-5000,10-100%,bang,5000);

INSERT INTO Customer VALUES(1,pratap,walikar,[email protected],0116ghc,29/02/1990,bang,31646181,yes,M);

INSERT INTO seller VALUES(1,pratap,walikar,[email protected],16654ghc,29/02/1990,bang,bang,);

INSERT INTO order VALUES(1,2,bang,bang,);

2) CREATE TABLE Category( categoryId int primary key, name varchar(255), description text );

ProductCategory( productId int, categoryId int, foreign key (productId) references Product(productId), foreign key (categoryId) references Category(categoryId), primary key (productId, categoryId));

Subcategory( subcategoryId int primary key, categoryId int, name varchar(255), description text, foreign key(categoryId) references Category(categoryId));

CREATE TABLE segment( segmentId int primary key, name varchar(255), description text );

ProductSegment( productId int, segmentId int, foreign key (productId) references Product(productId), foreign key (segmentId) references Segment(segmentId), primary key (productId, segmentId));

CREATE TABLE Tag( tagId int primary key, name varchar(255));

ProductTag( productId int, tagId int, foreign key (productId) references Product(productId), foreign key (tagId) references Tag(tagId), primary key (productId, tagId));

CREATE TABLE Cart( cartId int primary key, customerId int, foreign key (customerId) references Customer(customerId));

CartItem( cartItemId int primary key, cartId int, productId int, quantity int, foreign key (cartId) references Cart(cartId), foreign key (productId) references Product(productId));

3)1)SELECT a.ord_no,a.purch_amt,
b.cust_name,b.city
FROM orders a,customer b
WHERE a.customer_id=b.customer_id
AND a.purch_amt BETWEEN 500 AND 2000;

2)RENAME TABLE product TO prod;

3)SELECT Customer_Name
FROM Customer
WHERE
EXISTS (SELECT Customer_Name,prod_Name
FROM Customer
WHERE Customer.Customer_No = product.Customer_No AND product.prod_name = prod_Purchase.prod_No);

4) Customers (customer_fnameA,customer_lnameA)
Orders (order_id, order_date, customer_id)
Order_details (order_id, item_id)
Items (item_id);

5)SELECT * FROM orders a WHERE purch_amt >1000 (SELECT AVG(purch_amt) FROM orders b WHERE b.customer_id = a.customer_id);

6) ALTER TABLE Customers
INSERt Email varchar(255);