Тема итоговой работы SQL: "Система учета продаж и клиентов в авто салоне."
BD создавалась в SQL Server Management Studio 19
--------------------------------------
CREATE DATABASE Accounting_db;
--------------------------------------
USE Accounting_db;

-- Клиенты
CREATE TABLE Customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20),
    email VARCHAR(255),
    address VARCHAR(255)
);

-- Автомобили
CREATE TABLE Cars (
    car_id INT IDENTITY(1,1) PRIMARY KEY,
    make VARCHAR(255) NULL,
    model VARCHAR(255) NULL,
    YEAR INT,
    color VARCHAR(255),
    mileage INT,
    price DECIMAL(10,2),
    description TEXT,
    STATUS VARCHAR(255)
);

-- Сотрудники
CREATE TABLE Employees (
    employee_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    ROLE VARCHAR(255),
    phone_number VARCHAR(20),
    email VARCHAR(255)
);

-- Продажи
CREATE TABLE Sales (
    sale_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT,
    car_id INT,
    employee_id INT,
    sale_date DATE,
    total_price DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (car_id) REFERENCES Cars(car_id),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

-- Заказы
CREATE TABLE Orders (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT,
    car_id INT,
    employee_id INT,
    order_date DATE,
    STATUS VARCHAR(255),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (car_id) REFERENCES Cars(car_id),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

-- Просмотры автомобилей
CREATE TABLE CarViews (
    view_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT,
    car_id INT,
    view_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (car_id) REFERENCES Cars(car_id)
);
--------------------------------------
USE Accounting_db;

-- таблица Customers
INSERT INTO Customers (first_name, last_name, phone_number, email, address) VALUES
('Иван', 'Иванов', '89001234567', '[email protected]', 'ул. Ленина, 10'),
('Петр', 'Петров', '89112345678', '[email protected]', 'ул. Пушкина, 15'),
('Ольга', 'Смирнова', '89223456789', '[email protected]', 'ул. Лермонтова, 20'),
('Алексей', 'Кузнецов', '89334567890', '[email protected]', 'ул. Толстого, 25'),
('Мария', 'Иванова', '89445678901', '[email protected]', 'ул. Чехова, 30'),
('Дмитрий', 'Петров', '89556789012', '[email protected]', 'ул. Горького, 35'),
('Екатерина', 'Сидорова', '89667890123', '[email protected]', 'ул. Маяковского, 40'),
('Андрей', 'Кузнецов', '89778901234', '[email protected]', 'ул. Есенина, 45'),
('Наталья', 'Иванова', '89889012345', '[email protected]', 'ул. Блока, 50'),
('Сергей', 'Петров', '89990123456', '[email protected]', 'ул. Пастернака, 55'),
('Анна', 'Сидорова', '89001234567', '[email protected]', 'ул. Цветаевой, 60'),
('Михаил', 'Кузнецов', '89112345678', '[email protected]', 'ул. Ахматовой, 65'),
('Елена', 'Иванова', '89223456789', '[email protected]', 'ул. Мандельштама, 70'),
('Владимир', 'Петров', '89334567890', '[email protected]', 'ул. Бродского, 75'),
('Светлана', 'Сидорова', '89445678901', '[email protected]', 'ул. Пастернака, 80'),
('Александр', 'Кузнецов', '89556789012', '[email protected]', 'ул. Ахматовой, 85'),
('Ирина', 'Иванова', '89667890123', '[email protected]', 'ул. Мандельштама, 90'),
('Максим', 'Петров', '89778901234', '[email protected]', 'ул. Бродского, 95'),
('Татьяна', 'Сидорова', '89889012345', '[email protected]', 'ул. Пастернака, 100'),
('Олег', 'Кузнецов', '89990123456', '[email protected]', 'ул. Ахматовой, 105');

-- таблица Cars
INSERT INTO Cars (make, model, YEAR, color, mileage, price, description, STATUS) VALUES
('Toyota', 'Camry', 2022, 'Синий', 10000, 2000000, 'Новый автомобиль', 'В наличии'),
('Honda', 'CR-V', 2021, 'Красный', 20000, 1800000, 'Хороший автомобиль с небольшим пробегом', 'В наличии'),
('Mazda', 'CX-5', 2020, 'Серый', 30000, 1600000, 'Надежный автомобиль с комфортным салоном', 'В наличии'),
('Kia', 'Sportage', 2019, 'Белый', 40000, 1400000, 'Просторная машина с множеством функций', 'В наличии'),
('Hyundai', 'Tucson', 2018, 'Черный', 50000, 1200000, 'Экономичный и практичный автомобиль', 'В наличии'),
('Nissan', 'X-Trail', 2023, 'Синий', 5000, 2200000, 'Новый автомобиль с полным приводом', 'В наличии'),
('Mitsubishi', 'Outlander', 2022, 'Серебристый', 10000, 1900000, 'Современный внедорожник с просторным салоном', 'В наличии'),
('Subaru', 'Forester', 2021, 'Зеленый', 15000, 1700000, 'Надежный автомобиль с хорошей проходимостью', 'В наличии'),
('Suzuki', 'Vitara', 2020, 'Белый', 25000, 1500000, 'Компактный и маневренный автомобиль', 'В наличии'),
('Renault', 'Duster', 2019, 'Красный', 35000, 1300000, 'Доступный и практичный внедорожник', 'В наличии'),
('Volkswagen', 'Tiguan', 2023, 'Синий', 5000, 2100000, 'Новый автомобиль с богатой комплектацией', 'В наличии'),
('Audi', 'Q5', 2022, 'Черный', 10000, 2800000, 'Премиальный автомобиль с комфортным салоном', 'В наличии'),
('BMW', 'X3', 2021, 'Белый', 15000, 2600000, 'Динамичный автомобиль с полным приводом', 'В наличии'),
('Mercedes-Benz', 'GLC', 2020, 'Серебристый', 20000, 2400000, 'Стильный автомобиль с множеством функций', 'Под заказ'),
('Volvo', 'XC60', 2019, 'Красный', 25000, 2200000, 'Безопасный и комфортный автомобиль', 'В наличии'),
('Lexus', 'NX', 2023, 'Синий', 5000, 2500000, 'Новый автомобиль с премиальным салоном', 'В наличии'),
('Infiniti', 'QX50', 2022, 'Черный', 10000, 2300000, 'Стильный автомобиль с спортивным характером', 'В наличии'),
('Porsche', 'Macan', 2021, 'Красный', 15000, 3000000, 'Спортивный автомобиль с мощным двигателем', 'Под заказ'),
('Land Rover', 'Discovery Sport', 2020, 'Белый', 20000, 2700000, 'Престижный внедорожник с полным приводом', 'Под заказ'),
('Range Rover', 'Evoque', 2019, 'Серый', 25000, 2500000, 'Стильный и комфортный автомобиль', 'В наличии');

-- Таблица Employees
INSERT INTO Employees (first_name, last_name, ROLE, phone_number, email) VALUES
('Алексей', 'Сидоров', 'Менеджер по продажам', '89223456789', '[email protected]'),
('Мария', 'Кузнецова', 'Консультант по продажам', '89334567890', '[email protected]'),
('Иван', 'Петров', 'Менеджер по работе с клиентами', '89445678901', '[email protected]'),
('Ольга', 'Смирнова', 'Финансовый консультант', '89556789012', '[email protected]'),
('Дмитрий', 'Иванов', 'Менеджер по маркетингу', '89667890123', '[email protected]'),
('Екатерина', 'Кузнецова', 'Специалист по сервисному обслуживанию', '89778901234', '[email protected]'),
('Андрей', 'Петров', 'Менеджер по логистике', '89889012345', '[email protected]'),
('Наталья', 'Смирнова', 'Менеджер по персоналу', '89990123456', '[email protected]'),
('Сергей', 'Иванов', 'Специалист по информационным технологиям', '89001234567', '[email protected]'),
('Анна', 'Кузнецова', 'Менеджер по закупкам', '89112345678', '[email protected]'),
('Михаил', 'Петров', 'Менеджер по продажам', '89223456789', '[email protected]'),
('Елена', 'Смирнова', 'Консультант по продажам', '89334567890', '[email protected]'),
('Владимир', 'Иванов', 'Менеджер по работе с клиентами', '89445678901', '[email protected]'),
('Светлана', 'Кузнецова', 'Финансовый консультант', '89556789012', '[email protected]'),
('Александр', 'Петров', 'Менеджер по маркетингу', '89667890123', '[email protected]'),
('Ирина', 'Смирнова', 'Специалист по сервисному обслуживанию', '89778901234', '[email protected]'),
('Максим', 'Иванов', 'Менеджер по логистике', '89889012345', '[email protected]'),
('Татьяна', 'Кузнецова', 'Менеджер по персоналу', '89990123456', '[email protected]'),
('Олег', 'Петров', 'Специалист по информационным технологиям', '89001234567', '[email protected]'),
('Мария', 'Смирнова', 'Менеджер по закупкам', '89112345678', '[email protected]');

-- Таблица Sales
INSERT INTO Sales (customer_id, car_id, employee_id, sale_date, total_price) VALUES
(1, 1, 1, '2024-03-15', 2000000),
(2, 2, 2, '2024-03-10', 1800000),
(3, 3, 3, '2024-03-05', 1600000),
(4, 4, 4, '2024-02-28', 1400000),
(5, 5, 5, '2024-02-23', 1200000),
(6, 6, 6, '2024-02-18', 2200000),
(7, 7, 7, '2024-02-13', 1900000),
(8, 8, 8, '2024-02-08', 1700000),
(9, 9, 9, '2024-02-03', 1500000),
(10, 10, 10, '2024-01-28', 1300000),
(11, 11, 11, '2024-01-23', 2100000),
(12, 12, 12, '2024-01-18', 2800000),
(13, 13, 13, '2024-01-13', 2600000),
(14, 14, 14, '2024-01-08', 2400000),
(15, 15, 15, '2024-01-03', 2200000),
(16, 16, 16, '2023-12-28', 2500000),
(17, 17, 17, '2023-12-23', 2300000),
(18, 18, 18, '2023-12-18', 3000000),
(19, 19, 19, '2023-12-13', 2700000),
(20, 20, 20, '2023-12-08', 2500000);

-- Таблица Orders
INSERT INTO Orders (customer_id, car_id, employee_id, order_date, STATUS) VALUES
(1, 1, 1, '2024-03-07', 'В обработке'),
(2, 2, 2, '2024-03-02', 'Подтвержден'),
(3, 3, 3, '2024-02-29', 'Отменен'),
(4, 4, 4, '2024-02-20', 'В обработке'),
(5, 5, 5, '2024-02-17', 'Подтвержден'),
(6, 6, 6, '2024-02-12', 'Отменен'),
(7, 7, 7, '2024-02-05', 'В обработке'),
(8, 8, 8, '2024-02-01', 'Подтвержден'),
(9, 9, 9, '2024-01-25', 'Отменен'),
(10, 10, 10, '2024-01-21', 'В обработке'),
(11, 11, 11, '2024-01-18', 'Подтвержден'),
(12, 12, 12, '2024-01-12', 'Отменен'),
(13, 13, 13, '2024-01-10', 'В обработке'),
(14, 14, 14, '2024-01-02', 'Подтвержден'),
(15, 15, 15, '2024-01-01', 'Отменен'),
(16, 16, 16, '2023-12-26', 'В обработке'),
(17, 17, 17, '2023-12-21', 'Подтвержден'),
(18, 18, 18, '2023-12-16', 'Отменен'),
(19, 19, 19, '2023-12-11', 'В обработке'),
(20, 20, 20, '2023-12-06', 'Подтвержден');

-- Таблица CarViews
INSERT INTO CarViews (customer_id, car_id, view_date) VALUES
(1, 1, '2023-03-15'),
(2, 2, '2023-03-10'),
(3, 3, '2023-03-05'),
(4, 4, '2023-02-28'),
(5, 5, '2023-02-23'),
(6, 6, '2023-02-18'),
(7, 7, '2023-02-13'),
(8, 8, '2023-02-08'),
(9, 9, '2023-02-03'),
(10, 10, '2023-01-28'),
(11, 11, '2023-01-23'),
(12, 12, '2023-01-18'),
(13, 13, '2023-01-13'),
(14, 14, '2023-01-08'),
(15, 15, '2023-01-03'),
(16, 16, '2022-12-28'),
(17, 17, '2022-12-23'),
(18, 18, '2022-12-18'),
(19, 19, '2022-12-13'),
(20, 20, '2022-12-08');
---------------------------------------
--Приложение для базы данных, написанное на Python.

import pyodbc
#Подключение к bd. После Server= 'Ввести имя сервера', после DATABASE= 'Ввести название bd.
connection_to_db = pyodbc.connect(r'Driver={SQL Server};Server=LSD_ADMIN\SQLEXPRESS;DATABASE=Accounting_db;Trusted_Connection=yes;')
cursor = connection_to_db.cursor()
# Меню для приложения
menu = '''\nМеню:
1 - Показать список авто
2 - Добавить марку и модель нового авто
3 - Редактировать марку и модель авто
4 - Удалить марку и модель авто
0 - Выход из программы
'''
# Создание меню
while True:
    print(menu)
    choice = input("Выберите опцию: ")

    if choice == '1':
        # Показать список автомобилей
        cursor.execute('SELECT make, model FROM Cars')
        rows = cursor.fetchall()
        for row in rows:
            make, model = row
            print(f"Марка: {make}, Модель: {model}")
    elif choice == '2':
        # Добавление нового авто
        make = input("Введите марку нового авто: ")
        model = input("Введите модель нового авто: ")
        cursor.execute('INSERT INTO Cars (make, model) VALUES (?, ?)', (make, model))
        connection_to_db.commit()
    elif choice == '3':
        # Редактирование авто
        car_id = input("Введите ID авто для редактирования: ")
        new_make = input("Введите новую марку: ")
        new_model = input("Введите новую модель: ")
        cursor.execute('UPDATE Cars SET make=?, model=? WHERE car_id=?', (new_make, new_model, car_id))
        connection_to_db.commit()
        pass
    elif choice == '4':
        # Удалить марку и модель авто
        car_id = input("Введите ID авто для удаления: ")
        cursor.execute('UPDATE Cars SET make = NULL, model = NULL WHERE car_id = ?', (car_id,))
        connection_to_db.commit()
        pass
    elif choice == '0':
        break
    else:
        print("Неверный выбор.")

connection_to_db.close()  # Закрываем соединение с базой данных