Notes

----------creating the database--------------------------

create database SQLTest

-----------------creating the required tables------------

create table Client(
client_id int primary key,
first_name varchar(50),
last_name varchar(50),
age int
)

create table Country(
country_id int primary key,
"name" varchar(50)
)

create table "Adrdress"(
address_id int primary key,
client_id int foreign key references Client(client_id),
street varchar(50),
number int,
zip_code int,
"state" varchar(50),
country_id int foreign key references Country(country_id)
)

create table Phone(
phone_id int primary key,
client_id int foreign key references Client(client_id),
country_code int,
phone int
)

create table [Order](
order_id int primary key,
client_id int foreign key references Client(client_id),
"date" date
)

create table Item(
item_id int primary key,
order_id int foreign key references [Order](order_id),
"description" varchar(50),
"value" int,
amount int
)

-------------------------Inserting values in the created tables------------------------

insert into Client (client_id,first_name,last_name,age)
values(1,'Rohit','Sharma',32),
(2,'Raj','Mehta',29),
(3,'Sachin','Gupta',34),
(4,'Ankita','Sharma',24),
(5,'Anil','Kumar',39)

insert into Client (client_id,first_name,last_name,age)
values
(6,'Rohit','Singh',43),
(7,'Asif','Khan',28),
(8,'Virat','Kohli',33),
(9,'Anushka','Sharma',30),
(10,'Rishabh','Pant',25)

insert into Country(country_id,"name")
values(1,'India'),
(2,'Australia'),
(3,'England'),
(4,'Argentina'),
(5,'Spain')

insert into Country(country_id,"name")
values
(6,'Brazil'),
(7,'Netherlands'),
(8,'Mexico'),
(9,'France'),
(10,'Germany')

insert into "Adrdress"(address_id,client_id,street,number,zip_code,"state",country_id)
values
(1,2,'Floyd Street',21,42165,'Melbourne',2),
(2,1,'Camac Street',11,43465,'West Bengal',1),
(3,3,'Flint Street',13,23467,'Southampton',3),
(4,5,'Vamos Street',18,42763,'Mudos',5),
(5,4,'Diego Street',31,42199,'Buenos',4)

insert into "Adrdress"(address_id,client_id,street,number,zip_code,"state",country_id)
values
(6,7,'Cris Street',45,34544,'Bruks',6),
(7,6,'Block Street',23,43615,'Flynn',8),
(8,8,'Zc Street',44,28867,'Aeres',7),
(9,9,'Soi Street',11,42903,'Mundo',10),
(10,10,'Vym Street',23,42299,'Ohio',9)

insert into Phone(phone_id,client_id,country_code,phone)
values
(1,5,001,898123),
(2,1,002,898178),
(3,2,003,876590),
(4,3,004,898190),
(5,4,005,567890)

insert into Phone(phone_id,client_id,country_code,phone)
values
(6,10,006,893683),
(7,8,007,896678),
(8,7,008,898590),
(9,6,009,800990),
(10,9,0010,521890)

insert into "Order"(order_id,client_id,"date")
values
(1,2,'2023-02-12'),
(2,1,'2023-02-15'),
(3,4,'2023-02-19'),
(4,3,'2023-02-22'),
(5,5,'2023-02-22')

insert into "Order"(order_id,client_id,"date")
values
(6,7,'2023-02-26'),
(7,6,'2023-02-27'),
(8,9,'2023-02-21'),
(9,8,'2023-02-23'),
(10,10,'2023-02-24')

insert into Item(item_id,order_id,"description","value",amount)
values
(1,3,'item dispatched',5,500),
(2,2,'item dispatched',6,590),
(3,1,'item not dispatched yet',3,400),
(4,4,'item not dispatched',2,600),
(5,5,'item dispatched',8,1500)

insert into Item(item_id,order_id,"description","value",amount)
values
(6,10,'item dispatched',2,345),
(7,6,'item dispatched',6,1200),
(8,8,'item not dispatched yet',7,1400),
(9,7,'item not dispatched',2,400),
(10,9,'item dispatched',9,1600)

---------------select,update and delete queries------------------------------

select * from Country

select * from Client c
where c.first_name = 'Rohit'

select c.first_name , p.country_code from
Client c, Phone p where
c.client_id = p.client_id
order by c.first_name

Update Client
set first_name = 'Mohit'
where client_id = 3

delete from Country
where name = 'China'

-----------first stored procedure by joining Order,Client and Adrdress tables----------

create procedure spJoinOrderClientAddress
as
begin
select * from Adrdress a inner join
Client c on a.client_id = c.client_id
inner join "Order" o
on c.client_id = o.client_id
order by c.first_name
end

exec spJoinOrderClientAddress


---------parametrized stored procedure joining 2 tables---------

create procedure spGetClientNameByState
@State varchar(50)
as
begin
select concat(first_name,' ',last_name) as ClientName from
client c inner join "Adrdress" a
on c.client_id = a.client_id
where a."state" = @State
end

declare @stateName varchar(50)
set @stateName = 'West Bengal'
exec spGetClientNameByState @stateName