Notes

create database practice5

use practice5

create table countries
(code int Not Null Primary Key,
name nvarchar(50),
continent_name 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))

alter table users
add constraint users_countries_FK Foreign Key(country_code)
references countries

create table merchants
(Id int Not Null Primary Key,
merchant_name nvarchar(50),
admin_id int,
country_code int,
created_at nvarchar(50))

alter table merchants
add constraint merchants_countries_FK Foreign Key(country_code)
references countries

alter table merchants
add constraint merchants_users_FK Foreign Key(admin_id)
references users

create table orders
(Id int Not Null Primary Key,
user_id int,
status nvarchar(50),
created_at nvarchar(50))

alter table orders
add constraint orders_users_FK Foreign Key(user_id)
references users

create table products
(Id int Not Null Primary Key,
merchant_id int,
name nvarchar(50),
price int,
status nvarchar(50),
created_at nvarchar(50))

alter table products
add constraint products_merchants_FK Foreign Key(merchant_id)
references merchants

create table order_items
(order_id int Not Null,
product_id int,
quantity int)

alter table order_items
add constraint order_items_orders_FK Foreign Key(order_id)
references orders

alter table order_items
add constraint order_items_products_FK Foreign Key(product_id)
references products

insert into countries values
(101,'India','Asia'),
(102,'China','Asia'),
(103,'Nigeria','Africa'),
(104,'Egypy','Africa'),
(105,'USA','North America')

insert into users values
(201,'Taha','[email protected]','Male','1999-21-11',101,'Non-Foreign'),
(202,'Tabish','[email protected]','Male','1997-02-01',101,'Non-Foreign'),
(203,'Jessie','[email protected]','Female','2000-11-12',105,'Foreign'),
(204,'Jackie','[email protected]','Male','1973-04-05',102,'Foreign'),
(205,'Rihana','[email protected]','Female','1990-02-03',103,'Foreign')

insert into merchants values
(301,'Merchant1',201,101,'Non-Foreign'),
(302,'Merchant2',202,101,'Non-Foreign'),
(303,'Merchant3',205,103,'Foreign'),
(304,'Merchant4',204,102,'Foreign'),
(305,'Merchant5',203,105,'Foreign')

insert into orders values
(401,201,'Dispatched','Non-Foreign'),
(402,202,'Cancelled','Non-Foreign'),
(403,203,'Dispatched','Foreign'),
(404,204,'Dispatched','Foreign'),
(405,205,'Cancelled','Foreign')

insert into products values
(501,301,'iphone 10',40000,'In-Stock','Foreign'),
(502,302,'iphone 11',50000,'In-Stock','Foreign'),
(503,303,'iphone 12',60000,'In-Stock','Foreign'),
(504,304,'iphone 13',70000,'In-Stock','Foreign'),
(505,305,'iphone 14',80000,'In-Stock','Foreign')

insert into order_items values
(401,501,1),
(402,502,2),
(403,503,1),
(404,504,2),
(405,505,1)

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

create procedure products_details
as
begin
select * from products
end

create procedure products_details_cost @price int
as
begin
select * from products
where price=@price
end

products_details

products_details_cost @price=50000

select users.full_name,users.gender,countries.name,countries.continent_name
from users
inner join countries
on users.country_code=countries.code