Project 1

CREATE DATABASE swagindia;
USE swagindia;
SHOW TABLES ;

CREATE TABLE customer (
customer_id INT PRIMARY KEY ,
customer_name CHAR ( 10) NOT NULL ,
Phon_number INT NOT NULL ,
customer_city CHAR (20) ,
customer_country CHAR( 20) ,
pincode INT ,
email CHAR (30) NOT NULL ,
premimum CHAR(1)
);

INSERT INTO customer VALUES ( 1001, 'shubhas' , 80000200,'hariyana', 'india' , 230405, '[email protected]', 'y ' ) ;
INSERT INTO customer VALUES ( 1002, 'shubham ' , 90900200,'panjab', 'india' , 230405, '[email protected]', 'n' ) ;
INSERT INTO customer VALUES ( 1003, 'shubh' , 80000400,'uttar pradesh ', 'india' , 230405, '[email protected]', 'y ' ) ;
INSERT INTO customer VALUES ( 1004, 'rakesh', 80800809, 'allahabad' , 'America', 809990 ,'[email protected] ', 'n');

CREATE TABLE seller (
seller_id INT PRIMARY KEY ,
NAME CHAR(20 ) ,
size_available CHAR(10),
city CHAR(30),
cost DECIMAL(10,2),
quantity_available INT ,
color CHAR( 10),
prod_id INT,
FOREIGN KEY (prod_id ) REFERENCES products(prod_id)
);

INSERT INTO seller VALUES (2001, 'T-shirt', 'xxl ' , 'hariyana' , 200, 20, 'red',102);
INSERT INTO seller VALUES (2002, 'T-shirt', 'xl ' , 'noida' , 250, 20, 'white',101);


CREATE TABLE adminstrator (
admin_id INT PRIMARY KEY ,
admin_name CHAR(20)
);

INSERT INTO adminstrator VALUES ( 101, 'harprite' );

CREATE TABLE products (
current_discount INT,
product_id INT PRIMARY KEY ,
brand_name CHAR( 20 )
);

INSERT INTO products VALUES ( 500, 101,'nice' ),
( 200,102,'overlays'),
(300, 103, 'livies'),
(400, 104 , 'calvin');


CREATE TABLE categories(
ctr_id INT PRIMARY KEY ,
color CHAR ( 30),
product_id INT ,
FOREIGN KEY ( product_id ) REFERENCES products( product_id) ,
age INT ,
gender CHAR(1) ,
ctr_name CHAR ( 200 ) ,
ctr_prise DECIMAL ( 10, 2)
);

INSERT INTO categories VALUES ( 3001,'red',101,12, 'm' ,'t_shirt',500.00),
( 3002, 'green' , 102, 22, 'f', 'shirt',400.00);

CREATE TABLE wishlist (
wishlist_id INT PRIMARY KEY ,
customer_id INT ,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ,
ctr_id INT ,
FOREIGN KEY ( ctr_id) REFERENCES categories(ctr_id)
);
INSERT INTO wishlist VALUES ( 201, 1001, 3001) , ( 202, 1002, 3002 ) ;

CREATE TABLE cart (
cart_id INT PRIMARY KEY ,
customer_id INT ,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ,
ctr_id INT ,
FOREIGN KEY ( ctr_id) REFERENCES categories(ctr_id)

);
SELECT * FROM cart
INSERT INTO cart VALUES ( 301, 1002, 3002) , ( 302, 1002, 3002 ) ;

CREATE TABLE payment (
payment_id INT PRIMARY KEY ,
customer_id INT,
FOREIGN KEY (customer_id ) REFERENCES customer(customer_id) ,
cart_id INT ,
FOREIGN KEY ( cart_id ) REFERENCES cart(cart_id)
) ;

SELECT *FROM payment
INSERT INTO payment VALUES( 501, 1001,301)

CREATE TABLE orders (
order_id INT PRIMARY KEY ,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(cust_id),
payment_id INT ,
FOREIGN KEY ( payment_id) REFERENCES payment( payment_id)
);
ALTER TABLE orders ADD order_amount INT ;
INSERT INTO orders VALUES ( 601, 1001, 501)

CREATE TABLE recived (
recived_id INT PRIMARY KEY ,
customer_id INT,
FOREIGN KEY (customer_id ) REFERENCES customer(customer_id),
order_id INT ,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);


CREATE TABLE reviews (
reviews_id INT PRIMARY KEY ,
product_stars INT ,
customer_id INT ,
FOREIGN KEY ( customer_id ) REFERENCES customer(customer_id)
);

INSERT INTO reviews VALUES (701,5, 1001);

1
SELECT o.order_id, o.brand, o.payment , c.customer_name , c.customer_city , c.customer_country, c.customer_id FROM orders o INNER JOIN customer c USING (customer_id )

2
ALTER TABLE products RENAME COLUMN product_id TO prod_id;

3

SELECT *FROM orders WHERE customer_id IS NOT NULL ;

4
SELECT *FROM customer LEFT JOIN orders USING (customer_id ) WHERE customer_name LIKE '%a%'AND order_id IS NOT NULL ;
5
SELECT *FROM customer LEFT JOIN orders USING (customer_id ) WHERE order_id IS NOT NULL AND order_amount>=10000;
6 .

CREATE TABLE procedures(
procedure_id INT PRIMARY KEY
);
INSERT INTO procedures VALUES (001);
ALTER TABLE procedures ADD pro_name CHAR (20) ;