Notes

create table client(client_id int not null primary key, first_name varchar(20),last_name varchar(20),age int)

create table [order] (order_id int not null primary key,client_id int foreign key references client(client_id) ,[date] date)

create table country(country_id int not null primary key,[name] varchar(20))

create table phone(phone_id int,client_id int foreign key references client(client_id),country_code int,phone int)

create table item(item_id int not null primary key,order_id int foreign key references [order](order_id)
,[description] varchar(20),[value] int,amount int)

create table [address] (address_id int not null primary key,client_id int foreign key references client(client_id) , street varchar(20),
number int, zip_code int,[state] varchar(20),country_id int foreign key references country(country_id ))

insert into client values(1,'sam','j',20)
insert into client values(2,'pam','j',23)
insert into client values(3,'tod','j',22)
insert into client values(4,'ram','j',22)
insert into client values(5,'ted','j',24)
insert into client values(6,'keit','j',21)
insert into client values(7,'scot','j',22)
insert into client values(8,'tina','j',21)
insert into client values(9,'alina','r',22)
insert into client values(10,'stiles','g',21)

insert into [order] values(1,2,'02-02-2022')
insert into [order] values(2,4,'05-07-2022')
insert into [order] values(3,6,'11-10-2022')
insert into [order] values(4,3,'09-06-2022')
insert into [order] values(5,7,'10-04-2022')

insert into phone values(1,1,2,1234)
insert into phone values(2,2,1,1213)
insert into phone values(3,3,1,1222)
insert into phone values(4,4,2,1243)
insert into phone values(5,5,1,1211)
insert into phone values(6,6,2,1221)
insert into phone values(7,7,2,1211)
insert into phone values(8,8,1,1321)
insert into phone values(9,9,1,1311)
insert into phone values(10,10,1,1311)

insert into item values(1,3,'lamp',2,500)
insert into item values(2,4,'table',1,900)
insert into item values(3,3,'laptop',1,50000)
insert into item values(4,5,'headphones',2,3000)
insert into item values(5,2,'tv',1,5000)

insert into [address] values(1,1,'1main',1234,208,'a',1)
insert into [address] values(2,2,'2main',1213,205,'s',2)
insert into [address] values(3,3,'3main',1222,202,'w',1)
insert into [address] values(4,4,'4main',1243,208,'a',1)
insert into [address] values(5,5,'5main',1211,203,'q',2)
insert into [address] values(6,6,'6main',1221,201,'e',2)
insert into [address] values(7,7,'7main',1211,202,'w',1)
insert into [address] values(8,8,'8main',1321,205,'s',2)
insert into [address] values(9,9,'9main',1331,205,'s',2)
insert into [address] values(10,10,'10main',1311,203,'q',2)

insert into country values(1,'usa')
insert into country values (2,'uk')

select * from client
select * from [address]
select * from phone
select * from [order]
select * from item
select * from country

delete from client where first_name = 'tod'

update client set last_name = 'u' where client_id = 2


create procedure allinfo
as
begin
select * from address join client on address.client_id = client.client_id
join [order] on [order].order_id = client.order_id
end

execute allinfo

create procedure spOrderInfo
{
@client_id int,
@des varchar(20) output
as
begin
select @des = item.description from order join item on order.order_id = item.order_id where order.client_id = @client_id
end
declare @a varchar(20)
execute spOrderInfo 208,@d out
print 'description'+@a