Notes

--Creating Table
create table order_items
(order_id int not null primary key,
product_id int ,
quantity int)
--Inserting Values
insert into order_items
values(11,21,34),(12,22,86),(13,23,34),(14,24,99)
--Viewing data
select*from order_item

create table Products
(id int not null primary key,
merchant_id int ,name varchar(30),price int,
status varchar(10),created_at varchar(30))

insert into products
values(21,31,'Television',30000,'Y','Korea'),(22,32,'SmartPhone',20000,'N','China'),
(23,33,'Goods',30000,'Y','India'),(24,34,'Car',3000000,'Y','Germany')
select*from products

create table merchants(
id int not null primary Key,
merchant_name varchar(30),
admin_id int,
country_code int,
created_at varchar(20))

insert into merchants
values(31,'Jai',51,41,'Korea'),(32,'Rita',52,42,'China'),
(33,'Himanshu',53,43,'India'),(34,'Prashant',54,44,'Germany')

create table countries(
code int not null Primary key,
name varchar(30),
continent_name varchar(30))

insert into countries
values(41,'Korea','Aisa'),(42,'China','Asia'),(43,'India','Asia'),
(44,'Germany','Europe')

create table users
(id int not null primary key,
full_name varchar(30),
email varchar(40),
gender varchar(10),
date_of_birth varchar(15),
country_code int,
created_at varchar(30))

insert into users
values(51,'Divy Gupta','[email protected]','Male','25/3/2000',42,'9/3/8888'),
(52,'Abhay Singh','[email protected]','Female','5/3/2001',41,'02/2/2022'),
(53,'Rajat Yadav','[email protected]','Male','2/10/2000',43,'25/4/3333'),
(54,'Abhay Patel','[email protected]','Male','25/3/1999',44,'5/3/2333')

create table orders(
id int not null primary key,
[user_id] int,
status varchar(10),
created_at varchar(20))

insert into orders
values(11,51,'Y','11/4/2000'),(12,52,'N','7/8/1111'),
(13,53,'Y','9/11/1112'),(14,54,'N','3/9/2121')

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

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

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

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

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

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

alter table orders
add constraint FK7
foreign key ([user_id]) references users(id)



--Procedure1
CREATE PROCEDURE Proc1
AS
BEGIN
select Products.id as PID , Products.merchant_id ,Products.name
as Product_name,Products.price,merchants.merchant_name
from Products
full join merchants
on Products.merchant_id=merchants.id
END

execute Proc1
--Procedure2
CREATE PROCEDURE Proc2
AS
BEGIN
select id,full_name,email,name as Country_name
from Users
full join Countries
on Users.country_code=countries.code
END

execute Proc2
--Procedure3
CREATE PROCEDURE SelectCountry (@code int)
AS
BEGIN
select code , name
from countries
where code=@code
END

execute [SelectCountry]@code=43;