Notes

create database SQLAssigNment
USE SQLAssigNment

create table Address
(
address_id int primary key,
client_id int,
street nvarchar(50),
number int,
zip_code int,
state nvarchar(20),
country_id int
)

insert into Address
values
(101,2315,'SIWAN',2,657091,'rajasthan',1),
(102,2311,'Pachhrukhi',5,841009,'pune',1),
(103,2318,'mairwa',21,4003,'banglore',3),
(104,2320,'church Street',88,22611,'poland',2),
(105,2313,'Wehnsa',2320,48933,'New delhi',2),
(106,2317,'boring Road',611,54671,'pakistan',4),
(107,2314,'gali ',231,2007,'america',3),
(108,2312,'hasanp',331,1212,'bihar',5),
(109,2316,'mg road',8,800054,'Uttrakhand',1),
(110,2319,'patna',681,4005,'switerzerland',3)


create table Client
(
client_id int primary key,
first_name nvarchar(20),
last_name nvarchar(20),
age int
)

insert into Client
values
(2311,'ankesh','Kumar',56),
(2312,'jam','lee',27),
(2313,'akash','pathan',23),
(2314,'ajit','yadav',31),
(2315,'Ankit','kumar',28),
(2316,'punit','kushwaha',42),
(2317,'shekhar','suman',38),
(2318,'prakash','kumar',26),
(2319,'hemant','singh',43),
(2320,'sujal','singh',23)


create table Orders
(
order_id int primary key,
client_id int,
date datetime
)


insert into Orders
values
(1,2313,'2022-12-25'),
(2,2317,'2022-12-28'),
(3,2314,'2022-12-30'),
(4,2313,'2023-01-21'),
(5,2318,'2023-01-26'),
(6,2317,'2023-01-31'),
(7,2320,'2023-02-03'),
(8,2317,'2023-02-15'),
(9,2312,'2023-02-27'),
(10,2311,'2023-02-28'),
(11,2319,'2023-01-12'),
(12,2311,'2023-01-14')


create table Item
(
item_id int primary key,
order_id int,
description nvarchar(50),
value int,
amount int
)

insert into item
values
(1001,3,'fan',1,35000),
(1002,6,'airpod',1,500),
(1003,1,'mobile',4,2000),
(1004,12,'pen',2,4000),
(1005,7,'bottle',1,5000),
(1006,2,'book',1,75000),
(1007,8,'pen',1,70000),
(1008,10,'laptop',2,2500),
(1009,4,'charger',1,800),
(1010,11,'watch',1,20000),
(1011,5,'freez',5,6000),
(1012,9,'washing',3,4500)


create table Phone
(
phone_id int primary key,
client_id int,
country_code int,
phone nvarchar(20)
)

insert into Phone
values
(50,2315,1,'9076542458'),
(51,2311,1,'7076567310'),
(52,2318,3,'8167996433'),
(53,2320,2,'906942290'),
(54,2313,2,'804654280'),
(55,2317,4,'922358975'),
(56,2314,3,'89753578'),
(57,2312,5,'7890653389'),
(58,2316,1,'8507842456'),
(59,2319,3,'8677547996')


create table Country
(
country_id int primary key,
name nvarchar(20)
)


insert into Country
values
(1,'america'),
(2,'india'),
(3,'pakistan'),
(4,'ukrain'),
(5,'Japan'),
(6,'poland'),
(7,'China'),
(8,'Germany'),
(9,'iran'),
(10,'southAfrica')


ALTER TABLE Address
ADD CONSTRAINT FK_ClientAddress
FOREIGN KEY (client_id) REFERENCES Client(client_id);

ALTER TABLE Address
ADD CONSTRAINT FK_CountryAddress
FOREIGN KEY (country_id) REFERENCES Country(country_id);

ALTER TABLE Phone
ADD CONSTRAINT FK_ClientPhone
FOREIGN KEY (client_id) REFERENCES Client(client_id);

ALTER TABLE Orders
ADD CONSTRAINT FK_ClientoOder
FOREIGN KEY (client_id) REFERENCES Client(client_id);

ALTER TABLE Item
ADD CONSTRAINT FK_OrderItems
FOREIGN KEY (order_id) REFERENCES Orders(order_id);

UPDATE Orders
SET date='2022-03-10'
WHERE order_id=11;

select * from Orders
Delete from Orders
where order_id=12

select * from Country
select * from Client
select * from Orders
select * from Phone
select * from Address
select * from item


create Procedure SP_GetDetails
AS
Begin
select X.address_id,Y.first_name,Y.last_name,Z.order_id,Z.date
from Client Y
inner join Address X
on X.client_id=Y.client_id
inner join Orders Z
on Y.client_id=Z.client_id
END

EXEC SP_GetDetails

CREATE PROCEDURE SP_GetDescriptnItm1
(
@itemID int
)
AS
BEGIN
SELECT X.first_name,X.last_name,Y.description,Y.amount,Z.order_id
FROM Orders Z
join Client X on Z.client_id=X.client_id
join Item Y on Z.order_id=Y.order_id
WHERE Y.item_id=@itemID
END

EXEC SP_GetDescriptnItm1 1002;