final_lab.accdb

CREATE TABLE Organization (
    ID AUTOINCREMENT PRIMARY KEY,
    NAME TEXT(100),
    INN TEXT(20),
    ADDRESS TEXT(150),
    PHONE TEXT(20),
    DIRECTOR TEXT(100)
);

CREATE TABLE Contract (
    ID AUTOINCREMENT PRIMARY KEY,
    CONTRACT_NO TEXT(20),
    PRICE CURRENCY,
    WORK_TYPE TEXT(100),
    CONTRACT_DATE DATETIME,
    ORG1_ID LONG,
    ORG2_ID LONG,
    CONSTRAINT fk_org1 FOREIGN KEY (ORG1_ID) REFERENCES Organization(ID),
    CONSTRAINT fk_org2 FOREIGN KEY (ORG2_ID) REFERENCES Organization(ID)
);

INSERT INTO Organization (NAME, INN, ADDRESS, PHONE, DIRECTOR)
VALUES ('СтройСервис', '7701234567', 'Москва, ул. Ленина 10', '123-45-67', 'Иванов И.И.');

INSERT INTO Organization (NAME, INN, ADDRESS, PHONE, DIRECTOR)
VALUES ('ТехПроект', '7802345678', 'СПб, Невский 30', '234-56-78', 'Петров П.П.');

INSERT INTO Organization (NAME, INN, ADDRESS, PHONE, DIRECTOR)
VALUES ('МонтажГрупп', '5409876543', 'Новосибирск, Советская 12', '345-67-89', 'Сидоров С.С.');

INSERT INTO Contract (CONTRACT_NO, PRICE, WORK_TYPE, CONTRACT_DATE, ORG1_ID, ORG2_ID)
VALUES ('D001', 1500000, 'Проектирование', #2024-01-10#, 1, 2);

INSERT INTO Contract (CONTRACT_NO, PRICE, WORK_TYPE, CONTRACT_DATE, ORG1_ID, ORG2_ID)
VALUES ('D002', 2200000, 'Строительство', #2024-02-15#, 1, 3);

INSERT INTO Contract (CONTRACT_NO, PRICE, WORK_TYPE, CONTRACT_DATE, ORG1_ID, ORG2_ID)
VALUES ('D003', 950000, 'Монтаж', #2024-03-05#, 2, 1);

INSERT INTO Contract (CONTRACT_NO, PRICE, WORK_TYPE, CONTRACT_DATE, ORG1_ID, ORG2_ID)
VALUES ('D004', 1250000, 'Сервисное обслуживание', #2024-04-20#, 3, 1);

SELECT
    Organization.NAME AS Организация,
    Contract.CONTRACT_NO AS Договор,
    Contract.PRICE AS Стоимость
FROM Organization, Contract
WHERE Organization.ID = Contract.ORG1_ID
   OR Organization.ID = Contract.ORG2_ID;

SELECT
    Organization.NAME AS Организация,
    COUNT(Contract.ID) AS Количество_договоров,
    AVG(Contract.PRICE) AS Средняя_стоимость
FROM Organization, Contract
WHERE Organization.ID = Contract.ORG1_ID
   OR Organization.ID = Contract.ORG2_ID
GROUP BY Organization.NAME;