Notes

-- CREATE DATABASE TASK 1
create database Task1

--CREATE TABLE MERCHANTS
create table merchants(
id int primary key,
merchant_name varchar(50),
admin_id int,
country_code int,
created_at varchar(50))

--ADD FOREIGN KEY IN MERCHANTS
Alter table merchants
add constraint merchants_admin_id_FK
FOREIGN KEY (admin_id) references users(Id)

Alter table merchants
add constraint merchants_country_code_FK
FOREIGN KEY (country_code) references countries(code)


INSERT INTO merchants(id,merchant_name,admin_id,country_code,created_at)
values(1,'Adani',11,+91,'Home'),
(2,'Ambani',12,+86,'Office'),
(3,'Tata',13,+84,'Building')


--CREATE TABLE USERS
create table users (
id int primary key,
full_name varchar(50),
email varchar(50),
gender varchar(50),
date_of_birth varchar(50),
country_code int,
created_at varchar(50))

--ADD FOREIGN KEY IN USERS
Alter table users
add constraint users_country_code_FK
FOREIGN KEY (country_code) references countries(code)

INSERT INTO users(id,full_name,email,gender,date_of_birth,country_code,created_at)
values(11,'Adani Gujrati','[email protected]','male','12 july 2022',+91,'Home'),
(12,'Ambani Mukesh','[email protected]','male','16 oct 2012',+86,'Office'),
(13,'Tata Ratan','[email protected]','male','5 nov 1993',+84,'Building')

--CREATE TABLE COUNTRIES
create table countries(
code int primary key,
[name] varchar(50),
continent_name varchar(50))

INSERT INTO countries(code,[name],continent_name)
values(+91,'India','Asia'),
(+86,'China','Asia'),
(+84,'Vietnam','Asia')

--CREATE TABLE ORDERS
create table orders(
id int primary key,
[user_id] int,
[status] varchar(50),
created_at varchar(50))

--ADD FOREIGN KEY IN ORDERS
Alter table orders
add constraint orders_user_id_FK
FOREIGN KEY (user_id) references users(id)

INSERT INTO orders(id,[user_id],[status],created_at)
values(0111,11,'Active','Home'),
(0112,12,'Inactive','Office'),
(0113,13,'Active','Building')

--CREATE TABLE PRODUCTS
create table products(
id int primary key,
merchant_id int,
[name] varchar(50) ,
price int,
[status] varchar(50),
created_at varchar(50))

--ADD FOREIGN KEY IN PRODUCTS
Alter table products
add constraint products_merchant_id_FK
FOREIGN KEY (merchant_id) references merchants(id)

INSERT INTO products(id,merchant_id,[name],price,[status],created_at)
values(522,1,'Lg',1200,'Active','Home'),
(523,2,'Samsung',1300,'Inactive','Office'),
(524,3,'Redmi',1400,'Active','Building')

-- CREATE TABLE ORDER ITEMS
create table order_items(
order_id int,
product_id int,
quantity int)

--ADD FOREIGN KEY IN ORDER ITEMS
Alter table order_items
add constraint order_items_order_id_FK
FOREIGN KEY (order_id) references orders(id)

Alter table order_items
add constraint order_items_product_id_FK
FOREIGN KEY (product_id) references products(id)

INSERT INTO order_items(order_id,product_id,quantity)
values(0111,522,120),
(0112,523,160),
(0113,524,210)

-- CREATE STORED PROCEDURE 1
Create Procedure GetFirstThreeTable
AS
BEGIN

select * from order_items
select * from orders
select * from products
END

EXEC GetFirstThreeTable

-- CREATE STORED PROCEDURE 2
Create Procedure GetSecondThreeTable
AS
BEGIN

select * from merchants
select * from users
select * from countries
END

EXEC GetSecondThreeTable

-- CREATE STORED PROCEDURE WITH ONE PARAMETER
Create Procedure SingleSPParameter @merchant_name varchar(50)
AS
BEGIN
SELECT * FROM merchants WHERE merchant_name = @merchant_name
END

EXEC SingleSPParameter @merchant_name ='Adani';