-- пароль c765oe22_RU
-- Вариант 6. Спроектировать структуру базы данных о заказах, 
-- полученных сотрудниками фирмы: фамилия, сумма заказа, наименование товара, название фирмы-клиента, фамилия заказчика.
-- Вариант 6. Заказы
DROP DATABASE IF EXISTS laba;
CREATE DATABASE laba;
USE laba;

CREATE TABLE Company (
  id INT PRIMARY KEY AUTO_INCREMENT,
  company_name VARCHAR(200) NOT NULL
);

CREATE TABLE ClientsCompany (
  id INT PRIMARY KEY AUTO_INCREMENT,
  company_id INT NOT NULL,
  last_name VARCHAR(200) NOT NULL,
  FOREIGN KEY (company_id) REFERENCES Company(id)
);

CREATE TABLE Product (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(200) NOT NULL
);
CREATE TABLE Employee(
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee_name VARCHAR(200) NOT NULL
);
CREATE TABLE Orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  company_id INT NOT NULL,
  clients_id INT NOT NULL,
  employee_id INT NOT NULL,
  price DECIMAL(12,2) NOT NULL,
FOREIGN KEY (product_id) REFERENCES Product(id),
FOREIGN KEY (company_id) REFERENCES Company(id),
FOREIGN KEY (clients_id) REFERENCES ClientsCompany(id),
FOREIGN KEY (employee_id) REFERENCES Employee(id)
);

INSERT INTO Company (company_name) VALUES
('HIHIK'),
('DNS'),
('Krendel');

INSERT INTO ClientsCompany (company_id, last_name) VALUES
(1, 'Ivanov'),
(1, 'Chumakov'),
(2, 'Smirnov'),
(3, 'Sokolova');

INSERT INTO Product (product_name) VALUES
('Laptop'),
('Scan'),
('Router'),
('Display');

INSERT INTO Employee (employee_name) VALUES
('Sahsa'),
('Misha'),
('Kostik'),
('Ivan');

INSERT INTO Orders (product_id, company_id, clients_id, employee_id, price) VALUES
(1, 1, 1, 1, 125000.00),
(2, 1, 2, 2, 18990.00),
(3, 2, 3, 3, 7990.00),
(4, 3, 4, 4, 45990.00),
(1, 2, 3, 4, 109990.00);


 

SELECT Orders.id, product_name, company_name, last_name, employee_name, price FROM Orders
    INNER JOIN Product ON Product.id = product_id
    INNER JOIN ClientsCompany ON clients_id = ClientsCompany.id
    INNER JOIN Company ON Orders.company_id = Company.id
    INNER JOIN Employee ON Orders.employee_id = Employee.id
    ORDER BY Orders.id;
