Õpimap norem tarkvararendaja, Aleksandr Nadvornõi

Создайте базу данных согласно схеме, прописывая через SQL запросы создание и заполнение таблиц. 

Начните с таблиц первичного ключа — brands, category, customers, далее таблицы products, stores, staffs, orders, order_items, stocks.

DB

Create table categories(

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

);

INSERT INTO categories (category_name) VALUES (‘Electronics’);
INSERT INTO categories (category_name) VALUES (‘Books’);
INSERT INTO categories (category_name) VALUES (‘Clothing’);
INSERT INTO categories (category_name) VALUES (‘Home & Kitchen’);
INSERT INTO categories (category_name) VALUES (‘Sports & Outdoors’);
INSERT INTO categories (category_name) VALUES (‘Toys & Games’);
INSERT INTO categories (category_name) VALUES (‘Beauty & Personal Care’);
INSERT INTO categories (category_name) VALUES (‘Automotive’);
INSERT INTO categories (category_name) VALUES (‘Health & Wellness’);
INSERT INTO categories (category_name) VALUES (‘Garden & Outdoor’);

Create table brands(
brand_id int primary key identity(1,1),
brand_name varchar(20)
);

INSERT INTO brands (brand_name) VALUES (‘Sony’);
INSERT INTO brands (brand_name) VALUES (‘Samsung’);
INSERT INTO brands (brand_name) VALUES (‘Nike’);
INSERT INTO brands (brand_name) VALUES (‘Adidas’);
INSERT INTO brands (brand_name) VALUES (‘Apple’);
INSERT INTO brands (brand_name) VALUES (‘Microsoft’);
INSERT INTO brands (brand_name) VALUES (‘Toyota’);
INSERT INTO brands (brand_name) VALUES (‘Honda’);
INSERT INTO brands (brand_name) VALUES (‘LG’);
INSERT INTO brands (brand_name) VALUES (‘Dell’);

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 (‘Xperia XZ’, 1, 1, 2022, 499.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘Galaxy S21’, 2, 1, 2023, 799.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘Air Max’, 3, 3, 2021, 119.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘Ultraboost’, 4, 3, 2022, 179.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘iPhone 13’, 5, 1, 2023, 999.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘Surface Pro’, 6, 1, 2022, 1299.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘Camry’, 7, 8, 2022, 24499.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘Civic’, 8, 8, 2023, 21999.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘OLED TV’, 9, 1, 2022, 1599.99);
INSERT INTO products (product_name, brand_id, category_id, model_year, list_price) VALUES (‘XPS 13’, 10, 1, 2023, 1399.99);

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 (1, 1, 50);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (1, 2, 30);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (2, 3, 20);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (2, 4, 15);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (3, 5, 10);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (3, 6, 5);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (4, 7, 25);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (4, 8, 40);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (5, 9, 35);
INSERT INTO stocks (store_id, product_id, quantity) VALUES (5, 10, 45);

create table customers(
customers_id int not null primary key identity(1,1),
first_name varchar(50),
last_name varchar(50),
phone int,
email varchar(50),
street varchar(50),
city varchar(50),
state_ varchar(50),
zip_code int);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘John’, ‘Doe’, 34567890, ‘john.doe@example.com’, ‘123 Main St’, ‘Anytown’, ‘Anystate’, 12345);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Jane’, ‘Smith’, 45678901, ‘jane.smith@example.com’, ‘456 Elm St’, ‘Othertown’, ‘Otherstate’, 23456);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Alice’, ‘Johnson’, 56789012, ‘alice.johnson@example.com’, ‘789 Oak St’, ‘Thistown’, ‘Thistate’, 34567);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Bob’, ‘Brown’, 67890123, ‘bob.brown@example.com’, ‘101 Pine St’, ‘Sometown’, ‘Somestate’, 45678);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Charlie’, ‘Davis’, 78901234, ‘charlie.davis@example.com’, ‘202 Cedar St’, ‘Anycity’, ‘Anystate’, 56789);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘David’, ‘Miller’, 89012345, ‘david.miller@example.com’, ‘303 Birch St’, ‘Othercity’, ‘Otherstate’, 67890);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Eva’, ‘Wilson’, 90123456, ‘eva.wilson@example.com’, ‘404 Maple St’, ‘Thiscity’, ‘Thistate’, 78901);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Frank’, ‘Moore’, 01234567, ‘frank.moore@example.com’, ‘505 Walnut St’, ‘Somecity’, ‘Somestate’, 89012);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Grace’, ‘Taylor’, 12345678, ‘grace.taylor@example.com’, ‘606 Ash St’, ‘Yettown’, ‘Yetstate’, 90123);
INSERT INTO customers (first_name, last_name, phone, email, street, city, state_, zip_code)
VALUES (‘Hank’, ‘Anderson’, 34506789, ‘hank.anderson@example.com’, ‘707 Willow St’, ‘Lasttown’, ‘Laststate’, 10234);

create table order_items(
order_id int not null,
item_id int not null,
product_id int,
PRIMARY KEY (order_id, item_id),
FOREIGN KEY(product_id) REFERENCES products(product_id),
quantity int,
list_price decimal(7, 2),
discount varchar(20));

INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (1, 1, 1, 2, 499.99, ‘10%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (1, 2, 2, 1, 799.99, ‘5%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (2, 1, 3, 3, 119.99, ‘15%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (2, 2, 4, 1, 179.99, ‘0%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (3, 1, 5, 1, 999.99, ‘20%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (3, 2, 6, 2, 1299.99, ‘10%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (4, 1, 7, 1, 24499.99, ‘5%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (4, 2, 8, 1, 21999.99, ‘0%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (5, 1, 9, 1, 1599.99, ‘25%’);
INSERT INTO order_items (order_id, item_id, product_id, quantity, list_price, discount)
VALUES (5, 2, 10, 1, 1399.99, ‘15%’);

create table stores(
store_id int not null primary key identity(1,1),
store_name varchar(50),
phone int,
email varchar(50),
street varchar(50),
city varchar(50),
state_ varchar(50),
zip_code int);

INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Tech World’, 34567890, ‘contact@techworld.com’, ‘100 Tech Ave’, ‘Tech City’, ‘Tech State’, 10001);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Book Haven’, 45678901, ‘info@bookhaven.com’, ‘200 Book St’, ‘Book Town’, ‘Book State’, 20002);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Fashion Hub’, 56789012, ‘support@fashionhub.com’, ‘300 Fashion Rd’, ‘Fashion City’, ‘Fashion State’, 30003);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Home Essentials’, 67890123, ‘service@homeessentials.com’, ‘400 Home Blvd’, ‘Home Town’, ‘Home State’, 40004);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Sports Arena’, 78901234, ‘help@sportsarena.com’, ‘500 Sports Ln’, ‘Sports City’, ‘Sports State’, 50005);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Toy Kingdom’, 89012345, ‘contact@toykingdom.com’, ‘600 Toy Dr’, ‘Toy City’, ‘Toy State’, 60006);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Beauty Bliss’, 90123456, ‘info@beautybliss.com’, ‘700 Beauty Ct’, ‘Beauty City’, ‘Beauty State’, 70007);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Auto Depot’, 01234567, ‘support@autodepot.com’, ‘800 Auto Pl’, ‘Auto City’, ‘Auto State’, 80008);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Health Plus’, 12345678, ‘service@healthplus.com’, ‘900 Health Pkwy’, ‘Health City’, ‘Health State’, 90009);
INSERT INTO stores (store_name, phone, email, street, city, state_, zip_code)
VALUES (‘Garden Center’, 34506789, ‘help@gardencenter.com’, ‘1000 Garden Ter’, ‘Garden City’, ‘Garden State’, 10010);

create table staffs(
staffs_id int not null primary key identity(1,1),
first_name varchar(50),
last_name varchar(50),
email varchar(50),
phone int,
active bit,
store_id int,
FOREIGN KEY(store_id) REFERENCES stores(store_id),
manager_id int,
FOREIGN KEY(manager_id) REFERENCES staffs(staffs_id))

INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Alice’, ‘Brown’, ‘alice.brown@example.com’, 34567890, 1, 1, NULL);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Bob’, ‘Smith’, ‘bob.smith@example.com’, 45678901, 1, 2, 1);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Charlie’, ‘Johnson’, ‘charlie.johnson@example.com’, 56789012, 1, 3, 1);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘David’, ‘Williams’, ‘david.williams@example.com’, 67890123, 1, 4, 2);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Eva’, ‘Jones’, ‘eva.jones@example.com’, 78901234, 1, 5, 2);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Frank’, ‘Miller’, ‘frank.miller@example.com’, 89012345, 1, 1, 3);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Grace’, ‘Davis’, ‘grace.davis@example.com’, 90123456, 1, 2, 3);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Hank’, ‘Wilson’, ‘hank.wilson@example.com’, 01234567, 1, 3, 4);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Ivy’, ‘Moore’, ‘ivy.moore@example.com’, 12345678, 1, 4, 4);
INSERT INTO staffs (first_name, last_name, email, phone, active, store_id, manager_id)
VALUES (‘Jack’, ‘Taylor’, ‘jack.taylor@example.com’, 34506789, 1, 5, 5);

create table orders(
orders_id int not null primary key identity(1,1),
customers_id int,
FOREIGN KEY(customers_id) REFERENCES customers(customers_id),
order_status varchar(20),
order_date date,
required_date date,
shipped_date date,
store_id int,
FOREIGN KEY(store_id) REFERENCES stores(store_id),
staffs_id int,
FOREIGN KEY(staffs_id) REFERENCES staffs(staffs_id))

INSERT INTO orders (customers_id, order_status, order_date, required_date, shipped_date, store_id, staffs_id)
VALUES
(1, ‘Pending’, ‘2024-05-01’, ‘2024-05-10’, NULL, 1, 1),
(2, ‘Shipped’, ‘2024-05-02’, ‘2024-05-12’, ‘2024-05-08’, 2, 2),
(3, ‘Pending’, ‘2024-05-03’, ‘2024-05-13’, NULL, 1, 3),
(4, ‘Shipped’, ‘2024-05-04’, ‘2024-05-14’, ‘2024-05-09’, 3, 2),
(5, ‘Delivered’, ‘2024-05-05’, ‘2024-05-15’, ‘2024-05-11’, 2, 3),
(6, ‘Pending’, ‘2024-05-06’, ‘2024-05-16’, NULL, 3, 1),
(7, ‘Shipped’, ‘2024-05-07’, ‘2024-05-17’, ‘2024-05-12’, 1, 2),
(8, ‘Delivered’, ‘2024-05-08’, ‘2024-05-18’, ‘2024-05-13’, 3, 3),
(9, ‘Pending’, ‘2024-05-09’, ‘2024-05-19’, NULL, 2, 1),
(10, ‘Shipped’, ‘2024-05-10’, ‘2024-05-20’, ‘2024-05-14’, 1, 3);

select * from brands;
select * from categories;
select * from customers;
select * from products;
select * from stores;
select * from staffs;
select * from orders;
select * from order_items;
select * from stocks;

drop table brands;
drop table categories;
drop table customers;
drop table products;
drop table stores;
drop table staffs;
drop table orders;
drop table order_items;
drop table stocks;