Notes

--> Creating Database
Create database AllDetails

--> Creating all the tables , inserting values and having primary and foreign keys
Create table Address
(
address_id int Primary key ,
client_id int ,
streat nvarchar(20),
number nvarchar(20),
zip_code nvarchar(10),
state nvarchar(10),
country_id int
)

Insert into Address(address_id,client_id,streat,number,zip_code,state,country_id)
values( 1 , 101 , 'S.V Road' , '9087612345' , '700051' , 'WestBengal',201),
( 2 , 102 , 'S.V Road' , '6789123456' , '700123' , 'WestBengal',202),
( 3 , 103 , 'M.G Road' , '9000012345' , '700059' , 'WestBengal',203),
( 4 , 104 , 'R.D Road' , '9087612000' , '700055' , 'WestBengal',204),
( 5 , 105 , 'Gandhi Road' , '9080012345' , '700951' , 'WestBengal',205),
( 6 , 106 , 'Airport Road' , '9087600345' , '700678' , 'WestBengal',206),
( 7 , 107 , 'M.M Road' , '7897612345' , '700890' , 'WestBengal',207),
( 8 , 108 , 'Sukanta Road' , '6577612345' , '700129' , 'WestBengal',208),
( 9 , 109 , 'Station Road' , '8997612345' , '700789' , 'WestBengal',209),
( 10 , 110 , 'P.R Road' , '5677612345' , '700891' , 'WestBengal',210)


Create table Client
(
client_id int Primary Key ,
first_name nvarchar(20) ,
last_name nvarchar(20),
age int
)
Insert into Client(client_id,first_name,last_name,age)
values(101 , 'Ritu','Saha',23),
(102 , 'Madhu','Saha',28),
(103 , 'Arijit','Sen',53),
(104 , 'Ram','Datta',30),
(105 , 'Sam','Das',27),
(106 , 'Tom','Kumar',45),
(107 , 'Kaul','Roy',49),
(108 , 'Komal','Dey',60),
(109 , 'Raj','Kumar',40),
(110 , 'Arun','Saha',50)

Alter table Address
add constraint
idfk foreign key(client_id)references Client(client_id)

Create table country
(
country_id int Primary Key ,
name nvarchar(20)
)

Insert into country(country_id,name)
values(201,'India'),
(202,'India'),
(203,'India'),
(204,'India'),
(205,'India'),
(206,'India'),
(207,'India'),
(208,'India'),
(209,'India'),
(210,'India')

Alter table Address
add constraint
countryidfk foreign key(country_id)references country(country_id)

Create table Phone
(
phone_id int ,
client_id int Primary Key ,
country_code int ,
phone nvarchar(20)
)
Insert into Phone(phone_id,client_id,country_code,phone)
values( 301 ,101, 401 , '907612345'),
( 302 ,102, 402 , '907612300'),
( 303 ,103, 403 , '707612345'),
( 304 ,104, 404 , '807612345'),
( 305 ,105, 405 , '607612345'),
( 306 ,106, 406 , '887612345'),
( 307 ,107, 407 , '667612345'),
( 308 ,108, 408 , '677612345'),
( 309 ,109, 409 , '767612345'),
( 310 ,110, 410 , '757612345')

Alter table Client
add constraint
phoneidfk foreign key(client_id)references Phone(client_id)

Create table Orders
(
order_id int ,
client_id int Primary Key ,
date nvarchar(20)
)
Insert into Orders(order_id,client_id,date)
values(101,501,'21/11/2022'),
(102,502,'22/01/2022'),
(103,503,'01/09/2022'),
(104,504,'02/11/2022'),
(105,505,'09/10/2022'),
(106,506,'21/02/2023'),
(107,507,'21/10/2023'),
(108,508,'01/01/2023'),
(109,509,'31/01/2023'),
(110,510,'08/11/2022')
Alter table Client
add constraint
Ordersidfk foreign key(client_id)references Orders(client_id)

Create table item(
item_id int ,
order_id int Primary key ,
description nvarchar(20),
value int ,
amount int
)
Insert into item(item_id,order_id,description,value,amount)
values(601,101,'TV',10,1000),
(602,102,'Micro-Oven',20,2000),
(603,103,'Radio',10,700),
(604,104,'Mobile',60 , 9000),
(605,105,'Gas-Oven',8,8000),
(606,106,'Clothes',11,7000),
(607,107,'Washing-Machine',9,1100),
(608,108,'Table',9,900),
(609,109,'Chair',6,300),
(610,110,'Lamp',1,100)

Alter table Orders
add constraint
Itemidfk foreign key(order_id)references item(order_id)

--> Doing Operation - Select , Delete,Update
Select * from Address

Select * from Address where state = 'WestBengal'

Delete from Address where address_id=10

Update item
set description='Study-Lamp'
where item_id=610

--> Without Parameters sp , joining 3 tables

Create Procedure spOrderDetail
as
begin
Select a.address_id , a.client_id, a.streat , a.number ,a.zip_code, a.state , a.country_id,c.first_name ,c.last_name , c.age , o.order_id , o.date
from Address a full join Client c on a.client_id = c.client_id full join Orders o on o.client_id = c.client_id
end

spOrderDetail

--> With Parameters sp , joining 2 tables

Create Procedure spOrder_Item
@client_id int ,
@description nvarchar(20) Output
as
begin
Select @description = i.description from Orders o full join item i on o.order_id = i.order_id
where o.client_id = @client_id

end

Declare @d nvarchar(20)
Execute spOrder_Item 501 , @d out
Print 'Description '+@d