Notes

Create database Assignment

create Table Address(address_id int primary key not null,client_id int,street nvarchar(50),number bigint,zip_code int,
state nvarchar(50),country_id int)

Alter table Address add foreign key (client_id) references Client(client_id)
Alter table Address add foreign key (country_id) references Country(country_id)



insert into Address(address_id,client_id,street,number,zip_code,state,country_id) values
(360,101,'Deckers Lane',9123721390,743145,'West Bengal',91),
(361,102,'Melody Drive',9804235308,700031,'California',82),
(362,103,'James Street',7278862507,700032,'Ontario',73),
(363,104,'Thyme Avenue',8481804425,700033,'Queensland',64),
(364,105,'Morton Street',6291877490,700034,'Auckland',55),
(365,106,'Hummocky Road',7003713647,700035,'Dhaka',46),
(366,107,'Kent Drive',9432657123,700036,'Pennsylvania',37),
(367,108,'White Pine Lane',8013436978,700037,'Virginia',28),
(368,109,'Alexandra Road',9823764550,700038,'Sindh',19),
(369,110,'Glenwood Avenue',9198237465,700039,'Ohio',00)


create table Country(country_id int primary key not null, name nvarchar(50))



insert into Country(country_id,name) values
(91,'India'),
(82,'United States'),
(73,'Canada'),
(64,'Australia'),
(55,'New Zealand'),
(46,'Bangladesh'),
(37,'United States'),
(28,'United States'),
(19,'Pakistan'),
(00,'United States')

create table Client(client_id int primary key not null,first_name nvarchar(50),last_name nvarchar(50),age int)

insert into Client(client_id,first_name,last_name,age) values
(101,'Deep','Mukherjee',22),
(102,'Som','Banerjee',21),
(103,'Raj','Chatterjee',19),
(104,'Deb','Roy',29),
(105,'Akash','Sarkar',42),
(106,'Harsh','Gupta',34),
(107,'Aniket','Basu',26),
(108,'Joy','Dey',25),
(109,'Supriyo','Halder',32),
(110,'Kingshuk','Das',30)

create table Phone(phone_id int primary key not null,client_id int,country_code int,phone bigint)

alter table Phone add foreign key (client_id) references Client(client_id)

insert into Phone(phone_id,client_id,country_code,phone) values
(1,101,91,9123721390),
(2,102,82,9804235308),
(3,103,73,7278862507),
(4,104,64,8481804425),
(5,105,55,6291877490),
(6,106,46,7003713647),
(7,107,37,9432657123),
(8,108,28,8013436978),
(9,109,19,9823764550),
(10,110,00,9198237465)


create table Orders(order_id int primary key not null,client_id int, date date)

alter table Orders add foreign key (client_id) references Client(client_id)


insert into Orders(order_id,client_id,date)values
(111,101,'2022-01-20'),
(112,102,'2022-02-21'),
(113,103,'2022-03-22'),
(114,104,'2022-04-23'),
(115,105,'2022-05-24'),
(116,106,'2022-06-25'),
(117,107,'2022-07-26'),
(118,108,'2022-08-27'),
(119,109,'2022-09-28'),
(120,110,'2022-10-29')

create table Item(item_id int primary key not null,order_id int,description nvarchar(50),value int,amount int)

alter table Item add foreign key (order_id) references Orders(order_id)


insert into Item values
(1,111,'Microwave Oven',10000,20000),
(2,112,'TV',25000,50000),
(3,113,'Washing Machine',30000,60000),
(4,114,'Phone',40000,80000),
(5,115,'PS5',50000,100000),
(6,116,'Laptop',60000,120000),
(7,117,'Camera',70000,140000),
(8,118,'Bike',80000,160000),
(9,119,'Car',90000,180000),
(10,120,'Vaccuum Cleaner',8000,16000)

/*Queries for Update,Delete and Select */

UPDATE Item SET value='45000',amount='90000' where item_id='4'

DELETE FROM Item where order_id='120'

SELECT * FROM item
SELECT street,state FROM Address
SELECT Distinct state from Address