Question for SQL Server - Code

Question

What are the names of the pilots who did less than '1:12' in the 1st round of the 2nd race?

• How can I make the times of every turn?

• I’ll have to make a new table?

The SQL Server tables created were the following:

CREATE TABLE Carro(
IdCarro INT NOT NULL,
Marca VARCHAR(20) NOT NULL,
Modelo VARCHAR(50),
PRIMARY KEY (IdCarro));

CREATE TABLE Piloto(
IdPiloto INT NOT NULL,
Nome VARCHAR(20) NOT NULL,
Morada VARCHAR(50),
Telefone INT,
PRIMARY KEY (IdPiloto));

CREATE TABLE Equipa(
IdEquipa INT NOT NULL,
Nome VARCHAR(20),
Morada VARCHAR(50),
Telefone INT,
PRIMARY KEY (IdEquipa));

CREATE TABLE Patrocinador(
IdPatrocinador INT NOT NULL,
Nome VARCHAR(20)NOT NULL,
Morada VARCHAR(50),
PRIMARY KEY (IdPatrocinador));

CREATE TABLE Prova(
IdProva INT NOT NULL,
Nome VARCHAR(20) NOT NULL,
Local VARCHAR(50) NOT NULL,
Data DATE NOT NULL,
NrVoltas INT NOT NULL,
MelhorTempo TIME,
PRIMARY KEY (IdProva));

CREATE TABLE EquipaParticipaProva(
IdEquipa INT NOT NULL,
IdCarro INT NOT NULL,
IdPiloto INT NOT NULL,
IdProva INT NOT NULL,
TempoPorVolta TIME,
TempoFinal TIME,
PosicaoRelativa INT,
PosicaoFinal INT,
FOREIGN KEY (IdProva) REFERENCES Prova(IdProva),
FOREIGN KEY (IdCarro) REFERENCES Carro(IdCarro),
FOREIGN KEY (IdPiloto) REFERENCES Piloto(IdPiloto),
PRIMARY KEY (IdEquipa,IdPiloto,IdCarro));

CREATE TABLE PatrocinadorOficial(
IdPatrocinador INT NOT NULL,
IdProva INT NOT NULL,
Valor MONEY NOT NULL,
FOREIGN KEY (IdProva) REFERENCES Prova(IdProva),
PRIMARY KEY (IdPatrocinador, IdProva));

CREATE TABLE PatrocinadorNaoOficial(
IdPatrocinador INT NOT NULL,
IdProva INT NOT NULL,
Valor MONEY NOT NULL,
FOREIGN KEY (IdProva) REFERENCES Prova(IdProva),
PRIMARY KEY (IdPatrocinador, IdProva));

CREATE TABLE Mecanico(
IdMecanico INT NOT NULL,
Nome VARCHAR(20),
Morada VARCHAR(50),
Telefone INT,
PRIMARY KEY (IdMecanico));

CREATE TABLE Afinacao(
IdAfinacao INT NOT NULL,
IdMecanico INT NOT NULL,
IdEquipa INT NOT NULL,
TipoAfinacao VARCHAR(20) NOT NULL,
TempoDespendido TIME,
Data DATE NOT NULL,
FOREIGN KEY (IdEquipa) REFERENCES Equipa(IdEquipa),
FOREIGN KEY (IdMecanico) REFERENCES Mecanico(IdMecanico),
PRIMARY KEY (IdAfinacao));

"Inserts" placed for testing:

INSERT INTO Carro
VALUES (1, 'Nissan', 'Skyline');

INSERT INTO Carro
VALUES (2, 'Subaru', 'Impreza');

INSERT INTO Carro
VALUES (3, 'Mitsubishi', 'Evolution');

INSERT INTO Piloto
VALUES (111, 'Carlos Simão', 'Castelo Branco', 111111111);

INSERT INTO Piloto
VALUES (112, 'Paulo Solipa', 'Castelo Branco', 111111112);

INSERT INTO Piloto
VALUES (113, 'JaGanhou', 'Lisboa', 111111113); 

INSERT INTO Piloto
VALUES (114, 'AsdoVolante', 'Lisboa', 111111114);

INSERT INTO Equipa
VALUES (001, 'OsAtolados', 'Castelo Branco', 272722123);

INSERT INTO Equipa
VALUES (002, 'Sparco', 'Lisboa', 212312432); 

INSERT INTO Equipa
VALUES (003, 'ESTeam', 'Castelo Branco', 272343573);

INSERT INTO Prova
VALUES (010, 'RallycrossCB', 'Castelo Branco', '2017-02-02', 4, '00:04:23');

INSERT INTO EquipaParticipaProva
VALUES (002, 3, 113, 010, '00:01:20', '00:04:40', 3, 3);

INSERT INTO PATROCINADOR
VALUES(921, 'IPCB', 'Av da Talagueira');

INSERT INTO PATROCINADOR
VALUES(922, 'Bells Bar', 'Praceta Qualquer Coisa');

INSERT INTO PATROCINADOR
VALUES(923, 'MonsterEnergy', 'USA');

INSERT INTO PATROCINADOR
VALUES(924, 'Sical', 'Lisboa');

INSERT INTO PATROCINADOROFICIAL
VALUES(923, 010, 10000);

INSERT INTO PATROCINADOROFICIAL
VALUES(921, 11, 8000);

INSERT INTO PATROCINADORNAOOFICIAL
VALUES(920, 010, 5000);

INSERT INTO PATROCINADORNAOOFICIAL
VALUES(922, 11, 3000);

INSERT INTO PATROCINADORNAOOFICIAL
VALUES(924, 10, 6000);

INSERT INTO EQUIPAPARTICIPAPROVA
VALUES(1, 3, 111, 11, '00:01:00', '00:10:00', 1, 1);

INSERT INTO Prova
VALUES (11, 'RallyLels', 'Bells', '2017-03-04', 10, '00:10:00');

INSERT INTO MECANICO
VALUES(333, 'Zacarias','Leiria',933628487);

INSERT INTO MECANICO
VALUES(334, 'Zé', 'Bragança', 963628487);

INSERT INTO MECANICO
VALUES(335, 'Luís', 'Lisboa', 913628487);

INSERT INTO AFINACAO
VALUES(20, 333, 001, 'Travões', '1:00:00', '2017-02-03');

INSERT INTO AFINACAO
VALUES(21, 334, 002, 'Luzes', '00:15:00', '2017-02-03');

INSERT INTO AFINACAO
VALUES(22, 335, 003, 'Travoes', '1:00:00', '2017-02-01');

Answer 1

I made something very simple to try to help you first with the structure of your database, then we will see the SQL commands for this.

See the model and any doubt, just talk. I made it very simple and obviously without the particularities of your needs.