Notes

Kumar Shubham Milestone


1)
create database Client_db;
use Client_db;

create table Address(address_id int 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 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 Phone(phone_id int primary key,
client_id int, country_code int, phone varchar(50));

create table [Order] (order_id int primary key,
client_id int, [date] date);

create table item(item_id int primary key,
order_id int, description varchar(50), value int, amount int);


2)
Alter table Address
add constraint fk_client foreign key(client_id) references Client(client_id);

Alter table Address
add constraint fk_country foreign key(country_id) references Country(country_id);

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

Alter table [Order]
add constraint fk_ordercli foreign key(client_id) references Client(client_id);

Alter table Item
add constraint fk_item foreign key(order_id) references [Order](order_id)

3)
insert into Client values(1,'Jony','Mathews',26)
insert into Client values(2,'Sarena','Williams',28)
insert into Client values(3,'Novak','Dojovik',22)
insert into Client values(4,'Sachin','Tendulkar',32)
insert into Client values(5,'KL','Rahul',31)
insert into Client values(6,'Mohammed','Siraj',33)
insert into Client values(7,'Shreya','Mishra',34)
insert into Client values(8,'Jhon','Cuber',35)
insert into Client values(9,'Jeniffer','Lawrence',23)
insert into Client values(10,'Tiger','Woods',44)


insert into Country values(1,'India')
insert into Country values(2,'Ukraine')
insert into Country values(3,'Pakistan')
insert into Country values(4,'Switzerland')
insert into Country values(5,'Sri Lanka')
insert into Country values(6,'Afganistan')
insert into Country values(7,'Ireland')
insert into Country values(8,'South Africa')
insert into Country values(9,'Nepal')
insert into Country values(10,'Mayanmmar')

insert into Address values(1,1,'7th Arjuna',217, 201308,'Uttar Pradesh',1)
insert into Address values(2,2,'8th wingston',216,201309,'New York',5)
insert into Address values(3,3,'9th Harbour',215,10077,'Chekosolo',5)
insert into Address values(4,4,'10 Pragaritu',218,89898,'Maharastra',8)
insert into Address values(5,5,'9th Talchar',219,8777,'Pearth',9)
insert into Address values(6,6,'11th kaniya',220,17777,'sydney',6)
insert into Address values(7,7,'9th Zebra',211,76777,'Andhra',1)
insert into Address values(8,8,'10 wall ',214,79977,'lahore',6)
insert into Address values(9,9,'11th Cream',223,45556,'Paris',8)
insert into Address values(10,10,'12th State',244,30134,'ghana',9)

insert into Phone values(1,1,91,'9717204810')
insert into Phone values(2,2,41,'8654555633')
insert into Phone values(3,3,42,'8178849864')
insert into Phone values(4,4,43,'9868006890')
insert into Phone values(5,5,33,'3456789434')
insert into Phone values(6,6,61,'3445677778')
insert into Phone values(7,7,88,'4344556644')
insert into Phone values(8,8,99,'4556778773')
insert into Phone values(9,9,86,'9765555444')
insert into Phone values(10,10,87,'6789878934')

insert into [Order] values(1,3,'01-01-23')
insert into [Order] values(2,4,'04-02-23') -- done
insert into [Order] values(3,5,'08-03-23')
insert into [Order] values(4,6,'16-04-23')
insert into [Order] values(5,7,'30-05-23')
insert into [Order] values(6,8,'04-06-23')
insert into [Order] values(7,9,'06-07-23')
insert into [Order] values(8,1,'08-08-23')
insert into [Order] values(9,2,'10-09-23')
insert into [Order] values(10,3,'14-10-23')

insert into item values(1,11,'Toffe',12,100)
insert into item values(2,12,'Choclate',13,400)
insert into item values(3,13,'Chow',14,200)
insert into item values(4,14,'Oreo',15,89)
insert into item values(5,15,'Britania',16,94)
insert into item values(6,16,'surf',17,84)
insert into item values(7,17,'soap',18,310)
insert into item values(8,18,'Brush',19,64)
insert into item values(9,19,'Shampoo',20,350)
insert into item values(10,20,'Patanjali',26,340)
4)
Update>>
Update Country
set name='Behrain' where country_id=4;

Delete>>
delete from item
where item_id=10

Select>>
select client_id,name as Country_Name from Address inner join Country on Address.country_id=Country.country_id
5)
CREATE PROCEDURE SP_ORDER_CLIENT_ADDRESS
AS
BEGIN
SELECT
a.address_id, c.first_name, o.date from
Address a join client c on a.client= c.client_id
join Order o on o.client_id = a.client_id
END
EXEC SP_ORDER_CLIENT_ADDRESS
6)
CREATE PROCEDURE SP_Para_Name @street varchar(20), @order_id int
AS
BEGIN
SELECT a.address_id, a.street, b.order_id, b.date
From Address a join order b on a.client_id=b.client_id
where a.street= @street
and b.order_id= @order_id
END
declare @street varchar(20)
exec SP_Para_Name