Project 1

CREATION OF TABLES:

create database Ecommerce;
use Ecommerce;

create table admin(
admin_id int,
admin_name char(20),
email_address varchar(30),
password varchar(20)
);

create table products(
prod_id int,
prod_name varchar(100),
prod_warranty varchar(100),
colour varchar(50),
cost int,
sizes varchar(100),
stock_status varchar(100)
);

create table customers(
cust_id int,
cust_name varchar(100),
gender varchar(10),
password varchar(100),
email varchar(10),
contact int,
delivery_add varchar(100)
);

create table orders(
order_status varchar(100),
delivery_time varchar(20)
);

create table billing(
bill_id int,
bill_date int,
card_type varchar(100),
card_no int,
delivery_date int
);

create table purchase_record(
purchase_id int,
quantity int,
price varchar(100),
discount varchar(100),
status varchar(100)
);

create table category(
cat_id int,
cat_name varchar(100)
);

create table sub_category(
sub_catid int,
sub_catname varchar(100)
);

create table cancellation(
cancel_id int,
cancel_date varchar(100),
refund_amount varchar(100)
);

create table feedback(
feedback_id int,
prod_id int,
cust_id int,
rating varchar(10),
foreign key(prod_id) references product(prod_id),
foreign key(cust_id) references customer(cust_id)
);

INSERTING VALUES

insert into admin values(1,"Irish","[email protected]","irishirila");

insert into category values('1011');

insert into Customer values('cid100','ABCM1235','rajat','G-453','632014',9893135876, 'crt1011');

insert into Seller values('sid100','12345','aman','delhi cmc');

insert into Product values('pid1001','jeans','red','32','M',10,10005,20,'sid100');

insert into Seller_Phone_num values('9943336206','sid100');

insert into Cart_item values(3,to_date('10-OCT-1999','dd-mon-yyyy'),'crt1011','pid1001','Y');

insert into Payment values('pmt1001',to_date('10-OCT-1999','dd-mon-yyyy'),'online','cid100','crt1011',NULL);

TASK 3

1) select name,id from customers order by city;

select * from customers where city="Nagercoil";

2) alter table products rename column product to prod_;

3) select customer_id,min(purch_amt) from orders;

4) select * from customers where name like "a%a";

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

PROCEDURE :

* Create database named Ecommerce

* use Ecommerce database

* To create tables , we have to use the syntax:

create table tablename(

);

Inside this we can give the needed column names with the specific datatype ie) int,varchar, char ...

* Now to insert values in the particular table , the syntax is

insert into tablename(column 1, column2,...) values(val1 ,val1,...);

*To alter the table,

the alter table statement is usef to add, delete and modify columns in an existing table.

the syntax is :

TO ADD:

alter table tablename add column column_name datatype;

To DROP:

alter table tablemname drop column column_name;

TO MODIFY :

alter table tablename modify column columname datatype;