Notes

create database practice
use practice

--CREATING TABLE SCHEMA--
create table order_items(
order_id int not null Primary Key,
product_id int,
quantity int
);

create table orders(
id int not null Primary Key,
user_id int,
status nvarchar(50),
created_at nvarchar(50)
);

create table products(
id int not null Primary Key,
merchant_id int,
name nvarchar(50),
price int,
status nvarchar(50),
created_at nvarchar(50)
);

create table merchants(
id int not null Primary Key,
merchant_name nvarchar(50),
admin_id int,
Country_code int,
created_at nvarchar(50)
);

create table users(
id int not null Primary Key,
full_name nvarchar(50),
email nvarchar(50),
gender nvarchar(50),
date_of_birth nvarchar(50),
Country_code int,
created_at nvarchar(50)
);

create table countries(
code int,
name nvarchar(50),
continent_name nvarchar(50)
);

insert into countries(code,name, continent_name)
values(61,'Australia','Australia'),
(880,'Bangladesh','Asia'),
(56,'Chile','South America'),
(33,'France','Europe'),
(91,'India','Asia')

insert into users(id,full_name,email,gender,date_of_birth,Country_code,created_at)
values(001,'Rory James','[email protected]','Female','23-09-1999',33,'18-10-22'),
(002,'Nathan Scott','[email protected]','Male','03-10-1989',91,'18-10-22'),
(003,'Lucas Danes','[email protected]','Male','13-03-1799',56,'18-10-22'),
(004,'Emma Jones','[email protected]','Female','21-07-1899',880,'18-10-22'),
(005,'Hazel Davis','[email protected]','Female','20-12-1998',61,'18-10-22')

insert into merchants(id,merchant_name,admin_id,Country_code,created_at)
values(101,'Lukes Diner',004,880,'18-10-22'),
(102,'Home Care',003,56,'18-10-22'),
(103,'Dragonfly INN',001,33,'18-10-22'),
(104,'Davis Bakery',005,61,'18-10-22'),
(105,'Scott car and services',002,91,'18-10-22')

insert into orders(id,user_id,status,created_at)
values(1001,001,'logged_in','18-10-22'),
(1002,004,'logged_in','18-10-22'),
(1003,002,'logged_out','18-10-22'),
(1004,003,'logged_in','18-10-22'),
(1005,005,'logged_out','18-10-22')

insert into products(id,merchant_id,name,price,status,created_at)
values(12341,104,'ASR009',345,'stocked','18-10-22'),
(12342,103,'ASR049',2345,'out_of_stock','18-10-22'),
(12343,101,'FGR003',340,'out_of_stock','18-10-22'),
(12344,102,'HJM005',782,'stocked','18-10-22'),
(12345,105,'FWR009',56,'stocked','18-10-22')

insert into order_items(order_id,product_id,quantity)
values(1003,12345,34),
(1002,12343,456),
(1004,12344,20),
(1001,12342,80),
(1005,12341,100)

--APPLYING FOREIGN KEYS--

alter table order_items
add constraint FK_ORDERS
foreign key (order_id) references orders(id)


alter table order_items
add constraint FK_PRODUCTS
foreign key (product_id) references products(id)


alter table orders
add constraint FK_USER
foreign key (user_id) references users(id)

alter table products
add constraint FK_merchants
foreign key (merchant_id) references merchants(id)


alter table merchants
add constraint FK_USERS
foreign key (admin_id) references users(id)

alter table users
add constraint FK_CODES
foreign key (country_code) references countries(code)

alter table merchants
add constraint FK_CODE
foreign key (country_code) references countries(code)

select*from countries
select*from order_items
select*from orders
select*from products
select*from merchants
select*from users

create procedure Procedure1
as
begin

SELECT u.name,u.price,u.status,m.Country_code,m.merchant_name,m.id,u.id
FROM products u
RIGHT JOIN merchants m ON m.id = u.merchant_id
ORDER BY u.id

end;

exec Procedure1;

create procedure Procedure2 @country_code int
as
begin

select*from users
where Country_code=@country_code
end;

exec Procedure2 @country_code = 91