tablecreation.sql

create database practiseadvancesql 
use practiseadvancesql

create table customers(
customerid int primary key ,
firstname varchar(50) not null ,
lastname varchar(50),
email varchar(100) unique,
country varchar(50),
createddate date
);
select * from customers

create table orders (
orderid int primary key ,
orderdate date,
customerid int not null,
sales decimal(10,2),
constraint fk_orders_customers
foreign key (customerid)
references customers(customerid)
);

insert into customers values 
(1, 'Anjali', 'Kolambkar', 'anjali@gmail.com', 'canada', '2026-05-27'),
(2, 'Abha', 'Gosain', 'abha@gmail.com', 'usa', '2026-03-30'),
(3, 'Hetsi', 'Tewar', 'hetsi@gmail.com', 'canada', '2026-06-3'),
(4, 'Sakshee', 'Kulkarni', 'sakshee@gmail.com', 'India', '2026-05-15'),
(5, 'Manali', 'Patel', 'manali@gmail.com', 'london', '2026-02-2');

insert into orders values 
(101, '2026-03-23', 1, 500),
(102, '2026-07-2', 2, 400),
(103, '2026-11-11', 3, 300),
(104, '2026-12-7', 4, 100),
(105, '2026-08-28', 5, 200);

create table products(
productid int primary key, 
productname varchar(50),
price decimal(10,2) check (price > 0)
);

create table orderdetails (
orderdetailid int primary key ,
orderid int ,
productid int,
quantity int check(quantity > 0),

constraint fk_od_orders
foreign key(orderid)
references orders(orderid) , 

constraint fk_od_products 
foreign key (productid)
references products(productid)
);
 
create table payments ( 
paymentid int primary key ,
orderid int ,
amount decimal(10,2),
paymentdate date,

constraint fk_payments_orders
foreign key(orderid)
references orders(orderid)
);

insert into products values 
(1, 'Laptop', 50000),
(2, 'Mobile', 20000),
(3, 'Headphones', 2000),
(4, 'Keyboard', 1500),
(5, 'Mouse', 800);

insert into orderdetails values 
(1, 101, 1, 1), 
(2, 101, 5, 2), 
(3, 102, 2, 1), 
(4, 103, 3, 2), 
(5, 104, 4, 1); 

INSERT INTO Payments VALUES
(1, 101, 51600, '2026-03-23'),
(2, 102, 20000, '2026-03-22'),
(3, 103, 4000, '2026-03-21'),
(4, 104, 1500, '2026-03-20'),
(5, 105, 200, '2026-03-19');