Notes

---CREATED DATABASE---
Create Database ShoppingPlatform


---CREATING TABLES---
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] varchar(50),
created_at varchar(50))


Create Table products
(id int not null Primary Key,
merchant_id int,
name varchar(50),
price int,
status varchar(50),
created_at varchar(50))


Create Table merchants
(id int not null Primary Key,
merchant_name varchar(50),
admin_id int,
country_code int,
created_at varchar(50))


Create Table users
(id int not null Primary Key,
full_name varchar(50),
email varchar(50),
gender varchar(10),
date_of_birth varchar(20),
country_code int,
created_at varchar(30))


Create Table countries
(code int not null Primary Key,
[name] varchar(50),
continent_name varchar(50))


------INSERTING DATA IN TABLES------
Insert Into order_items(order_id,product_id,quantity)
Values (1,1,2),
(2,2,4),
(3,3,1),
(4,1,4),
(5,4,6)


Insert Into products(id,merchant_id,[name],price,[status],created_at)
Values (1,1,'Prime bottle',32000,'Available','02-06-2022'),
(2,2,'Sidemen Merch',4900,'Not Available','07-09-2022'),
(3,1,'XIX Drinks',41000,'Available','31-12-2021'),
(4,3,'Side Plus',18000,'Available','17-10-2022'),
(5,4,'Eat Side ',8000,'Not Available','23-06-2022')


Insert Into merchants(id,merchant_name,admin_id,country_code,created_at)
Values (1,'Ksi',1,1,'01-01-2022'),
(2,'Tobi',2,2,'05-09-2022'),
(3,'Josh',1,3,'07-10-2022'),
(4,'Vikk',3,1,'23-03-2022'),
(5,'Bhez',4,2,'05-05-2022')


Insert Into orders(id,[user_id],[status],created_at)
Values (1,1,'Delivered','01-10-2022'),
(2,2,'Not Delivered','17-10-2022'),
(3,1,'Delivered','02-10-2022'),
(4,3,'Delivered','09-09-2022'),
(5,2,'Not Delivered','17-10-2022')


Insert Into users(id,full_name,email,gender,date_of_birth,country_code,created_at)
Values (1,'Danny ','[email protected]','Male','03-07-1998',1,'15-08-2022'),
(2,'Keanu Reeves','[email protected]','Male','24-10-1993',1,'12-09-2022'),
(3,'Emma Watson','[email protected]','Female','12-12-1990',2,'01-01-2021'),
(4,'Chris Evans','[email protected]','Male','02-11-1993',3,'01-03-2022'),
(5,'Julia Stark','[email protected]','Female','12-12-1999',4,'23-03-2022')


Insert Into countries(code,[name],continent_name)
Values (1,'India','Asia'),
(2,'USA','North America'),
(3,'Germany','Europe'),
(4,'Sydney','Australia'),
(5,'Canada','North America')



-----CREATING FOREIGN KEYS-----
Alter Table order_items
Add Constraint order_items_orders_FK
Foreign Key(order_id) references orders(id)


Alter Table order_items
Add Constraint product_id_products_FK
Foreign Key(product_id) references products(id)


Alter Table products
Add Constraint products_merchants_FK
Foreign Key(merchant_id) references merchants(id)


Alter Table orders
Add Constraint orders_users_FK
Foreign Key([user_id]) references users(id)


Alter Table merchants
Add Constraint merchants_users_FK
Foreign Key(admin_id) references users(id)


Alter Table merchants
Add Constraint merchants_countries_FK
Foreign Key(country_code) references countries(code)


Alter Table users
Add Constraint users_countries_FK
Foreign Key(country_code) references countries(code)


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

create Procedure getCompanyandProduct
as
begin
select * from countries
end

exec getCompanyandProduct


create Procedure getGender2 @gender nvarchar(50)
as
begin
select full_name from users1
where gender = @gender
end

exec getGender2 @gender = 'Male'
create Procedure getCompanyandProductname
as
begin
select merchant_name , name
from merchants s
Inner Join products p ON s.id = p.merchant_id
order by merchant_name
end

exec getCompanyandProductname