Notes

create table country
(
country_id int identity(1,1) primary key,
country_name varchar(50)
)
create table [address]
(
address_id int identity(1,1) primary key,
client_id int,
street varchar(50),
number int,
zip_code int,
[state] varchar(50),
country_id int
)
create table client
(
client_id int identity(1,1) primary key,
first_name varchar(50),
last_name varchar(50),
age int
)
create table phone
(
phone_id int identity(1,1) primary key,
client_id int ,
country_code int,
phone int
)
create table [order]
(
order_id int identity(1,1) primary key,
client_id int,
[date] date
)
create table item
(
item_id int identity(1,1) primary key,
order_id int,
[description] varchar(100),
[value] int,
amount int
)

alter table address
add constraint fk_countryId foreign key(country_id)
references country(country_id)
alter table address
add constraint fk_clientId foreign key(client_id)
references client(client_id)

alter table phone
add constraint fk_PhoneClientId foreign key (client_id)
references client(client_id)

alter table [order]
add constraint fk_OrderClientId foreign key (client_id)
references client(client_id)

alter table item
add constraint fk_itemOrderId foreign key(order_id)
references [order](order_id)

insert into country (country_name)
values
('India'),('China'),('Germany'),('Belgium'),('Austria'),
('Japan'),('Canada'),('France'),('Brazil'),('Italy')

insert into client(first_name,last_name,age)
values
('lena','smith',23),
('james','green',25),
('tom','taylor',35),
('david','king',40),
('lucy','white',29),
('john','smith',32),
('alen','lee',24),
('noah','grace',42),
('june','ford',33),
('mike','ross',38)

insert into [order](client_id,[date])
values
(1,'2022-02-21'),
(2,'2021-03-01'),
(3,'2021-01-30'),
(1,'2020-07-25'),
(4,'2022-09-19'),
(6,'2020-10-09'),
(9,'2021-01-28'),
(3,'2021-12-13'),
(9,'2020-11-06'),
(5,'2022-05-26')

insert into phone(client_id,country_code,phone)
values
(2,91,11112222),
(1,42,01013345),
(1,42,01013555),
(3,49,90909989),
(4,567,12123567),
(5,78,88880678),
(6,90,76543889),
(7,92,12347898),
(8,90,89765467),
(9,87,90854367),
(10,786,90865347)

insert into item (order_id,[description],[value],amount)
values
(1,'tv',2,10000),
(2,'microwave',5,20000),
(3,'washing machine',3,30000),
(4,'mobile',10,15000),
(5,'chimney',7,25000),
(6,'geyser',15,7000),
(7,'laptop',20,45000),
(8,'speaker',6,4000),
(9,'headphone',50,900),
(10,'charger',100,500)

select * from client
select * from country
insert into [address] (client_id,street,number,zip_code,[state],country_id)
values
(1,'22b',4,9090,'abc',11),
(2,'2b',56,1234,'abc',12),
(3,'65a',49,5786,'def',17),
(4,'234a',455,2467,'abc',11),
(5,'202a',36,8594,'hig',19),
(6,'222b',2,7494,'klm',15),
(7,'12b',987,1345,'hig',20),
(8,'99a',82,0735,'def',18),
(9,'101a',41,6421,'pqr',13),
(10,'56b',45,5565,'stu',12)

select * from item where amount>=15000
update [order] set [date]='2022-01-30' where client_id=2
insert into client (first_name,last_name,age) values ('rahul','shaw',23)
select * from client
delete from client where client_id=11
select * from client



create procedure spjoin
as
begin
select [address].address_id,[address].street,client.client_id,client.first_name,[order].[date]
from [address] inner join client
on [address].client_id=client.client_id inner join [order]
on client.client_id=[order].client_id
end
exec spjoin


create procedure spjoin1
@cid int
as
begin
select [address].address_id,[address].street,client.client_id,client.first_name
from [address] inner join client
on [address].client_id=client.client_id
where client.client_id=@cid
end

declare @cid int
exec spjoin1 1