Relationship between 4 tables

Question

I have the tables:

-- NORTE --
create table tb_indices_norte(
id_norte int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint,
num_inst smallint, 
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_norte)) default charset = utf8; 

-- SUL --
create table tb_indices_sul(
id_sul int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint,
num_inst smallint, 
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_sul)) default charset = utf8;

-- LESTE --
create table tb_indices_leste(
id_leste int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint, 
num_inst smallint,
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_leste)) default charset = utf8;

-- OESTE--
create table tb_indices_oeste(
id_oeste int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint, 
num_inst smallint,
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_oeste)) default charset = utf8;

How should I proceed to make a SELECT that returns me to SUM(totOvos), SUM(num_pos), SUM(num_inst) among the 4 tables WHERE ano = 'X' AND sem_epi BETWEEN 'Y' AND 'Z' GROUP BY sem_epi

I am beginner in SQL, I know I must define the PRIMARY KEYS and FOREIGN KEY, my problem is just this, how to define in the tables and how to create this SELECT.

Answer 1

Amiraldo observing your scheme, as Motta commented, it is not necessary to create 4 tables, just create 2 where one stores the data NORTH, SOUTH, EAST and WEST (each with its respective ID) and the other stores the details of the day, week, month, year, required as you requested. I made a sketch here where you can adopt it or modify it to your liking.

Below follows the Code:

Table Indices

CREATE TABLE Indices(
    ID INT IDENTITY PRIMARY KEY NOT NULL,
    Indice NVARCHAR(15) UNIQUE NOT NULL
)
GO

Table Details

CREATE TABLE DetalhesDoDia(
    ID INT IDENTITY PRIMARY KEY NOT NULL,
    IndicesID INT NOT NULL,
    Localidade NVARCHAR(30) NOT NULL,
    SemEPI SMALLINT NOT NULL DEFAULT 0,
    Ovos SMALLINT,
    NumPos SMALLINT,
    NumInst SMALLINT,
    IPO DECIMAL(5,1),
    IDO DECIMAL(5,1),
    Ano NVARCHAR(4),

    CONSTRAINT FK_IndicesDoDetalhe
    FOREIGN KEY (IndicesID)
    REFERENCES [dbo].[Indices](ID)
)
GO

Select that returns all data

SELECT Indices.Indice, Detalhes.Localidade, Detalhes.SemEPI, Detalhes.Ovos, Detalhes.NumPos, Detalhes.NumInst, Detalhes.IPO, Detalhes.IDO, Detalhes.Ano
FROM [dbo].[DetalhesDoDia] AS Detalhes
INNER JOIN [dbo].[Indices] AS Indices
ON Detalhes.[IndicesID] = Indices.[ID]
GO

Select with the Sum

SELECT SUM(Detalhes.Ovos) AS 'Total OVOS', SUM(Detalhes.NumPos) AS 'Total POS', SUM(Detalhes.NumInst) AS 'Total INST'
FROM [dbo].[DetalhesDoDia] AS Detalhes
INNER JOIN [dbo].[Indices] AS Indices
ON Detalhes.[IndicesID] = Indices.[ID]
WHERE Detalhes.Ano = '2018'
AND Detalhes.SemEPI BETWEEN 0 AND 5
GROUP BY Detalhes.SemEPI