Pieprasījumi
1.
-- Objekti (krustojumi)
CREATE TABLE objektis (
    objekta_id INT PRIMARY KEY,
    nosaukums VARCHAR(255)
);

-- Skaitītāji
CREATE TABLE skaititaji_1 (
   address_nr BIGINT PRIMARY KEY,
    objekta_id INT,
    uzstadits DATE,
    demontets DATE,
    radijumu_skaits INT,
    FOREIGN KEY (objekta_id) REFERENCES objektis(objekta_id)
);

-- Rādījumu vēsture (ja vajag arī rādījumu izsekošanu)
CREATE TABLE radijumi (
   skaititaji_nr BIGINT,
    datums DATE,
    reading DECIMAL(10,2),
    FOREIGN KEY (skaititaji_nr) REFERENCES skaititaji_1(skaititaji_nr)
);

2.-- Objekti
INSERT INTO objektis (objekta_id, nosaukums)
VALUES
(1, 'Atmodas-Asteru'),
(2, 'Brīvības-Garozas'),
(3, 'Brīvības-Lāčplēša');

-- Skaitītāji
INSERT INTO skaititaji_1 (address_nr, objekta_id, uzstadits, demontets, radijumu_skaits)
VALUES
(5301873827, 1, '2020-01-31', '2021-05-01', 13),
(4107948968, 2, '2020-01-31', '2021-10-31', 10),
(568796,      2, '2020-01-31', '2020-10-31', 9),
(524351225,   3, '2020-12-01', '2021-10-31', 5),
(568693,      3, '2020-01-31', '2020-10-31', 9);

3. SELECT s.address_nr, o.nosaukums AS objekts, s.uzstadits, s.demontets, s.radijumu_skaits
FROM skaititaji_1 s
JOIN objektis o ON s.objekta_id = o.objekta_id;

4. SELECT * FROM radijumi
WHERE skaititaja_nr = 5301873827
ORDER BY datums;

5.SELECT 
    o.objektis AS object_name,
    o.object_id,
    o.address_name,
    m.meter_meter AS meter_number,
    d.obis,
    d.date,
    d.value
FROM 
    meter_accounting ma
JOIN 
    objects o ON ma.object_id = o.object_id
JOIN 
    meters m ON ma.meter_id = m.meter_id
JOIN 
    meter_data d ON d.meter_id = m.meter_id
WHERE 
    d.date BETWEEN '2024-11-01' AND '2024-11-30'
    AND ma.date_from <= d.date
    AND (ma.date_to IS NULL OR ma.date_to >= d.date);

6.SELECT 
    child.id AS ielas_id,
    child.lname AS iela,
    parent.lname AS pilseta,
    child.address_name AS pilna_adrese
FROM 
    address child
JOIN 
    address parent ON child.address_index = parent.id
WHERE 
    child.address_level = 7  -- ielas līmenis
    AND parent.address_level = 3  -- pilsētas līmenis
    AND parent.lname = 'Jelgava'
ORDER BY 
    child.lname;

7. SELECT
    ROW_NUMBER() OVER (ORDER BY i.address_name) AS Nr,
    i.address_name AS Iela,
    i.lname AS Pilseta
FROM
    address i
WHERE
    i.address_level = 7
ORDER BY
    i.address_name;

8.SELECT
    ROW_NUMBER() OVER (ORDER BY address_name) AS Nr,
    address_name AS Iela,
    lname AS Pilseta,
    DATE_FORMAT(CURDATE(), '%d.%m.%Y') AS Datums
FROM address
WHERE address_level = 7
ORDER BY address_name;