ShopBase / localhost

create database ShopBase
USE ShopBase

Create table categories(

category_id int primary key identity(1,1),
category_name varchar(20)

);
INSERT INTO categories(category_name) VALUES ('cheese'),('clothes'),('meat')


Create table brands(
brand_id int primary key identity(1,1),
brand_name varchar(20)
);
INSERT INTO brands(brand_name) VALUES ('Nike'),('Adidas'),('Prismo')

CREATE TABLE products(
product_id int not null primary key identity(1,1),
product_name varchar(20),
brand_id int,
category_id int,
model_year int,
list_price decimal(7, 2),
FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
INSERT INTO products(product_name, brand_id, category_id, model_year, list_price) VALUES ('mocarella', 3, 1, 2024, 2.5), ('air jordan 1', 1, 2, 2014, 116.5), ('pig meat', 3, 3, 2024, 1.5)

CREATE TABLE stores(
store_id int primary key identity(1,1),
store_name varchar(50),
phone varchar(50),
email varchar(50),
street varchar(50),
city varchar(50),
state varchar(50),
zip_code varchar(50)
)
INSERT INTO stores(store_name, phone, email, street, city, state, zip_code) VALUES ('Arvutitark', '53551699', 'arvutitark@info.ee', 'Kristiine', 'Tallinn', NULL, '10614'), ('Prisma', '56417112', 'prisma@info.ee', 'Kristiine', 'Tallinn', NULL, '10614'), ('Nike', '53272321', 'euronics@info.ee', 'Mustamae', 'Tallinn', NULL, '10524')


-- ....
CREATE TABLE stocks(
store_id int not null,
product_id int,
quantity int,
PRIMARY KEY (store_id, product_id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
FOREIGN KEY(store_id) REFERENCES stores(store_id)
);
INSERT INTO stocks(store_id, product_id, quantity) VALUES (2, 4, 5),(2, 2, 11), (3, 3, 6)

CREATE TABLE customers(
customer_id int primary key identity(1,1),
first_name varchar(50),
last_name varchar(50),
phone varchar(50),
email varchar(50),
street varchar(50),
city varchar(50),
state varchar(50),
zip_code varchar(50)
);
INSERT INTO customers(first_name, last_name, phone, email, street, city, state, zip_code) VALUES ('John', 'Doe', '53551699', 'johndoe@gmail.com', 'Eshkerija', 'Tallinn', NULL, 10614), ('Jane', 'Doe', '52417341', 'janedoe@gmail.com', 'Not eshkerija', 'Tallinn', NULL, 10634), ('Mark', 'Pillar', '76622151', 'maripillar@gmail.com', 'Bikobsk', 'Tallinn', NULL, 10624)

create table order_items(
order_id int identity(1,1),
item_id int,
product_id int,
quantity int,
list_price int,
discount int,
PRIMARY KEY (order_id, item_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO order_items(item_id, product_id, quantity, list_price, discount) VALUES (2, 2, 43, 23, 20),(3, 3, 55, 23, 52),(4, 4, 51,64, 63)

CREATE TABLE staffs(
staff_id int primary key identity(1,1),
first_name varchar(50),
last_name varchar(50),
email varchar(50),
phone varchar(50),
active bit,
store_id int,
manager_id int,
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (manager_id) REFERENCES staffs(staff_id)
);

INSERT INTO staffs(first_name, last_name, email, phone, active, store_id, manager_id) VALUES ('Martin', 'Sild', 'mar@gmail.com', '5235523',1,  1, 1), ('Lev', 'Jegorov', 'levj@gmail.com', '5251551', 0, 1, 1), ('Timur', 'Bashirov', 'tmbsh@gmail.com', '64146144', 1, 1, 1)


--
create table orders(
order_id int primary key identity(1,1),
customer_id int,
order_status varchar(50),
order_date date,
required_date date,
shipped_date date,
store_id int,
staff_id int,
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (staff_id) REFERENCES staffs(staff_id)
);
INSERT INTO orders(customer_id, order_status, order_date, required_date, shipped_date, store_id, staff_id) VALUES (1, 'Shipping', '2024-12-01', '2024-12-06', '2024-12-07', 1, 1), (2, 'Deliveried', '2024-02-01', '2024-02-06', '2024-02-07', 2, 2), (3, 'Deliveried', '2024-06-01', '2024-06-06', '2024-06-07', 3, 3)